A KQL database in Microsoft Fabric is a Real-Time Intelligence database that runs inside an Eventhouse and answers low-latency queries over streaming telemetry, logs, and event data. I recommend it whenever you need time-series or log analysis with fast results, not batch reporting. It connects natively to OneLake, so other Fabric experiences can read the same data without a copy. The rest of this guide walks through creating one, feeding it data and keeping it fast in production.
TL;DR:
- Enabling OneLake after database creation restricts table renaming, so schema stability should be finalized before toggling this setting.
- Materialized views are essential for recurring aggregations to maintain speed, even if they slightly reduce data freshness.
- Filtering data early in KQL queries and limiting intermediate row counts significantly improve overall performance.
- Using database shortcuts saves storage but involves a small synchronization lag, making them ideal for non-production or sandbox environments.
- Checking activity logs promptly helps identify capacity limits or permission issues that commonly cause creation failures.
Table of Contents
- Prerequisites and permissions before you start
- How to create a KQL database in Microsoft Fabric
- Getting data into your KQL database
- Writing KQL queries that actually perform well
- Managing and monitoring your KQL database day to day
- Performance and operational best practices worth adopting early
- A Fabric consultant’s take on KQL database design
- How Datataal can help you get this right the first time
- Sources
- FAQ
Prerequisites and permissions before you start
Before you open the Fabric portal, get these in place. Skipping this step is the most common cause of a blocked creation flow.
- A Fabric-enabled workspace with capacity assigned and access to Real-Time Intelligence.
- An existing Eventhouse, or the rights to create one inside your workspace.
- Contributor or admin permissions on the KQL database itself, not just the workspace.
- A decision on OneLake availability, since enabling it later restricts table renaming.
- Confirmation of your workspace region and capacity, since your query and ingestion URIs are tied to those settings.
I always settle the OneLake decision early. It sounds like a toggle you can flip anytime, but changing your mind after tables are named creates avoidable rework.
How to create a KQL database in Microsoft Fabric
Creating a database takes minutes through the portal, and the same task can be scripted for repeatable deployments. Here is the sequence I follow with clients:
- Open your workspace and select Real-Time Intelligence from the experience switcher.
- Choose an existing Eventhouse, or create a new one if this is a fresh project.
- Select New KQL database and give it a name that will survive a rename freeze once OneLake availability is on.
- Decide OneLake availability, retention and caching settings at creation time rather than after ingestion starts.
- Once created, copy the Query URI and Ingestion URI from the database details pane. You will need both for client tools and ingestion pipelines.
- Open the associated KQL queryset to confirm the connection works before building anything else.
- Check the table folder structure and confirm your permissions match what downstream consumers expect.
For automation, the REST API Items operations for KQL databases let you create, update and delete databases programmatically, and a Terraform resource exists for teams that manage Fabric infrastructure as code with service principal authentication.
A KQL database in Fabric is a specialised item inside Real-Time Intelligence that runs within an Eventhouse and supports native OneLake integration. That native connection is what lets a semantic model or notebook read Eventhouse data directly, which matters when you are designing around OneLake to avoid data duplication.
If creation fails, check the activity log in the workspace first. Most errors trace back to capacity limits or missing Real-Time Intelligence permissions rather than the database configuration itself.
Getting data into your KQL database
You have three main routes for ingestion, and picking the right one depends on your source and latency needs.
- Eventstreams connect to sources like Event Hubs, IoT Hub or change data capture feeds through the Get data wizard, where you preview and map incoming JSON or Parquet before it lands in a table.
- Real-Time Hub connectors offer a more guided path for common sources without hand-building a stream.
- Database shortcuts let you reference an existing Azure Data Explorer cluster or another KQL database as read-only, avoiding a full copy.
Ingestion through Eventstreams or Real-Time Hub connectors means you rarely need a full ETL pipeline just to populate a table. Use update policies when you need transformation logic to run automatically as data lands, and reserve pre-processing inside the Eventstream for cases where you must reshape or filter before the data ever reaches the database. Our Eventstream guide covers this decision in more depth. Database shortcuts carry a short synchronisation lag, so treat them as suited to sandboxes and non-production workloads rather than sub-second consistency needs.
Pro Tip: Test your mapping on a small preview batch before committing an Eventstream to production, since a mismatched schema is far easier to fix before ingestion starts than after.
Writing KQL queries that actually perform well
KQL uses a pipe-based model, so you chain operators left to right rather than nesting subqueries the way you might in T-SQL. The common operators tutorial covers the ones you will reach for daily:
whereto filter rows as early as possible in the pipeline.projectandextendto shape and add columns.summarizecombined withbin()for time-bucketed aggregation.top,takeandsortto control result size and order.
If your team is moving from SQL, the explain operator converts T-SQL syntax into its KQL equivalent, which is a genuinely practical bridge while your analysts learn idiomatic query shapes. Copilot inside the KQL queryset can also draft a starting query from a plain-language description, which is useful for a first pass before you tune it.
Filter early, reduce intermediate row counts, and summarise on pre-binned time windows rather than raw timestamps.
That guidance from Microsoft’s query tutorials is worth internalising, because query order changes performance more than almost anything else in KQL. For recurring summaries, build a materialized view instead of re-scanning raw tables on every run. The KQL queryset itself supports multiple data-source tabs, so you can query several databases side by side, save queries for the team, and share a queryset rather than passing scripts around.
Managing and monitoring your KQL database day to day
The database ribbon and Explorer pane give you the controls you will use most: refresh, enable live view, create related items, and launch Get data without leaving the database context. The manage and monitor documentation describes three main surfaces worth checking regularly.
| Surface | What it shows | Why it matters |
|---|---|---|
| Tables view | Row counts, schema, data preview | Confirms ingestion landed correctly |
| Query insights | Duration percentiles, cache miss rate, top queries | Flags slow or expensive queries early |
| Database details | Retention, caching policy, OneLake availability | Confirms operational settings match design |
Retention and caching policies set how long data stays queryable at full speed versus moved to cold storage, and table update policies control any automatic transformation on ingestion, which is critical to managing AI hallucinated data effectively. Your Query URI and Ingestion URI live in the database details pane; share them only with the tools and people that need direct access, since both act as connection credentials to production data.
Performance and operational best practices worth adopting early
A handful of habits separate a KQL database that stays fast under load from one that slows down as volume grows.
- Build materialized views for any aggregation you run repeatedly. Microsoft’s data management guidance notes this avoids rescanning raw tables and keeps results current, though it does trade some freshness and storage for that speed.
- Set caching and retention windows deliberately rather than accepting defaults, since a longer hot cache costs more but shortens query time on recent data.
- Filter before you summarise. Reducing cardinality before a
summarizestep consistently outperforms filtering afterwards. - Use database shortcuts to avoid duplicating data across environments, but expect a small metadata synchronisation lag and test accordingly, as community reports on OneLake shortcut behaviour confirm.
Pro Tip: Run a load test against your materialized views before go-live, since freshness trade-offs only become visible once real query volume hits the database.
A Fabric consultant’s take on KQL database design
The decision I see teams get wrong most often is enabling OneLake availability before they have settled table naming. Once it is on, renaming gets restricted, and that constraint outlives whatever convenience prompted the early toggle. I generally hold off until the schema is stable.
For architecture, I favour a Medallion pattern with materialized views handling the aggregation layer, which avoids the duplicated, orphaned gold models that show up when every team builds its own summary table. On handover, I leave operations teams a short document: retention and update policy settings, a list of materialized views with their refresh logic, and the queries that matter, so nobody has to reverse-engineer intent from raw KQL months later.
— Michael
How Datataal can help you get this right the first time
Building a KQL database is straightforward once you know the steps. Designing one that stays fast, governed and easy to hand over as your data volume grows is a different job, and it is the one I do for clients across data architecture, engineering and Power BI integration.
My services cover the practical range this article touches on:
- Microsoft Fabric Architecture design, including Eventhouse and OneLake decisions made before they become expensive to reverse.
- Data Engineering for ingestion pipelines, Eventstreams and update policy logic.
- Power BI & Reporting integration so your real-time data reaches dashboards without a duplicate copy.
- Azure Synapse to Fabric migration for teams moving existing workloads across.
If you would rather have a specialist handle the design decisions than work through them solo, Microsoft Fabric consulting services are available; current prices are on the pricing page.
FAQ
What is a KQL database?
A KQL database is a Real-Time Intelligence item in Microsoft Fabric that runs inside an Eventhouse and is built for low-latency queries over streaming events, logs and telemetry. It integrates natively with OneLake so other Fabric experiences can read the same data without duplicating it.
Is Service Fabric deprecated?
Service Fabric is a separate Microsoft platform for building and running microservices and containers, and it is distinct from Microsoft Fabric, the data analytics platform this article covers. Check Microsoft’s own product documentation directly for Service Fabric’s current lifecycle status, since that detail is not part of the data platform discussed here.
What database does Fabric use?
Microsoft Fabric offers several database types depending on the workload, including the KQL database for real-time and log analytics within Real-Time Intelligence, alongside lakehouse and warehouse items for other data patterns. Which one you use depends on whether your priority is streaming event data or structured batch analytics.
What are the limitations of KQL?
Enabling OneLake availability on a KQL database restricts later table renaming, so naming decisions need to happen early. Database shortcuts also introduce a short metadata synchronisation lag, which makes them better suited to non-production or sandbox use than workloads needing sub-second consistency.
How do I convert a SQL query to KQL?
The explain operator in Microsoft Fabric converts T-SQL syntax into its KQL equivalent, which gives SQL-fluent teams a working starting point before they refine the query for KQL’s idioms. It is intended as a learning bridge rather than a guarantee of the most efficient query shape.
