Kusto and KQL — pipelines, safe queries and honest windows

A dashboard request says: “Show the rolling five-minute p95 latency, sampled every minute.” The tempting query groups by one-minute timestamps. It produces one-minute p95 values, not five-minute p95 values. Joining a few chart points with a line does not add the missing observations.

Separate the chart's sample interval from its lookback duration. We will compute both on the same six-request dataset, expose why averages of percentiles give the wrong answer, and decide what a missing point means.

KQL surfaces, schema and the storage model

Kusto Query Language is shared by Azure Data Explorer (ADX), Azure Monitor/Application Insights, Microsoft Sentinel and other Microsoft products, but they do not expose identical tables, plugins or management commands. Resource Graph supports a subset; an ADX materialized-view command is not an Azure Monitor workspace setup command. Establish the product and schema before copying a query. This chapter uses the current documented ADX/Kusto language contract as checked on 7 October 2026, with Application Insights examples explicitly labelled by their table convention. No Kusto service execution is claimed.

ADX stores data in compressed columnar extents, with indexes/metadata that help prune irrelevant data. It is optimized for append-oriented analytical ingestion, not a row-by-row OLTP update loop. However, administrative soft-delete, purge and other data-management operations do exist: “append-oriented” does not mean data can never be removed. Retention controls how long data remains queryable; hot-cache policy controls fast-tier placement, not permission or retention. Inspect effective database/table policy inheritance instead of assuming a universal default.

A datetime predicate on a real stored column can eliminate irrelevant work; applying a conversion to every row may lose that advantage. Extents are not a promise that every table is rigidly partitioned by an application timestamp. Ingestion time and event time differ, late arrivals occur, and partition/merge policies must match measured access patterns. Row-store ingestion buffering and columnar extents are implementation stages, not SQL-style row-store primary-key design choices.

KQL's text is a logical pipeline, while the engine still has optimization/planning behavior. It can push suitable predicates and choose physical execution strategies. Do not claim “there is no query planner”. Write selective, narrow, semantically equivalent pipelines so their cost and intermediate shapes are reviewable, then inspect actual diagnostics. Moving a filter before an aggregation can change the question, as the device example below demonstrates.

Pipeline syntax and core operators

A tabular operator takes a table and produces a table. A complete prefix can be run to inspect its shape; scalar functions and management commands are not all tabular operators. Statements are separated by semicolons. Start with a named, permitted table and getschema in a read-only query surface; take is a useful sample, not an instruction to scan every stored byte or a random statistically representative sample.

The following self-contained fixture gives the operators something concrete to transform:

let Requests = datatable(Time:datetime, Operation:string, DurationMs:real, Success:bool)
[
  datetime(2026-10-07T12:00:10Z), "GET /orders", 100.0, true,
  datetime(2026-10-07T12:00:40Z), "GET /orders", 900.0, false,
  datetime(2026-10-07T12:01:10Z), "GET /orders", 200.0, true,
  datetime(2026-10-07T12:01:20Z), "GET /health", 5.0, true
];
Requests
| where Time >= datetime(2026-10-07T12:00:00Z) and Time < datetime(2026-10-07T12:02:00Z)
| where Operation == "GET /orders"
| project Time, Operation, DurationMs, Success
| summarize Total=count(), Errors=countif(Success == false), MeanMs=avg(DurationMs)
    by Minute=bin(Time, 1m), Operation
| extend ErrorPercent = 100.0 * Errors / Total
| order by Minute asc

Read the shapes: four rows → four in-range rows → three order rows → two minute/operation groups → two rows with an error percentage. The first minute has Total 2, Errors 1, MeanMs 500 and ErrorPercent 50; the second has 1, 0, 200 and 0. Percentiles are a different aggregation from the mean, and neither changes group membership.

The core vocabulary is broader than the original “eight/ten operators” slogan:

• where filters rows. String equality == is case-sensitive; =~ is case-insensitive. has matches a term; contains matches a substring. Choose semantics first, then exploit the term index where applicable.
• project selects/renames/computes columns and drops unspecified columns. project-away drops named columns; project-rename changes names without dropping the others. An allow-list projection is safer for data returned by an API than an ever-expanding deny-list of sensitive fields.
• extend adds or replaces a computed column while retaining other columns. Carrying a large dynamic payload through joins increases intermediate cost; an added column does not itself narrow the row.
• summarize groups and aggregates. Without by, many aggregates yield one result row even for an empty input (count is zero); with grouping there may be no groups. Handle denominators explicitly.
• order by sorts. top 10 by DurationMs desc expresses the largest ten, whereas take 10 gives up to ten rows with no stable ordering contract. A sort plus take can be optimized equivalently; do not promise top is always faster. Add a deterministic tie-breaker when the UI requires one.
• distinct returns distinct tuples. count returns a one-row table with a count column; use toscalar(...) where a scalar expression is required. dcount is an approximate scalar aggregate, not a replacement when actual distinct rows are needed.
• getschema returns column names/types. Application Insights requests.timestamp/duration/operation_Id/itemCount differs from workspace AppRequests.TimeGenerated/DurationMs/OperationId/ItemCount; rename and convert deliberately, not by guessing.

Practice: replace == with =~ for an operation name that differs only in case. The result can change; this is not merely a performance hint. Remove summarize to inspect the three source rows, then restore it. Replace order by with take 1; you can no longer claim the result is the earliest minute.

Aggregation, latest state and exactness

count/countif count rows; sum and sumif add numeric values or sample weights. avg is a sum/count concept, while min/max are extrema. percentile and percentiles estimate nearest-rank quantiles; report population, units and sampling before interpreting p95/p99. The exact Python nearest-rank fixture later in this lesson intentionally does not reproduce Kusto's approximation algorithm.

dcount(x, accuracy) uses a HyperLogLog variant. The documented default accuracy 1 has an error standard deviation around 0.8%; accuracy 0 is 1.6%, and accuracy 4 is 0.2%, not exact. Small-set exact-count optimizations exist at documented thresholds. These percentages describe an error distribution, not an absolute bound for every input. Use count_distinct, or distinct followed by count within supported limits, when an exact cardinality is required. HLL sketches support approximate count merging; they cannot recover the members of a make_set that was truncated.

make_set collects distinct values; make_list retains multiplicity, with ordering dependent on input ordering. Both require explicit bounded size for large populations (the modern documented maximum is 1,048,576 elements). A million-element payload is usually a poor dashboard response. Use a summary/top-N with an explicit “other” category when that answers the question.

arg_max(Time, columns...) by Entity selects a row at the greatest time for each entity. Ties are not a deterministic business conflict-resolution rule; supply a version/ordering design when two events have equal timestamps. A seven-day filter yields “latest seen in seven days”, not latest ever. Choose required payload columns instead of * where feasible.

let DeviceEvents = datatable(DeviceId:string, Time:datetime, Firmware: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(Time, Firmware, Region) by DeviceId
| where Firmware == "1.2.3"
| project DeviceId, LastSeen=Time, Firmware, Region

The answer is device b only. Filtering Firmware before arg_max would resurrect a's obsolete 1.2.3 row. This corrects the original claim that moving the filter would not change correctness. Maintaining a current-state table moves work to ingestion/updates but introduces ordering, replay and consistency obligations; querying the append log trades that maintenance for read work.

Joins: semantics before placement

KQL's default join is innerunique, which deduplicates the left input by join key before matching. Use explicit kind=inner when all matching left duplicates must survive. leftouter retains all left rows; unmatched scalar fields use type-specific missing values (strings are often empty rather than a nullable string). leftsemi retains matching left rows without right payload; leftanti keeps orphans. Right/full variants follow the corresponding retained-side contract.

let L = datatable(Key:string, Event:string)["x", "a", "x", "b", "y", "c"];
let R = datatable(Key:string, Label:string)["x", "one", "x", "two"];
L | join kind=inner (R) on Key

This returns four matching rows: a/one, a/two, b/one, b/two. The default innerunique can keep one of a/b and return two rows instead. leftsemi returns a and b; leftanti returns c. An outer join adds c with missing right fields. Joining request rows to several exceptions can legitimately multiply rows, so counting joined rows does not count requests.

Placement is strategy-dependent, not a mythical input limit. For ordinary joins, a small left input is often recommended; broadcast join broadcasts the small left input. LargeFacts | lookup SmallDimension on Key instead broadcasts the right dimension and can fail when it is too large. Large/high-cardinality joins may benefit from shuffle; skewed keys can still concentrate work. Filter both sides to the intended time/tenant range and preserve join semantics when changing sides. A left outer join cannot be flipped casually without changing what is retained.

The documented defaults 500,000 records or 64 MB concern result truncation returned to the client (also relevant to cross-cluster subqueries), not a silent 500k-left-input join cap. Exceeding them reports a partial query failure. A client that displays received rows while ignoring completion/error status can present misleading partial output. set notruncation is not a dashboard fix: aggregate or deliberately export/stream an authorized large result, and keep memory/time/workload limits. A tiny returned take result does not bound the cost of every upstream join.

Practice: predict the four-row inner fixture, then add the first y dimension row, ["y", "three"], to R. Inner now returns five, leftanti returns zero, and the ordinary inner result still has all three event identities. This tests multiplicity, not a nonexistent join-input truncation rule.

Union, correlation and table shape

union stacks rows; it does not match keys. Outer union keeps the union of columns, and a name used with different types may produce type-suffixed columns. kind=inner keeps the common schema. withsource=SourceTable labels the source expression/table. Missing numeric values and absent string values need explicit handling; string coalesce skips empty strings as well as null-like values in its supported scalar contract.

let start = ago(1h);
let traceId = "synthetic-trace-id";
union withsource=SourceTable
  (requests | where timestamp >= start and operation_Id == traceId
    | project timestamp, operation_Id, Detail=name),
  (dependencies | where timestamp >= start and operation_Id == traceId
    | project timestamp, operation_Id, Detail=name),
  (traces | where timestamp >= start and operation_Id == traceId
    | project timestamp, operation_Id, Detail=message),
  (exceptions | where timestamp >= start and operation_Id == traceId
    | project timestamp, operation_Id, Detail=outerMessage)
| order by timestamp asc

This is an Application Insights-schema query design, not a captured service result. It keeps each telemetry item rather than multiplying requests by all trace rows. For actual parentage also project span IDs/parent IDs using each table's schema; an operation ID groups a trace but does not itself encode every edge. Trace/message fields may contain personal data; access control, retention and redaction still apply.

union isfuzzy=true relaxes source resolution under documented conditions when some sources exist; it does not ignore every execution error or grant cross-workspace permission. union + summarize can replace a join only when it expresses the same intended computation (for example combining additive counters), not merely because a join key is high-cardinality.

Let, reuse and materialization

let binds an expression, not an immutable snapshot of its evaluated result. Scalar, tabular and function bindings help name a lookback, a permitted operation set or a reusable calculation. Repeated use may reevaluate the expression, especially relevant for nondeterministic functions. materialize(...) caches a tabular expression for reuse within a query, not across requests.

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

The shared materialization cache is documented as 5 GB per cluster node across concurrent queries, not a reserved 5 GB allocation per invocation; exhaustion aborts the query. Narrow/filter before materialization when equivalent, then compare with and without it. A once-used subquery often gains nothing from materializing. The as/partition materialized hint applies only in its documented operator context, not as a universal add-on to any pipeline.

The two statements above return separate result tables. Client code must deliberately consume the expected result set(s); no chart should silently read only whichever table happens to be first. Naming start gives a consistent reviewable boundary and makes safe parameter constraints obvious; it does not by itself provide injection defense for string-built query text.

Fixed bins, query windows and chart resolution

Use half-open [from,to) filters for adjacent fixed reporting periods so boundary events are counted once. bin(timestamp,5m) rounds down; rate sum(itemCount)/300.0 is requests/second only for a complete five-minute bucket with the documented sampling convention. A partial current bucket needs its actual observation duration or exclusion. Empty count buckets can be filled with zero only if telemetry availability is known; a quiet service and a broken collector are not equivalent.

make-series constructs aligned arrays of aggregated values and axis positions per group. An explicit start/end fixes comparison boundaries. For missing latency bins use a null default rather than zero; a zero is an observed latency claim. series_decompose_anomalies uses decomposition and a documented residual scoring rule (default ctukey percentile-based behavior), not “three standard deviations” by definition. series_iir filters/smooths a numeric series; it does not reconstruct a rolling percentile from already aggregated p95s. Choose counts/averages/sketches based on their actual composability.

The preserved worked lab below separates membership, sample interval and lookback duration before any production query is attempted.

First decide which requests belong

For this lesson, a rolling window at minute t includes observations with t - 5 < time <= t. The open-left, closed-right interval is a chosen convention. A fixed one-minute bin uses a different grouping convention: timestamps are rounded down to the minute boundary. Microsoft documents bin() as rounding down and make-series as aggregating by axis bins. Neither implies overlapping lookbacks.

Use the two branches to ask which source rows contribute to each plotted value. The same request can contribute to several consecutive rolling samples, but only one fixed minute bin. A chart without this membership definition is not reviewable.

A percentile you can check by hand

Take five requests with latency 100, 100, 100, 100 and 1,000 milliseconds. The nearest-rank p95 chooses rank ceil(0.95 × 5) = 5, hence 1,000 ms. This lab uses an exact nearest-rank definition. Kusto's percentile() estimates a percentile and need not match every small-fixture boundary exactly; we do not claim to reproduce its internal approximation algorithm.

from math import ceil
 
rows = [(0, 1000), (1, 100), (2, 100),
        (3, 100), (4, 100), (5, 100)]
 
def p95(values):
    if not values:
        return None
    values = sorted(values)
    return values[ceil(0.95 * len(values)) - 1]
 
def rolling(t):
    return p95([value for time, value in rows if t - 5 < time <= t])
 
def minute_bin(t):
    return p95([value for time, value in rows if t <= time < t + 1])
 
assert minute_bin(4) == 100
assert rolling(4) == 1000
assert rolling(5) == 100  # the request at time zero has aged out
assert rolling(20) is None
assert (1000 + 100 + 100 + 100 + 100) / 5 == 280
print("bin@4=100; rolling@4=1000; rolling@5=100; missing=None")

At minute four, the rolling window includes the slow request at zero; the minute-four bin does not. At minute five the strict left boundary excludes zero. The mean of the five per-minute p95s is 280 ms, which is not the five-minute p95 of 1,000 ms. Percentiles are not generally composable by averaging; use raw observations or a documented mergeable sketch.

Read KQL without inventing its behavior

The expression make-series p95 = percentile(duration, 95) on timestamp step 1m by name groups into one-minute axis bins per operation name. A five-minute rolling result needs an explicit lookback operation over the underlying data or an appropriate documented aggregation representation. Merely replacing step 1m with step 5m produces five-minute bins, still not a rolling value sampled each minute.

For a real workspace, decide whether event time or ingestion time defines membership, whether sampling weights are required, and whether failed/time-out requests are included. The Python fixture has one observation per request and no sampling. A deployment's schema, missing values, data volume and query cost must be checked before publishing a runnable KQL adaptation. No Kusto service was used here, so there is no purported tested cloud query to copy.

Missing is not zero

A minute with no requests has no observed latency percentile. Plotting zero would suggest excellent performance even though nothing was measured. For counts, a known empty minute may correctly be zero; for latency, preserve null/missing and let the chart show a gap. A failed instrumentation pipeline is a third condition, not a quiet service.

A success-ratio SLI also needs counts rather than averages of group percentages. If one tenant has one successful request out of one and another has ninety out of a hundred, the overall success ratio is 91/101, about 90.10%, not (100% + 90%)/2 = 95%. Report tenant slices too: the aggregate can conceal a failing small tenant.

Exercises

1. At minute three, which times are in the rolling five-minute window and what is p95?
2. Would five-minute fixed bins answer a rolling-window question at minute six?
3. Why should an empty latency bin not be filled with zero?

Limits

The code verifies window membership and exact toy percentiles, not Kusto performance or approximation error. Larger windows duplicate logical membership across samples, so a production design must budget computation and choose data structures deliberately. Late data can revise historical samples; dashboards should state freshness and revision policy.

Pivots, unpivots and stable dashboard contracts

evaluate pivot(Category, sum(Value), Group) turns long rows into one column per category. Aggregate/filter before widening. Without an explicit output schema, a new category can add a column and break a typed consumer; a rare absent category can remove one. For automated dashboards, use a bounded category set and OutputSchema or keep long format and let the chart group it.

let Counts = datatable(Region:string, Firmware:string, Devices:long)
["east", "old", 2, "east", "new", 3, "west", "new", 4];
Counts
| evaluate pivot(Firmware, sum(Devices), Region): (Region:string, old:long, new:long)

Expected semantic totals are east 5 and west 4. In a real fixture verify the plugin's missing-cell value and that the declared schema matches the actual supported output. evaluate narrow() is a supported wide-to-long plugin where available, producing generic row/column/value representation; it does not automatically restore your original business key/types. A deliberate union of named projections is often clearer for a fixed schema. Plugins are product-specific: keep an ordinary long-format fallback when the selected Azure query surface lacks pivot/narrow.

Worked latest-state pivot: first choose one current row per device, then filter relevant firmware, then count by Region/Firmware, then pivot. The final rows are regions, not one row per device. Check that the sum of pivot cells equals the pre-pivot device count. The original exercise conflated these two grains.

Performance, diagnostics and safe limits

Optimize measured work, not slogans:

1. Scope to permitted tables, tenant and time. Prefer a stored datetime predicate and selective term predicates where their semantics fit. Time filtering is often powerful, not always the most selective possible first predicate.
2. Project required columns before large joins/materialization. extend alone retains wide payloads; arg_max(Time, *) carries every column.
3. Aggregate early only when it preserves needed detail and multiplicity. A mean of means is wrong without counts; a mean of p95 values is not p95. Latest-state filtering has the ordering trap demonstrated above.
4. Select join strategy from sizes, key cardinality and skew, then verify semantics. Shuffle is not a cure for every imbalance; lookup assumes a small right side.
5. Read query completion status, CPU/memory/result statistics and the supported UI query diagnostics/plan. .show queries lists visible completed-query information under permissions, not guaranteed per-operator profiling; running queries use a different command. Progressive results change delivery, not inherently the optimizer or a safety limit.

Set reasonable lookback, output rows/bytes, server timeout, cancellation, concurrency and workload-group policy for API callers. query_take_max_records limits returned records in its documented context; it is not a security sandbox or bound on all scanned/intermediate work. Do not grant a dashboard principal administrator rights so it can “fix” limits. A read-only least-privilege role can still consume resources if arbitrary expensive queries are accepted.

For a slow-query exercise, use an approved disposable fixture and compare equivalent query outputs before comparing CPU/memory. Do not remove filters on a live billion-row table just to force an error. Report cold/warm cache, data size and concurrency with any timing; a target such as three seconds is an acceptance objective, not a benchmark already achieved by this lesson.

Parameter binding, authorization and .NET integration

A parameter substitutes a typed value, not a table/column/operator. Declare op:string and bind it through ClientRequestProperties. A payload such as x' or 1==1 // must remain the operation-name string; it is not a runnable operator sequence. Do not propose SQL-style quote replacement as an adequate KQL escaping library. If the caller can select a grouping column or sort order, map a small enum to a fixed reviewed query template.

declare query_parameters(op:string, fromTime:datetime, toTime:datetime);
requests
| where timestamp >= fromTime and timestamp < toTime
| where name == op
| summarize Total=sum(itemCount), Errors=sumif(itemCount, success == false)
| extend ErrorRate=iff(Total == 0, real(null), 1.0 * Errors / Total)

For this classic sampled schema the counts are represented estimates. A validated endpoint must also enforce a maximum range, fromTime < toTime, known operation values where appropriate, row/data permissions and cancellation. Parameterization prevents syntax injection; it does not authorize tenant access or cap cost by itself. Derive tenant from validated claims and bind that value too when the table holds multiple tenants. A saved function is reusable query code, not automatically a stored-procedure permission boundary.

The following Microsoft.Azure.Kusto.Data integration sketch uses the documented connection and request-property APIs. Its exact package patch must be selected and compiled in the consuming project; no .NET SDK or service execution is asserted here. Keep client construction in a reusable service lifecycle, not a new authentication/client pool for each incoming request.

using System.Diagnostics;
using Kusto.Data;
using Kusto.Data.Common;
using Kusto.Data.Net.Client;
 
// ADX example: Events(Time:datetime, Operation:string, Success:bool), one row per request.
// Application configuration supplies a permitted cluster/database, never request input.
var connection = new KustoConnectionStringBuilder(clusterUri)
    .WithAadSystemManagedIdentity();
using var queryClient = KustoClientFactory.CreateCslQueryProvider(connection);
const string query = """
declare query_parameters(op:string);
Events
| where Time >= ago(1h)
| where Operation == 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;{trace}";
properties.SetParameter("op", validatedOperationName);
using var reader = await queryClient.ExecuteQueryAsync(database, query, properties);
while (reader.Read())
{
    var total = reader.GetInt64(reader.GetOrdinal("Total"));
    var errors = reader.GetInt64(reader.GetOrdinal("Errors"));
    // Return an allow-listed DTO; handle null/empty/error completion as designed.
}

clusterUri, database and validatedOperationName are explicit application inputs, not missing credentials. Here Events is an ADX table with the declared unsampled schema, not a built-in Application Insights table. This Kusto client/cluster connection is not an Azure Monitor workspace connection; use that product's supported query API and schema for workspace telemetry. The earlier ActivityTraceId ?? string expression mixed incompatible types; converting TraceId to string before fallback fixes that error. WithAadSystemManagedIdentity authenticates as the host identity; it does not grant ADX database viewer permissions or solve private DNS/network routing. Use the lowest read permission required and verify the identity of each slot. A ClientRequestId aids correlation but must not contain tokens, raw KQL secrets or personal query text. Add package-supported cancellation/server-timeout handling and partial-failure processing in the actual service wrapper, then integration-test it before shipping.

Materialized views, saved functions and update policies

A persisted function saves parameterized query logic; it is evaluated when invoked. A materialized view incrementally maintains a supported aggregation over its source, allowing repeated reads to avoid repeating all historical work. A direct view query can combine materialized data with the unmaterialized delta; freshness, delta size, grouping cardinality and ingestion health still affect cost. Latency is not constant, and a five-minute dashboard refresh frequency alone is not sufficient justification.

For an ADX table Events(Time:datetime, Operation:string, Success:bool) containing one row per unsampled event, the following is a management-command design, not a command executed by this review:

.create materialized-view EventCountsHourly on table Events
{
    Events
    | summarize Total=count(), Errors=countif(Success == false)
        by Operation, Hour=bin(Time, 1h)
}

Querying the view for a period uses sum(Total), sum(Errors) and division of those sums; never average hourly error percentages equally when traffic differs. The current documented supported-aggregation list includes percentile/percentiles as well as count/sum/arg_max and sketches. That does not make averaging hourly p95s correct for a day. Choose the required raw data or mergeable representation and its supported query/maintenance contract. Feature availability and materialized-view definition restrictions belong to the actual ADX/Fabric surface; do not offer this management command as an Azure Monitor operation.

A persisted function for the same definition can centralize a consistent denominator:

.create-or-alter function with (folder="dashboards")
    EventErrorRate(fromTime:datetime, toTime:datetime, op:string)
{
    Events
    | where Time >= fromTime and Time < toTime
    | where Operation == op
    | summarize Total=count(), Errors=countif(Success == false)
    | extend ErrorRate=iff(Total == 0, real(null), 1.0 * Errors / Total)
}

Function creation requires separate management permission; a dashboard reader need not own it. Store definitions in source control, review parameter and schema changes, and version them for dependent clients. Backfill is an explicitly planned operation with cost/time/recovery concerns, not a free consequence of adding a definition.

An update policy transforms newly ingested rows into another table, useful for denormalization and typed extraction. Its transaction/failure settings and late-data/replay behavior affect consistency; it is not a row-authorization filter. Use the service's row-level security/access-control features where required. A transformation that drops a tenant column can make later enforcement impossible; a materialized view or aggregation table also needs an explicit privacy and access model.

Production triage and worked review answers

An Application Insights triage query should retain its measurement contract:

let fromTime = ago(1h);
requests
| where timestamp >= fromTime
| 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,1m), cloud_RoleName, name
| extend ErrorRate=iff(Represented == 0, real(null), 1.0 * Failures / Represented)
| order by Minute desc

Counts reflect classic itemCount weighting. The percentile columns are labelled observed because unequal sampling can bias a naive percentile. The current minute is partial; neither a partial minute nor missing ingestion should be interpreted as a sudden performance improvement. Grouping by both role and name preserves the operation slice. High-cardinality names with embedded IDs should be normalized at instrumentation rather than spawning an unbounded chart series.

The original chapter's twelve self-check topics now have explicit answers:

1. A pipeline prefix produces an intermediate table: inspect before and after summarize to explain row reduction.
2. There is no silent 500k-left-join-input rule. Default result limits are 500k records/64 MB with partial-failure reporting; clients must read completion errors.
3. leftsemi answers membership without right payload or right-match multiplication; inner retains all matching combinations when explicitly selected.
4. arg_max picks the latest row per group; time filtering, ties and payload size determine what “latest” means and what it costs.
5. dcount accuracy 4 is still approximate; the documented error/memory tradeoff differs from the default accuracy 1.
6. take is unordered. top or order by plus a limit expresses a ranking; resolve ties when stable output matters.
7. materialize can avoid repeated work or exhaust its shared node cache. Narrow it and benchmark equivalent queries, rather than wrapping every let.
8. let names and reuses expressions, allowing reviewers to see window/threshold assumptions; it does not freeze a snapshot or replace parameter binding.
9. Injection arises when input becomes syntax. Bind typed values, allow-list query shapes, authorize resources and constrain workload; quote replacement alone is not the defense.
10. A materialized view maintains aggregation state; a saved function reuses query logic. Both need schema/version/permission review, and neither guarantees constant latency.
11. union stacks events and is suitable for a trace timeline; join combines matches and can multiply rows. They are not interchangeable merely for performance.
12. Early projection reduces wide intermediate payloads, but dropping fields needed for later computation or authorization changes correctness.

The six original practical exercises have separate design solutions. They do not certify access to, or execution against, an Azure workspace.

Exercise 1 solution: On an authorized read-only surface, inventory requests, dependencies, exceptions and traces with getschema, or the workspace equivalents AppRequests, AppDependencies, AppExceptions and AppTraces. Record event time, operation ID, span/parent IDs and duration units for each. The union example needs explicit projections because message, name and outerMessage are different payloads. Schema discovery is a prerequisite, not evidence that every product exposes all four classic names.

Exercise 2 solution: The triage query above filters one hour, bins one minute and groups by both cloud_RoleName and name. For a hand-checkable minute, suppose stored items have (itemCount, success) equal to (10, true) and (2, false): represented total is 12, failures 2 and error ratio 1/6, not one failure divided by two stored rows. Retain observed percentile labels, exclude or label the incomplete minute and do not replace missing latency with zero.

Exercise 3 solution: Use the duplicate-key datatables in the join section: explicit inner produces four pairs; default innerunique produces two and may choose either left x event. An exception join can multiply a request, so compare distinct request IDs and intended matching pairs, not just joined counts. Apply the same intended time/tenant window to both inputs; removing time filters changes the population and does not guarantee a truncation warning or parity. Do not run the original unfiltered production stress drill. A negative fixture with two right matches exposes multiplicity without a cloud query.

Exercise 4 solution: For DeviceEvents, latest-before-filter leaves device b on firmware 1.2.3; filter-before-latest incorrectly also retains a. Count by region/firmware only after choosing current device rows. The separate Counts fixture pivots east to old=2/new=3 and west to new=4, with total 9 before and after widening. The result grain is one region, not one device. An absent category needs the declared output-schema/missing-cell contract checked on the chosen service.

Exercise 5 solution: A bucketed ADX triage function retains the original lookback/bucket exercise rather than substituting an unrelated unbucketed function. This definition assumes Events(Time:datetime, Operation:string, Role:string, Success:bool, DurationMs:real) with one row per request. The extra Role and DurationMs columns extend the smaller SDK/view example's schema. The management command is a design for an authorized database owner, not an action performed here:

.create-or-alter function with (folder="dashboards")
    EventTriage(lookback:timespan, bucket:timespan)
{
    Events
    | where Time >= ago(lookback) and Time < now()
    | summarize Total=count(), Errors=countif(Success == false),
        ObservedP95Ms=percentile(DurationMs, 95)
        by Minute=bin(Time, bucket), Role, Operation
    | extend ErrorRate=iff(Total == 0, real(null), 1.0 * Errors / Total)
}

A separate read query is EventTriage(1h, 1m) | order by Minute desc. The caller must allow-list positive bounded values (for example lookback at most 24h and bucket at least 1m), authorize the data scope and set time/workload limits. A function parameter is not an access-control boundary. Preserve incomplete-bucket and sampling semantics when adapting to actual telemetry; creating a function and querying it require different permissions.

Exercise 6 solution: In a local review, treat x' or 1==1 // as a single operation-name value. A deliberately concatenated query changes query text; SetParameter("op", value) leaves the fixed query unchanged. The latter still needs authorized tenant/resource selection and bounded workload. Do not deploy a deliberately injectable endpoint. Only a supported Kusto client/service test can prove server-side binding; the local negative control tests the text-versus-value distinction, not the KQL parser.

The capstone data path is now explicit: validated URL filters → API authorization → bounded parameterized KQL → a typed result with units/denominator/missing semantics → chart. Materialization is an optimization behind that contract, not a reason to weaken tenant boundaries or relabel a fixed bin as a rolling window.

Sources and verification boundary

Primary sources checked on 7 October 2026:

• Microsoft Kusto: bin() — bin rounds down to multiples of the bin width; it does not construct an overlapping rolling window.
• Microsoft Kusto: make-series — make-series aggregates rows grouped by the axis bins and group keys, then constructs arrays; a one-minute step does not imply a five-minute lookback.
• Microsoft Kusto percentile documentation — percentile computes an estimate of the nearest-rank percentile; accuracy depends on local population density.
• Google SRE: Implementing SLOs — An SLI can be good events divided by eligible events; an error budget is the complement of the objective, over a defined window.

The numerical fixtures and Python programs are teaching examples, not measurements of a deployed service. Python blocks were executed locally; no cloud deployment or model inference was performed.

Previous: A metering ledger that survives a replay. Track map.

Review refinements — 9 October 2026

The SDK example now targets an explicitly declared ADX Events table rather than implying that an ADX client automatically connects to Application Insights. The six original exercises have individual design answers, including a bucketed saved function. The join extension adds the first y row, not a nonexistent second one. The protected exact-percentile Python fixture is unchanged; local checks are not Kusto execution.

• Kusto connection strings and managed identity — the documented system-identity builder targets a Kusto service.
• Create or alter a stored function — the function is database metadata with separate creation/alteration permissions.

Versioned source contracts

Official passages were checked on 7 October 2026; immutable tags are used where available, while service docs remain dated snapshots. The saved evidence records include exact passages, URL, source date/revision and snapshot hash. These references support the stated contracts, not an unperformed service deployment.

• bin — bin rounds down; it does not create overlapping lookbacks.
• join — Default innerunique deduplicates the left; explicit inner preserves duplicates.
• percentile — Kusto percentile is an estimate, unlike the exact toy model.
• kql-limits — Record/byte result limits report partial failure, not a silent join-left-input cap.
• kql-dcount — dcount accuracy is probabilistic, not exact at level four.
• kql-materialize — Materialize uses a bounded shared node cache and can abort.
• kql-pivot — Dynamic pivot schemas can break automation; specify OutputSchema or long format.
• kql-params — Typed declared parameters parse bound values, not arbitrary query syntax.
• kql-mv-create — Current service supports a specific aggregation set, including percentiles; still not composable by averaging.
• kql-broadcast — Broadcast join broadcasts small left; lookup has the opposite size assumption.
• kql-lookup — Lookup broadcasts the right dimension.
• kql-best — KQL has optimization behavior; logical operator order must still preserve semantics.
• kql-update-policy — Update policies transform ingestion with failure semantics, not row authorization.
• kql-mv — Direct view queries combine materialized state and new data; no constant-latency guarantee.