Data Exploration in the Fabric Eventhouse with KQL

Kevin Feasel (@feaselkl)
https://csmore.info/on/rti

Who Am I? What Am I Doing Here?

Catallaxy Services
Curated SQL
Finding Ghosts in Your Data

@feaselkl

Motivation

Critical business operations may not be able to wait for the next nightly batch. The Kusto Query Language (KQL) enables these scenarios, allowing us to query and act on enormous volumes of timestamped, append-only events in near-real time as part of Fabric Real-Time Intelligence.

Today, we will explore a supply chain, from raw stream to dashboard to alert.

Agenda

  1. Real-Time Intelligence
  2. KQL Fundamentals
  3. Streaming Ingestion
  4. Time Series Patterns
  5. Real-Time Dashboards
  6. Alerting with Activator
  7. Wrapping Up

Fabric Real-Time Intelligence

Fabric Real-Time Intelligence is a solution for building (near) real-time data pipelines, dashboards, and alerts using Kusto Query Language (KQL) and other integrated components.

The Components

  • Eventhouse: a KQL-native database optimized for time series, streaming, and log analytics
  • Real-Time Hub: a tenant-wide catalog of streaming data sources
  • Eventstream: low-code ingestion from source, through transformation, to destination
  • Real-Time Dashboards: live monitoring built directly on KQL queries
  • Activator: event-driven conditions that trigger notifications and workflows

The Arc of the Talk

Every real-time system does the same four things in the same order:

We will walk that arc end to end, covering the stages as well as the interactions between them.

What Is an Eventhouse?

A database optimized for data that arrives constantly and (usually) does not receive updates.

  • Append-only, with time as the natural partition key
  • Columnar storage with aggressive indexing on text and timestamps
  • Ingestion period of seconds, not pipeline runs
  • Uses KQL for queries, rather than T-SQL

Eventhouse or Lakehouse?

Eventhouse

  • Events arriving now
  • Time series and logs
  • Sub-minute freshness
  • Interactive exploration

Lakehouse

  • Curated, modelled history
  • Joins across many domains
  • Freshness measured in hours
  • Scheduled transformation

Most architectures use both: the Eventhouse is where data lands hot, the lakehouse is where it settles.

Agenda

  1. Real-Time Intelligence
  2. KQL Fundamentals
  3. Streaming Ingestion
  4. Time Series Patterns
  5. Real-Time Dashboards
  6. Alerting with Activator
  7. Wrapping Up

Kusto Query Language

The Kusto Query Language (KQL) is designed for querying large datasets efficiently. The syntax is similar to Splunk's query language versus a SQL variant. Even so, the concepts of KQL are quite similar to T-SQL in spite of the syntax differences.

One key difference is that KQL is case-sensitive, whereas T-SQL will depend on the collation.

Pipes

A query is a table, followed by a chain of operators that each take a table and return a table.

Read it top to bottom. There is no need to jump to the end of the query to find out what it selects.

It's Not Just for Logs

KQL was born analyzing telemetry, and that heritage shows in its time series and text-search operators.

But anything with a timestamp and a lot of rows is a candidate, including IoT readings, financial ticks, clickstream, application traces, and supply chain events.

Our Demo: A Supply Chain

Synthetic but statistically realistic data, courtesy of Spindle. It includes 10 related tables with enforced foreign key integrity.

  • warehouse, supplier, material: the reference data
  • purchase_order, purchase_order_line: what was ordered
  • shipment: how it travels, and with which carrier
  • shipment_event: the live stream

The Join Path

Demo Time

code/02 - KQL Basics.kql

Agenda

  1. Real-Time Intelligence
  2. KQL Fundamentals
  3. Streaming Ingestion
  4. Time Series Patterns
  5. Real-Time Dashboards
  6. Alerting with Activator
  7. Wrapping Up

Real-Time Hub

The Real-Time hub is a tenant-wide catalog of every streaming source available to you, including Event Hubs, IoT Hub, Kafka topics, database change feeds, Fabric events, and more.

The goal is to discover and subscribe rather than build bespoke ingestion for each source.

Eventstream

Low-code ingestion: connect a source, optionally transform in flight, land it in a destination.

Sources

  • Event Hubs / IoT Hub
  • Kafka
  • Database CDC feeds
  • Custom endpoints

Destinations

  • Eventhouse
  • Lakehouse
  • Derived streams
  • Custom endpoints

Data Formats

  • Text: CSV, TSV, SCSV, unstructured TXT
  • JSON
  • Avro
  • Parquet
  • ORC

How This Demo Feeds the Eventhouse

Spindle generates statistically realistic supply chain events. A small Python bridge relays them to an Eventstream custom endpoint, which is Event Hub compatible.

Starting the Stream

And to load the seed history the demos query against:

Demo Time

code/03 - Streaming and Real-Time Patterns.kql

Agenda

  1. Real-Time Intelligence
  2. KQL Fundamentals
  3. Streaming Ingestion
  4. Time Series Patterns
  5. Real-Time Dashboards
  6. Alerting with Activator
  7. Wrapping Up

Tumbling Windows: bin()

Animation of a timeline of events with six fixed one-minute windows appearing one at a time. The windows tile end to end without overlapping, and each event falls inside exactly one of them.

Hopping Windows

Animation of a timeline of events with six five-minute windows appearing one at a time, each starting one minute after the last. The windows overlap, and a marked event falls inside five of them.

Event Time vs. Ingestion Time

event_timestamp is when the thing happened. ingestion_time() is when the Eventhouse received it.

The gap between them is your end-to-end latency. Measure it and you have a Service Level Objective (SLO) you can alert on.

make-series

Where summarize leaves holes, make-series fills them.

That matters more than it sounds: every series function below needs evenly spaced points. A missing hour is not a gap in the chart; it silently shifts every subsequent value.

The Series Family

  • series_stats_dynamic: min, max, mean, variance
  • series_outliers: per-point outlier scores
  • series_decompose: seasonal, trend, residual
  • series_decompose_anomalies: flags the unexplained
  • series_periods_detect: finds hidden rhythms
  • series_decompose_forecast: extends forward
  • series_fir: moving averages

Materialized Views

Pre-aggregate on ingestion so dashboard tiles stay cheap as volume grows.

Query it exactly like a table.

Update Policies

Transform on ingest rather than on read. For example, we can route only the exceptions into their own narrow table, so the alerting query never scans the firehose.

Demo Time

code/04 - Time Series.kql

Anomalies on demand:

Agenda

  1. Real-Time Intelligence
  2. KQL Fundamentals
  3. Streaming Ingestion
  4. Time Series Patterns
  5. Real-Time Dashboards
  6. Alerting with Activator
  7. Wrapping Up

"Real-Time" Dashboards

Microsoft Fabric offers "Real-Time Dashboard," a set of tiles built on KQL queries. With no semantic model in between, the data is up to date without needing to think about refresh schedules.

Because this is not a Power BI report, there are limits to the number of available visuals.

Refresh Rates

Pick an interval that respects the ingestion batching window.

Live refresh enables immediate updates to the dashboard as new data arrives, and caps the maximum refresh rate to avoid overwhelming the backend.

For visuals that do not support live refresh, you can still configure a scheduled refresh interval of, e.g., every 5 minutes.

Parameters and Drill-Down

  • Time pickers bind to _startTime and _endTime
  • Dropdowns bind to a KQL query—shipment | distinct carrier_name
  • Any tile column can drill through to a detail page

Dashboards or Power BI?

Real-Time Dashboards

  • Operational monitoring
  • Second-scale refresh
  • KQL authors
  • Lives beside the data

Power BI

  • Modelled, shareable reporting
  • Scheduled refresh
  • Business authors
  • Joins beyond the Eventhouse

Demo Time

code/05 - Dashboards and Alerts.kql

Agenda

  1. Real-Time Intelligence
  2. KQL Fundamentals
  3. Streaming Ingestion
  4. Time Series Patterns
  5. Real-Time Dashboards
  6. Alerting with Activator
  7. Wrapping Up

Activator

Fabric Activator provides no-code event detection capabilities against streaming or Eventhouse data.

Because it triggers on events, actions are taken immediately when conditions are met rather than running on a timer.

This also allows you to respond to issues in near-real time, rather than discovering them after the fact.

Anatomy of a Rule

  • Object: what you are watching (a carrier, a warehouse, a shipment)
  • Property: the value you track over time
  • Condition: the threshold, and how long it must hold
  • Action: notification, Power Automate flow, Fabric item trigger

Three Conditions Worth Having

  1. Exception rate exceeds 5% in a 5-minute window
  2. Throughput collapses (nothing has arrived recently)
  3. A single location's exception count spikes

The first is the obvious one. The second is the one people forget.

Why "Nothing Arrived" Needs Its Own Rule

A rate-based alert cannot fire when there is no data.

No events means no rows, which means no rate is computed, which means the threshold never trips. A dead pipeline looks exactly like a healthy one.

Alert on silence, not just on noise.

Actions

  • Teams and email notifications
  • Power Automate flows
  • Fabric item triggers: start a pipeline, refresh a model, execute a User Data Function, etc.

Demo Time

Fire the alert to see a tenfold burst for 60 seconds, starting 30 seconds in:

Power BI Integration

An Eventhouse is a first-class Power BI source. The KQL database exposes a connector, and every query you have written is reusable as a source.

  • DirectQuery: always current, at the cost of a round trip per interaction
  • Import: fast and cheap to interact with, as stale as your last refresh
  • Direct Lake: query the data in the lake directly, without importing
  • Composite: import the history, DirectQuery the hot tail

Which Tool, When

Use a Real-Time Dashboard when someone is watching the screen because something might go wrong.

Use Power BI when someone needs to understand what happened, alongside data that does not live in the Eventhouse.

Use Activator when nobody should have to watch at all.

Agenda

  1. Real-Time Intelligence
  2. KQL Fundamentals
  3. Streaming Ingestion
  4. Time Series Patterns
  5. Real-Time Dashboards
  6. Alerting with Activator
  7. Wrapping Up

Performance Tips

  • Filter first, and filter on time first: time is the partition key, so the engine can skip whole extents
  • Prefer case-sensitive operators: == over =~, has over contains
  • Search named columns, not every column
  • materialize() anything you reference more than once
  • take 10 while exploring, and drop it once the shape is right

Query Limits

  • 500,000 records or 64 MB returned to the client, by default
  • set notruncation; to disable
  • set truncationmaxsize / set truncationmaxrecords to adjust

These limits exist to stop you from accidentally retrieving enormous amounts of data and paying the price in time and money.

Real-Time Intelligence or Lakehouse?

Reach for Real-Time Intelligence when freshness is the requirement: data arrives continuously, questions are exploratory, and the answer stops being useful within minutes.

Reach for lakehouse analytics when correctness and breadth matter more: curated history, joins across domains, results that must reconcile.

In many cases, the right answer for most organizations is "both," with the Eventhouse in front.

Demo Time

code/06 - Query Recommendations.kql

Wrapping Up

KQL remains the best language available for timestamped, append-only data at volume. Fabric Real-Time Intelligence brings the whole story together in one place: ingest, query, visualize, alert.

Wrapping Up

To learn more, go here:
https://csmore.info/on/rti


And for help, contact me:
feasel@catallaxyservices.com | @feaselkl


Catallaxy Services consulting:
https://CSmore.info/on/contact