A data engineer is designing a semantic model in Microsoft Fabric. The model needs to incorporate data from a large, frequently updated data lake (Delta Lake tables in OneLake) and also from a small, static SQL Server dimension table. The primary goal is to achieve the best possible query performance for reports that combine data from both sources, while ensuring the data lake data is always near real-time. Which storage mode combination should be used?
- ADirect Lake for all tables.
- BDirect Lake for Delta Lake tables and Import for the SQL Server dimension table.
- CImport for all tables.
- DDirectQuery for Delta Lake tables and Import for the SQL Server dimension table.
Show answer & explanationAnswer & explanation
Correct answer: B. Direct Lake for Delta Lake tables and Import for the SQL Server dimension table.
Direct Lake mode is ideal for large, frequently updated data lake tables in OneLake to provide near real-time data with excellent performance. For a small, static SQL Server dimension table, Import mode offers the best performance by caching the data, making the combined 'Mixed' approach optimal for both performance and freshness.
Why the other options are wrong
- A. Direct Lake is specifically for OneLake data. SQL Server tables cannot be directly in Direct Lake mode.
- C. Importing all tables would involve periodic refreshes for the large Delta Lake data, preventing near real-time freshness.
- D. DirectQuery for large, frequently updated data lake tables can be performant but generally less optimized than Direct Lake for OneLake data, and might not achieve the 'best possible query performance' as Direct Lake would.
Direct Lake with Import (Mixed Mode)
A semantic model configuration that combines Direct Lake mode for tables sourced from OneLake (for near real-time, high performance) and Import mode for other tables (like small dimensions from SQL databases) to achieve optimal performance and data freshness across diverse sources.
- Leverages Direct Lake benefits for OneLake data.
- Uses Import mode for other data for maximum performance.
- Enables a 'mixed' approach within a single semantic model.
- Ideal for composite models with OneLake and traditional DB sources.
Memory trick: Direct Lake for the lake, Import for the static stake.