In this post we are going to cover the Fabric Data Warehouse: lakehouse or warehouse, what T-SQL works, keys that are not enforced, IDENTITY, MERGE, cross-database queries and recovery. These are 7 real Microsoft Fabric interview questions, asked the way they come up on a real project, and the last one is a scenario where a bad load corrupted a warehouse table this morning.
The Fabric warehouse is the place where you write data with T-SQL. It stores Delta tables in OneLake, just like the lakehouse, but you load it with stored procedures, MERGE and multi-table transactions. Interviewers ask about it because developers coming from SQL Server assume everything works the same, and the differences (keys, IDENTITY, isolation) are exactly where real loads go wrong.
Every SQL step below runs on the sample data, and the output under each query is the real result.
Part 5 of the Real Time Microsoft Fabric Interview Questions and Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 1: Your manager asks: should we build on the lakehouse or the warehouse? How do you decide?
- Question 2: A developer coming from SQL Server asks what T-SQL works in the Fabric warehouse. What is supported and what isn't?
- Question 3: You define a primary key on a warehouse table and duplicates still get in. Why, and how do you prevent them?
- Question 4: The dimension needs a surrogate key. How does IDENTITY work in the Fabric warehouse, and what are its limits?
- Question 5: The staging data must be upserted into the fact table. How do you write it with MERGE, and how do you keep it idempotent?
- Question 6: A report needs data from the lakehouse and two warehouses. How do cross-database queries work?
- Scenario 7: A bad load corrupted a warehouse table this morning. How do you recover, and how do you prepare for next time?
- The full SQL script
The short answer
The Fabric warehouse is full read-write T-SQL over Delta tables in OneLake, with multi-table transactions under snapshot isolation. Keys are NOT ENFORCED, so the load must prevent duplicates, ideally with an idempotent MERGE. IDENTITY gives BIGINT surrogate keys with gaps, and time travel and clones help you recover a bad load.
Keep that in mind. Every question below is one piece of it.
Let's create some sample data
These small tables stand in for the staging and fact tables of a Fabric warehouse load: a sales fact with its staging batch, and an orders fact with tonight's staging data. The script is written for PostgreSQL, and it runs on SQL Server or the Fabric warehouse with small changes, like DATETIME2(6) instead of TIMESTAMP.
DROP TABLE IF EXISTS fact_sales_clean;
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS stg_sales;
DROP TABLE IF EXISTS fact_orders;
DROP TABLE IF EXISTS stg_orders;
-- Fabric warehouse: a PRIMARY KEY can only be declared NONCLUSTERED and NOT ENFORCED,
-- so it never blocks a duplicate. We simulate that with a table that has no constraint.
-- Load 1 = yesterday, load 2 = tonight's first attempt (it failed at a later step).
CREATE TABLE fact_sales (sale_id INT, customer_id INT, amount DECIMAL(10,2), load_id INT);
INSERT INTO fact_sales VALUES
(5001, 11, 120.00, 1), (5002, 12, 75.50, 1), (5003, 13, 210.00, 1),
(5004, 14, 89.00, 2), (5005, 15, 340.00, 2), (5006, 12, 58.25, 2);
-- Tonight's batch, still in staging when the pipeline was retried
CREATE TABLE stg_sales (sale_id INT, customer_id INT, amount DECIMAL(10,2));
INSERT INTO stg_sales VALUES
(5004, 14, 89.00), (5005, 15, 340.00), (5006, 12, 58.25);
-- Fact table for the MERGE question (Fabric warehouse: use DATETIME2(6) for modified_at)
CREATE TABLE fact_orders (order_id INT, customer_id INT, amount DECIMAL(10,2),
status VARCHAR(12), modified_at TIMESTAMP);
INSERT INTO fact_orders VALUES
(7001, 11, 120.00, 'Shipped', '2026-10-20 09:00'),
(7002, 12, 75.50, 'Open', '2026-10-20 10:00'),
(7003, 13, 210.00, 'Open', '2026-10-20 11:00'),
(7004, 14, 99.00, 'Open', '2026-10-20 12:00');
-- Tonight's staging: two changed orders and two new ones, one row per key
CREATE TABLE stg_orders (order_id INT, customer_id INT, amount DECIMAL(10,2),
status VARCHAR(12), modified_at TIMESTAMP);
INSERT INTO stg_orders VALUES
(7002, 12, 75.50, 'Shipped', '2026-10-21 08:00'),
(7004, 14, 89.00, 'Open', '2026-10-21 09:00'),
(7005, 15, 340.00, 'Open', '2026-10-21 10:00'),
(7006, 16, 150.00, 'Open', '2026-10-21 11:00');Here is the sales fact table. Load 1 was yesterday. Load 2 was tonight's first attempt, sales 5004 to 5006, and it failed at a later step.
| sale_id | customer_id | amount | load_id |
|---|---|---|---|
| 5001 | 11 | 120.00 | 1 |
| 5002 | 12 | 75.50 | 1 |
| 5003 | 13 | 210.00 | 1 |
| 5004 | 14 | 89.00 | 2 |
| 5005 | 15 | 340.00 | 2 |
| 5006 | 12 | 58.25 | 2 |
Question 1: Your manager asks: should we build on the lakehouse or the warehouse? How do you decide?
Start with this: it's not a storage decision. Both store Delta tables in OneLake, and both are queried by the same SQL engine. The real difference is who writes the data, and with which language.
| Lakehouse | Warehouse | |
|---|---|---|
| WRITES WITH | Spark: PySpark, Spark SQL | T-SQL: procedures, MERGE |
| DATA | structured, semi-structured, files | structured tables |
| SQL ACCESS | SQL analytics endpoint: read-only | full read and write T-SQL |
| TRANSACTIONS | per Delta table | multi-table ACID |
| TYPICAL LAYER | bronze and silver | gold star schema |
Choose the lakehouse when the team writes with Spark: notebooks in PySpark or Spark SQL, semi-structured or unstructured files, and data science. Its SQL analytics endpoint lets analysts query it, but it's read-only.
Choose the warehouse when the team writes T-SQL: stored procedures, MERGE, and transactions that span several tables. It's for structured data, with full read and write T-SQL.
In a real project, it's often both. Bronze and silver live in a lakehouse, built with Spark, and the gold star schema lives in a warehouse, built by the SQL developers. If the team is Spark-first, lakehouse everywhere is fine too.
Ask who will maintain it in two years, and pick the engine that matches their skills.
Question 2: A developer coming from SQL Server asks what T-SQL works in the Fabric warehouse. What is supported and what isn't?
Most everyday T-SQL works, so most load logic moves over. Here is what's supported:
- CREATE TABLE AS SELECT, INSERT, UPDATE, DELETE and TRUNCATE
- MERGE, which is generally available
- Views, stored procedures and functions
- Multi-table transactions, under snapshot isolation
About that last one: the warehouse always uses snapshot isolation. If you set another isolation level, it's ignored. Conflicts are detected at table level, so two jobs updating the same table can abort one of them.
Now what's missing:
- Triggers and synonyms
- Materialized views
- Recursive CTEs
- Enforced keys: primary, unique and foreign keys exist only as NOT ENFORCED
- Savepoints and distributed transactions
And ALTER COLUMN is still in preview, so plan your data types up front.
To load files, use COPY INTO, from ADLS, Blob storage or OneLake, in CSV, Parquet or JSON lines format:
COPY INTO dbo.stg_sales
FROM 'https://acct.dfs.core.windows.net/raw/sales/*.parquet'
WITH (
FILE_TYPE = 'PARQUET',
CREDENTIAL = (IDENTITY = 'Workspace Identity')
);Scan the old code for triggers and recursive queries first. Those are the pieces you have to redesign, usually as steps in a pipeline.
Question 3: You define a primary key on a warehouse table and duplicates still get in. Why, and how do you prevent them?
Because in the Fabric warehouse, primary, unique and foreign keys can only be declared as NOT ENFORCED. They're metadata. The engine never checks them, so nothing stops a second copy of the same key.
-- Fabric warehouse: the only allowed form
ALTER TABLE dbo.fact_sales
ADD CONSTRAINT pk_fact_sales
PRIMARY KEY NONCLUSTERED (sale_id) NOT ENFORCED;Let's see it happen. Load 2 inserted sales 5004 to 5006, then failed at a later step, and the retry appends the same batch again as load 3. Our plain table, with no constraint, simulates the not enforced key.
INSERT INTO fact_sales
SELECT sale_id, customer_id, amount, 3
FROM stg_sales;The statement runs and changes the data. The check query below shows the result.
SELECT COUNT(*) AS row_count,
COUNT(DISTINCT sale_id) AS distinct_keys
FROM fact_sales;| row_count | distinct_keys |
|---|---|
| 9 | 6 |
The insert succeeds: 9 rows, but only 6 distinct keys. Now let's find the duplicates. Group by the business key and keep the keys with more than one row.
SELECT sale_id, COUNT(*) AS copies,
MIN(load_id) AS first_load,
MAX(load_id) AS last_load
FROM fact_sales
GROUP BY sale_id
HAVING COUNT(*) > 1
ORDER BY sale_id;| sale_id | copies | first_load | last_load |
|---|---|---|---|
| 5004 | 2 | 2 | 3 |
| 5005 | 2 | 2 | 3 |
| 5006 | 2 | 2 | 3 |
Three sales, 5004, 5005 and 5006, each loaded twice: first by load 2, and again by the retry, load 3.
To fix it, we keep one row per key with ROW_NUMBER, ordered by load_id so the first copy wins, and write the result to a clean table with CREATE TABLE AS SELECT.
CREATE TABLE fact_sales_clean AS
SELECT sale_id, customer_id, amount, load_id
FROM (SELECT sale_id, customer_id, amount, load_id,
ROW_NUMBER() OVER (PARTITION BY sale_id
ORDER BY load_id) AS rn
FROM fact_sales) d
WHERE rn = 1;The statement runs and changes the data. The check query below shows the result.
SELECT COUNT(*) AS row_count,
COUNT(DISTINCT sale_id) AS distinct_keys
FROM fact_sales_clean;| row_count | distinct_keys |
|---|---|
| 6 | 6 |
6 rows and 6 distinct keys. The duplicates are gone.
To prevent it, deduplicate staging, load with MERGE or an INSERT with NOT EXISTS, and run the duplicate check from above after every load. If it returns rows, fail the pipeline.
Question 4: The dimension needs a surrogate key. How does IDENTITY work in the Fabric warehouse, and what are its limits?
IDENTITY is generally available in the Fabric warehouse now. You declare the surrogate key column as BIGINT IDENTITY, and the warehouse generates a value for every new row. Your insert simply leaves that column out.
CREATE TABLE dbo.dim_customer (
customer_sk BIGINT IDENTITY,
customer_id VARCHAR(20) NOT NULL,
customer_name VARCHAR(100),
city VARCHAR(50)
);
INSERT INTO dbo.dim_customer
(customer_id, customer_name, city)
VALUES ('C101', 'Ava Brooks', 'Austin');The limits matter:
| Limit | Fabric warehouse |
|---|---|
| TYPE | BIGINT only |
| SEED, STEP | cannot be set |
| ORDER | not guaranteed |
| GAPS | possible |
Values are unique, but not in order, and there can be gaps, because the engine is distributed. So never use the surrogate key to find the latest row, and never assume there are no gaps. Use a load timestamp for order, and the business key to look rows up.
For the unknown member, turn on IDENTITY_INSERT, insert -1 with the name Unknown, and turn it off again. Then reseed with DBCC CHECKIDENT, so new keys can't collide. The same switch helps when you migrate keys.
SET IDENTITY_INSERT dbo.dim_customer ON;
INSERT INTO dbo.dim_customer
(customer_sk, customer_id, customer_name, city)
VALUES (-1, 'UNKNOWN', 'Unknown', 'Unknown');
SET IDENTITY_INSERT dbo.dim_customer OFF;
DBCC CHECKIDENT('dbo.dim_customer', RESEED);Before IDENTITY, teams built keys with ROW_NUMBER plus the current maximum. That still works, but IDENTITY is the cleaner answer now.
Question 5: The staging data must be upserted into the fact table. How do you write it with MERGE, and how do you keep it idempotent?
MERGE does the insert and the update in one statement, and it's generally available in the Fabric warehouse.
Tonight, staging has four orders: 7002 and 7004 are already in the fact table with changes, and 7005 and 7006 are new.
| order_id | amount | status |
|---|---|---|
| 7002 | 75.50 | Shipped |
| 7004 | 89.00 | Open |
| 7005 | 340.00 | Open |
| 7006 | 150.00 | Open |
Before the load, the fact table has 4 rows, with a total of 504.50.
| row_count | total_amount |
|---|---|
| 4 | 504.50 |
Match on the business key. When matched and the staged row is newer, update it. When not matched, insert it. The same MERGE runs in the Fabric warehouse and in PostgreSQL, where we run it here.
MERGE INTO fact_orders AS t
USING stg_orders AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.modified_at > t.modified_at THEN
UPDATE SET amount = s.amount,
status = s.status,
modified_at = s.modified_at
WHEN NOT MATCHED THEN
INSERT (order_id, customer_id, amount, status,
modified_at)
VALUES (s.order_id, s.customer_id, s.amount,
s.status, s.modified_at);The statement runs and changes the data. The check query below shows the result.
SELECT COUNT(*) AS row_count, SUM(amount) AS total_amount
FROM fact_orders;| row_count | total_amount |
|---|---|
| 6 | 984.50 |
6 rows, with a total of 984.50. Order 7002 became Shipped, order 7004 went from 99.00 to 89.00, and orders 7005 and 7006 were inserted. Now let's rerun the exact same MERGE.
MERGE INTO fact_orders AS t
USING stg_orders AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.modified_at > t.modified_at THEN
UPDATE SET amount = s.amount,
status = s.status,
modified_at = s.modified_at
WHEN NOT MATCHED THEN
INSERT (order_id, customer_id, amount, status,
modified_at)
VALUES (s.order_id, s.customer_id, s.amount,
s.status, s.modified_at);The statement runs and changes the data. The check query below shows the result.
SELECT COUNT(*) AS row_count, SUM(amount) AS total_amount
FROM fact_orders;| row_count | total_amount |
|---|---|
| 6 | 984.50 |
Still 6 rows, and the same total, 984.50. Nothing doubled. That's idempotent.
Two things keep it that way. Staging needs one row per key, or the MERGE fails when a target row matches twice, or inserts a new key twice. And the newer-row condition stops an old batch from overwriting a newer change.
The warehouse uses snapshot isolation, so don't run two MERGEs into the same table at the same time. One of them aborts with an update conflict.
Question 6: A report needs data from the lakehouse and two warehouses. How do cross-database queries work?
Inside one workspace, you just use three-part names: item, schema, table. A warehouse query can join its own tables with another warehouse, and with a lakehouse through its SQL analytics endpoint.
Let's look at the example. The fact comes from the SalesWH warehouse, the customer dimension from the SalesLH lakehouse, and the targets from the FinanceWH warehouse.
SELECT d.region,
SUM(f.amount) AS sales_amount,
MAX(t.target_amount) AS target_amount
FROM SalesWH.dbo.fact_sales AS f
JOIN SalesLH.dbo.dim_customer AS d
ON d.customer_id = f.customer_id
JOIN FinanceWH.dbo.sales_target AS t
ON t.region = d.region
GROUP BY d.region;No data is copied. Every item already stores Delta in OneLake, and one SQL engine reads it all.
Now the limits:
- It works within the same workspace only.
- Cross-region connections are not supported.
- The lakehouse side is read-only: you read from it, but writes go to the warehouse.
If the data sits in another workspace, create a OneLake shortcut to it in a lakehouse in your workspace, then query it like a local table.
Wrap these joins in a view in the gold warehouse, so the report sees one clean object, and the security lives in one place.
Scenario 7: A bad load corrupted a warehouse table this morning. How do you recover, and how do you prepare for next time?
Let's recover it in four steps.
- Stop the damage. Pause the schedule, so the next run doesn't build on bad data.
- Find the time. Find when the bad load committed, from the pipeline run history or query insights.
- Time travel. Look at the table as it was just before that time.
- Clone and replace. Restore just that table, in one transaction.
For time travel, add a FOR TIMESTAMP AS OF hint to the query, in UTC, and compare the totals with what you see today.
-- check the table before the bad load (UTC)
SELECT COUNT(*) AS row_count, SUM(amount) AS total
FROM dbo.fact_sales
OPTION (FOR TIMESTAMP AS OF '2026-10-22T05:00:00.000');Then create a zero-copy clone at that point in time. It copies only metadata, so it's fast.
-- zero-copy clone at that point in time
CREATE TABLE dbo.fact_sales_restore
AS CLONE OF dbo.fact_sales AT '2026-10-22T05:00:00.000';Then replace the bad data from the clone, in one transaction:
BEGIN TRANSACTION;
DELETE FROM dbo.fact_sales;
INSERT INTO dbo.fact_sales
SELECT * FROM dbo.fact_sales_restore;
COMMIT TRANSACTION;If several tables are damaged, restore the whole warehouse to a restore point instead.
All of this only works within the retention window, so act quickly, and check your warehouse's retention setting rather than assuming a number.
To prepare for next time:
- Load through staging, and validate before you publish.
- Create a user-defined restore point before big releases.
- Keep duplicate and total checks in the pipeline.
- Practice a restore once, before you need it.
Key points to remember
- Lakehouse for Spark writers, warehouse for T-SQL writers; often both
- Full T-SQL DML incl. MERGE; snapshot isolation; no triggers or recursive CTEs
- Keys are NOT ENFORCED: the load must prevent duplicates
- IDENTITY: BIGINT only, unique, not ordered, gaps possible
- Recover with time travel and a zero-copy clone, within retention
The full SQL script
Here is everything in one script: the sample data and every query from this post, in order. Copy it and run it top to bottom.
Show the full SQL script (sample data and every query)
-- ========================================================================
-- Fabric Interview Q&A #5 - The Fabric data warehouse
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16 (and cross-checked on DuckDB). 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 sql_q05; SET search_path TO sql_q05;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales_clean;
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS stg_sales;
DROP TABLE IF EXISTS fact_orders;
DROP TABLE IF EXISTS stg_orders;
-- Fabric warehouse: a PRIMARY KEY can only be declared NONCLUSTERED and NOT ENFORCED,
-- so it never blocks a duplicate. We simulate that with a table that has no constraint.
-- Load 1 = yesterday, load 2 = tonight's first attempt (it failed at a later step).
CREATE TABLE fact_sales (sale_id INT, customer_id INT, amount DECIMAL(10,2), load_id INT);
INSERT INTO fact_sales VALUES
(5001, 11, 120.00, 1), (5002, 12, 75.50, 1), (5003, 13, 210.00, 1),
(5004, 14, 89.00, 2), (5005, 15, 340.00, 2), (5006, 12, 58.25, 2);
-- Tonight's batch, still in staging when the pipeline was retried
CREATE TABLE stg_sales (sale_id INT, customer_id INT, amount DECIMAL(10,2));
INSERT INTO stg_sales VALUES
(5004, 14, 89.00), (5005, 15, 340.00), (5006, 12, 58.25);
-- Fact table for the MERGE question (Fabric warehouse: use DATETIME2(6) for modified_at)
CREATE TABLE fact_orders (order_id INT, customer_id INT, amount DECIMAL(10,2),
status VARCHAR(12), modified_at TIMESTAMP);
INSERT INTO fact_orders VALUES
(7001, 11, 120.00, 'Shipped', '2026-10-20 09:00'),
(7002, 12, 75.50, 'Open', '2026-10-20 10:00'),
(7003, 13, 210.00, 'Open', '2026-10-20 11:00'),
(7004, 14, 99.00, 'Open', '2026-10-20 12:00');
-- Tonight's staging: two changed orders and two new ones, one row per key
CREATE TABLE stg_orders (order_id INT, customer_id INT, amount DECIMAL(10,2),
status VARCHAR(12), modified_at TIMESTAMP);
INSERT INTO stg_orders VALUES
(7002, 12, 75.50, 'Shipped', '2026-10-21 08:00'),
(7004, 14, 89.00, 'Open', '2026-10-21 09:00'),
(7005, 15, 340.00, 'Open', '2026-10-21 10:00'),
(7006, 16, 150.00, 'Open', '2026-10-21 11:00');
-- Step 1: The retry appends the same batch again (the declared key does not stop it)
INSERT INTO fact_sales
SELECT sale_id, customer_id, amount, 3
FROM stg_sales;
-- check the result
SELECT COUNT(*) AS row_count,
COUNT(DISTINCT sale_id) AS distinct_keys
FROM fact_sales;
-- Step 2: Find the duplicated keys
SELECT sale_id, COUNT(*) AS copies,
MIN(load_id) AS first_load,
MAX(load_id) AS last_load
FROM fact_sales
GROUP BY sale_id
HAVING COUNT(*) > 1
ORDER BY sale_id;
-- Step 3: Fix: keep one row per key in a clean table (CTAS with ROW_NUMBER)
CREATE TABLE fact_sales_clean AS
SELECT sale_id, customer_id, amount, load_id
FROM (SELECT sale_id, customer_id, amount, load_id,
ROW_NUMBER() OVER (PARTITION BY sale_id
ORDER BY load_id) AS rn
FROM fact_sales) d
WHERE rn = 1;
-- check the result
SELECT COUNT(*) AS row_count,
COUNT(DISTINCT sale_id) AS distinct_keys
FROM fact_sales_clean;
-- Step 4: MERGE the staging data into the fact table
MERGE INTO fact_orders AS t
USING stg_orders AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.modified_at > t.modified_at THEN
UPDATE SET amount = s.amount,
status = s.status,
modified_at = s.modified_at
WHEN NOT MATCHED THEN
INSERT (order_id, customer_id, amount, status,
modified_at)
VALUES (s.order_id, s.customer_id, s.amount,
s.status, s.modified_at);
-- check the result
SELECT COUNT(*) AS row_count, SUM(amount) AS total_amount
FROM fact_orders;
-- Step 5: Rerun the same MERGE: nothing doubles
MERGE INTO fact_orders AS t
USING stg_orders AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.modified_at > t.modified_at THEN
UPDATE SET amount = s.amount,
status = s.status,
modified_at = s.modified_at
WHEN NOT MATCHED THEN
INSERT (order_id, customer_id, amount, status,
modified_at)
VALUES (s.order_id, s.customer_id, s.amount,
s.status, s.modified_at);
-- check the result
SELECT COUNT(*) AS row_count, SUM(amount) AS total_amount
FROM fact_orders;
Part 6: Data Factory in Fabric
The sample data on this page is invented for teaching. Every output table is the real result of the query above it, run on PostgreSQL 16 over that sample data; where SQL Server syntax is different, the post says so. Part of the Real Time Microsoft Fabric Interview Questions and Answers series on TechBrothersIT. Features, limits and their status (GA or preview) are current as of October 2026; check the Microsoft Learn page for anything you quote in an interview.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.