In this post we are going to cover storage modes in Power BI: Import, DirectQuery, Direct Lake, Dual, composite models, aggregations and live connections. These are 7 real Power BI interview questions, and the last one is a scenario a manager asks: the business wants near real-time sales on a 2-billion-row table, but the DirectQuery reports are slow.
The storage mode decides where the data lives when a visual asks for it: in memory inside the model, at the source database, or in Delta tables in OneLake. That one choice drives how fast the reports are, how fresh the data is, and what it costs. That is why interviewers ask about it. A good answer shows you can pick a mode for a reason, not out of habit.
Most of this topic is design, so most answers are explanations and comparison tables. For aggregations we use a small sample data set, so you can check the numbers yourself.
Part 2 of the Real Time Power BI Interview Questions and Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 1: What are the storage modes in Power BI: Import, DirectQuery, Direct Lake, plus Dual and live connection?
- Question 2: Import vs DirectQuery: when would you choose each?
- Question 3: What is Direct Lake, how is it different from Import and DirectQuery, and what is fallback?
- Question 4: What is a composite model, and what is Dual storage mode?
- Question 5: What are aggregations (user-defined aggregation tables), and when do they help?
- Question 6: What is the difference between a live connection and DirectQuery to a Power BI semantic model or Analysis Services?
- Question 7: Scenario - near real-time sales on a 2-billion-row table, but DirectQuery reports are slow. What design do you propose?
- The full SQL script
The short answer
Import copies data into memory: fastest, but only as fresh as the last refresh. DirectQuery queries the source live, so speed depends on the source. Direct Lake reads Delta tables in OneLake without copy refreshes. Dual dimensions, aggregations and hybrid tables combine the best of each.
Keep that summary in mind. Every question below is one piece of it.
Let's create some sample data
We use a tiny shop: 5 products, 4 customers in two regions, a date table for 2025 and 2026, and 12 sales in January to March of each year. The script below is written for PostgreSQL (the date table uses generate_series), and it works on any database with small changes. Load the four tables into Power BI Desktop and relate fact_sales to each dimension on its key.
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_product (product_key INT PRIMARY KEY, product VARCHAR(30), category VARCHAR(20));
INSERT INTO dim_product VALUES
(1, 'Laptop Stand', 'Accessories'), (2, 'USB-C Cable', 'Accessories'),
(3, 'Monitor', 'Electronics'), (4, 'Keyboard', 'Electronics'),
(5, 'Desk Lamp', 'Home');
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, customer VARCHAR(30), city VARCHAR(20), region VARCHAR(10), email VARCHAR(40));
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@tbshop.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@tbshop.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@tbshop.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@tbshop.com');
-- one row per day, 2025-01-01 .. 2026-12-31 (calendar year = fiscal year here)
CREATE TABLE dim_date (date DATE PRIMARY KEY, year INT, quarter VARCHAR(2), month_no INT, month VARCHAR(3));
INSERT INTO dim_date
SELECT d, EXTRACT(YEAR FROM d), 'Q' || EXTRACT(QUARTER FROM d), EXTRACT(MONTH FROM d), TO_CHAR(d, 'Mon')
FROM generate_series(DATE '2025-01-01', DATE '2026-12-31', INTERVAL '1 day') AS g(d);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
order_date DATE REFERENCES dim_date(date),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
qty INT,
unit_price DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
( 1, '2025-01-10', 1, 1, 2, 40.00), ( 2, '2025-01-22', 3, 2, 1, 250.00),
( 3, '2025-02-05', 2, 3, 5, 10.00), ( 4, '2025-02-18', 5, 4, 1, 60.00),
( 5, '2025-03-03', 4, 1, 2, 50.00), ( 6, '2025-03-27', 3, 3, 1, 250.00),
( 7, '2026-01-08', 1, 2, 3, 40.00), ( 8, '2026-01-25', 3, 1, 1, 250.00),
( 9, '2026-02-11', 2, 4, 4, 10.00), (10, '2026-02-20', 4, 3, 2, 50.00),
(11, '2026-03-09', 5, 2, 2, 60.00), (12, '2026-03-30', 3, 4, 2, 250.00);Here are the 12 sales. Each row is one sale line: a date, a product, a customer, a quantity and a unit price.
| sale_id | order_date | product_key | customer_key | qty | unit_price |
|---|---|---|---|---|---|
| 1 | 2025-01-10 | 1 | 1 | 2 | 40.00 |
| 2 | 2025-01-22 | 3 | 2 | 1 | 250.00 |
| 3 | 2025-02-05 | 2 | 3 | 5 | 10.00 |
| 4 | 2025-02-18 | 5 | 4 | 1 | 60.00 |
| 5 | 2025-03-03 | 4 | 1 | 2 | 50.00 |
| 6 | 2025-03-27 | 3 | 3 | 1 | 250.00 |
| 7 | 2026-01-08 | 1 | 2 | 3 | 40.00 |
| 8 | 2026-01-25 | 3 | 1 | 1 | 250.00 |
| 9 | 2026-02-11 | 2 | 4 | 4 | 10.00 |
| 10 | 2026-02-20 | 4 | 3 | 2 | 50.00 |
| 11 | 2026-03-09 | 5 | 2 | 2 | 60.00 |
| 12 | 2026-03-30 | 3 | 4 | 2 | 250.00 |
And the products, so you can see which ones are Electronics, Accessories and Home. We group by category in Question 5.
| product_key | product | category |
|---|---|---|
| 1 | Laptop Stand | Accessories |
| 2 | USB-C Cable | Accessories |
| 3 | Monitor | Electronics |
| 4 | Keyboard | Electronics |
| 5 | Desk Lamp | Home |
Question 1: What are the storage modes in Power BI: Import, DirectQuery, Direct Lake, plus Dual and live connection?
There are three main modes: Import, DirectQuery and Direct Lake. Then there are two more you should name: Dual and live connection.
- Import copies the data into the model, compressed in memory by the VertiPaq engine. It is the fastest mode, but the data is only as fresh as the last refresh.
- DirectQuery keeps the data at the source. Every visual sends a query to the database, so the data is current, but the speed depends on that source.
- Direct Lake is the Fabric mode. It reads Delta tables in OneLake straight into memory, without a copy refresh.
- Dual means a table acts as Import or DirectQuery, depending on the query.
- A live connection is a report sitting on a published semantic model, or on Analysis Services, with no local model at all.
Say where the data lives for each one: in memory, at the source, or in OneLake. That one sentence per mode is the answer the interviewer is listening for.
Question 2: Import vs DirectQuery: when would you choose each?
Default to Import. Choose DirectQuery only when the data must be real time, is too large to import, or must stay at the source for security or sovereignty reasons.
Import is the default because queries run in memory, you get every DAX and Power Query feature, and the source is only hit during refresh. The cost is refresh latency and memory. On Pro, a model is limited to 1 GB.
DirectQuery pays for freshness with speed and features. Every visual, for every user, sends a query to the source. Power Query steps must fold to that source, and some DAX is limited.
Here is the comparison.
| Import | DirectQuery | |
|---|---|---|
| FRESHNESS | as of the last refresh | live, at query time |
| PERFORMANCE | fastest, in memory | depends on the source |
| SIZE | 1 GB per model on Pro, more on capacity | data stays at the source |
| DAX / POWER QUERY | everything | must fold, some DAX limits |
| SOURCE LOAD | only during refresh | every visual, every user |
Look at the last row. With Import, the source only works during refresh. With DirectQuery, it works for every visual and every user, all day.
Mention the pitfall: don't switch to DirectQuery just because the model is big. Large model format, incremental refresh, or Direct Lake usually work better.
Question 3: What is Direct Lake, how is it different from Import and DirectQuery, and what is fallback?
Direct Lake reads Delta tables in OneLake straight into the VertiPaq engine. You get close to Import speed, without copying the data.
Two words to know. Its refresh is called framing: it only points the model at the latest Delta version, so it takes seconds. And transcoding loads just the columns a query needs into memory.
Fallback means a query that can't run in Direct Lake runs as DirectQuery instead. Whether that can happen depends on which of the two flavors you use:
| Direct Lake on OneLake | Direct Lake on SQL endpoint | |
|---|---|---|
| FALLBACK | none: refresh fails | to DirectQuery |
| SOURCES | many Fabric items, shortcuts | one item |
| WITH IMPORT | composite: yes | no |
| AUTHORING | Desktop (GA) and web | created in the web |
Direct Lake on the SQL endpoint can fall back to DirectQuery, for SQL views, SQL endpoint security, or when a table exceeds the guardrails. Direct Lake on OneLake never falls back. If a guardrail is exceeded, the refresh fails.
The guardrails depend on the SKU. On F64, a table can have up to 1.5 billion rows.
To avoid fallback on the SQL endpoint flavor, set the Direct Lake behavior to Direct Lake only, and materialize views as tables.
Question 4: What is a composite model, and what is Dual storage mode?
A composite model mixes storage modes in one model. For example, an Import Targets table next to a DirectQuery Sales table. It can also use DirectQuery to more than one source.
The catch is the relationships. A relationship between different source groups is a limited relationship. It behaves like an inner join, adds no blank row, and RELATED doesn't work across it.
Dual fixes that for the shared dimensions. A Dual table is cached in memory and is also available at the source. Picture a model with Targets in Import, Sales in DirectQuery, and Product and Date in Dual. When a visual uses Targets, Product answers from memory. When a visual uses Sales, Product joins at the source in one query, as a regular relationship.
| Dimension in | Relationship to the DirectQuery fact |
|---|---|
| Import | limited: inner join, no blank row, no RELATED |
| Dual | regular: joins at the source, slicers from memory |
There is a bonus worth saying: slicers built on Dual dimensions read their values from memory, so they don't send a query to the source.
Question 5: What are aggregations (user-defined aggregation tables), and when do they help?
An aggregation table is a pre-summarized copy of a big fact table. Power BI answers from the small table when it can, and goes to the big one only when it has to.
Let's build one on our sample data. We group the sales by year and category.
| year | category | qty | sales | detail_rows |
|---|---|---|---|---|
| 2025 | Accessories | 7 | 130.00 | 2 |
| 2025 | Electronics | 4 | 600.00 | 3 |
| 2025 | Home | 1 | 60.00 | 1 |
| 2026 | Accessories | 7 | 160.00 | 2 |
| 2026 | Electronics | 5 | 850.00 | 3 |
| 2026 | Home | 2 | 120.00 | 1 |
Check this result yourself with SQL
The aggregation table pre-summarizes the 12 detail rows into 6 rows at the year and category grain.
SELECT d.year, p.category,
SUM(f.qty) AS qty,
SUM(f.qty * f.unit_price) AS sales,
COUNT(*) AS detail_rows
FROM fact_sales f
JOIN dim_date d ON d.date = f.order_date
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY d.year, p.category
ORDER BY d.year, p.category;Our 12 detail rows become 6 rows. For example, 2026 Electronics is 850.00 in one row, where the detail table needs 3 rows for it.
You map this table in Manage aggregations. The detail table stays DirectQuery, and the aggregation table is usually Import. When a visual asks for sales by year or by category, Power BI answers from the small table in memory. It only goes to the source when a visual asks for something below that grain, like a single product.
For that to be safe, the totals must match. Let's compare the detail table and the aggregation table.
| year | detail_sales | agg_sales |
|---|---|---|
| 2025 | 790.00 | 790.00 |
| 2026 | 1130.00 | 1130.00 |
| Total | 1920.00 | 1920.00 |
Check this result yourself with SQL
Every year and the grand total are identical, so visuals at or above the aggregation grain can safely answer from it.
WITH agg AS (
SELECT d.year, p.category, SUM(f.qty * f.unit_price) AS sales
FROM fact_sales f
JOIN dim_date d ON d.date = f.order_date
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY d.year, p.category),
det AS (
SELECT EXTRACT(YEAR FROM order_date)::INT AS year, SUM(qty * unit_price) AS sales
FROM fact_sales
GROUP BY 1)
SELECT year, detail_sales, agg_sales FROM (
SELECT CAST(det.year AS VARCHAR(5)) AS year, det.sales AS detail_sales,
(SELECT SUM(a.sales) FROM agg a WHERE a.year = det.year) AS agg_sales, 0 AS srt
FROM det
UNION ALL
SELECT 'Total', (SELECT SUM(sales) FROM det), (SELECT SUM(sales) FROM agg), 1) t
ORDER BY srt, year;2025 is 790.00 in both, 2026 is 1130.00 in both, and the grand total is 1920.00 in both. If these ever differ, your report gives different answers depending on which table Power BI picked.
Two pitfalls to mention:
- RLS must filter both tables, the aggregation and the detail table.
- Prove the aggregation is hit. Look for the Aggregate Table Rewrite Query event in SQL Profiler.
Question 6: What is the difference between a live connection and DirectQuery to a Power BI semantic model or Analysis Services?
With a live connection, your report sits directly on a published semantic model, or on Analysis Services. There is no local model. You can build visuals and add report-level measures, but no Power Query, no new tables and no relationships.
If you need more, you choose to make changes to the model. Desktop then adds a local model that uses DirectQuery to the published one. That is a composite model on top of a semantic model.
Now you can import a local Targets table, relate it to the remote tables, and add your own measures. Those cross-source relationships are limited relationships, the same as in Question 4.
| Live connection | DirectQuery to the model | |
|---|---|---|
| LOCAL MODEL | none | yes (composite) |
| MEASURES | report-level only | your own measures |
| OTHER DATA | no | import and add tables |
| RELATIONSHIPS | no | yes, limited across sources |
Two requirements to mention: you need Build permission on the source model, and a chain of models can be at most 3 levels deep.
Question 7: Scenario - near real-time sales on a 2-billion-row table, but DirectQuery reports are slow. What design do you propose?
Start with the question back to the business: what does near real time really mean? Often, refreshing every 30 minutes on a capacity is enough. Then give the design options, in this order.
- Hybrid table first. Most of those 2 billion rows are history, and history doesn't change. Make sales a hybrid table: incremental refresh keeps older periods as Import partitions in memory, and adds a real-time DirectQuery partition for the newest data. This needs PPU or a capacity.
- Aggregations if the fact must stay DirectQuery. Add Import aggregation tables and put the dimensions in Dual. Then most visuals never touch the 2 billion detail rows.
- Direct Lake if the data already lands in Fabric as Delta. Check the guardrails: on F64 a table is limited to 1.5 billion rows, so this table needs F128, which allows 3 billion.
Then state the trade-offs. Hybrid tables need PPU or a capacity, not Pro. And Direct Lake doesn't support hybrid tables, so you pick one path or the other for this table.
Asking what "near real time" means is part of the answer. It shows the manager you solve the business problem, not just the technical one.
Key points to remember
- Import is the fast default; DirectQuery is live
- Direct Lake reads Delta in OneLake; watch fallback
- Dual dimensions avoid limited relationships
- Aggregations answer from memory; totals must match
- Hybrid tables: Import history plus live recent data
The full SQL script
If you want to rebuild everything at once, here is the whole script: the sample data and the SQL check behind every result above.
Show the full SQL script (sample data and every check)
-- ========================================================================
-- Power BI Interview Q&A #2 - Import, DirectQuery and Direct Lake
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA pbi_q02; SET search_path TO pbi_q02;
-- SQL Server: write DATE '2026-06-01' as CAST('2026-06-01' AS DATE).
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_product (product_key INT PRIMARY KEY, product VARCHAR(30), category VARCHAR(20));
INSERT INTO dim_product VALUES
(1, 'Laptop Stand', 'Accessories'), (2, 'USB-C Cable', 'Accessories'),
(3, 'Monitor', 'Electronics'), (4, 'Keyboard', 'Electronics'),
(5, 'Desk Lamp', 'Home');
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, customer VARCHAR(30), city VARCHAR(20), region VARCHAR(10), email VARCHAR(40));
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@tbshop.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@tbshop.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@tbshop.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@tbshop.com');
-- one row per day, 2025-01-01 .. 2026-12-31 (calendar year = fiscal year here)
CREATE TABLE dim_date (date DATE PRIMARY KEY, year INT, quarter VARCHAR(2), month_no INT, month VARCHAR(3));
INSERT INTO dim_date
SELECT d, EXTRACT(YEAR FROM d), 'Q' || EXTRACT(QUARTER FROM d), EXTRACT(MONTH FROM d), TO_CHAR(d, 'Mon')
FROM generate_series(DATE '2025-01-01', DATE '2026-12-31', INTERVAL '1 day') AS g(d);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
order_date DATE REFERENCES dim_date(date),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
qty INT,
unit_price DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
( 1, '2025-01-10', 1, 1, 2, 40.00), ( 2, '2025-01-22', 3, 2, 1, 250.00),
( 3, '2025-02-05', 2, 3, 5, 10.00), ( 4, '2025-02-18', 5, 4, 1, 60.00),
( 5, '2025-03-03', 4, 1, 2, 50.00), ( 6, '2025-03-27', 3, 3, 1, 250.00),
( 7, '2026-01-08', 1, 2, 3, 40.00), ( 8, '2026-01-25', 3, 1, 1, 250.00),
( 9, '2026-02-11', 2, 4, 4, 10.00), (10, '2026-02-20', 4, 3, 2, 50.00),
(11, '2026-03-09', 5, 2, 2, 60.00), (12, '2026-03-30', 3, 4, 2, 250.00);
-- Step 1: Aggregation table: sales by year and category
SELECT d.year, p.category,
SUM(f.qty) AS qty,
SUM(f.qty * f.unit_price) AS sales,
COUNT(*) AS detail_rows
FROM fact_sales f
JOIN dim_date d ON d.date = f.order_date
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY d.year, p.category
ORDER BY d.year, p.category;
-- Step 2: Detail fact vs aggregation table: the totals match
WITH agg AS (
SELECT d.year, p.category, SUM(f.qty * f.unit_price) AS sales
FROM fact_sales f
JOIN dim_date d ON d.date = f.order_date
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY d.year, p.category),
det AS (
SELECT EXTRACT(YEAR FROM order_date)::INT AS year, SUM(qty * unit_price) AS sales
FROM fact_sales
GROUP BY 1)
SELECT year, detail_sales, agg_sales FROM (
SELECT CAST(det.year AS VARCHAR(5)) AS year, det.sales AS detail_sales,
(SELECT SUM(a.sales) FROM agg a WHERE a.year = det.year) AS agg_sales, 0 AS srt
FROM det
UNION ALL
SELECT 'Total', (SELECT SUM(sales) FROM det), (SELECT SUM(sales) FROM agg), 1) t
ORDER BY srt, year;
Part 3: Power Query and Query Folding
The sample data on this page is invented for teaching. The result tables were produced by running the same calculation in SQL on PostgreSQL 16 over that sample data (open "Check this result yourself" under any result), so you can rebuild every number in Power BI Desktop and compare.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.