Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest Fabric, Power BI, and SQL learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
You can create a logical copy of KQL database data in an eventhouse by turning on OneLake availability. Turning on OneLake availability means that you can query the data in your KQL database in Delta Lake format via other Fabric engines such as Direct Lake mode in Power BI, Warehouse, Lakehouse, Notebooks, and more.
Delta Lake is a unified data lake table format that achieves seamless data access across all compute engines in Microsoft Fabric. For more information on Delta Lake, see What is Delta Lake?.
In this article, you learn how to turn on availability of KQL database data in OneLake.
You can turn on OneLake availability at the database or table level. When enabled at the database level, all new tables and their data are made available in OneLake. When turning on the feature, you can also choose to apply this option to existing tables by selecting the Apply to existing tables option. Turning on at the table level makes only that table and its data available in OneLake. The Data retention policy of your KQL database is also applied to the data in OneLake. Data removed from your KQL database at the end of the retention period is also removed from OneLake. If you turn off OneLake availability, data is soft deleted from OneLake.
While OneLake availability is turned on, you can't do the following tasks:
If you need to do any of these tasks, use the following steps:
Important
Turning off OneLake availability soft deletes your data from OneLake. When you turn availability back on, only new data is made available in OneLake with no backfill of the deleted data.
Turn off OneLake availability.
Perform the desired task.
Turn on OneLake availability.
Important
For more information about the time it takes for data to appear in OneLake, see Adaptive behavior.
There's no additional storage cost to turn on OneLake availability. For more information, see resource consumption.
You can turn on OneLake availability either on a KQL database or table level.
To turn on OneLake availability, browse to the OneLake section in the details pane of your KQL database or table.
Set Availability to On.
The database refreshes automatically.
You turned on OneLake availability in your KQL database. You can now access all the new data added to your database at the given OneLake path in Delta Lake format. You can also choose to create a OneLake shortcut from a Lakehouse, Data Warehouse, or query the data directly via Power BI Direct Lake mode.
Eventhouse offers a robust mechanism that intelligently batches incoming data streams into one or more Parquet files, structured for analysis. Batching data streams is important when dealing with trickling data. Writing many small Parquet files into the lake can be inefficient resulting in higher costs and poor performance.
Eventhouse's adaptive mechanism can delay write operations if there isn't enough data to create optimal Parquet files. This ensures Parquet files are optimal in size and adhere to Delta Lake best practices. The Eventhouse adaptive mechanism ensures that the Parquet files are primed for analysis and balances the need for prompt data availability with cost and performance considerations.
Note
For example, use the following command to set the delay to 5 minutes:
.alter-merge table <TableName> policy mirroring dataformat=parquet with (IsEnabled=true, TargetLatencyInMinutes=5);
Caution
Adjusting the delay to a shorter period might result in a suboptimal delta table with a large number of small files, which can lead to inefficient query performance. The resultant table in OneLake is read-only and can't be optimized after creation.
You can monitor how long ago new data was added in the lake by checking your data latency using the .show table mirroring operations command command.
Results are measured from the last time data was added. When Latency results in 00:00:00, all the data in the KQL database is available in OneLake.
When you turn on OneLake availability on a table, a delta log folder is created along with any corresponding JSON and Parquet files. You can view the files that were made available in OneLake and their properties while remaining within Real-Time Intelligence.
To view the files, hover over a table in the Explorer pane and then select the More menu [...] > View files.
To view the properties of the delta log folder or the individual files, hover over the folder or file and then select the More menu [...] > Properties.
To view the files in the delta log folder:
By default, when OneLake availability is turned on, a mirroring policy is enabled. You can use the policy to monitor data latency or alter it to partition delta tables.
Note
If you turn off OneLake availability, the mirroring policy's IsEnabled
property is set to false (IsEnabled=false
).
You can partition your delta tables to improve query speed. For information about when to partition your OneLake files, see When to partition tables. Each partition is represented as a separate column using the PartitionName listed in the Partitions list. This means your OneLake copy has more columns than your source table.
To partition your delta tables, use the .alter-merge table policy mirroring command.
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest Fabric, Power BI, and SQL learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Work with Delta Lake tables in Microsoft Fabric - Training
Tables in a Microsoft Fabric lakehouse are based on the Delta Lake technology commonly used in Apache Spark. By using the enhanced capabilities of delta tables, you can create advanced analytics solutions.
Certification
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.
Documentation
Create a database shortcut - Microsoft Fabric
Learn how to create a database shortcut to data in another KQL Database or in Azure Data Explorer in Real-Time Intelligence.
Eventhouse overview - Microsoft Fabric
Learn about eventhouse data storage in Real-Time Intelligence.
Monitoring status and performance of an Eventstream item - Microsoft Fabric
Learn how to monitor the status and performance of an eventstream.