Querying with KQL | Guided by A Guide to Cloud

What is KQL?

Simple explanation

Think of KQL as a language built for timestamps.

SQL was designed for tables of customers and orders. KQL was designed for streams of events — log entries, sensor readings, trade events, and application telemetry. Every KQL query starts with “take this stream of events and…” — it reads like a recipe, step by step, top to bottom.

In Fabric, KQL queries run against Eventhouse databases. If your data is time-stamped and append-heavy, KQL is your tool.

KQL basics: The pipe model

KQL queries flow top-to-bottom through a pipe (|):

TradeEvents
| where Timestamp > ago(24h)
| where TradeType == "Buy"
| summarize TotalVolume = sum(Quantity), TradeCount = count() by Symbol
| top 10 by TotalVolume desc

What’s happening line by line:

Start with the TradeEvents table
Filter to events in the last 24 hours
Filter to “Buy” trades only
Group by symbol, calculate total volume and count
Return the top 10 by volume

KQL vs SQL comparison

KQL reads top-to-bottom with pipes; SQL uses nested clauses
Operation	SQL	KQL
Select all columns	SELECT * FROM trades	trades
Filter rows	WHERE price > 100	\| where price > 100
Select columns	SELECT symbol, price FROM trades	trades \| project symbol, price
Count rows	SELECT COUNT(*) FROM trades	trades \| count
Group and aggregate	SELECT symbol, SUM(qty) FROM trades GROUP BY symbol	trades \| summarize sum(qty) by symbol
Sort results	ORDER BY price DESC	\| sort by price desc
Limit results	SELECT TOP 10	\| take 10 or \| top 10 by column
Time filter	WHERE timestamp > DATEADD(hour, -24, GETDATE())	\| where Timestamp > ago(24h)

Time-series operations

KQL’s strength is time-based analysis. The bin() function groups timestamps into intervals:

Aggregate by time window

// Trades per hour for the last 7 days
TradeEvents
| where Timestamp > ago(7d)
| summarize TradeCount = count(), AvgPrice = avg(Price) by bin(Timestamp, 1h)
| render timechart

Time-based comparison

// Compare today's trade volume to yesterday
TradeEvents
| where Timestamp > ago(2d)
| extend DayLabel = iff(Timestamp > ago(1d), "Today", "Yesterday")
| summarize TotalVolume = sum(Quantity) by DayLabel

Bucketed average

// 5-minute bucketed average of trade price
TradeEvents
| where Timestamp > ago(1h)
| summarize AvgPrice = avg(Price) by bin(Timestamp, 5m)
| render timechart

Common KQL operations for DP-600

Operation	KQL Syntax	Purpose
`where`	`	where column == value`
`project`	`	project col1, col2`
`extend`	`	extend NewCol = expression`
`summarize`	`	summarize agg() by groupcol`
`bin()`	`bin(Timestamp, 1h)`	Group timestamps into intervals
`ago()`	`ago(24h)`, `ago(7d)`	Relative time filter
`top`	`	top 10 by column desc`
`render`	`	render timechart`
`join`	`	join kind=inner (table2) on key`
`distinct`	`	distinct column`

Scenario: Raj monitors trade anomalies

Raj at Atlas Capital writes a KQL query to detect unusual trading activity:

TradeEvents
| where Timestamp > ago(1h)
| summarize TradeCount = count(), TotalValue = sum(Price * Quantity) by Trader
| where TradeCount > 100 or TotalValue > 10000000
| sort by TotalValue desc

This query surfaces traders who have made more than 100 trades in the last hour or traded more than $10 million — potential anomalies that the compliance team should investigate.

Exam tip: When KQL appears on the exam

The DP-600 exam does not expect deep KQL expertise (that is for KQL-specific certs). You need to:

Read a KQL query and understand what it does
Identify when KQL is the right tool (time-series, streaming data, Eventhouse)
Know basic operations — where, summarize, bin, ago, project
Know KQL vs SQL — choose the right language for the scenario

You will NOT be asked to write complex KQL from scratch.

Question

What is KQL's pipe model?

Click or press Enter to reveal answer

Answer

KQL queries flow top-to-bottom through pipe operators (|). Each step transforms the result of the previous step: start with a table, filter, project, summarize, sort. This makes queries readable as a sequence of transformations.

Click to flip back

Question

What does the bin() function do in KQL?

Click or press Enter to reveal answer

Answer

bin() groups continuous values (usually timestamps) into discrete intervals. bin(Timestamp, 1h) groups events into hourly buckets. Essential for time-series aggregation — 'events per hour', 'average per 5 minutes', etc.

Click to flip back

Question

What does ago() do in KQL?

Click or press Enter to reveal answer

Answer

ago() creates a relative timestamp. ago(24h) means '24 hours before now', ago(7d) means '7 days ago'. Used in where clauses for time-based filtering: | where Timestamp > ago(24h) returns the last 24 hours of data.

Click to flip back

Knowledge Check

Raj at Atlas Capital needs to find the total trade volume per hour for the last 7 days in his Eventhouse. Which KQL query is correct?

Knowledge Check

An exam question describes a scenario with application logs arriving at 10,000 events per second. The team needs to find error patterns over the last 24 hours, grouped by 15-minute intervals. Which approach is most appropriate?

🎬 Video coming soon

Next up: Querying with DAX — Data Analysis Expressions for analytical querying and measure evaluation.