fabric-eventhouse
Skill1 starsChanged 35 days agoMITView on GitHub
Use for Microsoft Fabric Eventhouse / KQL Database. Covers connection (cluster URI via `kqlDatabases` REST, kusto.kusto.windows.net audience, az rest temp-file for `|` escaping), authoring (`.create-merge` for safe schema evolution, ingestion inline/set-or-append/from-storage `;impersonate`, streaming policy, CSV/JSON mappings, retention/caching/partitioning/merge policies, materialized views + update policies, external tables), OneLake-availability-ON constraints (add/delete column ✅ April 2026+; type/rename/RLS/deletes need availability off), per-KQL-database remote MCP server (http, read/query auth, not in global MCP template), 4-role permissions (viewer/user/ingestor/admin), KQL query patterns (time-filter-first, has vs contains, materialize), string-matching speed table, KQL graph operators (`make-graph`/`graph-match`/`graph()` snapshots, openCypher — in-engine KQL graph, NOT the GraphModel item; see fabric-iq), and Fabric gotchas (`;impersonate`, MV stuck at 0%, dynamic vs string, == case-sensitive).
- Deletes or force-pushes
In this repository38 other files it gives its agents.
- claude/CLAUDE.mdCLAUDE.md
- CLAUDE.mdCLAUDE.md
- author-skillSkill
- drift-auditSkill
- drift-handoffSkill
- drift-updateSkill
- test-skillSkill
- triageSkill
- fabric-iqSkill
- fabric-catalog-governanceSkill
- fabric-deploySkill
- fabric-rest-apiSkill
- fabric-securitySkill
- fabric-variable-librarySkill
- fabric-databaseSkill
- fabric-dataflowSkill
- fabric-data-pipelineSkill
- fabric-sparkSkill
- fabric-warehouse-monitoringSkill
- fabric-warehouseSkill
- fabric-activatorSkill
- fabric-event-schema-setSkill
- fabric-eventstreamSkill
- fabric-realtime-dashboardSkill
- learnSkill
- fabric-semantic-model-auditSkill
- fabric-tmdlSkill
- pbip-project-structureSkill
- pbir-report-workflowSkill
- linkedin-highlightsSkill
- msix-packagingSkill
- code-reviewSkill
- commitSkill
- find-sessionSkill
- landSkill
- prune-branchesSkill
- recreate-repoSkill
- run-briefsSkill
The file
Files
Fabric Eventhouse / KQL Database
KQL management and query patterns specific to Fabric Eventhouse. Assumes familiarity with KQL — focus is on Fabric-specific behavior and az-rest-driven workflows.
Connection
- Cluster URI: each KQL Database has its own
queryServiceUri,https://<cluster>.kusto.fabric.microsoft.com. Discover withGET /v1/workspaces/{wsId}/kqlDatabases(returnsqueryServiceUri+databaseNameper item). - Token audience:
https://kusto.kusto.windows.net/.defaultfor all direct access. A wrong audience surfaces as a 401 "Request is invalid and cannot be processed", not an auth error. - Query endpoint:
POST {clusterUri}/v1/rest/query, body{"db":"<dbName>", "csl":"<KQL>"}. - KQL
|breaks shell escaping — write the JSON body to a temp file and pass--body @<file>, never inline. - If a
scripts/data/kql.shwrapper is deployed in the repo (an internal local-cli template), prefer it over theaz restrecipe for queries and management commands: it takes KQL on-q, from a file with-ior on stdin with no temp file, and routes a leading-dot command to/v1/rest/mgmt. It fails on an HTTP error status, and it reads a 200's body for the partial failures and warnings Kusto reports there, printing each on stderr with a non-zero exit, so a flagged result never passes for a clean one.-llists the databases the repo has configured and-hprints the rest. It is an accelerator that happens to be present, never a dependency; item definitions still take the REST envelope.
Worked az rest invocation and the REST item-definition envelope:
references/programmatic-access.md.
Schema Discovery
.show tables
.show table T schema as json // column names + types, for diffing in code
.show table T cslschema // the same as name:type, to paste into .alter / .create
.show table T details // row count, extent count, size
.show functions
.show materialized-views
.show database principals // who has what roleSchema Evolution
.create-merge tableis the safe / idempotent form — adds missing columns, never drops existing. Prefer over.create tablefor repeatable deployments. A deployment pipeline appears to apply a KQL database's schema the same way: two stages reached only by pipelines held every column added in the repo and every one since dropped from it, the same set in both (inferred from their end state, 2026-10-07; Learn's deployment pages don't say). Run drops live in each stage..alter-merge table T (NewCol: string)— add column..alter table T (Col1:type, Col2:type, …)restates the whole column list and is the only command that reorders: the table keeps exactly the columns listed, in that order, and drops any left out, with their data. Start from.show table T cslschema. Learn also warns that parallel ingestion "that disregards the order of columns" can land values in the wrong columns, and prescribes a mapping or a pause. A CSV sender is inside that; name-keyed MultiJSON is likely outside it, since it maps by name (references/ingestion.md), and alters under live unmapped MultiJSON streaming did no harm (2026-09-22), but no deliberately concurrent test has been run..rename column T.OldName to NewName— rename..drop column T.OldCol— irreversible; fails if column is used in materialized view or function (drop dependents first)..drop table T ifexists— guarded drop.- Atomic blue-green swap via
.rename tables A=B, B=C, C=A(single command, atomic).
Docstring and folder: measure, don't read. Learn overstates what
.alter table does to them and says nothing of what .create-merge
does. A bare .alter table, with no with (...) clause, leaves the
table's docstring and folder as they were, though Learn says it
overwrites both; column docstrings survive too, since the command has no
syntax for them. .create-merge table … with (docstring = "…") does set
an existing table's docstring, where Learn's page covers only columns.
Measured on Fabric Eventhouse, not ADX: the alter on 2026-09-22 against
before/after schema dumps (docstring on real tables in two environments,
folder on a scratch table), the create-merge on 2026-09-17. Restating
with (docstring = "…", folder = "…") on an alter still costs nothing
and guards the documented reading: belt-and-braces, not a required
workaround.
With OneLake availability ON the surface narrows: adding and deleting columns work (April 2026+), but altering a column type, renaming a table, applying RLS, and deleting/truncating/purging data do not — toggle availability off, change, toggle back on. Full matrix: references/onelake-availability.md.
Ingestion
| Command | Use for |
.ingest inline into table T <| | Small data / testing |
.set-or-append T <| | Append KQL query results |
.set-or-replace T <| | Replace table contents from a query |
.ingest into table T (h'abfss://...;impersonate') | Blob / ADLS / OneLake files — the ;impersonate suffix is required |
Streaming ingestion needs a policy on the table or its database: read
it before enabling one. A database policy covers every table, and a
table's own overrides it (Learn). Fabric set one, IsEnabled: true, in
the second after it created a database by REST, and a sibling read the
same (journal and policy reads, 2026-10-12; other creation routes
unchecked). So .show database D policy streamingingestion, then the
table's for one that refuses, and
.alter table Events policy streamingingestion enable only where neither
is on.
Pair every DDL change on a streaming-ingested table with a cache
clear. Nodes cache each table's streaming-ingestion schema. The
refresh after a .create-merge is automatic but asynchronous and takes
minutes, and until it lands every ingest keeps writing the old shape —
a new column arrives empty on every row, with a success response and
a matching record count. Nothing errors anywhere.
.clear table T cache streamingingestion schema // one table
.clear database cache streamingingestion schema // every table, one callReach for the database-scoped form after a bulk operation — one call
across 19 renamed tables against 19 round trips. It also collapses the
cold-table window in which a freshly created table answers 520 to
every ingest (measured 76 s, 2026-09-14). Both return one row per node;
a Status=Failed row is safely retryable.
Schema-associated Eventstream destinations auto-create one table per schema named
{CloudEventType}_{CloudEventSchemaVersion}(e.g.Orders_v1) — don't create these by hand. Seefabric-eventstream.
Full command syntax and CSV/JSON mapping definitions: references/ingestion.md.
Policies
| Policy | Purpose | Notes |
| Retention | Soft-delete period + recoverability | JSON body with SoftDeletePeriod and Recoverability |
| Caching | Hot (SSD) vs cold tier window | .alter table T policy caching hot = 30d |
| Partitioning | Hash key for extent pruning | Hash on column with XxHash64, MaxPartitionCount |
| Merge | Background extent merge thresholds | RowCountUpperBoundForMerge, MaxExtentsToMerge |
| Streaming ingestion | Enable streaming endpoint | Table or database; a new Fabric database may carry it. .show database D policy streamingingestion first |
Database-level policies (.alter database MyDB policy ...) act as defaults; table-level overrides them. Check effective policy with .show table T policy retention.
Materialized Views
.create materialized-view with (backfill=true) EventCounts on table Events {
Events | summarize Count = count(), LastSeen = max(Timestamp) by EventType
}
// Lifecycle
.show materialized-view EventCounts statistics
.disable materialized-view EventCounts
.enable materialized-view EventCounts
.drop materialized-view EventCounts
// Query a materialized view
materialized_view("EventCounts") | where EventType == "Login"Stored Functions and Update Policies
.create-or-alter function takes docstring, folder, and default parameter
values — see references/REFERENCE.md. The Fabric-specific
piece is wiring one as an update policy, an automatic transform applied on
ingestion into the source table:
.create-or-alter function ParseRawEvents() {
RawEvents
| extend Parsed = parse_json(RawData)
| project
Timestamp = todatetime(Parsed.timestamp),
UserId = tostring(Parsed.userId)
}
.alter table ParsedEvents policy update
@'[{"IsEnabled":true,"Source":"RawEvents","Query":"ParseRawEvents()","IsTransactional":true}]'IsTransactional: true makes the source-row insert and the transform atomic — failed transform aborts both.
External Tables (OneLake / ADLS)
.create external table ExternalSales (
OrderDate: datetime, ProductId: string, Quantity: int, Amount: real
) kind=storage
dataformat=parquet
( h'abfss://workspace@onelake.dfs.fabric.microsoft.com/lakehouse.Lakehouse/Tables/sales;impersonate' )
external_table("ExternalSales") | where OrderDate > ago(30d) | summarize sum(Amount) by ProductIdPermission Model
| Role | Query | Ingest | Admin |
viewer | ✅ | ❌ | ❌ |
user | ✅ | ❌ | ❌ |
ingestor | ❌ | ✅ | ❌ |
admin | ✅ | ✅ | ✅ |
.add database MyDB viewers ('aaduser=user@contoso.com')
.add database MyDB admins ('aaduser=admin@contoso.com')
.add table T admins ('aaduser=tableadmin@contoso.com')Layered with Fabric workspace roles (Admin/Member/Contributor/Viewer). restrict access and security functions provide RLS.
A database shortcut in an eventhouse can share selected subitems (tables, shortcut tables, materialized views, functions) instead of the whole source database, GA since September 2026 (Learn, 2026-10-06).
Query Patterns
| Pattern | Why |
Always filter by time first — where Timestamp > ago(...) | Enables extent pruning |
Use has over contains | has uses term index (fast); contains is substring scan (slow) |
project early to drop unused columns | Reduces memory |
summarize with bin(Timestamp, 1h) | Efficient time bucketing |
take 100 for exploration | Avoids full scans |
materialize() reused subexpressions | Caches intermediate result |
Avoid * in project | Explicit column list survives schema changes |
String matching speed
| Operator | Indexed | Case-Sensitive | Speed |
== | ✅ | Yes | Fastest |
=~ | ❌ partial | No | Medium |
has | ✅ | No | Fast |
has_cs | ✅ | Yes | Fast |
startswith | partial | No | Medium |
contains | ❌ | No | Slow |
matches regex | ❌ | Yes | Slowest |
Monitoring
.show queries // currently running
.show commands | where StartedOn > ago(1h)
.show journal // management ops history
.show ingestion failures | where FailedOn > ago(24h)
.show table T extents // per-table extent count + size
.show database datastats // DB size, extent count, row countCross-Database Queries
database("OtherDB").OtherTable | take 10Graph semantics
KQL can do graph analysis inside the KQL engine — make-graph for a
transient graph, graph() over a persistent snapshot, then graph-match /
graph-shortest-paths. This is not the standalone Fabric GraphModel
item, which is a separate workload using GQL / ISO-39075 (see fabric-iq).
Same word, different engine.
Operators, snapshot management and limits, and the openCypher/GQL-over-KQL preview: references/graph-operators.md.
Gotchas
| Issue | Cause | Fix |
Request is invalid and cannot be processed (401) | Wrong token audience | Use https://kusto.kusto.windows.net/.default |
dynamic column shows as string | Stored as string: a string-typed column, or a value that never parsed. JSON text streamed into a dynamic column was parsed on ingestion (once, 2026-10-12); invalid JSON is untested | Check the column's type (getschema); wrap with parse_json(col) or todynamic() |
Forbidden (403) on management commands | Insufficient role | Need admin or ingestor database role |
| OneLake / ADLS ingest auth fails | Missing ;impersonate on URI | Append ;impersonate to the storage URI |
| Materialized view stuck at 0% | No new source data or backfill pending | .show materialized-view MV statistics |
.drop column fails | Column referenced by materialized view or function | Drop dependents first |
| Streaming ingestion errors | No streaming policy on the table or its database, or a table-level false over the database's | Read both policies, then .alter table T policy streamingingestion enable |
| External table returns no data | Path / format / schema mismatch | Verify abfss:// path, dataformat=, and column types match source |
| Retention deleting data too soon | Table-level policy overrides DB default | .show table T policy retention |
dcount() returns approximate value | HyperLogLog by design | dcount(col, 4) for higher accuracy (costly), or T | distinct col | count for exact |
.show tables details reports TotalRowCount 0 for every table after a rename or other structural change | Cached, asynchronously-recomputed statistic — the data is untouched | Verify before concluding anything: union withsource=SourceTable T1, T2, … | summarize count() by SourceTable. Reproduced 2026-09-15 across 19 tables |
.show database policy mirroring / policy update → "Syntax error: A recognition error occurred" | Those policy .show commands are table-scoped only — unlike the streaming-ingestion cache clear, which is database-scoped | .show table T policy mirroring. There is no multi-table form either — .show tables (A, B) policy mirroring fails the same way |
Remote MCP server (preview)
A hosted HTTP MCP server per KQL database gives schema discovery, NL→KQL, and
query execution. It connects from Claude Code — through a headersHelper
command supplying an az bearer at the https://api.fabric.microsoft.com
audience, measured 2026-09-14 on CLI 2.1.268 against this exact
workspace/item-bound shape over two rounds. What fails is Claude Code's
automatic OAuth flow, which registers via Dynamic Client Registration that
Microsoft's auth server doesn't support — a fact about the flow, not about the
endpoint. Watch the failure text on this URL shape: a helper that yields no
credential prints Error dialing <url>, which reads like a network fault and
is an auth one. The local fabric-rti-mcp (uvx microsoft-fabric-rti-mcp)
stays worth preferring for breadth rather than reachability — it also reaches
ADX and Eventstream. URL pattern, auth requirements, and the config block:
references/remote-mcp.md.
Reference
- Microsoft Learn: Eventhouse overview
- Full MS Learn link bundle (KQL management commands / ingestion / mappings / materialized views / update policies / monitoring / REST query API): references/REFERENCE.md
See also
- fabric-rest-api skill —
kqlDatabaseslisting endpoint and pagination;kusto.kusto.windows.net/.defaultaudience details in itsreferences/auth.md; thefabCLI for Eventhouse item creation, principals and exports in itsreferences/cli.md - fabric-iq skill — the other graph workload: the standalone Fabric GraphModel item (GQL / ISO-39075 over OneLake). Different engine; the KQL graph operators above do NOT apply there.
Does it work?
Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.
No reviews yet
People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.
- It worked
- Partly
- It didn't
Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.
Your trail
Files you open will collect here, so you can find them again.
Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.
Now do this for your own.
Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

