This post is a part of the DP-700: Implementing Data Engineering Solutions Using Microsoft Fabric Exam Prep Hub.
This topic falls under these sections:
Ingest and transform data (30–35%)
   --> Ingest and transform streaming data
      --> Process data by using KQL

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available from the hub's main page below the exam topics section.

Introduction

As organizations increasingly rely on real-time analytics, the ability to query, transform, and analyze streaming data efficiently has become a critical skill for data engineers. Within Microsoft Fabric, one of the most important technologies for real-time data processing is Kusto Query Language (KQL).

KQL is the primary query language used in Real-Time Intelligence, Eventhouses, KQL Databases, and many streaming analytics scenarios within Microsoft Fabric. It is specifically optimized for high-performance analysis of large volumes of telemetry, log, event, and time-series data.

For the DP-700 exam, candidates should understand how KQL is used to process streaming data, when it should be selected over Spark or SQL, common KQL operators, ingestion concepts, aggregation techniques, windowing functions, and real-time analytics patterns.

What Is KQL?

Kusto Query Language (KQL) is a read-optimized query language developed by Microsoft for exploring, analyzing, and transforming large volumes of structured, semi-structured, and streaming data.

KQL is the primary language used in:

• Microsoft Fabric Real-Time Intelligence
• Eventhouses
• KQL Databases
• Azure Data Explorer
• Microsoft Sentinel
• Azure Monitor Logs

KQL is designed for:

• Fast interactive analytics
• Log analysis
• Telemetry processing
• Streaming data analytics
• Time-series analysis
• Monitoring solutions

Unlike traditional T-SQL, KQL uses a pipeline-style syntax that makes analytical queries easier to read and maintain.

Why Use KQL for Streaming Data?

KQL is optimized for scenarios involving:

• High ingestion rates
• Near real-time querying
• Large event volumes
• Time-series analysis
• Operational monitoring
• IoT telemetry
• Application logs
• Security analytics

A major advantage is that newly ingested streaming data can often be queried within seconds of arrival.

KQL in Microsoft Fabric

Within Microsoft Fabric, KQL is primarily used in:

Eventhouses

Eventhouses provide scalable storage and analytics for real-time data.

Capabilities include:

• High-speed ingestion
• KQL querying
• Streaming analytics
• Time-series analysis
• Dashboard integration

Eventhouses are commonly used as the central repository for streaming event data.

KQL Databases

A KQL Database is a database inside an Eventhouse.

It stores:

• Tables
• Functions
• Materialized views
• Policies

KQL queries execute against these databases.

KQL Processing Workflow

A typical streaming architecture looks like:

Event Source
      |
      v
Eventstream
      |
      v
Eventhouse
      |
      v
KQL Database
      |
      v
KQL Queries
      |
      v
Reports / Dashboards

Data arrives continuously and becomes available for KQL analysis almost immediately.

Understanding KQL Query Structure

A basic KQL query:

Sales
| where Region == "East"
| summarize TotalSales = sum(Amount)

The pipe symbol (|) passes results from one operation to the next.

This pipeline approach is a key exam topic.

Filtering Streaming Data

The where operator filters records.

Example:

DeviceReadings
| where Temperature > 100

Common uses:

• Error events
• High temperatures
• Security incidents
• Suspicious transactions

Filtering early in a query improves performance.

Selecting Columns

The project operator selects specific columns.

Example:

Orders
| project OrderID, CustomerID, Amount

Benefits:

• Reduced memory usage
• Faster query execution
• Cleaner output

Sorting Results

The sort operator orders data.

Example:

Orders
| sort by OrderDate desc

This is frequently used in monitoring and dashboard scenarios.

Aggregating Data with Summarize

The summarize operator is one of the most important KQL operators.

Example:

Sales
| summarize TotalSales = sum(Amount)

Common aggregation functions:

Grouping Data

Grouping is accomplished with summarize and a grouping column.

Example:

Sales
| summarize TotalSales=sum(Amount)
    by Region

Output:

This pattern is heavily used in analytics solutions.

Time-Based Analysis

Streaming data is frequently analyzed by time.

Example:

Events
| summarize Count=count()
    by bin(Timestamp, 1h)

The bin() function groups records into fixed time windows.

Common windows:

• 1 minute
• 5 minutes
• 15 minutes
• 1 hour
• 1 day

Working with Time-Series Data

Time-series analysis is one of KQL’s strengths.

Example:

SensorData
| summarize AvgTemp=avg(Temperature)
    by bin(Timestamp, 5m)

This creates temperature averages every five minutes.

Typical use cases:

• IoT monitoring
• Server performance
• Manufacturing systems
• Financial transactions

Parsing Semi-Structured Data

Streaming data often arrives as JSON.

Example:

Events
| extend DeviceID = tostring(Event.DeviceID)

Common functions:

Creating Calculated Columns

The extend operator adds calculated values.

Example:

Sales
| extend Tax = Amount * .07

Common uses:

• Calculations
• Data enrichment
• Derived metrics

Joining Streaming Data

KQL supports joins between datasets.

Example:

Orders
| join Customers
on CustomerID

Common scenarios:

• Customer enrichment
• Product lookups
• Reference data joins

However, excessive joins can impact performance on very large streaming datasets.

Materialized Views

Materialized views precompute query results.

Benefits include:

• Faster analytics
• Reduced query costs
• Improved dashboard performance

Example scenario:

A dashboard continuously displays hourly sales totals.

Instead of recalculating every query, a materialized view stores precomputed results.

This is a frequently tested DP-700 optimization topic.

Update Policies

Update policies automatically transform data during ingestion.

Example:

RawEvents Table
        |
Update Policy
        |
ProcessedEvents Table

Benefits:

• Automatic transformation
• Consistent processing
• Reduced query complexity

Common use cases:

• JSON parsing
• Data enrichment
• Data normalization

Streaming Ingestion

Fabric supports streaming ingestion into Eventhouses.

Characteristics:

• Low latency
• High throughput
• Near real-time availability

Common sources include:

• Eventstreams
• Azure Event Hubs
• IoT devices
• Application telemetry
• Custom applications

KQL vs Spark Structured Streaming

DP-700 commonly tests when to choose each technology.

Use KQL When:

• Analyzing event data
• Monitoring telemetry
• Building operational dashboards
• Performing log analytics
• Working with Eventhouses

Use Spark When:

• Complex transformations are required
• Machine learning workloads exist
• Advanced ETL processing is needed
• Large-scale data engineering pipelines are required

KQL vs T-SQL

For streaming analytics scenarios in Fabric, KQL is often the preferred option.

Performance Best Practices

Filter Early

Good:

Events
| where EventType == "Error"
| summarize count()

Poor:

Events
| summarize count()
| where EventType == "Error"

Filtering early reduces processing volume.

Project Only Required Columns

Avoid retrieving unnecessary data.

Events
| project Timestamp, DeviceID

Use Materialized Views

For frequently executed analytical queries, materialized views improve performance significantly.

Use Appropriate Time Bins

Choose bin sizes carefully:

• Smaller bins = more detailed analysis
• Larger bins = better performance

Common DP-700 Exam Scenarios

Scenario 1

You need near real-time analysis of millions of IoT events.

Best choice: Eventhouse + KQL

Scenario 2

You need complex machine learning transformations on streaming data.

Best choice: Spark Structured Streaming

Scenario 3

You need a dashboard showing rolling hourly transaction counts.

Best choice: KQL summarize with bin() function

Scenario 4

You need automatic transformation of incoming JSON data.

Best choice: Update policies

DP-700 Exam Tips

Remember these key points:

• KQL is optimized for real-time analytics and event data.
• Eventhouses are the primary storage and analytics engine for KQL workloads.
• KQL uses a pipeline syntax (|).
• where filters data.
• project selects columns.
• extend creates calculated columns.
• summarize performs aggregations.
• bin() groups time-series data into intervals.
• Materialized views improve query performance.
• Update policies automate ingestion-time transformations.
• KQL is generally preferred over Spark for interactive streaming analytics.

Practice Exam Questions

Question 1

You need to analyze streaming telemetry data arriving from thousands of IoT devices and provide near real-time dashboards. Which technology should you primarily use?

A. Warehouse stored procedures
B. Dataflow Gen2
C. KQL in an Eventhouse
D. Power Query

Correct Answer: C

Explanation: KQL and Eventhouses are optimized for real-time analytics, telemetry processing, and interactive querying of streaming data.

Question 2

Which KQL operator is used to filter rows from a dataset?

A. summarize
B. where
C. project
D. extend

Correct Answer: B

Explanation: The where operator filters records based on specified conditions.

Question 3

A query needs to calculate total sales by region. Which KQL operator should be used?

A. project
B. where
C. summarize
D. extend

Correct Answer: C

Explanation: summarize performs aggregations such as sums, averages, and counts.

Question 4

Which operator is used to create a calculated column?

A. join
B. where
C. summarize
D. extend

Correct Answer: D

Explanation: The extend operator creates new calculated columns within a query.

Question 5

You need to display the number of events generated every hour. Which function should be used?

A. bin()
B. tostring()
C. parse_json()
D. countif()

Correct Answer: A

Explanation: The bin() function groups data into fixed time intervals for time-series analysis.

Question 6

Which Fabric component serves as the primary analytics engine for KQL workloads?

A. Lakehouse
B. Warehouse
C. Eventhouse
D. Dataflow Gen2

Correct Answer: C

Explanation: Eventhouses are designed for high-scale event ingestion and KQL-based analytics.

Question 7

What is the primary benefit of a materialized view?

A. Data encryption
B. Faster query performance through precomputed results
C. Reduced storage requirements
D. Automatic schema detection

Correct Answer: B

Explanation: Materialized views store precomputed query results, reducing query execution time.

Question 8

A data engineer must automatically transform incoming JSON data during ingestion. Which feature should be used?

A. Spark checkpointing
B. Eventstream routing
C. Data Activator
D. Update policies

Correct Answer: D

Explanation: Update policies automatically transform data as it is ingested into KQL tables.

Question 9

Which scenario is best suited for KQL instead of Spark Structured Streaming?

A. Large-scale machine learning pipeline
B. Deep learning model training
C. Interactive analysis of streaming telemetry data
D. Complex ETL involving hundreds of joins

Correct Answer: C

Explanation: KQL excels at real-time querying and analytics of telemetry, log, and event data.

Question 10

Which KQL operator is used to select specific columns from a dataset?

A. project
B. summarize
C. extend
D. where

Correct Answer: A

Explanation: The project operator returns only the specified columns, improving efficiency and readability.

Go to the DP-700 Exam Prep Hub main page.