Storage Modes: Import, DirectQuery, and Direct Lake

Quick Answer: Import copies data into the model (fastest but needs refresh). DirectQuery queries the source live (real-time but slower). Direct Lake reads from OneLake files (near-Import speed, no refresh needed, Fabric only). Composite models mix Import and DirectQuery in one model for flexibility.

Choosing the right storage mode is one of the most critical decisions in Power BI development. The PL-300 tests this topic extensively because it impacts performance, data freshness, model size, and licensing.

Import Mode (In-Memory)

Import mode is the default and most common storage mode. Data is copied from the source into the Power BI model's in-memory columnar engine (VertiPaq).

How Import Works

1. Power BI connects to the data source
2. Data is loaded and compressed into the VertiPaq engine
3. The connection to the source is closed
4. All queries are resolved from the in-memory cache
5. Data must be refreshed to pick up changes at the source

Import Mode Advantages

• Fastest query performance — all data is in memory
• Full DAX functionality — all DAX functions are supported
• Offline capability — reports work without source connectivity
• Query folding — transformations can be pushed to the source during load
• Compression — VertiPaq typically achieves 10:1 compression

Import Mode Limitations

When to Use Import

• Datasets under 1 GB (compressed)
• Data that changes on a predictable schedule (daily, hourly)
• Reports requiring complex DAX calculations
• Scenarios where query performance is the top priority
• Data sources with slow query performance

DirectQuery Mode

DirectQuery sends queries to the underlying data source every time a user interacts with a report. No data is stored in the Power BI model.

How DirectQuery Works

1. User interacts with a report visual
2. Power BI translates the visual's data requirements into a source query (SQL, etc.)
3. The query is sent to the data source
4. Results are returned and rendered in the visual
5. No data is cached (each interaction generates new queries)

DirectQuery Advantages

• Real-time data — always shows current data from the source
• No model size limits — data stays at the source
• No refresh needed — eliminates refresh scheduling and failures
• Suitable for large datasets — works with multi-terabyte sources

DirectQuery Limitations

When to Use DirectQuery

• Data changes frequently and near-real-time visibility is required
• Datasets are too large to import (multi-terabyte)
• Corporate policies prevent data duplication
• The source database is well-optimized for analytical queries
• Row-level security must be enforced at the source level

Direct Lake Mode

Direct Lake is the newest storage mode, available exclusively with Microsoft Fabric (F or P SKUs). It reads data directly from Parquet/Delta files in OneLake.

How Direct Lake Works

1. Data is stored as Parquet/Delta files in a Fabric Lakehouse or Warehouse
2. Power BI reads the data directly from OneLake (no copy, no query translation)
3. Data is loaded into memory on-demand, similar to Import
4. If data exceeds available memory, the engine falls back to DirectQuery
5. Data freshness is automatic when source files are updated

Direct Lake Advantages

• Near-Import performance without copying data
• Automatic data freshness — no scheduled refresh needed
• Large dataset support — handles much larger datasets than Import
• Reduced storage costs — data stays in OneLake (one copy)
• Full DAX support — same capabilities as Import mode

Direct Lake Limitations

When to Use Direct Lake

• Organization uses Microsoft Fabric
• Data is already in a Fabric Lakehouse or Warehouse
• Need Import-like performance without refresh overhead
• Dataset is too large for Import but too demanding for DirectQuery

Composite Models

Composite models allow mixing Import and DirectQuery tables in a single model:

• Import small dimension tables for fast filtering
• Use DirectQuery for large fact tables that change frequently
• Create aggregation tables (imported) that speed up high-level queries
• DirectQuery tables handle detail-level drill-through

Dual Storage Mode

Tables set to Dual mode can function as either Import or DirectQuery depending on the query context:

• When joined with Import tables → acts as Import
• When joined with DirectQuery tables → acts as DirectQuery
• Automatically optimized by the engine

Decision Framework

$\text{Storage Mode} = f(\text{Data Size}, \text{Freshness Needs}, \text{Source Performance}, \text{Licensing})$

On the Exam

The PL-300 frequently tests:

• Choosing the correct storage mode for a given scenario
• Understanding the performance implications of each mode
• Knowing the limitations of DirectQuery (DAX restrictions, no calculated tables)
• Recognizing when Direct Lake is the appropriate choice
• Understanding composite model architecture and dual storage mode