Launch Deal · 50% off 1st month ·

1.2 — Choose Between Import, DirectQuery and Direct Lake

PL-300 Domain 1 · Skill 1.2

What You Need to Know

The storage mode decides where a table's data lives and when queries reach the source. You set it per table in Model view (Properties > Advanced > Storage mode).

The three modes

Import DirectQuery Direct Lake
Where the data is Compressed copy in the semantic model (VertiPaq) Stays in the source. Every visual sends a query Delta tables in OneLake, loaded into memory on demand
Freshness As of the last refresh Current (within the visual's cache) Current after framing, which only reads metadata and is near-instant
Query speed Fastest Depends on the source Close to Import
Power Query Full Only steps that fold to the source None. Shaping is done upstream in Fabric
DAX Full Most functions, some calculated column limits Full measures. Calculated columns and tables over Direct Lake tables are limited
Model size Limited by capacity (1 GB per model on Pro) Not limited by the model Limited by the capacity's guardrails
Typical use Most reports Huge or fast-changing data, or data that must stay in the source Large Fabric lakehouse or warehouse data

Choosing

  • "Data changes every minute and users must see it": DirectQuery (optionally with automatic page refresh, lesson 3.7).
  • "Data is too large to import" or "can't leave the source for compliance": DirectQuery.
  • "Data is already in Delta tables in a Fabric lakehouse or warehouse, and you want Import-like speed without scheduled refresh": Direct Lake.
  • Otherwise: Import, which gives the best performance and every feature.

DirectQuery limits worth remembering

  • A query that returns more than 1 million rows from the source fails (for example, an unfiltered table visual over a large table).
  • Power Query transformations that can't fold are not allowed.
  • Performance depends on source indexes and load. Each visual can send several queries.
  • Single sign-on and RLS in the source are possible with some sources.

Direct Lake details

  • Available only in Fabric (a Fabric capacity or trial).
  • Direct Lake on SQL endpoint can fall back to DirectQuery when a query uses a SQL view, SQL-level security, or the model exceeds the capacity's guardrails. Direct Lake on OneLake reads the Delta tables directly and doesn't fall back.
  • After the lakehouse or warehouse loads new data, the model's reframing (automatic or on refresh) points it at the latest Delta table version.

Composite models and Dual mode

A composite model mixes storage modes, for example a DirectQuery fact table with Import dimensions.

  • Dual mode lets a table act as Import or DirectQuery depending on the query. Set dimension tables to Dual when they relate to DirectQuery fact tables. Queries that only touch the dimension (like a slicer) use the cached copy, and joins with the fact table can still run in the source.
  • Switching a table from Import to DirectQuery isn't possible. Switching from DirectQuery or Dual to Import is, but it can't be undone.

Key Concept: Import trades freshness for speed and features. DirectQuery trades speed for freshness and scale. Direct Lake gives near-Import speed over Fabric Delta tables without a data copy, but only in Fabric.

Exam Traps

  • Choosing Direct Lake for data that isn't in OneLake Delta tables, or for a workspace without Fabric capacity
  • Expecting a DirectQuery table to support Power Query steps that don't fold
  • Picking Import when the requirement says the report must show changes within seconds
  • Leaving dimension tables in Import mode next to a DirectQuery fact table (Dual avoids weak relationships and extra queries)
  • Expecting to switch a table back from Import to DirectQuery

Check your understanding

Practice Scenario

A semantic model reports on a 2-billion-row Sales table in Azure SQL Database. Sales figures must reflect changes within a minute. The Product, Store and Date tables are small and change rarely. Which storage modes should you use?

Report issue

Log in to save your progress.