A data engineer is configuring a semantic model in Microsoft Fabric. The model will be used for both high-level aggregated reporting and detailed, ad-hoc analysis. The underlying data resides in a data warehouse (Azure Synapse Analytics dedicated SQL pool). The engineer wants to achieve optimal performance for frequently accessed aggregated data while ensuring that less common, detailed queries still return real-time data from the source. Which storage mode configuration best supports these requirements?
- ASet all tables to DirectQuery mode.
- BUse Dual storage mode for tables requiring both aggregated performance and real-time detail.
- CSet all tables to Import mode.
- DImplement Direct Lake mode for the entire model.
Show answer & explanationAnswer & explanation
Correct answer: B. Use Dual storage mode for tables requiring both aggregated performance and real-time detail.
Dual storage mode allows a table to behave as both Import and DirectQuery. For aggregated queries, the semantic model uses the in-memory (Import mode) cache for fast performance. For detailed queries or when real-time data is explicitly requested, it falls back to DirectQuery mode, fetching the latest data directly from the source. This perfectly balances the need for fast aggregated reporting and real-time detailed analysis.
Why the other options are wrong
- A. DirectQuery mode would provide real-time data but might be slower for aggregated reports compared to using an in-memory cache.
- C. Import mode would provide fast aggregated reports but would not offer real-time data for detailed analysis without frequent, costly refreshes.
- D. Direct Lake mode is optimized for Delta Lake tables in OneLake, not Azure Synapse Analytics dedicated SQL pools, and thus is not applicable here.
Dual Storage Mode
Dual storage mode in Microsoft Fabric semantic models allows a table to operate in both Import and DirectQuery modes simultaneously. It leverages the Import cache for faster aggregated queries and falls back to DirectQuery for real-time or detailed queries, offering a hybrid approach to data access and performance.
- Table data is cached (Import) and also accessible directly from the source (DirectQuery).
- Engine automatically chooses the most efficient mode for a query.
- Ideal for balancing performance of aggregated reports with real-time detail.
- Reduces the need for separate models or complex partitioning strategies.
Memory trick: Dual mode: two paths, one for speed, one for fresh data, a perfect deed.