# Fabric Eventhouse

> 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 + u…

- **Type:** Skill
- **Install:** `agentstack add skill-wardawgmalvicious-claude-config-fabric-eventhouse`
- **Verified:** Pending review
- **Seller:** [wardawgmalvicious](https://agentstack.voostack.com/s/wardawgmalvicious)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [wardawgmalvicious](https://github.com/wardawgmalvicious)
- **Source:** https://github.com/wardawgmalvicious/claude-config/tree/main/skills/fabric-eventhouse

## Install

```sh
agentstack add skill-wardawgmalvicious-claude-config-fabric-eventhouse
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# 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 a unique `queryServiceUri` of the form `https://.kusto.fabric.microsoft.com`. Discover via Fabric REST: `GET /v1/workspaces/{wsId}/kqlDatabases` (returns `queryServiceUri` and `databaseName` per item).
- **Token audience**: `https://kusto.kusto.windows.net/.default` for all direct access.
- **Query endpoint**: `POST {clusterUri}/v1/rest/query` with body `{"db":"", "csl":""}`.
- **KQL `|` breaks shell escaping** — write the JSON body to a temp file and use `--body @` (bash) or `@$env:TEMP\kql_body.json` (PowerShell).

```bash
cat > /tmp/kql_body.json  ago(1d)

// Replace table contents with KQL query results
.set-or-replace Events  **Schema-associated Eventstream destinations** auto-create one table per schema named `{CloudEventType}_{CloudEventSchemaVersion}` (e.g. `SLTerms_v1`) — you don't create these by hand. The producer's `cloudEvents:type` and `dataschema` version drive the table name; see `fabric-eventstream` → *Producing to a schema-associated custom endpoint*.

## Data Mappings

```kql
// CSV — by ordinal
.create table Events ingestion csv mapping "EventsCsvMapping"
'[{"column":"Timestamp","datatype":"datetime","ordinal":0},
  {"column":"EventType","datatype":"string","ordinal":1}]'

// JSON — by JSONPath
.create table Events ingestion json mapping "EventsJsonMapping"
'[{"column":"Timestamp","path":"$.timestamp","datatype":"datetime"},
  {"column":"EventType","path":"$.eventType","datatype":"string"}]'

.show table Events ingestion csv mappings
.show table Events ingestion json mappings
```

## 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 | `.alter table T policy streamingingestion enable` |

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

```kql
.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"
```

**Supported aggregations**: `count()`, `sum()`, `min()`, `max()`, `dcount()`, `avg()`, `countif()`, `sumif()`, `arg_max()`, `arg_min()`, `make_set()`, `make_list()`, `percentile()`, `take_any()`.

## Stored Functions and Update Policies

```kql
// Stored function — supports docstring, folder, default param values
.create-or-alter function with (
    docstring = "Get events for a user in time range",
    folder = "Analytics"
) GetUserEvents(userId: string, lookback: timespan = 1d) {
    Events | where Timestamp > ago(lookback) and UserId == userId
}

// Update policy — automatic transform on ingestion to 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)

```kql
.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

| Role | Query | Ingest | Admin |
|---|---|---|---|
| `viewer` | ✅ | ❌ | ❌ |
| `user` | ✅ | ❌ | ❌ |
| `ingestor` | ❌ | ✅ | ❌ |
| `admin` | ✅ | ✅ | ✅ |

```kql
.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.

## 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

```kql
.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

```kql
database("OtherDB").OtherTable | take 10
```

## Graph semantics (KQL graph operators)

KQL can do graph analysis **inside the KQL engine** — this is a different thing from the standalone **Fabric GraphModel** item (a separate workload using GQL / ISO-39075; see the **fabric-graph** skill). Same word "graph", different engine: here a graph is built from KQL tabular data (or a stored snapshot) and queried with KQL graph operators, not GQL.

**Transient graph** — built in-memory per query with `make-graph`, then matched. `make-graph` must be followed by a graph operator.

```kql
edges
| make-graph Source --> Destination with nodes on name   // or: with_node_id=name
| graph-match (mallory)-[attacks]->(victim)-[hasPermission]->(trent)
    where mallory.name == "Mallory" and trent.name == "Trent"
    project Attacker = mallory.name, Compromised = victim.name, System = trent.name
```

**Persistent graph** (preview) — materialize a snapshot once, query repeatedly via the `graph()` function.

```kql
.create-or-alter graph_model OrgGraph
// model is a JSON doc: "Schema" { Nodes, Edges } + "Definition" { Steps: AddNodes / AddEdges }
.make graph_snapshot OrgGraph_v1 from OrgGraph

graph("OrgGraph")                            // latest snapshot; graph("OrgGraph","OrgGraph_v1") for a specific one
| graph-match (mgr) (  )` runs the operator per partition value (multitenant) — the partition column must exist in both edges and nodes tables.

## Gotchas

| Issue | Cause | Fix |
|---|---|---|
| `Request is invalid and cannot be processed` (401) | Wrong token audience | Use `https://kusto.kusto.windows.net/.default` |
| Query timeout | No time filter, scanning too much | Add `where Timestamp > ago(...)` |
| `has` returns unexpected results | Whole-term match, not substring | Use `contains` for substring (slower) |
| `==` misses rows | Case-sensitive on strings | Use `=~` for case-insensitive |
| `dynamic` column shows as string | Stored as string, not dynamic | 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 | Streaming policy not enabled | `.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 |
| `render` not showing in CLI | `render` is a client-side hint | Use Real-Time Intelligence portal or export data |

## Item definition envelope (REST)

For programmatic create/update via Fabric REST (see fabric-rest-api skill for the envelope):

| Item | Format | Required parts |
|---|---|---|
| **Eventhouse** | `JSON` | `EventhouseProperties.json` (currently empty: `{}`) |
| **KQLDatabase** | `JSON` | `DatabaseProperties.json` (+ optional `DatabaseSchema.kql`) |

`DatabaseProperties.json` schema:

```json
{
  "databaseType": "ReadWrite",
  "parentEventhouseItemId": "",
  "oneLakeCachingPeriod": "P36500D",
  "oneLakeStandardStoragePeriod": "P365000D"
}
```

`DatabaseSchema.kql` is an optional KQLDatabase part — KQL management commands run at deploy time. Use to seed tables, materialized views, functions, and ingestion mappings as part of the definition (e.g. `.create-merge table MyLogs (...)` blocks).

## Remote MCP server (preview)

A hosted, HTTP-transport MCP server lets Copilot, GitHub Copilot CLI, and custom agents discover the KQL schema, generate KQL from natural language, execute queries, and sample data — without local install.

- **URL pattern**: `https://api.fabric.microsoft.com/v1/mcp/dataPlane/workspaces/{workspaceId}/items/{kqlDatabaseId}/kqlEndpoint` — **per KQL database**, not workspace-wide.
- **Find URL**: Fabric portal → workspace → KQL database → **Database details** > **Overview** > **Copy URI** next to **MCP Server URI**.
- **Transport**: `http`.
- **Auth**: caller needs **Read** or **Query** permission on the KQL database. Schema discovery additionally requires **Copilot in Fabric** to be enabled at the tenant; without it, the server can only execute KQL — no schema introspection, no NL→KQL.
- **Per-database scoping** means the URL is workspace+item-specific; do not put it in a global MCP template (e.g. `~/.claude/mcp/.mcp.global.template.json`). Add it to a per-project template (`~/.claude/mcp/.mcp.project.template.json` carries this entry with `` / `` placeholders) or a per-IDE config such as `.vscode/mcp.json`.

```json
{
  "mcpServers": {
    "eventhouse-remote-mcp": {
      "type": "http",
      "url": "https://api.fabric.microsoft.com/v1/mcp/dataPlane/workspaces//items//kqlEndpoint"
    }
  }
}
```

## Reference

- Microsoft Learn: [Eventhouse overview](https://learn.microsoft.com/fabric/real-time-intelligence/eventhouse)
- Microsoft Learn: [KQL string operators (has vs contains, term index)](https://learn.microsoft.com/kusto/query/datatypes-string-operators?view=microsoft-fabric)
- Microsoft Learn: [Eventhouse OneLake availability](https://learn.microsoft.com/fabric/real-time-intelligence/event-house-onelake-availability)
- Microsoft Learn: [Get started with the remote MCP server for Eventhouse](https://learn.microsoft.com/fabric/real-time-intelligence/mcp-remote-eventhouse)
- Comprehensive MS Learn link bundle (KQL management commands / ingestion / mappings / materialized views / update policies / monitoring / REST query API): [references/REFERENCE.md](references/REFERENCE.md)

## See also

- fabric-auth skill — `kusto.kusto.windows.net/.default` audience details
- fabric-cli skill — `fab` CLI for Eventhouse item creation, principals, exports
- fabric-rest-api skill — `kqlDatabases` listing endpoint and pagination
- fabric-graph 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.

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [wardawgmalvicious](https://github.com/wardawgmalvicious)
- **Source:** [wardawgmalvicious/claude-config](https://github.com/wardawgmalvicious/claude-config)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: flagged — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-wardawgmalvicious-claude-config-fabric-eventhouse
- Seller: https://agentstack.voostack.com/s/wardawgmalvicious
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.