In this post we are going to cover performance and query tuning 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: a Power BI dashboard on Azure Data Explorer is slow for fifty users at month end.
Azure Data Explorer is fast when a query reads little data, and slow when it reads everything. Tuning is mostly two things: find out what the engine actually did, then reduce the data it has to touch. Interviewers ask about it because slow dashboards and failed queries are the first things users complain about.
Part 7 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 query that used to take seconds now takes minutes. How do you find out why?
- Question 2: Someone asks what the most important KQL performance habits are. What do you tell them?
- Question 3: A teammate asks how the partitioning policy helps. When do you use it?
- Question 4: A query uses the same subquery twice. How does materialize help?
- Question 5: Someone asks what hint.strategy=shuffle does for summarize and join. When do you use it?
- Question 6: Users hit query limits like result set too large or low memory errors. What do you do?
- Scenario 7: A Power BI dashboard on Azure Data Explorer is slow for fifty users at month end. How do you fix it?
- The full script
The short answer
Start from what the engine did: .show queries and .show commands-and-queries (scanned extents, CPU, memory, cache). Reduce data first (datetime filter first, has, few columns), use materialize(), shuffle and partitioning only where they fit, respect the query limits, and serve dashboards from materialized views.
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. mk_logs.py writes five days of invented request logs, 2,000 rows a day in one file per day, and setup.kql creates the Logs table and ingests each file separately. What only a cluster in Azure can show, such as real timings, the partitioning policy at work, sign-in and networking, is shown as code.
# logs_1001.csv .. logs_1005.csv: 2,000 invented requests a day
from datetime import datetime, timedelta
SERVICES = ["api", "checkout", "payments", "search"]
for d in range(5):
day = datetime(2026, 10, 1 + d)
rows = []
for i in range(2000):
ts = day + timedelta(seconds=43 * i)
svc = SERVICES[i % 4]
cust = "C%04d" % ((i * 37 + d * 11) % 800 + 1)
ms = 20 + (i * 53) % 400
if svc == "payments" and i % 9 == 2:
lvl, ms, msg = "Error", ms + 2500, "timeout calling bank gateway"
elif i % 25 == 0:
lvl, msg = "Error", "upstream returned 503"
elif i % 7 == 3:
lvl, msg = "Warning", "slow response from %s" % svc
else:
lvl, msg = "Info", "GET /%s/v2 ok" % svc
rows.append("%s,%s,%s,%s,%d,%s" % (ts.strftime("%Y-%m-%dT%H:%M:%SZ"),
svc, lvl, cust, ms, msg))
with open("logs_10%02d.csv" % (1 + d), "w") as f:
f.write("\n".join(rows) + "\n")
print("5 files, 10000 rows").create table Logs (Timestamp:datetime, Service:string, Level:string,
CustomerId:string, DurationMs:long, Message:string)
.ingest into table Logs (@"/kustodata/logs_1001.csv")
with (format='csv')
.ingest into table Logs (@"/kustodata/logs_1002.csv")
with (format='csv')
.ingest into table Logs (@"/kustodata/logs_1003.csv")
with (format='csv')
.ingest into table Logs (@"/kustodata/logs_1004.csv")
with (format='csv')
.ingest into table Logs (@"/kustodata/logs_1005.csv")
with (format='csv')Question 1: A query that used to take seconds now takes minutes. How do you find out why?
I look at what the engine did, not the clock. .show queries, and .show commands-and-queries, which keeps 30 days, give each run's duration, CPU, memory peak, scanned extents and cache statistics.
Let's make a case. The payments error report for October 5 filtered on the timestamp. A teammate "cleaned it up" with format_datetime. We run both versions.
// v1: the payments error report as it was
Logs
| where Timestamp between (datetime(2026-10-05) .. 1d)
| where Service == 'payments' and Level == 'Error'
| summarize Errors = count(), AvgMs = round(avg(DurationMs))
// v2: "cleaned up" with a text date
Logs
| where format_datetime(Timestamp, 'yyyy-MM-dd') == '2026-10-05'
| where Service == 'payments' and Level == 'Error'
| summarize Errors = count(), AvgMs = round(avg(DurationMs))$ kql -f report.kql
> Logs
Errors AvgMs
------ -----
74 2118
> Logs
Errors AvgMs
------ -----
74 2118Same answer from both: 74 errors, with an average of 2118 ms.
.show queries fills in the background, so the full script waits until both runs are listed. Then we read the scan statistics.
.show queries
| where Text has "Level == 'Error'" and Text has 'payments'
| extend s = ScannedExtentsStatistics
| project Filter = iff(Text has 'format_datetime', 'format_datetime',
'between'),
ScannedExtents = tolong(s.ScannedExtentsCount),
TotalExtents = tolong(s.TotalExtentsCount),
ScannedRows = tolong(s.ScannedRowsCount), State
| order by ScannedExtents asc$ kql -f show_queries.kql
> .show queries
Filter ScannedExtents TotalExtents ScannedRows State
--------------- -------------- ------------ ----------- ---------
between 1 5 2000 Completed
format_datetime 5 5 10000 Completed| Filter | ScannedExtents | TotalExtents | ScannedRows | State |
|---|---|---|---|---|
| between | 1 | 5 | 2000 | Completed |
| format_datetime | 5 | 5 | 10000 | Completed |
That tells the story. The original, between, scanned 1 of 5 extents and 2000 rows. The rewrite scanned all 5 and 10000 rows, because comparing text stopped the engine from skipping extents by time. These are the emulator's own statistics, not cluster numbers. On a cluster, compare Duration and TotalCpu too:
// on a cluster: 30 days of finished commands and queries
.show commands-and-queries
| where StartedOn > ago(7d) and Text has 'payments'
| project StartedOn, Duration, TotalCpu, MemoryPeak,
ScannedExtentsStatistics, CacheStatistics, WorkloadGroup
| order by Duration descThe usual causes are these:
- The data grew: and the time filter is missing or unusable
- Cold cache: the data fell out of the hot cache
- Concurrency: more queries at once, or throttling
- A changed query: contains instead of has, a bigger join
Compare a slow run and a fast run of the same query side by side, and fix the query before you scale the cluster.
Question 2: Someone asks what the most important KQL performance habits are. What do you tell them?
Reduce the data first. Filter on the datetime column right after the table, then on other table columns, and only then parse, join or summarize. Select only the columns you need.
Here are the habits side by side:
| Do | Instead of |
|---|---|
| where Timestamp between (...) right after the table | no time filter, or a filter on a computed date |
| Message has 'timeout' | Message contains 'timeout' |
| Level == 'Error' (or =~ 'error') | tolower(Level) == 'error' |
| project the columns you need | carry every column to the end |
| filter on table columns | filter on calculated columns |
| count or take on a new query first | the full result right away |
So use has, not contains, and case-sensitive operators where you can: =~, not tolower(). Filter on table columns, not calculated ones, and try count or take on a new query first.
But has matches whole terms. A term is a run of letters and digits, so timeout is one term. Let's count three text filters on October 5.
// one day of logs, three text filters
Logs
| where Timestamp between (datetime(2026-10-05) .. 1d)
| summarize HasTimeout = countif(Message has 'timeout'),
ContainsTime = countif(Message contains 'time'),
HasTime = countif(Message has 'time')$ kql -f habits.kql
> Logs
HasTimeout ContainsTime HasTime
---------- ------------ -------
56 56 0| HasTimeout | ContainsTime | HasTime |
|---|---|---|
| 56 | 56 | 0 |
has 'timeout' finds 56 lines, and so does contains 'time'. has 'time' finds 0, because time isn't a term in timeout. And only terms of three characters or more are indexed.
For heavier patterns:
- Dynamic fields: materialize them at ingestion (update policy)
- A reused let: materialize()
- Same-format strings: one parse, not many extracts
- Repeated aggregations: materialized views
A dashboard query with no time filter at all, so every refresh reads all the history.
Question 3: A teammate asks how the partitioning policy helps. When do you use it?
Mostly, you don't. By default, data is partitioned by ingestion time, and the docs say in most cases there's no need for this policy. It reingests data in the background into extents that are homogeneous on a key, which adds significant processing load.
It's recommended in three cases:
| Recommended when | Kind | Detail |
|---|---|---|
| frequent filters on a string or guid column, 10,000+ distinct values | Hash | PartitionAssignmentMode Uniform |
| frequent aggregations or joins on 1,000,000+ distinct values | Hash | PartitionAssignmentMode ByPartition |
| out-of-order data, filtered on a datetime column | UniformRange | OverrideCreationTime |
So check the numbers first. Let's count the rows, the distinct customers and the distinct services.
// enough distinct values to partition on?
Logs
| summarize Rows = count(), Customers = dcount(CustomerId),
Services = dcount(Service)$ kql -f cardinality.kql
> Logs
Rows Customers Services
----- --------- --------
10000 800 4| Rows | Customers | Services |
|---|---|---|
| 10000 | 800 | 4 |
We have 10000 rows, 800 customers and 4 services. 800 customers is far below the 10,000 bar, so no partitioning here.
For a real multi-tenant table with tens of thousands of tenants, it's a hash key on TenantId, with XxHash64, a MaxPartitionCount of 128, as recommended, and Uniform assignment. The emulator accepts the policy but doesn't apply it, so it's code here:
// a multi-tenant table with tens of thousands of tenants
.alter table TenantLogs policy partitioning ```{
"PartitionKeys": [{
"ColumnName": "TenantId",
"Kind": "Hash",
"Properties": {
"Function": "XxHash64",
"MaxPartitionCount": 128,
"PartitionAssignmentMode": "Uniform"
}
}]
}```If each partition would hold less than about 1 GB of compressed data, the policy isn't worth it. And it only applies to hot extents.
Question 4: A query uses the same subquery twice. How does materialize help?
A let statement binds a name to a calculation, not to its result. Use it twice, and it can be computed twice. materialize() caches the tabular result for the duration of the query, so both uses read the same rows.
Let's try it. One day of logs is summarized by service once, as day. The error share needs that result twice: once as rows, and once for the total, through toscalar().
let day = materialize(Logs
| where Timestamp between (datetime(2026-10-05) .. 1d)
| summarize Requests = count(),
Errors = countif(Level == 'Error') by Service);
let total = toscalar(day | summarize sum(Errors));
day
| extend ErrorShare = round(100.0 * Errors / total, 1)
| order by Service asc$ kql -f share.kql
> let day = materialize(Logs
Service Requests Errors ErrorShare
-------- -------- ------ ----------
api 500 20 14.9
checkout 500 20 14.9
payments 500 74 55.2
search 500 20 14.9| Service | Requests | Errors | ErrorShare |
|---|---|---|---|
| api | 500 | 20 | 14.9 |
| checkout | 500 | 20 | 14.9 |
| payments | 500 | 74 | 55.2 |
| search | 500 | 20 | 14.9 |
payments has 74 of the 134 errors, 55.2 percent. api, checkout and search have 20 each, 14.9 percent.
The docs set the rules. The cache is limited to 5 GB per cluster node, shared by all queries running at the same time, and a query that needs more fails. So push filters and projections inside, and keep the cached result small.
Run the query with and without materialize(). On the emulator, the scan statistics look the same either way, so the proof belongs on a real cluster.
Question 5: Someone asks what hint.strategy=shuffle does for summarize and join. When do you use it?
Shuffle splits the work by a key, so each node handles part of the keys, not all of them. It's for summarize and join on high-cardinality keys, when the normal query hits limits like memory.
hint.shufflekey = Key: orhint.strategy = shuffle(all keys)- summarize: high cardinality, ideally more than 1,000,000 values
- join: when both sides are too large
Let's count errors per customer, once plain and once with hint.shufflekey = CustomerId.
// plain
Logs
| summarize Errors = countif(Level == 'Error') by CustomerId
| order by Errors desc, CustomerId asc
| take 3
// shuffled on the high-cardinality key
Logs
| summarize hint.shufflekey = CustomerId
Errors = countif(Level == 'Error') by CustomerId
| order by Errors desc, CustomerId asc
| take 3$ kql -f shuffle.kql
> Logs
CustomerId Errors
---------- ------
C0059 5
C0587 5
C0023 4
> Logs
CustomerId Errors
---------- ------
C0059 5
C0587 5
C0023 4The same three customers with the same counts: C0059 and C0587 with 5, then C0023 with 4. The emulator is one node, so the hint changes nothing here. On a cluster, the number of partitions defaults to the node count.
Don't confuse it with broadcast:
| Hint | For | How |
|---|---|---|
| hint.strategy = shuffle | high-cardinality keys; two big join sides | split by key across nodes |
| hint.strategy = broadcast | a small LEFT side (tens of MB) | the left side is sent to every node |
Broadcast is for a small left side of a join, tens of MB, sent to every node. Shuffle is for two big sides.
Shuffle everywhere. Too many partitions can make a query slower. Test it on real data and real volumes.
Question 6: Users hit query limits like result set too large or low memory errors. What do you do?
First, know the limits. A result is capped at 500,000 records or 64 MB. Queries time out after 4 minutes by default, management commands after 10, and memory is limited per iterator and per node.
| Limit | Default | When you hit it |
|---|---|---|
| result set | 500,000 records or 64 MB | partial failure: E_QUERY_RESULT_SET_TOO_LARGE |
| query timeout | 4 minutes (up to 1 hour) | raise servertimeout, or make the query cheaper |
| management command timeout | 10 minutes | |
| memory | per iterator and per node | E_RUNAWAY_QUERY, low-memory failures |
Let's hit two of them for real. First we ask for 600,000 rows and print only the first 3. Then we set the per-iterator memory very low on purpose, maxmemoryconsumptionperiterator=1000, and run make_list(Message) by Service.
$ 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 'set maxmemoryconsumptionperiterator=1000; Logs | summarize make_list(Message) by Service'
Service list_Message
------- ------------
(no rows)
Error (partial result): Query execution has exceeded the allowed limits (80DA0001): Partial query
failure: Runaway query (E_RUNAWAY_QUERY). (message: ''summarize' operator has exceeded the memory
budget (1000) during evaluation. Results may be incorrect or incomplete (E_RUNAWAY_QUERY; see
https://aka.ms/kustoquerylimits).: ').The first query returns 500000 rows in total, then a partial query failure, E_QUERY_RESULT_SET_TOO_LARGE. Those rows aren't the full answer. The second becomes a runaway query, E_RUNAWAY_QUERY, with (no rows). Big string aggregations hit this on real data too.
The fixes:
- Return less: aggregate, project, filter;
.exportfor big exports - Memory: reduce the data before the heavy operator, or shuffle
- Timeout:
servertimeout, up to 1 hour
And set notruncation on purpose, not by habit.
In the default workload group, clients can relax these limits per request. On a shared cluster, a custom workload group sets firm limits per app.
Scenario 7: A Power BI dashboard on Azure Data Explorer is slow for fifty users at month end. How do you fix it?
Fix the work before the hardware. Fifty users clicking visuals means many small queries over the same data: make each one cheap, repeats reusable, and the cluster ready for the peak.
Here is the design:
- Power BI: 50 users, DirectQuery
- Workload group PowerBI: limits, 20 concurrent, queuing
- DailyServiceStats: a materialized view
- The cluster: the hot cache covers the month, scale out before month end
Cheap first. Let's create a materialized view with requests, errors and the 95th percentile per service and day, with an async backfill, and wait until the backfill has finished.
.create async materialized-view with (backfill=true)
DailyServiceStats on table Logs {
Logs
| summarize Requests = count(),
Errors = countif(Level == 'Error'),
P95Ms = percentile(DurationMs, 95)
by Service, Day = bin(Timestamp, 1d)
}# async backfill: the view can be queried once it has finished (up to 5 min)
for i in $(seq 1 60); do
n=$(kql "DailyServiceStats | count" 2>/dev/null | tail -1)
[ "${n:-0}" -ge 20 ] 2>/dev/null && exit 0
sleep 5
done
echo "backfill did not finish"; exit 1$ kql -q -f stats_view.kql
> .create async materialized-view with (backfill=true)
ok: .create async materialized-view with (backfill=true)
$ bash wait_backfill.shThe wait script returns once the view holds all 20 rows. Then the cluster side: a workload group for the dashboard with firm limits, 20 concurrent requests and request queuing, in preview, so peaks wait instead of failing and can't starve everyone else. A classification policy routes requests to it. The dashboard query reads the view with materialized_view(), and the results cache can answer an identical query for five minutes.
.create-or-alter workload_group PowerBI ```{
"RequestLimitsPolicy": {
"MaxExecutionTime": {"IsRelaxable": false, "Value": "00:01:00"},
"MaxResultRecords": {"IsRelaxable": false, "Value": 100000}
},
"RequestRateLimitPolicies": [{"IsEnabled": true,
"Scope": "WorkloadGroup", "LimitKind": "ConcurrentRequests",
"Properties": {"MaxConcurrentRequests": 20}}],
"RequestQueuingPolicy": {"IsEnabled": true}
}```set query_results_cache_max_age = time(5m);
materialized_view('DailyServiceStats')
| where Day == datetime(2026-10-05)
| project Service, Requests, Errors, P95Ms
| order by Service asc$ kql -q -f workload_group.kql
> .create-or-alter workload_group PowerBI ```{
ok: .create-or-alter workload_group PowerBI ```{
$ kql '.show workload_groups | project WorkloadGroupName | order by WorkloadGroupName asc'
WorkloadGroupName
-----------------
PowerBI
default
$ kql -f dashboard.kql
> set query_results_cache_max_age = time(5m);
Service Requests Errors P95Ms
-------- -------- ------ -----
api 500 20 396
checkout 500 20 397
payments 500 74 2750
search 500 20 399| Service | Requests | Errors | P95Ms |
|---|---|---|---|
| api | 500 | 20 | 396 |
| checkout | 500 | 20 | 397 |
| payments | 500 | 74 | 2750 |
| search | 500 | 20 | 399 |
PowerBI is listed next to default, and the view returns October 5 per service: payments with 74 errors and a P95 of 2750 ms, the others between 396 and 399.
In Power BI:
- DirectQuery: large, frequently updated fact tables
- Import: small dimension tables
- Query Reduction: no query on every slicer click
- Weak consistency: "to improve parallelism"
Before month end, I test it:
- Replay: the dashboard's queries with 50 users
- Monitor: CPU, Cache utilization factor, Throttled queries
- Fix: the slowest query first
Key points to remember
- Diagnose with .show queries / .show commands-and-queries: scanned extents, CPU, memory, cache
- Datetime filter first, has over contains, few columns, filters on table columns
- Partitioning only for 10,000+ distinct filter values or 1,000,000+ for aggregations
- materialize() for a reused let (5 GB per node); shuffle for high-cardinality keys
- Know the limits: 500,000 records / 64 MB, 4-minute timeout; dashboards on 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 #7 - Performance and query tuning
# 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_07_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('mk_logs.py', '# logs_1001.csv .. logs_1005.csv: 2,000 invented requests a day\nfrom datetime import datetime, timedelta\n\nSERVICES = ["api", "checkout", "payments", "search"]\nfor d in range(5):\n day = datetime(2026, 10, 1 + d)\n rows = []\n for i in range(2000):\n ts = day + timedelta(seconds=43 * i)\n svc = SERVICES[i % 4]\n cust = "C%04d" % ((i * 37 + d * 11) % 800 + 1)\n ms = 20 + (i * 53) % 400\n if svc == "payments" and i % 9 == 2:\n lvl, ms, msg = "Error", ms + 2500, "timeout calling bank gateway"\n elif i % 25 == 0:\n lvl, msg = "Error", "upstream returned 503"\n elif i % 7 == 3:\n lvl, msg = "Warning", "slow response from %s" % svc\n else:\n lvl, msg = "Info", "GET /%s/v2 ok" % svc\n rows.append("%s,%s,%s,%s,%d,%s" % (ts.strftime("%Y-%m-%dT%H:%M:%SZ"),\n svc, lvl, cust, ms, msg))\n with open("logs_10%02d.csv" % (1 + d), "w") as f:\n f.write("\\n".join(rows) + "\\n")\nprint("5 files, 10000 rows")\n')
write('setup.kql', """\
.create table Logs (Timestamp:datetime, Service:string, Level:string,
CustomerId:string, DurationMs:long, Message:string)
.ingest into table Logs (@"/kustodata/logs_1001.csv")
with (format='csv')
.ingest into table Logs (@"/kustodata/logs_1002.csv")
with (format='csv')
.ingest into table Logs (@"/kustodata/logs_1003.csv")
with (format='csv')
.ingest into table Logs (@"/kustodata/logs_1004.csv")
with (format='csv')
.ingest into table Logs (@"/kustodata/logs_1005.csv")
with (format='csv')
""")
run('python mk_logs.py')
run(KQL + ' -q -f setup.kql')
# Step 1: The payments error report: v1 filters on the timestamp, v2 on a formatted text date
write('report.kql', """\
// v1: the payments error report as it was
Logs
| where Timestamp between (datetime(2026-10-05) .. 1d)
| where Service == 'payments' and Level == 'Error'
| summarize Errors = count(), AvgMs = round(avg(DurationMs))
// v2: "cleaned up" with a text date
Logs
| where format_datetime(Timestamp, 'yyyy-MM-dd') == '2026-10-05'
| where Service == 'payments' and Level == 'Error'
| summarize Errors = count(), AvgMs = round(avg(DurationMs))
""")
run(KQL + ' -f report.kql')
# Step 2: Wait until .show queries lists both runs (background flush)
write('wait_logged.sh', """\
# .show queries fills in the background: wait for both runs (up to 5 min)
for i in $(seq 1 60); do
n=$(kql ".show queries | where Text has 'payments'
and State == 'Completed' | count" 2>/dev/null | tail -1)
[ "${n:-0}" -ge 2 ] 2>/dev/null && exit 0
sleep 5
done
echo ".show queries did not list both runs"; exit 1
""")
run('bash wait_logged.sh')
# Step 3: .show queries: scanned extents and rows of the two versions
write('show_queries.kql', """\
.show queries
| where Text has "Level == 'Error'" and Text has 'payments'
| extend s = ScannedExtentsStatistics
| project Filter = iff(Text has 'format_datetime', 'format_datetime',
'between'),
ScannedExtents = tolong(s.ScannedExtentsCount),
TotalExtents = tolong(s.TotalExtentsCount),
ScannedRows = tolong(s.ScannedRowsCount), State
| order by ScannedExtents asc
""")
run(KQL + ' -f show_queries.kql')
# Step 4: has matches whole terms: has timeout, contains time, has time
write('habits.kql', """\
// one day of logs, three text filters
Logs
| where Timestamp between (datetime(2026-10-05) .. 1d)
| summarize HasTimeout = countif(Message has 'timeout'),
ContainsTime = countif(Message contains 'time'),
HasTime = countif(Message has 'time')
""")
run(KQL + ' -f habits.kql')
# Step 5: Check the cardinality before a partitioning policy
write('cardinality.kql', """\
// enough distinct values to partition on?
Logs
| summarize Rows = count(), Customers = dcount(CustomerId),
Services = dcount(Service)
""")
run(KQL + ' -f cardinality.kql')
# Step 6: materialize(): one day summarized once, used twice
write('share.kql', """\
let day = materialize(Logs
| where Timestamp between (datetime(2026-10-05) .. 1d)
| summarize Requests = count(),
Errors = countif(Level == 'Error') by Service);
let total = toscalar(day | summarize sum(Errors));
day
| extend ErrorShare = round(100.0 * Errors / total, 1)
| order by Service asc
""")
run(KQL + ' -f share.kql')
# Step 7: summarize with and without hint.shufflekey: same result (one node)
write('shuffle.kql', """\
// plain
Logs
| summarize Errors = countif(Level == 'Error') by CustomerId
| order by Errors desc, CustomerId asc
| take 3
// shuffled on the high-cardinality key
Logs
| summarize hint.shufflekey = CustomerId
Errors = countif(Level == 'Error') by CustomerId
| order by Errors desc, CustomerId asc
| take 3
""")
run(KQL + ' -f shuffle.kql')
# Step 8: Query limits: 500,000 records (partial failure) and a runaway summarize under a tiny memory budget
run(KQL + " --max 3 'range x from 1 to 600000 step 1'", allow_error=True)
run(KQL + " 'set maxmemoryconsumptionperiterator=1000; Logs | summarize make_list(Message) by Service'", allow_error=True)
# Step 9: Month end: a daily materialized view (async backfill), wait until the backfill has finished
write('stats_view.kql', """\
.create async materialized-view with (backfill=true)
DailyServiceStats on table Logs {
Logs
| summarize Requests = count(),
Errors = countif(Level == 'Error'),
P95Ms = percentile(DurationMs, 95)
by Service, Day = bin(Timestamp, 1d)
}
""")
write('wait_backfill.sh', """\
# async backfill: the view can be queried once it has finished (up to 5 min)
for i in $(seq 1 60); do
n=$(kql "DailyServiceStats | count" 2>/dev/null | tail -1)
[ "${n:-0}" -ge 20 ] 2>/dev/null && exit 0
sleep 5
done
echo "backfill did not finish"; exit 1
""")
run(KQL + ' -q -f stats_view.kql')
run('bash wait_backfill.sh')
# Step 10: Month end: a PowerBI workload group with queuing, the dashboard query on the view
write('workload_group.kql', """\
.create-or-alter workload_group PowerBI ```{
"RequestLimitsPolicy": {
"MaxExecutionTime": {"IsRelaxable": false, "Value": "00:01:00"},
"MaxResultRecords": {"IsRelaxable": false, "Value": 100000}
},
"RequestRateLimitPolicies": [{"IsEnabled": true,
"Scope": "WorkloadGroup", "LimitKind": "ConcurrentRequests",
"Properties": {"MaxConcurrentRequests": 20}}],
"RequestQueuingPolicy": {"IsEnabled": true}
}```
""")
write('dashboard.kql', """\
set query_results_cache_max_age = time(5m);
materialized_view('DailyServiceStats')
| where Day == datetime(2026-10-05)
| project Service, Requests, Errors, P95Ms
| order by Service asc
""")
run(KQL + ' -q -f workload_group.kql')
run(KQL + " '.show workload_groups | project WorkloadGroupName | order by WorkloadGroupName asc'")
run(KQL + ' -f dashboard.kql')
Part 8: Security and Governance
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.