A data modeler is developing a semantic model in Microsoft Fabric. The model includes a 'Transactions' table, which is very large and contains historical data dating back 10 years. Only the last 3 years of data are frequently accessed by users for detailed analysis, while older data is rarely queried but must remain available for compliance. The modeler wants to optimize refresh performance and storage by refreshing only the most recent data, while efficiently handling the older, less-accessed data. Which feature should be configured?
- AIncremental Refresh with a 'store old data in DirectQuery' policy.
- BDual Mode for the 'Transactions' table, with all data imported.
- CDirectQuery Mode for the 'Transactions' table.
- DAutomatic Aggregations for the 'Transactions' table.
Show answer & explanationAnswer & explanation
Correct answer: A. Incremental Refresh with a 'store old data in DirectQuery' policy.
Incremental Refresh with a 'store old data in DirectQuery' policy is perfectly suited for this scenario. It allows refreshing only the latest partitions (e.g., last 3 years) in Import mode for fast access, while older, infrequently accessed data remains available directly from the source via DirectQuery, meeting compliance requirements without consuming Import mode memory or refresh time.
Why the other options are wrong
- B. Dual mode with all data imported would still mean importing all 10 years of data, failing to optimize storage and refresh for older data.
- C. DirectQuery alone would mean all queries go to the source, potentially slow, and doesn't optimize refresh or storage by importing recent data.
- D. Automatic Aggregations improve query performance for aggregated data but don't address the specific need to manage historical data retention and refresh efficiently across different storage modes.
Incremental Refresh with DirectQuery for Historical Data
This policy within incremental refresh allows recent data partitions to be imported for performance, while older, less-frequently accessed partitions remain in DirectQuery mode, reducing refresh times and memory usage.
- Optimizes refresh by only importing new data.
- Reduces memory consumption by keeping historical data in DirectQuery.
- Ensures all historical data is available for compliance without full import.
Memory trick: Recent data imports, old data waits, DirectQuery for history, no data fates.