Direct Lake vs Import vs DirectQuery in Power BI: Practical Patterns for Mixed-Mode Architectures

1. Quick recap: what actually changes between the modes?

Import

• Data is loaded into the VertiPaq in-memory engine.
• Queries hit the local model, not the source.
• Best for:
  • Complex DAX and heavy aggregations.
  • Small-to-medium models that can refresh within SLAs.
  • Scenarios where "last 15–60 minutes" latency is acceptable.

DirectQuery

• No data stored in the model (besides calculated tables/columns).
• Each visual triggers SQL (or equivalent) directly against the source.
• Best for:
  • Near real-time reporting when you can’t use streaming.
  • Strict data residency/security rules that forbid data duplication.
  • Very large datasets where Import is not feasible.

Direct Lake (Fabric)

• Power BI queries Delta tables in OneLake directly, but still uses the VertiPaq engine.
• No scheduled refresh; data is visible as soon as it lands in the Delta table.
• Best for:
  • Fabric-based lakehouse/warehouse architectures.
  • Large fact tables with frequent updates.
  • Scenarios where you want Import-like performance with near real-time freshness.

Key mental model:

• Import = snapshot, fast, needs refresh.
• DirectQuery = live, slower, depends on source.
• Direct Lake = live-ish from the lake, fast when used correctly.

2. Core mixed-mode patterns you’ll actually use

Pattern 1: Direct Lake fact + Import dimensions

When to use it

• Fact table is huge and updated very frequently (events, transactions, telemetry).
• Dimension tables are relatively small and stable (products, customers, calendars).
• You are on Microsoft Fabric and can store fact data as Delta tables in OneLake.

Architecture

• Fact table(s): Direct Lake from Lakehouse/Warehouse.
• Dimension tables: Import from the same Lakehouse/Warehouse (or another source).

Benefits

• Fact is always up-to-date without refresh.
• Dimensions can be enriched, cleaned, and slowly refreshed.
• You keep the model size manageable.

Gotchas

• Relationships must be single-direction and star-schema friendly.
• Avoid complex calculated columns that force materialization on the Direct Lake table.
• Monitor query plans; badly written measures can still kill performance.

Example measure pattern

Total Sales Amount = 
SUMX(
    'FactSales',
    'FactSales'[Quantity] * 'FactSales'[UnitPrice]
)

Keep measures set-based and avoid row-by-row lookups into large Direct Lake tables.

Pattern 2: Hybrid real-time – DirectQuery for latest data + Import history

When to use it

• You need near real-time for "today" but historical data doesn’t change.
• Source is a SQL database (on-prem or cloud) and you can’t move everything to Fabric yet.

Architecture

• FactHistory: Import (e.g., all data until yesterday).
• FactToday: DirectQuery (e.g., today’s partition from the OLTP database).
• Dimensions: Import.

Implementation steps (high level)

1. Create two tables:
   • FactSales_History (Import) – from a warehouse or snapshot view.
   • FactSales_Today (DirectQuery) – from the operational database.
2. Ensure identical schemas (same columns, data types, keys).
3. Create a calculated table to union them:

FactSales_Combined = 
UNION(
    FactSales_History,
    FactSales_Today
)

4. Hide the base tables and use only FactSales_Combined in your model.

Benefits

• Most queries hit the in-memory history.
• Only the latest slice uses DirectQuery.
• You can tune DirectQuery for a small, recent subset of data.

Gotchas

• Calculated table is Import – it will only update on model refresh.
• For true real-time, you need to refresh the model frequently enough.
• If "today" holds a lot of data, DirectQuery may still be slow.

Alternative: Use composite models with dual-mode dimensions and avoid the union table if you can model by date filter (e.g., separate pages for real-time vs historical).

Pattern 3: Direct Lake + DirectQuery side-by-side

When to use it

• Core analytics data lives in Fabric (Direct Lake).
• You need to join to a system that you can’t move or copy (e.g., legacy ERP, SaaS).

Architecture

• Fact tables: Direct Lake from Fabric Lakehouse or Warehouse.
• Core dimensions: Import or Direct Lake.
• External dimension or fact: DirectQuery from external SQL or other supported source.

Typical use case

• You have a central customer table in Fabric.
• You need to enrich it with credit status or risk score from an external system that must stay in its own DB.

Implementation tips

• Use composite models: mix Direct Lake and DirectQuery tables.
• Prefer relationships where the Direct Lake side is the fact and DirectQuery is a small dimension.
• Push as much logic as possible into the external source (views, computed columns) instead of heavy DAX.

Example: mapping Direct Lake customer to DirectQuery credit status

-- In the external SQL DB, create a simplifying view
CREATE VIEW vwCustomerCreditStatus AS
SELECT
    CustomerId,
    CASE 
        WHEN CreditScore >= 750 THEN 'Low Risk'
        WHEN CreditScore >= 600 THEN 'Medium Risk'
        ELSE 'High Risk'
    END AS CreditRiskBand
FROM dbo.CustomerCredit;

Then connect Power BI to vwCustomerCreditStatus via DirectQuery and relate it to your Direct Lake DimCustomer.

Gotchas

• Cross-source joins can generate complex query plans.
• Some DAX features are restricted in composite models, especially with DirectQuery.
• You must be careful with security – RLS across different sources adds complexity.

Pattern 4: Import model with Direct Lake "escape hatch" for detail

When to use it

• Analysts mostly need aggregated data.
• Occasionally they need to drill through to full transaction-level detail.
• Full detail is too big to import but can live in Fabric as a Direct Lake table.

Architecture

• Aggregated tables (daily, monthly, customer-level): Import.
• Transaction-level fact: Direct Lake.
• Drill-through page uses Direct Lake table; main report pages use Import.

Implementation idea

1. Build your main model with Import tables (FactSales_Agg, DimCustomer, etc.).
2. Add FactSales_Detail as Direct Lake.
3. Create a drill-through page filtered by keys like Date, Customer, Product.
4. Use the Direct Lake table only on the drill-through page.

Benefits

• 90% of usage stays fast and predictable in Import.
• Power users can still get granular detail when needed.
• Model remains smaller and refresh is quicker.

Gotchas

• Ensure keys used for drill-through exist in both aggregated and detailed tables.
• Users must understand that the detail page may be slightly slower.

3. Choosing the right mode: a decision checklist

Use this checklist per table (or group of tables), not just for the whole model.

3.1 Ask these questions

1. How fresh does the data need to be?

   • Hours: Import or Direct Lake.
   • Minutes: Direct Lake or DirectQuery.
   • Seconds: DirectQuery or streaming.

2. How big will it get in 12–24 months?

   • Still fits in memory comfortably: Import is fine.
   • Will explode (logs, events, IoT): Direct Lake or DirectQuery.

3. Can the source handle query load?

   • If not, avoid DirectQuery.
   • Consider moving to Fabric and using Direct Lake.

4. Do you control the schema and ETL?

   • If yes, you can design for Direct Lake (Delta, partitions, etc.).
   • If no, Import or DirectQuery may be simpler.

5. Is there strict compliance about where data lives?

   • May force DirectQuery or specific regions in Fabric.

3.2 Quick rule-of-thumb mapping

• Most analytical facts in Fabric → Direct Lake.
• Small, stable dimensions → Import (or Direct Lake if already in Fabric).
• External systems you can’t copy → DirectQuery.
• Very high concurrency dashboards → Prefer Import or Direct Lake.

4. Performance and reliability tactics for mixed-mode models

4.1 Keep a clean star schema

• Avoid snowflakes and many-to-many relationships.
• Use surrogate keys where possible.
• Minimize bi-directional relationships; they’re especially dangerous with DirectQuery.

4.2 Push work upstream

• Pre-aggregate in the lake or warehouse.
• Use views or materialized tables for complex joins and filters.
• Keep DAX measures simple and additive.

Example aggregation in a Fabric Warehouse:

CREATE TABLE dbo.FactSales_DailyAgg AS
SELECT
    OrderDateKey,
    CustomerKey,
    ProductKey,
    SUM(SalesAmount) AS SalesAmount,
    SUM(Quantity) AS Quantity
FROM dbo.FactSales_Detail
GROUP BY
    OrderDateKey,
    CustomerKey,
    ProductKey;

Then expose FactSales_DailyAgg via Direct Lake or Import, and reserve the detailed table for drill-through.

4.3 Use parameters and query folding in Power Query

For Import or Direct Lake sourcing, filter early and keep folding.

let
    Source = Sql.Database("sqlserver01", "SalesDW"),
    Sales = Source{[Schema="dbo",Item="FactSales"]}[Data],
    FilteredRows = Table.SelectRows(Sales, each [OrderDate] >= Date.AddYears(DateTime.Date(DateTime.LocalNow()), -2))
in
    FilteredRows

• This keeps only the last 2 years in memory.
• Ensure the filter folds to SQL (check in the Power Query UI).

4.4 Monitor and tune

• Use Performance Analyzer in Power BI Desktop.
• Watch for visuals that trigger multiple DirectQuery calls.
• Cache results where possible (aggregations, import small helper tables).

5. Security and governance in mixed-mode setups

5.1 Row-level security (RLS)

• RLS on Import and Direct Lake is straightforward.
• RLS on DirectQuery depends on source capabilities (e.g., security predicates, views).
• In composite models, be very explicit about which tables are filtered by RLS.

Example RLS filter on a dimension:

[Region] = USERPRINCIPALNAME()

(Usually you’d map the username/email to a region mapping table rather than filter directly.)

5.2 Data lineage and ownership

• Document which tables are Import, Direct Lake, and DirectQuery.
• Agree who owns each layer:
  • Source systems.
  • Lakehouse/Warehouse.
  • Power BI model.
• For Direct Lake, treat the Delta tables as the primary analytical asset and version them properly.

6. One practical takeaway

When you design your next Power BI model, don’t pick a single storage mode by habit. Instead, decide per table using this rule:

• Put fast-changing, big facts in Direct Lake.
• Keep reference dimensions and curated aggregates in Import.
• Use DirectQuery only where you truly can’t move the data.

If you consistently apply that rule and keep a clean star schema, most mixed-mode performance and complexity problems disappear before they even show up.