Tag: KQL

AI-200, azure August 15, 2026

Write KQL queries to analyze logs and metrics (AI-200 Exam Prep)

This post is a part of the AI-200: Developing AI Cloud Solutions on Azure  Exam Prep Hub.
This topic falls under these sections:
Secure, monitor, and troubleshoot Azure solutions (20–25%)
   --> Monitor and troubleshoot Azure solutions
      --> Write KQL queries to analyze logs and metrics

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 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Overview

Azure applications generate large amounts of telemetry, including application logs, resource logs, requests, dependencies, exceptions, performance information, and metrics. For an AI cloud developer, being able to turn this telemetry into useful information is an important troubleshooting and monitoring skill.

Kusto Query Language (KQL) is the query language used by Azure Monitor Logs and Log Analytics. It is designed for querying and analyzing large volumes of structured and semi-structured data. KQL is also used across several Microsoft services, including Azure Monitor, Azure Data Explorer, Microsoft Fabric, and Microsoft Sentinel.

For the AI-200 exam, you should be comfortable reading and writing KQL queries that:

Filter log records
Select and rename columns
Sort results
Limit returned records
Create calculated columns
Aggregate data
Group results
Analyze data over time
Identify errors and exceptions
Analyze application performance
Join or combine related data
Create time-series visualizations
Investigate trends and anomalies
Analyze telemetry from distributed applications

1. What Is Kusto Query Language?

Kusto Query Language (KQL) is a read-only query language optimized for analyzing large datasets.

A KQL query generally starts with a table and then applies a sequence of operations to that table.

For example:

			
AzureActivity
| where TimeGenerated > ago(1h)
| where ActivityStatus == "Failed"
| project TimeGenerated, OperationNameValue, ActivityStatus

The pipe character (|) passes the output of one operation to the next.

Conceptually:

			
Table
   ↓
Filter
   ↓
Filter
   ↓
Select columns
   ↓
Results

		

This pipeline-oriented approach is one of the most important characteristics of KQL.

KQL queries are read-only. They retrieve and analyze data rather than modifying the underlying records.

2. Azure Monitor Logs and Log Analytics

Azure Monitor Logs stores telemetry in a Log Analytics workspace.

Log Analytics is one of the primary tools used in the Azure portal to write and execute KQL queries.

The general relationship is:

			
Azure Resources / Applications
             ↓
        Azure Monitor
             ↓
      Diagnostic data
             ↓
    Log Analytics Workspace
             ↓
          KQL
             ↓
     Analysis / Alerts /
     Workbooks / Reports

		

Resource logs aren’t automatically available in a Log Analytics workspace simply because the resource exists. A diagnostic setting generally needs to be configured to send resource logs to the workspace.

KQL queries can subsequently be used for troubleshooting, analysis, dashboards, alerts, and reporting.

3. Understanding the Basic KQL Query Structure

A simple KQL query looks like this:

			
TableName
| operator
| operator
| operator

For example:

			
AppRequests
| where TimeGenerated > ago(1h)
| where Success == false
| project TimeGenerated, Name, ResultCode

Each line operates on the results produced by the previous line.

Important exam concept

KQL isn’t SQL.

For example:

SQL:

			
SELECT Name, ResultCode
FROM AppRequests
WHERE Success = 0;

KQL:

			
AppRequests
| where Success == false
| project Name, ResultCode

The order and syntax are different.

4. The `where` Operator

The where operator filters records.

			
AppRequests
| where Success == false

This returns only unsuccessful requests.

Multiple conditions can be combined:

			
AppRequests
| where Success == false
| where ResultCode == 500

Or:

			
AppRequests
| where Success == false and ResultCode == 500

You can also use or:

			
AppRequests
| where ResultCode == 500 or ResultCode == 503

Common comparison operators

Operator	Meaning
`==`	Equals
`!=`	Not equal
`>`	Greater than
`<`	Less than
`>=`	Greater than or equal
`<=`	Less than or equal
`contains`	Contains text
`startswith`	Starts with text
`endswith`	Ends with text
`in`	Matches one of several values

Example:

			
AppRequests
| where ResultCode in (500, 502, 503)

5. Filtering by Time

Time filtering is extremely important when troubleshooting.

A common approach is the ago() function.

			
AppRequests
| where TimeGenerated > ago(1h)

This means:

Return records generated within the last hour.

Other examples:

| where TimeGenerated > ago(30m)

| where TimeGenerated > ago(24h)

| where TimeGenerated > ago(7d)

You can also specify explicit timestamps:

			
| where TimeGenerated between (
    datetime(2026-08-10 08:00:00) ..
    datetime(2026-08-10 12:00:00)
)

Exam tip

When investigating an incident, filtering by time early is usually a good practice because it reduces the amount of data being processed and makes the results easier to interpret.

6. The `project` Operator

Use project to select the columns you want returned.

			
AppRequests
| project TimeGenerated, Name, ResultCode

Instead of returning every available column, the query returns only the selected columns.

You can also rename columns:

			
AppRequests
| project Timestamp = TimeGenerated, Endpoint = Name, Status = ResultCode

This is particularly useful when creating clean results for reports or troubleshooting.

7. The `project-away` Operator

project-away removes columns from the result.

For example:

			
AppRequests
| project-away Computer, TenantId

This can be useful when you want most columns but need to exclude a few.

8. The `extend` Operator

extend creates a calculated column.

For example:

			
AppRequests
| extend DurationSeconds = DurationMs / 1000.0
| project Name, DurationMs, DurationSeconds

The original columns remain available, and the calculated column is added to the result.

Another example:

			
AppRequests
| extend IsSlow = DurationMs > 2000

Now each record has an IsSlow value indicating whether the request exceeded two seconds.

`extend` vs. `project`

This distinction is important:

extend adds or calculates columns.
project controls which columns appear in the final result.

They are frequently used together.

9. The `summarize` Operator

summarize performs aggregations.

For example:

			
AppRequests
| summarize count()

This returns the number of records.

You can calculate averages:

			
AppRequests
| summarize avg(DurationMs)

Other common aggregation functions include:

Function	Purpose
`count()`	Number of records
`sum()`	Sum
`avg()`	Average
`min()`	Minimum
`max()`	Maximum
`dcount()`	Approximate distinct count
`countif()`	Count records meeting a condition
`sumif()`	Sum values meeting a condition
`avgif()`	Average values meeting a condition

10. Grouping with `summarize`

You can group results using by.

For example:

			
AppRequests
| summarize count() by ResultCode

This produces a count for each result code.

You can also group by multiple columns:

			
AppRequests
| summarize count() by Name, ResultCode

This is similar conceptually to a GROUP BY operation in SQL.

11. Conditional Aggregations

KQL’s conditional aggregation functions are particularly useful for monitoring.

For example:

			
AppRequests
| summarize
    TotalRequests = count(),
    FailedRequests = countif(Success == false)

You could calculate an error rate:

			
AppRequests
| summarize
    TotalRequests = count(),
    FailedRequests = countif(Success == false)
| extend ErrorRate = 100.0 * FailedRequests / TotalRequests

		

This is an excellent example of using KQL to turn raw telemetry into a meaningful application-health metric.

12. Sorting Results

Use order by or sort by to sort results.

			
AppRequests
| order by DurationMs desc

This puts the slowest requests first.

You can sort ascending:

			
AppRequests
| order by DurationMs asc

Troubleshooting example

To find the slowest requests:

			
AppRequests
| where TimeGenerated > ago(1h)
| order by DurationMs desc
| take 20

This returns the 20 slowest requests from the last hour.

13. Limiting Results with `take`

Use take to return a specified number of records.

			
AppRequests
| take 10

For troubleshooting:

			
AppExceptions
| where TimeGenerated > ago(30m)
| take 50

take is particularly useful when exploring an unfamiliar table.

14. The `distinct` Operator

Use distinct to return unique values.

			
AppRequests
| distinct Name

You can use multiple columns:

			
AppRequests
| distinct Name, ResultCode

This is useful for understanding the values contained in a dataset.

15. Searching Text

KQL provides several operators for text analysis.

For example:

			
AppExceptions
| where OuterMessage contains "timeout"

You can also use:

			
AppRequests
| where Name startswith "/api/"

And:

			
AppRequests
| where Name endswith "/health"

The has operator can also be useful for term-based searches:

			
AppExceptions
| where OuterMessage has "timeout"

Understanding the difference between text-search operators can matter when troubleshooting log data.

16. Working with Null and Empty Values

Use isnull() and isnotnull() when checking for null values.

			
AppRequests
| where isnull(UserId)

Or:

			
AppRequests
| where isnotnull(UserId)

For strings, isempty() and isnotempty() can be useful.

			
AppRequests
| where isnotempty(Name)

17. Binning Data with `bin()`

When analyzing data over time, you often don’t want every individual event.

Instead, you can group events into time intervals.

For example:

			
AppRequests
| summarize count() by bin(TimeGenerated, 5m)

This produces request counts in five-minute intervals.

You can use different intervals:

bin(TimeGenerated, 1m)

bin(TimeGenerated, 1h)

bin(TimeGenerated, 1d)

This is one of the most important techniques for creating time-series analyses.

18. Creating Time-Series Charts

A typical query might be:

			
AppRequests
| summarize RequestCount = count()
    by bin(TimeGenerated, 5m)
| render timechart

The query:

Counts requests.
Groups them into five-minute intervals.
Produces a time-series visualization.

Azure Monitor’s Metrics and log-based analysis commonly use this type of time-binning approach.

19. Analyzing Errors Over Time

Suppose an application is experiencing intermittent failures.

You could use:

			
AppRequests
| where TimeGenerated > ago(24h)
| summarize
    TotalRequests = count(),
    FailedRequests = countif(Success == false)
    by bin(TimeGenerated, 15m)
| extend ErrorRate = 100.0 * FailedRequests / TotalRequests
| render timechart

		

This allows you to see whether error rates are increasing, decreasing, or occurring in bursts.

20. Finding the Most Common Errors

For exception analysis:

			
AppExceptions
| where TimeGenerated > ago(24h)
| summarize Count = count() by OuterMessage
| order by Count desc
| take 20

		

This identifies the most frequently occurring exception messages.

A developer could then investigate whether the most common exception corresponds to a particular application component or deployment.

21. Analyzing HTTP Status Codes

A useful troubleshooting query is:

			
AppRequests
| where TimeGenerated > ago(1h)
| summarize Count = count() by ResultCode
| order by Count desc

To focus specifically on server errors:

			
AppRequests
| where TimeGenerated > ago(1h)
| where ResultCode startswith "5"
| summarize Count = count() by ResultCode
| order by Count desc

		

This can help identify whether an application is experiencing widespread 5xx failures.

22. Identifying Slow Requests

A simple performance query:

			
AppRequests
| where TimeGenerated > ago(1h)
| where DurationMs > 2000
| project TimeGenerated, Name, DurationMs, ResultCode
| order by DurationMs desc

		

This finds requests taking more than two seconds.

You can instead summarize performance by endpoint:

			
AppRequests
| summarize
    AverageDuration = avg(DurationMs),
    MaximumDuration = max(DurationMs),
    RequestCount = count()
    by Name
| order by AverageDuration desc

		

This helps identify endpoints that consistently perform poorly.

23. Counting Distinct Users

The dcount() function provides an approximate distinct count.

For example:

			
AppRequests
| summarize UniqueUsers = dcount(UserId)

This is often useful for telemetry where an exact distinct count isn’t required.

For example:

			
AppRequests
| summarize UniqueUsers = dcount(UserId)
    by bin(TimeGenerated, 1h)
| render timechart

24. Joining Data

Sometimes the information needed to investigate a problem is stored in multiple tables.

KQL supports operations such as join.

Conceptually:

			
TableA
| join kind=inner TableB on SomeColumn

For example, you might correlate application records with another dataset containing additional information.

The join operator combines rows from two tables based on matching values.

Important exam consideration

Don’t automatically use join simply because two datasets exist. First determine whether the required information can be obtained from a single table.

Also remember that Azure Monitor has some KQL differences and limitations compared with Azure Data Explorer. For example, certain cross-cluster functionality isn’t supported in Azure Monitor.

25. The `union` Operator

union combines data from multiple tables or datasets.

Conceptually:

union TableA, TableB

This is useful when similar telemetry exists in multiple tables.

For example, Application Insights telemetry can be analyzed across multiple telemetry tables.

26. Working with Application Insights Telemetry

Application Insights provides application telemetry such as:

Requests
Dependencies
Exceptions
Traces
Page views
Availability results
Custom events
Custom metrics

For example, you can examine requests:

			
requests
| where timestamp > ago(1h)
| summarize count() by resultCode

Or exceptions:

			
exceptions
| where timestamp > ago(1h)
| summarize count() by type
| order by count_ desc

The exact tables and schema depend on the telemetry architecture and Azure Monitor/Application Insights configuration being used, so the ability to inspect the available table schema is important.

27. Logs Versus Metrics

A key concept for AI-200 is understanding that logs and metrics are complementary.

Metrics

Metrics are typically numerical measurements designed for efficient monitoring and alerting.

Examples include:

CPU percentage
Request count
Memory utilization
Network traffic
Latency

Logs

Logs provide detailed records about events and operations.

Examples include:

Exceptions
HTTP requests
Dependency calls
Resource operations
Application traces
Security events

A metric might tell you:

Error rate increased to 12%.

A log query can help answer:

Which endpoint is failing, what exception is occurring, and which dependency is involved?

Azure Monitor supports working with both metrics and logs, and log-based metrics can themselves be represented through KQL queries.

28. Querying Resource Logs

Azure resources can send resource logs to Log Analytics through diagnostic settings.

Once the logs are available, KQL can be used to analyze them.

For example:

			
AzureActivity
| where TimeGenerated > ago(1h)
| summarize count() by Category

Or:

			
AzureActivity
| where TimeGenerated > ago(24h)
| where ActivityStatus == "Failed"
| project TimeGenerated, OperationNameValue, ActivityStatus

The exact table and fields depend on the Azure service and diagnostic configuration.

29. Using `render`

The render operator specifies how query results should be visualized.

For example:

			
AppRequests
| summarize count() by bin(TimeGenerated, 5m)
| render timechart

Other visualization types can be used depending on the data and analysis.

The important exam concept is that render affects how results are displayed, not how the underlying records are filtered or aggregated.

30. Detecting Anomalies

KQL includes capabilities for time-series analysis and anomaly detection.

For example, a time series can be created using make-series.

KQL also provides functions and operators that can be used for anomaly detection and forecasting. Azure Monitor documents these capabilities for analyzing telemetry without having to export the data to an external machine-learning system.

For AI-200, understand the general purpose:

Use KQL time-series capabilities to identify unusual behavior in application or infrastructure telemetry.

31. Example: Detecting an Increase in Errors

A practical investigation might proceed in stages.

Step 1 – Determine whether errors are occurring

			
AppRequests
| where TimeGenerated > ago(24h)
| where Success == false
| summarize count()

Step 2 – Determine when they occurred

			
AppRequests
| where TimeGenerated > ago(24h)
| where Success == false
| summarize count() by bin(TimeGenerated, 15m)
| render timechart

		

Step 3 – Identify the failing endpoints

			
AppRequests
| where TimeGenerated > ago(24h)
| where Success == false
| summarize count() by Name
| order by count_ desc

		

Step 4 – Identify the status codes

			
AppRequests
| where TimeGenerated > ago(24h)
| where Success == false
| summarize count() by ResultCode
| order by count_ desc

		

This demonstrates a very useful troubleshooting methodology:

Detect → locate → categorize → investigate

32. Example: Investigating a Slow AI Application

Suppose an AI application suddenly becomes slow.

You could start with:

			
AppRequests
| where TimeGenerated > ago(1h)
| summarize
    AverageDuration = avg(DurationMs),
    MaximumDuration = max(DurationMs),
    Requests = count()

		

Then identify slow endpoints:

			
AppRequests
| where TimeGenerated > ago(1h)
| summarize
    AverageDuration = avg(DurationMs)
    by Name
| order by AverageDuration desc

		

Then examine individual slow requests:

			
AppRequests
| where TimeGenerated > ago(1h)
| where DurationMs > 5000
| project TimeGenerated, Name, DurationMs, ResultCode
| order by DurationMs desc

		

Finally, investigate dependencies associated with those requests.

This type of correlation is especially useful in distributed AI applications where an API might call several backend services.

33. Query Performance and Cost

KQL can process very large datasets, but query design still matters.

Good practices include:

Filter early

Prefer:

			
AppRequests
| where TimeGenerated > ago(1h)
| where Success == false
| summarize count() by Name

rather than processing an unnecessarily large historical dataset.

Select only required columns

Use:

| project TimeGenerated, Name, ResultCode

when you don’t need every column.

Use appropriate time ranges

Don’t query seven days of telemetry when the incident occurred five minutes ago.

Aggregate when appropriate

Instead of returning millions of individual records:

| summarize count() by Name

may provide the information you actually need.

This is especially relevant when working with billable data and large workspaces.

34. Querying Basic and Auxiliary Logs

Azure Monitor supports different table plans, including Analytics, Basic, and Auxiliary tables.

There are additional query limitations for Basic and Auxiliary tables. For example, certain multi-table operations aren’t supported, and Basic table queries are limited to a single table in relevant scenarios. Query costs can also depend on the amount of data scanned.

For the exam, understand that not every KQL query capability is necessarily available against every type of Azure Monitor log table.

35. Running KQL Programmatically

KQL isn’t limited to the Azure portal.

The Azure Monitor Logs Query API allows applications and automation tools to execute KQL queries against a Log Analytics workspace. The API accepts a KQL query and optional time range and returns the query results.

For example, conceptually:

			
{
    "query": "AzureActivity | summarize count() by Category",
    "timespan": "PT12H"
}

This makes it possible to build custom monitoring applications and automation around Azure Monitor data.

36. Important KQL Operators for AI-200

You should be familiar with at least the following:

Operator/function	Purpose
`where`	Filter records
`project`	Select columns
`project-away`	Remove columns
`extend`	Add calculated columns
`summarize`	Aggregate data
`order by`	Sort records
`take`	Limit records
`distinct`	Return unique values
`join`	Combine related datasets
`union`	Combine datasets
`render`	Visualize results
`count()`	Count records
`countif()`	Conditional count
`avg()`	Average
`sum()`	Sum
`min()`	Minimum
`max()`	Maximum
`dcount()`	Approximate distinct count
`bin()`	Group values into intervals
`ago()`	Calculate a relative time
`isnull()`	Test for null
`isnotnull()`	Test for non-null
`contains`	Search for text
`has`	Search for a term
`in`	Match against a list

37. KQL Exam Tips

For AI-200, focus on understanding why an operator is used rather than simply memorizing syntax.

Remember:

where = filter

| where Status == "Failed"

project = choose columns

| project TimeGenerated, Status

extend = calculate/add columns

| extend DurationSeconds = DurationMs / 1000

summarize = aggregate

| summarize count() by Status

order by = sort

| order by DurationMs desc

take = limit rows

| take 10

bin = group into intervals

| summarize count() by bin(TimeGenerated, 5m)

render = visualize

| render timechart

A particularly important pattern is:

			
TABLE
→ where
→ extend/project
→ summarize
→ order
→ render

		

38. Putting It All Together

Consider this query:

			
AppRequests
| where TimeGenerated > ago(24h)
| where Success == false
| summarize
    FailedRequests = count()
    by bin(TimeGenerated, 30m), Name
| order by TimeGenerated asc
| render timechart

		

This query:

Starts with AppRequests.
Limits the analysis to the last 24 hours.
Keeps unsuccessful requests.
Groups failures into 30-minute intervals.
Separates them by endpoint name.
Sorts the results chronologically.
Creates a time-series visualization.

Understanding how each stage transforms the data is exactly the type of reasoning that can help with AI-200 scenario-based questions.

39. Key Takeaways

For the “Write KQL queries to analyze logs and metrics” topic, make sure you can:

Explain what KQL is.
Explain the role of Log Analytics and Azure Monitor Logs.
Understand KQL’s pipeline syntax.
Filter records with where.
Select columns with project.
Create calculated values with extend.
Aggregate records with summarize.
Group data with by.
Sort results with order by.
Limit results with take.
Find unique values with distinct.
Filter by relative time using ago().
Group time-series data using bin().
Calculate counts, averages, sums, minimums, and maximums.
Use conditional aggregations such as countif().
Analyze errors and exceptions.
Analyze request duration and performance.
Correlate data using join when appropriate.
Combine datasets using union.
Create visualizations using render.
Understand the relationship between logs and metrics.
Understand how diagnostic settings make resource logs available for querying.
Recognize that Azure Monitor has some KQL differences and limitations compared with Azure Data Explorer.
Understand how KQL can be executed programmatically through the Azure Monitor Logs Query API.

Practice Exam Questions

Question 1

An AI-powered web application is experiencing intermittent HTTP 500 errors. You need to determine how many failed requests occurred during each 10-minute interval during the last hour.

Which KQL query should you use?

			
AppRequests
| where TimeGenerated > ago(1h)
| where ResultCode == 500
| summarize count() by bin(TimeGenerated, 10m)

			
AppRequests
| where TimeGenerated > ago(10m)
| summarize count() by ResultCode

			
AppRequests
| summarize count() by TimeGenerated
| where ResultCode == 500

			
AppRequests
| project ResultCode
| take 10

Answer: A

Explanation

where filters the data to the desired time period and status code. summarize count() counts the records, while bin(TimeGenerated, 10m) groups the results into 10-minute intervals.

Question 2

You need to identify the 20 slowest API requests made during the last two hours.

Which query should you use?

			
AppRequests
| summarize avg(DurationMs) by Name
| take 20

			
AppRequests
| where TimeGenerated > ago(2h)
| order by DurationMs desc
| take 20

			
AppRequests
| where DurationMs > 20
| summarize count()

			
AppRequests
| project DurationMs
| order by DurationMs asc

Answer: B

Explanation

The query first restricts the data to the last two hours, sorts individual requests by duration in descending order, and then returns the first 20 records. This identifies the slowest individual requests.

Question 3

An application team wants to calculate the average request duration for each API endpoint.

Which operator should primarily be used?

A. project

B. extend

C. summarize

D. take

Answer: C

Explanation

summarize is the KQL operator used for aggregation. For example:

			
AppRequests
| summarize AverageDuration = avg(DurationMs) by Name

project selects columns, extend creates calculated columns, and take limits the number of returned records.

Question 4

You need to add a column called DurationSeconds containing the request duration converted from milliseconds to seconds.

Which KQL statement should you use?

| summarize DurationSeconds = DurationMs / 1000

| project DurationSeconds = DurationMs / 1000

| extend DurationSeconds = DurationMs / 1000.0

| where DurationSeconds = DurationMs / 1000

Answer: C

Explanation

extend adds a calculated column while retaining the existing columns. Using 1000.0 also ensures the calculation is performed as a floating-point calculation.

Question 5

An administrator wants to identify the number of failed operations grouped by operation name.

Which query is most appropriate?

			
AzureActivity
| summarize FailedOperations = countif(ActivityStatus == "Failed")
    by OperationNameValue

			
AzureActivity
| where OperationNameValue == "Failed"
| project ActivityStatus

			
AzureActivity
| order by ActivityStatus
| take 10

			
AzureActivity
| distinct OperationNameValue

Answer: A

Explanation

countif() counts records that satisfy a condition, and by OperationNameValue groups the counts by operation. This directly answers the requirement.

Question 6

You are investigating application traffic and want to see request counts in five-minute intervals displayed as a time-series chart.

Which query should you use?

			
AppRequests
| take 5
| render timechart

			
AppRequests
| summarize count() by TimeGenerated
| render piechart

			
AppRequests
| summarize count() by bin(TimeGenerated, 5m)
| render timechart

			
AppRequests
| project TimeGenerated
| render timechart

Answer: C

Explanation

bin() groups timestamps into five-minute intervals, summarize count() counts requests in each interval, and render timechart produces the time-series visualization.

Question 7

You want to return only the TimeGenerated, Name, and ResultCode columns from a request table.

Which operator should you use?

A. extend

B. project

C. summarize

D. distinct

Answer: B

Explanation

project controls which columns are returned.

For example:

			
AppRequests
| project TimeGenerated, Name, ResultCode

Question 8

You need to investigate exceptions generated during the previous 30 minutes and display only records where the exception message contains the word “timeout.”

Which query is appropriate?

			
AppExceptions
| where TimeGenerated > ago(30m)
| where OuterMessage contains "timeout"

			
AppExceptions
| summarize count() by OuterMessage

			
AppExceptions
| project TimeGenerated
| take 30

			
AppExceptions
| order by OuterMessage

Answer: A

Explanation

The first where restricts the data to the previous 30 minutes. The second filters exception messages containing "timeout".

Question 9

An application team wants to determine the approximate number of distinct users who generated requests during each hour.

Which query should you use?

			
AppRequests
| summarize count(UserId) by bin(TimeGenerated, 1h)

			
AppRequests
| summarize distinct(UserId) by bin(TimeGenerated, 1h)

			
AppRequests
| summarize dcount(UserId) by bin(TimeGenerated, 1h)

			
AppRequests
| distinct UserId
| render timechart

Answer: C

Explanation

dcount() provides an approximate distinct count. Combining it with bin(TimeGenerated, 1h) produces an approximate unique-user count for each hour.

Question 10

An application has experienced a sudden increase in failures. You want to determine whether the failures are concentrated in particular API endpoints and identify the number of failures per endpoint.

Which query is most appropriate?

			
AppRequests
| take 10

			
AppRequests
| project Name, Success

			
AppRequests
| summarize avg(DurationMs) by Name

			
AppRequests
| where Success == false
| summarize FailureCount = count() by Name
| order by FailureCount desc

Answer: D

Explanation

The query filters for failed requests, groups those failures by endpoint name, counts the failures, and sorts the endpoints from the highest failure count to the lowest. This is an effective way to identify which API endpoints are contributing most to the incident.

Final Exam Perspective

The most important thing to remember for this AI-200 topic is that KQL is fundamentally about turning large volumes of telemetry into actionable information.

A scenario might give you thousands or millions of log records and ask you to determine:

What failed?

Use where.

How many failed?

Use summarize count() or countif().

Where did the failures occur?

Use summarize ... by.

When did they occur?

Use bin() with a timestamp.

Which endpoint is the slowest?

Use summarize avg() or inspect individual records with order by.

What happened during the last hour?

Use ago(1h).

What does the trend look like?

Use time-based summarize and render timechart.

What information do I actually need to see?

Use project.

If you can recognize these patterns quickly, you will be well prepared for the KQL-related scenario questions in AI-200. Azure Monitor’s KQL capabilities are specifically designed for exploring logs, transforming and aggregating telemetry, identifying patterns and anomalies, troubleshooting applications, and supporting alerts and reports.

Go to the AI-200 Exam Prep Hub main page

DP-700, Microsoft Certification, Microsoft Fabric June 3, 2026

Process data by using KQL (DP-700 Exam Prep)

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:

Function	Purpose
sum()	Total values
avg()	Average
count()	Row count
min()	Minimum value
max()	Maximum value
dcount()	Distinct count

Grouping Data

Grouping is accomplished with summarize and a grouping column.

Example:

			
Sales
| summarize TotalSales=sum(Amount)
    by Region

Output:

Region	TotalSales
East	250000
West	300000

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:

Function	Purpose
tostring()	Convert to string
toint()	Convert to integer
todouble()	Convert to decimal
parse_json()	Parse JSON object

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.

Requirement	KQL	Spark Structured Streaming
Real-time analytics	Excellent	Good
Data science workloads	Limited	Excellent
Machine learning	Limited	Excellent
Interactive querying	Excellent	Moderate
Time-series analysis	Excellent	Good
Large-scale transformations	Moderate	Excellent
SQL-like querying	Excellent	Moderate

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

Feature	KQL	T-SQL
Streaming analytics	Excellent	Limited
Time-series analysis	Excellent	Moderate
OLTP operations	Poor	Excellent
Real-time dashboards	Excellent	Moderate
Log analytics	Excellent	Poor

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.

DP-700, Microsoft Certification, Microsoft Fabric June 3, 2026June 3, 2026

Identify and resolve Eventhouse errors (DP-700 Exam Prep)

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:
Monitor and optimize an analytics solution (30–35%)
   --> Identify and resolve errors
      --> Identify and resolve Eventhouse errors

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

Eventhouses are a foundational component of Microsoft Fabric Real-Time Intelligence. They provide highly scalable storage and querying capabilities for streaming, telemetry, log, IoT, and event-driven data. Eventhouses leverage Kusto technology and are optimized for high-ingestion rates, low-latency analytics, and real-time querying using Kusto Query Language (KQL).

Because Eventhouses are frequently used in mission-critical real-time analytics solutions, data engineers must be able to identify, troubleshoot, and resolve ingestion, querying, schema, connectivity, and performance issues.

For the DP-700 exam, understanding how to diagnose Eventhouse failures and interpret Eventhouse-related errors is an important skill.

Understanding Eventhouse Architecture

An Eventhouse serves as a logical container for one or more KQL databases.

A typical architecture includes:

Event sources
- Eventstreams
- Azure Event Hubs
- IoT devices
- Application telemetry
Data ingestion layer
- Streaming ingestion
- Eventstream destinations
- Connectors
KQL database
- Tables
- Functions
- Materialized views
Query layer
- KQL queries
- Dashboards
- Power BI
- Real-Time Intelligence workloads

Errors can occur anywhere within this architecture.

Common Categories of Eventhouse Errors

Most Eventhouse issues fall into the following categories:

Data ingestion failures
Query failures
Schema-related issues
Permission errors
Connectivity problems
Data latency issues
Resource or performance bottlenecks
Materialized view failures

Understanding which category an error belongs to helps accelerate troubleshooting.

Identifying Ingestion Errors

Ingestion problems are among the most common Eventhouse issues.

Symptoms include:

Missing records
Delayed records
Empty tables
Partial data loads

Common causes include:

Misconfigured Eventstream destination
Incorrect source mapping
Schema mismatches
Source connectivity issues
Permission problems

Example symptoms:

No records arriving in target table

Ingestion failed

Monitoring Ingestion Health

Fabric provides several methods for monitoring Eventhouse ingestion.

Important metrics include:

Records ingested
Ingestion rate
Failed ingestion count
Latency
Throughput

When troubleshooting ingestion:

Verify source events are arriving.
Confirm Eventstream is healthy.
Validate destination configuration.
Review ingestion metrics.
Check KQL database tables.

A common exam scenario involves determining where the ingestion pipeline is failing.

Schema Mapping Errors

Eventhouse ingestion often relies on schema mappings.

If incoming data does not match expected column definitions, ingestion may fail.

Example:

Expected schema:

Column	Type
DeviceId	string
Temperature	real

Incoming event:

			
{
   "DeviceId":"A100",
   "Temperature":"High"
}

Problem:

Temperature expected numeric value
Incoming value is text

Possible result:

Type conversion failure

Resolution:

Correct source format
Modify mapping
Adjust table schema

Query Errors

KQL queries frequently generate troubleshooting scenarios.

Common causes include:

Invalid syntax
Missing tables
Missing columns
Incorrect joins
Data type mismatches

Example:

			
Sales
| where Region == "West"
| summarize count() by Product

If Sales does not exist:

Table not found

Resolution:

Verify table name
Verify database context
Check permissions

Resolving KQL Syntax Errors

KQL syntax issues often produce immediate query failures.

Examples:

			
Sales
| where Region = "West"

Potential issue:

Incorrect operator usage

Error messages often identify:

Line number
Character position
Invalid operator

Resolution:

Review query syntax
Validate KQL operators
Test query incrementally

Permission and Access Errors

Users must have appropriate access to:

Workspace
Eventhouse
KQL database
Tables

Common errors:

Access denied

Unauthorized

Causes:

Missing workspace role
Missing Eventhouse permissions
Cross-workspace restrictions

Resolution:

Verify security assignments
Confirm user roles
Review database permissions

Data Latency Issues

A common real-time analytics problem is delayed data.

Symptoms:

Data eventually arrives
Dashboards appear stale
Queries return incomplete results

Potential causes:

Eventstream bottlenecks
Source delays
Heavy ingestion workloads
Query acceleration delays

Troubleshooting steps:

Check source event generation.
Verify Eventstream throughput.
Review ingestion metrics.
Validate Eventhouse health.

Identifying Missing Data

Sometimes ingestion succeeds but data appears missing.

Possible causes:

Filtering

KQL query filters may exclude rows.

Example:

			
Telemetry
| where DeviceId == "A100"

Data for other devices will not appear.

Wrong Time Range

Real-time queries often use time filters.

Example:

			
Telemetry
| where Timestamp > ago(1h)

Older data is intentionally excluded.

Wrong Database Context

Queries may execute against the wrong database.

Always verify:

Eventhouse
Database
Table

Materialized View Errors

Materialized views are commonly used to improve query performance.

Failures may occur because of:

Invalid source schema
Query changes
Missing source tables
Unsupported operations

Symptoms:

Stale results
Missing aggregates
Refresh failures

Resolution:

Validate source tables
Review materialized view definition
Check refresh status

Performance-Related Errors

Queries can become slow when:

Large tables are scanned
Filters are inefficient
Excessive joins occur
Aggregations process massive datasets

Example:

			
LargeTelemetryTable
| summarize count() by DeviceId

If billions of records exist, query performance may degrade.

Optimization techniques:

Filter early
Use time-based filtering
Leverage materialized views
Reduce unnecessary joins

Troubleshooting Eventstream-to-Eventhouse Issues

One of the most common DP-700 scenarios involves Eventstream ingestion.

Troubleshooting checklist:

Verify Event Source

Confirm events are being generated.

Verify Eventstream

Check:

Event counts
Errors
Throughput

Verify Destination

Confirm:

Correct Eventhouse selected
Correct KQL database selected
Correct table selected

Verify Table Schema

Ensure incoming events match expected schema.

Verify Permissions

Confirm write access exists.

Monitoring Tools for Eventhouse Troubleshooting

Fabric provides several tools that support Eventhouse monitoring.

Eventstream Monitoring

Used to validate:

Incoming events
Throughput
Failures

KQL Query Diagnostics

Used to:

Identify syntax errors
Analyze query performance
Investigate execution issues

Real-Time Intelligence Monitoring

Provides visibility into:

Data freshness
Query activity
Resource utilization

Workspace Monitoring

Helps identify:

Capacity constraints
Item failures
Operational issues

Best Practices to Prevent Eventhouse Errors

Validate Schemas Early

Prevent ingestion failures by validating source data structures.

Use Strong Naming Standards

Consistent table naming reduces query errors.

Monitor Ingestion Continuously

Track:

Ingestion rate
Failed records
Data freshness

Test KQL Queries Incrementally

Build queries step-by-step to identify errors quickly.

Implement Alerting

Configure alerts for:

Failed ingestion
Latency increases
Resource constraints

Use Materialized Views Appropriately

Improve performance for frequently executed aggregations.

Exam Tips

For the DP-700 exam, remember:

Ingestion failures are commonly caused by schema mismatches, mapping errors, or destination misconfigurations.
“Table not found” errors typically indicate missing tables, incorrect database context, or permission issues.
Data latency issues often originate upstream in Eventstreams or source systems.
Materialized view issues may result in stale or incomplete query results.
KQL syntax errors frequently identify line and character positions.
Monitoring ingestion metrics is a key troubleshooting technique.
Eventstream-to-Eventhouse configurations are common troubleshooting scenarios.
Permission issues often generate “Access Denied” or “Unauthorized” errors.
Query optimization techniques improve Eventhouse performance and reduce troubleshooting incidents.

Practice Exam Questions

Question 1

A data engineer notices that an Eventhouse table contains no records even though events are being generated by the source application.

What should be investigated FIRST?

A. Eventstream ingestion path and destination configuration

B. Semantic model refresh history

C. Power BI report filters

D. Lakehouse partition strategy

Correct Answer: A

Explanation:
If source events exist but no records appear in the Eventhouse, the most likely failure point is the ingestion path, Eventstream configuration, or destination mapping.

Question 2

A KQL query returns the following error:

Table 'SalesData' not found

What is the MOST likely cause?

A. Insufficient Spark memory

B. Incorrect database context or missing table

C. Eventstream latency

D. Notebook timeout

Correct Answer: B

Explanation:
This error typically occurs when the table does not exist, the wrong database is selected, or the user lacks access.

Question 3

Which issue is MOST likely to cause ingestion failures during Eventhouse data loading?

A. Excessive dashboard visualizations

B. Semantic model relationships

C. Schema mismatch between incoming events and destination table

D. Workspace naming conventions

Correct Answer: C

Explanation:
Schema mismatches are among the most common causes of ingestion failures because incoming data cannot be mapped correctly to destination columns.

Question 4

A user receives an “Unauthorized” message while querying an Eventhouse.

What is the MOST likely cause?

A. Invalid KQL syntax

B. Missing workspace or database permissions

C. Eventstream buffering

D. Query acceleration failure

Correct Answer: B

Explanation:
Unauthorized errors almost always indicate insufficient access rights to the Eventhouse, database, or underlying resources.

Question 5

Which monitoring metric is MOST useful for identifying ingestion problems?

A. Power BI bookmark usage

B. Semantic model storage size

C. Dashboard theme configuration

D. Failed ingestion count

Correct Answer: D

Explanation:
The failed ingestion count directly indicates records or batches that could not be successfully loaded.

Question 6

A query returns incomplete results because older records are not displayed.

Which KQL statement is MOST likely causing this behavior?

| project DeviceId

| extend DeviceName = tostring(DeviceId)

| where Timestamp > ago(1h)

| summarize count()

Correct Answer: C

Explanation:
Time filters such as ago(1h) intentionally exclude older records.

Question 7

What is a common symptom of a failed materialized view?

A. Increased semantic model refresh speed

B. Stale or incomplete aggregated results

C. Missing notebook parameters

D. Failed Spark pool creation

Correct Answer: B

Explanation:
Materialized view failures often result in outdated or incomplete aggregated data.

Question 8

Which troubleshooting action is MOST appropriate when diagnosing a KQL syntax error?

A. Increase workspace capacity

B. Delete the Eventhouse

C. Restart the semantic model

D. Review the line number and character position reported in the error

Correct Answer: D

Explanation:
KQL syntax errors typically provide exact locations that help identify the problem quickly.

Question 9

A real-time dashboard is showing data that is several minutes behind expected values.

What should be investigated FIRST?

A. Data freshness, ingestion latency, and Eventstream throughput

B. Power BI color themes

C. Workspace description fields

D. Notebook markdown cells

Correct Answer: A

Explanation:
Delayed dashboards are often caused by ingestion latency, source delays, or Eventstream bottlenecks.

Question 10

Which approach is MOST effective for preventing future Eventhouse ingestion errors?

A. Disable schema validation

B. Reduce dashboard refresh frequency

C. Validate source schemas and mappings before deployment

D. Remove monitoring metrics

Correct Answer: C

Explanation:
Proactive schema validation helps identify compatibility issues before data reaches production Eventhouse environments, significantly reducing ingestion failures.

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

DP-700, Python, SQL June 3, 2026

Transform data by using PySpark, SQL, and KQL (DP-700 Exam Prep)

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 batch data
      --> Transform data by using PySpark, SQL, and 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

One of the most important skills for the DP-700: Implementing Data Engineering Solutions Using Microsoft Fabric certification exam is knowing how to transform data using the appropriate technology. Microsoft Fabric provides multiple transformation engines, each optimized for specific workloads:

PySpark for large-scale distributed data engineering and advanced transformations
SQL for relational data manipulation, warehousing, and analytics
KQL (Kusto Query Language) for high-volume log, telemetry, event, and time-series data analysis

A successful Fabric Data Engineer must understand not only how each technology works, but also when to choose one over another.

Understanding the Transformation Options in Microsoft Fabric

Microsoft Fabric supports several data processing experiences:

Technology	Primary Use Case	Common Fabric Components
PySpark	Big data processing and engineering	Lakehouse, Notebooks
SQL	Relational transformations and analytics	Warehouse, SQL Endpoint
KQL	Streaming, telemetry, logs, event analytics	Eventhouse, Real-Time Intelligence

While all three can transform data, they are designed for different scenarios.

Transforming Data with PySpark

What is PySpark?

PySpark is the Python API for Apache Spark.

Spark is a distributed processing engine that allows data engineers to process extremely large datasets across multiple nodes simultaneously.

Within Microsoft Fabric, PySpark is typically used in:

Notebooks
Lakehouses
Spark Job Definitions

When to Use PySpark

PySpark is ideal when:

Working with large-scale datasets
Performing complex transformations
Processing semi-structured data
Building data engineering pipelines
Performing machine learning preparation
Handling Delta Lake tables

Examples include:

Cleaning raw data
Parsing JSON files
Aggregating billions of records
Creating dimensional model tables
Performing data quality checks

Reading Data with PySpark

Example:

df = spark.read.format("delta").load("Tables/Sales")

Filtering Data

filtered_df = df.filter(df.Amount > 1000)

Creating New Columns

			
from pyspark.sql.functions import col
new_df = df.withColumn(
    "TaxAmount",
    col("Amount") * 0.07
)

		

Aggregating Data

			
from pyspark.sql.functions import sum
summary_df = (
    df.groupBy("Region")
      .agg(sum("Amount").alias("TotalSales"))
)

		

Writing Results

summary_df.write.mode("overwrite").saveAsTable("SalesSummary")

PySpark Advantages

Scalability

Handles terabytes and petabytes of data.

Distributed Processing

Automatically parallelizes workloads.

Flexibility

Supports:

Structured data
Semi-structured data
Unstructured data

Data Engineering Focus

Excellent for ETL and ELT processes.

PySpark Limitations

More complex than SQL
Requires programming skills
Less familiar to business analysts
Higher resource consumption for small workloads

Transforming Data with SQL

What is SQL in Fabric?

SQL remains one of the most commonly used languages in Fabric.

You can use SQL within:

Fabric Data Warehouse
Lakehouse SQL Endpoint
SQL Query Editor
Stored Procedures
Data Pipelines

When to Use SQL

SQL is ideal for:

Relational transformations
Data warehouse development
Reporting datasets
Aggregations
Joins
Dimensional modeling

Examples:

Creating fact tables
Loading dimensions
Building reporting views
Data validation

Filtering Records

			
SELECT *
FROM Sales
WHERE Amount > 1000;

Aggregations

			
SELECT
    Region,
    SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Region;

		

Joining Tables

			
SELECT
    s.SaleID,
    c.CustomerName
FROM Sales s
INNER JOIN Customer c
    ON s.CustomerID = c.CustomerID;

		

Creating Transformation Tables

			
CREATE TABLE SalesSummary AS
SELECT
    Region,
    SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Region;

		

SQL Advantages

Familiarity

Most data professionals know SQL.

Readability

Easy to understand and maintain.

Relational Optimization

Optimized for joins and aggregations.

Warehousing Support

Ideal for star schemas and dimensional models.

SQL Limitations

Less effective for complex data engineering workflows
Not ideal for large-scale semi-structured data processing
Limited flexibility compared to PySpark

Transforming Data with KQL

What is KQL?

Kusto Query Language (KQL) is a read-optimized query language designed for:

Telemetry
Log analytics
Event processing
Streaming data
Time-series analysis

KQL is commonly used in:

Eventhouse
Real-Time Intelligence
KQL Databases

When to Use KQL

Use KQL when working with:

Sensor data
IoT events
Application logs
Security monitoring
Streaming datasets
Time-series analytics

Examples:

Monitoring manufacturing equipment
Detecting anomalies
Security event analysis
Operational dashboards

Filtering Data

			
Events
| where Temperature > 100

Summarization

			
Events
| summarize AvgTemp = avg(Temperature)
    by DeviceID

Time-Series Analysis

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

Detecting Trends

			
Events
| make-series AvgTemp=avg(Temperature)
    on Timestamp
    step 1h

KQL Advantages

High Performance

Optimized for large event datasets.

Time-Series Analytics

Excellent for temporal analysis.

Streaming Support

Designed for real-time workloads.

Fast Query Execution

Ideal for operational dashboards.

KQL Limitations

Not intended for traditional data warehousing
Less suitable for dimensional modeling
Not commonly used for batch ETL

Comparing PySpark, SQL, and KQL

Requirement	Best Choice
Large-scale ETL	PySpark
Data warehouse transformations	SQL
Star schema creation	SQL
Streaming analytics	KQL
Time-series analysis	KQL
Semi-structured JSON processing	PySpark
Machine learning preparation	PySpark
Business reporting datasets	SQL
Eventhouse analytics	KQL
Massive Delta Lake processing	PySpark

Choosing the Right Transformation Tool

Choose PySpark When

Processing very large datasets
Working with Data Lake data
Building engineering pipelines
Handling JSON or Parquet files
Performing advanced transformations

Choose SQL When

Building warehouses
Creating dimensional models
Developing reporting datasets
Performing relational transformations
Creating views and stored procedures

Choose KQL When

Working with event streams
Analyzing telemetry
Investigating logs
Performing time-series analysis
Monitoring operational systems

Exam Tips

Know the Primary Use Cases

A common DP-700 exam question asks which technology is most appropriate for a scenario.

Remember:

PySpark = Big Data Engineering
SQL = Relational Analytics and Warehousing
KQL = Real-Time and Time-Series Analytics

Understand Fabric Components

Know where each technology is primarily used:

Technology	Fabric Experience
PySpark	Lakehouse, Notebook
SQL	Warehouse, SQL Endpoint
KQL	Eventhouse

Focus on Scenario-Based Questions

The exam frequently describes a business requirement and asks which technology should be used.

For example:

IoT sensors → KQL
Warehouse dimension tables → SQL
Processing billions of JSON records → PySpark

Practice Exam Questions

Question 1

A data engineer must transform 20 TB of semi-structured JSON data stored in OneLake. Which technology is the best choice?

A. SQL

B. PySpark

C. KQL

D. Power Query

Answer: B

Explanation: PySpark is designed for distributed processing of massive datasets and handles semi-structured formats such as JSON efficiently.

Question 2

A Fabric solution requires creation of a star schema consisting of fact and dimension tables. Which technology is most appropriate?

A. SQL

B. KQL

C. Power BI DAX

D. Data Activator

Answer: A

Explanation: SQL is optimized for relational transformations and dimensional modeling commonly used in data warehouses.

Question 3

A company wants to analyze millions of IoT events arriving continuously from factory equipment. Which technology should be used?

A. KQL

B. Power Query

C. SQL

D. Excel

Answer: A

Explanation: KQL is designed specifically for high-volume event, telemetry, and time-series analysis workloads.

Question 4

Which Fabric component is most closely associated with KQL transformations?

A. Warehouse

B. Notebook

C. SQL Endpoint

D. Eventhouse

Answer: D

Explanation: Eventhouse is the primary Fabric experience for KQL-based analytics and real-time intelligence workloads.

Question 5

A data engineer needs to process Delta Lake tables using distributed compute. Which technology should be selected?

A. KQL

B. SQL

C. PySpark

D. Power BI

Answer: C

Explanation: PySpark integrates directly with Delta Lake and supports scalable distributed processing.

Question 6

Which language is specifically optimized for time-series analysis?

A. SQL

B. KQL

C. Python

D. DAX

Answer: B

Explanation: KQL includes built-in capabilities for temporal aggregation, anomaly detection, and time-series analytics.

Question 7

A Fabric Warehouse team needs to build a reusable transformation layer consisting of joins, aggregations, and views. Which technology should they use?

A. SQL

B. KQL

C. Dataflows Gen2

D. Spark ML

Answer: A

Explanation: SQL is the preferred language for relational transformations and warehouse development.

Question 8

Which technology is generally the best choice for preparing large datasets for machine learning?

A. KQL

B. SQL

C. DAX

D. PySpark

Answer: D

Explanation: PySpark provides scalable data preparation capabilities and integrates well with machine learning workflows.

Question 9

An engineer needs to summarize application log events by hour and identify usage trends. Which technology is most appropriate?

A. PySpark

B. Power Query

C. KQL

D. SQL

Answer: C

Explanation: KQL excels at log analytics, event monitoring, and time-based aggregations.

Question 10

A team needs a transformation language that is familiar to most database developers and optimized for relational joins. Which should they choose?

A. PySpark

B. KQL

C. Power Query

D. SQL

Answer: D

Explanation: SQL remains the standard language for relational querying, joins, aggregations, and warehouse transformations.

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

DP-700, Microsoft Certification, Microsoft Fabric June 3, 2026

Choose Between Dataflows Gen2, Notebooks, KQL, and T-SQL for data transformation (DP-700 Exam Prep)

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 batch data
      --> Choose Between Dataflows Gen2, Notebooks, KQL, and T-SQL for data transformation

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

Microsoft Fabric provides multiple technologies for transforming data. One of the most common challenges for a Data Engineer is determining which transformation tool is best suited for a specific business requirement.

The DP-700 exam frequently tests your ability to select the appropriate transformation technology based on:

Data volume
Data complexity
Required programming skills
Data source type
Performance requirements
Real-time versus batch processing needs
User expertise
Maintainability

The four most important transformation technologies covered in the exam are:

Dataflows Gen2
Notebooks
KQL
T-SQL

Although all four can transform data, they are optimized for different workloads and use cases.

Understanding their strengths, limitations, and ideal scenarios is critical for success on the DP-700 exam.

Overview of Transformation Technologies

Technology	Primary Purpose	Best For
Dataflows Gen2	Low-code ETL	Business-friendly transformations
Notebooks	Advanced engineering and Spark processing	Large-scale data engineering
T-SQL	Relational transformations	Warehouses and SQL workloads
KQL	Real-time analytics and telemetry processing	Logs and streaming data

Dataflows Gen2

What Are Dataflows Gen2?

Dataflows Gen2 are low-code data transformation tools within Microsoft Fabric that use Power Query.

They allow users to:

Connect to data sources
Clean data
Transform data
Load data into Fabric destinations

without writing significant amounts of code.

Transformation Engine

Dataflows Gen2 use:

Power Query
M Language (behind the scenes)

Most transformations are performed through a graphical interface.

Typical Transformations

Examples include:

Renaming columns
Removing duplicates
Filtering rows
Merging datasets
Splitting columns
Data type conversions
Calculated columns

When to Use Dataflows Gen2

Choose Dataflows Gen2 when:

Low-code development is desired
Data volumes are moderate
Business analysts participate in development
Transformations are relatively straightforward
Self-service data preparation is required

Examples:

Preparing Excel data
Cleaning CSV files
Combining multiple business datasets
Standard ETL processes

Advantages

Low-Code Experience

Minimal coding required.

Large Connector Library

Supports numerous source systems.

Easy Maintenance

Visual transformation steps are easier to understand.

Integration with Fabric

Loads directly into:

Lakehouses
Warehouses
Other Fabric destinations

Limitations

Less Flexible

Complex logic may become difficult.

Not Ideal for Very Large Data Volumes

Spark-based solutions often scale better.

Limited Advanced Programming

Compared to notebooks.

Notebooks

What Are Notebooks?

Notebooks are code-based development environments that support:

PySpark
Python
Scala
Spark SQL
R

within Microsoft Fabric.

Transformation Engine

Notebooks execute on Spark clusters.

This enables:

Distributed processing
Parallel execution
Large-scale transformations

Typical Transformations

Examples:

Complex joins
Data enrichment
Machine learning preparation
Feature engineering
Data quality validation
Custom business logic

When to Use Notebooks

Choose notebooks when:

Large data volumes exist
Spark processing is required
Advanced transformations are needed
Custom programming is necessary
Machine learning integration is planned

Examples:

Processing billions of records
Data science workflows
Medallion architecture pipelines
Complex transformations

Advantages

Massive Scalability

Handles large datasets efficiently.

Flexible Programming

Supports multiple languages.

Machine Learning Integration

Works with Spark ML libraries.

Advanced Data Engineering

Ideal for enterprise-scale pipelines.

Limitations

Requires Coding Skills

Less accessible for business users.

More Complex Development

Compared to Dataflows Gen2.

T-SQL

What Is T-SQL?

T-SQL (Transact-SQL) is Microsoft’s extension of SQL.

Fabric Warehouses and SQL endpoints support T-SQL for:

Querying
Transforming
Managing relational data

Transformation Techniques

Common operations include:

			
SELECT
JOIN
GROUP BY
CASE
CTE
MERGE
WINDOW FUNCTIONS

		

When to Use T-SQL

Choose T-SQL when:

Data resides in a Warehouse
Relational transformations are required
SQL expertise already exists
Dimensional models are being built

Examples:

Fact table loading
Dimension updates
Data warehouse ETL
Reporting data preparation

Advantages

Familiar Language

Widely used by data professionals.

Excellent Relational Processing

Optimized for structured data.

Strong Performance

Particularly for warehouse workloads.

Easy Integration

Works naturally with BI tools.

Limitations

Less Suitable for Unstructured Data

Not ideal for files and raw data.

Limited Distributed Processing

Compared to Spark.

KQL

What Is KQL?

Kusto Query Language (KQL) is designed for:

Log analytics
Telemetry analysis
Real-time data processing
Event analytics

KQL is commonly used in:

KQL Databases
Eventhouse
Real-Time Intelligence

Typical Transformations

Examples include:

Filtering events
Aggregations
Pattern detection
Time-series analysis
Stream transformations

When to Use KQL

Choose KQL when:

Working with telemetry data
Processing logs
Analyzing streaming events
Building real-time dashboards

Examples:

Sensor monitoring
Application logs
Security analytics
Operational monitoring

Advantages

Optimized for Time-Series Data

Excellent for event-driven workloads.

Fast Query Performance

Handles large event volumes efficiently.

Real-Time Analytics

Supports low-latency analysis.

Limitations

Not a General ETL Tool

Less suitable for traditional batch ETL.

Not Designed for Dimensional Modeling

Warehouses are generally better for reporting models.

Comparing Transformation Technologies

Requirement	Dataflows Gen2	Notebooks	T-SQL	KQL
Low-Code Development	Excellent	Poor	Moderate	Moderate
Large-Scale Processing	Moderate	Excellent	Good	Excellent
Relational Transformations	Moderate	Good	Excellent	Limited
Streaming Analytics	Limited	Moderate	Poor	Excellent
Machine Learning Support	Poor	Excellent	Poor	Limited
Telemetry Analytics	Poor	Moderate	Poor	Excellent
Business User Friendly	Excellent	Poor	Moderate	Moderate
Advanced Programming	Limited	Excellent	Moderate	Limited

Decision Framework

Choose Dataflows Gen2 When:

Low-code ETL is preferred
Business users are involved
Data volumes are moderate
Transformations are straightforward

Choose Notebooks When:

Spark processing is required
Data volumes are large
Complex transformations exist
Machine learning is involved

Choose T-SQL When:

Working with a Warehouse
Building dimensional models
SQL skills are available
Data is highly structured

Choose KQL When:

Processing logs
Analyzing telemetry
Supporting streaming analytics
Building operational monitoring solutions

Common DP-700 Scenario Questions

Scenario 1

A business analyst needs to combine Excel spreadsheets and remove duplicate rows using a visual interface.

Best choice:

Dataflows Gen2

Scenario 2

A data engineer must transform billions of records stored in a Lakehouse.

Best choice:

Notebook

Scenario 3

A warehouse team must populate fact and dimension tables.

Best choice:

T-SQL

Scenario 4

An operations team analyzes millions of application log events each hour.

Best choice:

KQL

Scenario 5

A machine learning team requires custom Python transformations.

Best choice:

Notebook

Exam Tips

Many DP-700 questions are not asking what can perform a transformation, but what should perform the transformation.

Remember these associations:

Requirement	Best Choice
Visual ETL	Dataflows Gen2
Spark processing	Notebook
Data warehouse transformations	T-SQL
Telemetry and logs	KQL
Machine learning preparation	Notebook
Self-service data preparation	Dataflows Gen2
Streaming analytics	KQL

Practice Exam Questions

Question 1

A business analyst needs to cleanse CSV files using a graphical interface with minimal coding. Which transformation technology should be used?

A. T-SQL

B. Notebook

C. KQL

D. Dataflows Gen2

Answer: D

Explanation

Dataflows Gen2 provide a low-code, visual interface that is ideal for business users and simple ETL processes.

Question 2

A data engineer must process several billion records stored in a Lakehouse using distributed computing.

Which option should be selected?

A. Notebook

B. Dataflows Gen2

C. T-SQL

D. KQL

Answer: A

Explanation

Notebooks leverage Spark for distributed processing and are designed for large-scale data transformations.

Question 3

Which technology is specifically optimized for transforming and analyzing telemetry and log data?

A. Dataflows Gen2

B. Notebook

C. KQL

D. T-SQL

Answer: C

Explanation

KQL is designed for log analytics, telemetry processing, and real-time operational analytics.

Question 4

A team is loading dimension and fact tables within a Fabric Warehouse.

Which transformation technology is most appropriate?

A. Notebook

B. Dataflows Gen2

C. KQL

D. T-SQL

Answer: D

Explanation

T-SQL is the preferred technology for relational transformations in Fabric Warehouses.

Question 5

A company requires machine learning feature engineering using Python libraries.

Which technology should be selected?

A. Notebook

B. Dataflows Gen2

C. T-SQL

D. KQL

Answer: A

Explanation

Notebooks support Python, Spark, and machine learning frameworks, making them ideal for feature engineering.

Question 6

Which technology relies primarily on Power Query transformations?

A. Notebook

B. Dataflows Gen2

C. T-SQL

D. KQL

Answer: B

Explanation

Dataflows Gen2 use Power Query and the M language behind the scenes for data transformations.

Question 7

An operations team needs to perform real-time aggregations on streaming sensor data.

Which option should be used?

A. Dataflows Gen2

B. Notebook

C. KQL

D. T-SQL

Answer: C

Explanation

KQL is optimized for real-time event processing and telemetry analysis.

Question 8

A data engineer needs maximum flexibility to implement custom business logic across multiple data sources.

Which technology is most appropriate?

A. KQL

B. Dataflows Gen2

C. T-SQL

D. Notebook

Answer: D

Explanation

Notebooks provide the highest degree of customization through programming languages such as Python and PySpark.

Question 9

A team already has extensive SQL expertise and needs to transform highly structured relational data in a Warehouse.

Which option is best?

A. Notebook

B. T-SQL

C. Dataflows Gen2

D. KQL

Answer: B

Explanation

T-SQL is optimized for relational transformations and leverages existing SQL skills.

Question 10

Which technology is generally the most business-user-friendly option for creating batch data transformation processes?

A. Notebook

B. KQL

C. T-SQL

D. Dataflows Gen2

Answer: D

Explanation

Dataflows Gen2 provide a visual, low-code experience that is easier for business users and citizen developers than code-based solutions.

DP-700 Exam Summary

When deciding between transformation technologies, focus on the primary workload:

Dataflows Gen2 → Low-code ETL and self-service data preparation
Notebooks → Spark, large-scale processing, advanced engineering, and machine learning
T-SQL → Relational transformations and warehouse development
KQL → Telemetry, logs, time-series analytics, and real-time event processing

A common DP-700 exam strategy is to identify the keywords in the scenario:

Visual interface → Dataflows Gen2
Billions of rows / Spark → Notebook
Warehouse / dimensional model → T-SQL
Logs / telemetry / real-time analytics → KQL

These keywords often point directly to the correct answer.

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

Analytics, Big Data, Business Intelligence (BI) Development, Data Analysis, Data Cleaning, Data Development, Data Integration, Data Integration (ETL), Data Modeling, Data Security, Data Strategy, Data Visualization, Data Warehousing, DP-600, Microsoft Certification, Microsoft Fabric, Performance Tuning December 28, 2025

Select, Filter, and Aggregate Data by Using KQL

This post is a part of the DP-600: Implementing Analytics Solutions Using Microsoft Fabric Exam Prep Hub; and this topic falls under these sections: 
Prepare data 
    --> Query and analyze data 
        --> Select, filter, and aggregate data by using KQL

The Kusto Query Language (KQL) is a read-only request language used for querying large, distributed, event-driven datasets — especially within Eventhouse and Azure Data Explorer–backed workloads in Microsoft Fabric. KQL enables you to select, filter, and aggregate data efficiently in scenarios involving high-velocity data like telemetry, logs, and streaming events.

For the DP-600 exam, you should understand KQL basics and how it supports data exploration and analytical summarization in a real-time analytics context.

KQL Basics

KQL is designed to be expressive and performant for time-series or log-like data. Queries are built as a pipeline of operations, where each operator transforms the data and passes it to the next.

Selecting Data

In KQL, the project operator performs the equivalent of selecting columns:

EventHouseTable
| project Timestamp, Country, EventType, Value

project lets you choose which fields to include
You can rename fields inline: | project Time=Timestamp, Sales=Value

Exam Tip:
Use project early to limit data to relevant columns and reduce processing downstream.

Filtering Data

Filtering in KQL is done using the where operator:

EventHouseTable
| where Country == "USA"

Multiple conditions can be combined with and/or:

| where Value > 100 and EventType == "Purchase"

Filtering early in the pipeline improves performance by reducing the dataset before subsequent transformations.

Aggregating Data

KQL uses the summarize operator to perform aggregations such as counts, sums, averages, min, max, etc.

Example – Aggregate Total Sales:

EventHouseTable
| where EventType == "Purchase"
| summarize TotalSales = sum(Value)

Example – Grouped Aggregation:

EventHouseTable
| where EventType == "Purchase"
| summarize CountEvents = count(), TotalSales = sum(Value) by Country

Time-Bucketed Aggregation

KQL supports time binning using bin():

EventHouseTable
| where EventType == "Purchase"
| summarize TotalSales = sum(Value) by Country, bin(Timestamp, 1h)

This groups results into hourly buckets, which is ideal for time-series analytics and dashboards.

Common KQL Aggregation Functions

Function	Description
`count()`	Total number of records
`sum(column)`	Sum of numeric values
`avg(column)`	Average value
`min(column)` / `max(column)`	Minimum / maximum value
`percentile(column, p)`	Percentile calculation

Combining Operators

KQL queries are often a combination of select, filter, and aggregation:

EventHouseTable
| where EventType == "Purchase" and Timestamp >= ago(7d)
| project Country, Value, Timestamp
| summarize TotalSales = sum(Value), CountPurchases = count() by Country
| order by TotalSales desc

This pipeline:

Filters for purchases in the last 7 days
Projects relevant fields
Aggregates totals and counts
Orders the result by highest total sales

KQL vs SQL: What’s Different?

Feature	SQL	KQL
Syntax	Declarative	Pipeline-based
Joins	Extensive support	Limited pivot semantics
Use cases	Relational data	Time-series, event, logs
Aggregation	`GROUP BY`	`summarize`

KQL shines when querying streaming or event data at scale — exactly the kinds of scenarios Eventhouse targets.

Performance Considerations in KQL

Apply where as early as possible.
Use project to keep only necessary fields.
Time-range filters (e.g., last 24h) drastically reduce scan size.
KQL runs distributed and is optimized for large event streams.

Practical Use Cases

Example – Top Countries by Event Count:

EventHouseTable
| summarize EventCount = count() by Country
| top 10 by EventCount

Example – Average Value of Events per Day:

EventHouseTable
| where EventType == "SensorReading"
| summarize AvgValue = avg(Value) by bin(Timestamp, 1d)

Exam Relevance

In DP-600 exam scenarios involving event or near-real-time analytics (such as with Eventhouse or KQL-backed lakehouse sources), you may be asked to:

Write or interpret KQL that:
- projects specific fields
- filters records based on conditions
- aggregates and groups results
Choose the correct operator (where, project, summarize) for a task
Understand how KQL can be optimized with time-based filtering

Key Takeaways

project selects specific fields.
where filters rows based on conditions.
summarize performs aggregations.
Time-series queries often use bin() for bucketing.
The KQL pipeline enables modular, readable, and optimized queries for large datasets.

Final Exam Tips

If a question involves event streams, telemetry, metrics over time, or real-time analytics, and asks about summarizing values after filtering, think KQL with where, project, and summarize.

project → select columns
where → filter rows
summarize → aggregate and group
bin() → time-based grouping
KQL is pipeline-based, not declarative like SQL
Used heavily in Eventhouse / real-time analytics

Practice Questions:

Here are 10 questions to test and help solidify your learning and knowledge. As you review these and other questions in your preparation, make sure to …

Identifying and understand why an option is correct (or incorrect) — not just which one
Look for and understand the usage scenario of keywords in exam questions to guide you
Expect scenario-based questions rather than direct definitions

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

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

✅ Correct Answer: C

Explanation:
project is the KQL operator used to select and optionally rename columns. KQL does not use SELECT like SQL.

2. Which operator is used to filter rows in a KQL query?

A. filter
B. where
C. having
D. restrict

✅ Correct Answer: B

Explanation:
The where operator filters rows based on conditions and is typically placed early in the query pipeline for performance.

3. How do you count the number of records in a table using KQL?

A. count(*)
B. summarize count()
C. summarize count(*)
D. summarize count()

✅ Correct Answer: D

Explanation:
In KQL, aggregation functions are used inside summarize. count() counts rows; count(*) is SQL syntax.

4. Which KQL operator performs aggregations similar to SQL’s GROUP BY?

A. group
B. aggregate
C. summarize
D. partition

✅ Correct Answer: C

Explanation:
summarize is the KQL operator used for aggregation and grouping.

5. Which query returns total sales grouped by country?

| group by Country sum(Value)

| summarize sum(Value) Country

| summarize TotalSales = sum(Value) by Country

| aggregate Value by Country

✅ Correct Answer: C

Explanation:
KQL requires explicit naming of aggregates and grouping using summarize … by.

6. What is the purpose of the `bin()` function in KQL?

A. To sort data
B. To group numeric values
C. To bucket values into time intervals
D. To remove null values

✅ Correct Answer: C

Explanation:
bin() groups values—commonly timestamps—into fixed-size intervals (for example, hourly or daily buckets).

7. Which query correctly summarizes event counts per hour?

| summarize count() by Timestamp

| summarize count() by hour(Timestamp)

| summarize count() by bin(Timestamp, 1h)

| count() by Timestamp

✅ Correct Answer: C

Explanation:
Time-based grouping in KQL requires bin() to define the interval size.

8. Which operator should be placed as early as possible in a KQL query for performance reasons?

A. summarize
B. project
C. order by
D. where

✅ Correct Answer: D

Explanation:
Applying where early reduces the dataset size before further processing, improving performance.

9. Which KQL query returns the top 5 countries by event count?

| top 5 Country by count()

| summarize count() by Country | top 5 by count_

| summarize EventCount = count() by Country | top 5 by EventCount

| order by Country limit 5

✅ Correct Answer: C

Explanation:
You must first aggregate using summarize, then use top based on the aggregated column.

10. In Microsoft Fabric, KQL is primarily used with which workload?

A. Warehouse
B. Lakehouse SQL endpoint
C. Eventhouse
D. Semantic model

✅ Correct Answer: C

Explanation:
KQL is the primary query language for Eventhouse and real-time analytics scenarios in Microsoft Fabric.

Overview

1. What Is Kusto Query Language?

2. Azure Monitor Logs and Log Analytics

3. Understanding the Basic KQL Query Structure

Important exam concept

4. The where Operator

Common comparison operators

5. Filtering by Time

Exam tip

6. The project Operator

7. The project-away Operator

8. The extend Operator

extend vs. project

9. The summarize Operator

10. Grouping with summarize

11. Conditional Aggregations

12. Sorting Results

Troubleshooting example

13. Limiting Results with take

14. The distinct Operator

15. Searching Text

16. Working with Null and Empty Values

17. Binning Data with bin()

18. Creating Time-Series Charts

19. Analyzing Errors Over Time

20. Finding the Most Common Errors

21. Analyzing HTTP Status Codes

22. Identifying Slow Requests

23. Counting Distinct Users

24. Joining Data

Important exam consideration

25. The union Operator

26. Working with Application Insights Telemetry

27. Logs Versus Metrics

Metrics

Logs

28. Querying Resource Logs

29. Using render

30. Detecting Anomalies

31. Example: Detecting an Increase in Errors

Step 1 – Determine whether errors are occurring

Step 2 – Determine when they occurred

Step 3 – Identify the failing endpoints

Step 4 – Identify the status codes

32. Example: Investigating a Slow AI Application

33. Query Performance and Cost

Filter early

Select only required columns

Use appropriate time ranges

Aggregate when appropriate

34. Querying Basic and Auxiliary Logs

35. Running KQL Programmatically

36. Important KQL Operators for AI-200

37. KQL Exam Tips

Remember:

38. Putting It All Together

39. Key Takeaways

Practice Exam Questions

Question 1

Answer: A

Explanation

Question 2

Answer: B

Explanation

Question 3

Answer: C

Explanation

Question 4

Answer: C

Explanation

Question 5

Answer: A

Explanation

Question 6

Answer: C

Explanation

Question 7

Answer: B

Explanation

Question 8

4. The `where` Operator

6. The `project` Operator

7. The `project-away` Operator

8. The `extend` Operator

`extend` vs. `project`

9. The `summarize` Operator

10. Grouping with `summarize`

13. Limiting Results with `take`

14. The `distinct` Operator

17. Binning Data with `bin()`

25. The `union` Operator

29. Using `render`