Black Friday50% off your first month on any monthly plan with EARLY50 See plans
All 35 lessons
  1. Start here: cloud, data, Fabric and Power BI basics

    1. 1. Start here: the exam, the plan and what you already know
    2. 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
    3. 3. Data basics: files, tables, formats and the lakehouse idea
    4. 4. A tour of Microsoft Fabric: OneLake, workspaces and items
    5. 5. Power BI in plain words: semantic models, reports and DAX
  2. Prepare data: get data in (exam area 2, 45-50%)

    1. 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
    2. 7. Choosing a data store: lakehouse, warehouse or eventhouse
    3. 8. Use data without copying: shortcuts, mirroring and OneLake integration
    4. 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
    5. 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
    6. 11. Messy data: duplicates, missing values and nulls
  3. Prepare data: transform and query (exam area 2)

    1. 12. Views, functions and stored procedures in T-SQL
    2. 13. Build a star schema in a lakehouse or warehouse
    3. 14. Aggregate, join and enrich: SQL and PySpark side by side
    4. 15. Query with the Visual query editor and SQL
    5. 16. Query with KQL
    6. 17. Query with DAX: DAX query view and EVALUATE
  4. Design and build semantic models (exam area 3, 25-30%)

    1. 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
    2. 19. Relationships: star schemas, bridge tables and many-to-many
    3. 20. DAX foundations: measures, filter context, CALCULATE and variables
    4. 21. DAX functions the exam tests: iterators, table filters, windows and information functions
    5. 22. Calculation groups, dynamic format strings and field parameters
    6. 23. Composite models and large semantic model storage format
  5. Optimize models, then secure them (exam areas 3 and 1)

    1. 24. Direct Lake in depth: fallback, refresh and the two flavors
    2. 25. Incremental refresh for semantic models
    3. 26. Faster reports and DAX: finding and fixing slow queries
    4. 27. Who can see what: workspace roles and item permissions
    5. 28. Fine-grained security: rows, columns, objects and files
    6. 29. Governance: sensitivity labels and endorsement
  6. Lifecycle, then exam practice (exam area 1)

    1. 30. Version control: Git integration and Power BI projects (.pbip)
    2. 31. Deployment pipelines and impact analysis
    3. 32. XMLA endpoint and reusable assets: .pbit, .pbids and shared semantic models
    4. 33. Mock exam 1
    5. 34. Final review and exam day: every decision on one page
    6. 35. Mock exam 2
See the leaderboard

20 min

Choose a storage mode: Import, DirectQuery, Direct Lake and Dual

Pick the right storage mode for a semantic model table from size, freshness and source.

By the end of this lesson you can

  • Explain how Import, DirectQuery, Direct Lake and Dual tables answer a query
  • Name the cost of each mode: refresh, latency, size limits and licensing
  • Choose a storage mode from the one constraint a scenario gives you

From earlier lessons · 1 of 2

Juniper Coffee's finance team owns an Import semantic model. A data engineer wants its tables as Delta tables in OneLake, without building a new pipeline. Which feature writes them there?

Pick one answer.

From earlier lessons · 2 of 2

A store manager clicks a slicer in a Power BI report. Which item receives the DAX query that the visual sends?

Pick one answer.

Juniper Coffee's till sales now fill a gold Lakehouse table: 900 million rows and growing. Store managers want today's numbers by lunchtime. Finance wants fast visuals at month end. The supplier price list changes once a month. One semantic model, four kinds of data. Each table needs a storage mode, and the wrong pick means stale numbers or slow reports.

[6]

What a storage mode is

Exam depth: know it cold.

A storage mode is a property of each table in a semantic model. It decides where the table's data lives and which engine answers queries about it.

  • Import and Direct Lake tables are answered by VertiPaq, the in-memory column engine inside every semantic model.
  • DirectQuery tables are answered by the source. The model translates each DAX query into the source's language, such as SQL.
  • Dual tables can do either.

A model whose tables use more than one storage mode is a composite model. Lesson 23 covers those.

[45][200]

Rendering diagram…
Same DAX query, four routes. Import and Direct Lake answer from memory; DirectQuery asks the source; Dual picks per query.

[45][200]

Import: a compressed copy in memory

Exam depth: know it cold.

Import copies the data into the model. VertiPaq compresses it, often about 10 times, and queries run from memory. It's usually the fastest mode, and it's the default in Power BI Desktop.

The costs:

  • Refresh. Data is only as current as the last refresh. Scheduled refresh allows 8 a day on shared (Pro) capacity and 48 a day on Premium or Fabric capacity.
  • Size. A Pro workspace caps a model at 1 GB. Premium Per User allows 100 GB. On a Fabric capacity the limit depends on the SKU, for example 25 GB on F64. Past 1 GB you turn on large semantic model storage format (lesson 23).
  • Memory. The whole model loads into memory. A full refresh needs room for a second copy.

In return you get every Power Query transform and every DAX function.

[201][202][203][204]

[201]

DirectQuery: ask the source every time

Exam depth: know it cold.

DirectQuery keeps no copy. The model stores only metadata, the table and column definitions. Each visual sends a native query, such as SQL, to the source.

When to use it:

  • The data is too large to import, even after you trim it.
  • Users need near real-time data, fresher than scheduled refresh allows.

The costs:

  • Queries are slower. Speed depends on the source, and every report user adds load to it.
  • Power Query steps and DAX are limited to what the source can translate. Calculated tables aren't DirectQuery; they're stored as Import.

Import size limits don't apply, and no scheduled refresh is needed.

[200][201]

You are an analytics engineer at Juniper Coffee. The supplier price list is a 40 MB table that changes once a month. Finance wants the fastest possible visuals and full Power Query transforms. Which storage mode should the table use?

Pick one answer.

[201]

Direct Lake: memory speed, no copy

Exam depth: know it cold.

Direct Lake reads Delta tables in OneLake straight into VertiPaq. Nothing is imported ahead of time. When a query needs a column, the model loads just that column into memory.

  • Speed is close to Import, because VertiPaq answers the queries.
  • Refresh is framing: the model reads only the Delta tables' metadata and points at the latest files. It takes seconds, not hours.
  • Freshness: the setting Keep your Direct Lake data up to date is on by default. It reframes when the Delta tables change.
  • License: Direct Lake needs a Fabric capacity (an F SKU, or a Premium P SKU). Import and DirectQuery work with any license.

Data prep happens upstream, in the lake. Direct Lake comes in two flavors with different fallback rules. Lesson 24 covers both in depth.

[45][198]

Import copies everything overnight. Direct Lake just updates the bookmark. Picture: Classic two-panel reaction meme, cartoon style.

Dual: both at once, chosen per query

Exam depth: know it cold.

A Dual table is stored as Import and can act as DirectQuery. For each query, the model uses the cached copy when it can and the source when it can't.

Use it for dimension tables, such as Store or Date. It pays off next to a big DirectQuery fact table from the same source:

  • A slicer listing stores reads the cached copy, so it's fast.
  • A visual joining Store to DirectQuery Sales sends one SQL query that joins both at the source.
  • It keeps relationships between DirectQuery and Import tables regular, not limited (lesson 23).

Dual tables have DirectQuery's limits on transforms and DAX. You can change a DirectQuery table to Import or Dual, but not back to DirectQuery.

[200][201]

You are an analytics engineer at Juniper Coffee. Sales is a DirectQuery table in Azure SQL Database. Store, from the same database, feeds slicers and relates to Sales and to an Import Targets table. Slicers must load fast. Which mode should Store use?

Pick one answer.

[200][201]

The four modes side by side

Exam depth: know it cold.

This table is the core of the lesson. Learn its rows.

QuestionImportDirectQueryDirect LakeDual
Where is the data?Copy in the model.In the source.Delta tables in OneLake.Copy, plus the source.
Who answers?VertiPaq.The source.VertiPaq.VertiPaq or the source.
How fresh?Last refresh.Latest.Last framing (automatic by default).Last refresh, or latest.
Refresh costFull data copy.None.Metadata only, seconds.Data copy.
Query speedFastest.Slowest.Close to Import.Fast when cached.
LicenseAny.Any.Fabric capacity only.Any.
TransformsAll Power Query and DAX.Limited.Done upstream in the lake.Limited.

Decision rule: data already in OneLake Delta tables on a Fabric capacity, choose Direct Lake. Must stay in a non-Fabric source or be near real-time, choose DirectQuery. Small or slow-changing, or a self-service analyst who needs Power Query, choose Import. A dimension shared with DirectQuery tables, choose Dual.

[45][201]

Match

Match each constraint in a question to the storage mode it points to.

Pick an item on the left, then its match on the right.

[45][200][201]

[45][200]

Try it in Fabric

A free Fabric trial is enough.

See a table's storage mode in Power BI Desktop:

  1. Open Model view and select a table.
  2. In the Properties pane, expand Advanced, then expand Storage mode.

Create a Direct Lake model:

  1. Open a Lakehouse, then switch to its SQL analytics endpoint.
  2. Select New semantic model.
  3. Name it, pick a workspace, pick a Direct Lake option under Storage (lesson 24 explains the two), and tick a few tables.
  4. Open the model and check a table's storage mode: it shows Direct Lake.

[199][200]

Exam-style practice

You are a BI developer at Juniper Coffee. Store managers need till sales from Azure SQL Database with changes visible within a minute. Security policy says the data must stay in the database and not be copied into Fabric. Which storage mode should the Sales table use?

Pick one answer.

[45][201]

You are an analytics engineer at Juniper Coffee. Two billion rows of till sales sit in Delta tables in a gold Lakehouse on an F64 capacity. Finance wants Import-like speed, but the nightly Import refresh no longer fits its window. What should you do?

Pick one answer.

[45]

You are a data analyst at a small bakery group. Everyone has Power BI Pro, and there's no Fabric capacity. The budget workbook is 20 MB and changes weekly. Users want the fastest visuals. Which storage mode fits?

Pick one answer.

[45][202]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 13

[45][198][200][201][202][204]

Worth keeping

  1. Import: fastest queries, but only as fresh as the last refresh, and size-capped by license.
  2. DirectQuery: always current and no copy, but slower and limited in DAX and Power Query.
  3. Direct Lake: VertiPaq speed over OneLake Delta tables, seconds-long framing, Fabric capacity only.
  4. Dual: dimensions that serve both cached and DirectQuery tables from one source.
  5. Find the deciding phrase: near real-time, no copy, huge data in OneLake, no refresh window, Pro only.

On the job

At Juniper Coffee, the analytics engineer builds the main sales model with Direct Lake over the gold Lakehouse. Data engineers own the Spark jobs that shape those tables, so business logic lives in one place.

A finance analyst then wants a planning sheet in the model. They can't write to the Lakehouse, so that one table comes in as Import with Power Query. A live store-operations dashboard reads the till database with DirectQuery, with Store and Date in Dual. Each table gets the mode its data and its users need. A good model mixes modes on purpose, never by accident.

[45]

You now know where each table's data lives. Next, how the tables connect: star schemas, bridge tables and the many-to-many relationships that trip up so many candidates.

Leaderboard

Loading…

50 XP for each lesson you finish, 10 for each right answer. Names show only for learners who turn on Show me on leaderboards.

Sources for this lesson (9)

Each fact in the lesson is marked with the number of the page it comes from.

  1. [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
  2. [45]Microsoft Learn: Direct Lake overview
  3. [198]Microsoft Learn: How Direct Lake works
  4. [199]Microsoft Learn: Develop Direct Lake semantic models
  5. [200]Microsoft Learn: Table storage mode in Power BI semantic models
  6. [201]Microsoft Learn: Semantic model modes in the Power BI service
  7. [202]Microsoft Learn: Power BI Premium Per User
  8. [203]Microsoft Learn: What is Power BI Premium?
  9. [204]Microsoft Learn: Large semantic models in Power BI Premium