Data Engineering SQL Usage

Three earlier chapters gave you the ideas. They are “Loading & Write Patterns”, “Idempotency, Retries & Exactly-Once” and “Incremental Processing & Backfills”.

This chapter gives you the SQL that does the work. It is PostgreSQL unless a note says otherwise.

A load changes a table that other people already use. So the SQL has one job: leave the table correct, even if the load runs twice.

Most examples use two tables. The table dw_orders is the warehouse copy of the shop’s orders. The table orders_stage is a new batch of changes.

Here is the warehouse table now, and the batch that reached it on 2026-01-27:

SELECT *
FROM orders_stage;

The ten patterns come in this order:

1. Pick a write strategy: append, full refresh, delete and insert, or upsert.
2. Clean the batch in a staging table, and keep one row for each key.
3. Upsert with INSERT … ON CONFLICT.
4. Merge with MERGE, which needs PostgreSQL 15.
5. Handle deleted rows.
6. Write set-based statements and wrap them in a transaction.
7. Read only new rows with a watermark and a lookback.
8. Build a date spine with generate_series.
9. Backfill a date range, then load a slowly changing dimension (SCD2).
10. Reconcile the source and the target.

Every load must choose how it writes into the target table. The best test is one question: what happens if the same load runs twice?

Scheduling tools (orchestrators, such as Airflow) retry failed tasks, so a second run will happen. This table compares the four common strategies:

Append is the risky one. Suppose dw_orders has no primary key, and a retry appends the same batch twice:

-- Run 1.
INSERT INTO dw_orders
SELECT * FROM orders_stage WHERE order_id IS NOT NULL;

-- A retry runs the same statement again.
INSERT INTO dw_orders
SELECT * FROM orders_stage WHERE order_id IS NOT NULL;

SELECT order_id, COUNT(*) AS copies
FROM dw_orders
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY order_id;

The next sections show delete and insert, and upsert. Full refresh has one trap: if the source stops sending a row, the rebuild deletes it.

A staging table holds the raw batch before the load touches the target. You clean the batch there. The target statement then reads clean rows only.

Two checks come first. Is any key missing, or used twice? This query finds both:

SELECT order_id, COUNT(*) AS rows_in_batch
FROM orders_stage
GROUP BY order_id
HAVING order_id IS NULL OR COUNT(*) > 1
ORDER BY order_id NULLS LAST;

Next, keep one row for each key. ROW_NUMBER numbers the rows inside each key, newest first. You keep row number 1:

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY order_id
           ORDER BY updated_at DESC
         ) AS row_num
  FROM orders_stage
  WHERE order_id IS NOT NULL
)
SELECT order_id, customer_id, status, amount, order_date, updated_at
FROM ranked
WHERE row_num = 1
ORDER BY order_id;

An upsert is one statement that inserts a new row and updates an existing one. It needs a unique key. In PostgreSQL, that is a primary key or a unique index.

This upsert loads clean_stage into dw_orders. The WHERE line at the end of the INSERT matters most: it stops an older row from overwriting a newer one.

-- dw_orders has PRIMARY KEY (order_id).
INSERT INTO dw_orders
  (order_id, customer_id, status, amount, order_date, updated_at)
SELECT order_id, customer_id, status, amount, order_date, updated_at
FROM clean_stage
ON CONFLICT (order_id) DO UPDATE
SET customer_id = EXCLUDED.customer_id,
    status      = EXCLUDED.status,
    amount      = EXCLUDED.amount,
    order_date  = EXCLUDED.order_date,
    updated_at  = EXCLUDED.updated_at
WHERE dw_orders.updated_at < EXCLUDED.updated_at;

SELECT * FROM dw_orders ORDER BY order_id;

Run the same statement again. Nothing changes, because every row now has the same updated_at. The statement is idempotent: a second run gives the same table.

MERGE joins a source to a target. Then it runs a rule for each source row. PostgreSQL 15 added it.

You write WHEN rules. A row is matched when the target has its key, and not matched when it does not. The first rule that fits a row runs.

First, predict what MERGE will do with a plain SELECT. It changes nothing, so it is safe to run:

SELECT s.order_id,
       CASE
         WHEN t.order_id IS NULL THEN 'WHEN NOT MATCHED: insert'
         WHEN s.updated_at > t.updated_at THEN 'WHEN MATCHED: update'
         ELSE 'no rule fits: do nothing'
       END AS merge_action
FROM clean_stage AS s
LEFT JOIN dw_orders AS t ON t.order_id = s.order_id
ORDER BY s.order_id;

Now write the MERGE. It carries out the same three decisions:

MERGE INTO dw_orders AS t
USING clean_stage AS s
  ON t.order_id = s.order_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN
  UPDATE SET customer_id = s.customer_id,
             status      = s.status,
             amount      = s.amount,
             order_date  = s.order_date,
             updated_at  = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, status, amount, order_date, updated_at)
  VALUES (s.order_id, s.customer_id, s.status, s.amount, s.order_date, s.updated_at);

Both tools reach the same table. This table helps you choose:

A tombstone is a source row that says a thing was deleted. You choose what the target does with it.

Many teams keep the row and mark it deleted. This is a soft delete.

Here, the source says the account of customer 5 is closed. The load sets is_deleted to true in dw_customers, the warehouse copy of the customers:

UPDATE dw_customers AS c
SET is_deleted = s.is_deleted
FROM customers_stage AS s
WHERE s.customer_id = c.customer_id
  AND c.is_deleted IS DISTINCT FROM s.is_deleted;

SELECT * FROM dw_customers ORDER BY customer_id;

A hard delete removes the row. It breaks joins from old orders. Use it only when a rule requires it, such as a privacy request.

The chapter Change Data Capture (CDC) shows how the source can send a delete event for each deleted row.

A set-based statement works on many rows in one command. A row-by-row loop sends one command for each row. Both can give the same table.

The loop usually costs more: each row needs its own trip to the database and back. The database can plan one big statement as a whole. Compare the two on the batch:

-- Row by row (avoid). The app runs this once for each row of clean_stage:
-- UPDATE dw_orders SET status = $1, amount = $2 WHERE order_id = $3;

-- Set-based. One statement for the whole batch:
UPDATE dw_orders AS t
SET status = s.status,
    amount = s.amount
FROM clean_stage AS s
WHERE t.order_id = s.order_id
  AND (t.status, t.amount) IS DISTINCT FROM (s.status, s.amount);

A load often needs several statements. A transaction groups them, so that all succeed or none do. You write BEGIN, the statements, then COMMIT.

This table shows why it matters when the second statement fails:

A watermark is the time up to which the pipeline has loaded. The next run reads only rows newer than it. That costs less than reading the whole source.

The table load_log stores the watermark, and only a successful run moves it. The shop’s source table is src_orders. The first query reads the rows after the last good watermark:

SELECT order_id, updated_at
FROM src_orders
WHERE updated_at >= (
  SELECT MAX(watermark_at)
  FROM load_log
  WHERE status = 'success'
)
ORDER BY updated_at;

Rows can arrive late like this. A lookback fixes it: the run starts reading a set time before the watermark. Here the lookback is one day:

SELECT order_id, updated_at
FROM src_orders
WHERE updated_at >= (
  SELECT MAX(watermark_at)
  FROM load_log
  WHERE status = 'success'
) - INTERVAL '1 day'
ORDER BY updated_at;

After the load commits, add a success row to load_log. Store the time this run started, called run_start in Incremental Processing & Backfills.

A date spine is one row for each date in a range, even dates with no data. generate_series builds it. Without a spine, a day with no orders is missing from the result.

This query builds the spine for three days:

SELECT d::date AS day
FROM generate_series(
       TIMESTAMP '2026-01-19',
       TIMESTAMP '2026-01-21',
       INTERVAL '1 day'
     ) AS g(d);

Now LEFT JOIN the orders onto the spine. Count the paid orders for each day:

SELECT d::date AS sales_date,
       COUNT(o.order_id) AS paid_orders,
       COALESCE(SUM(o.amount), 0) AS paid_amount
FROM generate_series(
       TIMESTAMP '2026-01-19',
       TIMESTAMP '2026-01-21',
       INTERVAL '1 day'
     ) AS g(d)
LEFT JOIN dw_orders AS o
  ON o.order_date = d::date
 AND o.status = 'paid'
GROUP BY d::date
ORDER BY sales_date;

A backfill rebuilds old dates, because the data changed after the first run. In the story, the payment of order 105 reached the warehouse on 2026-01-27. But its order_date is 2026-01-20, so the table daily_sales (paid orders for each day) is wrong for that date.

The fix is delete and insert for the date range, inside one transaction. Both statements use the same dates:

BEGIN;

DELETE FROM daily_sales
WHERE sales_date BETWEEN DATE '2026-01-19' AND DATE '2026-01-21';

INSERT INTO daily_sales (sales_date, paid_orders, paid_amount)
SELECT d::date, COUNT(o.order_id), COALESCE(SUM(o.amount), 0)
FROM generate_series(
       TIMESTAMP '2026-01-19',
       TIMESTAMP '2026-01-21',
       INTERVAL '1 day'
     ) AS g(d)
LEFT JOIN dw_orders AS o
  ON o.order_date = d::date
 AND o.status = 'paid'
GROUP BY d::date;

COMMIT;

SELECT * FROM daily_sales ORDER BY sales_date;

Three rules make a backfill safe:

1. Use the same date column and the same bounds in the DELETE and the INSERT.
2. Replace one date, or one small range, in each transaction. A failure then costs little, and you can resume.
3. Do not run it while the normal load writes the same dates.

A slowly changing dimension (SCD) is a table of things, such as customers, whose facts change. The type says what the load does when a value changes.

The chapter Slowly Changing Dimensions (SCD 0–7) explains each type. This table gives only the load action, when the country of Dev changes from NULL to IN:

Choose per column, not per table. A customer table can use Type 1 for email and Type 2 for country. The next section loads Type 2, the type with the most steps.

An SCD2 load has two steps: close the current row, then insert a new current row. The table is dim_customer from Slowly Changing Dimensions (SCD 0–7).

Its keys: the column customer_key is the surrogate key, a number the warehouse makes. The column customer_id is the business key, the id from the shop.

A version is true from valid_from, included, up to valid_to, not included. The date 9999-12-31 means still open.

On 2026-02-01, the source says Dev now has the country IN. It also sends Ben again, with no change:

BEGIN;

-- Step 1. Close the current row when the country changed.
UPDATE dim_customer AS d
SET valid_to = s.changed_on,
    is_current = false
FROM customer_changes AS s
WHERE d.customer_id = s.customer_id
  AND d.is_current
  AND d.country IS DISTINCT FROM s.country;

-- Step 2. Insert a new current row for each customer with none.
INSERT INTO dim_customer
  (customer_id, name, country, valid_from, valid_to, is_current)
SELECT s.customer_id, s.name, s.country,
       s.changed_on, DATE '9999-12-31', true
FROM customer_changes AS s
WHERE NOT EXISTS (
  SELECT 1
  FROM dim_customer AS d
  WHERE d.customer_id = s.customer_id
    AND d.is_current
);

COMMIT;

SELECT * FROM dim_customer
WHERE customer_id IN (2, 4)
ORDER BY customer_id, valid_from;

Run the block again. Step 1 closes nothing, because Dev’s current row already shows IN. Step 2 inserts nothing, because the NOT EXISTS test finds that row.

A brand-new customer has no current row, so step 2 inserts its first row.

A current table has one row for each customer. A history table, such as dim_customer, keeps every version.

Many teams keep both. The current table serves simple joins, and the history table answers questions about the past.

Two queries come with every SCD2 table. First, a health check: each customer must have exactly one current row. This query returns the customers that break the rule:

SELECT customer_id, COUNT(*) AS current_rows
FROM dim_customer
WHERE is_current
GROUP BY customer_id
HAVING COUNT(*) <> 1;

Second, the as-of query. It finds the version that was true on a date. This query asks for Dev on two dates:

SELECT a.as_of, d.customer_id, d.country
FROM (VALUES (DATE '2026-01-15'), (DATE '2026-02-05')) AS a(as_of)
JOIN dim_customer AS d
  ON d.customer_id = 4
 AND d.valid_from <= a.as_of
 AND a.as_of < d.valid_to
ORDER BY a.as_of;

A load is finished when you can prove it. Reconciliation compares the source and the target after the load. Start with counts and sums:

SELECT 'source' AS side,
       COUNT(*) AS row_count,
       SUM(amount) AS total_amount
FROM src_orders
UNION ALL
SELECT 'target', COUNT(*), SUM(amount)
FROM dw_orders;

This query lists the rows that differ. A FULL OUTER JOIN keeps rows that exist on one side only:

SELECT COALESCE(s.order_id, t.order_id) AS order_id,
       CASE
         WHEN t.order_id IS NULL THEN 'missing in target'
         WHEN s.order_id IS NULL THEN 'extra in target'
         ELSE 'values differ'
       END AS issue
FROM src_orders AS s
FULL OUTER JOIN dw_orders AS t
  ON t.order_id = s.order_id
WHERE t.order_id IS NULL
   OR s.order_id IS NULL
   OR s.status IS DISTINCT FROM t.status
   OR s.amount IS DISTINCT FROM t.amount
ORDER BY order_id;

Save the counts of every load: rows read, rows rejected, rows inserted, rows updated. Then you can check any run later.

These eight labs run in the SQL workspace. It is read-only, so each lab is a SELECT query. The query predicts or checks what a load would change.

Start with Dedupe staging before merge, then Detect upsert key violations. Finish with Load reconciliation summary.