2.1.3. Choosing a Storage Mode: Import, DirectQuery, and Direct Lake
💡 First Principle: A storage mode answers one question: where does the data live when a user clicks a visual? Import keeps a compressed copy inside Power BI; DirectQuery leaves data at the source and asks for it on every interaction; Direct Lake reads Delta tables in OneLake directly into the engine's memory without a full copy. Freshness, speed, size, and feature support all follow from that answer.
Import loads a compressed copy into the VertiPaq engine. Queries are fastest, every Power Query transformation and DAX function is available, and any license works. The costs: data is only as current as the last refresh, the model must fit size limits for your license and capacity, and large refreshes take time.
DirectQuery stores only metadata; each visual sends queries to the source. Data is near real-time and can exceed memory limits, and the source can enforce its own security through single sign-on. The costs: performance depends on the source (Microsoft's target is visuals under about 5 seconds), any query returning more than 1,000,000 rows fails (Premium can raise this), Power Query steps must fold into a single native query, calculated columns are limited to row-level expressions that fold, there's no automatic date hierarchy, and every user action generates source load.
Dual is a per-table setting in composite models: the table behaves as Import or DirectQuery depending on the query, which is typically used for dimension tables shared by Import and DirectQuery facts so they don't force slow cross-mode joins.
Direct Lake (Microsoft Fabric) loads columns from Delta tables in OneLake into memory on demand, giving Import-like VertiPaq performance without copying the data in a refresh. Its "refresh" is framing, a metadata operation that points the model at the latest Delta files and typically completes in seconds. It requires a Fabric capacity (an F SKU, or a legacy Power BI Premium P SKU, which Microsoft is retiring; Pro and PPU workspaces can't host it). Two flavors exist: Direct Lake on OneLake, which can use tables from multiple Fabric sources and does not fall back to DirectQuery (queries error if guardrails are exceeded), and Direct Lake on SQL endpoints, which uses a single source's SQL analytics endpoint and can fall back to DirectQuery (for example, when a table is a SQL view or guardrails are exceeded), controlled by the model's Direct Lake behavior property.
| Factor | Import | DirectQuery | Direct Lake |
|---|---|---|---|
| Data location at query time | Compressed copy in model | Source system | Delta tables in OneLake, paged into memory |
| Query performance | Fastest | Depends on source | Import-like |
| Freshness | As of last refresh | Near real-time | As of last framing (seconds to run) |
| Refresh cost | Full or incremental data copy | None | Metadata only |
| Power Query / DAX support | Full | Restricted (folding, row-level calc columns) | Limited (no Power Query; calc column support restricted) |
| License / platform | Any | Any | Fabric capacity (F SKU or legacy P SKU) |
| Typical scenario | Most models | Huge or rapidly changing source; source-enforced security | Large Fabric lakehouse or warehouse data |
⚠️ Exam Trap: "Ensure reports show data within seconds of changes in a Fabric lakehouse while keeping Import-level performance" points to Direct Lake, not DirectQuery. And Direct Lake on OneLake won't silently fall back to DirectQuery: if a scenario depends on fallback behavior for SQL views, it's describing Direct Lake on SQL endpoints.
Reflection Question: Why does a DirectQuery model become slower as more users open the report, while an Import model serving the same users doesn't put any extra load on the source database?
