Subscribe
Share
Share
Embed
Most teams learn KQL through the explain keyword, which translates their SQL into syntactically correct KQL — and stops at the subset SQL can express. We trace the structural gap between bin-plus-summarize and make-series, and why the anomaly detection most teams never reach is one operator away.
Episode 17 • 2026-04-24 Duration: 9:39
Matthias and Fabia explore the KQL Queryset in Microsoft Fabric — why the pipe-forward mental model beats SQL for time-series data, when to use make-series vs bin+summarize, and the architectural decision between KQL Queryset, Notebooks, and the SQL endpoint.
The Fabric Friday video on this topic: https://youtu.be/8amqWDeHT2Y
Built on ElevenLabs voice synthesis. Matthias — cloned voice. Fabia — designed AI co-host. See Matthias live on YouTube (Fabric Friday), at his meetups, and at conferences like FabCon.
Hosted by Matthias Falland — Microsoft Data Platform MVP and community architect behind the Fabric Periodic Table. New episodes every Friday.
Have an architecture decision you are wrestling with? DM Matthias on LinkedIn — find him as Matthias Falland. Three to five sentences about the decision, your team size, and your current stack. We anonymize before airing.
Built on ElevenLabs voice synthesis. Brand design based on fabricperiodictable.com.
Architecture decisions for Microsoft Fabric. Anonymized real customer scenarios, cost realism, counter-arguments included. Weekly episodes aligned with Fabric Friday recordings.
The KQL Function Call Nobody Writes
Episode 17 | 2026-04-24
Fabric Architecture Podcast | Hosted by Matthias Falland with AI co-host Fabia
---
[00:08] Fabia: There's a function built into Fabric's query engine that decomposes a time series into seasonal pattern, trend, and residual — then flags the outliers. One function call.
[00:19] Matthias: One function call that works in production?
[00:22] Fabia: series_decompose_anomalies. It's been in the Kusto engine since the Azure Data Explorer days. But it requires a data structure that SQL can't produce.
[00:32] Matthias: So the barrier isn't syntax.
[00:34] Fabia: That's what we're settling today. KQL looks like SQL with pipes, and most teams treat it that way. We're going to trace what happens when you stop at the familiar part — and what you're leaving behind.
[00:47] Fabia: SQL is set-based — you describe the shape of the result, and the engine figures out how to get there. KQL is pipe-forward. You start with a table, and each pipe operator transforms what it receives. Top to bottom, in the order things actually happen.
[01:04] Matthias: The Unix pipeline model.
[01:05] Fabia: Same idea, vertical. And it matters because in SQL, the WHERE clause runs before SELECT, but you write it after. In KQL, where you write it is where it runs. You can point at a line number and say 'the problem is this pipe step.' And the pipeline extends past the result set — KQL has a render operator built in. Write render timechart at the end of a query and the queryset draws the chart. No external tool.
[01:33] Matthias: Every junior who writes WHERE before SELECT is accidentally thinking in KQL.
[01:38] Fabia: In a way. But readability and built-in rendering are surface benefits. The structural difference goes deeper.
[01:45] Matthias: Deeper how?
[01:46] Fabia: Every pipe step takes a table and returns a table. That constraint means you can map where to WHERE, project to SELECT, summarize to GROUP BY — the translation is clean. And the queryset has an explain keyword that automates it. Write your SQL, prefix it with two dashes and the word explain, and the engine hands you the KQL.
[02:07] Matthias: I've pointed people at explain more times than I can count. It's the fastest onramp I know.
[02:14] Fabia: And the translation runs. That's the trap. It gives you syntactically valid KQL, but it's KQL shaped like SQL. Every query explain produces is a flat pipeline — where, project, summarize, sort, take. It'll never produce a make-series. It'll never reach the time-series functions. SQL has no equivalent to translate from.
[02:35] Matthias: So explain is teaching people the subset of KQL that looks like SQL — and stopping there.
[02:41] Fabia: It's a dictionary. Dictionaries translate what you already know how to say.
[02:46] Matthias: And I've been handing people the dictionary and calling it a course.
[02:50] Fabia: We got a question that lands right here. 'I see two ways to do time-bucketed aggregations: bin(Timestamp, 1h) inside summarize, and make-series. They seem to produce similar results. When should I use each?'
[03:05] Matthias: Three weeks into KQL. That's when you hit that fork.
[03:09] Fabia: bin plus summarize produces a flat table — one row per group per time bucket. If there's no data in a given hour, that row doesn't exist. You get a table with gaps. make-series produces one row per group, with arrays. The time axis is an array. The values are an array. And it fills in every gap with a default value.
[03:30] Matthias: Why does the gap-filling matter?
[03:32] Fabia: Because series_decompose_anomalies needs a regular signal. It's doing seasonal decomposition — separating the repeating pattern from the trend from the noise. If the input has gaps, the seasonality detection breaks. You can't call the anomaly function on the output of bin plus summarize. The arrays from make-series are the required input format.
[03:55] Matthias: So every team that stays on bin plus summarize is one operator away from anomaly detection they didn't know they had. They're writing GROUP BY in pipe syntax and calling it fluent KQL.
[04:07] Matthias: Hold on — what about teams already running Power BI against their KQL database? Where does the queryset fit in that stack?
[04:15] Fabia: Different refresh cadences. A Real-Time Dashboard tile updates the moment new data arrives — for live sensor feeds that's often within seconds. It's the screen on the wall, the NOC view, the live sensor feed. Power BI reports are for the weekly trend deck. Both connect to the same KQL database, different audiences, different speeds.
[04:38] Matthias: And the queryset itself is the workshop. You write the query there, prove it works, then either pin it to a Real-Time Dashboard or pull it into a notebook with Kqlmagic if you need Python around it.
[04:51] Fabia: The queryset is a workspace item — it saves, it shares, each tab can point at a different KQL database. It's where you explore and iterate. The notebook is where you build a pipeline around what you found.
[05:05] Matthias: Cost-wise, none of this works below F2. That's the minimum Fabric capacity for Real-Time Intelligence — querysets, KQL databases, Eventhouses, all of it. And Copilot for KQL needs the same floor plus tenant admin opt-in and a supported region.
[05:21] Fabia: For a team already on Fabric for their lakehouse, it's shared capacity — the KQL workload draws from the same pool. For a team spinning up Fabric specifically for Real-Time Intelligence, F2 is the entry SKU. One capacity reservation covering querysets, databases, dashboards — everything we've been discussing.
[05:42] Matthias: The call I keep coming back to is simpler than people want it to be. If a team's deep in Power BI and they connect to the KQL database through the SQL endpoint, they get T-SQL reads, familiar tooling, no new language. For quarterly trend analysis, that's a legitimate architecture. I'd sign off on it.
[06:02] Fabia: It is. The SQL endpoint gives you read-only T-SQL against the same data. SELECT only — no DDL, no writes — but for analytical reporting, it works.
[06:12] Matthias: So the counter-argument to this entire episode is: you might not need KQL at all.
[06:17] Fabia: KQL earns its learning curve when you need sub-second operational dashboards or the time-series analytics — anomaly detection, forecasting, the series functions. If your whole use case is 'what happened last quarter,' a SQL endpoint into Power BI does the job and your team ships this week.
[06:37] Matthias: The risk I'd genuinely lose sleep over is the team that starts on the SQL endpoint, then six months later someone asks for anomaly detection, and now they're running two query languages against the same database. The cognitive switching cost is where I've seen real damage — same table, same columns, different casing rules, different comparison operators. That compounds.
[07:01] Fabia: And the operators make it vicious. KQL wants double equals for comparison, has for token matching, contains for substrings. T-SQL wants a single equals and LIKE with percent signs. Both languages are case-sensitive by default here, so that part at least agrees — it's the syntax you have to reload in your head every time you switch tabs.
[07:24] Fabia: Show me the query pattern. If the work ends in render timechart and a live dashboard, commit to KQL. If it ends in a semantic model and a Power BI report, stay on T-SQL. The architecture that hurts is the one where nobody chose.
[07:39] Matthias: That's the honest recommendation. If you're going into Real-Time Intelligence, commit. Use explain to bootstrap, but set a deadline to learn make-series. That's where KQL stops being SQL in a different costume and starts earning its keep.
[07:55] Fabia: And earning it means something specific. Staying on T-SQL means choosing not to have native anomaly detection, series forecasting, or in-query rendering. Those capabilities are structural to the pipe-forward model. They can't be added on top.
[08:11] Matthias: Somewhere right now, a sensor's drifting. Been doing it for three days. The data's sitting in an Eventhouse. The bin-plus-summarize dashboard shows the average ticking up, slowly. One make-series call would've flagged it Tuesday.
[08:27] Fabia: That's the function call nobody wrote.
[08:29] Matthias: If you're wrestling with that call — the language call, I mean — or you've already made it and you're not sure, DM me on LinkedIn. Find me as Matthias Falland.
---
End of episode. ~08:40 estimated.