MS Stack Ch 12 — KQL contracts, joins and telemetry

Review and execution boundary

This chapter uses Microsoft's Kusto/ADX documentation as checked on 8 October 2026. KQL queries and management commands below are source-checked reference examples, not service-executed results. The small Python relational model is CPU-tested; it does not implement the Kusto parser, optimizer, percentile estimator or security engine. The separate minute-window lab develops the window calculation further.

KQL is shared by Azure Data Explorer, Azure Monitor/Application Insights and Microsoft Sentinel, but their tables, plugins and management capabilities are not interchangeable. Examples using lowercase requests, dependencies, exceptions and traces assume the Application Insights query schema. An ADX SDK example requires an ADX database with the stated table; it is not a way to connect that SDK directly to an Application Insights workspace. Workspace tables such as AppRequests instead use names including TimeGenerated, DurationMs, OperationId and ItemCount. Inspect the actual schema before adapting a query. pivot, narrow and materialized-view management below target their documented ADX/Fabric surfaces.

Chapter 12 of From Novice to Fluent on the Modern Microsoft Web Stack.

Why this chapter

An attractive telemetry chart can still be wrong. A join can count one failed request three times; a firmware filter can resurrect an obsolete device state; a one-minute p95 chart can be mistaken for a five-minute rolling p95. KQL fluency means explaining which rows, which grain, which units and which permissions each answer represents, before optimizing it.

Work through six capabilities: trace a pipeline's schema; choose aggregates and time membership; preserve join multiplicity; constrain input and output; distinguish query-time reuse from ingestion-time work; and bind values without confusing that with authorization. The completion criterion is a reviewed triage query with an explicit denominator, failure handling and bounded resource use—not a claim that a particular query will survive a 100-fold traffic increase.

Concepts and depth

Read each operator as a transformation of a relation. Write down the input schema, output schema and row-count effect. Then distinguish that logical transformation from the physical strategy selected by the service. The following sections teach storage, operators, joins, reuse, time, security and pre-aggregation in that order, so later optimization decisions have a correctness contract to preserve.

The Kusto storage model

ADX stores tables in extents, horizontal data shards whose records are physically arranged in compressed columns. Extents are immutable: data modification creates new extents and swaps them for old ones. Ingestion creates extents, merge policies combine smaller extents, and retention eventually removes eligible data. This is append-oriented analytics, not a recommendation to model every event as a transactional row update. It is also not a claim that deletion is impossible: administrative soft-delete and purge mechanisms exist and have different storage-erasure guarantees.

A stored datetime predicate can eliminate irrelevant work using the engine's indexing and metadata. This does not mean every table is partitioned solely by the application's event timestamp or that every time filter reads exactly the matching extents. Event time can precede ingestion time; late data and extent merging matter. Avoid converting a timestamp column on every row when ingestion can supply the appropriate datetime type. Treat extent partitioning/merging as measured operational policy, not a SQL row-store primary-key choice.

Retention determines how long ingested data is kept available and when it becomes eligible for removal; expiry is not an exact deletion instant. Hot-cache policy chooses fast-tier placement, not data authorization or total retention. Inspect effective table/database policies rather than assuming defaults. A longer hot horizon can improve old-data queries while increasing capacity requirements; a shorter retention horizon can make a supposedly historical query impossible.

KQL has an optimizer: Microsoft explicitly documents automatic predicate arrangement in many cases, while warning it is not guaranteed. Write selective filters and narrow projections early when equivalent, then measure. Moving a firmware predicate before arg_max changes the question; no optimizer slogan makes that safe. Sources: extents, retention, cache, best practices.

Pipeline syntax: the pipe is a tabular contract

A tabular operator consumes a table and emits a table. The pipe connects these transformations; scalar functions, declarations and dot-prefixed management commands are different language constructs. Complete query statements are separated by semicolons. For debugging, keep prerequisite let declarations and run a complete pipeline prefix, not a dangling pipe or half an aggregation.

This example assumes a custom table Requests(TimeGenerated:datetime, ResultCode:string, Operation:string), not a built-in table with that exact schema:

Requests
| where TimeGenerated > ago(1h)
| where ResultCode startswith "5"
| summarize Count = count() by bin(TimeGenerated, 1m), Operation
| order by TimeGenerated desc

After the first filter, rows are within the lookback; after the second, only codes beginning with 5 remain; after summarize, the grain is minute × operation, with columns TimeGenerated, Operation, Count. A minute with no matching events contributes no group. Sorting changes order, not counts. A future-dated row also satisfies a lower-bound-only lookback: use an explicit upper bound when the measurement contract requires one, as the worked triage query does.

There is no universal first-three-lines rule. Time and tenant scope often belong early, but the right predicates depend on the question. Project away unused payload before an expensive join without dropping columns required later for correlation, grouping or access checks. Language overview and where define the logical contract.

Core operators: where, project, project-away, extend, summarize, order by, top, take, distinct, count, getschema

These are a useful working vocabulary, not an empirically established percentage of all queries:

• where Predicate retains rows for which the predicate is true. String == is case-sensitive; =~ is case-insensitive. has searches terms and contains searches substrings: select the meaning before selecting a faster operator. Handle null/empty values deliberately.
• project A, NewName = B selects, renames or computes columns and drops the rest. Row count is unchanged.
• project-away Secret, DebugPayload drops named columns/patterns. An API's positive allow-list is safer than relying on a deny-list to anticipate every future sensitive column.
• project-rename NewName = OldName renames while retaining other columns.
• extend NewCol = Expr adds a computed column, or replaces an existing named column. It does not narrow other payload automatically.
• summarize Total=count() by Operation emits one row per distinct group. Without by, empty input still produces a single aggregate row; count and sum default to zero. With grouping, empty input produces no groups.
• order by Time desc, Id asc sorts on one or more keys. Include a tie-breaker if a reproducible display order is required.
• top 10 by DurationMs desc selects the ten largest values. Microsoft documents it as equivalent to sort by DurationMs desc | take 10 in both meaning and performance; it is not universally faster.
• take 10 returns up to ten rows. Without preceding ordering there is no guarantee which rows or that repeated runs agree. It is useful interactive reconnaissance, not a random sample or a ranked dashboard.
• distinct A, B returns distinct tuples. It does not compute an approximate cardinality.
• count returns a one-row, one-column table of type long, not a scalar. count() is the aggregate used inside summarize.
• getschema returns column names/types as rows, allowing you to verify rather than guess a source schema.

Run these two reference statements independently on an authorized, known table; they are schema inspection and a bounded-output sample, not a load test:

// Quick reconnaissance on an unknown table
MyTable | getschema

MyTable
| where TimeGenerated > ago(1h)
| take 10

A ten-row result limit is not a general bound on all upstream CPU or memory. Conversely, a bare take on a large table is not automatically a full-table scan. Source contracts: operators, string comparisons, summarize, top, count.

Joins: kinds, key matching, and the small-on-large rule

Use Left | join kind=inner (Right) on Key, or explicitly qualified equality such as on $left.RequestId == $right.ParentId when column names differ. Multiple join conditions are ANDed. Decide the retained population before choosing placement:

• innerunique is the default. It deduplicates the left input by join key, then matches. The retained representative is not a latest-event rule.
• inner emits every matching pair. Two left rows and three right rows on one key produce six rows.
• leftouter retains every left row, including unmatched rows, while matches can multiply it. Missing right cells need type-aware handling; strings do not support a null value in the same way as numeric types.
• leftsemi retains matching left rows with only left columns, without multiplication by the number of right matches. leftanti retains left rows with no match.
• rightouter, rightsemi and rightanti retain the corresponding right population; fullouter includes unmatched rows from either side. Flipping a left outer join without changing the intended retained side is not equivalent.

This Application Insights reference joins requests to exception details; it does not promise one result row per request:

// Decorate request rows with the exception details when one was thrown
requests
| where timestamp > ago(1h)
| join kind=leftouter (
    exceptions
    | where timestamp > ago(1h)
    | project operation_Id, ExceptionType = type, ExceptionMessage = outerMessage
) on operation_Id

An operation can contain multiple requests/exceptions. If the metric is failed requests, count at request grain before enrichment, aggregate exception context to the required key, or use a semi-join when only membership matters. Time-filtering both sides restricts the correlation population and may exclude a late or out-of-window partner; choose the window consciously.

For regular joins Microsoft recommends the smaller left input when possible. Broadcast join distributes the small left input, whereas lookup broadcasts the right dimension. Neither is an unconditional row-count rule; estimate bytes, key cardinality and skew, and preserve semantics. Shuffle can distribute large/high-cardinality work but is not a remedy for a wrong join kind or every hot key.

The 500,000 records / 64 MB defaults are result-set truncation limits, not a silent limit on the left join input. Exceeding either reports a partial query failure. Clients must handle completion/errors rather than treating already-received rows as complete. Cross-cluster subqueries can also encounter truncation. set notruncation does not fix wrong multiplicity or remove memory/concurrency constraints; aggregate bounded dashboard results instead of disabling safety limits. Join semantics, innerunique, broadcast, lookup, limits.

Set operators: union and union withsource

union vertically stacks rows; join matches them horizontally. Default outer union keeps the combined schema, supplying missing cells where an input lacks a column. If one column name has different types across inputs, the output can contain type-suffixed columns. union kind=inner keeps the common columns—not just matching data rows.

let opId = "00000000-0000-0000-0000-000000000000";
union requests, dependencies, traces, exceptions
| where operation_Id == opId
| order by timestamp asc

This is a simple trace lookup under the lowercase schema. Add a bounded time interval before using it operationally. union withsource=SourceTable includes provenance, useful when differently shaped telemetry shares a timeline. Example 2 filters and projects each leg explicitly rather than promising that every predicate will always be pushed down.

union isfuzzy=true relaxes source resolution under documented conditions: at least one source must resolve, warnings must be inspected, and later execution errors are not generally suppressed. It does not grant permissions or ensure every workspace exists. Replace a join with union + summarize only after proving that additive combination answers the same question; high key cardinality alone is not equivalence. Union contract.

Variables: let and materialize()

let names a scalar expression, tabular expression or function. It binds a calculation, not a frozen value; repeated references can recalculate. Keep each declaration terminated with a semicolon. Naming window and threshold assumptions makes review easier but does not make interpolated user input safe.

The following lowercase-schema example assumes duration in milliseconds. It first identifies the ten operations with most observed failed rows, then finds slow requests in those operations; it does not claim weighted event counts under sampling:

let LookbackHours = 24;
let HighLatencyMs = 5000;
let ErrorOperations =
    requests
    | where timestamp > ago(LookbackHours * 1h)
    | where success == false
    | summarize Errors = count() by name
    | top 10 by Errors desc
    | project name;
requests
| where timestamp > ago(LookbackHours * 1h)
| where name in (ErrorOperations)
| where duration > HighLatencyMs
| project timestamp, name, duration, operation_Id
| order by timestamp desc

If a costly tabular expression is reused, materialize() captures its result for the query's lifetime. Here one shared population supplies two reports:

let HotOps = materialize(
    requests
    | where timestamp > ago(1h)
    | summarize Hits = count() by name
    | where Hits > 1000
);
HotOps | top 10 by Hits desc;
requests
| where timestamp > ago(1h)
| where name in (HotOps | project name)
| summarize ObservedMeanMs=avg(duration) by name

These last two query statements return separate result tables. A consumer must deliberately select/consume the expected result sets. ObservedMeanMs is an unweighted mean of retained request rows.

Materialization uses a 5 GB cache per cluster node shared by concurrent queries; exhaustion aborts the query. Push common filters/projections inside only when equivalent. Compare performance with and without materialization; a once-used expression often gains nothing. hint.materialized belongs to documented as/partition contexts and shares the cache; it is not a universal pipeline suffix. Query-results caching across requests is a different mechanism with freshness constraints. Let, materialize.

Aggregation functions

Choose the aggregate from the measure, not from its familiar name:

• count() counts rows; countif(Predicate) counts rows satisfying the predicate. They measure requests only if one row represents one request.
• sum, avg, min, max summarize numeric values (with documented null handling). sumif(Value, Predicate) adds selected values, useful for sampling weights. An average of group averages requires their contributing counts.
• dcount(Value, accuracy) estimates distinct cardinality. Default accuracy 1 has documented error standard deviation 0.8%; accuracy 0 is 1.6%, and accuracy 4 is 0.2%, not exact. These are probabilistic error characteristics, not a fixed bound for every input. Documented small-set exact optimizations do not make every high-accuracy result exact. Use distinct ... | count when you need exact cardinality within acceptable limits.
• percentile(Value, 95) and percentiles(Value, 50, 95, 99) estimate nearest-rank percentiles. Include population size and units; a p99 over very few requests is not a robust tail estimate. Averages are useful measures, not inherently lies.
• make_set(Value, maxSize) collects distinct values; make_list retains multiplicity. Their modern default and maximum size is 1,048,576 elements. Set ordering is undefined; list ordering follows sorted input, otherwise it is undefined. Pick a smaller explicit cap for a dashboard and expose the bound to readers. HLL sketches merge approximate counts; they cannot restore members omitted from a bounded set.
• arg_max(Time, columns...) selects a row at the maximum value per group; arg_min is the minimum counterpart. max(Time) alone does not carry the row's payload. Equal timestamps need a business tie rule; arg_max alone is not that rule.

// Latency distribution per operation
requests
| where timestamp > ago(1h)
| summarize
    Total = count(),
    Errors = countif(success == false),
    p50 = percentile(duration, 50),
    p95 = percentile(duration, 95),
    p99 = percentile(duration, 99)
  by name
| extend ErrorRate = todouble(Errors) / Total
| order by Total desc

This measures observed rows, not estimated original traffic under sampling. Classic Application Insights itemCount represents how many events a retained item represents; use sum(itemCount) and sumif(itemCount, success == false) for the corresponding counts. Naive percentiles remain over observed duration values; weighting counts alone does not turn them into population percentiles. Keep null/unknown success visible if it is relevant to your denominator. Aggregates, dcount, percentiles, sampling.

Time-binning, date arithmetic, and time-window queries

bin(Time, 1m) rounds down to minute boundaries. Adjacent fixed reporting intervals commonly use [from,to) so an event at the boundary is counted once. ago(1h) subtracts one hour from current UTC time; within a single query statement the current time is consistent across uses. Datetime plus/minus timespan gives a datetime; subtracting datetimes gives a timespan. Declare UTC/event-time versus ingestion-time semantics before interpreting late arrivals.

This preserves a fixed-bin reference, not a rolling query:

requests
| where timestamp > ago(1d)
| make-series p95 = percentile(duration, 95) default = double(null)
  on timestamp step 1m by name
| render timechart

make-series emits aligned arrays per group. Without explicit start/end, the observed data determines boundaries; for comparable dashboards specify from and exclusive to. It defaults absent bins to zero, so the explicit null above avoids inventing a latency measurement. Arrays have a documented 1,048,576-element limit: bound the interval and resolution. render is a visualization instruction interpreted by the client, not a transformation that adds missing samples.

A rolling five-minute p95 sampled every minute instead needs raw rows satisfying a membership rule, for example t − 5 minutes < event time ≤ t. One event may contribute to several samples. Minute p95s of 1000, 100, 100, 100, 100 average to 280, while the exact nearest-rank p95 of those five raw observations is 1000. Kusto's estimate need not equal a toy exact estimator on every dataset; the membership distinction remains.

For complete five-minute count bins, count()/300.0 is observed requests per second. Under classic sampling, use represented counts; for a partial current bin, divide by actual observed duration or exclude it. Zero requests, missing latency, and a failed telemetry collector are three different conditions. series_decompose_anomalies scores residuals after decomposition (default custom Tukey behavior); series_iir applies an IIR filter for operations such as smoothing. Neither reconstructs raw rolling percentiles from already reduced p95s. Bin, make-series, anomalies, IIR.

Pivots and unpivots: evaluate pivot

An ADX/Fabric pivot turns category values into columns and aggregates cells. The following assumes an ADX table exposing the stated lowercase telemetry schema:

requests
| where timestamp > ago(1d)
| summarize Total = count() by name, cloud_RoleName
| evaluate pivot(cloud_RoleName, sum(Total))

The grain is one row per operation name and one column per role. Compare sums of cells with the pre-pivot counts. A new or missing role can change the schema; for automation, constrain categories and declare OutputSchema, or keep long-format rows. A declared schema mismatch raises an error rather than silently inventing a compatible contract.

evaluate narrow() produces a generic three-column display representation, including string-valued cells. It is not a lossless reconstruction of original business keys and types. A fixed union of named projections can be a clearer wide-to-long design. These plugins are not supported on every KQL surface. Pivot, narrow.

Performance rules

Optimize only after predicting the result:

1. Restrict permitted tables, time and tenant. Prefer a real datetime column and selective predicates with the right semantics.
2. Project required payload before joins/materialization. An allow-list reduces returned exposure but does not replace authorization.
3. Summarize early only when the next operation needs that grain. Preserve counts for weighted averages; do not pre-filter away events needed by latest-state selection.
4. Choose regular/broadcast/lookup/shuffle from measured sizes and key distribution, not a fabricated join-left limit.
5. Compare equivalent outputs before CPU/memory/latency. top and the corresponding sort/take have documented equivalent performance.

Use supported query diagnostics and completion statistics. .show queries reports visible completed-query information including CPU, peak memory and failure state; .show running queries addresses running work. These are not guaranteed per-operator profiles. Progressive results affect delivery and require a compatible client; enabling them is not itself optimization or a resource limit.

Bound lookback, response rows/bytes, timeout and concurrency, and reject partial failures. query_take_max_records limits returned records, not all scanned or intermediate work. query_results_cache_max_age permits a bounded-age cached result, not guaranteed freshness; choose it only when the product tolerates that age. Do not remove filters on a live large table merely to demonstrate a limit.

Cost is workload- and product-specific. ADX planning includes engine/data-management instances, storage/transactions, networking and service markup; it is not a universal price per KQL query. Hot-cache horizon, retention, ingestion, concurrency and view maintenance affect capacity. A materialized view trades background CPU/storage for less repeated aggregation; frequent refresh alone is insufficient justification. Measure with representative data and cold/warm cache, then use current regional pricing—no price or three-second benchmark is claimed here. Diagnostics, request properties, cost inputs.

Query injection and how to defend

Untrusted input must be a value, never query syntax. This retained C# fragment illustrates the defect; do not expose a vulnerable endpoint to try it:

// ❌ INJECTION — never concatenate untrusted input
var q = $"requests | where name == '{userInput}'";
// userInput = "x' or 1==1 | take 1000000 //"

Interpolation lets the supplied quote end a string and introduce operators. It does not guarantee a million returned rows, bypass the principal's permissions, or inevitably hang a thread: table size, limits, parsing and client behavior still apply. The security defect is that input can alter the authorized query's intent.

Declare typed scalar parameters, then bind their values through request properties. Table names, column names and sort syntax are not scalar parameters; map structural choices to a small set of reviewed templates. SQL-style quote replacement and inserting a datetime literal around untrusted text are not substitutes for binding.

declare query_parameters(op:string, lookback:timespan);
requests
| where timestamp > ago(lookback)
| where name == op
| project timestamp, name, duration, success

The application must independently authorize the database/tenant, validate a positive bounded lookback, restrict output fields and enforce timeouts/concurrency. A string such as x' or 1==1 // stays a literal operation name when properly bound; it need not be rejected as a string, and it must not broaden access. A saved function centralizes query logic but is not automatically a permission boundary.

Update policies are ingestion transformations, not row-level security. Use supported RBAC and, when required, an explicit RLS policy. RLS's principal is the query execution identity: a shared service identity is not automatically each web user's identity. Authorize/derive tenant context server-side or design the identity flow deliberately; never trust a caller-supplied tenant merely because it is parameterized. Check access to source and aggregate tables too. Parameters, roles, RLS.

Materialised views and persisted functions

A persisted function stores parameterized query logic, evaluated on invocation. A materialized view incrementally maintains a supported aggregation. Directly querying the view combines materialized state with the not-yet-materialized delta; latency is not constant, and ingestion delay remains relevant. Querying only the materialized portion has a different freshness contract.

This retained reference is an ADX management command, assuming an existing ADX table requests(timestamp:datetime, name:string, duration:real) with unsampled request rows; it was not executed:

.create materialized-view RequestsHourly on table requests
{
    requests
    | summarize Count = count(), p95 = percentile(duration, 95)
        by name, bin(timestamp, 1h)
}

The dated create-view documentation lists percentile/percentiles as supported aggregates. That permits per-group percentile state; it does not make the average of hourly p95s equal to daily p95. Requery the appropriate population or use a documented mergeable representation. Sum hourly counts only for the matching complete hours and population. A newly created view without backfill covers new ingestion, not automatically all historical source rows. Plan permissions, backfill cost, grouping cardinality and monitoring explicitly.

A saved function can centralize the denominator without storing aggregate data. Under a one-row-per-unsampled-request ADX schema:

.create-or-alter function with (folder = "dashboards")
    GetErrorRate(lookback:timespan = 1h, op:string = "*")
{
    requests
    | where timestamp > ago(lookback)
    | where op == "*" or name == op
    | summarize Total = count(), Errors = countif(success == false) by name
    | extend ErrorRate = todouble(Errors) / Total
}

GetErrorRate(1h, "Checkout") | top 10 by ErrorRate desc invokes it as a tabular expression. The "*" parameter is an explicit all-operations convention, not an authorization check. With grouping, empty input returns no groups. An API can omit the wildcard option and add an upper time boundary if its contract requires it. Function creation and alteration require appropriate management permissions; dashboard readers should not receive those just to invoke a reviewed query.

Version saved definitions with their schema/units/denominator. Update policies can transform newly ingested data into typed or denormalized target tables; their transaction/failure behavior must be designed, and a dropped tenant column can undermine later enforcement. This is distinct from both views and RLS. View behavior, create restrictions, functions, update policy.

Worked examples

For each example, check the declared schema and identify the output grain before adapting it. Expected counts and counterexamples below are derived from explicit populations; there are no fabricated Kusto result captures. The first two target Application Insights query names, the third a custom ADX table, and the fourth a .NET-to-ADX integration boundary.

Example 1 — Production triage query

For the lowercase Application Insights schema, distinguish represented counts from percentiles of observed rows:

let toTime = now();
let fromTime = toTime - 1h;
let bucket = 1m;
requests
| where timestamp >= fromTime and timestamp < toTime
| project timestamp, name, cloud_RoleName, duration, success, itemCount
| summarize
    Represented = sum(itemCount),
    Failures = sumif(itemCount, success == false),
    ObservedP95 = percentile(duration, 95),
    ObservedP99 = percentile(duration, 99)
  by Minute = bin(timestamp, bucket), cloud_RoleName, name
| extend ErrorRate = iff(Represented == 0, real(null), 1.0 * Failures / Represented)
| order by Minute desc

The grain is minute × role × operation. A retained success with weight 1 and failure with weight 9 imply represented counts 10 and 9, hence error rate 0.9—not the observed-row failure rate 0.5. The percentile columns are deliberately named Observed: unequal sampling can affect interpretation. Unknown success is not counted by success == false; decide whether unknowns need a separate metric. A current or leading partial minute is not a complete rate bucket, and late arrivals can revise results.

1.0 * Failures / Represented requests real arithmetic; the null guard handles a zero represented denominator. When there are no grouped rows, there is no series to fill. Keep missing latency distinct from zero and telemetry-health failures distinct from no traffic. Use normalized operation names to avoid an unbounded series for per-user/per-object URLs.

Example 2 — End-to-end trace reconstruction

Explicitly scope each leg, retain provenance, then sort the combined timeline:

let start = ago(1h);
let finish = now();
let opId = "abcd1234-5678-90ab-cdef-1234567890ab";
union withsource = SourceTable
    (requests | where timestamp >= start and timestamp < finish and operation_Id == opId
      | project timestamp, operation_Id, Detail=name),
    (dependencies | where timestamp >= start and timestamp < finish and operation_Id == opId
      | project timestamp, operation_Id, Detail=name),
    (exceptions | where timestamp >= start and timestamp < finish and operation_Id == opId
      | project timestamp, operation_Id, Detail=outerMessage),
    (traces | where timestamp >= start and timestamp < finish and operation_Id == opId
      | project timestamp, operation_Id, Detail=message)
| order by timestamp asc

The output keeps individual telemetry items instead of joining every request to every exception. Four matching inputs across the legs produce four rows, even when they share an operation ID. SourceTable may be qualified according to the source context. An operation ID groups a distributed trace; span IDs and parent IDs are needed to reconstruct actual parentage. Events outside the hour are deliberately excluded, so this is a bounded trace slice, not proof the complete trace was retained.

For differently named text fields, coalesce(name, type, message) selects the first non-null or non-empty string when the arguments share a supported type. Normalize union schemas before referring to columns that could acquire type suffixes. Message/exception details can contain personal data; project/redact accordingly and return only what the caller may read. Telemetry model, coalesce.

Example 3 — Latest state of a fleet from an event log

The following ADX fixture is self-contained reference KQL:

let DeviceEvents = datatable(DeviceId:string, TimeGenerated:datetime, FirmwareVersion:string, Region:string)
[
    "a", datetime(2026-10-01), "1.2.3", "west",
    "a", datetime(2026-10-02), "2.0.0", "west",
    "b", datetime(2026-10-02), "1.2.3", "east"
];
DeviceEvents
| summarize arg_max(TimeGenerated, FirmwareVersion, Region) by DeviceId
| where FirmwareVersion in ("1.2.3", "1.2.4")
| summarize Devices = count() by Region, FirmwareVersion
| evaluate pivot(FirmwareVersion, sum(Devices), Region)

Only device b remains, producing an east/1.2.3 count of 1. If firmware filtering happens before arg_max, device a's obsolete 1.2.3 record survives; the answer incorrectly includes west too. The filter placement changes correctness, not merely readability. A seven-day prefilter would mean “latest seen in seven days”, excluding silent older devices; state that qualification instead of calling it latest ever.

The pivot output grain is region, not one row per device. Reconcile the cell total against the pre-pivot device count. This exploratory pivot has a data-dependent column set; keep long format or a bounded declared output schema for a typed production consumer. Equal event timestamps need a separate version/conflict rule; the fixture deliberately has no tie.

Example 4 — Parameterised KQL from .NET with Managed Identity

This is a reference integration fragment for Microsoft.Azure.Kusto.Data, not a compiled or deployed .NET application. It requires a raw-string-capable C# project, a package version selected and compiled by the consuming application, an ADX endpoint/database, and an ADX table requests(timestamp:datetime, name:string, success:bool). The service identity needs explicit read permission and network access. The application supplies cluster, database and validatedOperationName; they are not raw caller-selected endpoints.

using System;
using System.Diagnostics;
using Kusto.Data;
using Kusto.Data.Common;
using Kusto.Data.Net.Client;
 
var connection = new KustoConnectionStringBuilder(cluster)
    .WithAadSystemManagedIdentity();
using var client = KustoClientFactory.CreateCslQueryProvider(connection);
const string query = """
declare query_parameters(op:string, lookback:timespan);
requests
| where timestamp > ago(lookback)
| where name == op
| summarize Total=count(), Errors=countif(success == false)
""";
var properties = new ClientRequestProperties();
var trace = Activity.Current?.TraceId.ToString() ?? Guid.NewGuid().ToString("N");
properties.ClientRequestId = $"dashboard.query;{trace}";
properties.SetOption("query_take_max_records", 10000L);
properties.SetParameter("op", validatedOperationName);
properties.SetParameter("lookback", "1h");
using var reader = client.ExecuteQuery(database, query, properties);
while (reader.Read())
{
    var total = reader.GetInt64(reader.GetOrdinal("Total"));
    var errors = reader.GetInt64(reader.GetOrdinal("Errors"));
    // Map approved fields to the application response after successful completion.
}

Microsoft's request-properties guidance supports string and long parameter overloads and recommends KQL literal strings for other types; the fixed "1h" value is parsed as the declared timespan. The operation string remains data. The synchronous call follows the basic SDK example; this is not an async web endpoint or complete partial-failure handler. The consuming service must use its chosen package's cancellation/timeout/error-completion contract and test it before shipping. Reuse clients through a deliberate application lifecycle rather than constructing an authentication/client pool for each request.

Managed identity selects the authentication identity; it does not grant RBAC permissions. ClientRequestId supports correlation and should not contain personal data or secrets. Converting TraceId to string before the fallback avoids mixing ActivityTraceId with a string. Counts here are unsampled rows; if the ADX ingestion uses sampling weights, change the declared schema and denominator accordingly. Connection API, request properties, basic C# query.

Hands-on exercises

These can be completed as table/contract exercises without service access. Optional service execution belongs in an authorized disposable environment, never an intentionally vulnerable endpoint or an unfiltered production load test.

1. Reconnaissance. Given requests(timestamp, duration, operation_Id, itemCount) and AppRequests(TimeGenerated, DurationMs, OperationId, ItemCount), write the schema mapping and identify the product query surface. If you have approved access, verify with getschema; do not assume all four lowercase telemetry tables exist in every database.
2. Triage. Explain Example 1's shape and calculate observed versus represented error rates for one success of weight 1 and one failure of weight 9. Define how a partial minute and unknown success are displayed. A latency target is a future measured acceptance criterion, not an already reproduced three-second result.
3. Join discipline. Use two requests on key x, one successful and one failed, and three tags on key x. Predict inner, default innerunique, leftsemi, and leftanti. Add an unmatched request on key y and repeat for leftouter. Do not try to reproduce a nonexistent 500k-left-input limit.
4. Latest-state pivot. Predict Example 3 with the firmware filter before and after arg_max. Identify the output grain and reconcile totals. Explain what a time filter would exclude.
5. Persisted function. Design Triage(lookback:timespan, bucket:timespan) using Example 1's body with a bounded lookback and positive bucket. Separate function-management permission from query permission. If one user asks for a zero bucket or an excessive range, decide what the API rejects before a service call.
6. Injection drill. Compare interpolation with the declared op parameter using the literal x' or 1==1 //. Review the query text and parameter map offline. Add the separate tenant-authorization and cost checks; do not publish an insecure test endpoint.

Self-check questions

1. What is the pipeline contract, and how do you debug an intermediate shape?
2. What do the default 500k/64 MB limits actually bound, and how does failure appear?
3. When is leftsemi preferable to inner?
4. What does arg_max guarantee, and what do time filtering and ties change?
5. How do default dcount and accuracy 4 differ?
6. Why is unordered take unsuitable for a ranking?
7. How can materialize help and hurt?
8. What does let bind, and what does it not secure?
9. What separates parameter binding, structural allow-lists and authorization?
10. When would you choose a view rather than a saved function?
11. When does union answer a different question from join?
12. Why project early, and when would early projection be wrong?

High-signal resources

Use primary reference contracts for syntax, defaults and limits; use courses and practitioner explanations for practice. Check the product badge and documentation date before copying an example. The linked references below and the section-local citations were checked on 8 October 2026; mutable documentation is not a pinned service/runtime version.

Official docs

• KQL overview: statements, pipes and query versus management operations.
• Best practices: predicate/projection and strategy guidance, subject to semantic equivalence.
• Query limits: result, memory and concurrency boundaries.
• Materialized views: materialized state, delta and freshness.
• Application Insights data model and AppRequests schema: actual telemetry names and units.
• Kusto request properties: typed input binding and diagnostics.

Books or courses

Start with Microsoft's first KQL query module and work the operators against a small known table. When choosing any book or video course, require exercises on default innerunique, sampling, window membership and parameterization; verify its examples against current primary references. A course's popularity or advertised duration is not evidence for an API contract.

Practitioner posts

Use practitioner walkthroughs as proposals to test: write the assumed schema, compute a tiny counterexample, then check the documented operator. For a post that recommends “always put the small table on the left”, ask whether it means regular join, broadcast or lookup. For a rolling-percentile recipe, inspect row membership rather than the chart label. For an SDK example, verify authentication, authorization and the actual target product separately. This reading method is more reliable than treating any secondary series as authoritative for mutable service limits.

Weekly milestones

1. Day 1: Identify product/schema and trace a complete query prefix. Finish reconnaissance and self-check 1–2.
2. Day 2: Compute row counts, sampling-weighted counts and ratios for Example 1; explain missing and partial intervals. Finish self-check 5–6 and 12.
3. Day 3: Predict every small join fixture and contrast union. Finish exercise 3 and self-check 3 and 11 without stressing a live cluster.
4. Days 4–5: Reconcile latest-state/pivot totals, fixed bins versus rolling membership, and materialization/view choices. Finish exercises 4–5 and self-check 4, 7 and 10.
5. Days 6–7: Review typed parameters, authorized tenant context and .NET integration prerequisites. Finish the offline injection drill and self-check 8–9. Optional service checks require a separately approved environment and recorded completion/errors.

Progress means correct predictions and explanations, not merely executing a query or visiting a page. If a prediction fails, isolate the smallest table that distinguishes your assumption from the documented behavior.

How it shows up in the capstone

Design the dashboard path as validated filters → API authorization → bounded parameterized query → checked completion/result schema → typed chart data. Each tile specifies units, population, sampling, time membership, missing-data behavior and freshness. A read-only identity still needs workload limits; a cache must not cross an authorization boundary.

Use saved functions to centralize reviewed definitions. Introduce materialized views only when the measured reuse and ingestion/cardinality costs justify them. A per-hour percentile tile and a daily percentile tile cannot share a naive average-of-p95 definition. Correlate query diagnostics with a non-sensitive request ID, and test denied access, excessive windows, empty results, partial failures and late data before claiming production readiness.

Previous chapter → Ch 11 — Highcharts Next chapter → Ch 13 — Azure App Service

Corrected contracts and failure analysis

Keep four counterexamples available during review: a many-to-many join changes grain; default innerunique drops one left representative; latest-state filtering can resurrect history; fixed bins do not implement overlapping windows. Sampling introduces a fifth: one stored row may represent several events. Security has similarly separate layers—typed values, allowed structure, data authorization and resource bounds.

The following new CPU teaching model checks these relational/arithmetic consequences using plain Python. It chooses both possible innerunique representatives rather than pretending to emulate Kusto's selection. It implements exact nearest-rank percentiles only for the explicit toy data, not Kusto's estimator. No service, SDK, parser, optimizer, access-control engine or billing behavior is reproduced.

from math import ceil
 
requests = [("x", "ok", True), ("x", "fail", False)]
tags = [("x", "a"), ("x", "b"), ("x", "c")]
pairs = [(req, tag) for req in requests for tag in tags if req[0] == tag[0]]
assert len(pairs) == 6
assert sum(not req[2] for req, tag in pairs) == 3
semi = [req for req in requests if any(req[0] == tag[0] for tag in tags)]
assert len(semi) == 2 and sum(not req[2] for req in semi) == 1
anti = [req for req in requests if not any(req[0] == tag[0] for tag in tags)]
assert anti == []
for representative in requests:
    unique_pairs = [(representative, tag) for tag in tags]
    assert len(unique_pairs) == 3
    assert sum(not req[2] for req, tag in unique_pairs) in (0, 3)
with_orphan = requests + [("y", "orphan", True)]
outer = [(req, tag) for req in with_orphan
         for tag in ([t for t in tags if t[0] == req[0]] or [None])]
assert len(outer) == 7 and outer[-1][1] is None
 
history = [("a", 1, "1.2.3"), ("a", 2, "2.0.0"), ("b", 2, "1.2.3")]
def latest(rows):
    result = {}
    for row in rows:
        if row[0] not in result or row[1] > result[row[0]][1]:
            result[row[0]] = row
    return list(result.values())
assert [r[0] for r in latest(history) if r[2] == "1.2.3"] == ["b"]
assert len(latest([r for r in history if r[2] == "1.2.3"])) == 2
 
def exact_p95(values):
    return sorted(values)[ceil(0.95 * len(values)) - 1] if values else None
latencies = [(0, 1000), (1, 100), (2, 100), (3, 100), (4, 100), (5, 100)]
def rolling(t):
    return exact_p95([v for time, v in latencies if t - 5 < time <= t])
assert rolling(4) == 1000 and rolling(5) == 100 and rolling(20) is None
assert exact_p95([v for time, v in latencies if 4 <= time < 5]) == 100
assert (1000 + 100 + 100 + 100 + 100) / 5 == 280
weights = [(True, 1), (False, 9)]
assert sum(w for ok, w in weights if not ok) / sum(w for ok, w in weights) == 0.9
assert sum(not ok for ok, w in weights) / len(weights) == 0.5
print("join=6; failures=3; semi=2; outer=7; latest=b; rolling=1000/100; weighted=0.9")

Boundary exercise with solution

Join two request rows to three tags on one key, then compute failure counts. What goes wrong if the join multiplicity is ignored?

Source-backed review notes

The decisive corrections are documented at their point of use: top is equivalent to sort/take, default innerunique deduplicates the left, result limits report partial failure, materialize shares a bounded node cache, and RLS is distinct from ingestion transformation. Documentation supports the language and service contracts; Python checks only the stated teaching model. Neither MDX compilation nor this source review establishes cloud runtime correctness, production performance or a completed deployment.