KQL deep dive
The read-only query language of Real-Time Intelligence. This page is the query-first companion to Domain 2: the Kusto pipe model, filtering and aggregation, joins, time-series analysis, and the KQL database objects — functions, update policies and materialized views — that the exam tests.
How this page fits the exam
KQL shows up wherever the stem says Eventhouse, KQL database, telemetry / logs, time-series or real-time. As with PySpark, you mostly have to read a query and know what it returns — or pick the operator/object that fits a requirement. Everything here is grounded in the official Kusto Query Language and Fabric Real-Time Intelligence documentation. Pair it with Domain 2 (streaming) and Domain 3 (Eventhouse optimization).
- The KQL data model & pipe structure — tables, tabular vs scalar operators, order
- Filtering & shaping — where, project, extend, distinct, sort, top
- Aggregation with summarize — by, bin, arg_max, make_list
- Joins & unions — join kinds, union, lookup, mv-expand
- Time-series analysis — make-series, ago, bin, render
- Functions, update policies & materialized views
- Data policies — retention, caching, one logical copy
- Query performance best practices
- Scenario quick reference
The KQL data model & pipe structure
KQL is the query language of an Eventhouse and its KQL databases. It is read-only: you query and shape data, but ingestion and table management use separate management (control) commands, which start with a dot (.create, .ingest). A query starts from a table and flows through a pipeline of operators separated by the pipe character |; each step transforms the table and passes it on.
SensorReadings"] --> F["where
filter rows"] F --> A["summarize
aggregate"] A --> S["sort / top
order"] S --> P["project
select columns"]
SensorReadings // 1. table (data source)
| where Timestamp > ago(1h) // 2. filter early
| where DeviceId == "sensor-42"
| summarize AvgTemp = avg(Temperature) by bin(Timestamp, 5m) // 3. aggregate
| sort by Timestamp desc // 4. order
| project Timestamp, AvgTemp // 5. select columns| Concept | Meaning |
|---|---|
| Tabular operator | Takes a table in, returns a table out: where, summarize, project, join, sort, top |
| Scalar function | Operates on a single value: ago(), bin(), tostring(), strcat() |
| Aggregation function | Only valid inside summarize / make-series: count(), avg(), dcount(), arg_max() |
| Management command | Starts with . — creates tables, ingests data, sets policies; not part of a query |
let | Names a value, expression, or subquery for reuse within a query |
Coming from SQL
KQL reads top-to-bottom in execution order, the reverse of SQL's SELECT … FROM … WHERE. SELECT → project, WHERE → where, GROUP BY → summarize … by, HAVING → a where after the summarize, ORDER BY → sort by, TOP → top. The official SQL-to-KQL cheat sheet maps the rest.
Filtering & shaping
| Operator | What it does |
|---|---|
where | Keep rows matching a predicate — the single most important operator |
project | Choose columns, in order, and compute new ones |
project-away / project-keep | Drop / keep columns by name or pattern |
project-rename | Rename columns |
extend | Add a calculated column, keeping the existing ones |
distinct | Distinct combinations of the given columns |
take / limit | Return up to N rows (arbitrary) — great while developing |
sort by / order by | Order rows (default descending) |
top N by | sort + take in one — use it instead of both |
Sales
| where Timestamp > ago(30d) and Amount > 0 // datetime filter first
| extend Margin = Amount - Cost // add a computed column
| where Region has "north" // 'has' matches whole terms, fast
| top 10 by Margin desc // sort + take in one operator
| project Timestamp, Region, Amount, MarginCaveat — has beats contains, and =~ beats tolower()
has matches whole indexed terms and uses the index, so it is far faster than contains, which scans for a substring anywhere. For case-insensitive equality use Col =~ "value", never tolower(Col) == "value" — wrapping the column in a function defeats the index. Filtering on a raw column beats filtering on a calculated one.
Aggregation with summarize
summarize collapses the table into groups defined by the by clause and computes aggregates for each. It is the workhorse of KQL analytics.
SensorReadings
| where Timestamp > ago(7d)
| summarize AvgTemp = avg(Temperature),
MaxTemp = max(Temperature),
Readings = count(),
Devices = dcount(DeviceId) // distinct count
by bin(Timestamp, 1h), Building
| sort by Timestamp asc| Aggregation | Returns |
|---|---|
count() / countif(pred) | Row count / conditional count |
dcount(col) | Approximate distinct count (fast at scale) |
sum, avg, min, max | Standard numeric aggregates |
percentile(col, 95) | Percentiles / distributions |
arg_max(x, *) / arg_min(x, *) | The whole row where x is largest / smallest — the "latest record per key" idiom |
make_list(col) / make_set(col) | Collect values into an array / distinct-value array |
SensorReadings
| summarize arg_max(Timestamp, *) by DeviceId // newest full row per deviceCaveat — summarize drops everything not aggregated or grouped
The output of summarize contains only the by columns and the computed aggregates. Referencing a column you neither grouped by nor aggregated fails later in the pipeline. To keep the full row while collapsing to one per key, use arg_max(Timestamp, *) — the * carries all columns. Also: grouping by a dynamic column needs an explicit cast such as tostring(col).
Joins & unions
join merges rows from two tables on matching keys; union stacks their rows. lookup is a join optimised for enriching a fact table from a dimension.
// Join — note the $left / $right column references
Readings
| join kind=inner (Devices) on $left.DeviceId == $right.Id
// lookup — like a left join for dimension enrichment, fewer keystrokes
Readings
| lookup kind=leftouter (Devices) on DeviceId
// union — stack rows from several tables
union withsource=SourceTable Readings_2025, Readings_2026
| where Timestamp > ago(1d)
// mv-expand — turn each element of a dynamic array into its own row
Events
| mv-expand Tag = Tags| Join kind | Keeps |
|---|---|
innerunique (default) | Inner join after de-duplicating keys on the left — the surprising default |
inner | All matching pairs from both sides |
leftouter / rightouter / fullouter | All left / right / both rows, nulls where unmatched |
leftsemi / rightsemi | Rows from one side that have a match (a filter) |
leftanti / rightanti | Rows from one side with no match (an anti-filter) |
Caveat — the default join is innerunique, not inner
If you write join with no kind, KQL uses innerunique, which first removes duplicate keys from the left table — this can silently drop rows compared with a SQL inner join. When you need true inner-join semantics, say kind=inner explicitly. For performance, put the smaller table on the left, and prefer where Col in (…) over a leftsemi join when you are only filtering by a single column.
Time-series analysis
KQL is built for time-series. bin() inside summarize buckets rows into fixed intervals; make-series goes further, producing a regular, gap-filled array per partition that you can chart, smooth, and run anomaly detection on.
let start = ago(1d);
let stop = now();
SensorReadings
| make-series
AvgTemp = avg(Temperature) default=0
on Timestamp from start to stop step 5m
by DeviceId // one series per device
| render timechart| Tool | Use |
|---|---|
ago(1h), now() | Relative and current time — the basis of nearly every filter |
bin(Timestamp, 5m) | Round each timestamp down to the window start (tumbling buckets) |
summarize … by bin(...) | Aggregate into time buckets — omits empty buckets |
make-series … step … default= | Regular time series with empty buckets filled — ready to chart/analyse |
series_fir(), series_fit_line() | Moving averages, trend lines, anomaly detection over a series |
render timechart | Plot the result (in querysets and dashboards) |
Caveat — summarize by bin() vs make-series
Both bucket by time, but summarize by bin() produces a row per non-empty bucket — missing intervals simply don't appear. make-series produces a continuous array across the whole range and fills gaps with default (or series_fill_*), which is what trend lines, moving averages and anomaly detection need. If a question wants a gap-free series or a chart, the answer is make-series.
Functions, update policies & materialized views
Beyond ad-hoc queries, a KQL database holds reusable objects. Three matter for the exam because they map cleanly to the medallion architecture inside Real-Time Intelligence.
.create-or-alter function HotDevices(threshold: real) {
SensorReadings
| where Timestamp > ago(1h)
| summarize AvgTemp = avg(Temperature) by DeviceId
| where AvgTemp > threshold
}// Bronze → Silver: runs automatically as data lands in RawEvents
.alter table SilverEvents policy update
```[{
"IsEnabled": true,
"Source": "RawEvents",
"Query": "RawEvents | where isnotempty(DeviceId) | extend Temp = todouble(Value)",
"IsTransactional": true
}]```// Latest bike count per station — deduplicated and pre-aggregated
.create-or-alter materialized-view with (folder="Gold") LatestReading on table SensorReadings
{
SensorReadings
| summarize arg_max(Timestamp, *) by DeviceId
}| Update policy | Materialized view | |
|---|---|---|
| When it runs | At ingestion time, per batch, before data is queryable in the target | In the background, after ingestion — source rows are queryable immediately |
| Good for | Row-level transforms, enrichment, filtering, splitting a stream (bronze→silver) | Aggregations and deduplication (arg_max) kept always-fresh (gold) |
| Aggregations? | No — only within a single ingestion batch | Yes — this is its whole purpose (one summarize) |
| Query cost | Paid once at ingestion | Cheaper than aggregating the source table each query |
Caveat — pick the object by when the work must happen
If the requirement is "aggregate / deduplicate and keep it always up to date for fast reads", it's a materialized view (it always combines the materialized part with un-materialized deltas, so results are fresh). If it's "transform or enrich each record as it lands, before it's queryable", it's an update policy. A summarize that must stay current across all data can't be an update policy, because update policies only see one ingestion batch at a time.
Data policies
| Policy | Controls | Exam angle |
|---|---|---|
| Retention policy | How long data is kept before automatic deletion (default 3,650 days, min 1 day) | "Drop telemetry after N days" → shorten retention |
| Caching (hot) policy | How much recent data is kept on fast local SSD (hot cache) vs cold storage | Speed vs cost: a wider hot cache = faster queries, more capacity used |
| One logical copy | Exposes the KQL database to OneLake via a shortcut so other Fabric engines read it | "Query Eventhouse data from a Lakehouse/notebook" without copying |
Retention and caching are independent
Retention decides whether data still exists; caching decides how fast the data that exists can be queried. You can retain two years of data but keep only the last 30 days in hot cache — recent queries stay fast while older data is still available, just slower and cheaper to store.
Query performance best practices
KQL cost and speed are driven almost entirely by how much data each step has to scan. The official query best-practices reduce to a short list.
| Do | Why |
|---|---|
| Filter early, right after the table | Every later step processes less data |
Put the datetime filter first | Kusto indexes time; a time predicate can eliminate whole data shards |
| Order predicates by selectivity | Most-selective filters first; single-column filters before multi-column ones |
Use has, not contains | Term index vs full substring scan |
Use =~, not tolower() | Keeps the column indexable |
project only what you need, early | Trims columns before expensive steps |
Smaller table on the left of a join | Reduces the data that must be broadcast/shuffled |
materialize() a subquery used many times | Computes it once instead of per reference |
limit / take while developing | Avoids scanning huge datasets during exploration |
Caveat — a time filter reduces scan; limit does not
A where Timestamp > ago(1h) genuinely reduces the data scanned because of the datetime index. limit / take only cap the rows returned — the engine may still scan the whole table first. To make a query cheaper, tighten the time window and project fewer columns; don't rely on limit to save cost.
Scenario quick reference
| Scenario | Deciding constraint | Answer |
|---|---|---|
| Latest full record per device | Keep the whole row | summarize arg_max(Timestamp, *) by DeviceId |
| Count events per 5-minute bucket | Time bucketing | summarize count() by bin(Timestamp, 5m) |
| Gap-free series to chart or analyse | Fill empty buckets | make-series … step … default=0 |
| Distinct users at scale | Approximate distinct | dcount() |
| Join dropped rows unexpectedly | Default join kind | Specify kind=inner (default is innerunique) |
| Enrich facts from a dimension table | Left-join semantics | lookup |
| Transform each record as it lands | Ingestion-time | Update policy |
| Always-fresh aggregate for fast reads | Post-ingestion aggregation | Materialized view |
| Substring/term match is slow | Index usage | has over contains |
| Case-insensitive equality | Keep the index | Col =~ "value" |
| Delete telemetry after N days | Lifecycle | Retention policy |
| Query Eventhouse data from a notebook | No duplication | One logical copy (OneLake shortcut) |
Verify against the docs
KQL is stable, but Real-Time Intelligence features and object syntax evolve. Confirm details against the KQL quick reference, materialized views and Fabric Real-Time Intelligence before relying on them.