Import vs DirectQuery
Power BI · Getting Data
Theory
Every table has a storage mode. In Import mode, Power BI copies the data into its compressed in-memory engine (VertiPaq) inside the model; visuals are very fast and all DAX and Power Query features are available, but data is only as fresh as the last refresh. In DirectQuery mode, no rows are stored; each time a visual renders, Power BI sends a query to the source, so results are current but speed depends on the source, and some transformations and DAX functions are limited.
For example, a Sales table of a few million rows that changes nightly is ideal for Import with a daily refresh. A live order-tracking table of several billion rows in a cloud warehouse, where managers need figures from the last few minutes, is a candidate for DirectQuery. You choose the mode when connecting and can see it per table in Model view (Properties → Storage mode).
Why we use it
The storage mode is the biggest design decision for performance and freshness. Picking the wrong one leads to slow reports or stale numbers, and it is a favourite interview and PL-300 question.
When to use it
Default to Import: it is fastest and most flexible. Choose DirectQuery when data is too large to import, must be near real time, or must stay in the source for governance. Mixing both in one model is a composite model.
How it works
- Connecting to a source
- Is data too large to import or needed in near real time? Yes: Choose DirectQuery and optimise the source. No: Choose Import.
- Import chosen: will it refresh often enough? Yes: Set a scheduled refresh. No: Consider incremental refresh or DirectQuery.
- Check storage mode per table in Model view
- Model meets speed and freshness needs
Syntax
Connect → Data connectivity mode: Import | DirectQuery
Model view → select table → Properties → Advanced → Storage mode: Import | DirectQuery | DualExample
Decision for the sample model:
1. Customers and Products: small, change rarely → Import.
2. Sales history: a few million rows, updated nightly → Import with scheduled refresh.
3. A live 'OrderStatus' table updated every minute in the warehouse → DirectQuery.
4. Because the model now mixes modes, it is a composite model; set Customers and Products to Dual so they can join efficiently with both.Key points
- Import = fastest visuals, data as fresh as the last refresh
- DirectQuery = live queries; speed depends on the source
- DirectQuery limits some Power Query steps and DAX features
- Dual mode lets a table act as Import or DirectQuery as needed
- Large imports can use incremental refresh instead of DirectQuery