3.4.4. KQL for Real-Time Processing
💡 First Principle: KQL (Kusto Query Language) is optimized for time-series analysis, excelling at aggregating and analyzing streaming data with minimal latency. It's the native language for Real-Time Intelligence.
Common KQL Patterns
// Filter recent events
TemperatureReadings
| where timestamp > ago(1h)
| where temperature > 100
// Aggregate by time bucket
TemperatureReadings
| summarize AvgTemp = avg(temperature) by bin(timestamp, 5m), deviceId
// Detect anomalies
TemperatureReadings
| summarize AvgTemp = avg(temperature), StdDev = stdev(temperature) by deviceId
| where AvgTemp > 100 or StdDev > 20
Update Policies for Ingest-Time Transformations
- Concept: Transform data automatically during ingestion
- Benefit: Data lands pre-transformed, reducing query complexity
- Use Case: Parsing JSON, calculating derived fields, filtering
- Setup: create the source table (raw ingestion) → create the target table with the transformation's output schema → run
.alter table Target policy update @'[{"IsEnabled": true, "Source": "Raw", "Query": "TransformRaw()"}]'(the query is usually a stored function) → ingest into the source and transformed rows appear in the target
Update Policy vs. Materialized View vs. Stored Function
| Feature | What it does | Pick it when |
|---|---|---|
| Update policy | Runs a query over each newly ingested batch in the source table and writes the results to a target table | Transform, filter, or enrich data at ingestion time; it runs per batch, so it can't aggregate across batches |
| Materialized view | Maintains an aggregation (summarize) over a source table; queries always return fresh results | Any aggregation kept up to date automatically, latest record per key (arg_max) |
| Stored function | Saved, reusable (optionally parameterized) query logic, evaluated against current data on every call | Share standard filters or logic across queries and users |
A materialized view is automatically disabled when a source-table change makes its query incompatible with the view's schema (for example, a retyped column, or arg_max(ts, *) over a changed schema); it stops updating until you fix it and re-enable it. Continuous export copies data out to external storage; it doesn't populate another KQL table.
KQL Operator Reference
| Operator / function | What it does | Note |
|---|---|---|
where | Keeps rows that match a predicate | Filter before a project that drops the filtered column |
project | Selects or renames columns | Like a SQL SELECT list |
extend | Adds a computed column to every row (row count unchanged) | summarize instead collapses rows into groups |
summarize ... by | One row per group with aggregates | count() output is named count_; countif(pred) counts matching rows |
top N by col desc | Sorts and keeps N rows | Use after summarize for "top 10 devices" |
take N | Returns N arbitrary rows, unordered | Quick preview only |
arg_min(ts, *) / arg_max(ts, *) | Whole row with the min/max value per group | First or latest event per device |
ago(1h) | Current time minus a timespan | where ts > ago(24h) |
parse_json(col) | Turns a JSON string into dynamic for .property access | Nested JSON fields |
parse col with 'User ' email ' logged in from ' ip | Extracts values between literal text | Structured log lines |
prev(col) / next(col) | Value from the previous/next row | Needs an ordered (serialized) row set, e.g. after order by |
make-series + series_fir() | Builds a time series, then applies a moving-average filter | Rolling/moving averages |
join | Combines two tables on a key | Like SQL JOIN |
Queries read left to right: each | passes the previous result to the next operator, instead of SQL's inside-out nested subqueries.
⚠️ Exam Trap: KQL databases use KQL, not T-SQL. T-SQL is for the Fabric Data Warehouse. Questions about "Eventhouse transformations" or "KQL database queries" require KQL syntax, not SQL.
