Welcome To TechBrothersIT: Microsoft Fabric Interview Questions and Answers Part 7

Microsoft Fabric Interview Questions and Answers Part 7 - Incremental Loads, MERGE and SCD

Microsoft Fabric Interview Q&A · Part 7 of 10

In this post we are going to cover incremental loads, MERGE and SCD on Delta tables in Microsoft Fabric: upserts, SCD Type 2, concurrency errors, duplicate keys in a batch, partition pruning and time travel. 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 rerun leaves duplicate rows in the silver table.

An incremental load writes only what changed since the last run into a Delta table, instead of rewriting the whole table every night. In Fabric that almost always means a MERGE. Interviewers ask about it because every lakehouse project needs it, and the hard parts are the details: duplicate keys, two jobs writing at once, and what a rerun does to the table.

Every SQL step below runs on the sample data, and the output under each query is the real result.

Part 7 of the Real Time Microsoft Fabric Interview Questions and Answers series on TechBrothersIT.

On this page

The short answer
Let's create some sample data
Question 1: New and changed orders arrive daily. How do you upsert them into a Delta table with MERGE in PySpark or Spark SQL?
Question 2: The customer dimension must keep address history. How do you build SCD Type 2 on Delta tables?
Question 3: Two jobs write to the same Delta table and one fails with a concurrency error. Why, and how do you avoid it?
Question 4: The source sends duplicates within the same batch and MERGE fails. Why, and what is the fix?
Question 5: A table is partitioned by day and MERGE scans the whole table. How do you make MERGE prune partitions?
Question 6: The business needs to see the table as it was last Friday. How does Delta time travel work, and how long can you go back?
Scenario 7: The silver table got duplicate rows after a rerun. How do you find, fix and prevent it?
The full SQL script

The short answer

Say this first

Load changes into Delta tables with MERGE on the business key: update when matched, insert when not. Build SCD Type 2 with one MERGE over a staged source, deduplicate each batch first, and put the partition column in the ON condition. Delta's optimistic concurrency and time travel explain conflicts and give you rollbacks, within the VACUUM retention.

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 source batches and the Delta tables of a Fabric lakehouse: silver orders, today's orders batch, a customer dimension with its staged extract, a batch with duplicate keys, a late batch and a silver sales table that was loaded twice. The script is written for PostgreSQL, and it runs on SQL Server with small changes, like DATETIME2 instead of TIMESTAMP and BIT instead of BOOLEAN.

Sample data - SQL script (PostgreSQL)

DROP TABLE IF EXISTS silver_orders;
DROP TABLE IF EXISTS orders_batch;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS stg_customer;
DROP TABLE IF EXISTS daily_batch;
DROP TABLE IF EXISTS late_batch;
DROP TABLE IF EXISTS silver_sales;

-- SQL Server: use DATETIME2 instead of TIMESTAMP, and BIT instead of BOOLEAN

-- Q1: the silver orders table, and today's batch of new and changed orders
CREATE TABLE silver_orders (order_id INT, status VARCHAR(12), amount DECIMAL(10,2),
                            order_date DATE, updated_at TIMESTAMP);
INSERT INTO silver_orders VALUES
  (1001, 'Shipped', 120.00, '2026-10-19', '2026-10-19 09:00'),
  (1002, 'Open',     75.50, '2026-10-19', '2026-10-19 10:30'),
  (1003, 'Shipped', 210.00, '2026-10-20', '2026-10-20 08:15'),
  (1004, 'Open',     89.00, '2026-10-20', '2026-10-20 13:45');
CREATE TABLE orders_batch (order_id INT, status VARCHAR(12), amount DECIMAL(10,2),
                           order_date DATE, updated_at TIMESTAMP);
INSERT INTO orders_batch VALUES
  (1002, 'Shipped',  75.50, '2026-10-19', '2026-10-21 08:00'),
  (1005, 'Open',    340.00, '2026-10-21', '2026-10-21 09:15'),
  (1006, 'Open',     58.25, '2026-10-21', '2026-10-21 11:40');

-- Q2: an SCD Type 2 customer dimension, and today's customer extract
CREATE TABLE dim_customer (customer_id INT, city VARCHAR(20), start_date DATE,
                           end_date DATE, is_current BOOLEAN);
INSERT INTO dim_customer VALUES
  (7, 'Dallas', '2024-03-01', NULL, TRUE),
  (8, 'Denver', '2025-01-10', NULL, TRUE);
CREATE TABLE stg_customer (customer_id INT, city VARCHAR(20), effective_date DATE);
INSERT INTO stg_customer VALUES
  (7, 'Austin', '2026-10-21'),
  (8, 'Denver', '2026-10-21'),
  (9, 'Miami',  '2026-10-21');

-- Q4: a batch where the source sent some orders twice
CREATE TABLE daily_batch (order_id INT, status VARCHAR(12), updated_at TIMESTAMP);
INSERT INTO daily_batch VALUES
  (1002, 'Packed',    '2026-10-22 08:00'),
  (1002, 'Shipped',   '2026-10-22 14:30'),
  (1007, 'Open',      '2026-10-22 09:00'),
  (1007, 'Cancelled', '2026-10-22 16:10'),
  (1008, 'Open',      '2026-10-22 10:20');

-- Q5: a late batch for a table partitioned by order_date
CREATE TABLE late_batch (order_id INT, status VARCHAR(12), order_date DATE);
INSERT INTO late_batch VALUES
  (1003, 'Returned', '2026-10-20'),
  (1009, 'Open',     '2026-10-20'),
  (1006, 'Shipped',  '2026-10-21'),
  (1010, 'Open',     '2026-10-21');

-- Q7: a silver table loaded by append; load 703 was a rerun of load 702
CREATE TABLE silver_sales (order_id INT, amount DECIMAL(10,2), order_date DATE, load_id INT);
INSERT INTO silver_sales VALUES
  (2001,  50.00, '2026-10-20', 701), (2002,  80.00, '2026-10-20', 701), (2003, 35.00, '2026-10-20', 701),
  (2004, 120.00, '2026-10-21', 702), (2005,  60.00, '2026-10-21', 702), (2006, 45.00, '2026-10-21', 702),
  (2004, 120.00, '2026-10-21', 703), (2005,  60.00, '2026-10-21', 703), (2006, 45.00, '2026-10-21', 703);

Here is the silver orders table before today's load. Four orders, 1001 to 1004, and order 1002 is still Open.

silver_orders4 rows

order_id	status	amount	order_date	updated_at
1001	Shipped	120.00	2026-10-19	2026-10-19 09:00
1002	Open	75.50	2026-10-19	2026-10-19 10:30
1003	Shipped	210.00	2026-10-20	2026-10-20 08:15
1004	Open	89.00	2026-10-20	2026-10-20 13:45

Question 1: New and changed orders arrive daily. How do you upsert them into a Delta table with MERGE in PySpark or Spark SQL?

MERGE matches the daily batch to the target on the business key. Matched rows are updated, rows with no match are inserted, and it all lands in one atomic commit.

In Spark SQL it reads almost like English. UPDATE SET * and INSERT * mean every column, so the batch and the target must have the same schema.

upsert_orders.sql

MERGE INTO silver.orders AS t
USING orders_batch AS s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

In PySpark, DeltaTable.forName gives you the target. Alias it, merge the batch on the same condition, then update all when matched, insert all when not matched, and execute.

notebook.py

from delta.tables import DeltaTable
batch = spark.read.table("orders_batch")
tgt = DeltaTable.forName(spark, "silver.orders")
(tgt.alias("t")
    .merge(batch.alias("s"), "t.order_id = s.order_id")
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute())

Now let's try it on the sample data. Here is today's batch: order 1002 changed to Shipped, and orders 1005 and 1006 are new.

orders_batch3 rows

order_id	status	amount	order_date	updated_at
1002	Shipped	75.50	2026-10-19	2026-10-21 08:00
1005	Open	340.00	2026-10-21	2026-10-21 09:15
1006	Open	58.25	2026-10-21	2026-10-21 11:40

The step below is the same MERGE written with explicit columns, so it runs on PostgreSQL and SQL Server too.

SQL - Upsert the daily batch with MERGE (portable form of the Delta MERGE)

MERGE INTO silver_orders AS t
USING orders_batch AS s
  ON t.order_id = s.order_id
WHEN MATCHED THEN
  UPDATE SET status = s.status, amount = s.amount,
             updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (order_id, status, amount, order_date,
          updated_at)
  VALUES (s.order_id, s.status, s.amount, s.order_date,
          s.updated_at);

The statement runs and changes the data. The check query below shows the result.

SQL - check the data after the change

SELECT order_id, status, amount, updated_at
FROM silver_orders
ORDER BY order_id;

Output after the change6 rows

order_id	status	amount	updated_at
1001	Shipped	120.00	2026-10-19 09:00
1002	Shipped	75.50	2026-10-21 08:00
1003	Shipped	210.00	2026-10-20 08:15
1004	Open	89.00	2026-10-20 13:45
1005	Open	340.00	2026-10-21 09:15
1006	Open	58.25	2026-10-21 11:40

Six orders now. Order 1002 was updated in place to Shipped with the new updated_at, 2026-10-21 08:00, and orders 1005 (340.00) and 1006 (58.25) were inserted. Orders 1001, 1003 and 1004 are untouched.

Two tips

Add a condition like WHEN MATCHED AND s.updated_at > t.updated_at, so only a newer row wins and a late file can't overwrite fresher data. And unlike an append, a MERGE can be rerun without doubling anything.

Question 2: The customer dimension must keep address history. How do you build SCD Type 2 on Delta tables?

SCD Type 2 keeps every version of a row. Each row has a start date, an end date and an is_current flag. When the address changes, you close the current row and insert a new one.

Here is the dimension today: customer 7 in Dallas and customer 8 in Denver, both current.

dim_customer2 rows

customer_id	city	start_date	end_date	is_current
7	Dallas	2024-03-01	NULL	true
8	Denver	2025-01-10	NULL	true

And today's extract. Customer 7 moved from Dallas to Austin, customer 8 is unchanged in Denver, and customer 9 is new.

stg_customer3 rows

customer_id	city	effective_date
7	Austin	2026-10-21
8	Denver	2026-10-21
9	Miami	2026-10-21

The trick is one atomic MERGE over a staged source. Changed customers appear twice in it. First with a merge key, so they match the current row and expire it. Then with a NULL merge key, which never matches anything, so that copy is inserted as the new version. Let's run it.

SQL - SCD Type 2 in one MERGE with a staged source

MERGE INTO dim_customer AS t
USING (
  SELECT s.customer_id AS merge_key, s.*
  FROM stg_customer AS s
  UNION ALL
  SELECT CAST(NULL AS INT) AS merge_key, s.*
  FROM stg_customer AS s
  JOIN dim_customer AS d
    ON d.customer_id = s.customer_id
   AND d.is_current = true
  WHERE d.city <> s.city
) AS u
ON t.customer_id = u.merge_key AND t.is_current = true
WHEN MATCHED AND t.city <> u.city THEN
  UPDATE SET is_current = false,
             end_date = u.effective_date
WHEN NOT MATCHED THEN
  INSERT (customer_id, city, start_date, end_date,
          is_current)
  VALUES (u.customer_id, u.city, u.effective_date, NULL,
          true);

The statement runs and changes the data. The check query below shows the result.

SQL - check the data after the change

SELECT customer_id, city, start_date, end_date,
       is_current
FROM dim_customer
ORDER BY customer_id, start_date;

Output after the change4 rows

customer_id	city	start_date	end_date	is_current
7	Dallas	2024-03-01	2026-10-21	false
7	Austin	2026-10-21	NULL	true
8	Denver	2025-01-10	NULL	true
9	Miami	2026-10-21	NULL	true

Customer 7 now has two rows: Dallas is closed with an end_date of 2026-10-21 and is_current false, and Austin is the current row from 2026-10-21. Customer 8 matched, but the city is the same, so Denver is untouched. Customer 9, Miami, had no match and was inserted. The same statement runs in Spark SQL on a Delta table.

Real-project tip

With many tracked columns, compare a hash of them instead of each column. And from a database source, a Copy job also offers SCD Type 2 as an update method, with no code.

Question 3: Two jobs write to the same Delta table and one fails with a concurrency error. Why, and how do you avoid it?

Delta uses optimistic concurrency, with serializable isolation in Fabric. Nobody locks the table, so the conflict only shows up at commit time.

Let's walk through it. Say the nightly MERGE and an OPTIMIZE job both start from version 41:

Each job reads a snapshot of the table.
Each one writes new files.
Each one tries to commit the next version, 42, in the transaction log.
At commit time, Delta checks whether someone else changed the files it read.

With no logical conflict, Delta retries for you. With a real one, the second commit fails.

	No conflict	Conflict
EXAMPLE 1	two appends	MERGE and OPTIMIZE, same files
EXAMPLE 2	OPTIMIZE and an insert	MERGE read a partition with new rows

A MERGE and an OPTIMIZE on the same files fail with a ConcurrentDeleteReadException. A MERGE that read a partition someone else appended to gets a concurrent append exception.

To avoid it:

Partition or cluster by what each writer owns, like the day or the region, and put that column in the MERGE ON condition, so each writer reads its own files.
Schedule OPTIMIZE away from the load window, or use auto compaction.
Run one writer per table, serialized in the pipeline.
Add a retry with backoff for the rare conflict.

The mistake I see most

OPTIMIZE scheduled right on top of the nightly MERGE.

Question 4: The source sends duplicates within the same batch and MERGE fails. Why, and what is the fix?

MERGE allows one source row per target row. If two batch rows match the same target row, nobody can say which one wins.

Here is today's batch. Orders 1002 and 1007 arrive twice.

daily_batch5 rows

order_id	status	updated_at
1002	Packed	2026-10-22 08:00
1002	Shipped	2026-10-22 14:30
1007	Open	2026-10-22 09:00
1007	Cancelled	2026-10-22 16:10
1008	Open	2026-10-22 10:20

Order 1002 is already in silver_orders, so let's see what happens when we merge this batch as it is.

SQL - MERGE a batch that has the same key twice

MERGE INTO silver_orders AS t
USING daily_batch AS s
  ON t.order_id = s.order_id
WHEN MATCHED THEN
  UPDATE SET status = s.status, updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (order_id, status, updated_at)
  VALUES (s.order_id, s.status, s.updated_at);

Error - this query is meant to fail

MERGE command cannot affect row a second time
HINT:  Ensure that not more than one source row matches any one target row.

PostgreSQL refuses it: a MERGE can't affect the same row a second time. Delta fails the same way, with DELTA_MULTIPLE_SOURCE_ROW_MATCHING_TARGET_ROW_IN_MERGE. The worse case is silent: when the duplicate key is not in the target yet, like order 1007, nothing fails and the row is inserted twice.

The fix is to deduplicate the batch before the MERGE. ROW_NUMBER over the business key, ordered by the update time, newest first, and keep row 1.

SQL - Deduplicate the batch: keep the latest row per key

SELECT order_id, status, updated_at
FROM (
  SELECT b.*,
         ROW_NUMBER() OVER (PARTITION BY order_id
                            ORDER BY updated_at DESC) AS rn
  FROM daily_batch AS b
) AS x
WHERE rn = 1
ORDER BY order_id;

Output (PostgreSQL 16)3 rows

order_id	status	updated_at
1002	Shipped	2026-10-22 14:30
1007	Cancelled	2026-10-22 16:10
1008	Open	2026-10-22 10:20

Three rows, one per order. Order 1002 keeps Shipped from 14:30, order 1007 keeps Cancelled from 16:10, and order 1008 stays Open. In PySpark it is the same window, right before the merge.

notebook.py

from pyspark.sql import Window, functions as F

w = (Window.partitionBy("order_id")
     .orderBy(F.col("updated_at").desc()))
batch = (batch.withColumn("rn", F.row_number().over(w))
         .filter("rn = 1").drop("rn"))

Order by the source's real change timestamp, or you may keep an older version.

Check for null keys too

NULL never equals NULL, so a row with a null key never matches and is inserted again on every run.

Question 5: A table is partitioned by day and MERGE scans the whole table. How do you make MERGE prune partitions?

If the ON condition only has the order ID, Spark can't skip anything. Any day could hold a match, so it reads every partition of the table.

The fix is to put the partition column in the ON condition. Match on the date as well as the ID, and add the batch's days as literal values, because Spark can only prune with values it knows when it plans the query.

So the job first reads which days the batch touches. Let's run that on the late batch.

SQL - Which partitions does the batch touch?

SELECT order_date, COUNT(*) AS batch_rows
FROM late_batch
GROUP BY order_date
ORDER BY order_date;

Output (PostgreSQL 16)2 rows

order_date	batch_rows
2026-10-20	2
2026-10-21	2

Two days, 2026-10-20 and 2026-10-21, with 2 rows each. Those two days go into the ON condition, and only those two partitions are read and rewritten.

merge_pruned.sql

MERGE INTO silver.orders AS t
USING updates AS s
ON  t.order_id = s.order_id
AND t.order_date = s.order_date
AND t.order_date IN (DATE'2026-10-20', DATE'2026-10-21')
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

In PySpark, collect the distinct days and build the condition string before the merge.

notebook.py

days = [r.order_date for r in
        updates.select("order_date").distinct().collect()]
in_list = ", ".join(f"DATE'{d}'" for d in days)
cond = ("t.order_id = s.order_id"
        " AND t.order_date = s.order_date"
        f" AND t.order_date IN ({in_list})")
(tgt.alias("t").merge(updates.alias("s"), cond)
    .whenMatchedUpdateAll().whenNotMatchedInsertAll()
    .execute())

There is a bonus: two jobs that load different days now touch different files, so they don't conflict either. That is the fix from Question 3.

For new tables

Microsoft recommends liquid clustering over partitioning for new silver and gold tables, and the clustering column goes in the ON condition the same way. Low shuffle merge is already on by default.

Question 6: The business needs to see the table as it was last Friday. How does Delta time travel work, and how long can you go back?

Every write to a Delta table creates a new version in the transaction log. The old data files are not deleted right away, so you can query any earlier version whose files still exist.

Let's do it in order. First, DESCRIBE HISTORY lists the versions, with the timestamp and the operation. Say version 41 is the MERGE from Friday, October 16. Then query the table with TIMESTAMP AS OF the end of last Friday, or VERSION AS OF that version number. If the business wants the table itself back, RESTORE TABLE rolls it back. RESTORE is a new commit, so the history stays.

time_travel.sql

-- every commit: version, timestamp, operation
DESCRIBE HISTORY silver.orders;

-- the table at the end of last Friday
SELECT * FROM silver.orders
TIMESTAMP AS OF '2026-10-16 23:59:59';

-- or by version number
SELECT * FROM silver.orders VERSION AS OF 41;

-- roll the table back (a new commit)
RESTORE TABLE silver.orders TO VERSION AS OF 41;

How far back can you go? As long as the old files exist. VACUUM removes unreferenced files older than the retention period, 7 days by default. So last Friday works, unless someone vacuumed with a shorter retention. For more history, raise delta.deletedFileRetentionDuration and delta.logRetentionDuration, which is 30 days by default.

Not an archive

Time travel is for audits, debugging and rollbacks. For long-term history, write month-end copies. And time travel on the SQL analytics endpoint is still in preview.

Scenario 7: The silver table got duplicate rows after a rerun. How do you find, fix and prevent it?

First, why it happened. Load 702 appended the October 21 batch to silver_sales, and a later step in the pipeline failed. The pipeline was rerun, and load 703 appended the same batch again.

Let's prove it. Group by the business key and keep the keys with more than one row.

SQL - Scenario: find the orders that were loaded twice

SELECT order_id, COUNT(*) AS copies,
       MIN(load_id) AS first_load,
       MAX(load_id) AS last_load
FROM silver_sales
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY order_id;

Output (PostgreSQL 16)3 rows

order_id	copies	first_load	last_load
2004	2	702	703
2005	2	702	703
2006	2	702	703

Three orders, 2004, 2005 and 2006, each loaded twice: by load 702, and again by load 703.

Now the fix. If the rerun was the last commit on the table, the simplest fix is RESTORE to the version before it. One command, and the history is kept.

restore.sql

-- the rerun was the last commit: roll it back
DESCRIBE HISTORY silver.sales;
RESTORE TABLE silver.sales TO VERSION AS OF 57;

If other loads ran since, delete the extra copies instead and keep each order's first load. We write it as MERGE with WHEN MATCHED THEN DELETE, because Delta in Spark doesn't support subqueries in a DELETE condition. This statement also runs in Spark SQL.

SQL - Delete the extra copies, keep the first load of each order

MERGE INTO silver_sales AS t
USING (SELECT order_id, MIN(load_id) AS first_load
       FROM silver_sales
       GROUP BY order_id) AS f
  ON t.order_id = f.order_id
 AND t.load_id > f.first_load
WHEN MATCHED THEN DELETE;

The statement runs and changes the data. The check query below shows the result.

SQL - check the data after the change

SELECT load_id, COUNT(*) AS row_count,
       SUM(amount) AS amount
FROM silver_sales
GROUP BY load_id
ORDER BY load_id;

Output after the change2 rows

load_id	row_count	amount
701	3	165.00
702	3	225.00

Load 703 is gone. Load 701 keeps its 3 rows with 165.00, and load 702 keeps its 3 rows with 225.00.

To prevent it, make the load idempotent, so a rerun is just another safe run:

MERGE on the business key instead of an append.
replaceWhere to overwrite only the day you load.
A duplicate check after the load that fails the run if it returns rows.

notebook.py

(day_df.write.format("delta").mode("overwrite")
    .option("replaceWhere", "order_date = '2026-10-21'")
    .saveAsTable("silver.sales"))

Remember

Nothing in a Delta table blocks a duplicate key for you. The load design has to.

Key points to remember

MERGE on the business key: update when matched, insert when not
SCD Type 2: one MERGE over a staged source, expire and insert
Conflicts: optimistic concurrency; split writers, move OPTIMIZE
Deduplicate each batch with ROW_NUMBER; partition column in ON
Time travel and RESTORE within the VACUUM retention (7 days default)

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)

Full script - copy and run top to bottom

-- ========================================================================
-- Fabric Interview Q&A #7 - Incremental loads, MERGE and SCD
-- 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_q07;  SET search_path TO sql_q07;
-- ========================================================================

-- Step 0: create and load the sample data
DROP TABLE IF EXISTS silver_orders;
DROP TABLE IF EXISTS orders_batch;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS stg_customer;
DROP TABLE IF EXISTS daily_batch;
DROP TABLE IF EXISTS late_batch;
DROP TABLE IF EXISTS silver_sales;

-- SQL Server: use DATETIME2 instead of TIMESTAMP, and BIT instead of BOOLEAN

-- Q1: the silver orders table, and today's batch of new and changed orders
CREATE TABLE silver_orders (order_id INT, status VARCHAR(12), amount DECIMAL(10,2),
                            order_date DATE, updated_at TIMESTAMP);
INSERT INTO silver_orders VALUES
  (1001, 'Shipped', 120.00, '2026-10-19', '2026-10-19 09:00'),
  (1002, 'Open',     75.50, '2026-10-19', '2026-10-19 10:30'),
  (1003, 'Shipped', 210.00, '2026-10-20', '2026-10-20 08:15'),
  (1004, 'Open',     89.00, '2026-10-20', '2026-10-20 13:45');
CREATE TABLE orders_batch (order_id INT, status VARCHAR(12), amount DECIMAL(10,2),
                           order_date DATE, updated_at TIMESTAMP);
INSERT INTO orders_batch VALUES
  (1002, 'Shipped',  75.50, '2026-10-19', '2026-10-21 08:00'),
  (1005, 'Open',    340.00, '2026-10-21', '2026-10-21 09:15'),
  (1006, 'Open',     58.25, '2026-10-21', '2026-10-21 11:40');

-- Q2: an SCD Type 2 customer dimension, and today's customer extract
CREATE TABLE dim_customer (customer_id INT, city VARCHAR(20), start_date DATE,
                           end_date DATE, is_current BOOLEAN);
INSERT INTO dim_customer VALUES
  (7, 'Dallas', '2024-03-01', NULL, TRUE),
  (8, 'Denver', '2025-01-10', NULL, TRUE);
CREATE TABLE stg_customer (customer_id INT, city VARCHAR(20), effective_date DATE);
INSERT INTO stg_customer VALUES
  (7, 'Austin', '2026-10-21'),
  (8, 'Denver', '2026-10-21'),
  (9, 'Miami',  '2026-10-21');

-- Q4: a batch where the source sent some orders twice
CREATE TABLE daily_batch (order_id INT, status VARCHAR(12), updated_at TIMESTAMP);
INSERT INTO daily_batch VALUES
  (1002, 'Packed',    '2026-10-22 08:00'),
  (1002, 'Shipped',   '2026-10-22 14:30'),
  (1007, 'Open',      '2026-10-22 09:00'),
  (1007, 'Cancelled', '2026-10-22 16:10'),
  (1008, 'Open',      '2026-10-22 10:20');

-- Q5: a late batch for a table partitioned by order_date
CREATE TABLE late_batch (order_id INT, status VARCHAR(12), order_date DATE);
INSERT INTO late_batch VALUES
  (1003, 'Returned', '2026-10-20'),
  (1009, 'Open',     '2026-10-20'),
  (1006, 'Shipped',  '2026-10-21'),
  (1010, 'Open',     '2026-10-21');

-- Q7: a silver table loaded by append; load 703 was a rerun of load 702
CREATE TABLE silver_sales (order_id INT, amount DECIMAL(10,2), order_date DATE, load_id INT);
INSERT INTO silver_sales VALUES
  (2001,  50.00, '2026-10-20', 701), (2002,  80.00, '2026-10-20', 701), (2003, 35.00, '2026-10-20', 701),
  (2004, 120.00, '2026-10-21', 702), (2005,  60.00, '2026-10-21', 702), (2006, 45.00, '2026-10-21', 702),
  (2004, 120.00, '2026-10-21', 703), (2005,  60.00, '2026-10-21', 703), (2006, 45.00, '2026-10-21', 703);


-- Step 1: Upsert the daily batch with MERGE (portable form of the Delta MERGE)
MERGE INTO silver_orders AS t
USING orders_batch AS s
  ON t.order_id = s.order_id
WHEN MATCHED THEN
  UPDATE SET status = s.status, amount = s.amount,
             updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (order_id, status, amount, order_date,
          updated_at)
  VALUES (s.order_id, s.status, s.amount, s.order_date,
          s.updated_at);

-- check the result
SELECT order_id, status, amount, updated_at
FROM silver_orders
ORDER BY order_id;

-- Step 2: SCD Type 2 in one MERGE with a staged source
MERGE INTO dim_customer AS t
USING (
  SELECT s.customer_id AS merge_key, s.*
  FROM stg_customer AS s
  UNION ALL
  SELECT CAST(NULL AS INT) AS merge_key, s.*
  FROM stg_customer AS s
  JOIN dim_customer AS d
    ON d.customer_id = s.customer_id
   AND d.is_current = true
  WHERE d.city <> s.city
) AS u
ON t.customer_id = u.merge_key AND t.is_current = true
WHEN MATCHED AND t.city <> u.city THEN
  UPDATE SET is_current = false,
             end_date = u.effective_date
WHEN NOT MATCHED THEN
  INSERT (customer_id, city, start_date, end_date,
          is_current)
  VALUES (u.customer_id, u.city, u.effective_date, NULL,
          true);

-- check the result
SELECT customer_id, city, start_date, end_date,
       is_current
FROM dim_customer
ORDER BY customer_id, start_date;

-- Step 3: MERGE a batch that has the same key twice
-- This statement is EXPECTED TO FAIL: it shows the constraint protecting the data.
-- Remove the leading -- to run it and see the error.
-- MERGE INTO silver_orders AS t
-- USING daily_batch AS s
--   ON t.order_id = s.order_id
-- WHEN MATCHED THEN
--   UPDATE SET status = s.status, updated_at = s.updated_at
-- WHEN NOT MATCHED THEN
--   INSERT (order_id, status, updated_at)
--   VALUES (s.order_id, s.status, s.updated_at);

-- Step 4: Deduplicate the batch: keep the latest row per key
SELECT order_id, status, updated_at
FROM (
  SELECT b.*,
         ROW_NUMBER() OVER (PARTITION BY order_id
                            ORDER BY updated_at DESC) AS rn
  FROM daily_batch AS b
) AS x
WHERE rn = 1
ORDER BY order_id;

-- Step 5: Which partitions does the batch touch?
SELECT order_date, COUNT(*) AS batch_rows
FROM late_batch
GROUP BY order_date
ORDER BY order_date;

-- Step 6: Scenario: find the orders that were loaded twice
SELECT order_id, COUNT(*) AS copies,
       MIN(load_id) AS first_load,
       MAX(load_id) AS last_load
FROM silver_sales
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY order_id;

-- Step 7: Delete the extra copies, keep the first load of each order
MERGE INTO silver_sales AS t
USING (SELECT order_id, MIN(load_id) AS first_load
       FROM silver_sales
       GROUP BY order_id) AS f
  ON t.order_id = f.order_id
 AND t.load_id > f.first_load
WHEN MATCHED THEN DELETE;

-- check the result
SELECT load_id, COUNT(*) AS row_count,
       SUM(amount) AS amount
FROM silver_sales
GROUP BY load_id
ORDER BY load_id;

Next in this series

Part 8: Direct Lake and Power BI

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.

Welcome To TechBrothersIT

Label

Microsoft Fabric Interview Questions and Answers Part 7 - Incremental Loads, MERGE and SCD

The short answer

Let's create some sample data

Question 1: New and changed orders arrive daily. How do you upsert them into a Delta table with MERGE in PySpark or Spark SQL?

Question 2: The customer dimension must keep address history. How do you build SCD Type 2 on Delta tables?

Question 3: Two jobs write to the same Delta table and one fails with a concurrency error. Why, and how do you avoid it?

Question 4: The source sends duplicates within the same batch and MERGE fails. Why, and what is the fix?

Question 5: A table is partitioned by day and MERGE scans the whole table. How do you make MERGE prune partitions?

Question 6: The business needs to see the table as it was last Friday. How does Delta time travel work, and how long can you go back?

Scenario 7: The silver table got duplicate rows after a rerun. How do you find, fix and prevent it?

Key points to remember

The full SQL script

No comments:

Post a Comment