Learn how to build a complete real-time analytics pipeline in Microsoft Fabric using Eventstreams for ingestion, Eventhouses for ultra-fast time-series storage, and KQL Querysets for millisecond-latency queries. This expert-level lesson covers architecture internals, KQL query patterns, materialized views, and Power BI integration.
Imagine you're running operations for a logistics company. Thousands of delivery vehicles are streaming GPS coordinates, engine telemetry, and package scan events into your systems every second. You need to know — right now, not in an hour — which routes are falling behind, which vehicles are generating fault codes, and whether that urgent medical shipment is still on schedule. A batch pipeline that lands data in a lakehouse every 30 minutes is useless here. You need sub-second ingestion, millisecond query latency, and the ability to ask complex time-series questions across billions of events without breaking a sweat.
This is exactly the problem Microsoft Fabric's Real-Time Analytics workload was built to solve. Built on the proven foundation of Azure Data Explorer (ADX), Fabric brings a dedicated real-time path into its unified platform: Eventstreams capture and route streaming data from dozens of sources, Eventhouses store and index that data using columnar compression optimized for time-series, and KQL Querysets let you write Kusto Query Language queries against live data with millisecond response times. Together, they form a first-class streaming architecture that sits alongside — and integrates with — the rest of Fabric's lakehouse, warehouse, and reporting capabilities.
By the end of this lesson, you'll be able to design and build a complete real-time analytics pipeline in Microsoft Fabric, understand how the underlying technology makes high-frequency queries fast, and write KQL queries sophisticated enough to solve real operational problems. You won't just know which buttons to click — you'll understand why the architecture works the way it does and where its edge cases live.
What you'll learn:
This lesson assumes you're already comfortable with Microsoft Fabric workspaces and capacities. If you haven't set up a workspace yet, start with Fabric Capacities and Workspaces: F SKUs, Trials, and Setting Up Your First Workspace. You should understand Fabric's general architecture — particularly how OneLake provides the unified storage layer — and have at least a working knowledge of SQL. KQL syntax is taught here from scratch, but SQL intuition helps considerably.
For hands-on work, you'll need an F2 or higher Fabric capacity (or a Fabric trial). The Real-Time Analytics workload is not available on Power BI Premium P SKUs.
Before diving into individual components, it's worth understanding how Real-Time Analytics fits into Fabric's broader architecture — and what distinguishes it from the other data paths.
Fabric gives you multiple ways to land and query data. The lakehouse path (OneLake + Delta tables + Spark) is optimized for large-scale historical analysis, transformation pipelines, and machine learning. The warehouse path (T-SQL over Delta) adds familiar SQL semantics for structured reporting. Direct Lake mode in Power BI closes the loop for BI consumption without import. All of that is covered in depth in lessons like Building Your First Lakehouse in Microsoft Fabric and Building a Fabric Data Warehouse with T-SQL.
The Real-Time Analytics workload is a fourth path, purpose-built for scenarios where data volumes are high, event frequency is measured in thousands per second, and query latency must be sub-second even over billions of rows. It isn't a replacement for lakehouses or warehouses — it's a specialized tool for a specific class of problems.
What makes it architecturally distinct:
Columnar, compressed, time-indexed storage. Eventhouses use the same storage engine as Azure Data Explorer. Data is ingested into row groups sorted by ingestion time, then compressed using a columnar format optimized for high-cardinality string columns and numeric time series. This is fundamentally different from Delta's Parquet layout, which is optimized for batch reads.
Pull-based micro-batch ingestion. Despite the "real-time" label, Fabric Eventhouses actually ingest data in small, frequent micro-batches (typically 5-second intervals by default, configurable down to 1 second). This is a deliberate trade-off: true row-by-row streaming ingestion kills compression efficiency and index quality. The micro-batch approach lets the system achieve both low latency and high query performance.
KQL as the query language. Kusto Query Language is a pipe-based, read-only query language designed for telemetry and log analysis. It looks nothing like SQL at first, but once you internalize its compositional structure, you'll find it remarkably expressive for time-series and statistical queries.
Integration with OneLake. Eventhouses can expose their tables as Delta-compatible shortcuts in OneLake, meaning your real-time data is simultaneously queryable via KQL (for sub-second latency) and via Spark or SQL (for historical joins and ML). This is one of Fabric's most powerful integration patterns.
An Eventstream is a Fabric artifact that represents a managed, visual streaming pipeline. Underneath, it's built on Azure Event Hubs infrastructure — but the abstraction hides the operational burden of managing Kafka clusters, consumer groups, and checkpoint management. You define sources, optional transformations, and one or more destinations, and Fabric handles the rest.
Think of an Eventstream as a living DAG (directed acyclic graph) for streaming data. Data flows in from one or more sources, optionally passes through transformation operators, and fans out to multiple destinations simultaneously. You can write to an Eventhouse for real-time querying while simultaneously landing a copy in a lakehouse for long-term historical analysis.
Eventstreams support a growing catalog of source connectors:
The CDC sources are particularly interesting because they let you treat database changes as a stream. When a row is inserted, updated, or deleted in an Azure SQL Database, that change event flows through your Eventstream in real time — enabling patterns like real-time reporting on transactional systems without touching the OLTP database with analytics queries.
Note
The connector catalog is expanding rapidly. Check the official documentation before assuming a source type isn't available — several connectors have been added in minor Fabric updates without major announcements.
Let's build the logistics scenario from the introduction. Vehicles publish GPS and telemetry events to an Azure Event Hub. Each event looks like this:
{
"vehicleId": "VH-10482",
"routeId": "R-NYC-BOS-12",
"timestamp": "2024-11-15T14:23:07.441Z",
"latitude": 41.3083,
"longitude": -72.9279,
"speedKph": 87.4,
"engineTempC": 92.1,
"packageCount": 43,
"faultCode": null
}
To create an Eventstream, open your Fabric workspace, select New and choose Eventstream. Give it a meaningful name — es_vehicle_telemetry follows a useful convention of prefixing artifact type.
In the Eventstream canvas, click Add source and choose Azure Event Hub. You'll need to provide:
Listen permission (never use Manage permissions for read-only ingestion — follow least privilege)$Default, which is often shared)Once connected, you'll see a live preview of incoming events in the data preview pane at the bottom. This preview samples from the stream — it won't show every event, but it's invaluable for confirming your payload structure before wiring up destinations.
Between source and destination, you can add transformation operators. These run as managed stream processing logic — think Azure Stream Analytics, but with a simpler visual interface:
Filter — Drop events that don't match a condition. For example: only pass through events where faultCode is not null, routing fault events to a high-priority destination.
Manage fields — Project, rename, or add computed columns. You can add a derived speedCategory column using conditional logic, or rename timestamp to eventTime to match your destination schema.
Aggregate — Compute tumbling, hopping, or sliding window aggregates. For example: compute average speed per vehicle per 1-minute tumbling window. This produces a lower-volume, pre-aggregated stream perfect for dashboard feeding.
Union / Expand — Merge multiple source streams or unnest array fields.
Group by — Partition events for parallel processing.
Warning
Transformations in Eventstreams add latency. Each operator adds roughly 1-3 seconds of processing time. For scenarios where you need sub-second end-to-end latency, write raw events directly to the Eventhouse and do your transformations in KQL queries or materialized views instead.
The visual canvas is genuinely useful for documentation: you can see the entire streaming pipeline at a glance, which makes Eventstreams considerably more maintainable than imperative streaming code. But the transformation language has limits — complex stateful processing (like joins across streams with large time windows) is better handled downstream in KQL or in a dedicated tool like Azure Stream Analytics.
An Eventstream can write to multiple destinations simultaneously:
The ability to write to both an Eventhouse and a Lakehouse simultaneously is the key integration pattern. Your real-time queries run against the Eventhouse (sub-second latency, last 30-90 days of hot data). Your historical queries and ML pipelines run against the lakehouse (years of cold data via Spark or Dataflow Gen2). You get one ingestion pipeline serving two analytically distinct needs.
When configuring a KQL Database destination, you specify the target database, the target table name, and optionally an ingestion mapping — a JSON or CSV mapping that tells the system how to map JSON fields to table columns. If the table doesn't exist, you can configure the Eventstream to create it automatically with an inferred schema.
An Eventhouse is the Fabric artifact that hosts one or more KQL databases. It's the equivalent of a "server" in traditional database terms, but it auto-scales, is capacity-aware, and requires no infrastructure management on your part.
Within an Eventhouse, you create KQL databases. A KQL database is the container for tables, materialized views, functions, and external tables. You might have separate databases for different business domains (logistics, finance, HR) within a single Eventhouse, or separate Eventhouses for different teams with different capacity needs.
Key insight
Eventhouses are capacity-scoped. Your F SKU capacity determines how much compute is available for ingestion and query. An F2 can handle modest telemetry volumes (millions of events per hour). Production IoT workloads typically need F8 or higher. Unlike lakehouses and warehouses, which can burst into OneLake's storage layer, Eventhouse performance is closely tied to your capacity tier.
Understanding the storage internals helps you write better queries and avoid common performance pitfalls.
When events arrive at a KQL database via an Eventstream destination, they're first written to an in-memory buffer (the "hot cache"). After 5 seconds (default), or when the buffer reaches a size threshold, the data is sharded and persisted to Azure Blob Storage in a compressed, columnar format called extents (also called data shards).
An extent is an immutable unit of data. It contains a set of rows that arrived together, compressed column by column, plus an extent-level index. The extent stores min/max values for each column, enabling the query engine to skip extents entirely when they can't contain the data you're looking for — this is called extent-level pruning and is one of the primary reasons KQL queries over billions of rows can return in seconds.
Over time, small extents are merged into larger ones through a background process called extent merging. This improves query performance by reducing the number of extents the engine must open for a query, but it means that very recent data (seconds to minutes old) may temporarily show slightly degraded query performance compared to data that has been in the system for a few minutes.
The hot cache is a configurable in-memory (and SSD-backed) cache that holds a configurable time window of the most recent data. The default is 31 days. Data older than the hot cache window is still queryable, but reads go to blob storage (cold) and are slower. You configure this with:
.alter table VehicleTelemetry policy caching hot = 7d
Reducing the hot cache window dramatically reduces memory consumption but increases query latency for historical queries. In practice, set your hot cache to the window that your operational queries need (typically 7-30 days) and your data retention to however long you legally need to keep raw events (90-365 days is common).
When an Eventstream writes to a KQL database for the first time with auto-create enabled, the table schema is inferred from the JSON payload. This is convenient for development but dangerous in production — a schema change in the upstream payload can silently alter or break your table.
For production workloads, create tables explicitly:
.create table VehicleTelemetry (
VehicleId: string,
RouteId: string,
EventTime: datetime,
Latitude: real,
Longitude: real,
SpeedKph: real,
EngineTempC: real,
PackageCount: int,
FaultCode: string
)
Then create an ingestion mapping that maps your JSON field names to table columns:
.create table VehicleTelemetry ingestion json mapping 'vehicle_telemetry_mapping'
'['
' {"column": "VehicleId", "path": "$.vehicleId", "datatype": "string"},'
' {"column": "RouteId", "path": "$.routeId", "datatype": "string"},'
' {"column": "EventTime", "path": "$.timestamp", "datatype": "datetime"},'
' {"column": "Latitude", "path": "$.latitude", "datatype": "real"},'
' {"column": "Longitude", "path": "$.longitude", "datatype": "real"},'
' {"column": "SpeedKph", "path": "$.speedKph", "datatype": "real"},'
' {"column": "EngineTempC", "path": "$.engineTempC", "datatype": "real"},'
' {"column": "PackageCount", "path": "$.packageCount", "datatype": "int"},'
' {"column": "FaultCode", "path": "$.faultCode", "datatype": "string"}'
']'
Notice the field name mismatch: the JSON uses camelCase (vehicleId) while the table uses PascalCase (VehicleId). The mapping handles this translation explicitly. Without a mapping, the ingestion engine does a case-insensitive match on field names, which works until it doesn't — be explicit in production.
One of the most powerful Eventhouse features is materialized views. A materialized view is a pre-aggregated projection of a table that the engine keeps automatically updated as new data arrives. Unlike a regular query, you don't pay the aggregation cost at query time — it's already been computed.
For the logistics scenario, you might want to know the latest position and status of each vehicle without scanning the entire table for the most recent event per vehicle:
.create materialized-view with (backfill=true) LatestVehicleStatus
on table VehicleTelemetry
{
VehicleTelemetry
| summarize
arg_max(EventTime, *)
by VehicleId
}
The arg_max(EventTime, *) operator returns all columns (*) from the row with the maximum EventTime for each VehicleId. The materialized view maintains this continuously — as new events arrive, the view updates the row for each affected vehicle.
Tip
The backfill=true option tells the engine to populate the materialized view from existing data when it's created. Without it, the view only captures data arriving after creation. Always use backfill=true unless your table is genuinely empty.
A pre-aggregated view for per-minute speed averages by vehicle is equally useful for dashboards:
.create materialized-view SpeedAggregatesByMinute
on table VehicleTelemetry
{
VehicleTelemetry
| summarize
AvgSpeedKph = avg(SpeedKph),
MaxSpeedKph = max(SpeedKph),
EventCount = count()
by VehicleId, RouteId, bin(EventTime, 1m)
}
Querying this materialized view is orders of magnitude faster than running the aggregation over the raw table, especially when you have billions of events.
A KQL Queryset is a Fabric artifact that holds one or more KQL queries against a connected KQL database. It's analogous to a SQL script file in a warehouse context — a named, saveable, shareable collection of queries. Querysets are where you do your analytical work: exploratory investigation, building the queries that will back Power BI reports, or writing operational runbooks.
Querysets have tabs (like browser tabs, one per query), syntax highlighting and autocomplete, a results pane, and a query performance statistics view that shows how long each sub-operation took. The statistics view is invaluable for performance tuning.
KQL uses a pipe-based syntax where you start with a table name and chain operators using the | symbol. Each operator transforms the result set and passes it to the next:
VehicleTelemetry
| where EventTime > ago(1h)
| where FaultCode != ""
| project VehicleId, RouteId, EventTime, FaultCode, EngineTempC
| order by EventTime desc
| take 100
Read this as: "From VehicleTelemetry, keep only rows from the last hour, keep only rows with a non-empty FaultCode, select these five columns, sort newest first, take the top 100."
The most important KQL operators you'll use constantly:
where — Filter rows. Equivalent to SQL WHERE. The engine applies where clauses that reference the ingestion time first (because extent pruning is most effective on time ranges), then other filters.
project — Select and optionally rename columns. Equivalent to SELECT col1, col2 AS alias. Unlike SQL, project drops all columns not explicitly named.
extend — Add computed columns without dropping existing ones. Use this when you want to add a derived field while keeping everything else.
summarize — Group and aggregate. Equivalent to SELECT agg_func, GROUP BY.
join — Join two result sets. KQL joins have different semantics from SQL joins, which we'll cover shortly.
bin — Round a value down to a bucket boundary. bin(EventTime, 5m) rounds each timestamp down to the nearest 5-minute mark, enabling time-series grouping.
ago — A relative time function. ago(1h) returns the datetime exactly one hour ago. This is the idiomatic way to express "last N hours" in KQL.
Real-time analytics is fundamentally about time. KQL's time-series functions are where it leaves SQL far behind.
Tumbling window aggregation:
VehicleTelemetry
| where EventTime > ago(6h)
| summarize
AvgSpeedKph = avg(SpeedKph),
VehicleCount = dcount(VehicleId),
FaultEvents = countif(FaultCode != "")
by bin(EventTime, 15m)
| order by EventTime asc
| render timechart
The render timechart at the end tells the Queryset UI to display results as a time series chart rather than a table. This is for in-Queryset visualization — it doesn't affect query results when consumed by Power BI.
Detecting vehicles that have stopped reporting:
This is a classic operational query — find vehicles that sent telemetry more than 10 minutes ago and haven't been heard from since:
let RecentVehicles =
VehicleTelemetry
| where EventTime > ago(10m)
| distinct VehicleId;
VehicleTelemetry
| where EventTime > ago(2h)
| summarize LastSeen = max(EventTime) by VehicleId
| where LastSeen < ago(10m)
| join kind=leftanti RecentVehicles on VehicleId
| extend MinutesSinceLastSeen = datetime_diff('minute', now(), LastSeen)
| project VehicleId, LastSeen, MinutesSinceLastSeen
| order by MinutesSinceLastSeen desc
The let statement defines a reusable subquery — think of it as a named CTE. The leftanti join returns rows from the left side that have no match on the right side. So this returns vehicles that appeared in the last 2 hours but are not in the set of vehicles that reported in the last 10 minutes.
Anomaly detection — finding vehicles driving unusually fast:
VehicleTelemetry
| where EventTime > ago(24h)
| summarize
AvgSpeed = avg(SpeedKph),
StdDev = stdev(SpeedKph),
MaxSpeed = max(SpeedKph)
by VehicleId, RouteId
| extend ZScore = (MaxSpeed - AvgSpeed) / StdDev
| where ZScore > 3
| project VehicleId, RouteId, AvgSpeed, MaxSpeed, StdDev, ZScore
| order by ZScore desc
A Z-score above 3 means the maximum observed speed for that vehicle is more than 3 standard deviations above its own average — a statistical signal worth investigating. This is a simple example; KQL also has built-in ML functions like series_decompose_anomalies() for more sophisticated time-series anomaly detection.
Key insight
KQL's series_make_array() and related functions let you convert time-bucket aggregations into array-typed series, which you can then pass to built-in ML functions for seasonality decomposition, forecasting, and anomaly detection. These are genuinely useful for operational alerting — not just demonstrations.
KQL join semantics differ from SQL in ways that surprise SQL professionals:
The right side is broadcast. KQL assumes the right-side table (after join) is the smaller one and broadcasts it to all nodes. If you accidentally put a massive table on the right side, performance degrades severely. Unlike SQL, KQL doesn't automatically optimize join order.
Default join is innerunique, not inner. innerunique deduplicates the left side before joining — it returns one row per left-side key value, picking an arbitrary row when there are duplicates. Use inner explicitly if you want SQL-style inner join semantics.
Available join flavors: inner, innerunique, leftouter, rightouter, fullouter, leftanti, rightanti, leftsemi, rightsemi. The leftanti and leftsemi joins are particularly useful for "find items with/without a match" patterns.
A practical example — enriching vehicle telemetry with route metadata from a static reference table:
let RouteMetadata = datatable(RouteId: string, OriginCity: string, DestCity: string, PlannedDistanceKm: real)
[
"R-NYC-BOS-12", "New York", "Boston", 346.0,
"R-NYC-PHI-07", "New York", "Philadelphia", 151.0,
"R-BOS-HAR-03", "Boston", "Hartford", 172.0
];
VehicleTelemetry
| where EventTime > ago(1h)
| join kind=inner RouteMetadata on RouteId
| summarize
AvgSpeedKph = avg(SpeedKph),
EventCount = count()
by RouteId, OriginCity, DestCity
| order by AvgSpeedKph desc
The datatable operator creates an inline table literal — perfect for enrichment data in queries without needing a separate table. For production, you'd load reference data into a proper KQL table and update it via a Data Pipeline or Dataflow Gen2.
KQL supports stored functions — reusable parameterized query fragments that live in the database:
.create-or-alter function
with (docstring='Get vehicle events with fault codes in a time window')
FaultEvents(startTime: datetime, endTime: datetime, minEngineTemp: real)
{
VehicleTelemetry
| where EventTime between (startTime .. endTime)
| where FaultCode != ""
| where EngineTempC >= minEngineTemp
| project VehicleId, RouteId, EventTime, FaultCode, EngineTempC
}
Call it like this:
FaultEvents(ago(4h), now(), 100.0)
| summarize FaultCount = count() by VehicleId, FaultCode
| order by FaultCount desc
Functions are the building blocks of reusable KQL libraries. In complex deployments, teams maintain sets of functions that encode business logic — data quality filters, business entity definitions, SLA thresholds — so that analytical queries compose these functions rather than re-implementing logic everywhere.
A KQL Queryset can be used as a data source for Power BI reports in DirectQuery mode. When you publish a Power BI report connected to a KQL database in DirectQuery, each visual refresh executes a KQL query against the live Eventhouse data. This gives you dashboards that reflect data within seconds of it arriving — genuine real-time reporting without the complexity of streaming datasets.
To connect Power BI Desktop to a KQL database:
In Fabric's web UI, you can also connect a KQL Queryset directly to a Power BI report without leaving the browser — a faster path for initial development.
Warning
KQL DirectQuery in Power BI is not the same as Direct Lake mode. Direct Lake reads Delta tables directly from OneLake with in-memory caching — it's optimized for historical data that changes infrequently. KQL DirectQuery executes live Kusto queries on every visual refresh — it's optimized for data that changes every few seconds. Use the right tool for each layer of your architecture.
For real-time dashboards, pay attention to:
Auto-refresh intervals. Power BI supports page-level auto-refresh down to 1-second intervals for DirectQuery — but this hammers your Eventhouse with queries. For most operational dashboards, 15-30 second refresh intervals are sufficient and dramatically easier on capacity.
Query complexity. Every visual on a DirectQuery page fires its own KQL query on every refresh. A dashboard with 10 visuals refreshing every 15 seconds generates 40 queries per minute. Make sure your queries are fast (materialized views help enormously here) and consider whether some visuals can tolerate longer refresh intervals.
Fabric Real-Time Dashboards (formerly Power BI streaming datasets) are also available directly within Fabric — these are lightweight, KQL-native dashboards with their own rendering engine, faster than Power BI for very high-frequency updates. If you need sub-5-second refresh and your audience is operational users (not BI consumers), Real-Time Dashboards may be a better fit than Power BI.
One of the most architecturally significant features of Fabric's Real-Time Analytics is the ability to expose an Eventhouse table as a Delta-compatible shortcut in OneLake. This means:
You enable this in the Eventhouse settings: navigate to the KQL database, click the database name, and find the OneLake availability toggle. When enabled, Fabric creates Delta log files alongside the extent data, making the table appear as a Delta table in OneLake.
This enables patterns like:
Note
When OneLake availability is enabled for a KQL database, there's a slight overhead on ingestion because the system must also maintain Delta log files. For very high throughput scenarios (millions of events per second), benchmark with OneLake availability enabled before committing to it in production.
You can also create OneLake shortcuts that point from a lakehouse to an Eventhouse's OneLake-enabled tables. This means analysts who live in the lakehouse world can include real-time telemetry data in their Spark queries without any data movement — the shortcut provides logical access without physical copying.
This integration dissolves the traditional barrier between streaming and batch analytics. You're not choosing between real-time and historical — you're doing both, on the same data, with the right tool for each query pattern.
This exercise walks you through building a functional real-time analytics pipeline in Fabric. You'll need an F2+ workspace.
Step 1: Create an Eventhouse
In your Fabric workspace, click New and search for Eventhouse. Name it eh_logistics. Fabric automatically creates a KQL database with the same name inside it.
Step 2: Create the Vehicle Telemetry table
Open the KQL database and click Query to open a Queryset. Run this DDL:
.create table VehicleTelemetry (
VehicleId: string,
RouteId: string,
EventTime: datetime,
Latitude: real,
Longitude: real,
SpeedKph: real,
EngineTempC: real,
PackageCount: int,
FaultCode: string
)
Step 3: Create supporting structures
// Materialized view for latest vehicle status
.create materialized-view with (backfill=true) LatestVehicleStatus
on table VehicleTelemetry
{
VehicleTelemetry
| summarize arg_max(EventTime, *) by VehicleId
}
// Materialized view for 5-minute speed aggregates
.create materialized-view SpeedAggregates5Min
on table VehicleTelemetry
{
VehicleTelemetry
| summarize
AvgSpeedKph = avg(SpeedKph),
MaxSpeedKph = max(SpeedKph),
EventCount = count(),
FaultEvents = countif(FaultCode != "")
by VehicleId, RouteId, bin(EventTime, 5m)
}
Step 4: Create an Eventstream with Sample Data
In your workspace, create a new Eventstream named es_vehicle_telemetry. Add a Sample data source and choose the Bicycle rentals or Stock market sample (the exact vehicle scenario requires your own Event Hub, but the sample data will demonstrate the pipeline mechanics).
Add a destination pointing to your eh_logistics KQL database, targeting the VehicleTelemetry table with the ingestion mapping you'll create (for sample data, let Fabric auto-create the mapping to a test table, or manually map fields).
Step 5: Run analytical queries
After data flows in (allow 30-60 seconds for the first micro-batch), run these queries in your Queryset:
// How much data has arrived?
VehicleTelemetry
| count
// Check the materialized view is populated
LatestVehicleStatus
| take 10
// Time-series of events over the last hour
VehicleTelemetry
| where EventTime > ago(1h)
| summarize EventCount = count() by bin(EventTime, 1m)
| order by EventTime asc
| render timechart
// Speed anomalies
VehicleTelemetry
| where EventTime > ago(24h)
| summarize AvgSpeed = avg(SpeedKph), StdDev = stdev(SpeedKph), MaxSpeed = max(SpeedKph)
by VehicleId
| extend ZScore = (MaxSpeed - AvgSpeed) / StdDev
| where ZScore > 2
| order by ZScore desc
Step 6: Enable OneLake availability
In the Eventhouse settings, enable OneLake availability for the database. Navigate to your workspace's OneLake file explorer and confirm the VehicleTelemetry table appears as a Delta table. Open a Spark notebook and verify you can read it:
df = spark.read.format("delta").load("abfss://your-workspace@onelake.dfs.fabric.microsoft.com/eh_logistics.KQLDatabase/Tables/VehicleTelemetry")
df.show(5)
Event Hubs allow multiple consumer groups, each maintaining an independent read offset. If you connect your Eventstream to the $Default consumer group and another application (say, an Azure Function or a different Eventstream) also uses $Default, they compete for the same offsets. One consumer will read events that the other already consumed, or worse, events will be silently skipped. Always create a dedicated consumer group for each Eventstream connection.
New Eventhouses default to a 31-day hot cache. If your table grows large and you're not querying historical data often, you're paying for hot cache you don't need. Conversely, if your queries routinely scan 60 days of data but your hot cache is 7 days, half your query data comes from cold blob storage and your latency spikes unpredictably. Set the hot cache to match your operational query window, not the total retention period.
The most common KQL performance mistake is writing queries without a time filter:
// BAD: Scans all extents, all time
VehicleTelemetry
| where VehicleId == "VH-10482"
| order by EventTime desc
| take 100
// GOOD: Extent pruning eliminates most data immediately
VehicleTelemetry
| where EventTime > ago(7d) // Add this first
| where VehicleId == "VH-10482"
| order by EventTime desc
| take 100
Always put time-range filters first in your where clauses. KQL is smart enough to apply them out of order in some cases, but being explicit helps the optimizer and makes your intent clear.
As noted earlier, KQL assumes the right side of a join is the smaller "broadcast" table. If you accidentally join a small lookup table (left) to your billion-row telemetry table (right), performance collapses. If you find yourself in this situation, use the hint.strategy=broadcast or hint.strategy=shuffle join hints to guide the engine.
If your Eventstream appears active but data isn't arriving in the KQL table, check:
Database Ingestor role on the target KQL database. This is usually configured automatically, but in cross-workspace scenarios you may need to grant it explicitly.Materialized views are computed incrementally as new data arrives. However, if your view computes aggregations over the entire table history (no time filter), the backfill process (when backfill=true) must process all historical data. On a table with billions of rows, this can run for hours and consume significant capacity. For high-cardinality summarizations over long history, consider setting a lookback window:
.create materialized-view with (backfill=true, lookback=30d) LatestVehicleStatus
on table VehicleTelemetry
{
VehicleTelemetry
| summarize arg_max(EventTime, *) by VehicleId
}
Warning
Materialized views are eventually consistent for very recent data. The view is guaranteed to reflect data ingested more than a few seconds ago, but events in the most recent micro-batch may not yet appear. For dashboards, this is almost always acceptable. For precise operational alerts, query the base table and accept the higher query cost.
The default ingestion batching policy waits for 5 minutes, 1,000 files, or 1 GB (whichever comes first) before committing a batch. For real-time scenarios, you'll want to reduce the time threshold:
.alter table VehicleTelemetry policy ingestionbatching
'{"MaximumBatchingTimeSpan": "00:00:30", "MaximumNumberOfItems": 500, "MaximumRawDataSizeMB": 512}'
This configures batches to commit after 30 seconds, 500 events, or 512 MB. Smaller batches mean lower latency but more extents to manage and merge. Don't go below 5-10 seconds without a strong reason — the overhead of extent management at very high frequencies can hurt query performance.
By default, data is partitioned by ingestion time within each shard. For tables where queries almost always filter by a specific high-cardinality column (like VehicleId), a hash partitioning policy can significantly improve performance by co-locating rows with the same vehicle ID:
.alter table VehicleTelemetry policy partitioning
'{'
' "PartitionKeys": ['
' {'
' "ColumnName": "VehicleId",'
' "Kind": "Hash",'
' "Properties": {"Function": "XxHash64", "MaxPartitionCount": 128}'
' }'
' ]'
'}'
Hash partitioning is a significant operational commitment. The engine must reshuffle data during extent merges. Only apply it if profiling confirms that filtering by VehicleId is the dominant access pattern and queries are genuinely slow before partitioning. Measure before and after.
The right F SKU for your Real-Time Analytics workload depends primarily on:
A rough guideline: an F2 handles ~10,000 events/second with modest query load. F8 handles ~100,000 events/second with a busy dashboard. Above that, you're typically looking at F16+ or dedicated ADX clusters. These numbers vary significantly based on event size, schema complexity, and query patterns — benchmark your specific workload rather than trusting rules of thumb.
You've covered the full Real-Time Analytics stack in Microsoft Fabric. Let's consolidate the key ideas:
Eventstreams are managed streaming pipelines that abstract away the operational complexity of Event Hubs and stream processing. They connect sources (Event Hub, IoT Hub, CDC, custom apps) to destinations (Eventhouse, lakehouse, warehouse) with optional transformations. The key design decision is how much transformation to do in the Eventstream versus in KQL — when in doubt, do it in KQL where you have more expressive power and easier iteration.
Eventhouses and KQL databases store streaming data in a columnar, extent-based format optimized for time-series queries. Understanding the storage internals — extents, pruning, hot cache, materialized views — is the difference between queries that run in milliseconds and queries that run in minutes. Create tables explicitly, set cache and retention policies deliberately, and use materialized views for any aggregation that backs a dashboard or frequently-run query.
KQL is a pipe-based query language designed for telemetry analysis. Its time functions (ago, bin, between), aggregation operators (summarize, arg_max, series_make_array), and statistical functions (anomaly detection, forecasting) make it genuinely more expressive than SQL for this class of problem. The learning curve is real but worth it.
Integration with OneLake is what makes Fabric's approach distinctive. The same streaming data is simultaneously accessible via KQL (for real-time operational queries) and via Delta/Spark (for historical batch analytics and ML). This dissolves the traditional "lambda architecture" problem — no more separate batch and speed layers with reconciliation logic.
The natural next step is integrating your real-time analytics data with the broader Fabric medallion architecture. If you want to enrich streaming data with historical context, study Implementing the Medallion Architecture in Microsoft Fabric. If you need to orchestrate jobs that process data after it lands in the Eventhouse's OneLake Delta tables, Orchestrating Loads with Fabric Data Pipelines covers the scheduling and dependency management you'll need. And when you're ready to serve your processed real-time data to BI consumers, Direct Lake Mode in Power BI explains when to use Direct Lake versus KQL DirectQuery for dashboard performance.
Real-time analytics in Fabric is maturing fast. The combination of a battle-hardened storage engine (Azure Data Explorer), first-class Fabric integration, and the unified OneLake foundation makes it one of the most capable real-time platforms available without the traditional operational burden. The patterns you've learned here will serve you well across IoT, financial services, e-commerce operations, and any other domain where "yesterday's data" isn't good enough.