Real-Time Analytics Deep Dive: Window Types, Accelerated Shortcuts, KQL Functions, Materialized Views, and Eventhouse Optimization

Our Real-Time Intelligence post covered the architecture — Eventstream, Eventhouse, KQL basics, and dashboards. This post goes deeper: the five window types for time-based analysis, accelerated vs standard shortcuts in KQL databases, advanced KQL functions for dates, strings, and aggregations, materialized views for pre-computed aggregations, and Eventhouse optimization.

Table of Contents

• The Five Window Types
• Tumbling Windows
• Hopping (Sliding) Windows
• Sliding Windows
• Session Windows
• Snapshot Windows
• Accelerated vs Standard Shortcuts in KQL DB
• Creating Accelerated Shortcuts
• Advanced KQL Functions
• Date and Time Functions
• String Functions
• Aggregation Functions
• Dynamic and JSON Parsing
• Materialized Views in KQL
• Creating and Managing Materialized Views
• Eventhouse Optimization
• Retention Policies
• Caching Policies
• Partitioning
• Error Resolution in RTI
• Eventstream Errors
• Eventhouse Errors
• Common Mistakes
• Interview Questions
• Wrapping Up

The Five Window Types

Think of window types the way an airport control tower manages air traffic. The tower does not track every plane individually from takeoff to landing — that would be overwhelming. Instead, it groups planes into time slots: “all arrivals between 2:00 and 2:30,” “all departures between 2:15 and 2:45,” or “all activity on runway 27 until traffic stops.” Each grouping strategy answers a different operational question. Window types in Real-Time Analytics work the same way — they define how you slice a continuous stream of timestamped events into manageable groups for aggregation.

There are five window types, and choosing the right one depends entirely on the business question you are trying to answer. Tumbling windows give you clean, non-overlapping buckets. Hopping windows give you overlapping, smoothed-out averages. Sliding windows detect bursts of activity. Session windows group user activity by gaps in engagement. Snapshot windows capture the current state at a point in time. Let us walk through each one with a real-world analogy, a detailed explanation, and KQL code you can run.

Tumbling Windows

Analogy — The photo booth at a theme park. A photo booth takes one picture every 5 minutes. Everyone who walks through the booth between 2:00 and 2:05 ends up in the 2:00 photo. Everyone between 2:05 and 2:10 ends up in the 2:05 photo. No one appears in two photos, and no gap exists between photos. That is a tumbling window: fixed-size, non-overlapping, with each event assigned to exactly one window.

Tumbling windows are the most common window type in streaming analytics. You use them whenever you need clean, non-overlapping counts or aggregations — “how many orders per hour,” “average temperature every 5 minutes,” or “total revenue per day.” Because windows do not overlap, there is no double-counting, which makes tumbling windows the natural choice for billing, SLA reporting, and dashboards where each event should be counted exactly once.

In KQL, the bin() function handles tumbling windows. It rounds each timestamp down to the nearest bucket boundary. If your bin size is 5 minutes, a reading at 10:07 falls into the 10:05 bucket, and a reading at 10:12 falls into the 10:10 bucket.

// Tumbling window: 5-minute non-overlapping buckets
// Each event belongs to exactly ONE window
sensor_readings
| summarize
    avg_temp = avg(temperature),
    max_temp = max(temperature),
    event_count = count()
    by bin(timestamp, 5m), device_id
| order by timestamp desc

Here is what the window boundaries look like visually. Each event falls into one and only one bucket, with no overlap between adjacent windows:

Timeline:  10:00    10:05    10:10    10:15    10:20
           |--------|--------|--------|--------|
           Window 1  Window 2  Window 3  Window 4
           (5 min)   (5 min)   (5 min)   (5 min)

Event at 10:03 → Window 1 (10:00–10:05)
Event at 10:07 → Window 2 (10:05–10:10)
Event at 10:10 → Window 3 (10:10–10:15)  ← boundary goes to next window

When to use tumbling windows: Hourly/daily/monthly aggregations for dashboards. Billing calculations where each event must be counted once. SLA monitoring (“99.9% uptime per 5-minute window”). Any metric where overlapping counts would cause confusion.

Hopping (Sliding) Windows

Analogy — Overlapping security guard shifts. Your warehouse runs 8-hour security shifts, but shifts overlap by 4 hours. The 6 AM guard works until 2 PM. The 10 AM guard works until 6 PM. The 2 PM guard works until 10 PM. Between 10 AM and 2 PM, two guards are on duty simultaneously, and anything that happens during that overlap gets reported by both guards. Hopping windows work the same way: the window size is fixed (8 hours), but windows start at regular intervals (every 4 hours), creating overlap where events belong to multiple windows.

Hopping windows are essential for moving averages and trend detection. A tumbling window gives you one number per bucket — if your 5-minute average jumps from 20°C to 40°C, you see a sharp step. A hopping window smooths that step because adjacent windows share events. This is exactly how stock market moving averages work: a “10-day moving average” is a 10-day hopping window that hops forward by 1 day.

In KQL, you implement hopping windows by combining bin() with a window range. The hop size determines how often a new window starts, and the window size determines how wide each window is. Events are duplicated across overlapping windows — an event at 10:07 with a 10-minute window hopping every 5 minutes appears in both the 10:00–10:10 window and the 10:05–10:15 window.

// Hopping window: 10-minute window, hopping every 5 minutes
// Each event appears in TWO windows (because 10min / 5min hop = 2 overlaps)
sensor_readings
| where timestamp > ago(1h)
| summarize avg_temp = avg(temperature), event_count = count()
    by bin(timestamp, 5m), device_id
| extend window_start = timestamp,
         window_end = timestamp + 10m
| order by timestamp desc
// An event at 10:07 appears in both:
//   Window 10:00–10:10 (because 10:07 ≥ 10:00 and < 10:10)
//   Window 10:05–10:15 (because 10:07 ≥ 10:05 and < 10:15)

Here is how the overlap works visually. Notice how adjacent windows share a portion of their time range:

Timeline:  10:00         10:05         10:10         10:15
           |-------------|
           Window 1 (10:00–10:10)
                         |-------------|
                         Window 2 (10:05–10:15)
                                       |-------------|
                                       Window 3 (10:10–10:20)

Overlap zone: 10:05–10:10 is covered by BOTH Window 1 and Window 2
Event at 10:07 → counted in Window 1 AND Window 2

When to use hopping windows: Moving averages (stock prices, CPU usage trends). Smoothed dashboards where sharp jumps between buckets are misleading. Anomaly detection where you compare overlapping windows to spot gradual drift. Any scenario where the answer to “what happened in the last 10 minutes?” should update every 5 minutes rather than every 10.

Sliding Windows

Analogy — A car alarm with a sensitivity threshold. Your car alarm does not go off when someone walks past once. It triggers when it detects multiple disturbances within a short time window — say, 3 bumps within 10 seconds. Each bump starts a 10-second “lookback.” If enough bumps happen within that lookback period, the alarm fires. That is a sliding window: the window is anchored to each event and looks backward (or forward) for a fixed duration to check whether a threshold is crossed.

Sliding windows differ from tumbling and hopping in a crucial way: they are event-triggered, not time-triggered. A tumbling window exists whether or not any events occur in it. A sliding window only exists because an event arrived. This makes sliding windows ideal for burst detection — “did more than 100 login attempts happen within any 1-minute period?” — where the question is about density of events around a specific moment, not about fixed time buckets.

In KQL, sliding windows are typically implemented using bin() at a fine granularity (1 second or 1 minute) combined with a threshold filter. You count events in narrow bins, then check which bins exceed a limit. Alternatively, for precise sliding windows, you can use a self-join to look for events within a time range of each other.

// Sliding window: detect bursts — more than 10 events per minute per device
// Each 1-minute bin acts as a sliding reference point
sensor_readings
| where timestamp > ago(1h)
| summarize event_count = count() by device_id, bin(timestamp, 1m)
| where event_count > 10
| order by event_count desc
// These are your "burst" minutes — investigate further

For a more precise sliding window that looks at events within a specific duration of each other (not just binned), you can use a self-join. This query finds devices where two events happened within 5 seconds of each other:

// Precise sliding window using self-join
// Find events that have another event within 5 seconds (rapid-fire pattern)
sensor_readings
| where timestamp > ago(1h)
| join kind=inner (
    sensor_readings
    | where timestamp > ago(1h)
  ) on device_id
| where timestamp1 > timestamp and datetime_diff('second', timestamp1, timestamp) <= 5
| summarize rapid_pairs = count() by device_id
| where rapid_pairs > 5
// Devices with rapid-fire event patterns

When to use sliding windows: Burst detection (DDoS attacks, login brute-force, IoT sensor spikes). Rate limiting enforcement (“no more than 100 API calls per minute”). Fraud detection where the question is “did suspicious activity cluster around this moment?” Quality control where you need to detect a sudden spike in defects on a production line.

Session Windows

Analogy — A library study session. You sit down in the library at 2:00 PM. You read for a while, take notes, check your phone, then read more. As long as there is less than 30 minutes of total inactivity between actions, the library considers it one continuous “study session.” If you leave for lunch (a gap longer than 30 minutes) and come back, that starts a new session. The session length is not fixed — it stretches as long as activity continues. The only thing that ends a session is a long enough gap.

Session windows are dynamic — their length depends on user behavior, not on a fixed clock. This makes them the natural choice for analyzing user engagement, website visits, or device activity where what you care about is “a continuous period of interaction.” An e-commerce session might last 3 minutes (quick purchase) or 45 minutes (extensive browsing). A tumbling window cannot capture this — a 30-minute tumbling window would split a 45-minute shopping session across two windows, losing the context. A session window keeps the entire interaction together.

In KQL, session windows require sorting events chronologically, computing the gap between consecutive events, marking where new sessions begin (gap exceeds your threshold), and assigning session IDs via a running sum. This is more involved than a simple bin(), but it accurately captures the real session boundaries.

// Session window: group events with less than 5 minutes gap between them
// A gap > 5 minutes starts a new session
sensor_readings
| where timestamp > ago(24h)
| sort by device_id asc, timestamp asc
| extend gap_minutes = datetime_diff('minute', timestamp, prev(timestamp))
| extend is_same_device = (device_id == prev(device_id))
| extend new_session = iif(gap_minutes > 5 or isnull(gap_minutes) or not(is_same_device), 1, 0)
| extend session_id = row_cumsum(new_session)
| summarize
    session_start = min(timestamp),
    session_end = max(timestamp),
    session_duration_min = datetime_diff('minute', max(timestamp), min(timestamp)),
    event_count = count()
    by device_id, session_id
| order by session_start desc

Here is what sessions look like with a 5-minute gap threshold. Events within the threshold are grouped together; a gap longer than 5 minutes starts a new session:

Device A timeline:
  2:00  2:02  2:03     2:07  2:09          2:20  2:22  2:25
  |-----|-----|---------|-----|              |-----|-----|
  ←——— Session 1 (2:00–2:09) ——→  11min gap  ←— Session 2 (2:20–2:25) →

Session 1: 5 events, 9 minutes duration
Session 2: 3 events, 5 minutes duration
The 11-minute gap between 2:09 and 2:20 exceeds the 5-min threshold → new session

When to use session windows: Website/app user sessions (pages visited per session, session duration). IoT device activity cycles (machine on/off periods). Customer support interactions (group chat messages into conversations). Gaming sessions (playtime tracking). Any scenario where “how long was the user engaged?” matters more than “what happened at 2:00 PM?”

Snapshot Windows

Analogy — A yearbook photo. A yearbook does not capture every moment of the school year. It captures the state of the school at one specific point in time — who is enrolled, what they look like, which clubs they belong to. If a student transfers out in February, the September yearbook still shows them. If a student joins in March, the September yearbook does not. A snapshot window works the same way: it does not look at events over a range. It asks “what is the current state of each entity right now?”

Snapshot windows are fundamentally different from the other four types because they are not about aggregating events over time. They are about capturing the latest state per entity. In a sensor monitoring scenario, a snapshot answers “what is the current temperature at each device?” rather than “what was the average temperature over the last hour?” This makes snapshots the natural choice for dashboards that show current status, “last known location” tracking, and inventory level displays.

In KQL, arg_max() is the key function for snapshots. It returns the row with the maximum value in a specified column — when that column is a timestamp, it gives you the most recent record per group. Unlike max(timestamp) which returns only the timestamp value, arg_max(timestamp, *) returns the entire row associated with that maximum timestamp.

// Snapshot: current state of every device (latest reading per device)
sensor_readings
| summarize arg_max(timestamp, *) by device_id
// arg_max(timestamp, *) returns the ENTIRE ROW where timestamp is highest
// Result: one row per device showing its most recent reading

You can also create snapshots at a specific historical point in time rather than “right now.” This is useful for “what did the system look like at midnight?” or “state of all devices at the start of the incident”:

// Historical snapshot: state of all devices at midnight last night
let snapshot_time = startofday(now());
sensor_readings
| where timestamp < snapshot_time
| summarize arg_max(timestamp, *) by device_id
// Returns the last reading BEFORE midnight for each device
// Useful for daily state reports and point-in-time audits

When to use snapshot windows: “Current status” dashboards (fleet tracking, server health, inventory levels). Point-in-time audits (“state of the system at 3:00 AM when the alert fired”). “Last known” queries (last location of a delivery truck, last heartbeat from a server). Any question phrased as “what is X right now?” rather than “what happened to X over time?”

Window Type Comparison

Here is a side-by-side comparison of all five window types. The key decision factors are whether windows overlap, whether their size is fixed or dynamic, and what kind of question each one answers.

Window Type	Size	Overlap?	Triggered By	Best For	KQL Pattern
Tumbling	Fixed	No	Clock (fixed intervals)	Clean counts, billing, SLAs	bin(timestamp, 5m)
Hopping	Fixed	Yes	Clock (hop interval)	Moving averages, smooth trends	bin() + window range
Sliding	Fixed	Yes	Each event	Burst detection, rate limiting	bin(1m) + threshold or self-join
Session	Dynamic	No	Activity gaps	User sessions, device activity	prev() + row_cumsum()
Snapshot	Point-in-time	N/A	Query execution	Current state, last known value	arg_max(timestamp, *)

Accelerated vs Standard Shortcuts in KQL DB

KQL databases in Eventhouse can create shortcuts to data stored elsewhere — in a Lakehouse, another KQL database, or an external source. A shortcut makes that external data queryable with KQL without moving or copying it. However, the speed of queries against that shortcut depends on whether you choose Standard or Accelerated mode.

Think of it like reading a book from the library. A standard shortcut is like going to the library every time you need to look something up — the information is always current (whatever edition the library has), but each trip takes time. An accelerated shortcut is like photocopying the chapters you reference frequently and keeping them on your desk — lookups are instant, but your copy might be a few minutes behind if the library just received a new edition.

Feature	Standard Shortcut	Accelerated Shortcut
Data location	Reads from OneLake at query time	Copies data into Eventhouse cache
Query speed	Slower (round-trip to OneLake per query)	Faster (data already in Eventhouse memory)
Data freshness	Always current (reads live data)	Near real-time (minutes behind, depends on refresh interval)
Use case	Infrequent queries on large historical data	Frequent queries needing sub-second response
Storage cost	No duplication (data stays in OneLake only)	Duplicates data into Eventhouse (uses Eventhouse storage)
Maintenance	None (always points to source)	Refresh interval must be configured and monitored

Creating Accelerated Shortcuts

Setting up an accelerated shortcut takes about a minute. You create a OneLake shortcut inside your KQL database, then flip the Acceleration toggle to On. From that point forward, the Eventhouse periodically pulls data from the source and caches it locally, so your KQL queries hit the local cache instead of reaching across to OneLake every time.

To create one:

1. Open your KQL Database inside the Eventhouse
2. Click New → OneLake shortcut
3. Select the source (a Lakehouse table, another KQL database, or an external ADLS path)
4. Toggle Acceleration to On
5. Configure the refresh interval — how often the Eventhouse syncs data from the source (every 1 minute, 5 minutes, etc.)

Once the shortcut is created, you query it exactly like a regular table. There is no special syntax — the acceleration is transparent:

// Query an accelerated shortcut — same syntax as any KQL table
accelerated_sales_data
| where order_date > ago(7d)
| summarize revenue = sum(amount) by product
| top 10 by revenue
// Fast because data is pre-loaded into Eventhouse cache
// If this were a standard shortcut, same query would take longer
// because each execution would read from OneLake

DP-700 exam tip: Know when to choose accelerated (frequent queries, speed matters, slight staleness is acceptable) vs standard (infrequent queries, storage cost matters, data must always be current). If a dashboard refreshes every 30 seconds and queries a Lakehouse table, accelerated is the right choice. If an analyst runs one ad-hoc query per week against 5 years of history, standard is fine.

Advanced KQL Functions

The window types above define how you group events. The KQL functions below define what you calculate within each group. This section covers the most important function categories for Real-Time Analytics: date/time manipulation, string operations, aggregation functions beyond basic count/sum/avg, and JSON parsing for semi-structured event payloads.

Date and Time Functions

Time is the backbone of real-time analytics — every query starts with a time filter, and most aggregations group by time. KQL provides functions for getting the current time, extracting date parts (year, month, hour), performing date arithmetic (add days, compute differences), finding period boundaries (start of month, end of week), formatting timestamps for display, and filtering date ranges. Here are the essential ones in a single reference block:

// Current time
print now()

// Extract date parts from a timestamp
sensor_readings
| extend year = datetime_part("year", timestamp),
         month = datetime_part("month", timestamp),
         hour = datetime_part("hour", timestamp),
         day_of_week = dayofweek(timestamp)

// Date arithmetic — add/subtract durations, compute differences
| extend yesterday = timestamp - 1d,
         next_week = timestamp + 7d,
         age_hours = datetime_diff("hour", now(), timestamp)

// Period boundaries — useful for "start of month" grouping
| extend start_of_month = startofmonth(timestamp),
         end_of_month = endofmonth(timestamp),
         start_of_day = startofday(timestamp)

// Format timestamps for human-readable output
| extend formatted = format_datetime(timestamp, "yyyy-MM-dd HH:mm")

// Date range filter using between
| where timestamp between (datetime(2026-01-01) .. datetime(2026-06-01))

String Functions

Event data is rarely clean. Device names arrive in mixed case, fields contain extra whitespace, and identifiers are embedded inside longer strings. KQL string functions let you normalize, search, extract, split, and transform text fields without leaving the query. The extract() function is particularly powerful — it runs a regular expression against a string and pulls out the matching group, which is essential for parsing semi-structured log messages.

// Common string operations in one reference block
events
| extend lower_name = tolower(name),                        // Normalize case
         upper_name = toupper(name),
         trimmed = trim(" ", raw_name),                     // Remove whitespace
         contains_error = name contains "error",            // Case-insensitive search
         starts_with = name startswith "ERR",               // Prefix check
         extracted = extract("device-(\\d+)", 1, device_name), // Regex: pull device number
         split_parts = split(csv_field, ","),                // Split into array
         replaced = replace_string(phone, "-", ""),         // Remove dashes
         length = strlen(name),                             // String length
         substring = substring(name, 0, 5)                  // First 5 characters

Aggregation Functions

Beyond basic count(), sum(), and avg(), KQL provides advanced aggregation functions that answer sophisticated analytical questions in a single query. dcount() gives you approximate distinct counts (how many unique devices reported). percentile() gives you distribution metrics (the 95th percentile response time, which is far more useful than the average for SLA monitoring). make_list() and make_set() collect values into arrays — one keeps duplicates, the other deduplicates.

// Advanced aggregations — beyond count/sum/avg
sensor_readings
| where timestamp > ago(1h)
| summarize
    event_count = count(),                                  // Total events
    distinct_devices = dcount(device_id),                   // Approximate unique count
    percentile_95 = percentile(temperature, 95),            // 95th percentile
    median_temp = percentile(temperature, 50),              // Median (50th percentile)
    variance = variance(temperature),                       // Statistical variance
    stdev = stdev(temperature),                             // Standard deviation
    min_temp = min(temperature),                            // Minimum
    max_temp = max(temperature),                            // Maximum
    all_devices = make_list(device_id),                     // Array of ALL values (with dupes)
    unique_devices = make_set(device_id)                    // Array of UNIQUE values
    by bin(timestamp, 1h)

Dynamic and JSON Parsing

Many event sources send payloads as JSON strings. An IoT device might send {"device_id": "sensor-42", "readings": {"temperature": 22.5, "humidity": 65}} as a single string column. KQL’s parse_json() converts that string into a dynamic (JSON) object that you can navigate with dot notation. mv-expand then “explodes” JSON arrays into individual rows — essential for tags, labels, or nested lists.

// Parse JSON payload from a string column
events
| extend parsed = parse_json(json_payload)
| extend device = tostring(parsed.device_id),           // Navigate with dot notation
         temp = todouble(parsed.readings.temperature),  // Nested access + type cast
         tags = parsed.tags                             // Array remains as dynamic
| mv-expand tag = tags                                  // Expand array to one row per tag
// Result: if an event had tags ["urgent", "floor-2"], you now have
// two rows — one with tag="urgent" and one with tag="floor-2"

For the complete KQL function reference with 20+ string functions, 25+ date functions, all join types, and 8 real-world query patterns, see our KQL Complete Guide.

Materialized Views in KQL

Imagine you run a restaurant where 50 servers ask the kitchen “how many orders did we complete this hour?” every 30 seconds. Without a materialized view, the kitchen counts all the order tickets from scratch every single time — scanning through hundreds of tickets 50 times per minute. With a materialized view, the kitchen keeps a running tally on a whiteboard. When a new order completes, the tally updates. When a server asks, they just read the whiteboard. The whiteboard is the materialized view: a pre-computed aggregation that updates incrementally as new data arrives.

In KQL, a materialized view is a summarize query that the Eventhouse maintains automatically. When new data arrives in the source table, the view updates only the affected aggregation buckets — it does not re-scan the entire table. For a table with billions of rows, this means the difference between a 30-second full-table scan and a sub-second read of the pre-computed result.

Creating and Managing Materialized Views

The lifecycle of a materialized view has five stages: create, query, monitor, manage (disable/enable), and drop. Here is the complete reference for each stage:

// CREATE: hourly averages that auto-update when source data arrives
.create materialized-view HourlyAvgTemp on table sensor_readings
{
    sensor_readings
    | summarize avg_temp = avg(temperature), max_temp = max(temperature), count = count()
        by bin(timestamp, 1h), device_id
}

// QUERY: instant results — reads pre-computed aggregation, no re-scan
HourlyAvgTemp
| where timestamp > ago(7d)
| order by timestamp desc

// LIST all materialized views in the database
.show materialized-views

// CHECK HEALTH of a specific view (is it keeping up with ingestion?)
.show materialized-view HourlyAvgTemp extents

// DISABLE refresh (pause auto-updating to save compute during maintenance)
.alter materialized-view HourlyAvgTemp disable

// ENABLE refresh (resume — the view catches up on data it missed while paused)
.alter materialized-view HourlyAvgTemp enable

// DROP (delete the view permanently — does NOT delete source data)
.drop materialized-view HourlyAvgTemp

When to use materialized views: The decision comes down to how often the same aggregation is queried versus how much compute the pre-computation costs. If a dashboard tile queries “hourly average temperature” every 30 seconds, pre-computing it once and serving it instantly is dramatically cheaper than scanning billions of raw rows 120 times per hour. If a query runs once a month during an ad-hoc investigation, the maintenance overhead of a materialized view is not worth it.

Scenario	Use Materialized View?	Why
Dashboard tile querying hourly averages every 30 seconds	✅ Yes	Pre-compute once, serve instantly — saves 120 full scans per hour
Ad-hoc investigation of a specific device failure	❌ No	One-time query — no benefit from ongoing maintenance
Daily summary report queried by 50 analysts each morning	✅ Yes	Avoids 50 analysts each scanning billions of rows independently
One-time data exploration during a POC	❌ No	Not worth the maintenance overhead for temporary work
Real-time alerting that checks a threshold every 10 seconds	✅ Yes	Sub-second reads from the view vs multi-second scans every 10 seconds

Materialized views vs regular queries: A regular aggregation query scans ALL raw data every time it runs. A materialized view pre-computes the aggregation and maintains it incrementally — when new data arrives, only the new data is processed. For a table with 10 billion rows receiving 1 million new rows per hour, a regular query scans 10 billion rows. The materialized view processes only the 1 million new rows and merges them into the existing result. That is the difference between 30 seconds and 50 milliseconds.

Eventhouse Optimization

An Eventhouse is not a “set it and forget it” resource. Without tuning, your storage grows indefinitely (no automatic cleanup), all data sits in expensive hot cache (whether it is queried or not), and queries scan the entire table for every time filter (no partition pruning). The three levers for optimization are retention policies (how long to keep data), caching policies (what stays on fast SSD vs cheap blob storage), and partitioning (how data is physically organized for faster scans).

Retention Policies

Retention policies define how long data lives before automatic deletion. Without a retention policy, data stays forever — your Eventhouse grows by gigabytes per day and your storage costs climb linearly. The SoftDeletePeriod controls this: data older than this period is automatically purged during background cleanup. Set it based on how far back your queries ever need to look.

// Set data retention: auto-delete data older than 90 days
.alter table sensor_readings policy retention
    '{"SoftDeletePeriod": "90.00:00:00"}'

// Check current retention policy for a table
.show table sensor_readings policy retention

// Different retention per table based on how far back queries look:
// Hot IoT sensor data: keep 30 days (high volume, recent queries only)
.alter table sensor_readings policy retention
    '{"SoftDeletePeriod": "30.00:00:00"}'

// Aggregated daily summaries: keep 1 year (low volume, historical trend queries)
.alter table daily_summaries policy retention
    '{"SoftDeletePeriod": "365.00:00:00"}'

// Device metadata: keep 5 years (tiny table, needed for historical joins)
.alter table device_metadata policy retention
    '{"SoftDeletePeriod": "1825.00:00:00"}'

Caching Policies

Caching controls where data physically lives — hot cache (fast SSD attached to the Eventhouse compute nodes) vs cold storage (cheaper Azure blob storage). Recent data that is queried frequently should be hot for sub-second response times. Older data that is queried occasionally can be cold — queries still work, they just take a few seconds instead of milliseconds. The caching policy defines the boundary: “keep the last N days on SSD, everything older goes to blob.”

// Keep last 30 days on fast SSD, older data on cheaper blob storage
.alter table sensor_readings policy caching hot = 30d

// Check current caching policy
.show table sensor_readings policy caching

// Different caching per table based on query patterns:
// Real-time sensor data: 7 days hot (queried by live dashboards constantly)
.alter table sensor_readings policy caching hot = 7d

// Device metadata: 365 days hot (small table, always needed for joins)
.alter table device_metadata policy caching hot = 365d

// Historical summaries: 30 days hot (queried in weekly review meetings)
.alter table daily_summaries policy caching hot = 30d

How hot cache works in practice: Data within the hot cache period is stored on SSD — queries return in milliseconds. Data outside the hot cache period moves to Azure blob storage — queries return in 1-5 seconds (still fast, just not instant). Set the hot cache duration based on how far back your dashboards and regular queries typically look. Add a buffer beyond the typical lookback to cover ad-hoc investigation.

Dashboard Lookback	Recommended Hot Cache	Reasoning
Last 1 hour (real-time monitoring)	7 days	Buffer for ad-hoc investigation when alerts fire
Last 7 days (weekly ops review)	14–30 days	Cover the review window plus investigation depth
Last 30 days (monthly analysis)	45–60 days	Cover the analysis window with room to compare
Rarely queried historical data	7 days (minimal)	Keep minimal hot cache — cold queries are still fast enough

Partitioning

Partitioning controls how data is physically organized on disk. By default, data is organized by ingestion time. If your queries always filter by a specific column (like timestamp or device_id), explicit partitioning can dramatically speed up scans by allowing the query engine to skip irrelevant data extents entirely. Partition by the column you filter on most frequently.

// Partition by date for faster time-range queries
// Each partition covers 1 day of data
.alter table sensor_readings policy partitioning
@'{"PartitionKeys": [{"ColumnName": "timestamp", "Kind": "UniformRange", "Properties": {"Reference": "2020-01-01", "RangeSize": "1.00:00:00"}}]}'

// Partition by device_id for faster device-specific queries (hash partitioning)
.alter table sensor_readings policy partitioning
@'{"PartitionKeys": [{"ColumnName": "device_id", "Kind": "Hash", "Properties": {"Function": "XxHash64", "MaxPartitionCount": 128}}]}'

// Check current partitioning policy
.show table sensor_readings policy partitioning

Partitioning trade-off: Partitioning adds overhead during ingestion (data must be sorted and routed to the correct partition). Only add partitioning if your queries consistently filter on the partition column. If your queries already have time filters and performance is acceptable, the default ingestion-time organization may be sufficient.

Error Resolution in RTI

Real-Time Intelligence involves multiple moving parts — Eventstreams ingesting data, Eventhouses storing and querying it, and materialized views maintaining aggregations. When something breaks, the error messages can be cryptic. This section provides a diagnostic table for the most common errors in each component, along with the root cause and the fix.

Eventstream Errors

Error	Cause	Fix
Ingestion lag increasing	Source producing faster than destination can consume	Scale up Eventhouse capacity, add partitions to Event Hub, or apply Eventstream filter to reduce volume
Deserialization error	Event format does not match expected schema (wrong JSON structure, missing fields)	Fix source event format or update Eventstream schema mapping to match the actual payload
Connection lost to Event Hub	Event Hub namespace down, access key expired, or network issue	Check Event Hub health in Azure Portal, rotate keys if expired, verify network connectivity
No events flowing	Source stopped sending, Eventstream paused, or filter too restrictive	Check source application is running, verify Eventstream is not paused, review filter conditions
Duplicate events	At-least-once delivery from source (Event Hub default), or Eventstream restart replayed events	Add deduplication in KQL query using arg_max() or take_any(), or use Eventstream filter transformation

Monitoring Eventstream health: Open the Eventstream item in Fabric to see real-time metrics — events ingested per second, end-to-end latency (source to destination), and error counts per transformation node. If ingestion lag grows steadily, the source is producing faster than the destination can consume — either reduce the input volume (filter early) or increase the Eventhouse capacity.

Eventhouse Errors

Error	Cause	Fix
KQL query timeout	Scanning too much data without a time filter	Add | where timestamp > ago(1h). Use materialized views for repeated aggregations
Disk full / storage limit	No retention policy — data growing indefinitely	Set retention: .alter table T policy retention '{"SoftDeletePeriod":"90.00:00:00"}'
Materialized view stale	View refresh failed (source schema changed, view disabled, or compute exhausted)	Check health: .show materialized-view V extents. Re-enable or recreate if schema changed
Shortcut returns stale data	Accelerated shortcut refresh failed (source unavailable or permissions changed)	Check source connectivity and permissions, restart acceleration from the shortcut settings
Schema mismatch on ingestion	Source added or changed columns without updating the table schema	Update table: .alter table T (new_column:string). Restart ingestion mapping
Slow queries on older data	Querying data outside the hot cache period (data on cold storage)	Increase hot cache duration if this is a frequent query pattern, or accept the slower response

Common Mistakes

1. Not setting retention policies. Eventhouse storage grows indefinitely without a retention policy. A table ingesting 1 GB per day becomes 365 GB after a year. Set 30-90 day retention for raw data and longer retention for pre-aggregated summaries.

2. Using standard shortcuts when accelerated is needed. A dashboard that queries a OneLake shortcut every 30 seconds will be slow with standard mode because every query round-trips to OneLake. Switch to accelerated for any shortcut queried more than a few times per hour.

3. Not using materialized views for repeated aggregations. If 50 analysts or dashboard tiles run the same “hourly average” query, each one scans the entire raw table independently. One materialized view pre-computes the result and serves all 50 consumers instantly.

4. Wrong window type for the business question. Tumbling for non-overlapping counts, hopping for moving averages, sliding for burst detection, session for user activity, snapshot for current state. Using a tumbling window when you need a moving average gives you a staircased chart instead of a smooth trend line.

5. Querying without time filters. KQL tables in Eventhouse can contain billions of rows spanning months or years. A query without | where timestamp > ago(1h) scans everything, leading to timeouts and excessive compute costs. Always start with a time filter — narrow it to the smallest range that answers your question.

6. Setting hot cache equal to retention. If your retention is 90 days, setting hot cache to 90 days means ALL data sits on expensive SSD. Most queries only look back 7-14 days. Set hot cache to 14-30 days and let older data move to cheaper cold storage — cold queries still return in seconds.

7. Ignoring materialized view health. A materialized view that falls behind (refresh fails or lag grows) silently returns stale results. Periodically run .show materialized-view V extents to check if the view is keeping up with ingestion. If a source table schema changes, the view may break silently.

8. Not deduplicating at-least-once events. Event Hub and most streaming sources use at-least-once delivery — the same event may arrive twice. If you do not account for this, your counts, sums, and averages will be inflated. Use arg_max(timestamp, *) by event_id or a deduplication materialized view to ensure each event is counted once.

Interview Questions

Q: What are the five window types in Fabric Real-Time Analytics, and when do you use each?
A: Tumbling windows are fixed-size, non-overlapping buckets — use them for clean counts, billing, and SLA metrics. Hopping windows are fixed-size but overlap at regular intervals — use them for moving averages and smooth trend lines. Sliding windows are event-triggered with a fixed lookback duration — use them for burst detection and rate limiting. Session windows are dynamic-size based on activity gaps — use them for user session analysis and device activity cycles. Snapshot windows capture the latest state per entity at a point in time — use them for “current status” dashboards and last-known-value queries.

Q: What is the difference between accelerated and standard shortcuts in a KQL database?
A: A standard shortcut reads from OneLake at query time — always current but slower because every query makes a round-trip. An accelerated shortcut copies data into the Eventhouse cache and refreshes at a configured interval — faster because queries hit local SSD, but data may be minutes behind the source. Use accelerated for frequently queried tables and dashboards where sub-second response matters. Use standard for infrequent historical queries where storage cost and freshness matter more than speed.

Q: What is a materialized view, and how does it differ from running the same query directly?
A: A materialized view is a pre-computed aggregation that the Eventhouse maintains automatically. When new data arrives, the view updates incrementally — it processes only the new rows, not the entire table. A regular query scans all raw data every time it runs. For a table with 10 billion rows, the materialized view returns results in milliseconds (reads pre-computed output), while a direct query might take 30+ seconds (scans the full table). The trade-off is maintenance overhead — the view consumes compute to stay updated.

Q: How do retention and caching policies work together in an Eventhouse?
A: Retention controls how long data exists — after the retention period, data is permanently deleted. Caching controls where data is stored — within the hot cache period, data sits on fast SSD; outside it, data moves to cheaper blob storage but remains queryable. The hot cache period must be less than or equal to the retention period. A typical configuration is 90-day retention with 14-day hot cache: you keep 90 days of data, but only the most recent 14 days are on fast SSD for sub-second queries. Older data returns in 1-5 seconds from cold storage.

Q: How would you detect an IoT sensor sending abnormally high volumes of data?
A: Use a sliding window approach — count events per device per minute using bin(timestamp, 1m) and filter for devices exceeding a threshold. For example: sensor_readings | summarize count() by device_id, bin(timestamp, 1m) | where count_ > 100 finds any minute where a device sent more than 100 events. For continuous monitoring, create a materialized view with this logic and set a Data Activator alert on the view’s output to trigger notifications automatically.

Q: What is the difference between tumbling and session windows when analyzing user behavior?
A: A tumbling window groups events by fixed time buckets regardless of user activity — a 30-minute window from 2:00-2:30 captures everything in that range. A session window groups events by user activity with a configurable inactivity gap — if a user is active from 2:05 to 2:47 with no gap longer than 5 minutes, the entire period is one session, even though it spans two tumbling windows. Tumbling windows are better for scheduled reporting (“events per hour”). Session windows are better for engagement analysis (“average session duration,” “pages per visit”).

Q: You have a dashboard querying hourly averages from a table with 5 billion rows, and queries are timing out. How do you fix it?
A: Three steps. First, create a materialized view for the hourly aggregation — this pre-computes the result and serves it in milliseconds instead of scanning 5 billion rows. Second, ensure the hot cache period covers at least the dashboard’s lookback range (if the dashboard shows 7 days, set hot cache to 14+ days). Third, add explicit time filters to the query — | where timestamp > ago(7d) reduces the scan range from 5 billion rows to whatever arrived in the last 7 days.

Wrapping Up

This post completes your Real-Time Intelligence knowledge. You now understand how each window type works and when to use it, how to choose between accelerated and standard shortcuts, how materialized views eliminate redundant computation, and how retention, caching, and partitioning keep your Eventhouse fast and cost-effective. Combined with the RTI overview post and the KQL Complete Guide, you now cover all DP-700 streaming objectives.

Related posts:
– Real-Time Intelligence Overview
– KQL Complete Guide
– Spark Structured Streaming
– Data Activator

---

---

Naveen Vuppula is a Senior Data Engineering Consultant and app developer based in Ontario, Canada. He writes about Python, SQL, AWS, Azure, and everything data engineering at DriveDataScience.com.