First Steps in Fabric Real-Time Intelligence with KQL
When I started working with real-time intelligence and tried using KQL in Microsoft Fabric for the first time, I remember how overwhelming it felt. I got stuck on what seemed like simple steps, and figuring out the basics wasn’t always straightforward (big thanks to Brian (kql.how & dcode.bi), who helped me along the way). That’s exactly why I created this article and tutorial: to make it easier for others to get started.
KQL (Kusto Query Language) is a powerful way to query and analyze real-time data in Microsoft Fabric. If you’re new to real-time intelligence or KQL, this guide is for you! In this article we will have a look into:
- project (filter columns)
- where (filter rows)
- summarize (aggregate data)
- render (visualize results)
- order by (sort the data)
- top (get the top results)
How can I get started with KQL in Fabric Real-time Intelligence?
To start querying with KQL, you’ll first need some data. If you don’t already have an Eventhouse in Microsoft Fabric with data ready to use, don’t worry; it’s simple to set up. Microsoft provides an easy-to-follow tutorial to help you ingest data into an Eventhouse.
Once you’ve ingested some data (for example, one of the demo datasets Microsoft provides; I used the bike one), head to your Eventhouse and select the KQL Database. In the Eventhouse pane, look for the option “Query with code” and click it to open the query editor.
Once you click “Query with code”, you’ll see some helpful pre-set options, such as:
- Show any 100 records.
- Show the total count of records.
- Show records ingested in the last 24 hours.
If you’re completely new to KQL, I recommend starting with one of these, like “Show any 100 records”, to see what happens. It’s a great way to get a feel for queries and verify that your data is ready for exploration.
Basic KQL Queries
When you click “Show any 100 records”, a query is automatically generated, and you’ll see results in a table format.
Bikes: This is the table I am querying. It contains a sample dataset of bikes. If I ran this only, I would exceed the limit of records (500,000). But let’s face it, no one wants to scroll through that!
| (Pipe symbol): The pipe (|) connects commands in KQL, passing data from one step to the next.
take: The take command limits results. For example, take 100 gives you the first 100 rows.
Bikes
| take 100
Now, we will steadily build our code, and I will show you different commands along the way.
Project Statement
Sometimes, you don’t need all the columns in your dataset. With the project command, you can select only the columns that matter to you. This makes your query more focused and efficient, especially when working with large datasets.
For example, let’s say we’re only interested in the following columns:
- Neighbourhood: The area where the bikes are located.
- Number of Bikes: The number of bikes available.
- Number of Empty Docks: The number of empty docks.
- Timestamp: The timestamp for the data.
Bikes
| take 100
| project Neighbourhood, No_Bikes, No_Empty_Docks, current_time
Tip: Improving readability of column names
To make the output more user-friendly, you can use aliases in the project statement, replacing underscores (_) with spaces in column names.
This is especially handy when sharing the results with others or creating visualizations.
Bikes
| take 100
| project ['Neighbourhood'],
['Number of Bikes'] = No_Bikes,
['Empty Docks'] = No_Empty_Docks,
['Current Time'] = current_time
Where Statement
Once you’ve selected the columns you’re interested in, the next step is to focus on the rows that are most relevant to your analysis. The where command is perfect for filtering rows based on specific conditions.
Let’s say we want to see data only for neighbourhoods with more than 10 bikes available.
Tip: Combine Filters for More Precision
You can make your filters even more specific by combining multiple conditions using logical operators like and:
- and: Filters rows that meet all conditions.
For example, if you only want to include rows where there are more than 10 bikes and at least 5 empty docks the where statement would look like this:
| where No_Bikes > 10 and No_Empty_Docks > 5
Bikes
| take 100
| where No_Bikes > 10
| project ['Neighbourhood'],
['Number of Bikes'] = No_Bikes,
['Empty Docks'] = No_Empty_Docks,
['Current Time'] = current_time
Summarize Statement
Once you’ve filtered and refined your dataset, you may want to aggregate or group your data to gain high-level insights. The summarize command allows you to calculate totals, averages, counts, and more across grouped data.
Imagine you want to know the total number of bikes and empty docks available in each neighbourhood.
- summarize: Aggregate the data
- Total Bikes = sum(No_Bikes): Calculates the total number of bikes for each neighbourhood.
- Total Empty Docks = sum(No_Empty_Docks): Calculates the total number of empty docks for each neighbourhood.
The summarize command helps you move beyond raw data by calculating meaningful metrics. It’s ideal for analyzing patterns, comparing groups, or understanding overall trends.
Bikes
| take 100
| where No_Bikes > 10 and No_Empty_Docks > 5
| summarize ['Total Bikes'] = sum(No_Bikes), ['Total Empty Docks'] = sum(No_Empty_Docks)
by Neighbourhood
Summarize Second KQL Example
Let’s build on the previous example and introduce different types of aggregations.
- sum(): Calculates the total value of the specified column.
- sum(No_Bikes) calculates the total number of bikes available in each neighbourhood.
- sum(No_Empty_Docks) calculates the total number of empty docks available in each neighbourhood.
- avg(): Calculates the average value of the specified column.
- avg(No_Bikes) calculates the average number of bikes per neighbourhood.
- max(): Finds the maximum value in the specified column.
- max(No_Bikes) gives the highest number of bikes in any neighbourhood.
- min(): Finds the minimum value in the specified column.
- min(No_Empty_Docks) gives the lowest number of empty docks in any neighbourhood.
| take 100
| where No_Bikes > 10 and No_Empty_Docks > 5
| summarize
['Total Bikes'] = sum(No_Bikes),
['Total Empty Docks'] = sum(No_Empty_Docks),
['Average Bikes'] = avg(No_Bikes),
['Max Bikes'] = max(No_Bikes),
['Min Empty Docks'] = min(No_Empty_Docks)
by Neighbourhood
Render Statement
Once you have summarized or filtered your data, you might want to visualize it. In KQL, you can use the render command to create visualizations directly within your query results.
- render barchart: This tells KQL to display the results as a bar chart. There are several other options for different visualizations.
The result will be a bar chart showing the total number of bikes by neighbourhood.
This visual representation can help quickly identify patterns and trends in your data, making it much easier to interpret.
Bikes
| take 100
| where No_Bikes > 10 and No_Empty_Docks > 5
| summarize ['Total Bikes'] = sum(No_Bikes) by Neighbourhood
| render barchart
Order by Statement
Sorting your data can be very helpful when you want to see the most important or relevant records first. You can use the order by command to sort your query results by one or more columns.
- order by Total_Bikes desc: This sorts the results in descending order based on the total number of bikes.
If you want ascending order, you can use asc instead.
Tip: Combining render and order by
You can combine the render and order by commands. For example, you could sort the results by the number of bikes and then display the data as a bar chart to quickly spot the highest and lowest values.
Bikes
| take 100
| where No_Bikes > 10 and No_Empty_Docks > 5
| summarize ['Total Bikes'] = sum(No_Bikes) by Neighbourhood
| render barchart
| order by ['Total Bikes'] desc
Top Statement
The top command is useful when you want to limit your results to a specific number of rows, such as the top 5 or top 10 records, based on a particular column. This can be helpful for quickly identifying the best-performing categories or the most significant outliers.
For example, let’s say we want to see the top 5 neighbourhoods with the highest number of bikes.
top 5 by Total_Bikes desc: This retrieves the top 5 rows sorted in descending order based on the Total_Bikes column. You can adjust the number (5 in this case) to retrieve as many records as needed. You can also change desc to asc if you prefer ascending order.
Bikes
| take 100
| where No_Bikes > 10 and No_Empty_Docks > 5
| summarize ['Total Bikes'] = sum(No_Bikes) by Neighbourhood
| top 5 by ['Total Bikes'] desc
In summary - KQL Basics
This article is meant to serve as a starting point for absolute beginners looking to get hands-on with KQL. By understanding the basic commands like project, where, summarize, render, order by, and top, you can begin querying and analyzing your data effectively.
If you’re just starting out and don’t yet have data, check out the official documentation for step-by-step instructions on how to get set up with an eventhouse and load some demo data. Once you have your data in place, you’ll be ready to apply these commands and begin your KQL journey.
Watch this YouTube Video where I demo the different KQL statements.
I created this video to show you step-by-step how to use these KQL commands in your Fabric Real-time Intelligence KQL database.