KQL Databases in Fabric - Simply Explained

0:00 Welcome to the M365 FM podcast, where we explore the people, ideas and technologies shaping the future of work. Opening the query language that makes noise useful. Your team can collect every alert, every click, every device signal, then still miss the one change that needed a response. The data keeps arriving, but it lands as noise because nobody can ask it a clear question. Most people think kql feels hard because it isn't SQL, but the real tension here is simpler. Event data moves through time and we keep trying to treat it like a normal business table. Welcome to the M365 FM podcast, where we explore the people, ideas and technologies shaping the future of work. Kql databases in fabric give that moving data a place to land and Kql turns it into answers.

0:41 So first, where does all this data actually live? Events Kql database and tables inside a fabric workspace. The first object you'll meet is an event house. It's the building where your real time data work happens, but it doesn't hold a giant pile of data itself. Instead, it contains kql databases, and those databases hold the tables where your events land. The structure is simple, which helps when the name starts sounding similar. One fabric workspace can contain multiple event houses, and each event house can contain multiple databases. Then each database can contain multiple tables. That lets you separate data by team, workload or purpose without dumping every log metric and event into one crowded spot. Say your organization runs a customer app, a warehouse operation, and a fleet of connected devices.

1:24 You might keep app events in one database, device readings in another, and operational events in a third. Each table then holds one type of record. Application requests. Temperature readings. Page views or failed sign ins. A table in a Kql database describes events and. Each row records something that happened. It might include a timestamp, a device ID, a user ID, a status code, a location, or a message. The timestamp matters because it lets you ask a different kind of question. Not just what happened, but when it began, how often it happened, and whether the pattern is getting worse. This comes from the world of Azure Data Explorer, often called ADX. KCL databases and fabric use the same Kusto engine heritage. But now fabric brings it into a SaaS experience alongside the rest of your data work.

2:07 You don't have to treat it as a separate island with its own separate story. It sits in fabric inside your workspace, close to the tools your teams already use. Underneath, the engine stores data by columns rather than reading every row like a transaction system would. That matters when you only need a few fields from a huge volume of event records. If your question concerns timestamp, service name and error code, the engine focuses on those columns. Instead of dragging every detail through the query. Compression keeps large volumes manageable and caching accelerates repeated queries, so the whole system handles data that arrives continuously and lets you explore it quickly. You aren't waiting for a nightly process before you can look at an outage pattern that started ten minutes ago. There's also a key habit built into these tables.

2:48 They are append only new records arrive, and old records usually stay as they were. That fits logs and telemetry because an event already happened. You don't edit yesterday's failed request to pretend it succeeded you at the next event and investigate the sequence. That design isn't a limitation. It lets the database focus on fast ingestion and fast analysis across a large history of events. The goal isn't to run every business process inside CXL. The goal is to ask clear questions of data in motion. Fast storage only helps when it matches the question you're trying to answer. When CXL is the right tool, the biggest mistake reaching for CXL before you've asked the right question, start with the question, not the product name. What changed? When did it change? Is it still happening? And who needs to know before this turns into a customer problem, a security incident, or a very long support call?

3:33 CXL fits when the answers live inside a steady flow of events. Think failed sign ins, application errors, page clicks, device readings, delivery scans, service response times, or operational counters. Each event looks small on its own. A single failed request tells you almost nothing but ten thousand failed requests from the same service in one hour. That tells you exactly where to look. Take security, for instance. Your identity system records sign ins all day, including the user time, location, device, and outcome. You might need to find failed attempts against one account, spot, a sudden jump in failures, or check whether successful sign ins followed them. Those are event questions where the sequence matters as much as the individual record. Application telemetry works the same way. Every request carries a timestamp, endpoint, duration, status code, and service name.

4:17 If customers complain that checkout feels slow, you don't want a monthly average. You want to see exactly when response times climbed, which endpoint caused it, and whether failures climbed at the same time. This is where time series data enters the picture. A time series is simply a measurement tracked over time. Temperature from a sensor. Every minute. Available. Bikes at a station every few minutes. Requests per second from an app. Sales events across the day. The timestamp gives every number a place in the story. Logs are slightly different. They record actions or events in more detail. Someone signed in as service restarted a package failed to scan. A user clicked a button. You often start with logs to investigate a specific event, then group them over time to see the wider pattern.

4:58 That shift from one record to a trend is where Kql starts feeling natural. And here's the head fake, you might think. Kql is for every table in fabric, but it's built for a different job. If your workload constantly changes individual records, managers orders, updates, account balances, or depends on transaction rules, a traditional transactional database fits more naturally. Kql works best when events arrive, stay recorded, and need fast analysis at scale. Inside fabric real time intelligence. The flow stays simple a source, an app, an IoT device, or an Azure service produces data. An event stream connects to that source. Applies basic changes like filters or aggregations, then sends the output onward. From there, you root it into an event house and it's Kql database where your team queries the data that root matters because it separates jobs cleanly.

5:43 Event stream handles the moving data and its root, while Kql handles the questions. A dashboard shows the result to a team watching operations and activate a response when a condition is met. Imagine a warehouse sensor reporting a temperature above its safe range. A chart tells you the reading climbed, but a chart doesn't message the person on call. Activator can watch for that condition and trigger an email. A team's message or a power automate action. The moment the data crosses the line, you set. The real tension here is speed versus attention. Teams don't need every event pushed into teams. They need the event pattern that deserves action pushed to the right person. Once your data reaches a KCL database, you don't need to write one giant query. You build the answer in small steps and the pipe symbol keeps those steps readable.

6:25 KCl without the SQL overwhelm, KCL looks unfamiliar for about five minutes. Then the pattern clicks. You start with the table name no from clause wrapping the query in extra words. If your table is called service events, that's your first line. Then you add a pipe symbol. Each line takes the result from the line above and narrows, reshapes or groups it. Read it from top to bottom. Start with all service events. keep only failures. Count those failures by our rank the services with the most failures. That's the whole mental model. When you first open an unfamiliar table, don't try to write a clever query. Use. Take Kql service events. Take ten. That gives you a small sample of records. You can see the actual column names, the date format, the status values, and whether the data looks like you expected.

7:10 Take isn't a report. It's a quick check before you start making decisions. Once you know what you're looking at, use project to keep only the columns that answer your question. Kql service events take ten project timestamp service name, status, code duration. MPs. Think of project as choosing what you want on the screen. It's close to select in SQL, but it appears later in the flow where it makes sense. You can also rename a column as you project it, which helps when results go to someone outside the data team. Next comes the operator you'll use constantly where it filters rows. Kql service events where status code does five hundred. Project timestamp, service name, status, code duration, EMS that asks for server side failures and drops everything else.

7:52 Put filters near the start of your query. When you can. You reduce the data flowing through later steps and you make the question easier for another person to read. You can filter on time to KCL service events where timestamp deleted ago twenty four H where status code is five hundred. That query has a clear purpose. Show failures from the past day. Notice you can use more than one where line. Combining conditions into one line also works, but separate lines often make a query easier to scan when the logic grows. Sometimes the data contains what you need, but not in the shape you need. That's where extend comes in. It creates a calculated column without changing the source table. KCL service events extend is failure. Eval status code five hundred.

8:33 Project timestamp service name status code is failure. You haven't edited the underlying event. You've created a temporary label inside this query that's useful for calculations. Text cleanup. Extracting a value from a message or creating a category that makes later grouping easier. The moment raw rose turned into an answer is usually summarize KCL service events where timestamp ago twenty four hours where status code oh five hundred. Summarize failure count equals count out by service name instead of thousands of individual requests. You now get one row for each service and its failure count. You can use count, avg, min, max and some insights summarize depending on the question.

9:14 If you want the worst affected services, first add sort by or use top KCL service events where timestamp ago twenty four hours. Where status code five hundred. Summarize failure count is hours. Count by service name top five by failure count desk top both sorts and limits the result. Sort by gives you more control when you want to order the full output. Be explicit with ASC or desk while you're learning. It prevents the classic moment where the smallest number lands at the top and sends you looking at the wrong service. Now put those pieces into one investigation. A support team reports that customers can't complete payments. You don't start by reading every request. You filter to failed events, group them, then find the services producing the most failures.

9:57 Service events where timestamp ago six H. Where status code A five hundred. Summarize failure count egos count by service name. Top ten by failure count desc. That won't fix the payment service by itself, but it gives the team a focused place to start. And that beats searching through a wall of logs while customers keep waiting. If you already know SQL, use that experience as a bridge. Not a reason to avoid Kql. Project maps closely to select where will feel familiar. Summarize handles the grouped calculations you'd normally write with group by and take gives you a limited sample, though it doesn't promise a ranked result unless you sort first. Fabric can also help you translate your first queries. The explain command takes a SQL statement and returns an equivalent Kql query.

10:41 Use it to learn the pattern, then read the output line by line. Don't just paste it and move on because the pipes show the logic in the order the engine processes it. One small detail causes plenty of early errors. Column names are case sensitive. Service name and service name aren't the same column. Start with. Take. Copy the column name directly and use names that make the query readable six weeks from now. Filtering tells you which events deserve attention. The next question is whether those events form a pattern over time. Working with time series and log data. Open any event data set and. The first thing you should do is find the timestamp. That column tells you where every record fits in the sequence, and without it, a long list of errors or device readings is just noise with no sense of direction. Say a service produces five hundred failures during a day.

11:23 That count tells you there's a problem, but it doesn't tell you whether the failures appeared slowly, started at lunchtime or arrived in one sharp burst after a deployment time. Turns account into a sequence you can investigate, and that's where the real story starts. Kql uses BN to group timestamps into regular time buckets. If you want to see failures each hour, you can write this service events where timestamp ago twenty four hours where status code class five hundred summarize failure count. Each of us count by timestamp one h. Sort by timestamp asc Binin timestamp one h rounds each event time into an hourly bucket, so any failure between ten o'clock and ten fifty nine lands in the same hour.

12:08 The result is a clean timeline, not a jumble of individual rows for every single request. Switch one H to five M when you need to zoom in on a short incident, switch to one D when you're tracking a longer trend. The bucket size changes the story completely. A daily view can vary. A five minute outage and a five minute view can make a six month trend look like random noise. So start with the decision you need to make. Then pick a time window that reveals the signal. The same pattern works for all kinds of data. Count page visits by our group. Delivery scans by day. Average a temperature reading every fifteen minutes. The query stays the same because time is always one of your grouping fields. KCL device readings where timestamp ago seventy. Summarize. Average temperature. AVG. Temperature by bin.

12:50 Timestamp one. H device id. Sort by timestamp ASC. Now you can compare devices over time without digging through raw sensor rows. If one device drifts upward while the others stay flat, the operations team has a focused question is that machine overheating or is the sensor feeding bad data? But here's the catch. A regular summarize skips periods with no events. Those gaps matter. A missing hour might mean nobody visited the site or a device went silent, or your ingestion pipeline stopped working. Each of those tells a very different story. Use make series when you need a continuous time series with every interval present. You decide what a missing interval should mean by setting a default value. KCL service events where timestamp ago twenty four hours.

13:33 Where status code five hundred make series failure count idols counter default on timestamp. Step one H that builds an hour by hour series and fills empty hours with zero, but only use zero if zero makes sense for errors from a reliable source, zero usually means no errors for temperature readings. If a sensor goes offline, plugging in zero creates a fake freeze. Missing data isn't just a chart problem. It points to a real question about the business process and the data feed behind it. Before you commit to building a report, test the story with render. Group your events into time buckets and add a chart right in the Kql query set. Kql service events where timestamp ago twenty four hours. Where status code is a five hundred.

14:14 Summarize failure count. Iblis count by bin. Timestamp one h render time chart. You're not building the final dashboard yet. You're checking whether the result answers your question. Did failures spike after a release? Do they happen at the same time every day? Did a burst vanish after a fix? A chart shows you that faster than scrolling through a grid of numbers Once you have a clean series, KCL can spot unusual values with series decompose anomalies. Save that for later. First, nail down the timestamps bucket size and what missing values mean. An anomaly function flags a change, but it can't tell you if it's a broken sensor, a marketing campaign, or a real incident. Logs often need context from another table. A raw request might include a customer ID while another table holds the customer's tier or region.

14:56 A join lets you combine them so you can see whether a spike affects a specific customer group. Keep the event data on the left, then pull in only the business detail you need for longer investigations. Use let to name a piece of query logic once and reuse it. Define the last twenty four hours of failures, then count them by service in one result and chart them by hour in another. That keeps your query readable and prevents two versions of the same filter from drifting apart. Analysis only matters if someone can act on it. A pattern in Kql becomes valuable only when it reaches the team that can investigate the service. Inspect the device or reach out to the customer before the next event hits. Getting started in fabric and putting insight to work. To get started in fabric, you need a workspace on fabric enabled capacity.

15:38 Without that, you won't even see the real time intelligence items you need. Create an event house in that workspace. Open event House explorer and add a Kql database from the Kql databases area. Give it a clear name from day one. Fabric allows letters, numbers, underscores, periods, and hyphens, but no special characters. A name like Operations Events says a lot more than Test database two, especially six months later when your workspace is full of stuff. When you create it, fabric asks what kind of database you want. For most teams, choose a new database that gives you a database that receives and stores its own data. A shortcut database is a follower that points to another Kql database in Fabric or Azure Data Explorer. Use that when data already lives somewhere else and your team just needs access without duplicating it.

16:19 Fabric also creates an embedded Kql query set with the database. Open it and you can run queries immediately. Rename it later if the default name gets confusing, but don't wait for perfect naming before you start learning. Load a small familiar dataset and ask one useful question. CSV and Json files are perfect for that first session. They let you see the table structure, confirm column types and get comfortable before connecting a live source. Once the pattern clicks, you can bring in data through event streams, event hubs, or other supported sources. Keep your first path simple. Load sample data. Use take to peek at it. Use where to focus on a condition. Use summarize to count or average. Then use render to see the pattern. That short sequence teaches you more than a sprawling demo environment with ten settings on every screen.

17:00 After that, figure out where the result should go. You can build power BI reports from kql tables or from a saved query. Power BI works great when people need to explore numbers, compare periods, and share reports beyond the operations team. You're not locked into Kql either. Fabric offers other ways to work with data, a SQL analytics endpoint, a notebook, or a standalone Kql query set. Which one you pick depends on the person and the task a data analyst might want to report. A data engineer might want a notebook. An operations lead might just need a dashboard tile and an alert. There's also a bigger fabric consideration if other workloads need access to this data. Through one lake, you can configure the One Lake folder property to expose KCL database data in Delta format, but don't turn that on just because it's an option.

17:40 Turn it on only when another fabric workload genuinely needs that data before live event volume ramps up. Decide on data retention, query permissions, table ownership, and alert destinations. An alert that lands in an unattended mailbox protects nobody. A table with no owner gradually turns into a mystery that everyone avoids. Ask one practical question before you start building. Do you need a report of what happened last week, or do you need to catch a change while someone can still act? The answer determines whether KCL belongs in your design. Conclusion. Start with one question. KCL databases let you trace events through time until patterns turn into decisions you can act on, but the head fake is that writing the query is where the real learning begins. Subscribe to M365 FM for more on fabric, power platform and AI.

18:21 Next, watch the next session where we write beginner kql queries against real log data one pipe at a time.