Semantic model storage modes: Import, DirectQuery
A storage mode is a property of each table in a Power BI or Fabric semantic model. It decides whether the data is copied into the model’s memory (Import), left in the source and queried live (DirectQuery), read from Delta tables in OneLake on demand (Direct Lake), or either of the first two depending on the query (Dual). The decision rule for exams: pick the mode from the constraint in the scenario. “Fastest visuals, data can be hours old” is Import. “Must be current, or too big to copy, source is a database” is DirectQuery. “Large data already in a Fabric lakehouse or warehouse, no long refresh” is Direct Lake.
What happens when a visual loads
A report visual sends a DAX query to the semantic model. What the model does next depends on the storage mode of the tables involved, according to Microsoft’s Direct Lake overview:
- Import and Direct Lake are answered by the in-memory VertiPaq engine.
- DirectQuery translates the DAX into the source’s own language, usually SQL, and runs it on the source database every time.
That single difference explains most of the trade-offs: Import and Direct Lake are normally faster to interact with, and DirectQuery is always as current as the source but only as fast as the source is.
The four modes and composite models side by side
| Mode | Where the data lives | How it gets fresh | Pick it when |
|---|---|---|---|
| Import | Copied into the model | Scheduled or manual refresh; incremental refresh for big tables | Speed matters, any connector, latency of hours is fine |
| DirectQuery | Stays in the source | Every query reads the source | Near real-time data, or a source too large to import |
| Dual | Cached and queryable live | Refreshed like Import, but can also act as DirectQuery | Dimension tables next to a DirectQuery fact table |
| Direct Lake | Delta tables in OneLake, columns paged into memory when queried | A refresh only updates metadata (framing), takes seconds | Big Fabric lakehouse or warehouse data, import-like speed |
| Composite | A model with tables in more than one mode | Per table | Mixing a large live fact table with small cached tables |
Microsoft describes a Direct Lake refresh as analysing the metadata of the latest Delta table version and updating references to the latest files, whereas an Import refresh produces a copy of the data. Direct Lake needs a Fabric capacity (an F SKU; Power BI Premium P SKUs also qualify but are being retired); Import and DirectQuery work with any Fabric or Power BI licence.
Why Dual exists
Suppose Sales (millions of rows) stays in DirectQuery and a small SurveyResponse table is imported. Relationships between a DirectQuery table and an Import table are “limited”: the join cannot be pushed to the source. Setting the shared dimension tables (Date, Customer, Geography) to Dual avoids that, because a Dual table can behave as cached or as DirectQuery. Per Microsoft Learn, a query that touches only Dual tables is served from the cache. A query that mixes a Dual dimension with a DirectQuery fact table goes to the source, because pushing the aggregation down is cheaper than pulling every row.
Two details exam writers use. First, Dual tables carry the same functional constraints as DirectQuery tables, such as limited M transformations and restricted DAX in calculated columns. Second, you can change a DirectQuery table to Import or Dual, but you cannot set it back to DirectQuery afterwards; the exceptions are Power BI web modeling and live editing in Power BI Desktop, where version history can reverse the change.
Direct Lake and its two flavours
Direct Lake comes in two options, both described on the Direct Lake overview page:
| Direct Lake on OneLake | Direct Lake on SQL analytics endpoint | |
|---|---|---|
| Sources | One or more Fabric items with Delta tables | A single Fabric source with a SQL analytics endpoint |
| Falls back to DirectQuery | No | Yes, for example when a table is based on a SQL view or the endpoint enforces row-level security |
| Mix with Import tables | Yes | Not in the same model |
Fallback keeps reports working but slower, and the Direct Lake behavior property controls it. Capacity guardrails also matter: if the guardrails for the SKU are exceeded, Direct Lake on OneLake behaves like Import (refresh fails and the model cannot be queried until the Delta tables are optimised), while the SQL endpoint flavour falls back to DirectQuery if fallback is enabled. The guardrail numbers differ per SKU, so check the table on the Microsoft page rather than memorising them. Direct Lake pairs naturally with the gold layer of a medallion architecture, and it works best on well-maintained Delta tables.
A worked example
A retailer has a 2-billion-row Sales table in a Fabric lakehouse, a small Stores table, and a Targets spreadsheet owned by finance. A sound design: Sales as Direct Lake (too big to import, already Delta, no refresh window), Stores as Direct Lake from the same lakehouse, and Targets as an Import table added from Excel, since the analyst cannot write to the lakehouse and Import supports Power Query. That is a composite model that Microsoft supports with Direct Lake on OneLake plus Import. Row-level security should then be defined in the semantic model; see row-level security for how that interacts with each mode.
Which exams test this
| Exam | How the topic appears | On this site |
|---|---|---|
| DP-600 | Implement and manage semantic models (25–30%): choose a storage mode, design and build composite models, configure the large semantic model storage format, configure Direct Lake including fallback and refresh behavior, and choose between Direct Lake on OneLake and Direct Lake on SQL analytics endpoint | Semantic models explained |
| PL-300 | Prepare the data (25–30%): choose between Direct Lake, DirectQuery and Import when connecting to data. Dual, composite models and the two Direct Lake flavours are not named in this outline | Prepare the data explained |
How exam questions are built around it
The constraint word decides. “Real-time” or “current to the minute” points away from Import. “Without copying the data” or “billions of rows” points to DirectQuery or Direct Lake. “Fabric capacity” and “Delta tables in OneLake” point to Direct Lake. “Fastest” with an unrestricted connector points to Import.
Common traps: choosing Direct Lake for a non-Fabric source (it reads only Delta tables in OneLake, so an on-premises SQL Server needs Import, DirectQuery or mirroring first); assuming Direct Lake on SQL never uses DirectQuery (views and SQL-level security trigger fallback); and forgetting that Dual is a performance optimisation for composite models, not a freshness setting. Another recurring one is a refresh that “takes too long” for a large table: the answer is incremental refresh for Import, or Direct Lake, not a bigger capacity.
Practice questions
Question 1. A model has a very large Sales fact table in an Azure SQL database that must reflect changes within seconds, and small Date and Customer tables. Reports are slow because relationships between the fact table and imported dimensions are limited. What should you do with the dimension tables?
- A. Convert them to Direct Lake
- B. Set them to DirectQuery
- C. Set them to Dual
- D. Remove the relationships and filter with DAX
Show answer
Answer: C
Dual tables can be served from the cache or pushed to the source, so relationships with a DirectQuery fact table stop being limited and joins can run in the source. Making the dimensions DirectQuery loses the cached slicer speed, Direct Lake needs Delta tables in OneLake and does not apply to an Azure SQL source, and disabling relationships breaks filtering.
Question 2. A semantic model uses Direct Lake on SQL analytics endpoint. One table is built on a SQL view in the lakehouse endpoint, and users report that queries on that table are slower than on the others. What is the most likely cause?
- A. Incremental refresh is not configured
- B. Queries on the view fall back to DirectQuery
- C. The model has no Fabric capacity
- D. The view was converted to an Import table
Show answer
Answer: B
With Direct Lake on SQL analytics endpoint, a table based on a SQL view cannot be read directly from Delta files, so queries fall back to DirectQuery, which is slower than VertiPaq. Incremental refresh is an Import feature and does not apply here, the Fabric capacity is already required for Direct Lake, and the model does not silently convert views into Import tables.