cswork / libraryOpen practice workspace

Coding questions · Amazon

Design idempotent daily loads with deduping

This question evaluates data engineering competencies including idempotent ETL design, deduplication and upsert semantics, handling late-arriving updates, partitioning and incremental watermarks, load auditing and data-quality checks using SQL and orchestration with Python.

You need to load the last 7 days of orders into a large fact table from a noisy staging feed. Assume today is 2025-09-01. Requirements: idempotent reruns, late-arriving updates, and duplicate detection. Invent a complete approach (DDL + SQL/Python) that: 1) dedupes staging by (order_id, updated_at) keeping the latest; 2) MERGEs into the fact table only when the incoming row is newer; 3) detects whether a row was already loaded; 4) supports partitioning and incremental watermarks; 5) captures load audit metrics and data-quality checks (row counts, nulls, referential integrity). Use the schema and tiny sample data below, then write the exact SQL for the dedupe CTE and the MERGE (or equivalent upsert), plus a brief Python snippet (pseudo-code OK) for the orchestration and idempotent checkpointing.

Schema:

  • staging_orders(order_id INT, customer_id INT, order_date DATE, updated_at TIMESTAMP, total_amount DECIMAL(10,2), source_file STRING, ingest_time TIMESTAMP)
  • fact_orders(order_id INT PRIMARY KEY, customer_id INT, order_date DATE, updated_at TIMESTAMP, total_amount DECIMAL(10,2), load_batch_id STRING, loaded_at TIMESTAMP)
  • dim_customer(customer_id INT PRIMARY KEY, signup_date DATE)
  • loads_audit(batch_id STRING PRIMARY KEY, run_start TIMESTAMP, run_end TIMESTAMP, watermark_from DATE, watermark_to DATE, staged_rows INT, deduped_rows INT, inserted_rows INT, updated_rows INT, dq_errors INT)

Sample rows (monospaced ASCII): staging_orders (arriving 2025-09-01 for last 7 days)

order_idcustomer_idorder_dateupdated_attotal_amountsource_fileingest_time
10012025-08-262025-08-26 10:00:0049.00orders_202508262025-09-01 00:05:00
10122025-08-282025-08-28 09:00:0020.00orders_202508282025-09-01 00:05:10
10122025-08-282025-08-30 12:30:0022.00latefix_202508302025-09-01 00:05:10
10232025-08-312025-08-31 21:00:0015.50orders_202508312025-09-01 00:05:20
10392025-09-012025-09-01 00:01:00105.00orders_202509012025-09-01 00:05:30

fact_orders (before load)

order_idcustomer_idorder_dateupdated_attotal_amountload_batch_idloaded_at
10122025-08-282025-08-28 09:00:0020.00batch_08282025-08-28 10:00:00

dim_customer

customer_idsignup_date
12025-05-01
22024-11-11
32025-08-01

Answer specifics: a) show the exact SQL to dedupe staging (window function or aggregate); b) show a MERGE that updates when s.updated_at > t.updated_at and inserts if not matched; c) propose a robust watermark (e.g., max(order_date) − 1 day) to catch late data, and where you store it; d) show how you’d compute and store a row hash to detect prior loads; e) list two data-quality assertions that should fail the batch (and how to rollback safely).

Deduplicate noisy staging orders for the last 7-day window

You are loading orders from a noisy staging feed into a fact table. For this exercise, treat the run date as 2025-06-01 and the 7-day load window as 2025-05-26 through 2025-06-01 (inclusive).

Write a query that deduplicates staging_orders to ONE row per order_id within the window, keeping the row with the greatest updated_at. If there are ties on updated_at, keep the row with the greatest ingest_time.

Return the deduped rows with columns: order_id, customer_id, order_date, updated_at, total_amount, source_file, ingest_time ordered by order_id. For the sample output, format updated_at and ingest_time as YYYY-MM-DD HH24:MI:SS.

Table staging_orders

columntypeconstraints
order_idINTNOT NULL
customer_idINT
order_dateDATENOT NULL
updated_atTIMESTAMPNOT NULL
total_amountDECIMAL(10,2)
source_fileVARCHAR(64)NOT NULL
ingest_timeTIMESTAMPNOT NULL

Sample data:

customer_idingest_timeorder_dateorder_idsource_filetotal_amountupdated_at
12025-06-01 00:05:002025-05-26200orders_20250526492025-05-26 10:00:00
22025-06-01 00:05:102025-05-28201orders_20250528202025-05-28 09:00:00
22025-06-01 00:05:102025-05-28201latefix_20250530222025-05-30 12:30:00
32025-06-01 00:05:202025-05-31202orders_2025053115.52025-05-31 21:00:00
32025-06-01 00:06:002025-05-31202orders_20250531_rerun15.52025-05-31 21:00:00
92025-06-01 00:05:302025-06-01203orders_202506011052025-06-01 00:01:00
12025-06-01 00:05:152025-05-29204orders_20250529NULL2025-05-29 08:00:00

Expected output

One row per order_id in the 2025-05-26..2025-06-01 window, choosing the latest updated_at and breaking ties by latest ingest_time.

customer_idingest_timeorder_dateorder_idsource_filetotal_amountupdated_at
12025-06-01 00:05:002025-05-26200orders_2025052649.002025-05-26 10:00:00
22025-06-01 00:05:102025-05-28201latefix_2025053022.002025-05-30 12:30:00
32025-06-01 00:06:002025-05-31202orders_20250531_rerun15.502025-05-31 21:00:00
92025-06-01 00:05:302025-06-01203orders_20250601105.002025-06-01 00:01:00
12025-06-01 00:05:152025-05-29204orders_20250529NULL2025-05-29 08:00:00

Hints

  1. Use ROW_NUMBER() partitioned by order_id and ordered by updated_at desc, ingest_time desc.
  2. Filter to the explicit date window before deduping.

Idempotent MERGE into fact table (update only when staging is newer)

Using the same 7-day window (2025-05-26..2025-06-01 inclusive), perform an idempotent upsert into fact_orders from staging_orders:

  • First dedupe staging to one row per order_id (latest updated_at, tie-breaker ingest_time).
  • Only load rows that pass basic data quality gates:
    • total_amount is NOT NULL
    • customer_id exists in dim_customer (referential integrity)
  • MERGE behavior:
    • When matched on order_id, UPDATE only if s.updated_at > t.updated_at.
    • When not matched, INSERT.
  • For all inserted/updated rows, set:
    • load_batch_id = 'batch_0601'
    • loaded_at = TIMESTAMP '2025-06-01 00:10:00'
    • row_hash computed as MD5(CONCAT(order_id,'|',customer_id,'|',order_date,'|',updated_at,'|',total_amount)).

Write the MERGE SQL (or equivalent upsert). After the MERGE, return the contents of fact_orders ordered by order_id (excluding row_hash from the output). For validation, return the final fact_orders state that the MERGE would produce, with timestamps formatted as YYYY-MM-DD HH24:MI:SS.

Table staging_orders

columntypeconstraints
order_idINTNOT NULL
customer_idINT
order_dateDATENOT NULL
updated_atTIMESTAMPNOT NULL
total_amountDECIMAL(10,2)
source_fileVARCHAR(64)NOT NULL
ingest_timeTIMESTAMPNOT NULL

Sample data:

customer_idingest_timeorder_dateorder_idsource_filetotal_amountupdated_at
12025-06-01 00:05:002025-05-26200orders_20250526492025-05-26 10:00:00
22025-06-01 00:05:102025-05-28201orders_20250528202025-05-28 09:00:00
22025-06-01 00:05:102025-05-28201latefix_20250530222025-05-30 12:30:00
32025-06-01 00:05:202025-05-31202orders_2025053115.52025-05-31 21:00:00
32025-06-01 00:06:002025-05-31202orders_20250531_rerun15.52025-05-31 21:00:00
92025-06-01 00:05:302025-06-01203orders_202506011052025-06-01 00:01:00
12025-06-01 00:05:152025-05-29204orders_20250529NULL2025-05-29 08:00:00

Table fact_orders

columntypeconstraints
order_idINTPRIMARY KEY
customer_idINTNOT NULL
order_dateDATENOT NULL
updated_atTIMESTAMPNOT NULL
total_amountDECIMAL(10,2)NOT NULL
row_hashVARCHAR(64)
load_batch_idVARCHAR(40)NOT NULL
loaded_atTIMESTAMPNOT NULL

Sample data:

customer_idload_batch_idloaded_atorder_dateorder_idrow_hashtotal_amountupdated_at
2batch_05282025-05-28 10:00:002025-05-28201NULL202025-05-28 09:00:00
3batch_05312025-05-31 22:00:002025-05-31202NULL15.52025-05-31 21:00:00

Table dim_customer

columntypeconstraints
customer_idINTPRIMARY KEY
signup_dateDATENOT NULL

Sample data:

customer_idsignup_date
12025-01-01
22024-11-11
32025-05-01

Expected output

After the MERGE, fact_orders contains an inserted order 200, an updated order 201 (newer updated_at), and unchanged order 202. Orders failing DQ (missing dim_customer or null total_amount) are not loaded.

customer_idload_batch_idloaded_atorder_dateorder_idtotal_amountupdated_at
1batch_06012025-06-01 00:10:002025-05-26200492025-05-26 10:00:00
2batch_06012025-06-01 00:10:002025-05-28201222025-05-30 12:30:00
3batch_05312025-05-31 22:00:002025-05-3120215.52025-05-31 21:00:00

Hints

  1. Dedupe first, then apply DQ filters (non-null and dim join) before MERGE.
  2. Only update when staging.updated_at is strictly greater than fact.updated_at.

Compute an incremental watermark with a 1-day lookback overlap

To support incremental loads with late-arriving updates, compute the next watermark range for batch_id = 'batch_0601'.

Rules:

  • The run date is 2025-06-01, so set watermark_to = DATE '2025-06-01'.
  • Let prev_watermark_to be the maximum watermark_to in loads_audit.
  • Set watermark_from = prev_watermark_to - 1 day (1-day lookback overlap). If there is no previous run, use watermark_from = DATE '2025-05-26'.

Return a single row with columns: batch_id, watermark_from, watermark_to.

Table loads_audit

columntypeconstraints
batch_idVARCHAR(40)PRIMARY KEY
run_startTIMESTAMPNOT NULL
run_endTIMESTAMP
watermark_fromDATE
watermark_toDATE
staged_rowsINT
deduped_rowsINT
inserted_rowsINT
updated_rowsINT
dq_errorsINT

Sample data:

batch_iddeduped_rowsdq_errorsinserted_rowsrun_endrun_startstaged_rowsupdated_rowswatermark_fromwatermark_to
batch_05311190011002025-05-31 00:15:002025-05-31 00:05:001200902025-05-242025-05-31

Expected output

One row defining the next load window: watermark_to is the run date, watermark_from is the previous watermark_to minus one day.

batch_idwatermark_fromwatermark_to
batch_06012025-05-302025-06-01

Hints

  1. Use MAX(watermark_to) from loads_audit as the previous checkpoint.
  2. Subtract a 1-day interval to create an overlap for late-arriving updates.

Classify deduped staging rows as INSERT/UPDATE/SKIP/REJECT (idempotency planning)

For the 2025-05-26..2025-06-01 window, produce a pre-merge classification per deduped order_id to support idempotent reruns.

Steps:

  1. Dedupe staging to one row per order_id (latest updated_at; tie-breaker ingest_time).
  2. Compute incoming_hash = MD5(CONCAT(order_id,'|',customer_id,'|',order_date,'|',updated_at,'|',total_amount)).
  3. LEFT JOIN to fact_orders on order_id and LEFT JOIN to dim_customer on customer_id.
  4. Assign an action for each deduped row:
    • 'REJECT_NULL_TOTAL_AMOUNT' if total_amount IS NULL
    • 'REJECT_DIM_CUSTOMER' if customer_id not found in dim_customer
    • 'INSERT' if not in fact_orders
    • 'UPDATE' if in fact_orders AND staging.updated_at > fact.updated_at
    • 'SKIP_ALREADY_LOADED' if in fact_orders AND staging.updated_at = fact.updated_at AND values are identical (use hash comparison; if fact.row_hash is NULL, compute it from fact columns)
    • 'SKIP_NOT_NEWER' otherwise

Return: order_id, action ordered by order_id.

Table staging_orders

columntypeconstraints
order_idINTNOT NULL
customer_idINT
order_dateDATENOT NULL
updated_atTIMESTAMPNOT NULL
total_amountDECIMAL(10,2)
source_fileVARCHAR(64)NOT NULL
ingest_timeTIMESTAMPNOT NULL

Sample data:

customer_idingest_timeorder_dateorder_idsource_filetotal_amountupdated_at
12025-06-01 00:05:002025-05-26200orders_20250526492025-05-26 10:00:00
22025-06-01 00:05:102025-05-28201orders_20250528202025-05-28 09:00:00
22025-06-01 00:05:102025-05-28201latefix_20250530222025-05-30 12:30:00
32025-06-01 00:05:202025-05-31202orders_2025053115.52025-05-31 21:00:00
32025-06-01 00:06:002025-05-31202orders_20250531_rerun15.52025-05-31 21:00:00
92025-06-01 00:05:302025-06-01203orders_202506011052025-06-01 00:01:00
12025-06-01 00:05:152025-05-29204orders_20250529NULL2025-05-29 08:00:00

Table fact_orders

columntypeconstraints
order_idINTPRIMARY KEY
customer_idINTNOT NULL
order_dateDATENOT NULL
updated_atTIMESTAMPNOT NULL
total_amountDECIMAL(10,2)NOT NULL
row_hashVARCHAR(64)
load_batch_idVARCHAR(40)NOT NULL
loaded_atTIMESTAMPNOT NULL

Sample data:

customer_idload_batch_idloaded_atorder_dateorder_idrow_hashtotal_amountupdated_at
2batch_05282025-05-28 10:00:002025-05-28201NULL202025-05-28 09:00:00
3batch_05312025-05-31 22:00:002025-05-31202NULL15.52025-05-31 21:00:00

Table dim_customer

columntypeconstraints
customer_idINTPRIMARY KEY
signup_dateDATENOT NULL

Sample data:

customer_idsignup_date
12025-01-01
22024-11-11
32025-05-01

Expected output

One row per deduped staging order_id indicating what would happen in an idempotent load.

actionorder_id
INSERT200
UPDATE201
SKIP_ALREADY_LOADED202
REJECT_DIM_CUSTOMER203
REJECT_NULL_TOTAL_AMOUNT204

Hints

  1. Compute incoming hash and a comparable fact hash (use COALESCE for missing fact row_hash).
  2. Order the CASE checks so DQ rejects happen before deciding insert/update/skip.

Data-quality checks: nulls and referential integrity failures in the batch window

For the batch window 2025-05-26..2025-06-01 (inclusive), write SQL that outputs all data-quality failures that should cause the batch to fail.

Rules:

  • First dedupe staging to one row per order_id (latest updated_at; tie-breaker ingest_time).
  • Then emit one row per failing deduped record with:
    • order_id
    • dq_issue

Include at least these two assertions:

  1. total_amount is NULL -> dq_issue = 'NULL_TOTAL_AMOUNT'
  2. customer_id not found in dim_customer -> dq_issue = 'MISSING_DIM_CUSTOMER'

Return all failures ordered by order_id, dq_issue.

Table staging_orders

columntypeconstraints
order_idINTNOT NULL
customer_idINT
order_dateDATENOT NULL
updated_atTIMESTAMPNOT NULL
total_amountDECIMAL(10,2)
source_fileVARCHAR(64)NOT NULL
ingest_timeTIMESTAMPNOT NULL

Sample data:

customer_idingest_timeorder_dateorder_idsource_filetotal_amountupdated_at
12025-06-01 00:05:002025-05-26200orders_20250526492025-05-26 10:00:00
22025-06-01 00:05:102025-05-28201orders_20250528202025-05-28 09:00:00
22025-06-01 00:05:102025-05-28201latefix_20250530222025-05-30 12:30:00
32025-06-01 00:05:202025-05-31202orders_2025053115.52025-05-31 21:00:00
32025-06-01 00:06:002025-05-31202orders_20250531_rerun15.52025-05-31 21:00:00
92025-06-01 00:05:302025-06-01203orders_202506011052025-06-01 00:01:00
12025-06-01 00:05:152025-05-29204orders_20250529NULL2025-05-29 08:00:00

Table dim_customer

columntypeconstraints
customer_idINTPRIMARY KEY
signup_dateDATENOT NULL

Sample data:

customer_idsignup_date
12025-01-01
22024-11-11
32025-05-01

Expected output

Two failing orders in the window: one with a missing customer dimension, and one with a null total_amount (after dedupe).

dq_issueorder_id
MISSING_DIM_CUSTOMER203
NULL_TOTAL_AMOUNT204

Hints

  1. Run DQ on the deduped dataset, not on raw staging.
  2. Use a LEFT JOIN to detect missing dimension keys.