In this post we are going to cover incremental loads and change data capture in Azure Data Factory: high watermarks, change tracking and CDC, deletes, new files, safe reruns and metadata-driven copies. These are 7 real Azure Data Factory interview questions, asked the way they come up on a real project, and the last one is a scenario where a failed run and a retry leave duplicate orders in the target.
An incremental load copies only what changed since the last run, instead of the whole table every night. Interviewers ask about it because almost every real ADF project needs it, and the hard parts are the details: where the watermark comes from, when it moves, what happens to deletes, and what a rerun does to the target.
Every SQL step below runs on the sample data, and the output under each query is the real result.
Part 4 of the Real Time Azure Data Factory Interview Questions and Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 1: The nightly full load of the orders table takes four hours and only a few thousand rows change. How do you design a high-watermark incremental load in ADF?
- Question 2: Rows are updated in the source but the last-modified column isn't reliable. How do SQL change tracking and change data capture help, and how do they differ?
- Question 3: The incremental load must also capture deletes from the source. How do you handle deletes?
- Question 4: A new file lands in the data lake every hour. How do you copy only new or changed files?
- Question 5: The load must be rerun for last Tuesday after a source fix. How do you make the incremental load safe to rerun?
- Question 6: Your team has two hundred tables to load. What is a metadata-driven copy with a control table, and how does it work?
- Scenario 7: After a failed run and a retry, the target has duplicate orders. Why did it happen, and how do you fix and prevent it?
- The full SQL script
The short answer
An incremental load copies only what changed: a high watermark (Lookup, Copy, Stored procedure), or change tracking or CDC when the timestamp can't be trusted. Catch deletes separately, load files by time-partitioned folders, and make every load idempotent with a MERGE on the business key, so retries and reruns never duplicate rows.
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 and the target of the ADF pipeline: a control table with the watermarks, the source orders, two staging tables (the changed rows and the source keys), the warehouse table and an append-only landing table. The script is written for PostgreSQL, and it runs on SQL Server with small changes, like DATETIME2 instead of TIMESTAMP.
DROP TABLE IF EXISTS etl_control;
DROP TABLE IF EXISTS src_orders;
DROP TABLE IF EXISTS stg_orders;
DROP TABLE IF EXISTS stg_order_keys;
DROP TABLE IF EXISTS dw_orders;
DROP TABLE IF EXISTS orders_landing;
-- SQL Server: use DATETIME2 instead of TIMESTAMP (TIMESTAMP means rowversion there)
-- The control table: one row per source table, with its last loaded watermark
CREATE TABLE etl_control (
table_name VARCHAR(30) PRIMARY KEY,
load_type VARCHAR(12),
watermark_column VARCHAR(30),
watermark_value TIMESTAMP,
is_enabled INT);
INSERT INTO etl_control VALUES
('orders', 'incremental', 'last_modified', '2026-10-19 02:00', 1),
('customers', 'incremental', 'updated_at', '2026-10-19 02:00', 1),
('products', 'full', NULL, NULL, 1),
('order_lines', 'incremental', 'last_modified', '2026-10-19 02:00', 1),
('legacy_returns', 'full', NULL, NULL, 0);
-- The source system's orders table tonight (order 1003 was hard deleted)
CREATE TABLE src_orders (order_id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10,2),
status VARCHAR(12), last_modified TIMESTAMP);
INSERT INTO src_orders VALUES
(1001, 11, 120.00, 'Shipped', '2026-10-18 09:15'),
(1002, 12, 75.50, 'Shipped', '2026-10-18 14:40'),
(1004, 13, 210.00, 'Shipped', '2026-10-19 01:05'),
(1005, 14, 89.00, 'Shipped', '2026-10-19 10:20'),
(1006, 15, 340.00, 'Open', '2026-10-19 16:45'),
(1007, 12, 58.25, 'Open', '2026-10-20 00:30'),
(1008, 16, 150.00, 'Open', '2026-10-20 01:30');
-- The warehouse table after the last successful run
CREATE TABLE dw_orders (order_id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10,2),
status VARCHAR(12), last_modified TIMESTAMP, is_deleted INT);
INSERT INTO dw_orders VALUES
(1001, 11, 120.00, 'Shipped', '2026-10-18 09:15', 0),
(1002, 12, 75.50, 'Shipped', '2026-10-18 14:40', 0),
(1003, 13, 60.00, 'Open', '2026-10-18 16:00', 0),
(1004, 13, 210.00, 'Shipped', '2026-10-19 01:05', 0),
(1005, 14, 99.00, 'Open', '2026-10-18 11:00', 0);
-- What tonight's Copy activity lands in staging: the rows between the two watermarks
CREATE TABLE stg_orders (order_id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10,2),
status VARCHAR(12), last_modified TIMESTAMP);
INSERT INTO stg_orders
SELECT order_id, customer_id, amount, status, last_modified
FROM src_orders
WHERE last_modified > '2026-10-19 02:00' AND last_modified <= '2026-10-20 01:30';
-- Every key that still exists in the source, copied for the delete check
CREATE TABLE stg_order_keys (order_id INT PRIMARY KEY);
INSERT INTO stg_order_keys SELECT order_id FROM src_orders;
-- An append-only landing table: run 501 failed after its copy, the retry (run 502) appended again
CREATE TABLE orders_landing (row_id INT PRIMARY KEY, order_id INT, amount DECIMAL(10,2), run_id INT);
INSERT INTO orders_landing VALUES
(1, 1001, 120.00, 500), (2, 1002, 75.50, 500), (3, 1004, 210.00, 500),
(4, 1005, 89.00, 501), (5, 1006, 340.00, 501), (6, 1007, 58.25, 501), (7, 1008, 150.00, 501),
(8, 1005, 89.00, 502), (9, 1006, 340.00, 502), (10, 1007, 58.25, 502), (11, 1008, 150.00, 502);Here is the source orders table tonight. Order 1003 is missing because it was hard deleted in the source, and four orders changed after the last watermark, 2026-10-19 02:00.
| order_id | customer_id | amount | status | last_modified |
|---|---|---|---|---|
| 1001 | 11 | 120.00 | Shipped | 2026-10-18 09:15 |
| 1002 | 12 | 75.50 | Shipped | 2026-10-18 14:40 |
| 1004 | 13 | 210.00 | Shipped | 2026-10-19 01:05 |
| 1005 | 14 | 89.00 | Shipped | 2026-10-19 10:20 |
| 1006 | 15 | 340.00 | Open | 2026-10-19 16:45 |
| 1007 | 12 | 58.25 | Open | 2026-10-20 00:30 |
| 1008 | 16 | 150.00 | Open | 2026-10-20 01:30 |
Question 1: The nightly full load of the orders table takes four hours and only a few thousand rows change. How do you design a high-watermark incremental load in ADF?
Pick a column that always goes up, like a last modified time or an identity ID, and keep its last loaded value in a control table. Each run copies only the rows above it.
In ADF that is four activities: a Lookup for the old watermark, a Lookup for the new watermark, a Copy for the rows in between, and a Stored procedure that saves the new watermark. Let's run each one.
First, the old watermark, saved by the last successful run.
SELECT watermark_value
FROM etl_control
WHERE table_name = 'orders';| watermark_value |
|---|
| 2026-10-19 02:00 |
It is 2026-10-19 02:00. Now the second Lookup reads the new watermark, the max last modified in the source.
SELECT MAX(last_modified) AS new_watermark
FROM src_orders;| new_watermark |
|---|
| 2026-10-20 01:30 |
The new watermark is 2026-10-20 01:30. In the Copy source query, both values go in with expressions:
SELECT order_id, amount, status, last_modified
FROM dbo.Orders
WHERE last_modified >
'@{activity('OldWM').output.firstRow.watermark_value}'
AND last_modified <=
'@{activity('NewWM').output.firstRow.new_watermark}'At run time it resolves to this query:
SELECT order_id, amount, status, last_modified
FROM src_orders
WHERE last_modified > '2026-10-19 02:00'
AND last_modified <= '2026-10-20 01:30'
ORDER BY order_id;| order_id | amount | status | last_modified |
|---|---|---|---|
| 1005 | 89.00 | Shipped | 2026-10-19 10:20 |
| 1006 | 340.00 | Open | 2026-10-19 16:45 |
| 1007 | 58.25 | Open | 2026-10-20 00:30 |
| 1008 | 150.00 | Open | 2026-10-20 01:30 |
Four changed rows, orders 1005, 1006, 1007 and 1008, not the whole table. Order 1008 at exactly 01:30 is included because the upper bound uses <=.
Last, the Stored procedure activity moves the watermark.
UPDATE etl_control
SET watermark_value = '2026-10-20 01:30'
WHERE table_name = 'orders';The statement runs and changes the data. The check query below shows the result.
SELECT table_name, watermark_value
FROM etl_control
WHERE table_name = 'orders';| table_name | watermark_value |
|---|---|
| orders | 2026-10-20 01:30 |
The control table now says 2026-10-20 01:30. In SQL Server, keep that UPDATE in a stored procedure, and store the watermark as DATETIME2, because TIMESTAMP there means rowversion.
Read the new watermark before the copy, so rows that arrive during the copy are picked up next time. And update it only after the copy succeeds.
Question 2: Rows are updated in the source but the last-modified column isn't reliable. How do SQL change tracking and change data capture help, and how do they differ?
Both are SQL Server and Azure SQL features, and neither needs a timestamp. The database itself records what changed, including deletes.
| Change tracking | Change data capture | |
|---|---|---|
| HOW | synchronous, inside each transaction | reads the transaction log, async |
| RECORDS | changed keys and the operation | every change, with column values |
| YOU GET | net change: join back for the row | history, before and after an update |
| COST | lightweight; mind the retention | change tables and a capture process |
| IN ADF | Lookup, Copy with CHANGETABLE | net changes function, data flow CDC |
Change tracking is the lightweight one. It records which keys changed, and the operation, inside each transaction. You get the net change, not the old values, so you join back to the table for the current row. Keep the retention longer than the gap between loads.
ALTER DATABASE Sales SET CHANGE_TRACKING = ON;
ALTER TABLE dbo.Orders ENABLE CHANGE_TRACKING;
-- each run: what changed since the last version
SELECT ct.OrderID, ct.SYS_CHANGE_OPERATION, o.Status
FROM CHANGETABLE(CHANGES dbo.Orders, @last_version) AS ct
LEFT JOIN dbo.Orders AS o ON o.OrderID = ct.OrderID;In ADF, change tracking is two Lookups for the last and the current version, a Copy that joins the change table to the source, and a Stored procedure that saves the new version.
Change data capture reads the transaction log asynchronously and writes every change into change tables, with the values before and after an update. You get full history, at the cost of storage and a capture process. In ADF, query the net changes function, or use the CDC source in a mapping data flow.
-- CDC: net changes between two LSNs
SELECT *
FROM cdc.fn_cdc_get_net_changes_dbo_Orders(
@from_lsn, @to_lsn, 'all');The ADF change data capture resource, a continuously running CDC job, is still in preview, with no self-hosted runtime.
Latest state: change tracking. History: CDC.
Question 3: The incremental load must also capture deletes from the source. How do you handle deletes?
A watermark only sees rows that still exist. A hard delete leaves nothing to select, so the row lives on in the warehouse. There are three ways to catch it:
- Change tracking or CDC, which return delete operations with the changes.
- A soft delete in the source: an is_deleted flag that also updates last_modified, so the watermark sees it.
- A key comparison: when the source can't help, copy just the key column of the whole source into staging, and compare.
Let's try the third one. Any warehouse order with no matching key in stg_order_keys was deleted.
SELECT d.order_id, d.status, d.amount
FROM dw_orders d
WHERE NOT EXISTS (
SELECT 1 FROM stg_order_keys k
WHERE k.order_id = d.order_id)
ORDER BY d.order_id;| order_id | status | amount |
|---|---|---|
| 1003 | Open | 60.00 |
One row: order 1003, Open, 60.00. It is in the warehouse but no longer in the source.
Now we mark it instead of removing it. A soft delete keeps history, and reports filter on is_deleted = 0.
UPDATE dw_orders
SET is_deleted = 1
WHERE NOT EXISTS (
SELECT 1 FROM stg_order_keys k
WHERE k.order_id = dw_orders.order_id);The statement runs and changes the data. The check query below shows the result.
SELECT order_id, status, is_deleted
FROM dw_orders
ORDER BY order_id;| order_id | status | is_deleted |
|---|---|---|
| 1001 | Shipped | 0 |
| 1002 | Shipped | 0 |
| 1003 | Open | 1 |
| 1004 | Shipped | 0 |
| 1005 | Open | 0 |
Order 1003 now has is_deleted set to 1, and the other four orders stay at 0.
The key copy is heavier than the incremental load, so run it nightly or weekly, keys only, not every hour.
Question 4: A new file lands in the data lake every hour. How do you copy only new or changed files?
There are three ways, and they are not equal.
| How it works | Watch out | |
|---|---|---|
| LAST MODIFIED | filter by start and end time, in UTC | lists every file: slow on big folders |
| PARTITIONED FOLDERS | read only the window's yyyy/MM/dd/HH folder | the most performant pattern |
| STORAGE EVENT | fires per new blob, passes the file name | ADLS Gen2 or GPv2, Event Grid |
The simplest is the last modified filter on the copy source: a start and an end time, in UTC, and it copies only the files modified in that window.
"storeSettings": {
"type": "AzureBlobFSReadSettings",
"modifiedDatetimeStart": "@pipeline().parameters.start",
"modifiedDatetimeEnd": "@pipeline().parameters.end"
}Feed it from a tumbling window trigger. Each hourly run gets its exact window as parameters, and a failed hour can be rerun alone.
"parameters": {
"start": "@trigger().outputs.windowStartTime",
"end": "@trigger().outputs.windowEndTime"
}The catch: ADF still lists every file in the folder to check the dates. With years of files, listing takes longer than copying. Time-partitioned folders are the most performant pattern: a year, month, day and hour path, and you read only that window's folder.
@formatDateTime(pipeline().parameters.start,'yyyy/MM/dd/HH')The third option is a storage event trigger. It fires when a blob is created, and passes the file name and folder path to the pipeline. It needs ADLS Gen2 or general purpose v2 storage, and Event Grid.
One limit: the Copy Data tool's metadata-driven task loads files in full only.
For files, design the folder layout first. It makes reruns trivial.
Question 5: The load must be rerun for last Tuesday after a source fix. How do you make the incremental load safe to rerun?
A rerun is safe only if the load is idempotent: run it once or five times, same result. Plain appends are not. So load the target with a MERGE on the business key, or delete the slice first.
The ADF pattern: the Copy lands the slice in staging, with a truncate pre-copy script.
"sink": {
"type": "AzureSqlSink",
"preCopyScript": "TRUNCATE TABLE stg.Orders"
}Then a Stored procedure activity runs this MERGE. Matched keys are updated when the staged row is at least as new, and new keys are inserted.
MERGE INTO dw_orders AS t
USING stg_orders AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.last_modified >= t.last_modified THEN
UPDATE SET amount = s.amount, status = s.status,
last_modified = s.last_modified
WHEN NOT MATCHED THEN
INSERT (order_id, customer_id, amount, status,
last_modified, is_deleted)
VALUES (s.order_id, s.customer_id, s.amount, s.status,
s.last_modified, 0);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 dw_orders;| row_count | total_amount |
|---|---|
| 8 | 1102.75 |
8 rows, with a total of 1102.75. The warehouse had 5 orders, including the soft-deleted 1003. Order 1005 was updated from 99.00 to 89.00, and orders 1006, 1007 and 1008 were inserted. Now let's rerun the exact same MERGE.
MERGE INTO dw_orders AS t
USING stg_orders AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.last_modified >= t.last_modified THEN
UPDATE SET amount = s.amount, status = s.status,
last_modified = s.last_modified
WHEN NOT MATCHED THEN
INSERT (order_id, customer_id, amount, status,
last_modified, is_deleted)
VALUES (s.order_id, s.customer_id, s.amount, s.status,
s.last_modified, 0);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 dw_orders;| row_count | total_amount |
|---|---|
| 8 | 1102.75 |
Still 8 rows, and the same total, 1102.75. Nothing doubled. MERGE works the same way in SQL Server.
For last Tuesday, pass that window's start and end as parameters, or rerun that tumbling window. Never compute the window from utcNow() inside the pipeline, or the rerun loads today.
Don't move the global watermark backwards during a backfill. And keep the newer-row condition, so an old slice can't overwrite a row that changed since.
Question 6: Your team has two hundred tables to load. What is a metadata-driven copy with a control table, and how does it work?
Instead of two hundred pipelines, you build one. A control table holds a row per table, and the pipeline loops over it. Adding a table becomes an insert, not a deployment.
The control table needs the source and target names, the load type (full or incremental), the watermark column and its last value, and an enabled flag. Let's run the query the Lookup uses.
SELECT table_name, load_type, watermark_column,
watermark_value
FROM etl_control
WHERE is_enabled = 1
ORDER BY table_name;| table_name | load_type | watermark_column | watermark_value |
|---|---|---|---|
| customers | incremental | updated_at | 2026-10-19 02:00 |
| order_lines | incremental | last_modified | 2026-10-19 02:00 |
| orders | incremental | last_modified | 2026-10-20 01:30 |
| products | full | NULL | NULL |
Four enabled tables come back: customers, order_lines, orders and products. The legacy_returns table is disabled, so it doesn't come back at all. Notice orders already shows 2026-10-20 01:30, the watermark we saved in Question 1.
Then it works like this, in order:
- A Lookup runs that query.
- A ForEach loops over its output, one item per table.
- Inside the loop, a parameterized Copy builds its query from
item(), the current row. - A Stored procedure saves that table's new watermark.
"items": {
"value": "@activity('GetTables').output.value",
"type": "Expression"
},
"isSequential": false,
"batchCount": 20SELECT * FROM @{item().table_name}
WHERE @{item().watermark_column} >
'@{item().watermark_value}'Know the limits. A Lookup returns at most 5,000 rows, and a ForEach runs 20 items in parallel by default, up to 50. Set the batch count to what the source can handle.
The Copy Data tool's metadata-driven copy task can generate the control table, the stored procedure and three levels of pipelines. Either way, plan one pipeline set per source type.
Scenario 7: After a failed run and a retry, the target has duplicate orders. Why did it happen, and how do you fix and prevent it?
Here is how it happens. The copy appends with bulk insert. Run 501 copied its rows, then a later step failed, so the watermark never moved. The retry, run 502, appended the same window again.
First, prove it. Group by the business key, not the surrogate row_id, and keep the keys with more than one copy.
SELECT order_id, COUNT(*) AS copies,
MIN(run_id) AS first_run, MAX(run_id) AS last_run
FROM orders_landing
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY order_id;| order_id | copies | first_run | last_run |
|---|---|---|---|
| 1005 | 2 | 501 | 502 |
| 1006 | 2 | 501 | 502 |
| 1007 | 2 | 501 | 502 |
| 1008 | 2 | 501 | 502 |
Four orders, 1005 to 1008, each loaded twice: by run 501, and again by run 502.
To fix it, number the copies of each order with ROW_NUMBER, and delete everything after the first.
DELETE FROM orders_landing
WHERE row_id IN (
SELECT row_id
FROM (SELECT row_id,
ROW_NUMBER() OVER (PARTITION BY order_id
ORDER BY row_id) AS rn
FROM orders_landing) d
WHERE rn > 1);The statement runs and changes the data. The check query below shows the result.
SELECT run_id, COUNT(*) AS rows_loaded
FROM orders_landing
GROUP BY run_id
ORDER BY run_id;| run_id | rows_loaded |
|---|---|
| 500 | 3 |
| 501 | 4 |
Run 500 keeps its 3 rows, run 501 keeps its 4, and run 502 is gone.
To prevent it, add a unique index on the business key, the order ID.
CREATE UNIQUE INDEX ux_orders_landing_order
ON orders_landing (order_id);The statement runs without returning rows.
Now let's try the same append again.
INSERT INTO orders_landing
VALUES (12, 1008, 150.00, 503);duplicate key value violates unique constraint "ux_orders_landing_order"
DETAIL: Key (order_id)=(1008) already exists.The insert fails with a duplicate key error on order 1008. A failed run and an alert are better than silently doubled revenue.
Then fix the design, so a retry is just another safe run:
- MERGE or native upsert on the key, instead of an append.
- A pre-copy script that deletes the slice before loading it.
- Watermark last, updated on success only.
Key points to remember
- High watermark: Lookup old and new, Copy between, Stored procedure last
- Change tracking for net changes, CDC for history (ADF CDC resource: preview)
- Watermarks miss deletes: CT/CDC, soft deletes or a key comparison
- Files: time-partitioned folders beat the last modified filter
- Make loads idempotent: MERGE on the key, watermark on success only
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)
-- ========================================================================
-- ADF Interview Q&A #4 - Incremental loads and change data capture
-- 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_q04; SET search_path TO sql_q04;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS etl_control;
DROP TABLE IF EXISTS src_orders;
DROP TABLE IF EXISTS stg_orders;
DROP TABLE IF EXISTS stg_order_keys;
DROP TABLE IF EXISTS dw_orders;
DROP TABLE IF EXISTS orders_landing;
-- SQL Server: use DATETIME2 instead of TIMESTAMP (TIMESTAMP means rowversion there)
-- The control table: one row per source table, with its last loaded watermark
CREATE TABLE etl_control (
table_name VARCHAR(30) PRIMARY KEY,
load_type VARCHAR(12),
watermark_column VARCHAR(30),
watermark_value TIMESTAMP,
is_enabled INT);
INSERT INTO etl_control VALUES
('orders', 'incremental', 'last_modified', '2026-10-19 02:00', 1),
('customers', 'incremental', 'updated_at', '2026-10-19 02:00', 1),
('products', 'full', NULL, NULL, 1),
('order_lines', 'incremental', 'last_modified', '2026-10-19 02:00', 1),
('legacy_returns', 'full', NULL, NULL, 0);
-- The source system's orders table tonight (order 1003 was hard deleted)
CREATE TABLE src_orders (order_id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10,2),
status VARCHAR(12), last_modified TIMESTAMP);
INSERT INTO src_orders VALUES
(1001, 11, 120.00, 'Shipped', '2026-10-18 09:15'),
(1002, 12, 75.50, 'Shipped', '2026-10-18 14:40'),
(1004, 13, 210.00, 'Shipped', '2026-10-19 01:05'),
(1005, 14, 89.00, 'Shipped', '2026-10-19 10:20'),
(1006, 15, 340.00, 'Open', '2026-10-19 16:45'),
(1007, 12, 58.25, 'Open', '2026-10-20 00:30'),
(1008, 16, 150.00, 'Open', '2026-10-20 01:30');
-- The warehouse table after the last successful run
CREATE TABLE dw_orders (order_id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10,2),
status VARCHAR(12), last_modified TIMESTAMP, is_deleted INT);
INSERT INTO dw_orders VALUES
(1001, 11, 120.00, 'Shipped', '2026-10-18 09:15', 0),
(1002, 12, 75.50, 'Shipped', '2026-10-18 14:40', 0),
(1003, 13, 60.00, 'Open', '2026-10-18 16:00', 0),
(1004, 13, 210.00, 'Shipped', '2026-10-19 01:05', 0),
(1005, 14, 99.00, 'Open', '2026-10-18 11:00', 0);
-- What tonight's Copy activity lands in staging: the rows between the two watermarks
CREATE TABLE stg_orders (order_id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10,2),
status VARCHAR(12), last_modified TIMESTAMP);
INSERT INTO stg_orders
SELECT order_id, customer_id, amount, status, last_modified
FROM src_orders
WHERE last_modified > '2026-10-19 02:00' AND last_modified <= '2026-10-20 01:30';
-- Every key that still exists in the source, copied for the delete check
CREATE TABLE stg_order_keys (order_id INT PRIMARY KEY);
INSERT INTO stg_order_keys SELECT order_id FROM src_orders;
-- An append-only landing table: run 501 failed after its copy, the retry (run 502) appended again
CREATE TABLE orders_landing (row_id INT PRIMARY KEY, order_id INT, amount DECIMAL(10,2), run_id INT);
INSERT INTO orders_landing VALUES
(1, 1001, 120.00, 500), (2, 1002, 75.50, 500), (3, 1004, 210.00, 500),
(4, 1005, 89.00, 501), (5, 1006, 340.00, 501), (6, 1007, 58.25, 501), (7, 1008, 150.00, 501),
(8, 1005, 89.00, 502), (9, 1006, 340.00, 502), (10, 1007, 58.25, 502), (11, 1008, 150.00, 502);
-- Step 1: Lookup 1: read the old watermark from the control table
SELECT watermark_value
FROM etl_control
WHERE table_name = 'orders';
-- Step 2: Lookup 2: read the new watermark from the source, before the copy
SELECT MAX(last_modified) AS new_watermark
FROM src_orders;
-- Step 3: Copy: select only the rows between the two watermarks
SELECT order_id, amount, status, last_modified
FROM src_orders
WHERE last_modified > '2026-10-19 02:00'
AND last_modified <= '2026-10-20 01:30'
ORDER BY order_id;
-- Step 4: Stored procedure: move the watermark, only after the copy succeeded
UPDATE etl_control
SET watermark_value = '2026-10-20 01:30'
WHERE table_name = 'orders';
-- check the result
SELECT table_name, watermark_value
FROM etl_control
WHERE table_name = 'orders';
-- Step 5: Find hard deletes: warehouse keys that no longer exist in the source
SELECT d.order_id, d.status, d.amount
FROM dw_orders d
WHERE NOT EXISTS (
SELECT 1 FROM stg_order_keys k
WHERE k.order_id = d.order_id)
ORDER BY d.order_id;
-- Step 6: Soft delete them in the warehouse
UPDATE dw_orders
SET is_deleted = 1
WHERE NOT EXISTS (
SELECT 1 FROM stg_order_keys k
WHERE k.order_id = dw_orders.order_id);
-- check the result
SELECT order_id, status, is_deleted
FROM dw_orders
ORDER BY order_id;
-- Step 7: Idempotent load: MERGE the staged slice into the target
MERGE INTO dw_orders AS t
USING stg_orders AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.last_modified >= t.last_modified THEN
UPDATE SET amount = s.amount, status = s.status,
last_modified = s.last_modified
WHEN NOT MATCHED THEN
INSERT (order_id, customer_id, amount, status,
last_modified, is_deleted)
VALUES (s.order_id, s.customer_id, s.amount, s.status,
s.last_modified, 0);
-- check the result
SELECT COUNT(*) AS row_count, SUM(amount) AS total_amount
FROM dw_orders;
-- Step 8: Rerun the same MERGE: nothing doubles
MERGE INTO dw_orders AS t
USING stg_orders AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.last_modified >= t.last_modified THEN
UPDATE SET amount = s.amount, status = s.status,
last_modified = s.last_modified
WHEN NOT MATCHED THEN
INSERT (order_id, customer_id, amount, status,
last_modified, is_deleted)
VALUES (s.order_id, s.customer_id, s.amount, s.status,
s.last_modified, 0);
-- check the result
SELECT COUNT(*) AS row_count, SUM(amount) AS total_amount
FROM dw_orders;
-- Step 9: Metadata-driven: the Lookup query that feeds the ForEach
SELECT table_name, load_type, watermark_column,
watermark_value
FROM etl_control
WHERE is_enabled = 1
ORDER BY table_name;
-- Step 10: Scenario: find the orders that were loaded twice
SELECT order_id, COUNT(*) AS copies,
MIN(run_id) AS first_run, MAX(run_id) AS last_run
FROM orders_landing
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY order_id;
-- Step 11: Delete the extra copies, keep the first row of each order
DELETE FROM orders_landing
WHERE row_id IN (
SELECT row_id
FROM (SELECT row_id,
ROW_NUMBER() OVER (PARTITION BY order_id
ORDER BY row_id) AS rn
FROM orders_landing) d
WHERE rn > 1);
-- check the result
SELECT run_id, COUNT(*) AS rows_loaded
FROM orders_landing
GROUP BY run_id
ORDER BY run_id;
-- Step 12: Prevent it: a unique key on the business key
CREATE UNIQUE INDEX ux_orders_landing_order
ON orders_landing (order_id);
-- Step 13: A repeated append now fails loudly
-- This statement is EXPECTED TO FAIL: it shows the constraint protecting the data.
-- Remove the leading -- to run it and see the error.
-- INSERT INTO orders_landing
-- VALUES (12, 1008, 150.00, 503);
Part 5: Control Flow and Error Handling
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 Azure Data Factory 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.