1.2 — Choose Between Import, DirectQuery and Direct Lake
PL-300 Domain 1 · Skill 1.2
What You Need to Know
The storage mode decides where a table's data lives and when queries reach the source. You set it per table in Model view (Properties > Advanced > Storage mode).
The three modes
| Import | DirectQuery | Direct Lake | |
|---|---|---|---|
| Where the data is | Compressed copy in the semantic model (VertiPaq) | Stays in the source. Every visual sends a query | Delta tables in OneLake, loaded into memory on demand |
| Freshness | As of the last refresh | Current (within the visual's cache) | Current after framing, which only reads metadata and is near-instant |
| Query speed | Fastest | Depends on the source | Close to Import |
| Power Query | Full | Only steps that fold to the source | None. Shaping is done upstream in Fabric |
| DAX | Full | Most functions, some calculated column limits | Full measures. Calculated columns and tables over Direct Lake tables are limited |
| Model size | Limited by capacity (1 GB per model on Pro) | Not limited by the model | Limited by the capacity's guardrails |
| Typical use | Most reports | Huge or fast-changing data, or data that must stay in the source | Large Fabric lakehouse or warehouse data |
Choosing
- "Data changes every minute and users must see it": DirectQuery (optionally with automatic page refresh, lesson 3.7).
- "Data is too large to import" or "can't leave the source for compliance": DirectQuery.
- "Data is already in Delta tables in a Fabric lakehouse or warehouse, and you want Import-like speed without scheduled refresh": Direct Lake.
- Otherwise: Import, which gives the best performance and every feature.
DirectQuery limits worth remembering
- A query that returns more than 1 million rows from the source fails (for example, an unfiltered table visual over a large table).
- Power Query transformations that can't fold are not allowed.
- Performance depends on source indexes and load. Each visual can send several queries.
- Single sign-on and RLS in the source are possible with some sources.
Direct Lake details
- Available only in Fabric (a Fabric capacity or trial).
- Direct Lake on SQL endpoint can fall back to DirectQuery when a query uses a SQL view, SQL-level security, or the model exceeds the capacity's guardrails. Direct Lake on OneLake reads the Delta tables directly and doesn't fall back.
- After the lakehouse or warehouse loads new data, the model's reframing (automatic or on refresh) points it at the latest Delta table version.
Composite models and Dual mode
A composite model mixes storage modes, for example a DirectQuery fact table with Import dimensions.
- Dual mode lets a table act as Import or DirectQuery depending on the query. Set dimension tables to Dual when they relate to DirectQuery fact tables. Queries that only touch the dimension (like a slicer) use the cached copy, and joins with the fact table can still run in the source.
- Switching a table from Import to DirectQuery isn't possible. Switching from DirectQuery or Dual to Import is, but it can't be undone.
Key Concept: Import trades freshness for speed and features. DirectQuery trades speed for freshness and scale. Direct Lake gives near-Import speed over Fabric Delta tables without a data copy, but only in Fabric.
Exam Traps
- Choosing Direct Lake for data that isn't in OneLake Delta tables, or for a workspace without Fabric capacity
- Expecting a DirectQuery table to support Power Query steps that don't fold
- Picking Import when the requirement says the report must show changes within seconds
- Leaving dimension tables in Import mode next to a DirectQuery fact table (Dual avoids weak relationships and extra queries)
- Expecting to switch a table back from Import to DirectQuery
Check your understanding
Practice Scenario
A semantic model reports on a 2-billion-row Sales table in Azure SQL Database. Sales figures must reflect changes within a minute. The Product, Store and Date tables are small and change rarely. Which storage modes should you use?
Log in to save your progress.
Sources
- Semantic model modes in the Power BI service, Microsoft Learn
- Use DirectQuery in Power BI Desktop, Microsoft Learn
- Manage storage mode in Power BI Desktop, Microsoft Learn
- Direct Lake overview, Microsoft Learn