3.3 Storage Modes: Import vs DirectQuery vs Live Connection
Quick answer: the short, self-contained version of this lesson is the guide Power BI Import vs DirectQuery vs Dual.
Start with a problem
Amir chose DirectQuery for the 8-row practice file "so the data is live". Every slicer click now waits for the database.
Explain simply
Import copies data into Power BI (fastest; fresh as of the last refresh). DirectQuery leaves data in the source and queries it on every interaction. Live connection connects to an existing published semantic model.
Show the data
| Scenario | Rows | Freshness needed | Model exists? |
|---|---|---|---|
| City sales training report | 8 | Daily | No |
| Ops monitor of today's orders | Millions | Minutes | No |
| City managers' reports on Rani's central model | — | As the central model | Yes |
About this data: This table uses columns: Scenario, Rows, Freshness needed, Model exists?. This table uses columns: Storage mode. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv (source: src/practice/).
Demonstrate
- Read each scenario: size, freshness and whether a model already exists.
-
City sales training report → Import (Get data › Text/CSV › Load).
Your result should look like this:
Storage mode Import; refresh up to 8 times a day on Pro (48 on PPU/capacity) -
Ops monitor → DirectQuery (Get data › SQL Server › DirectQuery).
Your result should look like this:
Storage mode DirectQuery; each visual sends SQL to the source -
City managers → Live connection (Home › Power BI semantic models).
Your result should look like this:
Status bar Connected live to the Power BI semantic model -
Composite option: keep big facts in DirectQuery and small dimensions in Dual.
Show the result
Your result should look like this (fictional sample):
| Scenario | Mode |
|---|---|
| City sales training report | Import |
| Ops monitor | DirectQuery |
| City managers' reports | Live connection |
Explain what happened
Import was the default because the data is small and daily freshness is enough. DirectQuery fits very large or near-real-time sources; Live connection reuses a central model instead of copying it.
Explore further
- Direct Lake (Microsoft Fabric) reads Delta tables in OneLake with Import-like speed.
Detailed reference for this lesson:
This is one of the most important concepts in Power BI and a very common interview question.
Import mode
Data is copied into Power BI's in-memory engine (VertiPaq), highly compressed.
- Pros: fastest performance, full DAX and Power Query features, works offline in Desktop.
- Cons: data is only as fresh as the last refresh; model size limits apply (e.g. 1 GB per model in the Service with Pro, larger with PPU/capacity); refresh takes time for big data.
DirectQuery mode
Data stays in the source. Every time a visual is displayed or filtered, Power BI sends a query to the source database.
- Pros: near real-time data; very large data volumes; data does not need to be copied.
- Cons: slower visuals (depend on source speed); some Power Query transformations and DAX functions are limited; heavy load on the source database.
Live connection
Power BI connects to an existing semantic model – a published Power BI semantic model (formerly called dataset) or an Analysis Services model. You do not build a model in your file; you only build the report on top of the shared model.
- Pros: one "single version of truth" model reused by many reports; central governance.
- Cons: you cannot change the model in Power Query (you can add report-level measures). To extend it you convert it into a composite model (Import and DirectQuery together) using DirectQuery for Power BI semantic models (shared data models that many reports use). Module 4.7 compares DirectQuery and Live connection in detail.
Composite model and Dual mode
A composite model mixes Import and DirectQuery (the data stays in the source, and every click sends a live query) tables in one model. Tables can have the storage mode Import, DirectQuery or Dual (behaves as Import or DirectQuery depending on the query). Set it in Model view › select table › Properties › Advanced › Storage mode.
Direct Lake
In Microsoft Fabric there is a newer mode called Direct Lake, which reads Delta tables from OneLake directly with Import-like speed. You will meet it only when working with Fabric lakehouses or warehouses.
| Feature | Import | DirectQuery | Live connection |
|---|---|---|---|
| Where data lives | Inside the Power BI model | In the source | In another semantic model / Analysis Services |
| Performance | Fastest | Depends on source | Depends on the remote model |
| Data freshness | As of last refresh | Near real-time | Depends on remote model |
| Power Query transformations | Full | Limited (must fold to source) | Not available |
| DAX calculated columns | Yes | Yes, with limits | No (report measures only) |
| Typical use | Most reports | Very large or real-time data | Enterprise shared models |
Rule of thumb
Start with Import unless you have a clear reason not to (data too large, real-time need, or policy that data must not be copied). Most professional Power BI reports use Import mode.
Storage modes come again in more detail in Module 4, so for now just remember the basic difference.
Storage modes चा विषय Module 4 मध्ये आणखी detail मध्ये येईल, म्हणून आत्ता फक्त basic फरक लक्षात ठेवा.
Storage modes का विषय Module 4 में और detail में आएगा, इसलिए अभी बस basic फ़र्क याद रखिए.
Ravindra Bagale's Tip
I often see learners pick DirectQuery "because the data will be live" and then suffer slow visuals on small datasets. Start with Import unless there is a clear reason (very large data, a near real-time need, data that must stay at the source). You can explain the choice in interviews with this reasoning. Keep this in mind!
Ravindra Bagale's Tip – मराठी
मला खूप वेळा दिसतं की learners "data live राहील" म्हणून DirectQuery निवडतात आणि मग छोट्या datasets वर slow visuals चा त्रास सहन करतात. स्पष्ट कारण नसेल (खूप मोठा data, जवळजवळ real-time ची गरज, data source वरच राहिला पाहिजे) तर Import ने सुरुवात करा. Interview मध्ये हीच कारणं देऊन तुमची निवड समजावू शकता. लक्षात ठेवा!
Ravindra Bagale's Tip – हिंदी
मैं अक्सर देखता हूँ कि learners "data live रहेगा" सोचकर DirectQuery चुनते हैं और फिर छोटे datasets पर slow visuals झेलते हैं. जब तक कोई साफ़ वजह न हो (बहुत बड़ा data, लगभग real-time की ज़रूरत, data का source पर ही रहना ज़रूरी), Import से शुरू करो. Interview में इसी तर्क से अपनी पसंद समझा सकते हो. ध्यान रखना!
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Slow visuals on a tiny dataset | DirectQuery chosen for "live" data | Use Import |
| Cannot edit Power Query | Live connection to a semantic model | Expected; use report measures or a composite model |
| Transformation not supported error | DirectQuery step does not fold | Move the step to the source or use Import |
Practise independently
Practice task
An order arrives at 07:05 IST. The Import model refreshes at 07:00 and 13:00. When does the order appear in the report? With DirectQuery?
Hints
- Import shows data as of the last refresh.
Solution
Import: after the 13:00 refresh. DirectQuery: on the next visual interaction after 07:05 (near real time).
Think like a professional
Start with Import unless there is a clear reason (size, freshness, policy). Write the reason down when you choose otherwise.
Revision card
Interview card
Interview card
Comparison card
Comparison card