In this post we are going to cover joins, unions and lookups in KQL. 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: orders and payments arrive in separate tables, and finance wants the unmatched orders every morning.
Joins in KQL look like SQL joins, but they don't always behave like them. The default join kind is different, the side you put a table on matters, and lookup, union and the anti joins each solve their own problem. Interviewers ask about this because a wrong join kind gives a wrong number with no error at all.
Part 3 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 teammate asks what join kinds KQL has and why the default surprises people. What do you tell them?
- Question 2: Someone joins two big tables and the query is slow or runs out of memory. What do you do?
- Question 3: A report needs to add a device's region from a small dimension table. When do you use lookup instead of join?
- Question 4: Someone needs rows from several tables with the same shape. How do union and wildcards work?
- Question 5: A teammate asks how to find rows in one table with no match in another. Which join kind do you use?
- Question 6: Someone needs a login followed by an error within five minutes. How do you join on a time window?
- Scenario 7: Orders and payments arrive in separate tables and finance wants the unmatched orders every morning. How do you build it?
- The full script
The short answer
The default join is innerunique: it dedups the left keys, so write kind=inner for SQL behavior. Shrink both sides first, small side left, broadcast or shuffle when needed. lookup adds dimension columns to facts, union stacks tables, leftanti finds rows with no match, and a time-window join uses time buckets.
Let's create some sample data
Every query in this post runs on the Kusto emulator, Microsoft's Azure Data Explorer engine in a local container, in a database called shop. setup.kql creates small invented tables and loads them with .ingest inline, which is for exploration and prototyping, not production: Orders, Customers and Payments for the joins, Devices and Readings for the lookup, three Logs tables for the union, and Logins and AppErrors for the time window. kql is a small helper that sends a query to the emulator and prints the result as a text table. What only a cluster with several nodes can show, such as a shuffle join, is shown as code.
.create table Orders (OrderId:long, CustomerId:string, OrderTime:datetime,
Amount:real, Status:string)
.ingest inline into table Orders <|
1001,C001,2026-10-20T08:05:00Z,59.90,Paid
1002,C003,2026-10-20T11:03:00Z,20.00,Paid
1003,C005,2026-10-20T09:15:00Z,35.50,Pending
1004,C001,2026-10-20T12:30:00Z,12.00,Paid
1005,C006,2026-10-20T10:00:00Z,80.00,Paid
.create table Customers (CustomerId:string, Name:string, Region:string)
.ingest inline into table Customers <|
C001,Ana,North
C002,Ben,South
C003,Cara,North
C004,Dev,East
C005,Eli,West
.create table Payments (PaymentId:string, OrderId:long, PaidTime:datetime,
Amount:real)
.ingest inline into table Payments <|
P1,1001,2026-10-20T08:07:00Z,59.90
P2,1002,2026-10-20T11:05:00Z,20.00
P3,1004,2026-10-20T12:31:00Z,12.00
P4,1099,2026-10-20T09:00:00Z,5.00
.create table Devices (DeviceId:string, Site:string, Region:string)
.ingest inline into table Devices <|
d1,Oslo,North
d2,Lyon,West
d3,Pune,South
.create table Readings (Timestamp:datetime, DeviceId:string, Temp:real)
.ingest inline into table Readings <|
2026-10-20T08:00:00Z,d1,21.5
2026-10-20T08:00:00Z,d2,19
2026-10-20T08:05:00Z,d3,30.2
2026-10-20T08:05:00Z,d4,25
2026-10-20T08:10:00Z,d1,22
.create table LogsWeb (Timestamp:datetime, Level:string, DurationMs:long)
.create table LogsApi (Timestamp:datetime, Level:string, DurationMs:long)
.create table LogsJob (Timestamp:datetime, Level:string, DurationMs:real)
.ingest inline into table LogsWeb <|
2026-10-20T08:00:00Z,Info,120
2026-10-20T08:01:00Z,Error,900
2026-10-20T08:02:00Z,Info,80
.ingest inline into table LogsApi <|
2026-10-20T08:00:30Z,Info,45
2026-10-20T08:03:00Z,Error,3000
.ingest inline into table LogsJob <|
2026-10-20T02:00:00Z,Info,61000.5
.create table Logins (UserId:string, LoginTime:datetime)
.ingest inline into table Logins <|
u1,2026-10-20T08:00:00Z
u2,2026-10-20T08:10:00Z
u3,2026-10-20T09:00:00Z
.create table AppErrors (UserId:string, ErrorTime:datetime, Error:string)
.ingest inline into table AppErrors <|
u1,2026-10-20T08:03:00Z,timeout
u1,2026-10-20T08:30:00Z,timeout
u2,2026-10-20T08:14:59Z,declined
u3,2026-10-20T08:58:00Z,declinedQuestion 1: A teammate asks what join kinds KQL has and why the default surprises people. What do you tell them?
KQL has inner, left, right and full outer joins, semi and anti joins on either side, and the default, innerunique. innerunique removes duplicate keys from the left side before it joins.
Here are the kinds:
| Kind | Returns |
|---|---|
| innerunique (the default) | an inner join after removing duplicate left keys |
| inner | every matching pair, like SQL |
| leftouter, rightouter, fullouter | the matches, plus unmatched rows with empty columns |
| leftsemi, rightsemi | one side's rows that have a match, that side's columns only |
| leftanti, rightanti | one side's rows that have no match |
Now the surprise. Ana, customer C001, has two orders. Let's join orders to customers with no kind. We run q1.kql:
Orders
| join (Customers) on CustomerId
| project OrderId, CustomerId, Name
| order by OrderId asc$ kql -f q1.kql
> Orders
OrderId CustomerId Name
------- ---------- ----
1001 C001 Ana
1002 C003 Cara
1003 C005 Eli| OrderId | CustomerId | Name |
|---|---|---|
| 1001 | C001 | Ana |
| 1002 | C003 | Cara |
| 1003 | C005 | Eli |
Only three rows come back: 1001, 1002 and 1003. One of Ana's orders is gone, and which one survives is arbitrary.
Now we write kind=inner and run q1b.kql:
Orders
| join kind=inner (Customers) on CustomerId
| project OrderId, CustomerId, Name
| order by OrderId asc$ kql -f q1b.kql
> Orders
OrderId CustomerId Name
------- ---------- ----
1001 C001 Ana
1002 C003 Cara
1003 C005 Eli
1004 C001 Ana| OrderId | CustomerId | Name |
|---|---|---|
| 1001 | C001 | Ana |
| 1002 | C003 | Cara |
| 1003 | C005 | Eli |
| 1004 | C001 | Ana |
All four orders come back, including 1004 for Ana, the way a SQL developer expects. Same tables, same key, one more row.
There's no cross join kind: join on a constant key instead.
Always write the kind. The default is fine when the left key is unique, and silently wrong on facts.
Question 2: Someone joins two big tables and the query is slow or runs out of memory. What do you do?
First, make both sides smaller before the join. Then put the smaller table on the left, and only then reach for a hint.
Let's go step by step:
- Shrink both sides: a time filter and a
projectof only the columns you need, inside each side - Smaller table on the left: Microsoft's guidance for join
- broadcast: for a small left side, tens of megabytes
- shuffle: for two big sides and a key with many distinct values
Most slow joins I fix are whole tables, or a key duplicated on both sides that multiplies the rows. Here north customers go on the left and today's orders on the right, each side filtered and projected. We run q2.kql:
Customers
| where Region == "North"
| project CustomerId, Name
| join kind=inner (
Orders
| where OrderTime between (datetime(2026-10-20) .. 1d)
| project OrderId, CustomerId, Amount
) on CustomerId
| project OrderId, Name, Amount
| order by OrderId asc$ kql -f q2.kql
> Customers
OrderId Name Amount
------- ---- ------
1001 Ana 59.9
1002 Cara 20
1004 Ana 12| OrderId | Name | Amount |
|---|---|---|
| 1001 | Ana | 59.9 |
| 1002 | Cara | 20 |
| 1004 | Ana | 12 |
We get Ana's two orders and Cara's one. Next, estimate_data_size tells us how big a side is, and then we add hint.strategy=broadcast, which sends the small left side to every node. We run q2b.kql:
Customers
| summarize Bytes = sum(estimate_data_size(*))
Customers
| project CustomerId, Name
| join hint.strategy=broadcast kind=inner (Orders) on CustomerId
| project OrderId, Name
| order by OrderId asc$ kql -f q2b.kql
> Customers
Bytes
-----
59
> Customers
OrderId Name
------- ----
1001 Ana
1002 Cara
1003 Eli
1004 AnaOur customers are 59 bytes, so broadcast fits. If both sides are big, hint.strategy=shuffle, or a shuffle key, splits the join across the nodes by key:
// two big sides, a key with many distinct values
Clicks
| join hint.strategy=shuffle kind=inner (Sessions) on SessionId
// or name the key to shuffle by
Clicks
| join hint.shufflekey=SessionId kind=inner (Sessions) on SessionIdThe emulator is one node, so it can't show a speed difference, and its license forbids benchmarks. On a cluster, compare CPU and memory peak in .show commands-and-queries, before and after.
Question 3: A report needs to add a device's region from a small dimension table. When do you use lookup instead of join?
lookup is made for this: the big fact table on the left, the small dimension on the right. That's the opposite of join's assumption, where the small table goes on the left.
Let's add the site and region to every reading. We run q3.kql:
Readings
| lookup kind=leftouter (Devices) on DeviceId
| order by Timestamp asc, DeviceId asc$ kql -f q3.kql
> Readings
Timestamp DeviceId Temp Site Region
-------------------- -------- ---- ---- ------
2026-10-20T08:00:00Z d1 21.5 Oslo North
2026-10-20T08:00:00Z d2 19 Lyon West
2026-10-20T08:05:00Z d3 30.2 Pune South
2026-10-20T08:05:00Z d4 25
2026-10-20T08:10:00Z d1 22 Oslo North| Timestamp | DeviceId | Temp | Site | Region |
|---|---|---|---|---|
| 2026-10-20T08:00:00Z | d1 | 21.5 | Oslo | North |
| 2026-10-20T08:00:00Z | d2 | 19 | Lyon | West |
| 2026-10-20T08:05:00Z | d3 | 30.2 | Pune | South |
| 2026-10-20T08:05:00Z | d4 | 25 | ||
| 2026-10-20T08:10:00Z | d1 | 22 | Oslo | North |
Device d4 isn't in the dimension, so it's kept with empty columns: the default kind is leftouter. And the key DeviceId appears once.
Now the same with join kind=leftouter. We run q3b.kql:
Readings
| join kind=leftouter (Devices) on DeviceId
| order by Timestamp asc, DeviceId asc$ kql -f q3b.kql
> Readings
Timestamp DeviceId Temp DeviceId1 Site Region
-------------------- -------- ---- --------- ---- ------
2026-10-20T08:00:00Z d1 21.5 d1 Oslo North
2026-10-20T08:00:00Z d2 19 d2 Lyon West
2026-10-20T08:05:00Z d3 30.2 d3 Pune South
2026-10-20T08:05:00Z d4 25
2026-10-20T08:10:00Z d1 22 d1 Oslo NorthSame rows, but the key appears twice, as DeviceId and DeviceId1. And to follow join's guidance, we'd put the sides the other way round. Here is the comparison:
| lookup | join | |
|---|---|---|
| Left side | the big fact table | the smaller table |
| Right side | a small dimension, broadcast | the bigger table |
| Kinds | leftouter (default), inner | innerunique (default) and eight more |
| Key column | once | twice: DeviceId, DeviceId1 |
| Size limit | right side over several tens of MB fails | use hints for big sides |
lookup broadcasts the right side to every node, so the dimension must stay small: past several tens of megabytes, the query fails. Its only kinds are leftouter and inner.
An empty region is a data quality signal. I count readings with no region every day, so a new device never quietly falls out of a report.
Question 4: Someone needs rows from several tables with the same shape. How do union and wildcards work?
union stacks the rows of several tables. A wildcard like Logs* matches every table whose name starts with Logs, and withsource adds a column naming each row's table.
Let's count rows per source table. We run q4.kql:
union withsource=Source Logs*
| summarize Rows = count() by Source
| order by Source asc$ kql -f q4.kql
> union withsource=Source Logs*
Source Rows
------- ----
LogsApi 2
LogsJob 1
LogsWeb 3| Source | Rows |
|---|---|
| LogsApi | 2 |
| LogsJob | 1 |
| LogsWeb | 3 |
Two rows from LogsApi, one from LogsJob, three from LogsWeb.
By default, union is kind=outer: it keeps every column of every table and leaves the gaps empty. kind=inner keeps only the columns all tables share.
The trap is a type clash. LogsJob stores the duration as a real, the others as a long. We check the schema with q4b.kql:
union Logs*
| getschema
| project ColumnName, ColumnType$ kql -f q4b.kql
> union Logs*
ColumnName ColumnType
--------------- ----------
Timestamp datetime
Level string
DurationMs_long long
DurationMs_real realThe union has two columns, DurationMs_long and DurationMs_real, so a query that reads DurationMs breaks.
A missing table fails the whole query. isfuzzy=true runs with the tables that exist. We run q4c.kql, with -e so the file keeps going after the error:
union LogsWeb, LogsMobile
| count
union isfuzzy=true LogsWeb, LogsMobile
| count$ kql -e -f q4c.kql
> union LogsWeb, LogsMobile
Error: Semantic error: SEM0100: 'union' operator: Failed to resolve table expression named 'LogsMobile'
> union isfuzzy=true LogsWeb, LogsMobile
Count
-----
3
Warning: 'union' operator: Failed to resolve table expression named 'LogsMobile'The first union fails with Failed to resolve table expression named 'LogsMobile'. With isfuzzy=true we get 3 rows from LogsWeb and a warning instead.
Microsoft's advice: when you know the tables, name them instead of using a wildcard, and filter each leg before the union. A new table matching the pattern shouldn't change a report by surprise.
Question 5: A teammate asks how to find rows in one table with no match in another. Which join kind do you use?
leftanti. It returns the left rows that have no match on the right, with only the left columns. rightanti goes the other direction.
Let's find the orders nobody paid. We run q5.kql:
Orders
| join kind=leftanti (Payments) on OrderId
| project OrderId, CustomerId, Amount
| order by OrderId asc$ kql -f q5.kql
> Orders
OrderId CustomerId Amount
------- ---------- ------
1003 C005 35.5
1005 C006 80| OrderId | CustomerId | Amount |
|---|---|---|
| 1003 | C005 | 35.5 |
| 1005 | C006 | 80 |
Orders 1003 and 1005 have no payment.
Now we flip it with rightanti, and also try !in with a subquery. We run q5b.kql:
Orders
| join kind=rightanti (Payments) on OrderId
| project PaymentId, OrderId
Orders
| where OrderId !in ((Payments | project OrderId))
| project OrderId
| order by OrderId asc$ kql -f q5b.kql
> Orders
PaymentId OrderId
--------- -------
P4 1099
> Orders
OrderId
-------
1003
1005rightanti gives the payments with no order: P4, for an order 1099 that doesn't exist. Both directions matter in a reconciliation. The !in version returns the same two orders, 1003 and 1005. For a one-column filter, Microsoft's best practices prefer in over a left semi join, and !in is its anti version.
Avoid the SQL habit of a left outer join followed by a null check. It works, but it's longer, and it carries the right columns along just to throw them away.
I run both anti joins after every load, unpaid orders and orphan payments, and alert when either count jumps.
Question 6: Someone needs a login followed by an error within five minutes. How do you join on a time window?
Join on the user, then keep the errors in the five minutes after the login. For busy users, Microsoft's time-window pattern adds a time key so the join stays an equality.
Let's start with the simple version. We run q6b.kql:
Logins
| join kind=inner (AppErrors) on UserId
| where ErrorTime between (LoginTime .. LoginTime + 5min)
| project UserId, LoginTime, ErrorTime
| order by UserId asc$ kql -f q6b.kql
> Logins
UserId LoginTime ErrorTime
------ -------------------- --------------------
u1 2026-10-20T08:00:00Z 2026-10-20T08:03:00Z
u2 2026-10-20T08:10:00Z 2026-10-20T08:14:59ZIt's correct, but it pairs every login with every error of that user before the filter, which explodes for busy users.
Here is how the time-window pattern works:
- The login time is binned into buckets of half the window.
- On the error side,
rangelists every bucket the error could match, andmv-expandmakes them rows. - The join is on the user and the time key, an equality.
- A final
wherekeeps the exact window: zero to five minutes after the login.
We run q6.kql:
let lookupWindow = 5min;
let lookupBin = lookupWindow / 2.0;
Logins
| extend TimeKey = bin(LoginTime, lookupBin)
| join kind=inner (
AppErrors
| extend TimeKey = range(bin(ErrorTime - lookupWindow, lookupBin),
bin(ErrorTime, lookupBin), lookupBin)
| mv-expand TimeKey to typeof(datetime)
) on UserId, TimeKey
| where ErrorTime - LoginTime between (0min .. lookupWindow)
| project UserId, LoginTime, ErrorTime,
Gap = ErrorTime - LoginTime
| order by UserId asc$ kql -f q6.kql
> let lookupWindow = 5min;
UserId LoginTime ErrorTime Gap
------ -------------------- -------------------- --------
u1 2026-10-20T08:00:00Z 2026-10-20T08:03:00Z 00:03:00
u2 2026-10-20T08:10:00Z 2026-10-20T08:14:59Z 00:04:59| UserId | LoginTime | ErrorTime | Gap |
|---|---|---|---|
| u1 | 2026-10-20T08:00:00Z | 2026-10-20T08:03:00Z | 00:03:00 |
| u2 | 2026-10-20T08:10:00Z | 2026-10-20T08:14:59Z | 00:04:59 |
u1 failed 00:03:00 after logging in, and u2 00:04:59 after. u1's second error at 08:30 is too late, and u3's error came before the login.
Microsoft says this pattern can considerably speed up the join. The emulator can't show speed, so here we prove the rows instead: the same two as the simple version.
Scenario 7: Orders and payments arrive in separate tables and finance wants the unmatched orders every morning. How do you build it?
The core is a leftanti join, wrapped in a stored function with the day as a parameter, and a scheduled job that runs it every morning and sends the result to finance.
Here is the flow:
- Orders: one row per order
- Payments: one row per payment
- UnpaidOrders(day): stored function,
leftanti - Scheduled job: every morning
- Finance: the list, and the follow-up
The function takes one day of orders and removes every order with a payment. Payments count from the order day onward, so a payment that lands after midnight still matches. It's saved in a Finance folder. We run q7_func.kql:
.create-or-alter function with (folder="Finance",
docstring="Orders of one day with no payment")
UnpaidOrders(day:datetime) {
Orders
| where OrderTime >= day and OrderTime < day + 1d
| join kind=leftanti (
Payments
| where PaidTime >= day // late payments count too
| project OrderId
) on OrderId
}$ kql -q -f q7_func.kql
> .create-or-alter function with (folder="Finance",
ok: .create-or-alter function with (folder="Finance",Now we call it for October 20, add the customer name with a lookup, and check whether the paid orders were paid in full. We run q7_run.kql:
UnpaidOrders(datetime(2026-10-20))
| lookup kind=leftouter (Customers) on CustomerId
| project OrderId, CustomerId, Name, Amount
| order by OrderId asc
Orders
| join kind=inner (Payments
| summarize Paid = sum(Amount) by OrderId) on OrderId
| where Paid < Amount
| count$ kql -f q7_run.kql
> UnpaidOrders(datetime(2026-10-20))
OrderId CustomerId Name Amount
------- ---------- ---- ------
1003 C005 Eli 35.5
1005 C006 80
> Orders
Count
-----
0| OrderId | CustomerId | Name | Amount |
|---|---|---|---|
| 1003 | C005 | Eli | 35.5 |
| 1005 | C006 | 80 |
Order 1003 from Eli, and order 1005 from C006, who isn't in the customer table at all. The leftouter lookup keeps it with an empty name: a second finding. For the paid-in-full check, we sum the payments per order, since one order can have several, then join and compare: 0 short payments.
A Data Factory pipeline, or the report tool finance already uses, calls the function each morning. And keep each morning's list in a table, to see how long an order stayed unpaid.
Key points to remember
- The default join is innerunique: write kind=inner for SQL behavior, and always write the kind
- Big joins: shrink both sides, smaller left, broadcast for a small left, shuffle for two big ones
- lookup: facts left, a small dimension right, leftouter by default, the key once
- union with withsource and wildcards; watch type clashes and missing tables (isfuzzy)
- leftanti / rightanti for rows with no match; time-window joins use time buckets
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 #3 - Joins, unions and lookups
# 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_03_Code.py (Python 3.10 or later; kql.py itself needs no packages: it uses the REST API)
# "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:long, CustomerId:string, OrderTime:datetime,
Amount:real, Status:string)
.ingest inline into table Orders <|
1001,C001,2026-10-20T08:05:00Z,59.90,Paid
1002,C003,2026-10-20T11:03:00Z,20.00,Paid
1003,C005,2026-10-20T09:15:00Z,35.50,Pending
1004,C001,2026-10-20T12:30:00Z,12.00,Paid
1005,C006,2026-10-20T10:00:00Z,80.00,Paid
.create table Customers (CustomerId:string, Name:string, Region:string)
.ingest inline into table Customers <|
C001,Ana,North
C002,Ben,South
C003,Cara,North
C004,Dev,East
C005,Eli,West
.create table Payments (PaymentId:string, OrderId:long, PaidTime:datetime,
Amount:real)
.ingest inline into table Payments <|
P1,1001,2026-10-20T08:07:00Z,59.90
P2,1002,2026-10-20T11:05:00Z,20.00
P3,1004,2026-10-20T12:31:00Z,12.00
P4,1099,2026-10-20T09:00:00Z,5.00
.create table Devices (DeviceId:string, Site:string, Region:string)
.ingest inline into table Devices <|
d1,Oslo,North
d2,Lyon,West
d3,Pune,South
.create table Readings (Timestamp:datetime, DeviceId:string, Temp:real)
.ingest inline into table Readings <|
2026-10-20T08:00:00Z,d1,21.5
2026-10-20T08:00:00Z,d2,19
2026-10-20T08:05:00Z,d3,30.2
2026-10-20T08:05:00Z,d4,25
2026-10-20T08:10:00Z,d1,22
.create table LogsWeb (Timestamp:datetime, Level:string, DurationMs:long)
.create table LogsApi (Timestamp:datetime, Level:string, DurationMs:long)
.create table LogsJob (Timestamp:datetime, Level:string, DurationMs:real)
.ingest inline into table LogsWeb <|
2026-10-20T08:00:00Z,Info,120
2026-10-20T08:01:00Z,Error,900
2026-10-20T08:02:00Z,Info,80
.ingest inline into table LogsApi <|
2026-10-20T08:00:30Z,Info,45
2026-10-20T08:03:00Z,Error,3000
.ingest inline into table LogsJob <|
2026-10-20T02:00:00Z,Info,61000.5
.create table Logins (UserId:string, LoginTime:datetime)
.ingest inline into table Logins <|
u1,2026-10-20T08:00:00Z
u2,2026-10-20T08:10:00Z
u3,2026-10-20T09:00:00Z
.create table AppErrors (UserId:string, ErrorTime:datetime, Error:string)
.ingest inline into table AppErrors <|
u1,2026-10-20T08:03:00Z,timeout
u1,2026-10-20T08:30:00Z,timeout
u2,2026-10-20T08:14:59Z,declined
u3,2026-10-20T08:58:00Z,declined
""")
run(KQL + ' -q -f setup.kql')
# Step 1: The default join kind (innerunique): Ana's two orders become one row
write('q1.kql', """\
Orders
| join (Customers) on CustomerId
| project OrderId, CustomerId, Name
| order by OrderId asc
""")
run(KQL + ' -f q1.kql')
# Step 2: kind=inner: all four matching orders
write('q1b.kql', """\
Orders
| join kind=inner (Customers) on CustomerId
| project OrderId, CustomerId, Name
| order by OrderId asc
""")
run(KQL + ' -f q1b.kql')
# Step 3: Shrink both sides before the join, smaller side on the left
write('q2.kql', """\
Customers
| where Region == "North"
| project CustomerId, Name
| join kind=inner (
Orders
| where OrderTime between (datetime(2026-10-20) .. 1d)
| project OrderId, CustomerId, Amount
) on CustomerId
| project OrderId, Name, Amount
| order by OrderId asc
""")
run(KQL + ' -f q2.kql')
# Step 4: The size of the left side, and a broadcast join hint
write('q2b.kql', """\
Customers
| summarize Bytes = sum(estimate_data_size(*))
Customers
| project CustomerId, Name
| join hint.strategy=broadcast kind=inner (Orders) on CustomerId
| project OrderId, Name
| order by OrderId asc
""")
run(KQL + ' -f q2b.kql')
# Step 5: lookup: the fact table on the left, the device dimension on the right
write('q3.kql', """\
Readings
| lookup kind=leftouter (Devices) on DeviceId
| order by Timestamp asc, DeviceId asc
""")
run(KQL + ' -f q3.kql')
# Step 6: The same with join kind=leftouter: the key twice
write('q3b.kql', """\
Readings
| join kind=leftouter (Devices) on DeviceId
| order by Timestamp asc, DeviceId asc
""")
run(KQL + ' -f q3b.kql')
# Step 7: union with a wildcard and withsource
write('q4.kql', """\
union withsource=Source Logs*
| summarize Rows = count() by Source
| order by Source asc
""")
run(KQL + ' -f q4.kql')
# Step 8: A type clash: DurationMs long in two tables, real in one
write('q4b.kql', """\
union Logs*
| getschema
| project ColumnName, ColumnType
""")
run(KQL + ' -f q4b.kql')
# Step 9: A missing table fails the union; isfuzzy=true runs with a warning
write('q4c.kql', """\
union LogsWeb, LogsMobile
| count
union isfuzzy=true LogsWeb, LogsMobile
| count
""")
run(KQL + ' -e -f q4c.kql', allow_error=True)
# Step 10: leftanti: orders with no payment
write('q5.kql', """\
Orders
| join kind=leftanti (Payments) on OrderId
| project OrderId, CustomerId, Amount
| order by OrderId asc
""")
run(KQL + ' -f q5.kql')
# Step 11: rightanti: payments with no order; !in as an alternative
write('q5b.kql', """\
Orders
| join kind=rightanti (Payments) on OrderId
| project PaymentId, OrderId
Orders
| where OrderId !in ((Payments | project OrderId))
| project OrderId
| order by OrderId asc
""")
run(KQL + ' -f q5b.kql')
# Step 12: The simple time-window join: join on the user, then filter the time
write('q6b.kql', """\
Logins
| join kind=inner (AppErrors) on UserId
| where ErrorTime between (LoginTime .. LoginTime + 5min)
| project UserId, LoginTime, ErrorTime
| order by UserId asc
""")
run(KQL + ' -f q6b.kql')
# Step 13: The time-window join pattern: time buckets, range and mv-expand, then the exact window
write('q6.kql', """\
let lookupWindow = 5min;
let lookupBin = lookupWindow / 2.0;
Logins
| extend TimeKey = bin(LoginTime, lookupBin)
| join kind=inner (
AppErrors
| extend TimeKey = range(bin(ErrorTime - lookupWindow, lookupBin),
bin(ErrorTime, lookupBin), lookupBin)
| mv-expand TimeKey to typeof(datetime)
) on UserId, TimeKey
| where ErrorTime - LoginTime between (0min .. lookupWindow)
| project UserId, LoginTime, ErrorTime,
Gap = ErrorTime - LoginTime
| order by UserId asc
""")
run(KQL + ' -f q6.kql')
# Step 14: A stored function for the unpaid orders of one day
write('q7_func.kql', """\
.create-or-alter function with (folder="Finance",
docstring="Orders of one day with no payment")
UnpaidOrders(day:datetime) {
Orders
| where OrderTime >= day and OrderTime < day + 1d
| join kind=leftanti (
Payments
| where PaidTime >= day // late payments count too
| project OrderId
) on OrderId
}
""")
run(KQL + ' -q -f q7_func.kql')
# Step 15: The unpaid orders of October 20, with the customer name, and a paid-in-full check
write('q7_run.kql', """\
UnpaidOrders(datetime(2026-10-20))
| lookup kind=leftouter (Customers) on CustomerId
| project OrderId, CustomerId, Name, Amount
| order by OrderId asc
Orders
| join kind=inner (Payments
| summarize Paid = sum(Amount) by OrderId) on OrderId
| where Paid < Amount
| count
""")
run(KQL + ' -f q7_run.kql')
Part 4: Time Series and Analytics
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.