Azure Data Explorer Query Builder

What This Tool Does

Build Azure Data Explorer (Kusto) KQL queries for time-series analysis (percentile-based latency summaries, series_decompose_anomalies-based anomaly detection, funnel analysis via sequential left-outer joins) plus tableSchema and materializedViews definitions. series_decompose_anomalies takes a sensitivity threshold (1.5 in the example) as its second argument, a lower threshold flags more points as anomalous (more sensitive, more false positives), a higher threshold flags fewer (less sensitive, more missed real anomalies), tuning this value against your actual metric's normal variance is what determines whether the anomaly detection is actually useful or just noise, there's no universal correct value, it depends entirely on how volatile the underlying series naturally is.

Technical Details

Common Use Cases

1. 1Using percentile() with multiple percentile values (50, 90, 95, 99) in one summarize, computing the full latency distribution in a single query pass rather than needing four separate queries or an approximation from average and standard deviation alone.
2. 2Tuning series_decompose_anomalies' sensitivity threshold against the actual observed noise level of a specific metric, a naturally volatile metric needs a higher threshold to avoid constant false-positive anomaly flags, while a naturally stable metric can use a lower threshold to catch genuinely smaller deviations.
3. 3Building a materialized view (HourlyErrorSummary) pre-aggregating a common, expensive query pattern, so dashboards or frequent queries against that aggregation read from the cheap, pre-computed materialized view instead of re-scanning and re-aggregating raw HttpRequests data every time.
4. 4Using a sequence of left-outer joins by SessionId across pageViews/addToCart/purchases events to build a funnel analysis, correctly counting sessions that dropped off at each stage via dcountif on whether the corresponding join produced a non-null timestamp.

Common Questions

Is there a universally correct sensitivity value for series_decompose_anomalies?

No, the right threshold depends entirely on the specific metric's natural variance, a genuinely volatile metric (like request count during a marketing campaign) needs a higher threshold to avoid flagging normal fluctuation as anomalous, while a naturally stable metric (a steady-state error rate) can use a lower threshold to catch smaller, still-meaningful deviations. Tune this empirically against your specific metric's historical behavior rather than copying a value from an unrelated use case.

Does querying a materialized view always return fully up-to-date data?

Materialized views in ADX are incrementally updated based on new data arriving in the source table, but there's a processing lag between new source data arriving and the materialized view reflecting it, for most operational dashboards this lag (typically seconds to low minutes) is negligible, but don't assume a materialized view query is guaranteed to reflect the absolute latest row written to the source table at the exact moment you query it.

Why does the funnel analysis query use left-outer joins rather than inner joins?

A left-outer join preserves every row from the left-hand table (pageViews) even when there's no matching row in the right-hand table (addToCart or purchases), which is exactly what's needed to correctly count sessions that viewed a product but never added it to cart, an inner join would silently drop those non-converting sessions entirely, making it impossible to calculate a genuine funnel drop-off rate at each stage.

Expert Tips

Related Learning Guides

• Cost Management Guide24 min read
• Azure Monitor & Application Insights26 min read