Browse cheat sheets · Microsoft Fabric

Technology: Microsoft FabricCategory: Query LanguageDifficulty: IntermediateUpdated

KQL for Microsoft Fabric Cheat Sheet

The Kusto Query Language (KQL) a Microsoft Fabric data engineer uses most in Eventhouse and KQL querysets: filter, shape, sort, summarize, time windows with bin(), joins, nulls and dynamic fields, with a SQL-to-KQL mapping and seven practice exercises.

On this page
  1. Problem
  2. Quick answer
  3. From SQL to KQL
  4. Code / commands
  5. 0. Create a practice dataset
  6. 1. Filter with where
  7. 2. Shape columns: project, project-away, extend
  8. 3. Sort and limit: sort, order, top, take
  9. 4. Aggregate with summarize
  10. 5. Time: now(), ago(), datetime literals
  11. 6. Time windows with bin()
  12. 7. Joins: lookup and join
  13. 8. Conditional logic: iff() and case()
  14. 9. Missing values
  15. 10. Dynamic (JSON) fields and parsing
  16. 11. Latest value per key with arg_max()
  17. 12. Reuse with let
  18. Exercises
  19. Exercise 1: the last 30 minutes
  20. Exercise 2: failures by service
  21. Exercise 3: 5-minute windows
  22. Exercise 4: top failing devices and services
  23. Exercise 5: join to reference data
  24. Exercise 6: a calculated field
  25. Exercise 7: diagnose the wrong time window
  26. What the result means
  27. What to look for
  28. Production notes
  29. Common mistakes
  30. 1. Writing KQL in SQL order
  31. 2. Expecting sort by to be ascending
  32. 3. Trusting the default join
  33. 4. Treating ago(1d) as “today”
  34. 5. Testing strings with isnull()
  35. 6. Comparing a dynamic value without converting it
  36. Next step
  37. Related deep dives

Problem

In Microsoft Fabric, KQL is the language of Real-Time Intelligence: you use it to query an Eventhouse (its KQL databases), build Real-Time Dashboards, define Activator conditions, and read workspace monitoring logs.

If you know SQL, KQL feels familiar for about ten minutes. Then the differences start to matter:

  • the query reads top to bottom as a pipeline of operators, starting from the table;
  • string comparison with == is case-sensitive;
  • sort by sorts descending unless you say otherwise;
  • the default join removes duplicate keys on the left side;
  • almost everything is about time: ago(), bin() and time windows.

This sheet covers the KQL a Fabric data engineer needs most, not the whole language. Every example uses the same small dataset, so the queries build on each other.

Quick answer

Filter rows            → where
Pick / drop columns    → project, project-away, project-rename
Add a computed column  → extend
Sort / limit           → sort by (desc by default), top N by, take
Aggregate              → summarize Agg = func() by Column
Time buckets           → summarize ... by bin(Timestamp, 5m)
Relative time          → ago(30m), now(), startofday(now())
Enrich with reference  → lookup (small dimension) or join kind=...
Conditional values     → iff(), case()
Missing values         → isnull(), isempty(), coalesce()
JSON / dynamic fields  → Properties.field, tostring(), parse_json()
Chart it               → render timechart

The canonical shape of a real-time query:

TelemetryEvents
| where Timestamp > ago(1h)
| where Status == "Failed"
| summarize Failures = count()
    by bin(Timestamp, 5m), Service
| order by Timestamp desc

Read it as: take the table → keep the last hour → keep failures → count per 5-minute bucket and service → newest first.

From SQL to KQL

SQL idea KQL Where the mental model breaks
FROM t t (first line) The table comes first; each `
SELECT a, b project a, b project can also rename and compute: project Svc = Service
SELECT *, x AS y extend y = x extend keeps every column and adds new ones
WHERE where == is case-sensitive; use =~ for case-insensitive equality
GROUP BY + aggregates summarize ... by Aggregates and grouping columns live in one operator
HAVING where after summarize There is no HAVING; just filter the summarized rows
ORDER BY sort by / order by Default is descending, the opposite of SQL
TOP n ... ORDER BY top n by col Also descending by default
TOP n without order take n Arbitrary rows; fine for a peek, never for a result
JOIN join kind=... Default kind is innerunique, which deduplicates left-side keys; always state the kind
LEFT JOIN to a dimension lookup Built for enriching a large table with a small one
NULL isnull(), isempty() string columns are never null, only empty; numbers and datetimes can be null
CASE WHEN case() or iff() Functions inside extend, not a statement
DATEADD(hour, -1, GETUTCDATE()) ago(1h) Timestamps are UTC; timespans are literals such as 5m, 1h, 7d

Two habits help most: put the time filter first, and read every query top to bottom as a pipeline.

Code / commands

0. Create a practice dataset

Run these in a KQL database in an Eventhouse (each command separately). They create a telemetry table with about two days of synthetic events relative to now, plus a small reference table. The Real-Time Intelligence hands-on guide in Related deep dives loads the same shape from an eventstream instead.

.create table TelemetryEvents (
    Timestamp: datetime,
    DeviceId: string,
    Service: string,
    Region: string,
    EventType: string,
    DurationMs: long,
    Status: string,
    Value: real,
    Properties: dynamic
)
.set-or-append TelemetryEvents <|
range i from 1 to 20000 step 1
| extend Timestamp = now() - i * 9s
| extend Service = tostring(dynamic(["checkout", "payments", "search", "catalog", "auth"])[i % 5])
| extend Region = tostring(dynamic(["westeurope", "eastus", "southeastasia"])[toint(rand(3))])
| extend DeviceId = strcat("dev-", tostring(toint(rand(40))))
| extend Failed = rand() < iff(Service == "payments", 0.12, 0.03)
| extend Status = iff(Failed, "Failed", "Succeeded")
| extend EventType = iff(Failed, "error", "request")
| extend DurationMs = tolong(40 + rand(300) + iff(Service == "search", 400, 0))
| extend Value = round(rand() * 100, 2)
| extend Properties = bag_pack("version", iff(i % 7 == 0, "1.5.0", "1.4.2"), "tier", iff(i % 3 == 0, "gold", ""))
| project Timestamp, DeviceId, Service, Region, EventType, DurationMs, Status, Value, Properties
.set-or-append ServiceCatalog <|
datatable(Service: string, Team: string, Tier: string, SloMs: long)
[
    "checkout", "Commerce", "Tier 1", 400,
    "payments", "Commerce", "Tier 1", 400,
    "search",   "Discovery", "Tier 2", 500,
    "catalog",  "Discovery", "Tier 2", 600
]

auth is deliberately missing from ServiceCatalog, so you can see what each join kind does with an unmatched key.

1. Filter with where

TelemetryEvents
| where Timestamp > ago(30m)
| where Service == "payments" and Status == "Failed"
TelemetryEvents
| where Timestamp between (ago(2h) .. ago(1h))
| where Region in ("westeurope", "eastus")
| where Service =~ "PAYMENTS"
  • == is case-sensitive, =~ isn’t. in and in~ work the same way.
  • between includes both ends.
  • For text search, has matches whole terms and is faster than contains, which matches any substring.

2. Shape columns: project, project-away, extend

TelemetryEvents
| where Timestamp > ago(1h)
| project Timestamp, Service, DurationMs, Status
TelemetryEvents
| where Timestamp > ago(1h)
| project-away Properties, Value
| project-rename Svc = Service
TelemetryEvents
| where Timestamp > ago(1h)
| extend DurationSec = DurationMs / 1000.0
| extend IsSlow = DurationMs > 500

Dividing a long by 1000 gives an integer; divide by 1000.0 to keep decimals.

3. Sort and limit: sort, order, top, take

TelemetryEvents
| where Timestamp > ago(15m)
| sort by Timestamp desc, DurationMs asc
TelemetryEvents
| where Timestamp > ago(1h)
| top 10 by DurationMs
TelemetryEvents
| take 20

order by is the same operator as sort by. Both default to descending, and so does top. take returns arbitrary rows: use it to look at the data, never to answer a question.

4. Aggregate with summarize

TelemetryEvents
| where Timestamp > ago(1h)
| summarize
    Events = count(),
    Failures = countif(Status == "Failed"),
    Devices = dcount(DeviceId),
    AvgMs = avg(DurationMs),
    P95Ms = percentile(DurationMs, 95),
    MaxMs = max(DurationMs)
    by Service
| extend FailureRate = round(100.0 * Failures / Events, 2)
| order by FailureRate desc
  • summarize without by returns one row; with by, one row per group.
  • dcount() is an estimate, which is fine for monitoring.
  • Filter aggregated results with where after summarize (KQL has no HAVING).
  • Name every aggregate (Failures = ...); otherwise you get columns like count_.

5. Time: now(), ago(), datetime literals

TelemetryEvents
| where Timestamp >= startofday(now())
| summarize Events = count() by Service
TelemetryEvents
| where Timestamp between (datetime(2026-11-09 08:00) .. datetime(2026-11-09 09:00))
| summarize Events = count()
TelemetryEvents
| where Timestamp > ago(1h)
| extend LocalTime = datetime_utc_to_local(Timestamp, "Europe/Madrid")
| project Timestamp, LocalTime, Service
  • ago(1d) is a rolling 24 hours; startofday(now()) is since midnight UTC.
  • Timespan literals: 30s, 5m, 1h, 7d.
  • Datetimes are stored in UTC. Convert for display, not for filtering.

6. Time windows with bin()

TelemetryEvents
| where Timestamp > ago(3h)
| summarize
    Requests = count(),
    P95Ms = percentile(DurationMs, 95)
    by bin(Timestamp, 5m), Service
| render timechart
TelemetryEvents
| where Timestamp > ago(3h)
| make-series Failures = countif(Status == "Failed") default = 0
    on Timestamp from ago(3h) to now() step 5m
    by Service
| render timechart
  • bin(Timestamp, 5m) rounds each timestamp down to a fixed, non-overlapping 5-minute bucket: a tumbling window.
  • summarize ... by bin() returns no row for a bucket with no events. make-series fills gaps with a default, which matters for charts and alert thresholds.
  • Pick the bin size for the question: too small and the chart is noise, too large and a 2-minute outage disappears.

7. Joins: lookup and join

Enrich events with reference data. lookup is designed for a large table plus a small dimension, and keeps unmatched rows (left outer) by default:

TelemetryEvents
| where Timestamp > ago(1h)
| lookup kind=leftouter ServiceCatalog on Service
| summarize Events = count() by Service, Team, Tier

The same with join, stating the kind explicitly:

TelemetryEvents
| where Timestamp > ago(1h)
| summarize P95Ms = percentile(DurationMs, 95) by Service
| join kind=inner ServiceCatalog on Service
| where P95Ms > SloMs
| project Service, Team, P95Ms, SloMs
Kind Keeps Watch out
innerunique (default) Matches, after deduplicating left keys Silently drops duplicate left keys; surprises SQL users
inner Every matching pair Duplicates multiply rows, as in SQL
leftouter All left rows, matched or not Unmatched right columns are empty or null
leftanti Left rows with no match Useful to find events for services missing from the catalog
rightouter, fullouter, leftsemi, rightanti As their names say Less common in monitoring queries

Summarize before joining when you can: joining one row per service is cheaper than joining every event.

8. Conditional logic: iff() and case()

TelemetryEvents
| where Timestamp > ago(1h)
| extend LatencyBand = case(
    DurationMs < 200, "fast",
    DurationMs < 600, "ok",
    "slow")
| extend IsError = iff(Status == "Failed", 1, 0)
| summarize Events = count(), Errors = sum(IsError) by Service, LatencyBand

case() evaluates conditions in order and needs a final default value.

9. Missing values

TelemetryEvents
| where Timestamp > ago(1h)
| extend Tier = tostring(Properties.tier)
| summarize
    NoTier = countif(isempty(Tier)),
    NoValue = countif(isnull(Value))
    by Service
TelemetryEvents
| where Timestamp > ago(1h)
| extend Tier = tostring(Properties.tier)
| extend Tier = iff(isempty(Tier), "standard", Tier)
| extend Value = coalesce(Value, 0.0)
  • string values are never null in KQL, only empty: test strings with isempty() / isnotempty().
  • real, long, datetime and dynamic values can be null: test them with isnull() / isnotnull().
  • coalesce() returns the first non-null argument.

10. Dynamic (JSON) fields and parsing

TelemetryEvents
| where Timestamp > ago(1h)
| extend Version = tostring(Properties.version)
| summarize Failures = countif(Status == "Failed"), Events = count() by Version
datatable(Raw: string)
[
    '{"service":"auth","durationMs":812,"status":"Failed"}'
]
| extend Doc = parse_json(Raw)
| project
    Service = tostring(Doc.service),
    DurationMs = tolong(Doc.durationMs),
    Status = tostring(Doc.status)

Values read from a dynamic column are dynamic. Convert them (tostring(), tolong(), todatetime()) before you compare, group or join on them.

11. Latest value per key with arg_max()

TelemetryEvents
| where Timestamp > ago(1h)
| summarize arg_max(Timestamp, Status, DurationMs) by DeviceId

arg_max(Timestamp, *) returns the whole latest row for each key. It is also the usual way to remove duplicate events that arrive more than once.

12. Reuse with let

let window = 30m;
let failing =
    TelemetryEvents
    | where Timestamp > ago(window)
    | summarize Failures = countif(Status == "Failed") by Service
    | where Failures > 0;
failing
| lookup kind=leftouter ServiceCatalog on Service
| order by Failures desc

A let statement ends with ;. The query after the last let produces the result.

Exercises

Use the practice dataset from step 0. Write each query before you look at the suggested one.

Exercise 1: the last 30 minutes

Goal: list events from the last 30 minutes, newest first, with only the columns an on-call engineer needs.

TelemetryEvents
| where Timestamp > ago(30m)
| project Timestamp, Service, Region, Status, DurationMs
| sort by Timestamp desc

Interpretation: about 200 rows (one event every 9 seconds). If you see none, the dataset is older than 30 minutes: rerun the .set-or-append command.

Variation: add | take 5 before and after sort. Only one of them reliably shows the five newest events. Why?

Exercise 2: failures by service

Goal: count failures per service in the last hour.

TelemetryEvents
| where Timestamp > ago(1h)
| where Status == "Failed"
| summarize Failures = count() by Service
| order by Failures desc

Interpretation: payments should lead clearly, because the generator fails it about four times more often.

Variation: remove the second where and use countif(Status == "Failed") with count() to show a failure rate instead of a count. Which number would you put on a dashboard?

Exercise 3: 5-minute windows

Goal: show the number of requests per service in 5-minute buckets over the last 2 hours.

TelemetryEvents
| where Timestamp > ago(2h)
| summarize Requests = count() by bin(Timestamp, 5m), Service
| render timechart

Interpretation: fairly flat lines around 6–7 requests per service per bucket. This is a tumbling window: each event is counted in exactly one bucket.

Variation: change 5m to 1m and then 1h. Describe what each size hides.

Exercise 4: top failing devices and services

Goal: find the five device and service pairs with the most failures in the last 6 hours.

TelemetryEvents
| where Timestamp > ago(6h)
| where Status == "Failed"
| summarize Failures = count() by DeviceId, Service
| top 5 by Failures

Interpretation: expect mostly payments rows. In real telemetry, one device dominating the list usually points at the device, not the service.

Variation: return the top 5 by failure rate instead, only for pairs with at least 20 events. Where does the minimum belong in the pipeline?

Exercise 5: join to reference data

Goal: compare each service’s p95 latency with its SLO from ServiceCatalog.

TelemetryEvents
| where Timestamp > ago(1h)
| summarize P95Ms = percentile(DurationMs, 95) by Service
| lookup kind=leftouter ServiceCatalog on Service
| extend OverSlo = P95Ms > SloMs
| project Service, Team, P95Ms, SloMs, OverSlo

Interpretation: only search is over its SLO: the generator adds 400 ms to every search request, so its p95 lands around 700 ms against a 500 ms target. The other services stay near 330 ms. auth appears with an empty team and SLO, because it isn’t in the catalog.

Variation: use join kind=inner and then join kind=leftanti. Which one tells you the catalog is incomplete?

Exercise 6: a calculated field

Goal: classify every request as fast, ok or slow, and count each band per service.

TelemetryEvents
| where Timestamp > ago(1h)
| extend LatencyBand = case(DurationMs < 200, "fast", DurationMs < 600, "ok", "slow")
| summarize Events = count() by Service, LatencyBand
| order by Service asc, LatencyBand asc

Interpretation: search has no fast requests and many slow ones; the other services are split between fast and ok.

Variation: move the band thresholds into let statements, then reuse them in a second query.

Exercise 7: diagnose the wrong time window

Goal: a colleague wants “today’s failures” for a team in Madrid and wrote:

TelemetryEvents
| where Timestamp > ago(1d)
| where Status == "Failed"
| summarize Failures = count()

Interpretation: this counts the last 24 hours, not today, and “today” in Madrid doesn’t start at midnight UTC. In the morning it includes most of yesterday; in the evening it’s close to right, so the bug comes and goes.

A version that means “since midnight in Madrid”:

let madridMidnightUtc = datetime_local_to_utc(startofday(datetime_utc_to_local(now(), "Europe/Madrid")), "Europe/Madrid");
TelemetryEvents
| where Timestamp >= madridMidnightUtc
| where Status == "Failed"
| summarize Failures = count()

Variation: write the same filter for “yesterday in Madrid”, using a start and an end. Check both boundaries: does between include an event at exactly midnight on both sides?

What the result means

  • Zero rows: check the time filter first (ago() against data age, UTC vs local), then case-sensitive string comparisons.
  • More rows than expected after a join: kind=inner multiplies duplicates; innerunique (the default) hides them. State the kind and check key uniqueness on the reference side.
  • Missing time buckets: summarize ... by bin() doesn’t create buckets with no data. Use make-series when gaps must show as zero.
  • A metric jumps when the bin size changes: the bucket is part of the metric. A count per 1 minute and a count per 5 minutes are different numbers.
  • dcount() differs slightly from an exact count: it’s an estimate by design.

What to look for

  • A where Timestamp > ago(...) near the top of every query on a large table.
  • An explicit kind= on every join.
  • Comparisons on dynamic fields that aren’t converted with tostring() or tolong().
  • take used to produce an answer rather than to peek at data.
  • contains on large tables where has would do.
  • Bin sizes chosen to match the alert or dashboard question, not by habit.

Production notes

  • Filter on time first. Eventhouse data is organized by ingestion time, and time filters are what keep queries fast and cheap.
  • Summarize before joining and keep the smaller table on the right of lookup.
  • Name everything (Failures = count()): dashboards, Activator rules and downstream queries depend on stable column names.
  • Keep UTC in storage; convert to local time only for display.
  • Save reusable logic as functions in the KQL database instead of copying long queries between dashboards.
  • Check the schema before trusting a query written for another environment: TelemetryEvents | getschema.

Common mistakes

1. Writing KQL in SQL order

project before where works, but long queries become hard to read and easy to get wrong. Filter, then shape, then aggregate, then sort.

2. Expecting sort by to be ascending

KQL sorts descending by default. Write asc or desc every time.

3. Trusting the default join

The default innerunique deduplicates the left side’s keys. Use kind=inner, kind=leftouter or lookup deliberately.

4. Treating ago(1d) as “today”

It’s a rolling 24 hours in UTC. Use startofday() and an explicit time zone when the question is a calendar day.

5. Testing strings with isnull()

Strings are never null in KQL. Use isempty().

6. Comparing a dynamic value without converting it

Properties.version == "1.5.0" compares a dynamic value. tostring(Properties.version) == "1.5.0" says what you mean.

Next step

Practise these queries on live events. The Real-Time Intelligence hands-on guide below sends the same TelemetryEvents shape through an eventstream into an Eventhouse, then queries, windows and breaks it.

For the full language, the KQL reference on Microsoft Learn is the source of truth. This sheet was checked against it in November 2026, and every query here was validated with Microsoft’s Kusto language parser against the dataset’s schema.

← All cheat sheets