Choosing a Semantic Model Storage Mode for a Large Retail Schema
Contoso Retail has a 60 GB star schema in OneLake Delta with 500 million sales rows. About 250 analysts query 30 reports, with peak concurrency of 80. The current Import model refreshes nightly in two hours and consumes most of the F64 capacity. The business now wants 10-minute freshness. Row-level security restricts 12 regional teams. Some report queries scan high-cardinality columns and request up to 20 million rows. A team member proposes DirectQuery against the Fabric warehouse to avoid refresh entirely.
1. Recommend a storage mode and justify it against Import and DirectQuery for this workload.
Read the worked answer
Direct Lake is the best fit if the Delta tables meet its requirements: they are Delta, not views, and should be well optimized with V-Order and reasonable file sizes. It reads OneLake data without an Import refresh, so the two-hour refresh and much of the capacity cost disappear, and it can provide near-Import query performance. Import cannot meet 10-minute freshness at this size without unacceptable refresh load. DirectQuery to the warehouse would push every visual query to SQL and likely worsen concurrency at 80 users, especially for 20-million-row scans.
The judgment call is conditional: Direct Lake is not a free upgrade. If queries exceed Direct Lake guardrails, Fabric can fall back to DirectQuery, producing exactly the slow behavior the team wants to avoid. The model must be tested with the real high-cardinality queries before rollout.
2. How would you implement the 12 regional RLS roles and keep freshness at 10 minutes?
Read the worked answer
Create a region dimension with a stable region key and a security mapping table that maps user principal names to region. Define 12 model roles, each with a DAX filter on the region dimension, and test each role with a representative account. For Direct Lake, RLS is evaluated in the semantic model, so the Delta source does not need 12 copies of the data.
Freshness comes from the Delta tables being updated within the 10-minute target. Direct Lake reads the Delta log, so the ingestion pipeline must commit new Delta versions on that cadence and the semantic model must be able to see them. Validate the end-to-end lag: source change, Bronze write, Silver merge, Delta commit, and query visibility. If the ingestion pipeline itself takes 30 minutes, Direct Lake cannot invent freshness. Also verify that RLS predicates do not force expensive scans on high-cardinality columns.
3. What guardrails would you put in place before and after switching to Direct Lake?
Read the worked answer
Before switching, profile the Delta tables: remove views and unsupported column types, compact small files, apply V-Order, and ensure statistics are useful. Rewrite the worst queries to aggregate before returning rows; a visual requesting 20 million rows is a report design problem, not a storage-mode problem. Reduce model size by removing unused columns and high-cardinality calculated columns.
After switching, monitor for Direct Lake fallback to DirectQuery and for query latency regressions. Set a performance baseline from the Import model, compare the same report pages, and watch capacity CU. Establish a rule that any table failing guardrails is fixed at the Delta layer, not by silently reverting the model to Import. Document the fallback behavior so report authors understand why a query may suddenly slow down.