In this post we are going to cover real-world manager scenarios in Azure Data Explorer. These are 7 real Azure Data Explorer and KQL interview questions for data engineers, asked the way they come up on a real project, and the last one is a scenario: in the final round, the hiring manager asks you to design a real-time analytics platform with Azure Data Explorer.
These are the questions a manager asks when something has gone wrong or a decision is due: double counts on a dashboard, a cost spike, which platform to pick, an app that times out, a move to Fabric, slow old data. Interviewers ask them because the answer shows how you investigate, not just what you know.
Part 10 of the Real Time Azure Data Explorer and KQL Interview Questions and Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 1: A dashboard shows double counts after a pipeline retry. How do you investigate and fix it?
- Question 2: The cluster cost doubled this month. Where do you look?
- Question 3: Someone asks whether to use Azure Data Explorer, Synapse or Databricks for a new log analytics workload. How do you decide?
- Question 4: A query works in the web UI but times out from an application. How do you troubleshoot it?
- Question 5: A team wants to move from Azure Data Explorer to an Eventhouse in Microsoft Fabric. How do you plan it?
- Question 6: Users complain that last month's data is slow to query but today's is fast. What is going on?
- Scenario 7: In the final round, the hiring manager asks you to design a real-time analytics platform with Azure Data Explorer. What is your design?
- The full script
The short answer
Double counts: prove them, find the extents, drop by drop-by tag and reload with ingestIfNotExists. Cost: compute, storage, networking and markup, and .show journal for what changed. Slow old data is the caching policy. A move to Eventhouse is phased with union views, and a platform design runs from data connections to update policies, materialized views, security and monitoring.
Let's create some sample data
Every query below runs on the Kusto emulator, Microsoft's Azure Data Explorer engine in a local container, in a database called shop. kql is a small helper that sends a query or a .kql file to the emulator and prints the result as a table. setup.kql creates small invented tables: an empty Orders, three ClickEvents, an empty Telemetry, three October Invoices, eight Logs rows from September and October, and four raw AppLogs lines. The loads use .ingest inline, which is for exploration and prototyping, not production. legacy_db.kql and legacy.kql create a second database, legacy, with two September invoices; it plays the old cluster in question 5. What only a cluster in Azure can show, such as billing, autoscale, cache tiers and data connections, is shown as code.
.create table Orders (OrderId:string, Customer:string, Amount:real)
.create table ClickEvents (Ts:datetime, Page:string)
.ingest inline into table ClickEvents <|
2026-10-20T08:00:00Z,home
2026-10-20T08:01:00Z,cart
2026-10-20T08:02:00Z,home
.create table Telemetry (DeviceId:string, Temp:real, Ts:datetime)
.create table Invoices (InvoiceId:long, Amount:real, InvoiceDate:datetime)
.ingest inline into table Invoices <|
3001,120,2026-10-05T09:00:00Z
3002,80,2026-10-12T09:00:00Z
3003,200,2026-10-19T09:00:00Z
.create table Logs (Timestamp:datetime, Service:string, Level:string)
.ingest inline into table Logs <|
2026-09-10T08:00:00Z,api,Info
2026-09-10T09:00:00Z,api,Error
2026-09-10T10:00:00Z,api,Info
2026-09-10T11:00:00Z,web,Info
2026-09-11T08:00:00Z,api,Info
2026-09-11T09:00:00Z,web,Error
2026-10-20T08:00:00Z,api,Info
2026-10-20T09:00:00Z,web,Info
.create table AppLogs (Line:string)
.ingest inline into table AppLogs <|
2026-10-20T08:01:00Z ERROR payments timeout calling gateway
2026-10-20T08:02:00Z INFO checkout order placed
2026-10-20T08:03:00Z ERROR payments card declined
2026-10-20T08:04:00Z ERROR checkout coupon invalid.create database legacy persist (@"/kustodata/dbs/legacy/md", @"/kustodata/dbs/legacy/data").create table Invoices (InvoiceId:long, Amount:real, InvoiceDate:datetime)
.ingest inline into table Invoices <|
2001,150,2026-09-08T09:00:00Z
2002,90,2026-09-22T09:00:00ZQuestion 1: A dashboard shows double counts after a pipeline retry. How do you investigate and fix it?
Prove it's duplicates, find the extents they came from, drop them by tag, and reload so a retry can't load the same batch twice.
Let's reproduce it. The pipeline retried, so load.kql loads the same batch file twice with .ingest into. That command stands in for the pipeline; it's for prototyping, not production, but the retry is the same. Then check.kql looks at revenue, copies per order and extents.
o1,ann,10
o2,bob,20
o3,cat,30// the pipeline retried: the same batch file, loaded twice
.ingest into table Orders (@"/kustodata/batch_0101.csv")
with (format='csv',
tags='["ingest-by:batch_0101", "drop-by:batch_0101"]')
.ingest into table Orders (@"/kustodata/batch_0101.csv")
with (format='csv',
tags='["ingest-by:batch_0101", "drop-by:batch_0101"]')Orders | summarize Revenue = sum(Amount), Rows = count()
Orders
| summarize Copies = count() by OrderId
| where Copies > 1
| order by OrderId asc
.show table Orders extents
| summarize Extents = count(), Rows = sum(RowCount)$ kql -q -f load.kql
> .ingest into table Orders (@"/kustodata/batch_0101.csv")
ok: .ingest into table Orders (@"/kustodata/batch_0101.csv")
> .ingest into table Orders (@"/kustodata/batch_0101.csv")
ok: .ingest into table Orders (@"/kustodata/batch_0101.csv")
$ kql -f check.kql
> Orders | summarize Revenue = sum(Amount), Rows = count()
Revenue Rows
------- ----
120 6
> Orders
OrderId Copies
------- ------
o1 2
o2 2
o3 2
> .show table Orders extents
Extents Rows
------- ----
2 6Revenue is 120 from 6 rows, but the batch file has three orders worth 60. Counting rows per OrderId shows o1, o2 and o3 each 2 times. And there are 2 extents with 6 rows: the same batch, ingested twice.
The fix: each batch carries a drop-by and an ingest-by tag. We drop the extents with the batch's drop-by tag, then reload twice with ingestIfNotExists.
.drop extents <| .show table Orders extents
where tags has 'drop-by:batch_0101'
.ingest into table Orders (@"/kustodata/batch_0101.csv")
with (format='csv',
tags='["ingest-by:batch_0101", "drop-by:batch_0101"]',
ingestIfNotExists='["batch_0101"]')
.ingest into table Orders (@"/kustodata/batch_0101.csv")
with (format='csv',
tags='["ingest-by:batch_0101", "drop-by:batch_0101"]',
ingestIfNotExists='["batch_0101"]')$ kql -q -f fix.kql
> .drop extents <| .show table Orders extents
ok: .drop extents <| .show table Orders extents
> .ingest into table Orders (@"/kustodata/batch_0101.csv")
ok: .ingest into table Orders (@"/kustodata/batch_0101.csv")
> .ingest into table Orders (@"/kustodata/batch_0101.csv")
ok: .ingest into table Orders (@"/kustodata/batch_0101.csv")Orders | summarize Revenue = sum(Amount), Rows = count()| Revenue | Rows |
|---|---|
| 60 | 3 |
The second load is skipped, and revenue is back to 60 from 3 rows.
Prevention depends on the source:
| Prevent or clean up | How |
|---|---|
| ingest-by tag + ingestIfNotExists | one tag per batch, not a unique tag per small call |
| Dedup materialized view | take_any(*) by the business key |
| Soft delete | .delete table T records <| ... for a one-off cleanup |
One ingest-by and drop-by tag per batch, never a unique tag on every small call or file, because too many tags hurt performance.
Say which dashboards showed wrong numbers, and for how long.
Question 2: The cluster cost doubled this month. Where do you look?
Start in Azure Cost Management, by meter. Cluster cost is compute, storage, networking and the Azure Data Explorer markup.
| Cost part | Driven by | Check |
|---|---|---|
| Compute | SKU x node count | autoscale history, SKU changes |
| Storage | retention + a recoverability buffer | retention changes |
| Networking, features | followers, private endpoints, Python sandboxes | what was added this month |
| Markup | per production engine core | Dev/Test clusters: no markup |
Compute is the SKU times the node count, so check the autoscale history: did it scale out and stay out? Then look at what makes it scale:
- Bigger hot cache: more nodes
- Materialized views, update policies, partitioning: more CPU
- Streaming ingestion: uses local SSD, shrinks the cache
- Queries on cold data: storage read transactions
The engine tells you what changed. Let's make a month of changes in changes.kql: a 365-day hot cache and five years of retention on ClickEvents, streaming on Telemetry, and a new materialized view.
// what changed on the cluster this month
.alter table ClickEvents policy caching hot = 365d
.alter-merge table ClickEvents policy retention softdelete = 1825d
.alter table Telemetry policy streamingingestion enable
.create materialized-view ClicksPerPage on table ClickEvents {
ClickEvents | summarize Clicks = count() by Page, bin(Ts, 1h)
}$ kql -q -f changes.kql
> .alter table ClickEvents policy caching hot = 365d
ok: .alter table ClickEvents policy caching hot = 365d
> .alter-merge table ClickEvents policy retention softdelete = 1825d
ok: .alter-merge table ClickEvents policy retention softdelete = 1825d
> .alter table Telemetry policy streamingingestion enable
ok: .alter table Telemetry policy streamingingestion enable
> .create materialized-view ClicksPerPage on table ClickEvents {
ok: .create materialized-view ClicksPerPage on table ClickEvents {The journal fills in the background on the emulator, so the full script waits at this point until the view is listed. Then we read .show journal, filtered to policies and views.
$ kql '.show journal | where Event in ("ALTER-TABLE-CACHING-POLICY", "ALTER-TABLE-RETENTION-POLICY", "ALTER-TABLE-STREAMING-INGESTION-POLICY", "CREATE-MATERIALIZED-VIEW") | project Event, EntityName, Command = substring(ChangeCommand, 0, 66) | order by Event asc, EntityName asc'
Event EntityName Command
-------------------------------------- -------------
------------------------------------------------------------
ALTER-TABLE-CACHING-POLICY ClickEvents .alter table ClickEvents policy caching hot =
365d
ALTER-TABLE-RETENTION-POLICY ClickEvents .alter-merge table ClickEvents policy retention
softdelet...
ALTER-TABLE-STREAMING-INGESTION-POLICY Telemetry .alter table Telemetry policy streamingingestion
enable
CREATE-MATERIALIZED-VIEW ClicksPerPage .create materialized-view ClicksPerPage on table
ClickEve...| Event | EntityName | Command |
|---|---|---|
| ALTER-TABLE-CACHING-POLICY | ClickEvents | .alter table ClickEvents policy caching hot = 365d |
| ALTER-TABLE-RETENTION-POLICY | ClickEvents | .alter-merge table ClickEvents policy retention softdelete = 1825d |
| ALTER-TABLE-STREAMING-INGESTION-POLICY | Telemetry | .alter table Telemetry policy streamingingestion enable |
| CREATE-MATERIALIZED-VIEW | ClicksPerPage | .create materialized-view ClicksPerPage on table ClickEvents { |
Four entries: ALTER-TABLE-CACHING-POLICY and ALTER-TABLE-RETENTION-POLICY on ClickEvents, ALTER-TABLE-STREAMING-INGESTION-POLICY on Telemetry, and CREATE-MATERIALIZED-VIEW for ClicksPerPage, each with its command.
Two more things to check. Dev/Test clusters have no markup and no SLA, so production SKUs in dev are a common leak. And reserved capacity covers only the markup.
The fix is usually policy, not hardware: hot cache for the days people query, retention per table, and autoscale limits that match the budget.
Question 3: Someone asks whether to use Azure Data Explorer, Synapse or Databricks for a new log analytics workload. How do you decide?
By the main job. For interactive queries over logs, telemetry and time series, Azure Data Explorer, or an Eventhouse in Fabric, is the natural fit.
| Azure Data Explorer / Eventhouse | Databricks | SQL warehouse | |
|---|---|---|---|
| Built for | interactive logs, telemetry, time series | transformation, ML, the lakehouse | curated relational BI |
| Data | raw and semi-structured, as it arrives | files in the lake (Delta) | modeled tables |
| Language | KQL | Spark SQL, Python | T-SQL |
| Log analytics | the natural fit | when logs feed ML or big ETL | curated summaries only |
Why logs fit Azure Data Explorer: raw lines go in as they are, and one query filters, parses and counts them. Let's count errors per service straight from the unparsed lines in AppLogs.
AppLogs
| where Line has "ERROR"
| parse Line with Ts:datetime " " Level:string " " Service:string
" " Message:string
| where Level == "ERROR"
| summarize Errors = count(), Last = max(Ts) by Service
| order by Service asc$ kql -f logs.kql
> AppLogs
Service Errors Last
-------- ------ --------------------
checkout 1 2026-10-20T08:04:00Z
payments 2 2026-10-20T08:03:00Z| Service | Errors | Last |
|---|---|---|
| checkout | 1 | 2026-10-20T08:04:00Z |
| payments | 2 | 2026-10-20T08:03:00Z |
payments has 2 errors, the last at 2026-10-20T08:03:00Z, and checkout has 1, at 08:04.
My rules for the decision:
- Who queries it: and how fast it must answer
- How fresh: seconds, minutes, or tomorrow morning
- What the team runs: skills and platforms already in place
- Often both: ADX for hot and interactive, the lake and Databricks for history and ML
If Synapse means Synapse Data Explorer, it was retired on 7 October 2025, and its successor is Eventhouse in Microsoft Fabric.
Question 4: A query works in the web UI but times out from an application. How do you troubleshoot it?
Compare the two requests, not just the query text. The app often runs as another identity, in a workload group with tighter limits.
Here is what can differ, and where to check it:
| What can differ | Where to check |
|---|---|
| Identity, workload group | .show queries: Application, Principal, WorkloadGroup, Duration, State |
| Timeouts | server: 4 minutes default, servertimeout up to 1 hour; the SDK; the HTTP client |
| Result size | 500,000 records or 64 MB: partial query failure |
| Throttling, consistency | HTTP 429 (request rate limits); strong vs weak consistency |
First, the timeouts. The server default is four minutes for queries, and the servertimeout request property raises it up to one hour. The SDK and the app's own HTTP client have timeouts too, and the shortest one wins.
The other classic is result size: 500,000 records or 64 MB. Let's ask for 600,000 rows with our command line client.
$ kql --max 3 'range x from 1 to 600000 step 1'
x
-
1
2
3
... 499997 more rows (500000 rows in total)
Error (partial result): Query execution has exceeded the allowed limits (80DA0003): The results of this
query exceed the set limit of 500000 records, so not all records were returned
(E_QUERY_RESULT_SET_TOO_LARGE, 0x80DA0003). See https://aka.ms/kustoquerylimits for more
information and possible solutions..kql prints the rows it got, 500000 rows in total, and then the partial result error, E_QUERY_RESULT_SET_TOO_LARGE. Now the same query from a Python app with a 10-minute server timeout:
from datetime import timedelta
from azure.kusto.data import (ClientRequestProperties, KustoClient,
KustoConnectionStringBuilder)
from azure.kusto.data.exceptions import KustoServiceError
kcsb = KustoConnectionStringBuilder.with_no_authentication(
"http://localhost:8080") # emulator only
client = KustoClient(kcsb)
props = ClientRequestProperties()
props.set_option("servertimeout", timedelta(minutes=10)) # max 1 hour
try:
rows = client.execute("shop", "range x from 1 to 600000 step 1",
props)
print("rows:", len(rows.primary_results[0]))
except KustoServiceError as e:
print("failed:", type(e).__name__)
print("too large:", "E_QUERY_RESULT_SET_TOO_LARGE" in str(e))$ python app.py
failed: KustoMultiApiError
too large: TrueThe app gets an exception instead: failed: KustoMultiApiError, and too large: True.
The fixes:
- Return less: summarize in the engine
- Page: with stored query results
- notruncation: only when you need it
Also look for throttling, an HTTP 429, and the consistency setting.
Same principal, same database, same request properties, same workload group. Usually one of them differs.
Question 5: A team wants to move from Azure Data Explorer to an Eventhouse in Microsoft Fabric. How do you plan it?
Microsoft documents a phased migration with no downtime. The steps, in order:
- Schema: .show database schema as csl script, then .execute database script
- A view per table: the new table, union the old cluster's table
- Queries: repoint to the KQL database query URI
- Ingestion: switch to its ingestion URI
- History, then retire: Kusto Copy if needed, then retire the cluster
Step two is the clever one. A view named like each table unions the new table with the old one, so queries stay the same. Let's try it locally: the legacy database plays the old cluster. September's invoices are there, October's are in shop, and the view is called Invoices.
// in the new database: a view named like the table
.create-or-alter function with (view=true, skipvalidation=true)
Invoices() {
Invoices | union database("legacy").Invoices
}
Invoices
| summarize Invoices = count(), Amount = sum(Amount)
by Month = startofmonth(InvoiceDate)
| order by Month asc$ kql -q -f view.kql
> .create-or-alter function with (view=true, skipvalidation=true)
ok: .create-or-alter function with (view=true, skipvalidation=true)
> Invoices
Month Invoices Amount
-------------------- -------- ------
2026-09-01T00:00:00Z 2 240
2026-10-01T00:00:00Z 3 400| Month | Invoices | Amount |
|---|---|---|
| 2026-09-01T00:00:00Z | 2 | 240 |
| 2026-10-01T00:00:00Z | 3 | 400 |
One query returns both months: 2 invoices worth 240 in September, and 3 worth 400 in October.
In the Eventhouse, Microsoft's migration guide uses the same pattern with cluster():
// in the Eventhouse KQL database (Microsoft's migration guide)
.create function with (view=true) Invoices() {
Invoices
| union cluster("YourADXCluster").database("YourDatabase").Invoices
}Or start with a database shortcut: read only, with a slight lag.
Policies, row level security, workload groups, private networking and customer-managed keys each need their own plan. And size the Fabric capacity with a test.
Question 6: Users complain that last month's data is slow to query but today's is fast. What is going on?
Almost always the caching policy. Data inside the hot period sits on the nodes' local SSD and memory; older data is in cold storage, which is cheaper but slower.
- Hot cache, today's data: local SSD and memory, fast
- Cold storage, last month's data: cheaper, slower
The fixes:
- Match the hot period: to what people query; or a hot window for month end
- Warming takes up to an hour: and a bigger cache can mean more nodes, so pair it with optimized autoscale
Let's set the policy on Logs: 14 days hot, plus a hot window for September, and read it back.
.alter table Logs policy caching hot = 14d,
hot_window = datetime(2026-09-01) .. datetime(2026-10-01)
.show table Logs policy caching
| extend P = parse_json(Policy)
| project Hot = tostring(P.DataHotSpan.Value),
WindowFrom = todatetime(P.HotWindows[0].MinValue),
WindowTo = todatetime(P.HotWindows[0].MaxValue)$ kql -q -f cache.kql
> .alter table Logs policy caching hot = 14d,
ok: .alter table Logs policy caching hot = 14d,
> .show table Logs policy caching
ok: .show table Logs policy caching| Hot | WindowFrom | WindowTo |
|---|---|---|
| 14.00:00:00 | 2026-09-01T00:00:00Z | 2026-10-01T00:00:00Z |
Hot is 14.00:00:00, and the window runs from 2026-09-01T00:00:00Z to 2026-10-01T00:00:00Z. The emulator accepts it but keeps everything on local disk, so the speed difference can't be shown here.
The better fix for reports is a materialized view of daily totals, created async with backfill=true so it covers the old rows. The wait script returns once the backfill has finished, and then we read September.
// backfill over existing rows: the create must be async
.create async materialized-view with (backfill=true)
DailyLogs on table Logs {
Logs
| summarize Requests = count(), Errors = countif(Level == "Error")
by Day = bin(Timestamp, 1d), Service
}# the view answers once its async backfill has finished
for i in $(seq 1 60); do
n=$(kql "DailyLogs | count" 2>/dev/null | tail -1)
[ "${n:-0}" -ge 6 ] 2>/dev/null && exit 0
sleep 5
done
echo "backfill did not finish"; exit 1DailyLogs
| where Day between (datetime(2026-09-01) .. datetime(2026-09-30))
| order by Day asc, Service asc$ kql -q -f mv.kql
> .create async materialized-view with (backfill=true)
ok: .create async materialized-view with (backfill=true)
$ bash wait_backfill.sh
$ kql -f daily.kql
> DailyLogs
Day Service Requests Errors
-------------------- ------- -------- ------
2026-09-10T00:00:00Z api 3 1
2026-09-10T00:00:00Z web 1 0
2026-09-11T00:00:00Z api 1 0
2026-09-11T00:00:00Z web 1 1| Day | Service | Requests | Errors |
|---|---|---|---|
| 2026-09-10T00:00:00Z | api | 3 | 1 |
| 2026-09-10T00:00:00Z | web | 1 | 0 |
| 2026-09-11T00:00:00Z | api | 1 | 0 |
| 2026-09-11T00:00:00Z | web | 1 | 1 |
September comes back as four small summary rows instead of raw log lines: on September 10, api had 3 requests and 1 error and web 1 and 0; on September 11, api 1 and 0, and web 1 and 1. Summaries are cheap to keep hot.
Run the query with set query_datascope = "hotcache". If rows go missing, the query needed cold data.
Scenario 7: In the final round, the hiring manager asks you to design a real-time analytics platform with Azure Data Explorer. What is your design?
Left to right: sources, data connections, raw tables, transactional update policies, typed tables, materialized views, dashboards.
- Sources: Event Hubs, IoT Hub, Event Grid
- Data connections: managed identity, own consumer group
- Raw tables: queued (tuned) or streaming
- Update policies: transactional
- Typed tables: retention, cache
- Materialized views: dashboard aggregates
- Dashboards: Power BI DirectQuery, Grafana
Sources send to Event Hubs or IoT Hub, and files land in storage with Event Grid. Queued ingestion is tuned for latency, and streaming is for small, latency-critical tables.
Let's build the core path on the emulator. pipeline.kql creates RawEvents with a JSON mapping, the typed Readings table, a ParseRaw() function, a transactional update policy, and an hourly materialized view, DeviceHourly. Then .ingest inline, a prototyping command, stands in for the data connection with three raw events.
.create table RawEvents (Payload:dynamic)
.create table RawEvents ingestion json mapping 'RawMap'
'[{"column": "Payload", "Properties": {"Path": "$"}}]'
.create table Readings (Ts:datetime, DeviceId:string, Temp:real)
.create-or-alter function ParseRaw() {
RawEvents
| project Ts = todatetime(Payload.ts),
DeviceId = tostring(Payload.device), Temp = toreal(Payload.temp)
}
.alter table Readings policy update ```[{"IsEnabled": true,
"Source": "RawEvents", "Query": "ParseRaw()",
"IsTransactional": true}]```
.create materialized-view DeviceHourly on table Readings {
Readings | summarize AvgTemp = avg(Temp), Events = count()
by DeviceId, Hour = bin(Ts, 1h)
}.ingest inline into table RawEvents
with (format='json', ingestionMappingReference='RawMap') <|
{"ts": "2026-10-20T08:05:00Z", "device": "d1", "temp": 21.5}
{"ts": "2026-10-20T08:35:00Z", "device": "d1", "temp": 22.5}
{"ts": "2026-10-20T08:20:00Z", "device": "d2", "temp": 30.0}$ kql -q -f pipeline.kql
> .create table RawEvents (Payload:dynamic)
ok: .create table RawEvents (Payload:dynamic)
> .create table RawEvents ingestion json mapping 'RawMap'
ok: .create table RawEvents ingestion json mapping 'RawMap'
> .create table Readings (Ts:datetime, DeviceId:string, Temp:real)
ok: .create table Readings (Ts:datetime, DeviceId:string, Temp:real)
> .create-or-alter function ParseRaw() {
ok: .create-or-alter function ParseRaw() {
> .alter table Readings policy update ```[{"IsEnabled": true,
ok: .alter table Readings policy update ```[{"IsEnabled": true,
> .create materialized-view DeviceHourly on table Readings {
ok: .create materialized-view DeviceHourly on table Readings {
$ kql -q -f events.kql
> .ingest inline into table RawEvents
ok: .ingest inline into table RawEventsReadings | order by Ts asc| Ts | DeviceId | Temp |
|---|---|---|
| 2026-10-20T08:05:00Z | d1 | 21.5 |
| 2026-10-20T08:20:00Z | d2 | 30 |
| 2026-10-20T08:35:00Z | d1 | 22.5 |
Three raw events in, three typed rows out from the update policy: d1 at 21.5 and 22.5, and d2 at 30. Now the view by name:
$ kql 'DeviceHourly | order by DeviceId asc, Hour asc'
DeviceId Hour AvgTemp Events
-------- -------------------- ------- ------
d1 2026-10-20T08:00:00Z 22 2
d2 2026-10-20T08:00:00Z 30 1| DeviceId | Hour | AvgTemp | Events |
|---|---|---|---|
| d1 | 2026-10-20T08:00:00Z | 22 | 2 |
| d2 | 2026-10-20T08:00:00Z | 30 | 1 |
The hourly average per device: 22 for d1 from 2 events, and 30 for d2 from 1.
Around it:
- Security: Entra groups, RLS, private endpoints, restricted outbound
- Operations: metrics, diagnostic logs, freshness alerts; Bicep + Azure DevOps
- Cost and scale: optimized autoscale, followers or shortcuts; Eventhouse if Fabric-first
If the company is Fabric-first, I propose the same design in an Eventhouse.
Key points to remember
- Double counts: prove, find the extents, drop by drop-by tag, reload with ingestIfNotExists
- Cost: compute, storage, networking, markup; .show journal shows what changed
- Logs: Azure Data Explorer or Eventhouse; Synapse Data Explorer is retired
- App timeouts: compare principal, workload group, request properties; mind truncation
- Slow old data: caching policy, hot windows, materialized views
The full script
Here is a Python script that writes every file and runs every query in order on the Kusto emulator; start the emulator container first, as the comments at the top of the script show.
Show the full script that writes the files and runs every step
# ========================================================================
# Azure Data Explorer Interview Q&A #10 - Real-world manager scenarios
# TechBrothersIT. Names and sample data are invented for teaching.
# Every step below was run on the Kusto emulator (Kustainer, the Azure Data Explorer engine in a Linux container,
# build 2026.11.02); the outputs in the video and the blog post are the real outputs.
# To run it yourself (Docker on Linux, macOS or Windows with WSL), in an EMPTY folder:
# 1. Start the emulator with this folder mounted at /kustodata (the steps ingest files from there):
# docker run -d --name kustainer -e ACCEPT_EULA=Y -m 4G -p 8080:8080 -v "$PWD":/kustodata \
# mcr.microsoft.com/azuredataexplorer/kustainer-linux:latest
# Wait until `docker logs kustainer` says the database "is answering queries".
# 2. python KQL_Interview_QA_10_Code.py (Python 3.10 or later; kql.py itself needs no packages: it uses the REST API)
# The python scripts in some steps use the Kusto Python SDK: pip install azure-kusto-data azure-kusto-ingest
# "kql" in the commands is a small helper written by Step 0 (kql.py): it sends a query or a management command
# to http://localhost:8080 and prints the result as a text table (no sign-in: the emulator has no authentication).
# The emulator is for development and testing only; an Azure Data Explorer cluster needs Microsoft Entra ID sign-in
# (use the Kusto SDK or the web UI there).
# Steps that restart the emulator say so in a comment: run "docker restart kustainer" at that point.
# ========================================================================
import os, subprocess, sys, time
def write(path, text):
if text is None:
if os.path.exists(path): os.remove(path)
return
os.makedirs(os.path.dirname(path) or ".", exist_ok=True)
with open(path, "w", encoding="utf-8") as f:
f.write(text.strip("\n") + "\n")
def run(cmd, expect_error=False, allow_error=False, env=None):
print("\n$ " + cmd, flush=True)
rc = subprocess.run(cmd, shell=True, env=dict(os.environ, **(env or {}))).returncode
if rc != 0 and not (expect_error or allow_error):
raise SystemExit("stopped: this command was expected to succeed")
# Environment used by every step (the database the steps use)
os.environ.update({'KQL_DB': 'shop'})
# Step 0: the kql helper, the database and the starting files
write("kql.py", '"""kql: run KQL queries and management commands on the local Kusto emulator (Kustainer), print the results as text tables.\n kql \'StormEvents | take 3\' run one query or one management command (starts with \'.\')\n kql -f script.kql run every statement in the file (statements are separated by blank lines;\n // comment lines are skipped); each result is printed under its first line\n options: -d DB (default $KQL_DB or NetDefaultDB) --max N rows to print (default 30) --cell N max chars per cell (default 60)\n -q for management commands: print one line ("ok: <command>") instead of the result table\n --stats after a query, print the rows / extents the query scanned (from the engine\'s own query statistics)\n --props \'{"Options": {...}}\' client request properties (e.g. truncationmaxrecords)\n -e keep going after an error (the error is printed and the exit code is 1 at the end)\n --json print the last statement\'s first result as JSON {"cols": [...], "rows": [...]} (used by the video generator)\nEmpty cells: a null value in a non-string column prints as "null"; an empty or null string prints as nothing.\nA query that fails after returning some rows (for example a result over the truncation limit) prints the rows it got, then\n"Error (partial result): <engine message>", and exits 1.\nThe engine answers at http://localhost:8080 (no authentication: the emulator has none)."""\nimport json, os, re, sys, urllib.request, urllib.error\n\nURL = os.environ.get(\'KQL_URL\', \'http://localhost:8080\')\n\n\ndef post(path, db, csl, props=None):\n body = {\'db\': db, \'csl\': csl, \'properties\': json.dumps(props or {\'Options\': {}})}\n req = urllib.request.Request(URL + path, data=json.dumps(body).encode(), method=\'POST\',\n headers={\'Content-Type\': \'application/json; charset=utf-8\', \'Accept\': \'application/json\',\n \'x-ms-app\': \'kql-cli\', \'x-ms-client-request-id\': \'kql-cli\'})\n opener = urllib.request.build_opener(urllib.request.ProxyHandler({}))\n try:\n with opener.open(req, timeout=600) as r: return json.loads(r.read().decode(\'utf-8\'))\n except urllib.error.HTTPError as e:\n txt = e.read().decode(\'utf-8\', \'replace\')\n try:\n er = json.loads(txt)[\'error\']\n msg = er.get(\'@message\') or er.get(\'message\')\n except Exception:\n msg = txt.strip().split(\'\\n\')[0]\n msg = re.sub(r\'^Request is invalid and cannot be processed: \', \'\', msg)\n msg = re.sub(r\'\\s*\\(Activity stack:.*\', \'\', msg, flags=re.S)\n raise KqlError(msg.strip())\n\n\nclass KqlError(Exception): pass\n\n\ndef cell(v, width, typ=\'\'):\n if v is None: s = \'\' if typ in (\'string\', \'\') else \'null\'\n elif isinstance(v, bool): s = \'true\' if v else \'false\'\n elif isinstance(v, (dict, list)): s = json.dumps(v, separators=(\',\', \':\'), ensure_ascii=False)\n elif isinstance(v, float):\n s = repr(v)\n if s.endswith(\'.0\'): s = s[:-2]\n else: s = str(v)\n s = s.replace(\'\\r\', \' \').replace(\'\\n\', \' \')\n if len(s) > width: s = s[:width - 3] + \'...\'\n return s\n\n\ndef table(cols, rows, mx, width, types=None):\n rows = rows if isinstance(rows, list) else []\n types = types or [\'\'] * len(cols)\n shown = [[cell(v, width, types[i]) for i, v in enumerate(r)] for r in rows[:mx]]\n w = [max([len(c)] + [len(r[i]) for r in shown]) for i, c in enumerate(cols)]\n out = [\' \'.join(c.ljust(w[i]) for i, c in enumerate(cols)).rstrip(),\n \' \'.join(\'-\' * w[i] for i in range(len(cols)))]\n for r in shown:\n out.append(\' \'.join(v.ljust(w[i]) for i, v in enumerate(r)).rstrip())\n n = len(rows)\n if n > mx: out.append(\'... %d more rows (%d rows in total)\' % (n - mx, n))\n elif n == 0: out.append(\'(no rows)\')\n return \'\\n\'.join(out)\n\n\nLAST = {}\n\n\ndef one_api_msg(errs):\n msgs = []\n for e in errs or []:\n er = e.get(\'error\', e) if isinstance(e, dict) else {}\n m = er.get(\'@message\') or er.get(\'message\') or str(e)\n m = re.sub(r\'\\s*\\(Activity stack:.*\', \'\', m, flags=re.S).strip()\n if m not in msgs: msgs.append(m)\n return \' | \'.join(msgs) or \'unknown error\'\n\n\ndef run_query(db, csl, a):\n frames = post(\'/v2/rest/query\', db, csl, a.props)\n outs, stats, partial = [], None, None\n for f in frames:\n if f.get(\'FrameType\') == \'DataSetCompletion\' and f.get(\'HasErrors\') and partial is None:\n partial = one_api_msg(f.get(\'OneApiErrors\'))\n if f.get(\'FrameType\') != \'DataTable\': continue\n if f.get(\'TableKind\') == \'PrimaryResult\':\n rows = [r for r in f[\'Rows\'] if isinstance(r, list)]\n for r in f[\'Rows\']:\n if isinstance(r, dict) and r.get(\'OneApiErrors\'): partial = one_api_msg(r[\'OneApiErrors\'])\n cols = [c[\'ColumnName\'] for c in f[\'Columns\']]\n if \'cols\' not in LAST: LAST.update(cols=cols, rows=rows)\n outs.append(table(cols, rows, a.max, a.cell, [str(c.get(\'ColumnType\', \'\')).lower() for c in f[\'Columns\']]))\n if f.get(\'TableKind\') == \'QueryCompletionInformation\':\n cols = [c[\'ColumnName\'] for c in f[\'Columns\']]\n for r in f[\'Rows\']:\n d = dict(zip(cols, r))\n if d.get(\'EventTypeName\') == \'QueryResourceConsumption\':\n try: stats = json.loads(d[\'Payload\'])\n except Exception: pass\n if d.get(\'Level\') in (2, 3) and d.get(\'EventTypeName\') not in (\'QueryResourceConsumption\',):\n txt = d.get(\'Payload\') or \'\'\n try: txt = json.loads(txt).get(\'Text\', txt)\n except Exception: pass\n outs.append(\'%s: %s\' % (\'Warning\' if d.get(\'Level\') == 3 else \'Error\', txt))\n if a.stats and stats:\n sh = (stats.get(\'resource_usage\') or {}).get(\'cache\', {}).get(\'shards\', {})\n ix = stats.get(\'input_dataset_statistics\') or {}\n ex, rw = ix.get(\'extents\') or {}, ix.get(\'rows\') or {}\n outs.append(\'scanned: %s of %s extents, %s of %s rows\' % (ex.get(\'scanned\'), ex.get(\'total\'), rw.get(\'scanned\'), rw.get(\'total\')))\n if partial:\n outs.append(\'Error (partial result): \' + partial)\n raise PartialError(\'\\n\\n\'.join(outs))\n return \'\\n\\n\'.join(outs)\n\n\nclass PartialError(Exception): pass\n\n\ndef run_mgmt(db, csl, a):\n d = post(\'/v1/rest/mgmt\', db, csl, a.props)\n if a.q and not a.json: return \'ok: \' + first_line(csl)\n t = (d.get(\'Tables\') or [{}])[0]\n LAST.update(cols=[c[\'ColumnName\'] for c in t.get(\'Columns\', [])], rows=t.get(\'Rows\', []))\n return table([c[\'ColumnName\'] for c in t.get(\'Columns\', [])], t.get(\'Rows\', []), a.max, a.cell,\n [str(c.get(\'DataType\', c.get(\'ColumnType\', \'\'))).lower().replace(\'system.\', \'\') for c in t.get(\'Columns\', [])])\n\n\ndef first_line(s):\n l = s.strip().split(\'\\n\')[0].strip()\n return l if len(l) <= 90 else l[:87] + \'...\'\n\n\ndef statements(text):\n blocks, cur = [], []\n for l in text.split(\'\\n\'):\n if not l.strip():\n if cur: blocks.append(\'\\n\'.join(cur)); cur = []\n continue\n if l.strip().startswith(\'//\') and not cur: continue\n cur.append(l)\n if cur: blocks.append(\'\\n\'.join(cur))\n return blocks\n\n\ndef main():\n import argparse\n p = argparse.ArgumentParser(add_help=False)\n p.add_argument(\'-d\', default=os.environ.get(\'KQL_DB\', \'NetDefaultDB\'))\n p.add_argument(\'-f\'); p.add_argument(\'-q\', action=\'store_true\'); p.add_argument(\'-e\', action=\'store_true\')\n p.add_argument(\'--stats\', action=\'store_true\'); p.add_argument(\'--max\', type=int, default=30)\n p.add_argument(\'--cell\', type=int, default=60); p.add_argument(\'--props\'); p.add_argument(\'-h\', \'--help\', action=\'store_true\')\n p.add_argument(\'--json\', action=\'store_true\')\n p.add_argument(\'text\', nargs=\'?\')\n a = p.parse_args()\n if a.help or (not a.f and not a.text): print(__doc__); return 0\n a.props = json.loads(a.props) if a.props else None\n stmts = statements(open(a.f).read()) if a.f else [a.text]\n rc, first = 0, True\n if a.json:\n for s in stmts:\n LAST.clear()\n try:\n run_mgmt(a.d, s, a) if s.lstrip().startswith(\'.\') else run_query(a.d, s, a)\n except (KqlError, PartialError) as e:\n print(\'Error: \' + str(e)); return 1\n print(json.dumps(dict(cols=LAST.get(\'cols\', []), rows=LAST.get(\'rows\', [])), ensure_ascii=False))\n return 0\n for s in stmts:\n if a.f:\n if not first: print()\n print(\'> \' + first_line(s))\n first = False\n try:\n out = run_mgmt(a.d, s, a) if s.lstrip().startswith(\'.\') else run_query(a.d, s, a)\n if out: print(out)\n except PartialError as e:\n print(str(e)); rc = 1\n if not a.e: return 1\n except KqlError as e:\n print(\'Error: \' + str(e)); rc = 1\n if not a.e: return 1\n return rc\n\n\nif __name__ == \'__main__\':\n sys.exit(main())\n')
KQL = '"%s" "%s"' % (sys.executable, os.path.abspath("kql.py")) # commands below run it as: python kql.py ...
run(KQL + ' -d NetDefaultDB -q \'.create database shop persist (@"/kustodata/dbs/shop/md", @"/kustodata/dbs/shop/data")\'')
write('setup.kql', """\
.create table Orders (OrderId:string, Customer:string, Amount:real)
.create table ClickEvents (Ts:datetime, Page:string)
.ingest inline into table ClickEvents <|
2026-10-20T08:00:00Z,home
2026-10-20T08:01:00Z,cart
2026-10-20T08:02:00Z,home
.create table Telemetry (DeviceId:string, Temp:real, Ts:datetime)
.create table Invoices (InvoiceId:long, Amount:real, InvoiceDate:datetime)
.ingest inline into table Invoices <|
3001,120,2026-10-05T09:00:00Z
3002,80,2026-10-12T09:00:00Z
3003,200,2026-10-19T09:00:00Z
.create table Logs (Timestamp:datetime, Service:string, Level:string)
.ingest inline into table Logs <|
2026-09-10T08:00:00Z,api,Info
2026-09-10T09:00:00Z,api,Error
2026-09-10T10:00:00Z,api,Info
2026-09-10T11:00:00Z,web,Info
2026-09-11T08:00:00Z,api,Info
2026-09-11T09:00:00Z,web,Error
2026-10-20T08:00:00Z,api,Info
2026-10-20T09:00:00Z,web,Info
.create table AppLogs (Line:string)
.ingest inline into table AppLogs <|
2026-10-20T08:01:00Z ERROR payments timeout calling gateway
2026-10-20T08:02:00Z INFO checkout order placed
2026-10-20T08:03:00Z ERROR payments card declined
2026-10-20T08:04:00Z ERROR checkout coupon invalid
""")
write('legacy_db.kql', """\
.create database legacy persist (@"/kustodata/dbs/legacy/md", @"/kustodata/dbs/legacy/data")
""")
write('legacy.kql', """\
.create table Invoices (InvoiceId:long, Amount:real, InvoiceDate:datetime)
.ingest inline into table Invoices <|
2001,150,2026-09-08T09:00:00Z
2002,90,2026-09-22T09:00:00Z
""")
run(KQL + ' -q -f setup.kql')
run(KQL + ' -d NetDefaultDB -q -f legacy_db.kql')
run(KQL + ' -d legacy -q -f legacy.kql')
# Step 1: A retry loads the same batch twice; prove the duplicates and find the two extents
write('batch_0101.csv', """\
o1,ann,10
o2,bob,20
o3,cat,30
""")
write('load.kql', """\
// the pipeline retried: the same batch file, loaded twice
.ingest into table Orders (@"/kustodata/batch_0101.csv")
with (format='csv',
tags='["ingest-by:batch_0101", "drop-by:batch_0101"]')
.ingest into table Orders (@"/kustodata/batch_0101.csv")
with (format='csv',
tags='["ingest-by:batch_0101", "drop-by:batch_0101"]')
""")
write('check.kql', """\
Orders | summarize Revenue = sum(Amount), Rows = count()
Orders
| summarize Copies = count() by OrderId
| where Copies > 1
| order by OrderId asc
.show table Orders extents
| summarize Extents = count(), Rows = sum(RowCount)
""")
run(KQL + ' -q -f load.kql')
run(KQL + ' -f check.kql')
# Step 2: Drop the batch by its drop-by tag, reload twice with ingestIfNotExists: the second load is skipped
write('fix.kql', """\
.drop extents <| .show table Orders extents
where tags has 'drop-by:batch_0101'
.ingest into table Orders (@"/kustodata/batch_0101.csv")
with (format='csv',
tags='["ingest-by:batch_0101", "drop-by:batch_0101"]',
ingestIfNotExists='["batch_0101"]')
.ingest into table Orders (@"/kustodata/batch_0101.csv")
with (format='csv',
tags='["ingest-by:batch_0101", "drop-by:batch_0101"]',
ingestIfNotExists='["batch_0101"]')
""")
run(KQL + ' -q -f fix.kql')
# Step 3: A month of changes: hot cache, retention, streaming ingestion, a materialized view
write('changes.kql', """\
// what changed on the cluster this month
.alter table ClickEvents policy caching hot = 365d
.alter-merge table ClickEvents policy retention softdelete = 1825d
.alter table Telemetry policy streamingingestion enable
.create materialized-view ClicksPerPage on table ClickEvents {
ClickEvents | summarize Clicks = count() by Page, bin(Ts, 1h)
}
""")
run(KQL + ' -q -f changes.kql')
# Step 4: (wait until the engine has flushed its journal)
run('for i in $(seq 1 60); do n=$(kql \'.show journal | where Event == "CREATE-MATERIALIZED-VIEW" and EntityName == "ClicksPerPage" | count\' | tail -1); [ "$n" -ge 1 ] && break; sleep 5; done; echo journal flushed')
# Step 5: .show journal: who changed what
run(KQL + ' \'.show journal | where Event in ("ALTER-TABLE-CACHING-POLICY", "ALTER-TABLE-RETENTION-POLICY", "ALTER-TABLE-STREAMING-INGESTION-POLICY", "CREATE-MATERIALIZED-VIEW") | project Event, EntityName, Command = substring(ChangeCommand, 0, 66) | order by Event asc, EntityName asc\'')
# Step 6: Raw log lines parsed and counted in one query
write('logs.kql', """\
AppLogs
| where Line has "ERROR"
| parse Line with Ts:datetime " " Level:string " " Service:string
" " Message:string
| where Level == "ERROR"
| summarize Errors = count(), Last = max(Ts) by Service
| order by Service asc
""")
run(KQL + ' -f logs.kql')
# Step 7: 600,000 rows: the kql client gets 500,000 rows and the partial result error
run(KQL + " --max 3 'range x from 1 to 600000 step 1'", expect_error=True) # EXPECTED TO FAIL: the video shows this error
# Step 8: The same query from a Python app with a 10-minute server timeout: an exception
write('app.py', """\
from datetime import timedelta
from azure.kusto.data import (ClientRequestProperties, KustoClient,
KustoConnectionStringBuilder)
from azure.kusto.data.exceptions import KustoServiceError
kcsb = KustoConnectionStringBuilder.with_no_authentication(
"http://localhost:8080") # emulator only
client = KustoClient(kcsb)
props = ClientRequestProperties()
props.set_option("servertimeout", timedelta(minutes=10)) # max 1 hour
try:
rows = client.execute("shop", "range x from 1 to 600000 step 1",
props)
print("rows:", len(rows.primary_results[0]))
except KustoServiceError as e:
print("failed:", type(e).__name__)
print("too large:", "E_QUERY_RESULT_SET_TOO_LARGE" in str(e))
""")
run('python app.py')
# Step 9: Migration step 2 locally: a view named like the table unions the new and the old database
write('view.kql', """\
// in the new database: a view named like the table
.create-or-alter function with (view=true, skipvalidation=true)
Invoices() {
Invoices | union database("legacy").Invoices
}
Invoices
| summarize Invoices = count(), Amount = sum(Amount)
by Month = startofmonth(InvoiceDate)
| order by Month asc
""")
run(KQL + ' -q -f view.kql')
# Step 10: A 14-day hot cache plus a hot window for September (accepted; no cache tiers on the emulator)
write('cache.kql', """\
.alter table Logs policy caching hot = 14d,
hot_window = datetime(2026-09-01) .. datetime(2026-10-01)
.show table Logs policy caching
| extend P = parse_json(Policy)
| project Hot = tostring(P.DataHotSpan.Value),
WindowFrom = todatetime(P.HotWindows[0].MinValue),
WindowTo = todatetime(P.HotWindows[0].MaxValue)
""")
run(KQL + ' -q -f cache.kql')
# Step 11: A materialized view of daily totals (async backfill): September in a few summary rows
write('mv.kql', """\
// backfill over existing rows: the create must be async
.create async materialized-view with (backfill=true)
DailyLogs on table Logs {
Logs
| summarize Requests = count(), Errors = countif(Level == "Error")
by Day = bin(Timestamp, 1d), Service
}
""")
write('wait_backfill.sh', """\
# the view answers once its async backfill has finished
for i in $(seq 1 60); do
n=$(kql "DailyLogs | count" 2>/dev/null | tail -1)
[ "${n:-0}" -ge 6 ] 2>/dev/null && exit 0
sleep 5
done
echo "backfill did not finish"; exit 1
""")
write('daily.kql', """\
DailyLogs
| where Day between (datetime(2026-09-01) .. datetime(2026-09-30))
| order by Day asc, Service asc
""")
run(KQL + ' -q -f mv.kql')
run('bash wait_backfill.sh')
run(KQL + ' -f daily.kql')
# Step 12: The core path: raw JSON, a transactional update policy, typed rows, an hourly materialized view
write('pipeline.kql', """\
.create table RawEvents (Payload:dynamic)
.create table RawEvents ingestion json mapping 'RawMap'
'[{"column": "Payload", "Properties": {"Path": "$"}}]'
.create table Readings (Ts:datetime, DeviceId:string, Temp:real)
.create-or-alter function ParseRaw() {
RawEvents
| project Ts = todatetime(Payload.ts),
DeviceId = tostring(Payload.device), Temp = toreal(Payload.temp)
}
.alter table Readings policy update ```[{"IsEnabled": true,
"Source": "RawEvents", "Query": "ParseRaw()",
"IsTransactional": true}]```
.create materialized-view DeviceHourly on table Readings {
Readings | summarize AvgTemp = avg(Temp), Events = count()
by DeviceId, Hour = bin(Ts, 1h)
}
""")
write('events.kql', """\
.ingest inline into table RawEvents
with (format='json', ingestionMappingReference='RawMap') <|
{"ts": "2026-10-20T08:05:00Z", "device": "d1", "temp": 21.5}
{"ts": "2026-10-20T08:35:00Z", "device": "d1", "temp": 22.5}
{"ts": "2026-10-20T08:20:00Z", "device": "d2", "temp": 30.0}
""")
run(KQL + ' -q -f pipeline.kql')
run(KQL + ' -q -f events.kql')
# Step 13: The materialized view by name: hourly average per device
run(KQL + " 'DeviceHourly | order by DeviceId asc, Hour asc'")
All 10 parts are listed on the Real Time Azure Data Explorer and KQL Interview Questions and Answers page on this blog.
The sample data on this page is invented for teaching. Every terminal output is the real output of the command above it, run on the Kusto emulator (Kustainer, the Azure Data Explorer engine in a Linux container, build 2026.11.02). Features the emulator cannot show truthfully are shown as code. Part of the Real Time Azure Data Explorer and KQL Interview Questions and Answers series on TechBrothersIT. Features, limits and their status (GA or preview) are current as of November 2026; check the official Azure Data Explorer and Kusto documentation (learn.microsoft.com/azure/data-explorer and learn.microsoft.com/kusto) for anything you quote in an interview.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.