In this post we are going to cover Azure Data Explorer and KQL fundamentals. 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: the team wants to move application logs from a SQL database to Azure Data Explorer.
Azure Data Explorer is Microsoft's managed analytics service for logs, telemetry and other event data, and KQL, the Kusto Query Language, is how we query it. Interviewers start here because everything later depends on it: how a query is built, how the data is stored, and when this service is the right tool and when it is not.
Part 1 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: Your manager asks what Azure Data Explorer is and where it fits in a data platform. What do you say?
- Question 2: A new teammate asks how a KQL query is built. How do you explain the pipe model?
- Question 3: Someone asks how clusters, databases, tables and extents fit together. How do you explain them?
- Question 4: Leadership asks whether to use Azure Data Explorer or an Eventhouse in Microsoft Fabric. How do you decide?
- Question 5: A teammate asks the difference between queries and management commands. How do you explain it?
- Question 6: Someone asks when you would NOT use Azure Data Explorer. What do you say?
- Scenario 7: The team wants to move application logs from a SQL database to Azure Data Explorer. How do you plan it?
- The full script
The short answer
Azure Data Explorer is a fully managed, near real-time analytics platform for logs, telemetry, IoT and time series. A KQL query is a pipe of operators; management commands start with a dot. Data lives in clusters, databases, tables and immutable extents, and the same engine runs in a Fabric Eventhouse.
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. The sample data is a small invented request log: three CSV files of six rows each, and setup.kql creates the Requests table and loads them with .ingest into, which is for exploration and prototyping, not production. kql is a small helper that sends a query or a command to the emulator and prints the result as a text table. What only a cluster in Azure can show, such as retention and caching, is 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")Question 1: Your manager asks what Azure Data Explorer is and where it fits in a data platform. What do you say?
Azure Data Explorer is a fully managed, high-performance big data analytics platform for high volumes of data in near real time: log analytics, time series, IoT and exploratory analytics.
It sits on the hot path. Here is the flow:
- Apps and devices: logs, clicks, sensors
- Event Hubs, Event Grid: streams and files
- Azure Data Explorer: ingest, store, query
- Power BI, Grafana: dashboards
- Data lake: the long raw history
Let's see the kind of question it answers. We run q1.kql, requests and errors per hour on our sample log:
Requests
| where Timestamp >= datetime(2026-10-20 08:00)
| summarize Requests = count(),
Errors = countif(Level == "Error")
by bin(Timestamp, 1h)
| order by Timestamp asc$ kql -f q1.kql
> Requests
Timestamp Requests Errors
-------------------- -------- ------
2026-10-20T08:00:00Z 5 2
2026-10-20T09:00:00Z 5 2
2026-10-20T11:00:00Z 5 2
2026-10-20T12:00:00Z 3 1| Timestamp | Requests | Errors |
|---|---|---|
| 2026-10-20T08:00:00Z | 5 | 2 |
| 2026-10-20T09:00:00Z | 5 | 2 |
| 2026-10-20T11:00:00Z | 5 | 2 |
| 2026-10-20T12:00:00Z | 3 | 1 |
Over the 18 sample requests we get 5 requests and 2 errors at 08:00, 09:00 and 11:00, and 3 requests with 1 error at 12:00.
Microsoft's checklist for when it fits:
- Interactive analytics: aggregation, correlation, anomaly detection
- Volume, velocity, variety: lots of append-only events
- Raw data, not fully curated: query it as it lands
- Many concurrent queries: dashboards and people at once
It doesn't replace the warehouse. The warehouse serves curated reports; Azure Data Explorer answers what is happening right now. It sits beside the warehouse, not instead of it.
Question 2: A new teammate asks how a KQL query is built. How do you explain the pipe model?
A KQL query is a data flow. It starts with a table, and rows flow through operators separated by the pipe character. Each operator takes a table from the one before and hands a new table to the next.
Let's build one question in three versions. First the table alone, then a where on the level, then a summarize by service with an order by. We run q2.kql:
Requests
| count
Requests
| where Level == "Error"
| count
Requests
| where Level == "Error"
| summarize Errors = count() by Service
| order by Errors desc$ kql -f q2.kql
> Requests
Count
-----
18
> Requests
Count
-----
7
> Requests
Service Errors
-------- ------
payments 5
checkout 2The Requests table alone has 18 rows. With the filter we get 7 errors. After the summarize, payments has 5 and checkout has 2.
Order matters. Filter first, so every later operator sees fewer rows. Microsoft's best practices put the where right after the table, with the time filter first, because the datetime index can skip whole shards.
let statements go in front of the query. Each one ends with a semicolon, and there are no blank lines between them and the query:
let errors = Requests | where Level == "Error";- then the query that uses
errors
When a result looks wrong, comment out the lower lines and check each stage on its own.
Question 3: Someone asks how clusters, databases, tables and extents fit together. How do you explain them?
A cluster is the compute, a set of nodes running the engine. A database lives in a cluster, a table lives in a database, and a table's data lives in extents.
Let's go from the top:
- Cluster: compute, the engine nodes
- Databases: up to 10,000 per cluster
- Tables: up to 10,000 per database, plus functions and policies
- Extents: immutable and columnar, kept in Azure Storage and cached on the nodes' local SSDs
On the emulator, the cluster is one container. We run q3.kql with .show databases, .show tables and .show table Requests extents:
.show databases
| project DatabaseName, PersistentStorage
| order by DatabaseName asc
.show tables
| project TableName
.show table Requests extents
| project RowCount, OriginalSize
| order by OriginalSize asc$ kql -f q3.kql
> .show databases
DatabaseName PersistentStorage
------------ --------------------------------------
NetDefaultDB /kusto/tmp/Kusto.Personal/NetDefaultDB
shop /kustodata/dbs/shop/md
> .show tables
TableName
---------
Requests
> .show table Requests extents
RowCount OriginalSize
-------- ------------
6 461
6 477
6 481.show databases lists NetDefaultDB, the emulator's built-in default, and shop, our database. .show tables lists Requests. We loaded Requests from three CSV files, so it has three extents of 6 rows each.
An extent is a horizontal slice of the table, stored by column and compressed, with indexes and metadata like its creation time. Extents are immutable. A change writes new extents and swaps them in, in one transaction. A cluster merges small extents in the background; the emulator never does, so here they stay three.
Retention and caching work on extents, by creation time. And many tiny loads make many tiny extents, so batch your loads.
Question 4: Leadership asks whether to use Azure Data Explorer or an Eventhouse in Microsoft Fabric. How do you decide?
It's the same engine and the same KQL, so the choice is about the platform around it, not the language.
An Eventhouse is Fabric's container for KQL databases. It's billed by Fabric capacity and suspends when idle, unless you set a minimum. Azure Data Explorer is a cluster you size and run, paying for its compute, storage and the service markup. Let's compare them:
| Azure Data Explorer | Eventhouse in Fabric | |
|---|---|---|
| Engine, language | the Kusto engine, KQL | the same engine and KQL |
| Container | a cluster with databases | an Eventhouse with KQL databases |
| Billing | the cluster: compute (SKU, nodes), storage, markup | Fabric capacity; suspends when idle |
| You get | cluster control: SKU, scale, networking, keys | Copilot, Notebooks, Activator, Power BI |
| My pick when | you need cluster-level control | the company is Fabric-first |
Microsoft's comparison says Azure gives pro developers maximum control for custom architectures, while Real-Time Intelligence gives an integrated workflow with fewer manually stitched services.
Then my own rule, and I say clearly in the interview that it is my rule, not Microsoft's. If the company is Fabric first, with Power BI and capacity billing, I pick an Eventhouse. If I need cluster-level control, like the SKU, private endpoints, customer-managed keys or follower databases, I pick Azure Data Explorer.
Microsoft documents a phased migration with no downtime, and a database shortcut shows an Azure Data Explorer database inside Fabric, read-only.
Question 5: A teammate asks the difference between queries and management commands. How do you explain it?
Management commands start with a dot, and no query may. Queries read data and change nothing. Commands read metadata or change the service: create tables, set policies, ingest data.
Here is the difference side by side:
| Queries | Management commands | |
|---|---|---|
| Starts with | a table name, let, print | a dot: .show, .create, .alter, .ingest |
| Does | reads data, changes nothing | reads metadata or changes the service |
| Can contain | no management command | a query, after <| |
| REST endpoint | /v2/rest/query | /v1/rest/mgmt |
| Who can run it | a database Viewer | creating a table: Database User |
Let's try both. We run q5.kql: .show tables piped into project, then .create table, then .set-or-append, which runs a query and appends its result. After that we run a query that tries to pipe into .show tables:
.show tables
| project TableName
.create table ErrorCounts (Service:string, Errors:long)
| project TableName
.set-or-append ErrorCounts <|
Requests
| where Level == "Error"
| summarize Errors = count() by Service
ErrorCounts
| order by Service asc$ kql -f q5.kql
> .show tables
TableName
---------
Requests
> .create table ErrorCounts (Service:string, Errors:long)
TableName
-----------
ErrorCounts
> .set-or-append ErrorCounts <|
ExtentId OriginalSize ExtentSize CompressedSize IndexSize RowCount
------------------------------------ ------------ ---------- -------------- --------- --------
<id> 48 1005 330 675 2
> ErrorCounts
Service Errors
-------- ------
checkout 2
payments 5
$ kql 'Requests | where Level == "Error" | .show tables'
Error: Syntax error: SYN0002: Query operator expected. [line:position=1:36]Yes, a .show result can flow into query operators. .create table returns ErrorCounts, and .set-or-append writes one extent of 2 rows: checkout 2 and payments 5. The other direction fails: the engine says Syntax error: SYN0002: Query operator expected. A command can contain a query, but a query can't contain a command.
Kusto keeps them apart by language, protocol and API, for security. They use different REST endpoints and permissions: a viewer can query, but creating a table takes a database user.
.set-or-append is direct ingestion, for exploration and prototyping, not production. Commands live in scripts, in source control, and run from a pipeline.
Question 6: Someone asks when you would NOT use Azure Data Explorer. What do you say?
When the workload is transactional. Extents are immutable, so there's no cheap way to update one row, and deleting records means a soft delete or a purge.
So I go through the common cases, and I name the better fit for each:
| Workload | Better fit | Why |
|---|---|---|
| Order entry, frequent row updates | Azure SQL Database | transactions, row-level updates |
| Small relational app | Azure SQL Database | simpler to build and run |
| Curated star schema for finance | a warehouse | many dimension joins, curated model |
| ACID across several tables | a relational database | multi-statement transactions |
| Logs, telemetry, IoT, events | Azure Data Explorer | append-only, time-filtered, ad hoc |
Order entry with frequent row updates belongs in Azure SQL Database. Small relational apps too: if the data fits in a SQL database and people edit rows all day, a SQL database is simpler to build and run.
A curated star schema for finance reporting, with many dimension joins, is a warehouse job. And if you need ACID transactions across several tables, use a relational database.
Where Azure Data Explorer shines instead: append-only logs, telemetry, IoT and events, filtered by time, with ad hoc questions over a lot of rows.
This is my view from real projects, not a Microsoft list, and I say it that way in the interview.
Name the alternative every time. A real platform often has all three: SQL for the app, a warehouse for curated reports, Azure Data Explorer for the event stream.
Scenario 7: The team wants to move application logs from a SQL database to Azure Data Explorer. How do you plan it?
In phases:
- Model the table: typed columns, a datetime for the time
- Test load and validate: row count and time range match the export
- Rewrite the reports: SQL to KQL, compare the numbers
- History once: LightIngest, creation time from the log date
- New logs continuously: queued ingestion or Event Hubs
- Run in parallel, then cut over: retention and caching set per table
The time column is a datetime, not text or a Unix number, because time filters depend on it. Let's load the SQL export with ignoreFirstRecord=true, so the header isn't a row, and validate. .ingest into here is for exploration and prototyping, not production. We count the export's data rows, run q7_load.kql, then q7_check.kql:
LogTime,Severity,App,UserId,Message
2026-10-19T22:10:00Z,Info,web,U01,login ok
2026-10-19T22:14:00Z,Error,api,U02,timeout calling inventory
2026-10-19T23:05:00Z,Warning,jobs,,nightly export slow
2026-10-20T00:30:00Z,Error,jobs,,nightly export failed
2026-10-20T06:45:00Z,Info,web,U03,login ok
2026-10-20T07:02:00Z,Error,api,U03,timeout calling inventory
2026-10-20T07:15:00Z,Error,api,U01,invalid token
2026-10-20T07:40:00Z,Info,web,U02,checkout ok
2026-10-20T08:05:00Z,Error,web,U04,payment page 500
2026-10-20T08:20:00Z,Info,api,U04,order created.create table AppLogs (Timestamp:datetime, Level:string,
App:string, UserId:string, Message:string)
.ingest into table AppLogs (@"/kustodata/applogs_export.csv")
with (format="csv", ignoreFirstRecord=true)AppLogs
| summarize Rows = count(), First = min(Timestamp),
Last = max(Timestamp)
AppLogs
| where isnull(Timestamp)
| count$ tail -n +2 applogs_export.csv | wc -l
10
$ kql -q -f q7_load.kql
> .create table AppLogs (Timestamp:datetime, Level:string,
ok: .create table AppLogs (Timestamp:datetime, Level:string,
> .ingest into table AppLogs (@"/kustodata/applogs_export.csv")
ok: .ingest into table AppLogs (@"/kustodata/applogs_export.csv")
$ kql -f q7_check.kql
> AppLogs
Rows First Last
---- -------------------- --------------------
10 2026-10-19T22:10:00Z 2026-10-20T08:20:00Z
> AppLogs
Count
-----
0The export has 10 data rows, the table has 10, the time range is 2026-10-19T22:10:00Z to 2026-10-20T08:20:00Z, and 0 rows lost their timestamp.
Then we rewrite each report. This is the SQL report today, errors per app since midnight:
-- the report today, on SQL Server
SELECT App, COUNT(*) AS Errors
FROM dbo.AppLogs
WHERE Severity = 'Error'
AND LogTime >= '2026-10-20'
GROUP BY App
ORDER BY Errors DESC, App;In KQL it's a time filter, a level filter, summarize and order by. We run q7_report.kql:
AppLogs
| where Timestamp >= datetime(2026-10-20)
| where Level == "Error"
| summarize Errors = count() by App
| order by Errors desc, App asc$ kql -f q7_report.kql
> AppLogs
App Errors
---- ------
api 2
jobs 1
web 1| App | Errors |
|---|---|
| api | 2 |
| jobs | 1 |
| web | 1 |
api 2, jobs 1, web 1, to compare with the SQL report. On the Azure cluster we set retention and caching per table:
// on the Azure cluster: keep a year, the last 30 days hot
.alter-merge table AppLogs policy retention softdelete = 365d
.alter table AppLogs policy caching hot = 30dCopying the SQL schema one to one, with text timestamps and identity keys. Model the table for the questions you will ask: time first.
Key points to remember
- Azure Data Explorer: managed, near real-time analytics for logs, telemetry, IoT and time series
- A KQL query is a pipe: table, then operators; filter first, time filter first
- Cluster, databases, tables, and immutable, columnar extents
- Eventhouse: same engine in Fabric; the choice is the platform, not the language
- Management commands start with a dot and can contain queries; not for OLTP workloads
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 #1 - Azure Data Explorer and KQL fundamentals
# 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_01_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")
""")
run(KQL + ' -q -f setup.kql')
# Step 1: Requests and errors per hour on the sample request log
write('q1.kql', """\
Requests
| where Timestamp >= datetime(2026-10-20 08:00)
| summarize Requests = count(),
Errors = countif(Level == "Error")
by bin(Timestamp, 1h)
| order by Timestamp asc
""")
run(KQL + ' -f q1.kql')
# Step 2: The pipe model: the table, then a filter, then summarize and order by
write('q2.kql', """\
Requests
| count
Requests
| where Level == "Error"
| count
Requests
| where Level == "Error"
| summarize Errors = count() by Service
| order by Errors desc
""")
run(KQL + ' -f q2.kql')
# Step 3: Databases, tables and the extents of the Requests table
write('q3.kql', """\
.show databases
| project DatabaseName, PersistentStorage
| order by DatabaseName asc
.show tables
| project TableName
.show table Requests extents
| project RowCount, OriginalSize
| order by OriginalSize asc
""")
run(KQL + ' -f q3.kql')
# Step 4: Management commands (.show, .create, .set-or-append) and a query; a query cannot contain a command
write('q5.kql', """\
.show tables
| project TableName
.create table ErrorCounts (Service:string, Errors:long)
| project TableName
.set-or-append ErrorCounts <|
Requests
| where Level == "Error"
| summarize Errors = count() by Service
ErrorCounts
| order by Service asc
""")
run(KQL + ' -f q5.kql')
run(KQL + ' \'Requests | where Level == "Error" | .show tables\'', expect_error=True) # EXPECTED TO FAIL: the video shows this error
# Step 5: SQL logs exported to CSV: create a typed table, load the file, validate the counts
write('applogs_export.csv', """\
LogTime,Severity,App,UserId,Message
2026-10-19T22:10:00Z,Info,web,U01,login ok
2026-10-19T22:14:00Z,Error,api,U02,timeout calling inventory
2026-10-19T23:05:00Z,Warning,jobs,,nightly export slow
2026-10-20T00:30:00Z,Error,jobs,,nightly export failed
2026-10-20T06:45:00Z,Info,web,U03,login ok
2026-10-20T07:02:00Z,Error,api,U03,timeout calling inventory
2026-10-20T07:15:00Z,Error,api,U01,invalid token
2026-10-20T07:40:00Z,Info,web,U02,checkout ok
2026-10-20T08:05:00Z,Error,web,U04,payment page 500
2026-10-20T08:20:00Z,Info,api,U04,order created
""")
write('q7_load.kql', """\
.create table AppLogs (Timestamp:datetime, Level:string,
App:string, UserId:string, Message:string)
.ingest into table AppLogs (@"/kustodata/applogs_export.csv")
with (format="csv", ignoreFirstRecord=true)
""")
write('q7_check.kql', """\
AppLogs
| summarize Rows = count(), First = min(Timestamp),
Last = max(Timestamp)
AppLogs
| where isnull(Timestamp)
| count
""")
run('tail -n +2 applogs_export.csv | wc -l')
run(KQL + ' -q -f q7_load.kql')
run(KQL + ' -f q7_check.kql')
# Step 6: The error report rewritten from SQL to KQL
write('q7_report.kql', """\
AppLogs
| where Timestamp >= datetime(2026-10-20)
| where Level == "Error"
| summarize Errors = count() by App
| order by Errors desc, App asc
""")
run(KQL + ' -f q7_report.kql')
Part 2: KQL Query Essentials
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.