In this post we are going to cover idempotent loads and data quality checks, two ideas that interviewers like to put together because both are about making a pipeline safe to run.
An idempotent load is a load that gives the same result no matter how many times it runs. If the job is retried, the warehouse ends up exactly the same as after one run, with no duplicates. Data quality checks are tests we run on a batch before we load it, so bad source data is caught instead of quietly ending up in reports.
We will first break a load by simply retrying it, then fix it with a delete-then-insert pattern, and finally write the quality checks in SQL.
Question 40 of the Real DWH Question and Answers series on TechBrothersIT.
The interview answer
An idempotent load gives the same result no matter how many times it runs, so a retry never creates duplicates. Common patterns: delete-then-insert by batch or partition, MERGE on keys, or truncate and reload. Data quality checks run before loading: row counts, uniqueness, not null, valid ranges, referential integrity and freshness, and a failing check stops or flags the load.
Why loads run twice
Idempotency matters because, in real life, loads run more than once:
- Retry - the job failed halfway and the scheduler runs it again.
- Re-run - someone runs yesterday again after fixing a bug.
- Backfill - we reload last month.
If the load simply appends rows, every one of those re-runs adds the same data again. And duplicates do not throw errors. They just make every total wrong.
Let's create some sample data
We need a small product dimension, a staging table with two daily batches, and an empty fact table. Like many warehouse fact tables, fact_sales has no enforced primary key.
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS stg_sales;
DROP TABLE IF EXISTS dim_product;
CREATE TABLE dim_product (product_key INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO dim_product VALUES (1, 'Pen'), (2, 'Notebook');
-- staging holds two daily batches; the June 2 batch has quality problems
CREATE TABLE stg_sales (sale_id INT, sale_date DATE, product_key INT, amount DECIMAL(10,2));
INSERT INTO stg_sales VALUES
(1, '2026-06-01', 1, 10.00),
(2, '2026-06-01', 2, 25.00),
(3, '2026-06-02', 1, 15.00),
(3, '2026-06-02', 1, 15.00), -- duplicate
(4, '2026-06-02', 9, 20.00), -- product 9 does not exist
(5, '2026-06-02', 2, NULL), -- missing amount
(6, '2026-06-02', 2, -8.00); -- negative amount
-- a typical warehouse fact table: no enforced primary key
CREATE TABLE fact_sales (sale_id INT, sale_date DATE, product_key INT, amount DECIMAL(10,2));The product dimension has only two products.
SELECT * FROM dim_product ORDER BY 1;| product_key | name |
|---|---|
| 1 | Pen |
| 2 | Notebook |
Here is staging. The June 1 batch is clean, but the June 2 batch has problems on purpose: sale 3 appears twice, sale 4 points to product 9, which does not exist, sale 5 has no amount, and sale 6 has a negative amount.
SELECT * FROM stg_sales ORDER BY 1;| sale_id | sale_date | product_key | amount |
|---|---|---|---|
| 1 | 2026-06-01 | 1 | 10.00 |
| 2 | 2026-06-01 | 2 | 25.00 |
| 3 | 2026-06-02 | 1 | 15.00 |
| 3 | 2026-06-02 | 1 | 15.00 |
| 4 | 2026-06-02 | 9 | 20.00 |
| 5 | 2026-06-02 | 2 | NULL |
| 6 | 2026-06-02 | 2 | -8.00 |
Step 1: Naive load of the June 1 batch
Let's start with the simplest possible load: append the June 1 rows from staging into the fact table.
INSERT INTO fact_sales
SELECT * FROM stg_sales
WHERE sale_date = DATE '2026-06-01';Now let's check the result.
SELECT COUNT(*) AS fact_rows, SUM(amount) AS total FROM fact_sales;| fact_rows | total |
|---|---|
| 2 | 35.00 |
Two rows and a total of 35.00. That is correct, so far.
Step 2: The job is retried
Now let's pretend the scheduler retried the job, and the same append runs again.
INSERT INTO fact_sales
SELECT * FROM stg_sales
WHERE sale_date = DATE '2026-06-01';Now let's check the result.
SELECT COUNT(*) AS fact_rows, SUM(amount) AS total FROM fact_sales;| fact_rows | total |
|---|---|
| 4 | 70.00 |
The fact table now has 4 rows and the total is 70.00. Every row is in the fact twice, and revenue doubled. Nothing failed, there was no error, and every report built on this table is now wrong.
Step 3: Delete the batch, then insert it
Now let's make the load idempotent. Before inserting the batch, we delete anything already loaded for that batch, which here means every row with the sale date June 1. Then we insert it.
DELETE FROM fact_sales
WHERE sale_date = DATE '2026-06-01';
INSERT INTO fact_sales
SELECT * FROM stg_sales
WHERE sale_date = DATE '2026-06-01';Now let's check the result.
SELECT COUNT(*) AS fact_rows, SUM(amount) AS total FROM fact_sales;| fact_rows | total |
|---|---|
| 2 | 35.00 |
We are back to 2 rows and 35.00. The delete removed the doubled rows, and the insert loaded the batch once.
Run the delete and the insert in one transaction, so a failure between the two never leaves the batch deleted but not reloaded.
Step 4: Run it again
The real test of idempotency is running the same load again. Let's run exactly the same delete and insert one more time.
DELETE FROM fact_sales
WHERE sale_date = DATE '2026-06-01';
INSERT INTO fact_sales
SELECT * FROM stg_sales
WHERE sale_date = DATE '2026-06-01';Now let's check the result.
SELECT COUNT(*) AS fact_rows, SUM(amount) AS total FROM fact_sales;| fact_rows | total |
|---|---|
| 2 | 35.00 |
Still 2 rows and 35.00. Running it once or ten times gives the same answer. A MERGE on the business key gives the same guarantee, and for partitioned tables we overwrite the whole partition.
Which checks to run
Idempotency protects us from our own pipeline. Data quality checks protect us from bad source data. These are the standard checks:
- Row counts - source and target have the same number of rows.
- Unique keys - no key appears twice.
- Not null - required columns are filled.
- Valid ranges - for example, amount is 0 or more.
- Referential integrity - every key exists in its dimension.
- Freshness - today's batch actually arrived.
Decide the action for each check. Critical failures stop the load. Minor ones can load but raise an alert, or send the bad rows to a quarantine table.
Step 5: Data quality checks in SQL
Now let's run the checks on the June 2 batch, before it is loaded. Each check counts the rows that fail it, and the results are stacked with UNION ALL so we get one small report.
SELECT 'duplicate sale_id' AS check_name, COUNT(*) AS failed
FROM (SELECT sale_id FROM stg_sales
WHERE sale_date = DATE '2026-06-02'
GROUP BY sale_id HAVING COUNT(*) > 1) d
UNION ALL
SELECT 'amount is NULL', COUNT(*) FROM stg_sales
WHERE sale_date = DATE '2026-06-02' AND amount IS NULL
UNION ALL
SELECT 'amount < 0', COUNT(*) FROM stg_sales
WHERE sale_date = DATE '2026-06-02' AND amount < 0
UNION ALL
SELECT 'unknown product', COUNT(*) FROM stg_sales s
WHERE sale_date = DATE '2026-06-02'
AND NOT EXISTS (SELECT 1 FROM dim_product p
WHERE p.product_key = s.product_key);| check_name | failed |
|---|---|
| duplicate sale_id | 1 |
| amount is NULL | 1 |
| amount < 0 | 1 |
| unknown product | 1 |
Every check fails exactly once: one duplicated sale_id (sale 3), one NULL amount (sale 5), one negative amount (sale 6) and one unknown product (sale 4, product 9). In a real pipeline, any count above 0 on a critical check stops the load and alerts the team. Tools like dbt tests or Great Expectations run the same kind of checks, but underneath they are queries like these.
Follow-up questions interviewers ask
Why not just add a primary key to the fact?
Many warehouses do not enforce constraints for load speed; and a key error on retry still fails the job instead of making it safe to re-run.
Why run delete + insert in one transaction?
So a failure between the two never leaves the batch deleted but not reloaded.
Where do failed rows go?
To a quarantine table with the failure reason, so they can be fixed and reprocessed.
The full script
Here is everything from this post in one script. Copy it and run it top to bottom.
-- ========================================================================
-- Data Warehouse Interview Q&A #40 - Idempotent loads and data quality checks
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA dwh_q40; SET search_path TO dwh_q40;
-- SQL Server: write DATE '2026-06-01' as CAST('2026-06-01' AS DATE).
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS stg_sales;
DROP TABLE IF EXISTS dim_product;
CREATE TABLE dim_product (product_key INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO dim_product VALUES (1, 'Pen'), (2, 'Notebook');
-- staging holds two daily batches; the June 2 batch has quality problems
CREATE TABLE stg_sales (sale_id INT, sale_date DATE, product_key INT, amount DECIMAL(10,2));
INSERT INTO stg_sales VALUES
(1, '2026-06-01', 1, 10.00),
(2, '2026-06-01', 2, 25.00),
(3, '2026-06-02', 1, 15.00),
(3, '2026-06-02', 1, 15.00), -- duplicate
(4, '2026-06-02', 9, 20.00), -- product 9 does not exist
(5, '2026-06-02', 2, NULL), -- missing amount
(6, '2026-06-02', 2, -8.00); -- negative amount
-- a typical warehouse fact table: no enforced primary key
CREATE TABLE fact_sales (sale_id INT, sale_date DATE, product_key INT, amount DECIMAL(10,2));
-- Step 1: Naive load: append the June 1 batch
INSERT INTO fact_sales
SELECT * FROM stg_sales
WHERE sale_date = DATE '2026-06-01';
-- check the result
SELECT COUNT(*) AS fact_rows, SUM(amount) AS total FROM fact_sales;
-- Step 2: The job is retried: the same append runs again
INSERT INTO fact_sales
SELECT * FROM stg_sales
WHERE sale_date = DATE '2026-06-01';
-- check the result
SELECT COUNT(*) AS fact_rows, SUM(amount) AS total FROM fact_sales;
-- Step 3: Idempotent load: delete the batch, then insert it
DELETE FROM fact_sales
WHERE sale_date = DATE '2026-06-01';
INSERT INTO fact_sales
SELECT * FROM stg_sales
WHERE sale_date = DATE '2026-06-01';
-- check the result
SELECT COUNT(*) AS fact_rows, SUM(amount) AS total FROM fact_sales;
-- Step 4: Run the idempotent load again: same result
DELETE FROM fact_sales
WHERE sale_date = DATE '2026-06-01';
INSERT INTO fact_sales
SELECT * FROM stg_sales
WHERE sale_date = DATE '2026-06-01';
-- check the result
SELECT COUNT(*) AS fact_rows, SUM(amount) AS total FROM fact_sales;
-- Step 5: Data quality checks on the June 2 batch, before it is loaded
SELECT 'duplicate sale_id' AS check_name, COUNT(*) AS failed
FROM (SELECT sale_id FROM stg_sales
WHERE sale_date = DATE '2026-06-02'
GROUP BY sale_id HAVING COUNT(*) > 1) d
UNION ALL
SELECT 'amount is NULL', COUNT(*) FROM stg_sales
WHERE sale_date = DATE '2026-06-02' AND amount IS NULL
UNION ALL
SELECT 'amount < 0', COUNT(*) FROM stg_sales
WHERE sale_date = DATE '2026-06-02' AND amount < 0
UNION ALL
SELECT 'unknown product', COUNT(*) FROM stg_sales s
WHERE sale_date = DATE '2026-06-02'
AND NOT EXISTS (SELECT 1 FROM dim_product p
WHERE p.product_key = s.product_key);
Key points
- Idempotent: same result on every run
- Plain append + retry = duplicates
- Delete-then-insert by batch, or MERGE
- Check counts, unique, not null, ranges, RI, freshness
- Critical failures stop the load
Question 41 — Kimball vs Inmon Kimball vs Inmon: what is the difference?
Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.