agentleFS
wardawgmalviciouswardawgmalvicious/agent-config/skills/fabric-realtime/fabric-eventhouse/SKILL.md

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

The file

Files
SKILL.md

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 with GET /v1/workspaces/{wsId}/kqlDatabases (returns queryServiceUri + databaseName per item).
  • Token audience: https://kusto.kusto.windows.net/.default for 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.sh wrapper is deployed in the repo (an internal local-cli template), prefer it over the az rest recipe for queries and management commands: it takes KQL on -q, from a file with -i or 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. -l lists the databases the repo has configured and -h prints 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 role

Schema Evolution

  • .create-merge table is the safe / idempotent form — adds missing columns, never drops existing. Prefer over .create table for 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

CommandUse 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 call

Reach 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. See fabric-eventstream.

Full command syntax and CSV/JSON mapping definitions: references/ingestion.md.

Policies

PolicyPurposeNotes
RetentionSoft-delete period + recoverabilityJSON body with SoftDeletePeriod and Recoverability
CachingHot (SSD) vs cold tier window.alter table T policy caching hot = 30d
PartitioningHash key for extent pruningHash on column with XxHash64, MaxPartitionCount
MergeBackground extent merge thresholdsRowCountUpperBoundForMerge, MaxExtentsToMerge
Streaming ingestionEnable streaming endpointTable 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 ProductId

Permission Model

RoleQueryIngestAdmin
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

PatternWhy
Always filter by time first — where Timestamp > ago(...)Enables extent pruning
Use has over containshas uses term index (fast); contains is substring scan (slow)
project early to drop unused columnsReduces memory
summarize with bin(Timestamp, 1h)Efficient time bucketing
take 100 for explorationAvoids full scans
materialize() reused subexpressionsCaches intermediate result
Avoid * in projectExplicit column list survives schema changes

String matching speed

OperatorIndexedCase-SensitiveSpeed
==✅YesFastest
=~❌ partialNoMedium
has✅NoFast
has_cs✅YesFast
startswithpartialNoMedium
contains❌NoSlow
matches regex❌YesSlowest

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 count

Cross-Database Queries

database("OtherDB").OtherTable | take 10

Graph 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

IssueCauseFix
Request is invalid and cannot be processed (401)Wrong token audienceUse https://kusto.kusto.windows.net/.default
dynamic column shows as stringStored 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 untestedCheck the column's type (getschema); wrap with parse_json(col) or todynamic()
Forbidden (403) on management commandsInsufficient roleNeed admin or ingestor database role
OneLake / ADLS ingest auth failsMissing ;impersonate on URIAppend ;impersonate to the storage URI
Materialized view stuck at 0%No new source data or backfill pending.show materialized-view MV statistics
.drop column failsColumn referenced by materialized view or functionDrop dependents first
Streaming ingestion errorsNo streaming policy on the table or its database, or a table-level false over the database'sRead both policies, then .alter table T policy streamingingestion enable
External table returns no dataPath / format / schema mismatchVerify abfss:// path, dataformat=, and column types match source
Retention deleting data too soonTable-level policy overrides DB default.show table T policy retention
dcount() returns approximate valueHyperLogLog by designdcount(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 changeCached, asynchronously-recomputed statistic — the data is untouchedVerify 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 — kqlDatabases listing endpoint and pagination; kusto.kusto.windows.net/.default audience details in its references/auth.md; the fab CLI for Eventhouse item creation, principals and exports in its references/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.

Download now (ZIP)

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
What did you use it for? e.g. Reviewing pull requests in a Rails app

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.