In this post we are going to cover KQL query essentials. 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: support asks for the top ten customers with the most failed requests today and their error rate.
These are the operators we write every day: where, project, extend, summarize, has, arg_max, let and stored functions. Interviewers ask about them because small details decide whether a query is right and fast: which filter goes first, which string operator uses the index, and where integer division quietly changes a number.
Part 2 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 needs the last hour of errors from a logs table. How do you filter by time and value in KQL?
- Question 2: Someone asks the difference between project, extend and project-away. When do you use each?
- Question 3: A report needs counts per hour per service. How does summarize with bin work?
- Question 4: A teammate asks the difference between has, contains and ==. Which one do you use and why?
- Question 5: Someone needs the latest row per device. How do you use arg_max?
- Question 6: A query repeats the same filter in three places. How do let statements and stored functions help?
- Scenario 7: Support asks for the top ten customers with the most failed requests today and their error rate. How do you write it?
- The full script
The short answer
Filter time first, then values. project shapes, extend adds, project-away drops. summarize ... by bin() groups by time, has uses the term index while contains scans, arg_max returns the latest whole row, and let and stored functions name logic once.
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 two small invented tables: Requests, 18 web requests loaded from three CSV files with .ingest into, and Telemetry, 8 device readings loaded with .ingest inline. Both are for exploration and prototyping, not production. kql is a small helper that sends a query to the emulator and prints the result as a text table. Queries that need a live table, such as ago(1h), are shown as code.
2026-10-20T08:05:00Z,Info,checkout,C001,120,order placed
2026-10-20T08:12:00Z,Error,payments,C002,3050,db-timeout while charging card
2026-10-20T08:20:00Z,Info,catalog,C003,45,search ok
2026-10-20T08:41:00Z,Warning,checkout,C001,900,slow response from payments.api
2026-10-20T08:55:00Z,Error,checkout,C004,1500,Error: payment declined
2026-10-20T09:02:00Z,Info,catalog,C002,50,search ok2026-10-20T09:10:00Z,Error,payments,C002,3010,db-timeout while charging card
2026-10-20T09:15:00Z,Info,checkout,C005,130,order placed
2026-10-20T09:31:00Z,Error,payments,C001,2900,ERROR connecting to db.primary
2026-10-20T09:47:00Z,Info,catalog,C003,40,search ok
2026-10-20T11:03:00Z,Info,checkout,C003,110,order placed
2026-10-20T11:09:00Z,Error,checkout,C002,1700,user error: invalid coupon2026-10-20T11:20:00Z,Warning,catalog,C005,700,cache miss
2026-10-20T11:44:00Z,Error,payments,C004,3100,err code 42 from gateway
2026-10-20T11:58:00Z,Info,payments,C005,200,refund issued
2026-10-20T12:06:00Z,Error,payments,C002,2950,db-timeout while charging card
2026-10-20T12:30:00Z,Info,checkout,C001,125,order placed
2026-10-20T12:52:00Z,Info,catalog,C004,48,search ok.create table Requests (Timestamp:datetime, Level:string, Service:string,
CustomerId:string, DurationMs:long, Message:string)
.ingest into table Requests (@"/kustodata/requests_part1.csv") with (format="csv")
.ingest into table Requests (@"/kustodata/requests_part2.csv") with (format="csv")
.ingest into table Requests (@"/kustodata/requests_part3.csv") with (format="csv")
.create table Telemetry (Timestamp:datetime, DeviceId:string, Temp:real,
Battery:long, Status:string)
.ingest inline into table Telemetry <|
2026-10-20T08:00:00Z,dev-01,21.4,98,ok
2026-10-20T08:00:00Z,dev-02,19.8,76,ok
2026-10-20T08:00:00Z,dev-03,23.1,54,ok
2026-10-20T08:15:00Z,dev-01,21.9,97,ok
2026-10-20T08:15:00Z,dev-02,20.4,75,ok
2026-10-20T08:30:00Z,dev-01,22.6,97,ok
2026-10-20T08:30:00Z,dev-03,27.9,41,warn
2026-10-20T08:45:00Z,dev-02,20.1,74,okQuestion 1: A teammate needs the last hour of errors from a logs table. How do you filter by time and value in KQL?
Two where clauses, time first. Put the time filter right after the table, because the engine indexes datetime columns and can skip whole shards outside the window. Then filter the value.
Our sample data is fixed in October, so a let pins now to 12:10 on October 20. We run q1.kql:
let now_ = datetime(2026-10-20 12:10); // pinned "now"
Requests
| where Timestamp between (now_ - 1h .. now_)
| where Level == "Error"
| project Timestamp, Service, Message
| order by Timestamp asc$ kql -f q1.kql
> let now_ = datetime(2026-10-20 12:10); // pinned "now"
Timestamp Service Message
-------------------- -------- ------------------------------
2026-10-20T11:44:00Z payments err code 42 from gateway
2026-10-20T12:06:00Z payments db-timeout while charging card| Timestamp | Service | Message |
|---|---|---|
| 2026-10-20T11:44:00Z | payments | err code 42 from gateway |
| 2026-10-20T12:06:00Z | payments | db-timeout while charging card |
The last hour holds two payments errors: err code 42 from gateway at 11:44, and db-timeout while charging card at 12:06. The coupon error at 11:09 is just outside the window.
On a live table nothing is newer than now, so where Timestamp > ago(1h) does it:
// on a live table
Requests
| where Timestamp > ago(1h)
| where Level == "Error"
| project Timestamp, Service, Message
| order by Timestamp descTwo details matter here:
==is case-sensitive, so the level must match exactly.- Filter on table columns, not calculated ones: compare
Timestampitself, don't wrap it in a function first.
On a table I don't know yet, I start with take 10 or a count before running anything big.
Question 2: Someone asks the difference between project, extend and project-away. When do you use each?
project keeps only the columns you list, and can rename or compute them. extend keeps every column and adds new ones. project-away drops the columns you name and keeps the rest. project-keep and project-rename complete the family:
| Operator | What it does | Columns after it |
|---|---|---|
| project | keeps only the listed columns; can rename and compute | just the list |
| extend | adds computed columns | all of them, plus the new ones |
| project-away | drops the listed columns | all the others |
| project-keep | keeps the listed columns | in the table's own order |
| project-rename | renames columns | same columns, new names |
Let's try two of them on the error rows. extend adds Seconds and a Slow flag, and project-away drops Message and CustomerId. We run q2.kql:
Requests
| where Level == "Error"
| extend Seconds = DurationMs / 1000.0,
Slow = DurationMs > 2000
| project-away Message, CustomerId
| order by Timestamp asc$ kql -f q2.kql
> Requests
Timestamp Level Service DurationMs Seconds Slow
-------------------- ----- -------- ---------- ------- -----
2026-10-20T08:12:00Z Error payments 3050 3.05 true
2026-10-20T08:55:00Z Error checkout 1500 1.5 false
2026-10-20T09:10:00Z Error payments 3010 3.01 true
2026-10-20T09:31:00Z Error payments 2900 2.9 true
2026-10-20T11:09:00Z Error checkout 1700 1.7 false
2026-10-20T11:44:00Z Error payments 3100 3.1 true
2026-10-20T12:06:00Z Error payments 2950 2.95 true| Timestamp | Level | Service | DurationMs | Seconds | Slow |
|---|---|---|---|---|---|
| 2026-10-20T08:12:00Z | Error | payments | 3050 | 3.05 | true |
| 2026-10-20T08:55:00Z | Error | checkout | 1500 | 1.5 | false |
| 2026-10-20T09:10:00Z | Error | payments | 3010 | 3.01 | true |
| 2026-10-20T09:31:00Z | Error | payments | 2900 | 2.9 | true |
| 2026-10-20T11:09:00Z | Error | checkout | 1700 | 1.7 | false |
| 2026-10-20T11:44:00Z | Error | payments | 3100 | 3.1 | true |
| 2026-10-20T12:06:00Z | Error | payments | 2950 | 2.95 | true |
Everything else stays in the table's order, with the new columns at the end: the first error is 3050 ms, 3.05 seconds, Slow is true.
When do I use each? extend for derived columns, project at the end for the shape a report needs, and project-away when a table is wide and I only want to drop a few columns.
Now the math. A long divided by a long is integer division. We run q2b.kql on the checkout rows:
Requests
| where Service == "checkout"
| project Timestamp, DurationMs,
Sec = DurationMs / 1000, Seconds = DurationMs / 1000.0
| order by Timestamp asc$ kql -f q2b.kql
> Requests
Timestamp DurationMs Sec Seconds
-------------------- ---------- --- -------
2026-10-20T08:05:00Z 120 0 0.12
2026-10-20T08:41:00Z 900 0 0.9
2026-10-20T08:55:00Z 1500 1 1.5
2026-10-20T09:15:00Z 130 0 0.13
2026-10-20T11:03:00Z 110 0 0.11
2026-10-20T11:09:00Z 1700 1 1.7
2026-10-20T12:30:00Z 125 0 0.125120 ms divided by 1000 gives 0. Divided by 1000.0, we get 0.12.
On wide tables, project before a join or an arg_max with *: fewer columns means less data to carry and return.
Question 3: A report needs counts per hour per service. How does summarize with bin work?
summarize groups rows by the keys after by, and runs the aggregates once per group. bin(Timestamp, 1h) rounds each timestamp down to the hour, so times turn into hourly buckets we can group by.
Let's count all requests, and the errors with countif, by hour and service, ordered by both keys. We run q3.kql:
Requests
| summarize Requests = count(),
Errors = countif(Level == "Error")
by bin(Timestamp, 1h), Service
| order by Timestamp asc, Service asc$ kql -f q3.kql
> Requests
Timestamp Service Requests Errors
-------------------- -------- -------- ------
2026-10-20T08:00:00Z catalog 1 0
2026-10-20T08:00:00Z checkout 3 1
2026-10-20T08:00:00Z payments 1 1
2026-10-20T09:00:00Z catalog 2 0
2026-10-20T09:00:00Z checkout 1 0
2026-10-20T09:00:00Z payments 2 2
2026-10-20T11:00:00Z catalog 1 0
2026-10-20T11:00:00Z checkout 2 1
2026-10-20T11:00:00Z payments 2 1
2026-10-20T12:00:00Z catalog 1 0
2026-10-20T12:00:00Z checkout 1 0
2026-10-20T12:00:00Z payments 1 1At 08:00, checkout had 3 requests and 1 error, and payments had 1 request, which failed.
Now look at the hours: 08:00, 09:00, 11:00 and 12:00. There's no 10:00 group, because no request arrived then, and summarize only returns groups that exist. For a chart without gaps, make-series fills them; that is in the next part of this series.
Two habits:
- Name the aggregates: or you get
count_ - Bin explicitly: the automatic hourly bins for datetime columns are no longer supported
One-minute bins over a month: over 43,000 buckets per service. Match the bin to the chart.
Question 4: A teammate asks the difference between has, contains and ==. Which one do you use and why?
has matches a whole term and uses the term index. contains matches any substring and scans. == is exact and case-sensitive; =~ ignores case.
Kusto indexes string columns too. It splits text into terms, runs of letters and digits, and indexes terms of three characters or more. That is the index has can use.
Let's take every message that contains err and test has on it. We run q4.kql:
Requests
| where Message contains "err"
| project Message,
HasErr = Message has "err",
HasError = Message has "error"
| order by Message asc$ kql -f q4.kql
> Requests
Message HasErr HasError
------------------------------ ------ --------
ERROR connecting to db.primary false true
Error: payment declined false true
err code 42 from gateway true false
user error: invalid coupon false true| Message | HasErr | HasError |
|---|---|---|
| ERROR connecting to db.primary | false | true |
| Error: payment declined | false | true |
| err code 42 from gateway | true | false |
| user error: invalid coupon | false | true |
Four messages contain the letters err, but has "err" is true for only one, err code 42 from gateway. In the others, err is part of the longer term error, which has "error" matches, in any case.
Now the counts over all 18 rows. We run q4b.kql:
Requests
| summarize HasError = countif(Message has "error"),
HasErr = countif(Message has "err"),
ContainsErr = countif(Message contains "err"),
LevelEq = countif(Level == "error"),
LevelEqi = countif(Level =~ "error")$ kql -f q4b.kql
> Requests
HasError HasErr ContainsErr LevelEq LevelEqi
-------- ------ ----------- ------- --------
3 1 4 0 7| HasError | HasErr | ContainsErr | LevelEq | LevelEqi |
|---|---|---|---|---|
| 3 | 1 | 4 | 0 | 7 |
has "error" gives 3, has "err" 1, contains "err" 4. Level == "error" in lower case gives 0, because it's case-sensitive, and =~ finds 7.
So I use has for words in log text, == for exact values, and contains only when I truly need a substring. Microsoft says has works faster than contains.
A term shorter than three characters isn't indexed, so that search scans. And has won't find part of a word, by design.
Question 5: Someone needs the latest row per device. How do you use arg_max?
arg_max finds, per group, the row where an expression is largest, and returns that row's other columns too. summarize arg_max(Timestamp, *) by DeviceId gives the whole latest row per device, in one pass.
First the naive way, max(Timestamp) by device. We run q5a.kql:
Telemetry
| summarize Last = max(Timestamp) by DeviceId
| order by DeviceId asc$ kql -f q5a.kql
> Telemetry
DeviceId Last
-------- --------------------
dev-01 2026-10-20T08:30:00Z
dev-02 2026-10-20T08:45:00Z
dev-03 2026-10-20T08:30:00ZIt gives the latest time, but the temperature, battery and status are gone, and we'd need a join back to get them. Now with arg_max. We run q5.kql:
Telemetry
| summarize arg_max(Timestamp, *) by DeviceId
| order by DeviceId asc$ kql -f q5.kql
> Telemetry
DeviceId Timestamp Temp Battery Status
-------- -------------------- ---- ------- ------
dev-01 2026-10-20T08:30:00Z 22.6 97 ok
dev-02 2026-10-20T08:45:00Z 20.1 74 ok
dev-03 2026-10-20T08:30:00Z 27.9 41 warn| DeviceId | Timestamp | Temp | Battery | Status |
|---|---|---|---|---|
| dev-01 | 2026-10-20T08:30:00Z | 22.6 | 97 | ok |
| dev-02 | 2026-10-20T08:45:00Z | 20.1 | 74 | ok |
| dev-03 | 2026-10-20T08:30:00Z | 27.9 | 41 | warn |
dev-01 at 08:30 with 22.6 degrees, dev-02 at 08:45, and dev-03 at 08:30 with status warn. The key comes first, then the timestamp, then the rest.
Instead of *, you can list columns, like Temp and Status, to keep the result narrow. And if two rows share the latest time, don't rely on which one you get: dedupe first.
A latest-status dashboard runs this all day, so I put it in a materialized view with arg_max. That is part 6 of this series.
Question 6: A query repeats the same filter in three places. How do let statements and stored functions help?
Inside one query, a let statement names the filter once. When many queries need it, a stored function saves it in the database, with parameters.
Let's start with let. errors is the error rows and slow is 2000 ms, and the query body uses both. We run q6.kql:
let errors = Requests | where Level == "Error";
let slow = 2000;
errors
| where DurationMs > slow
| summarize SlowErrors = count() by Service
| order by Service asc$ kql -f q6.kql
> let errors = Requests | where Level == "Error";
Service SlowErrors
-------- ----------
payments 5payments has 5 slow errors.
Now the stored function. .create-or-alter function saves ErrorsFor in the database, with a folder, a docstring, and a parameter with a default. Then anyone calls it like a table. We run q6_func.kql, then q6_use.kql:
.create-or-alter function with (folder="Reports",
docstring="Error rows for one service")
ErrorsFor(svc:string, minMs:long = 0) {
Requests
| where Level == "Error" and Service == svc
| where DurationMs > minMs
}ErrorsFor("payments")
| count
ErrorsFor("payments", 3000)
| project Timestamp, DurationMs
| order by Timestamp asc
.show functions
| project Name, Parameters, Folder$ kql -q -f q6_func.kql
> .create-or-alter function with (folder="Reports",
ok: .create-or-alter function with (folder="Reports",
$ kql -f q6_use.kql
> ErrorsFor("payments")
Count
-----
5
> ErrorsFor("payments", 3000)
Timestamp DurationMs
-------------------- ----------
2026-10-20T08:12:00Z 3050
2026-10-20T09:10:00Z 3010
2026-10-20T11:44:00Z 3100
> .show functions
Name Parameters Folder
--------- -------------------------- -------
ErrorsFor (svc:string, minMs:long=0) ReportsErrorsFor("payments") returns 5 rows. With a minimum of 3000 ms, 3. .show functions lists it with its parameters, (svc:string, minMs:long=0), and the folder Reports.
One subtlety: let binds a name to a calculation, not to its value. Reference it twice and it can run twice, so wrap heavy reuse in materialize(), or use toscalar() for a single value.
Functions are the contract for dashboards. Creating one needs Database User, altering it needs Function Admin, and the scripts live in source control.
Scenario 7: Support asks for the top ten customers with the most failed requests today and their error rate. How do you write it?
Filter to today first, then summarize per customer: the total with count(), and the failures with countif(). Then compute the rate with real division, and sort with a tie-breaker.
With today pinned to October 20, we run q7.kql:
let today = datetime(2026-10-20); // pinned "today"
Requests
| where Timestamp >= today and Timestamp < today + 1d
| summarize Total = count(),
Failed = countif(Level == "Error") by CustomerId
| extend ErrorRate = round(100.0 * Failed / Total, 1)
| order by Failed desc, CustomerId asc
| take 10$ kql -f q7.kql
> let today = datetime(2026-10-20); // pinned "today"
CustomerId Total Failed ErrorRate
---------- ----- ------ ---------
C002 5 4 80
C004 3 2 66.7
C001 4 1 25
C003 3 0 0
C005 3 0 0| CustomerId | Total | Failed | ErrorRate |
|---|---|---|---|
| C002 | 5 | 4 | 80 |
| C004 | 3 | 2 | 66.7 |
| C001 | 4 | 1 | 25 |
| C003 | 3 | 0 | 0 |
| C005 | 3 | 0 | 0 |
C002 has 4 failures out of 5, 80 percent. C004 has 2 of 3, 66.7, and C001 1 of 4. Note the 100.0: without it, integer division gives 66, not 66.7.
C003 and C005 both have 0 failures: a tie. Without a second sort key, their order can change from run to run. So I order by Failed desc, then CustomerId, and take 10.
Why not top 10 by Failed? top takes only one sort expression. Let's give it a second one with q7_top.kql:
Requests
| summarize Failed = countif(Level == "Error") by CustomerId
| top 10 by Failed desc, CustomerId asc$ kql -f q7_top.kql
> Requests
Error: Syntax error: SYN0002: The incomplete fragment is unexpected. [line:position=3:24]The engine returns Syntax error: SYN0002: The incomplete fragment is unexpected.
On a live table, today is startofday(now()):
// on a live table
let today = startofday(now());
Requests
| where Timestamp >= today
| summarize Total = count(),
Failed = countif(Level == "Error") by CustomerId
| where Total >= 20 // a minimum volume
| extend ErrorRate = round(100.0 * Failed / Total, 1)
| order by Failed desc, CustomerId asc
| take 10One failure out of one request is 100 percent, and that customer isn't the real problem. So the live version keeps only customers with at least 20 requests.
Key points to remember
- Time filter first, right after the table, then the value filter; pin now on sample data
- project shapes, extend adds, project-away drops; long / long is integer division
- summarize ... by bin(Timestamp, 1h): only groups that exist come back
- has = whole term, uses the index; contains scans; == is case-sensitive
- arg_max(Timestamp, *) for the latest row; let and stored functions name logic once
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 #2 - KQL query essentials
# 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_02_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('requests_part1.csv', """\
2026-10-20T08:05:00Z,Info,checkout,C001,120,order placed
2026-10-20T08:12:00Z,Error,payments,C002,3050,db-timeout while charging card
2026-10-20T08:20:00Z,Info,catalog,C003,45,search ok
2026-10-20T08:41:00Z,Warning,checkout,C001,900,slow response from payments.api
2026-10-20T08:55:00Z,Error,checkout,C004,1500,Error: payment declined
2026-10-20T09:02:00Z,Info,catalog,C002,50,search ok
""")
write('requests_part2.csv', """\
2026-10-20T09:10:00Z,Error,payments,C002,3010,db-timeout while charging card
2026-10-20T09:15:00Z,Info,checkout,C005,130,order placed
2026-10-20T09:31:00Z,Error,payments,C001,2900,ERROR connecting to db.primary
2026-10-20T09:47:00Z,Info,catalog,C003,40,search ok
2026-10-20T11:03:00Z,Info,checkout,C003,110,order placed
2026-10-20T11:09:00Z,Error,checkout,C002,1700,user error: invalid coupon
""")
write('requests_part3.csv', """\
2026-10-20T11:20:00Z,Warning,catalog,C005,700,cache miss
2026-10-20T11:44:00Z,Error,payments,C004,3100,err code 42 from gateway
2026-10-20T11:58:00Z,Info,payments,C005,200,refund issued
2026-10-20T12:06:00Z,Error,payments,C002,2950,db-timeout while charging card
2026-10-20T12:30:00Z,Info,checkout,C001,125,order placed
2026-10-20T12:52:00Z,Info,catalog,C004,48,search ok
""")
write('setup.kql', """\
.create table Requests (Timestamp:datetime, Level:string, Service:string,
CustomerId:string, DurationMs:long, Message:string)
.ingest into table Requests (@"/kustodata/requests_part1.csv") with (format="csv")
.ingest into table Requests (@"/kustodata/requests_part2.csv") with (format="csv")
.ingest into table Requests (@"/kustodata/requests_part3.csv") with (format="csv")
.create table Telemetry (Timestamp:datetime, DeviceId:string, Temp:real,
Battery:long, Status:string)
.ingest inline into table Telemetry <|
2026-10-20T08:00:00Z,dev-01,21.4,98,ok
2026-10-20T08:00:00Z,dev-02,19.8,76,ok
2026-10-20T08:00:00Z,dev-03,23.1,54,ok
2026-10-20T08:15:00Z,dev-01,21.9,97,ok
2026-10-20T08:15:00Z,dev-02,20.4,75,ok
2026-10-20T08:30:00Z,dev-01,22.6,97,ok
2026-10-20T08:30:00Z,dev-03,27.9,41,warn
2026-10-20T08:45:00Z,dev-02,20.1,74,ok
""")
run(KQL + ' -q -f setup.kql')
# Step 1: The last hour of errors, with "now" pinned to 12:10 on the sample day
write('q1.kql', """\
let now_ = datetime(2026-10-20 12:10); // pinned "now"
Requests
| where Timestamp between (now_ - 1h .. now_)
| where Level == "Error"
| project Timestamp, Service, Message
| order by Timestamp asc
""")
run(KQL + ' -f q1.kql')
# Step 2: extend adds columns, project-away drops columns
write('q2.kql', """\
Requests
| where Level == "Error"
| extend Seconds = DurationMs / 1000.0,
Slow = DurationMs > 2000
| project-away Message, CustomerId
| order by Timestamp asc
""")
run(KQL + ' -f q2.kql')
# Step 3: Integer division: a long divided by a long
write('q2b.kql', """\
Requests
| where Service == "checkout"
| project Timestamp, DurationMs,
Sec = DurationMs / 1000, Seconds = DurationMs / 1000.0
| order by Timestamp asc
""")
run(KQL + ' -f q2b.kql')
# Step 4: Requests and errors per hour per service with summarize and bin
write('q3.kql', """\
Requests
| summarize Requests = count(),
Errors = countif(Level == "Error")
by bin(Timestamp, 1h), Service
| order by Timestamp asc, Service asc
""")
run(KQL + ' -f q3.kql')
# Step 5: has matches whole terms, contains matches substrings
write('q4.kql', """\
Requests
| where Message contains "err"
| project Message,
HasErr = Message has "err",
HasError = Message has "error"
| order by Message asc
""")
run(KQL + ' -f q4.kql')
# Step 6: Counting has, contains, == and =~ over all rows
write('q4b.kql', """\
Requests
| summarize HasError = countif(Message has "error"),
HasErr = countif(Message has "err"),
ContainsErr = countif(Message contains "err"),
LevelEq = countif(Level == "error"),
LevelEqi = countif(Level =~ "error")
""")
run(KQL + ' -f q4b.kql')
# Step 7: max(Timestamp) per device: the time only
write('q5a.kql', """\
Telemetry
| summarize Last = max(Timestamp) by DeviceId
| order by DeviceId asc
""")
run(KQL + ' -f q5a.kql')
# Step 8: arg_max(Timestamp, *) per device: the whole latest row
write('q5.kql', """\
Telemetry
| summarize arg_max(Timestamp, *) by DeviceId
| order by DeviceId asc
""")
run(KQL + ' -f q5.kql')
# Step 9: let statements name a filter and a value once
write('q6.kql', """\
let errors = Requests | where Level == "Error";
let slow = 2000;
errors
| where DurationMs > slow
| summarize SlowErrors = count() by Service
| order by Service asc
""")
run(KQL + ' -f q6.kql')
# Step 10: A stored function with a folder, a docstring and a default parameter
write('q6_func.kql', """\
.create-or-alter function with (folder="Reports",
docstring="Error rows for one service")
ErrorsFor(svc:string, minMs:long = 0) {
Requests
| where Level == "Error" and Service == svc
| where DurationMs > minMs
}
""")
write('q6_use.kql', """\
ErrorsFor("payments")
| count
ErrorsFor("payments", 3000)
| project Timestamp, DurationMs
| order by Timestamp asc
.show functions
| project Name, Parameters, Folder
""")
run(KQL + ' -q -f q6_func.kql')
run(KQL + ' -f q6_use.kql')
# Step 11: Top ten customers by failed requests today, with the error rate and a tie-breaker
write('q7.kql', """\
let today = datetime(2026-10-20); // pinned "today"
Requests
| where Timestamp >= today and Timestamp < today + 1d
| summarize Total = count(),
Failed = countif(Level == "Error") by CustomerId
| extend ErrorRate = round(100.0 * Failed / Total, 1)
| order by Failed desc, CustomerId asc
| take 10
""")
run(KQL + ' -f q7.kql')
# Step 12: top accepts one sort expression: a second one is a syntax error
write('q7_top.kql', """\
Requests
| summarize Failed = countif(Level == "Error") by CustomerId
| top 10 by Failed desc, CustomerId asc
""")
run(KQL + ' -f q7_top.kql', expect_error=True) # EXPECTED TO FAIL: the video shows this error
Part 3: Joins, Unions and Lookups
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.