Skill v1.0.0
Trusted Publisher100/100version: "1.0.0" name: kql description: "KQL language expertise for writing correct, efficient Kusto queries using the Fabric RTI MCP tools. Covers syntax gotchas, join patterns, dynamic types, datetime pitfalls, regex patterns, serialization, memory management, result-size discipline, and advanced functions (geo, vector, graph). USE THIS SKILL whenever writing, debugging, or reviewing KQL queries — even simple ones — because the gotchas section prevents the most common errors that waste tool calls and cause expensive retry cascades. Trigger on: KQL, Kusto, ADX, Azure Data Explorer, Fabric Eventhouse, log analysis, data exploration, time series, anomaly detection, summarize, where clause, join, extend, project, let statement, parse operator, extract function, any mention of pipe-forward query syntax."
KQL Mastery
Try it yourself: All✅examples in this skill can be run against the public help cluster:https://help.kusto.windows.net, databaseSamples(containsStormEvents,SimpleGraph_Nodes/Edges,nyc_taxi, and more).
1. Running KQL with Fabric RTI MCP
Fabric RTI MCP exposes Kusto functionality as MCP tools. Authentication is handled transparently using Azure Identity.
Available tools
| Tool | Purpose | |
|---|---|---|
kusto_query | Execute a KQL query on a database | |
kusto_command | Execute a management command (.show, .create, etc.) | |
kusto_list_entities | List databases, tables, external tables, materialized views, functions, graphs | |
kusto_describe_database | Get schema for all entities in a database | |
kusto_describe_database_entity | Get schema for a specific entity (table, function, etc.) | |
kusto_sample_entity | Get sample data from a table or other entity | |
kusto_graph_query | Execute a graph query using snapshots or transient graphs | |
kusto_ingest_inline_into_table | Ingest inline CSV data into a table | |
kusto_known_services | List configured Kusto services | |
kusto_get_shots | Retrieve semantically similar shots from a shots table | |
kusto_deeplink_from_query | Build a deeplink URL to open a query in the web explorer | |
kusto_show_queryplan | Get the execution plan for a query without running it | |
kusto_diagnostics | Get a best-effort cluster health and capacity summary |
Query vs management commands
KQL has two execution planes, each with its own MCP tool:
| Plane | Tool | Starts with | Examples | ||
|---|---|---|---|---|---|
| Query | kusto_query | Table name, let, print, datatable | `StormEvents \ | where State == "TEXAS"` | |
| Management | kusto_command | .show, .create, .set, .drop, .alter | .show tables, .show table T schema |
Basic usage
# Query plane — use kusto_querykusto_query(cluster_uri="https://help.kusto.windows.net",database="Samples",query="StormEvents | summarize count() by EventType | top 5 by count_ desc")# Management plane — use kusto_commandkusto_command(cluster_uri="https://help.kusto.windows.net",database="Samples",command=".show tables")# Schema exploration — use kusto_describe_database or kusto_describe_database_entitykusto_describe_database(cluster_uri="https://help.kusto.windows.net",database="Samples")# Sample data — use kusto_sample_entitykusto_sample_entity(cluster_uri="https://help.kusto.windows.net",database="Samples",entity_name="StormEvents",entity_type="table",sample_size=5)# Graph queries — use kusto_graph_querykusto_graph_query(cluster_uri="https://mycluster.kusto.windows.net",database="MyDB",graph_name="MyGraph",query="| graph-match (node) project labels=labels(node)")# Deeplinks — use kusto_deeplink_from_querykusto_deeplink_from_query(cluster_uri="https://help.kusto.windows.net",database="Samples",query="StormEvents | count")
Exploration workflow
When encountering a new cluster or database:
- List entities:
kusto_list_entities(cluster_uri, entity_type="tables", database="MyDB") - Get schema:
kusto_describe_database_entity(entity_name="MyTable", entity_type="table", cluster_uri=..., database=...) - Sample data:
kusto_sample_entity(entity_name="MyTable", entity_type="table", cluster_uri=..., sample_size=5) - Count rows:
kusto_query(query="MyTable | count", cluster_uri=..., database=...) - Run analysis:
kusto_query(query="MyTable | where ... | summarize ...", cluster_uri=..., database=...)
2. Dynamic Type Discipline
KQL's dynamic type is flexible but strict in certain contexts. A common mistake is using a dynamic column in summarize by, order by, or join on without casting.
The rule: Any time you use a dynamic-typed column in by, on, or order by, wrap it in an explicit cast.
// ❌ ERROR: "Summarize group key 'Partners' is of a 'dynamic' type"| summarize count() by Partners// ✅ FIX| summarize count() by tostring(Partners)
// ❌ ERROR: "order operator: key can't be of dynamic type"| order by Area desc// ✅ FIX| order by tostring(Area) desc
// ❌ ERROR in join: dynamic join key| join kind=inner other on $left.Area == $right.Area// ✅ FIX — cast both sides| extend Area_str = tostring(Area)| join kind=inner (other | extend Area_str = tostring(Area)) on Area_str
Self-correction: When you see "is of a 'dynamic' type" in an error, add tostring(), tolong(), or todouble().
3. Join Patterns & Pitfalls
KQL joins have constraints that differ from SQL.
Equality only
KQL join conditions support only `==`. No <, >, !=, or function calls in join predicates.
// ❌ ERROR: "Only equality is allowed in this context"| join on geo_distance_2points(a.Lat, a.Lon, b.Lat, b.Lon) < 1000// ✅ WORKAROUND — pre-bucket into spatial cells, then join on cell ID| extend cell = geo_point_to_s2cell(Lon, Lat, 8)| join kind=inner (other | extend cell = geo_point_to_s2cell(Lon, Lat, 8)) on cell
For range joins, pre-bin values: | extend bin_val = bin(Value, 100), then join on bin_val.
Left/right attribute matching
Both sides of a join on clause must reference column entities only — not expressions, not aggregates.
// ❌ ERROR: "for each left attribute, right attribute should be selected"| join kind=inner other on $left.col1// ✅ FIX — specify both sides explicitly| join kind=inner other on $left.col1 == $right.col1
Cardinality check before large joins
Always check cardinality before joining tables with >10K rows. A cross-join explosion was the source of the single E_RUNAWAY_QUERY error (25K × 195 = potential 4.8M rows).
// Before joining, check how many rows each side contributesTableA | summarize dcount(JoinKey) // → 25,000? Too many for an unconstrained joinTableB | summarize dcount(JoinKey) // → 195? OK if filtered first
4. Regex in KQL
KQL handles regex natively — no need for Python.
The extract_all gotcha
Unlike Python's re.findall(), KQL's extract_all requires capturing groups in the regex:
// ❌ ERROR: "extractall(): argument 2 must be a valid regex with [1..16] matching groups"| extend words = extract_all(@"[a-zA-Z]{3,}", Text)// ✅ FIX — add parentheses around the pattern| extend words = extract_all(@"([a-zA-Z]{3,})", Text)
Regex toolkit — don't fall back to Python
| Function | Use case | Example | |
|---|---|---|---|
extract(regex, group, source) | Single match | extract(@"User '([^']+)'", 1, Msg) | |
extract_all(regex, source) | All matches (needs ()) | extract_all(@"(\w+)", Text) | |
parse | Structured extraction | parse Msg with * "User '" Sender "' sent" * | |
matches regex | Boolean filter | where Url matches regex @"^https?://" | |
replace_regex | Find and replace | replace_regex(Text, @"\s+", " ") |
5. Serialization Requirements
Window functions need serialized (ordered) input.
// ❌ ERROR: "Function 'row_cumsum' cannot be invoked. The row set must be serialized."| summarize Online = sum(Direction) by bin(Timestamp, 5m)| extend CumulativeOnline = row_cumsum(Online)// ✅ FIX — add | serialize (or | order by, which implicitly serializes)| summarize Online = sum(Direction) by bin(Timestamp, 5m)| order by Timestamp asc| extend CumulativeOnline = row_cumsum(Online)
Functions requiring serialization: row_number(), row_cumsum(), prev(), next(), row_window_session().
6. Memory-Safe Query Patterns
The most common memory error. Caused by scanning too much data without pre-filtering.
The progression of safety
Safest ──────────────────────────────────────────────── Most dangerous| count | take 10 | where + summarize | summarize (no filter) | full scan
Rules for large tables (>1M rows)
- Always start with `| count` to understand table size
- Always `| where` before `| summarize` — filter time range, partition key, or category first
- Never `dcount()` on high-cardinality columns without pre-filtering
- Check join cardinality before executing (see Section 3)
- Use `materialize()` for subqueries referenced multiple times
// ❌ OUT OF MEMORY — 24M rows, no filter, dcount on every columnConsumption| summarize dcount(Consumed), count() by Timestamp, HouseholdId, MeterType| where dcount_Consumed > 1// ✅ SAFE — filter first, then aggregateConsumption| where Timestamp between (datetime(2023-04-15) .. datetime(2023-04-16))| summarize dcount(Consumed) by HouseholdId, MeterType| where dcount_Consumed > 1
When you see E_LOW_MEMORY_CONDITION
The query touched too much data. Your options:
- Add
| wherefilters (time range, partition key) - Reduce the number of
bycolumns insummarize - Break into smaller time windows and union results
- Use
| sample 10000for exploratory work instead of full scans
When you see E_RUNAWAY_QUERY
A join or aggregation produced too many output rows. Check join cardinality — one or both sides is too large.
7. Result Size Discipline
Large results slow down analysis. Prevention:
| Query type | Safeguard | |||
|---|---|---|---|---|
| Exploratory | Always end with `\ | take 10 or \ | take 20` | |
| Aggregation | Use `\ | top 20 by ... not unbounded summarize` | ||
| Wide rows (vectors, JSON) | `\ | project` only needed columns | ||
make_list() / make_set() | Avoid on high-cardinality groups (produces huge cells) | |||
| Unknown size | Run `\ | count` first |
The vector trap: Tables with embedding columns (1536-dim float arrays) produce ~30KB per row. Even | take 20 yields 600KB. Always | project away vector columns unless you specifically need them.
With MCP tools: Use kusto_sample_entity for quick data previews. For deeper exploration, use kusto_query with | take N or | top N to bound results.
8. String Comparison Strictness
KQL sometimes requires explicit casts when comparing computed string values — even when both sides are already strings.
// ❌ ERROR: "Cannot compare values of types string and string. Try adding explicit casts"| where geo_point_to_s2cell(Lon, Lat, 16) == other_cell// ✅ FIX — wrap both sides in tostring()| where tostring(geo_point_to_s2cell(Lon, Lat, 16)) == tostring(other_cell)
This is most common with computed values from geo_point_to_s2cell() and strcat() comparisons. When in doubt, cast with tostring().
9. Advanced Functions
KQL handles these natively — no need for Python:
Vector similarity
// try it! — cosine similarity on Iris feature vectorslet target = pack_array(5.1, 3.5, 1.4, 0.2);Iris| extend Vec = pack_array(SepalLength, SepalWidth, PetalLength, PetalWidth)| extend sim = series_cosine_similarity(Vec, target)| top 5 by sim desc
Geo operations
// Distance between two points (meters)StormEvents | extend dist = geo_distance_2points(BeginLon, BeginLat, EndLon, EndLat)// Spatial bucketing for joinsStormEvents | extend cell = geo_point_to_s2cell(BeginLon, BeginLat, 8)
Graph queries
Use the kusto_graph_query MCP tool for graph traversal. It automatically handles graph snapshots when available:
// Persistent graph model — query the latest snapshotgraph("Simple")| graph-match (src)-[e*1..5]->(dst)where src.name == "Alice"project src.name, dst.name, path_length = array_length(e)// Transient graph — build inline with make-graphSimpleGraph_Edges| make-graph source --> target with SimpleGraph_Nodes on id| graph-match (src)-[e*1..5]->(dst)where src.name == "Alice"project src.name, dst.name, path_length = array_length(e)
Time series
// Create a time series and detect anomaliesStormEvents| make-series count() default=0 on StartTime step 1d| extend anomalies = series_decompose_anomalies(count_)
For detailed examples and patterns, consult references/advanced-patterns.md.
10. Self-Correction Lookup Table
When you encounter an error, look it up here before retrying:
| Error message contains | Likely cause | Fix | |||
|---|---|---|---|---|---|
is of a 'dynamic' type | Dynamic column in by/on/order by | Wrap in tostring()/tolong() | |||
Only equality is allowed | Range predicate in join condition | Pre-bucket with S2/H3 cells or bin() | |||
extractall(): matching groups | Missing () in regex | Add (): @"(\w+)" not @"\w+" | |||
row set must be serialized | Window function on unsorted data | Add `\ | serialize or \ | order by` before it | |
Cannot compare values of types string and string | Computed string comparison | Add tostring() on both sides | |||
Failed to resolve column named 'X' | Wrong column name or wrong table | Use kusto_describe_database_entity to check column names | |||
E_LOW_MEMORY_CONDITION | Query touched too much data | Add `\ | where` filters, reduce time range, break into steps | ||
E_RUNAWAY_QUERY | Join/aggregation produced too many rows | Check cardinality before joining; add pre-filters | |||
for each left attribute, right attribute | Join on clause incomplete | Use explicit form: on $left.X == $right.Y | |||
needs to be bracketed | Reserved word used as identifier | Use ['keyword'] syntax | |||
plugin doesn't exist | Unavailable plugin on this cluster | Fall back to equivalent function or Python | |||
Expected string literal in datetime() | Bare integer in datetime literal | Use datetime(2024-01-01) not datetime(2024) | |||
Unexpected token after by | Complex expression in summarize by-clause | extend the expression first, then summarize by the column | |||
not recognized / unknown operator | Operator not available on this engine | Check operator support; try equivalent (order by = sort by) |
11. Datetime Pitfalls
Datetime literals are a common source of errors. A wrong literal format can cascade into completely different approaches instead of fixing the small issue.
Literal format
// ❌ WRONG — bare year is not a valid datetime| where StartTime > datetime(2007)// ✅ RIGHT — always use full date format| where StartTime > datetime(2007-01-01)
Filtering by year, month, or hour
// ❌ WRONG — comparing datetime column to integer| where StartTime == 2007// ✅ RIGHT — use datetime_part() to extract components| where datetime_part("year", StartTime) == 2007// ✅ ALSO RIGHT — use between with datetime range| where StartTime between (datetime(2007-01-01) .. datetime(2007-12-31T23:59:59))
Time bucketing in summarize
// This works, but can be harder to read and reuse in complex queries| summarize count() by startofmonth(StartTime)// Clearer — extend first, then summarize by the computed column| extend Month = startofmonth(StartTime)| summarize count() by Month| order by Month asc
Useful datetime functions
| Function | Purpose | Example | |
|---|---|---|---|
bin(ts, 1h) | Round down to bucket boundary | bin(Timestamp, 1d) | |
startofmonth(ts) | First day of month | startofmonth(Timestamp) | |
datetime_part("hour", ts) | Extract component | datetime_part("year", Timestamp) | |
format_datetime(ts, fmt) | Format as string | format_datetime(Timestamp, "yyyy-MM") | |
ago(1d) | Relative time | where Timestamp > ago(1d) | |
between(a .. b) | Range filter (inclusive) | where Timestamp between (datetime(2024-01-01) .. datetime(2024-01-31T23:59:59)) | |
todatetime(str) | Parse string → datetime | todatetime("2024-01-15T10:30:00Z") | |
totimespan(str) | Parse string → timespan | totimespan("01:30:00") |
12. Operator Naming & Equality
KQL has subtle differences from SQL syntax.
Naming conventions
| Entity | Convention | Example | |
|---|---|---|---|
| Tables | UpperCamelCase | StormEvents, NetworkLogs | |
| Columns | UpperCamelCase | StartTime, EventType | |
Variables (let) | snake_case | let filtered_events = ... | |
| Built-in functions | snake_case | format_bytes(), geo_distance_2points() | |
| Stored functions | UpperCamelCase | .create function GetTopUsers |
Equality operators
// In where clauses, == is case-sensitive, =~ is case-insensitive| where State == "TEXAS" // exact match| where State =~ "texas" // case-insensitive| where State != "TEXAS" // not equal| where State !~ "texas" // case-insensitive not equal// In joins, use == only| join kind=inner other on $left.Key == $right.Key
sort vs order
Both sort by and order by work identically in KQL — they are aliases. Use whichever you prefer, but be consistent.
contains vs has
// contains: substring match (slower)| where Message contains "error" // finds "MyErrorHandler" too// has: term/word match (faster, uses index)| where Message has "error" // matches word boundaries only// For exact prefix/suffix| where Message startswith "Error:"| where Message endswith ".log"
13. Error Recovery Strategy
When a first KQL query fails, the temptation is to abandon the entire approach and try something completely different. The correct response is almost always to fix the specific error, not change strategy.
The pattern to avoid
Query 1: extract(@"pattern", 1, col) → Parse errorQuery 2: todynamic(col) → Different errorQuery 3: parse_json(col) → Another errorQuery 4: Python script → Works but 10x tokens
The correct pattern
Query 1: extract(@"pattern", 1, col) → Parse error (bad escaping)Query 2: extract(@"pattern", 1, col) → Fix the specific escaping issue → Success
Rules for error recovery:
- Read the error message carefully — it almost always tells you exactly what's wrong
- Fix the specific syntax/escaping issue, don't switch approaches
- Use the self-correction table (Section 10) to map errors to fixes
- Only switch approaches after 2 failed fixes of the same query
- The
parseoperator is often simpler thanextract()for structured text:
// Instead of complex regex:// extract(@"User '([^']+)' sent (\d+) bytes", 1, Message)// Use parse for structured extraction:| parse Message with * "User '" Username "' sent " ByteCount " bytes" *
14. Query Writing Checklist
Before running any KQL query, mentally check:
- Pre-filtered? Large tables have a
| wherebefore any| summarize - Result bounded? Exploratory queries end with
| take Nor| top N - Dynamic columns cast? Any dynamic column in
by/on/order byis wrapped - Regex has groups?
extract_allpatterns have()around what you want to capture - Join cardinality safe? Both sides checked with
dcount()before joining - Needed columns only? Wide tables get
| projectto drop unneeded columns - Datetime literals valid? Using
datetime(2024-01-01)notdatetime(2024)or bare integers - Complex by-expressions? Use
| extendfirst, then| summarize bythe computed column - Error recovery plan? If a query fails, fix the specific error — don't change strategy
- Right tool? Use
kusto_queryfor queries,kusto_commandfor management commands,kusto_sample_entityfor quick previews - Checked the plan? For expensive queries (joins, large tables), use
kusto_show_queryplanto compare approaches before executing - Cluster healthy? Before heavy workloads, use
kusto_diagnosticsto check capacity and permissions
15. Diagnostics & Query Optimization
Two tools let you look before you leap: kusto_show_queryplan for query cost estimation and kusto_diagnostics for cluster health.
Query plan analysis
kusto_show_queryplan plans a query without executing it. Returns:
| Field | What it tells you | |
|---|---|---|
stats.PlanSize | Overall plan complexity (bytes). Compare two approaches — higher = heavier. | |
stats.RelopSize | Logical operator tree size. Grows with operator count. | |
execution_hints.estimated_rows | Total rows the engine expects to process. The strongest cost signal. | |
execution_hints.shard_scans | Per-shard {total_rows, has_selection}. More shards = more parallel scans. | |
execution_hints.shard_scans[].has_selection | true = a filter narrows the scan (extent pruning). false = full scan. | |
execution_hints.concurrency | Parallelism hint. -1 = auto (precomputed), 1 = parallel partitions. | |
relop_tree | Logical operator tree. Look for ConstantDataTable (precomputed) or InnerEquiJoin (expensive). | |
error | Semantic errors caught without executing. Validates column names and table references. |
kusto_show_queryplan(query="Trips | where pickup_datetime > datetime(2014-01-01) | summarize count() by vendor_id",cluster_uri="https://help.kusto.windows.net",database="Samples")
What queryplan CAN detect
- Expensive joins: Self-joins double
estimated_rowsandshard_scanscount. - Materialize overhead:
materialize()+ join has higher PlanSize than single-pass multi-aggregation. - Precomputed results: Bare
| countreturnsestimated_rows=1andConstantDataTablein the tree — no scan. - Column/table typos: Returns an
errorfield with the semantic error message. - Filter presence:
has_selection=truevsfalseshows whether awhereclause narrows the scan.
What queryplan CANNOT detect
- Data volume differences: Full scan vs filtered scan on the same table show similar
estimated_rows(both report table size). Usehas_selectioninstead —falsemeans full scan. - Type mismatches: KQL auto-casts, so
where fare_amount == "expensive"plans successfully (returns 0 rows at runtime). - Ambiguous join columns: KQL resolves these at plan time without error.
Comparing two query approaches
The pattern: plan both, compare estimated_rows and shard_scans.
# Plan A: direct summarizeplan_a = kusto_show_queryplan(query="Trips | summarize count() by vendor_id, payment_type", ...)# Plan B: self-join (looks "clever" but is worse)plan_b = kusto_show_queryplan(query="Trips | as T | join kind=inner (T | summarize by vendor_id) on vendor_id | summarize count() by payment_type", ...)# Compare: plan_b.execution_hints.estimated_rows will be 2x plan_a's → pick A
Regression thresholds: Flag a rewrite as a regression if estimated_rows increases >50% or shard_scans count increases >30%.
Cluster diagnostics
kusto_diagnostics runs 7 commands (best-effort — permission failures don't block others):
| Section | What it tells you | |
|---|---|---|
capacity | Resource slots: Queries, Ingestions, Merges, etc. Each has Total/Consumed/Remaining. | |
cluster | Node count, cores, RAM (total and available), product version. | |
principal_roles | Your permissions per database (Viewer, Admin, etc.). | |
diagnostics | Cluster health: IsHealthy, merge/ingestion load factors, extent counts. | |
workload_groups | Configured workload policies (requires admin). | |
rowstores | Rowstore memory state (requires admin). | |
ingestion_failures | Failed ingestions in last 24h. |
kusto_diagnostics(cluster_uri="https://help.kusto.windows.net",database="Samples")
When to use diagnostics
- Before heavy workloads: Check
capacity— ifQueries.Remainingis low, wait or batch. - Batching decisions:
batch_size = min(remaining_slots / 2, num_tasks)— leave 50% headroom. - Permission checks:
principal_rolestells you what you can do before you try and fail. - Debugging ingestion issues:
ingestion_failuressurfaces errors from the last 24h.