DP-900: how to understand and use analytical queries with KQL in Azure Data Explorer

I will teach the skill: understand and use analytical queries with Kusto Query Language (KQL) in Azure Data Explorer — a useful skill for DP-900 because it demonstrates how to explore large volumes of telemetry and log data in Azure. KQL is widely used in observability, IoT and analytics scenarios, and showing that you can build efficient queries and interpret them is relevant to understanding analytical workloads on the exam.

What you need to know

Azure Data Explorer is a service optimized for ingestion and querying of large amounts of time-series and log data. The language used to query that data is Kusto Query Language (KQL). KQL is declarative and pipeline-oriented: you transform a set of records through a sequence of operators, separated by a pipe (|). This approach makes it easy to read and compose complex transformations, because each operator receives a table and returns another.

Simple example: imagine a table called Telemetry with columns Timestamp, DeviceId, Temperature. To get the average temperature per hour per device:

Telemetry
| where Timestamp >= ago(24h)
| summarize AvgTemp = avg(Temperature) by bin(Timestamp, 1h), DeviceId
| order by Timestamp asc

The where operator filters, summarize aggregates, and bin groups by time intervals. In real environments, you may have, for example, 10 million rows per day in a telemetry table; therefore it's critical to limit the period and apply filters early to reduce the amount of data processed.

How it works

KQL processes data in stages: it queries the table, applies filters, projects columns, aggregates and sorts. The most important operators for DP-900 are:

• where: filters rows (use early for performance).
• project: selects/renames columns, reduces row width.
• summarize: aggregates (avg, count, sum, min, max) and is essential for metrics.
• bin: groups timestamps into regular intervals (1m, 1h, 1d).
• extend: creates calculated columns without removing existing ones.
• join: combines two tables (similar to SQL JOIN), useful to attach metadata.

Example with calculated columns, join and sorting:

let recent = Telemetry | where Timestamp >= ago(7d);
let devices = Devices | project DeviceId, Location, FirmwareVersion;
recent
| extend TempF = Temperature * 9/5 + 32
| where TempF > 80
| summarize Count = count(), AvgF = avg(TempF) by DeviceId, bin(Timestamp, 1d)
| join kind=leftouter devices on DeviceId
| order by AvgF desc

The let statement makes it easy to reuse subqueries; the join adds metadata such as location. On well-configured clusters you can process hundreds of thousands of events per second; in development scenarios it's common to test with one-hour or one-day windows to iterate faster.

In practice

Step-by-step to build a useful analytical query in Azure Data Explorer:

1. Identify the table and the needed columns (e.g.: Telemetry, Timestamp, DeviceId, Temperature).
2. Apply a time filter with where Timestamp >= ago(...) to reduce volume initially — for example, start with ago(1d) when testing and only expand to 30 days when the logic is correct.
3. Project only the columns you need: | project Timestamp, DeviceId, Temperature to reduce I/O and memory.
4. Create calculated columns with extend if you need to transform data (e.g.: convert units or extract parts of strings).
5. Aggregate with summarize using by and, for time series, bin to group into regular intervals.
6. Sort and limit the result with order by and take if needed for dashboards or debugging.

Complete example — detect devices with recent temperature spikes and list the top 50:

Telemetry
| where Timestamp >= ago(1d)
| summarize MaxTemp = max(Temperature) by DeviceId
| where MaxTemp > 75
| order by MaxTemp desc
| take 50

This pattern is useful to feed a dashboard that shows the 50 devices with the highest spikes in the last 24 hours and integrate with alerts.

Common mistakes

• Filtering too late: applying where only after heavy operations (join/aggregate) increases cost and time. Filter as early as possible to reduce processed data.
• Using summarize without the correct by: forgetting bin in time series yields aggregations by distinct timestamps and unhelpful results.
• Not limiting data for debugging: when testing, use take or short ranges to avoid reading excessive data and causing slow or costly queries.
• Doing joins without an appropriate key or without limiting the secondary table: poorly constructed joins can multiply results and increase cost.

How to practice

Practice in the Azure portal with an Azure Data Explorer cluster or use Azure's free experience (check cost policies). Create sample tables with 100k to 10M records to understand behavior under load. For DP-900 preparation, Microsoft provides an official free Practice Assessment — use it to gauge knowledge without resorting to real exam questions. Also consult Microsoft's official DP-900 study guide, which describes the skills measured and links to hands-on labs and KQL documentation.

In summary

• KQL is pipeline-oriented: chained operators transform data step by step; each operator produces a table that feeds the next.
• Filter early with where, aggregate with summarize and use bin for time series; these practices reduce cost and speed up queries.
• Avoid expensive operations without filters; test with small samples (take) and iterate with small windows before scaling to 30 days or more.
• Practice in Azure and use the official Practice Assessment and Microsoft's study guide — both free — to prepare for DP-900, and try sample materials with tens of thousands to millions of records to gain confidence in real scenarios.