Snowflake Interview Questions and Answers Part 8 - Incremental Loads, MERGE and SCD

Snowflake Interview Q&A · Part 8 of 10

In this post we are going to cover incremental loads, MERGE and SCD in Snowflake: upserts with MERGE, the nondeterministic MERGE error, duplicate staging rows, SCD Type 2, idempotent loads and deletes. These are 7 real Snowflake interview questions, asked the way they come up on a real project, and the last one is a scenario where the fact table totals are wrong after a failed run and a retry.

An incremental load moves only what changed since the last run, instead of reloading the whole table every night. In Snowflake that usually means a MERGE from a staging table into the target. Interviewers ask about it because every pipeline needs it, and the hard parts are the details: duplicate keys in the source, history in a dimension, deleted rows, and what a rerun does to the data.

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

Part 8 of the Real Time Snowflake Interview Questions and Answers series on TechBrothersIT.

On this page
  1. The short answer
  2. Let's create some sample data
  3. Question 1: New and changed orders arrive daily in a staging table. How do you upsert them into the target with MERGE?
  4. Question 2: MERGE fails with a nondeterministic error. Why, and how do you fix the source?
  5. Question 3: The staging table has duplicate rows for the same order. How do you keep only the latest one?
  6. Question 4: The customer dimension must keep address history. How do you build SCD Type 2 in Snowflake?
  7. Question 5: The nightly job ran twice and loaded duplicates. How do you make the load idempotent?
  8. Question 6: Source rows are deleted and the target still shows them. How do you handle deletes in an incremental load?
  9. Scenario 7: After a failed run and a retry, the fact table totals are wrong. How do you find, fix and prevent it?
  10. The full SQL script

The short answer

Say this first

Load changes with MERGE on the business key: update when matched, insert when not. Make the source one row per key (ROW_NUMBER, or QUALIFY in Snowflake) so MERGE stays deterministic, build SCD Type 2 as close-and-insert in one transaction, and keep every load idempotent, with deletes handled separately and a totals check after each run.

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 target tables of a Snowflake load: an orders target with two staging batches, a customer dimension with its daily extract, the source's order keys, and a sales fact with the control totals the source reported. The script is written for PostgreSQL, and it runs in Snowflake with small changes, like TIMESTAMP_NTZ instead of TIMESTAMP.

Sample data - SQL script (PostgreSQL)
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS stg_orders;
DROP TABLE IF EXISTS stg_orders_d2;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS stg_customer;
DROP TABLE IF EXISTS src_order_keys;
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS source_totals;

-- Snowflake: TIMESTAMP_NTZ instead of TIMESTAMP; everything else runs as is

-- Q1: the target orders table, and today's staging rows (one changed, two new)
CREATE TABLE orders (order_id INT, status VARCHAR(12), amount DECIMAL(10,2),
                     updated_at TIMESTAMP);
INSERT INTO orders VALUES
  (1001, 'Shipped', 100.00, '2026-10-20 09:00'),
  (1002, 'Open',    250.00, '2026-10-20 10:30'),
  (1003, 'Shipped',  80.00, '2026-10-20 11:15'),
  (1004, 'Open',     60.00, '2026-10-20 13:45');
CREATE TABLE stg_orders (order_id INT, status VARCHAR(12), amount DECIMAL(10,2),
                         updated_at TIMESTAMP);
INSERT INTO stg_orders VALUES
  (1002, 'Shipped', 250.00, '2026-10-21 08:00'),
  (1005, 'Open',     40.00, '2026-10-21 09:15'),
  (1006, 'Open',    150.00, '2026-10-21 11:40');

-- Q2 and Q3: the next day's staging, where the source sent two orders twice
CREATE TABLE stg_orders_d2 (order_id INT, status VARCHAR(12), amount DECIMAL(10,2),
                            updated_at TIMESTAMP);
INSERT INTO stg_orders_d2 VALUES
  (1004, 'Packed',    60.00, '2026-10-22 08:00'),
  (1004, 'Shipped',   60.00, '2026-10-22 14:30'),
  (1007, 'Open',      35.00, '2026-10-22 09:00'),
  (1007, 'Cancelled', 35.00, '2026-10-22 16:10'),
  (1008, 'Open',      90.00, '2026-10-22 10:20');

-- Q4: an SCD Type 2 customer dimension, and today's customer extract
CREATE TABLE dim_customer (customer_id INT, city VARCHAR(20), valid_from DATE,
                           valid_to DATE, is_current BOOLEAN);
INSERT INTO dim_customer VALUES
  (21, 'Dallas', '2024-03-01', NULL, TRUE),
  (22, 'Denver', '2025-01-10', NULL, TRUE);
CREATE TABLE stg_customer (customer_id INT, city VARCHAR(20), changed_on DATE);
INSERT INTO stg_customer VALUES
  (21, 'Austin', '2026-10-21'),
  (22, 'Denver', '2026-10-21'),
  (23, 'Miami',  '2026-10-21');

-- Q6: every order key that still exists in the source tonight (1003 was hard deleted)
CREATE TABLE src_order_keys (order_id INT);
INSERT INTO src_order_keys VALUES (1001), (1002), (1004), (1005), (1006);

-- Q7: the fact table after load 802 failed at a later step and load 803 (the retry) inserted again
CREATE TABLE fact_sales (sale_id INT, sale_date DATE, amount DECIMAL(10,2), load_id INT);
INSERT INTO fact_sales VALUES
  (5001, '2026-10-20', 120.00, 801), (5002, '2026-10-20',  80.00, 801), (5003, '2026-10-20', 40.00, 801),
  (5004, '2026-10-21', 200.00, 802), (5005, '2026-10-21', 100.00, 802), (5006, '2026-10-21', 50.00, 802),
  (5004, '2026-10-21', 200.00, 803), (5005, '2026-10-21', 100.00, 803), (5006, '2026-10-21', 50.00, 803);
-- the control totals the source system reported for each day
CREATE TABLE source_totals (sale_date DATE, src_rows INT, src_amount DECIMAL(10,2));
INSERT INTO source_totals VALUES ('2026-10-20', 3, 240.00), ('2026-10-21', 3, 350.00);

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

orders4 rows
order_idstatusamountupdated_at
1001Shipped100.002026-10-20 09:00
1002Open250.002026-10-20 10:30
1003Shipped80.002026-10-20 11:15
1004Open60.002026-10-20 13:45

Question 1: New and changed orders arrive daily in a staging table. How do you upsert them into the target with MERGE?

MERGE joins the staging table to the target on the business key. Matched rows are updated, rows with no match are inserted, all in one statement.

Here is today's staging table. Order 1002 changed to Shipped, and orders 1005 and 1006 are new.

stg_orders3 rows
order_idstatusamountupdated_at
1002Shipped250.002026-10-21 08:00
1005Open40.002026-10-21 09:15
1006Open150.002026-10-21 11:40

And the target it goes into, with four orders:

orders4 rows
order_idstatusamountupdated_at
1001Shipped100.002026-10-20 09:00
1002Open250.002026-10-20 10:30
1003Shipped80.002026-10-20 11:15
1004Open60.002026-10-20 13:45

Now the statement. MERGE into orders, using the staging table, on the order ID. When matched, and the staging row is newer, update. When not matched, insert. Let's run it.

SQL - Upsert the staging rows into the target with MERGE
MERGE INTO orders AS t
USING stg_orders AS s
  ON t.order_id = s.order_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN
  UPDATE SET status = s.status, amount = s.amount,
             updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (order_id, status, amount, updated_at)
  VALUES (s.order_id, s.status, s.amount,
          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 orders
ORDER BY order_id;
Output after the change6 rows
order_idstatusamountupdated_at
1001Shipped100.002026-10-20 09:00
1002Shipped250.002026-10-21 08:00
1003Shipped80.002026-10-20 11:15
1004Open60.002026-10-20 13:45
1005Open40.002026-10-21 09:15
1006Open150.002026-10-21 11:40

The target has six orders now. Order 1002 was updated in place to Shipped, with the new updated_at of 2026-10-21 08:00. Orders 1005 (40.00) and 1006 (150.00) were inserted, and 1001, 1003 and 1004 are untouched.

In Snowflake, you can shorten it with UPDATE ALL BY NAME and INSERT ALL BY NAME. The column names and their count must match, but the order can differ.

snowflake_by_name.sql
MERGE INTO orders AS t USING stg_orders AS s
  ON t.order_id = s.order_id
WHEN MATCHED AND s.updated_at > t.updated_at
  THEN UPDATE ALL BY NAME
WHEN NOT MATCHED THEN INSERT ALL BY NAME;
My tip

Keep that newer-row condition. Then a late file with an old version can't overwrite fresher data.

Question 2: MERGE fails with a nondeterministic error. Why, and how do you fix the source?

A MERGE needs at most one source row per target row. If two source rows match the same target row, nobody can say which version should win.

Here is the next day's batch. Orders 1004 and 1007 each arrive twice.

stg_orders_d25 rows
order_idstatusamountupdated_at
1004Packed60.002026-10-22 08:00
1004Shipped60.002026-10-22 14:30
1007Open35.002026-10-22 09:00
1007Cancelled35.002026-10-22 16:10
1008Open90.002026-10-22 10:20

Order 1004 is already in the target, 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 orders AS t
USING stg_orders_d2 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, amount, updated_at)
  VALUES (s.order_id, s.status, s.amount,
          s.updated_at);
Error - this query is meant to failMERGE 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. Snowflake refuses to guess too. The parameter ERROR_ON_NONDETERMINISTIC_MERGE is TRUE by default, so the MERGE fails. If you set it to FALSE, Snowflake picks one of the rows, and which one is not defined.

The dangerous case is the quiet one. Order 1007 is not in the target yet, and the insert branch never errors, so it would simply be inserted twice.

The fix belongs in the source, not in the parameter. First, find the duplicate keys: group by the order ID, having a count greater than one.

SQL - Find the duplicate keys in staging
SELECT order_id, COUNT(*) AS copies
FROM stg_orders_d2
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY order_id;
Output (PostgreSQL 16)2 rows
order_idcopies
10042
10072

Two keys, 1004 and 1007, with 2 copies each. Then make the source one row per key: group by the key, as the Snowflake docs suggest, or keep the latest row, which is the next question.

Don't flip the parameter

Setting it to FALSE hides the error, but you still get an undefined row in the target.

Question 3: The staging table has duplicate rows for the same order. How do you keep only the latest one?

Number the copies of each order with ROW_NUMBER, partitioned by the order ID and ordered by the update time, newest first. Then keep row 1.

Here is the batch again: 1004 arrives as Packed and then Shipped, 1007 as Open and then Cancelled.

stg_orders_d25 rows
order_idstatusamountupdated_at
1004Packed60.002026-10-22 08:00
1004Shipped60.002026-10-22 14:30
1007Open35.002026-10-22 09:00
1007Cancelled35.002026-10-22 16:10
1008Open90.002026-10-22 10:20

In portable SQL, the row number goes in a subquery, and the outer query filters on it. Let's run it.

SQL - Keep the latest row per order (ROW_NUMBER in a subquery)
SELECT order_id, status, updated_at
FROM (
  SELECT b.*,
         ROW_NUMBER() OVER (PARTITION BY order_id
                            ORDER BY updated_at DESC) AS rn
  FROM stg_orders_d2 AS b
) AS x
WHERE rn = 1
ORDER BY order_id;
Output (PostgreSQL 16)3 rows
order_idstatusupdated_at
1004Shipped2026-10-22 14:30
1007Cancelled2026-10-22 16:10
1008Open2026-10-22 10:20

Three rows, one per order. Order 1004 keeps Shipped from 14:30, order 1007 keeps Cancelled from 16:10, and order 1008 stays Open as it is.

In Snowflake, the shortcut is QUALIFY. It filters on a window function the way HAVING filters on an aggregate, so you don't need the subquery.

snowflake_qualify.sql
SELECT *
FROM stg_orders_d2
QUALIFY ROW_NUMBER() OVER (
          PARTITION BY order_id
          ORDER BY updated_at DESC) = 1;
Real-project tip

Order by the source's real change time, and add a tie breaker, like the load time or the file row number. If two copies share a timestamp, ROW_NUMBER can pick either one, and the result can change between runs.

Question 4: The customer dimension must keep address history. How do you build SCD Type 2 in Snowflake?

SCD Type 2 keeps every version of a customer. Each row has a valid_from date, a valid_to date and an is_current flag. When the city changes, you close the current row and insert a new one.

Here is today's customer extract. Customer 21 moved from Dallas to Austin, customer 22 is still in Denver, and customer 23 is new.

stg_customer3 rows
customer_idcitychanged_on
21Austin2026-10-21
22Denver2026-10-21
23Miami2026-10-21

And the dimension before the load, with two current rows:

dim_customer2 rows
customer_idcityvalid_fromvalid_tois_current
21Dallas2024-03-01NULLtrue
22Denver2025-01-10NULLtrue

Step one closes the current rows whose tracked columns changed: set is_current to FALSE, and valid_to to the change date.

SQL - SCD Type 2, step 1: close the current rows that changed
UPDATE dim_customer AS d
SET is_current = FALSE,
    valid_to = s.changed_on
FROM stg_customer AS s
WHERE d.customer_id = s.customer_id
  AND d.is_current = TRUE
  AND d.city <> s.city;

The statement runs without returning rows.

Step two inserts a new version for every staged customer that has no current row left. That's the changed customers, plus the new ones. Unchanged customers still have a current row, so they are skipped.

SQL - SCD Type 2, step 2: insert a new version for every key with no current row
INSERT INTO dim_customer
  (customer_id, city, valid_from, valid_to, is_current)
SELECT s.customer_id, s.city, s.changed_on,
       CAST(NULL AS DATE), TRUE
FROM stg_customer AS s
LEFT JOIN dim_customer AS d
  ON d.customer_id = s.customer_id
 AND d.is_current = TRUE
WHERE d.customer_id IS NULL;

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, valid_from, valid_to,
       is_current
FROM dim_customer
ORDER BY customer_id, valid_from;
Output after the change4 rows
customer_idcityvalid_fromvalid_tois_current
21Dallas2024-03-012026-10-21false
21Austin2026-10-21NULLtrue
22Denver2025-01-10NULLtrue
23Miami2026-10-21NULLtrue

Now customer 21 has two rows: Dallas is closed with a valid_to of 2026-10-21, and Austin is current from 2026-10-21. Denver for customer 22 is untouched, and Miami for customer 23 is new.

In Snowflake, wrap both steps in one transaction, BEGIN TRANSACTION and COMMIT, and keep any DDL out of it, because DDL commits the open transaction.

Real-project tip

Compare a HASH of the tracked columns, and run it from a stream and a task, or a dbt snapshot. A dynamic table can't MERGE, so it fits only when the source keeps every version.

Question 5: The nightly job ran twice and loaded duplicates. How do you make the load idempotent?

Idempotent means one run or two runs give the same result. A plain INSERT is not: run it twice, and every row is there twice. A MERGE on the business key is.

Let's prove it. First, the row count and total after the first MERGE from Question 1.

SQL - Row count and total after the first run
SELECT COUNT(*) AS row_count, SUM(amount) AS total_amount
FROM orders;
Output (PostgreSQL 16)1 row
row_counttotal_amount
6680.00

6 rows, with a total of 680.00. Now let's rerun the exact same MERGE.

SQL - Rerun the exact same MERGE: nothing doubles
MERGE INTO orders AS t
USING stg_orders AS s
  ON t.order_id = s.order_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN
  UPDATE SET status = s.status, amount = s.amount,
             updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (order_id, status, amount, updated_at)
  VALUES (s.order_id, s.status, s.amount,
          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 COUNT(*) AS row_count, SUM(amount) AS total_amount
FROM orders;
Output after the change1 row
row_counttotal_amount
6680.00

Still 6 rows, still 680.00. Nothing doubled.

Snowflake helps at the file and stream level too:

  • COPY INTO skips loaded files, using load metadata it keeps for 64 days.
  • No FORCE = TRUE in production, because it reloads those files and duplicates the data.
  • Snowpipe keeps its own load history for 14 days, and recreating the pipe drops it.
  • A stream's offset moves only when the transaction that reads it commits, so a run that rolls back leaves the changes for the retry.
My rule

Every step must be safe to run twice. Dedupe the source, MERGE on the key, and move any watermark only after success.

Question 6: Source rows are deleted and the target still shows them. How do you handle deletes in an incremental load?

An incremental load only sees rows that still exist. A hard delete leaves nothing to load, so the row lives on in the target. There are three ways to catch it:

  • A change feed with the operation: MERGE with a delete branch.
  • A soft delete flag in the source: the load sees it.
  • A key compare: copy all source keys, and find what is gone.

With a change feed, the MERGE gets a delete branch: when matched and the operation is D, delete. From a Snowflake stream, the operation is the METADATA$ACTION column, and an update arrives as a DELETE plus INSERT pair, marked by METADATA$ISUPDATE.

merge_cdc.sql
MERGE INTO orders AS t
USING orders_changes AS s
  ON t.order_id = s.order_id
WHEN MATCHED AND s.op = 'D' THEN DELETE
WHEN MATCHED THEN
  UPDATE SET status = s.status, amount = s.amount
WHEN NOT MATCHED AND s.op <> 'D' THEN
  INSERT (order_id, status, amount)
  VALUES (s.order_id, s.status, s.amount);

When the source can't help, we use the key compare. Any target order with no key in the source was deleted. Let's find them.

SQL - Find hard deletes: target keys that no longer exist in the source
SELECT t.order_id, t.status, t.amount
FROM orders AS t
WHERE NOT EXISTS (
  SELECT 1 FROM src_order_keys AS k
  WHERE k.order_id = t.order_id)
ORDER BY t.order_id;
Output (PostgreSQL 16)1 row
order_idstatusamount
1003Shipped80.00

One order, 1003, Shipped for 80.00. It is in the target, but no longer in the source. Now let's delete it.

SQL - Delete them from the target
DELETE FROM orders
WHERE NOT EXISTS (
  SELECT 1 FROM src_order_keys AS k
  WHERE k.order_id = orders.order_id);

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
FROM orders
ORDER BY order_id;
Output after the change5 rows
order_idstatusamount
1001Shipped100.00
1002Shipped250.00
1004Open60.00
1005Open40.00
1006Open150.00

Order 1003 is gone, and five orders are left: 1001, 1002, 1004, 1005 and 1006. If reports need history, set a deleted flag instead of deleting.

Plan the cost

The key compare is heavier than the incremental load, so run it nightly or weekly, with keys only.

Scenario 7: After a failed run and a retry, the fact table totals are wrong. How do you find, fix and prevent it?

First, what happened. Load 802 inserted the October 21 sales, and then a later step failed. The run was retried, and the retry, load 803, inserted the same rows again.

Let's find it with a totals check: rows and amount per day in the fact table, next to the totals the source reported.

SQL - Scenario: totals check, fact table vs source control totals
SELECT f.sale_date,
       COUNT(*) AS fact_rows,
       SUM(f.amount) AS fact_amount,
       MAX(c.src_rows) AS src_rows,
       MAX(c.src_amount) AS src_amount
FROM fact_sales AS f
JOIN source_totals AS c
  ON c.sale_date = f.sale_date
GROUP BY f.sale_date
ORDER BY f.sale_date;
Output (PostgreSQL 16)2 rows
sale_datefact_rowsfact_amountsrc_rowssrc_amount
2026-10-203240.003240.00
2026-10-216700.003350.00

October 20 matches: 3 rows and 240.00 on both sides. October 21 has 6 rows and 700.00, but the source sent 3 rows and 350.00. Which load added the extra rows? Group that day by load ID.

SQL - Which load added the extra rows?
SELECT load_id, COUNT(*) AS row_count,
       SUM(amount) AS amount
FROM fact_sales
WHERE sale_date = '2026-10-21'
GROUP BY load_id
ORDER BY load_id;
Output (PostgreSQL 16)2 rows
load_idrow_countamount
8023350.00
8033350.00

Loads 802 and 803 each added 3 rows and 350.00. Now the fix: delete the extra load, then rerun the same totals check.

SQL - Fix: delete the duplicate load, then rerun the totals check
DELETE FROM fact_sales
WHERE load_id = 803;

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

SQL - check the data after the change
SELECT f.sale_date,
       COUNT(*) AS fact_rows,
       SUM(f.amount) AS fact_amount,
       MAX(c.src_rows) AS src_rows,
       MAX(c.src_amount) AS src_amount
FROM fact_sales AS f
JOIN source_totals AS c
  ON c.sale_date = f.sale_date
GROUP BY f.sale_date
ORDER BY f.sale_date;
Output after the change2 rows
sale_datefact_rowsfact_amountsrc_rowssrc_amount
2026-10-203240.003240.00
2026-10-213350.003350.00

Both days match the source now: 3 rows and 240.00 on October 20, and 3 rows and 350.00 on October 21.

To prevent it, make the load idempotent:

  • Delete and insert the day's slice, in one transaction.
  • Or MERGE on the sale ID.
  • Run the totals check after every load, and fail the run on a mismatch.

Here is the slice reload. If the run is retried, the delete removes whatever the failed attempt inserted, so the day is loaded once.

reload_slice.sql
BEGIN TRANSACTION;
DELETE FROM fact_sales
 WHERE sale_date = '2026-10-21';
INSERT INTO fact_sales
SELECT sale_id, sale_date, amount, 804
FROM stg_sales;
COMMIT;
Remember

Fact totals must match the source, or the run fails. That one check catches a double load before the business does.

Key points to remember

  • MERGE on the business key, with a newer-row condition
  • Duplicate source keys fail MERGE: ERROR_ON_NONDETERMINISTIC_MERGE
  • Keep the latest row: ROW_NUMBER, or QUALIFY in Snowflake
  • SCD Type 2: close changed rows, insert new versions, one transaction
  • Idempotent loads, deletes by key compare, totals check every run

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
-- ========================================================================
-- Snowflake Interview Q&A #8 - 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_q08;  SET search_path TO sql_q08;
-- ========================================================================

-- Step 0: create and load the sample data
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS stg_orders;
DROP TABLE IF EXISTS stg_orders_d2;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS stg_customer;
DROP TABLE IF EXISTS src_order_keys;
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS source_totals;

-- Snowflake: TIMESTAMP_NTZ instead of TIMESTAMP; everything else runs as is

-- Q1: the target orders table, and today's staging rows (one changed, two new)
CREATE TABLE orders (order_id INT, status VARCHAR(12), amount DECIMAL(10,2),
                     updated_at TIMESTAMP);
INSERT INTO orders VALUES
  (1001, 'Shipped', 100.00, '2026-10-20 09:00'),
  (1002, 'Open',    250.00, '2026-10-20 10:30'),
  (1003, 'Shipped',  80.00, '2026-10-20 11:15'),
  (1004, 'Open',     60.00, '2026-10-20 13:45');
CREATE TABLE stg_orders (order_id INT, status VARCHAR(12), amount DECIMAL(10,2),
                         updated_at TIMESTAMP);
INSERT INTO stg_orders VALUES
  (1002, 'Shipped', 250.00, '2026-10-21 08:00'),
  (1005, 'Open',     40.00, '2026-10-21 09:15'),
  (1006, 'Open',    150.00, '2026-10-21 11:40');

-- Q2 and Q3: the next day's staging, where the source sent two orders twice
CREATE TABLE stg_orders_d2 (order_id INT, status VARCHAR(12), amount DECIMAL(10,2),
                            updated_at TIMESTAMP);
INSERT INTO stg_orders_d2 VALUES
  (1004, 'Packed',    60.00, '2026-10-22 08:00'),
  (1004, 'Shipped',   60.00, '2026-10-22 14:30'),
  (1007, 'Open',      35.00, '2026-10-22 09:00'),
  (1007, 'Cancelled', 35.00, '2026-10-22 16:10'),
  (1008, 'Open',      90.00, '2026-10-22 10:20');

-- Q4: an SCD Type 2 customer dimension, and today's customer extract
CREATE TABLE dim_customer (customer_id INT, city VARCHAR(20), valid_from DATE,
                           valid_to DATE, is_current BOOLEAN);
INSERT INTO dim_customer VALUES
  (21, 'Dallas', '2024-03-01', NULL, TRUE),
  (22, 'Denver', '2025-01-10', NULL, TRUE);
CREATE TABLE stg_customer (customer_id INT, city VARCHAR(20), changed_on DATE);
INSERT INTO stg_customer VALUES
  (21, 'Austin', '2026-10-21'),
  (22, 'Denver', '2026-10-21'),
  (23, 'Miami',  '2026-10-21');

-- Q6: every order key that still exists in the source tonight (1003 was hard deleted)
CREATE TABLE src_order_keys (order_id INT);
INSERT INTO src_order_keys VALUES (1001), (1002), (1004), (1005), (1006);

-- Q7: the fact table after load 802 failed at a later step and load 803 (the retry) inserted again
CREATE TABLE fact_sales (sale_id INT, sale_date DATE, amount DECIMAL(10,2), load_id INT);
INSERT INTO fact_sales VALUES
  (5001, '2026-10-20', 120.00, 801), (5002, '2026-10-20',  80.00, 801), (5003, '2026-10-20', 40.00, 801),
  (5004, '2026-10-21', 200.00, 802), (5005, '2026-10-21', 100.00, 802), (5006, '2026-10-21', 50.00, 802),
  (5004, '2026-10-21', 200.00, 803), (5005, '2026-10-21', 100.00, 803), (5006, '2026-10-21', 50.00, 803);
-- the control totals the source system reported for each day
CREATE TABLE source_totals (sale_date DATE, src_rows INT, src_amount DECIMAL(10,2));
INSERT INTO source_totals VALUES ('2026-10-20', 3, 240.00), ('2026-10-21', 3, 350.00);


-- Step 1: Upsert the staging rows into the target with MERGE
MERGE INTO orders AS t
USING stg_orders AS s
  ON t.order_id = s.order_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN
  UPDATE SET status = s.status, amount = s.amount,
             updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (order_id, status, amount, updated_at)
  VALUES (s.order_id, s.status, s.amount,
          s.updated_at);

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

-- Step 2: Row count and total after the first run
SELECT COUNT(*) AS row_count, SUM(amount) AS total_amount
FROM orders;

-- Step 3: Rerun the exact same MERGE: nothing doubles
MERGE INTO orders AS t
USING stg_orders AS s
  ON t.order_id = s.order_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN
  UPDATE SET status = s.status, amount = s.amount,
             updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (order_id, status, amount, updated_at)
  VALUES (s.order_id, s.status, s.amount,
          s.updated_at);

-- check the result
SELECT COUNT(*) AS row_count, SUM(amount) AS total_amount
FROM orders;

-- Step 4: 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 orders AS t
-- USING stg_orders_d2 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, amount, updated_at)
--   VALUES (s.order_id, s.status, s.amount,
--           s.updated_at);

-- Step 5: Find the duplicate keys in staging
SELECT order_id, COUNT(*) AS copies
FROM stg_orders_d2
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY order_id;

-- Step 6: Keep the latest row per order (ROW_NUMBER in a subquery)
SELECT order_id, status, updated_at
FROM (
  SELECT b.*,
         ROW_NUMBER() OVER (PARTITION BY order_id
                            ORDER BY updated_at DESC) AS rn
  FROM stg_orders_d2 AS b
) AS x
WHERE rn = 1
ORDER BY order_id;

-- Step 7: SCD Type 2, step 1: close the current rows that changed
UPDATE dim_customer AS d
SET is_current = FALSE,
    valid_to = s.changed_on
FROM stg_customer AS s
WHERE d.customer_id = s.customer_id
  AND d.is_current = TRUE
  AND d.city <> s.city;

-- Step 8: SCD Type 2, step 2: insert a new version for every key with no current row
INSERT INTO dim_customer
  (customer_id, city, valid_from, valid_to, is_current)
SELECT s.customer_id, s.city, s.changed_on,
       CAST(NULL AS DATE), TRUE
FROM stg_customer AS s
LEFT JOIN dim_customer AS d
  ON d.customer_id = s.customer_id
 AND d.is_current = TRUE
WHERE d.customer_id IS NULL;

-- check the result
SELECT customer_id, city, valid_from, valid_to,
       is_current
FROM dim_customer
ORDER BY customer_id, valid_from;

-- Step 9: Find hard deletes: target keys that no longer exist in the source
SELECT t.order_id, t.status, t.amount
FROM orders AS t
WHERE NOT EXISTS (
  SELECT 1 FROM src_order_keys AS k
  WHERE k.order_id = t.order_id)
ORDER BY t.order_id;

-- Step 10: Delete them from the target
DELETE FROM orders
WHERE NOT EXISTS (
  SELECT 1 FROM src_order_keys AS k
  WHERE k.order_id = orders.order_id);

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

-- Step 11: Scenario: totals check, fact table vs source control totals
SELECT f.sale_date,
       COUNT(*) AS fact_rows,
       SUM(f.amount) AS fact_amount,
       MAX(c.src_rows) AS src_rows,
       MAX(c.src_amount) AS src_amount
FROM fact_sales AS f
JOIN source_totals AS c
  ON c.sale_date = f.sale_date
GROUP BY f.sale_date
ORDER BY f.sale_date;

-- Step 12: Which load added the extra rows?
SELECT load_id, COUNT(*) AS row_count,
       SUM(amount) AS amount
FROM fact_sales
WHERE sale_date = '2026-10-21'
GROUP BY load_id
ORDER BY load_id;

-- Step 13: Fix: delete the duplicate load, then rerun the totals check
DELETE FROM fact_sales
WHERE load_id = 803;

-- check the result
SELECT f.sale_date,
       COUNT(*) AS fact_rows,
       SUM(f.amount) AS fact_amount,
       MAX(c.src_rows) AS src_rows,
       MAX(c.src_amount) AS src_amount
FROM fact_sales AS f
JOIN source_totals AS c
  ON c.sale_date = f.sale_date
GROUP BY f.sale_date
ORDER BY f.sale_date;
Next in this series

Part 9: Security, Governance and Data Sharing

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 Snowflake syntax is different, the post says so. Part of the Real Time Snowflake Interview Questions and Answers series on TechBrothersIT. Features, limits and their status (GA or preview) are current as of October 2026; check the Snowflake documentation for anything you quote in an interview.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)