cswork / libraryOpen practice workspace

Coding questions · Stripe

Design an idempotent SQL ETL for late data

This question evaluates practical skills in idempotent ETL design, deduplication by natural keys, handling late-arriving and out-of-order records, upsert/merge strategies, and SQL-based aggregation for daily metrics.

You own the daily_user_metrics fact table. Build an idempotent, rerunnable ETL that can be triggered for any date D and correctly handles duplicates, late-arriving records (up to 2 days late), and status changes. You must write whiteboard-level SQL for the core transformation and describe the upsert strategy.

Warehouse (UTC). Raw landing schemas and tiny samples:

Table: users_dim

u_idcreated_atcountryis_test
12025-08-28 10:00:00US0
22025-08-30 12:00:00CA0
32025-08-15 09:00:00US1

Table: events_raw

event_idu_idevent_typeevent_tsingested_atsource
e11view2025-09-01 02:03:002025-09-01 02:03:05web
e11view2025-09-01 02:03:002025-09-02 01:00:00replay (duplicate, late)
e21purchase2025-09-01 03:10:002025-09-01 03:10:04web
e32view2025-08-31 23:59:592025-09-01 00:00:01web (late arrival for 08-31)

Table: orders_raw

order_idu_idamountstatusorder_tsupdated_atingested_at
o11100.00paid2025-09-01 03:10:002025-09-01 03:12:002025-09-01 03:12:05
o11100.00refunded2025-09-01 03:10:002025-09-03 09:00:002025-09-03 09:00:10 (late status change)
o2250.00pending2025-09-01 20:00:002025-09-01 20:01:002025-09-01 20:01:05

Target: daily_user_metrics (dt DATE, u_id BIGINT, first_event_ts TIMESTAMP, events_cnt INT, paid_orders_cnt INT, paid_orders_amt DECIMAL(12,2)). Exclude users_dim.is_test = 1.

Tasks (be precise and tricky):

  1. Dedup staging logic. Write SQL CTE(s) that deduplicate events_raw by event_id keeping only the row with the max(ingested_at). Do the same for orders_raw by order_id, keeping the row with the max(updated_at) as the authoritative status snapshot. Explain why dedup by natural keys (event_id/order_id) is safer than relying on ROW_NUMBER over (u_id, event_ts) here.
  2. Daily metric for D=2025-09-01. Using only SQL, produce one row per non-test user with: first_event_ts on D; events_cnt on D; paid_orders_cnt and paid_orders_amt on D where an order counts only if the latest status (per your dedup) is in ('paid','shipped','completed') and not in ('refunded','canceled'). Show the SELECT that computes these fields using your deduped CTEs. Ensure events are filtered by event_ts between [D 00:00:00, D 23:59:59.999] in UTC.
  3. Late data capture window. Assume records for D can arrive up to 2 days late. Describe and write SQL for an incremental approach that, on run date R=D+0, D+1, and D+2, recomputes partitions for [D-2, D] and then MERGEs only the dt=D partition in daily_user_metrics so that late-arriving rows are included exactly once. Show an example MERGE (or INSERT OVERWRITE PARTITION) for dt=D and explain how it remains idempotent on reruns.
  4. Guardrails and failure modes. Enumerate at least five edge cases your ETL must handle (e.g., partial writes/transactionality, schema drift adding a nullable column to events_raw, null event_ts vs non-null ingested_at, daylight saving changes if you later switch to country-local days, replayed backfills that resend old event_ids). For each, state the defensive technique (e.g., write-ahead staging + checksum, schema evolution policy, fallback to ingested_at for partition pruning but event_ts for business logic, surrogate partitioning strategy, MERGE with deterministic dedup).
  5. Validation. Propose two reconciliation queries: one that compares counts and sums between orders_raw (latest status) and daily_user_metrics for D, and one that detects duplicate event_ids that still leaked into the D partition. Include expected results when using the sample data above.

Deduplicate raw events and compute daily user metrics for 2025-09-01

You own the daily_user_metrics fact table. For a fixed date D = '2025-09-01' (UTC), build the core SQL transformation that deduplicates raw events and orders, then computes daily metrics per user.

Requirements:

  1. Dedup staging logic:

    • Create a CTE that deduplicates events_raw by event_id, keeping only the row with the greatest ingested_at per event_id.
    • Create a CTE that deduplicates orders_raw by order_id, keeping only the row with the greatest updated_at per order_id as the authoritative status snapshot.

  2. Daily metric for D = '2025-09-01':

    • Using only SQL and your deduplicated CTEs, produce one row per non-test user (users_dim.is_test = 0) with the columns: 
        dt (DATE),
  u_id (BIGINT),
  first_event_ts (TIMESTAMP),
  events_cnt (INT),
  paid_orders_cnt (INT),
  paid_orders_amt (DECIMAL(12,2)).
    • first_event_ts on D is the minimum event_ts for that user on D.
    • events_cnt on D is the total number of events for that user on D.
    • paid_orders_cnt and paid_orders_amt on D count only orders where:
      • order_ts is on D (UTC), and
      • the latest status from your deduped orders CTE is in ('paid', 'shipped', 'completed') and not in ('refunded', 'canceled').
    • Filter events by event_ts between '2025-09-01 00:00:00' (inclusive) and '2025-09-02 00:00:00' (exclusive) in UTC.
    • Users with no events or qualifying orders on D must still appear with NULL first_event_ts and 0 counts/amounts.

Write a single SELECT statement (using CTEs) that returns these per-user metrics for dt = '2025-09-01'. For the sample output, format first_event_ts as YYYY-MM-DD HH24:MI:SS when it is non-NULL.

Table users_dim

columntypeconstraints
u_idBIGINTPRIMARY KEY, NOT NULL
created_atTIMESTAMPNOT NULL
countryVARCHAR(2)NOT NULL
is_testINTNOT NULL

Sample data:

countrycreated_atis_testu_id
US2025-08-28 10:00:0001
CA2025-08-30 12:00:0002
US2025-08-15 09:00:0013

Table events_raw

columntypeconstraints
event_idVARCHAR(20)NOT NULL
u_idBIGINTNOT NULL
event_typeVARCHAR(50)NOT NULL
event_tsTIMESTAMPNOT NULL
ingested_atTIMESTAMPNOT NULL
sourceVARCHAR(20)NOT NULL

Sample data:

event_idevent_tsevent_typeingested_atsourceu_id
e12025-09-01 02:03:00view2025-09-01 02:03:05web1
e12025-09-01 02:03:00view2025-09-02 01:00:00replay1
e22025-09-01 03:10:00purchase2025-09-01 03:10:04web1
e32025-08-31 23:59:59view2025-09-01 00:00:01web2

Table orders_raw

columntypeconstraints
order_idVARCHAR(20)NOT NULL
u_idBIGINTNOT NULL
amountDECIMAL(12,2)NOT NULL
statusVARCHAR(20)NOT NULL
order_tsTIMESTAMPNOT NULL
updated_atTIMESTAMPNOT NULL
ingested_atTIMESTAMPNOT NULL

Sample data:

amountingested_atorder_idorder_tsstatusu_idupdated_at
1002025-09-01 03:12:05o12025-09-01 03:10:00paid12025-09-01 03:12:00
1002025-09-03 09:00:10o12025-09-01 03:10:00refunded12025-09-03 09:00:00
502025-09-01 20:01:05o22025-09-01 20:00:00pending22025-09-01 20:01:00

Expected output

Per-user daily metrics for dt = '2025-09-01' based on deduplicated events_raw and orders_raw, excluding test users. User 1 has two events and no qualifying paid orders after considering the latest refunded status; user 2 has no events on 2025-09-01 and no qualifying orders.

dtevents_cntfirst_event_tspaid_orders_amtpaid_orders_cntu_id
2025-09-0122025-09-01 02:03:000.0001
2025-09-010NULL0.0002

Hints

  1. Use ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingested_at DESC) to pick the latest ingest per event_id.
  2. Compute per-user aggregates in CTEs for events and orders, then left join them to users_dim to ensure users with zero activity still appear.

Idempotent MERGE for late-arriving daily metrics (2-day window)

Suppose the ETL for daily_user_metrics runs once per day in UTC, and data for a given logical day D can arrive up to 2 days late. You want the ETL to be idempotent and rerunnable: re-running it for the same day should always produce the same final state, and late-arriving updates (like a status change from 'paid' to 'refunded') must be reflected.

For this question, fix D = '2025-09-01' (UTC) and assume:

  • Events are filtered by event_ts between '2025-09-01 00:00:00' (inclusive) and '2025-09-02 00:00:00' (exclusive).
  • Orders are filtered by order_ts in the same window.
  • The latest status per order_id is taken from orders_raw using the greatest updated_at, and qualifying statuses are in ('paid', 'shipped', 'completed') and not in ('refunded', 'canceled').
  • Only non-test users (users_dim.is_test = 0) are included.

Using the same deduplication logic as in Question 1, write a MERGE-based upsert that recomputes the per-user metrics for dt = '2025-09-01' and upserts them into daily_user_metrics. The MERGE must be idempotent: running it multiple times produces the same final rows, and it correctly overwrites any stale metrics from earlier runs that did not see the latest data (for example, where order o1 was still 'paid' before a late 'refunded' update arrived).

Write a single SQL statement (you may use CTEs) that:

  1. Builds a per-user aggregate for dt = '2025-09-01' (matching the logic from Question 1).
  2. MERGEs that aggregate into daily_user_metrics on (dt, u_id), updating existing rows and inserting missing ones.

Assume the warehouse supports ANSI-style MERGE.

Table users_dim

columntypeconstraints
u_idBIGINTPRIMARY KEY, NOT NULL
created_atTIMESTAMPNOT NULL
countryVARCHAR(2)NOT NULL
is_testINTNOT NULL

Sample data:

countrycreated_atis_testu_id
US2025-08-28 10:00:0001
CA2025-08-30 12:00:0002
US2025-08-15 09:00:0013

Table events_raw

columntypeconstraints
event_idVARCHAR(20)NOT NULL
u_idBIGINTNOT NULL
event_typeVARCHAR(50)NOT NULL
event_tsTIMESTAMPNOT NULL
ingested_atTIMESTAMPNOT NULL
sourceVARCHAR(20)NOT NULL

Sample data:

event_idevent_tsevent_typeingested_atsourceu_id
e12025-09-01 02:03:00view2025-09-01 02:03:05web1
e12025-09-01 02:03:00view2025-09-02 01:00:00replay1
e22025-09-01 03:10:00purchase2025-09-01 03:10:04web1
e32025-08-31 23:59:59view2025-09-01 00:00:01web2

Table orders_raw

columntypeconstraints
order_idVARCHAR(20)NOT NULL
u_idBIGINTNOT NULL
amountDECIMAL(12,2)NOT NULL
statusVARCHAR(20)NOT NULL
order_tsTIMESTAMPNOT NULL
updated_atTIMESTAMPNOT NULL
ingested_atTIMESTAMPNOT NULL

Sample data:

amountingested_atorder_idorder_tsstatusu_idupdated_at
1002025-09-01 03:12:05o12025-09-01 03:10:00paid12025-09-01 03:12:00
1002025-09-03 09:00:10o12025-09-01 03:10:00refunded12025-09-03 09:00:00
502025-09-01 20:01:05o22025-09-01 20:00:00pending22025-09-01 20:01:00

Table daily_user_metrics

columntypeconstraints
dtDATENOT NULL
u_idBIGINTNOT NULL
first_event_tsTIMESTAMP
events_cntINTNOT NULL
paid_orders_cntINTNOT NULL
paid_orders_amtDECIMAL(12,2)NOT NULL

Sample data:

dtevents_cntfirst_event_tspaid_orders_amtpaid_orders_cntu_id
2025-09-0122025-09-01 02:03:0010011
2025-09-010NULL002
2025-08-3112025-08-31 23:59:59002

Expected output

The MERGE statement does not itself return rows. After it runs, the daily_user_metrics table will contain one row per non-test user for dt = '2025-09-01' with metrics recomputed from the latest raw data snapshot. In this sample, it will overwrite the stale row for user 1 that still shows paid_orders_cnt = 1 and paid_orders_amt = 100.00, setting both to 0 because the latest status for order o1 is 'refunded'.

Hints

  1. Build a deterministic Daily_agg CTE that produces exactly one row per (dt, u_id), then MERGE it into daily_user_metrics on (dt, u_id).
  2. Idempotence comes from always recomputing metrics from the latest deduplicated raw data and fully overwriting the existing row for that (dt, u_id).

Reconciliation checks between raw data and daily_user_metrics for 2025-09-01

To validate your ETL, you want simple reconciliation checks between the raw tables and daily_user_metrics for a given day.

For D = '2025-09-01' (UTC), using the same deduplicated views of events_raw and orders_raw as in previous questions, write a single SQL query that returns two rows:

  1. An orders vs fact check:

    • Compute the total number of orders (raw_count) and total amount (raw_amount) from orders_raw where:
      • order_ts is between '2025-09-01 00:00:00' (inclusive) and '2025-09-02 00:00:00' (exclusive), and
      • the latest status per order_id (by updated_at) is in ('paid', 'shipped', 'completed').
    • Compute the corresponding aggregates from daily_user_metrics for dt = '2025-09-01':
      • fact_count = SUM(paid_orders_cnt)
      • fact_amount = SUM(paid_orders_amt)
    • Return these as one row with check_name = 'orders_vs_fact'.

  2. A duplicate events check:

    • Using your deduplicated events CTE (one row per event_id), check for duplicate event_ids for which event_ts falls on D.
    • Return the number of such duplicates as duplicate_event_ids (this should be 0 if the dedup is correct) in a second row with check_name = 'duplicate_events_on_d'.

Design the query to return the columns:

   check_name (VARCHAR),
   raw_count (INT),
   raw_amount (DECIMAL(12,2)),
   fact_count (INT),
   fact_amount (DECIMAL(12,2)),

duplicate_event_ids (INT).

Use NULL where a field is not applicable for a given check. Show the SQL and the expected output when run on the sample data.

Table users_dim

columntypeconstraints
u_idBIGINTPRIMARY KEY, NOT NULL
created_atTIMESTAMPNOT NULL
countryVARCHAR(2)NOT NULL
is_testINTNOT NULL

Sample data:

countrycreated_atis_testu_id
US2025-08-28 10:00:0001
CA2025-08-30 12:00:0002
US2025-08-15 09:00:0013

Table events_raw

columntypeconstraints
event_idVARCHAR(20)NOT NULL
u_idBIGINTNOT NULL
event_typeVARCHAR(50)NOT NULL
event_tsTIMESTAMPNOT NULL
ingested_atTIMESTAMPNOT NULL
sourceVARCHAR(20)NOT NULL

Sample data:

event_idevent_tsevent_typeingested_atsourceu_id
e12025-09-01 02:03:00view2025-09-01 02:03:05web1
e12025-09-01 02:03:00view2025-09-02 01:00:00replay1
e22025-09-01 03:10:00purchase2025-09-01 03:10:04web1
e32025-08-31 23:59:59view2025-09-01 00:00:01web2

Table orders_raw

columntypeconstraints
order_idVARCHAR(20)NOT NULL
u_idBIGINTNOT NULL
amountDECIMAL(12,2)NOT NULL
statusVARCHAR(20)NOT NULL
order_tsTIMESTAMPNOT NULL
updated_atTIMESTAMPNOT NULL
ingested_atTIMESTAMPNOT NULL

Sample data:

amountingested_atorder_idorder_tsstatusu_idupdated_at
1002025-09-01 03:12:05o12025-09-01 03:10:00paid12025-09-01 03:12:00
1002025-09-03 09:00:10o12025-09-01 03:10:00refunded12025-09-03 09:00:00
502025-09-01 20:01:05o22025-09-01 20:00:00pending22025-09-01 20:01:00

Table daily_user_metrics

columntypeconstraints
dtDATENOT NULL
u_idBIGINTNOT NULL
first_event_tsTIMESTAMP
events_cntINTNOT NULL
paid_orders_cntINTNOT NULL
paid_orders_amtDECIMAL(12,2)NOT NULL

Sample data:

dtevents_cntfirst_event_tspaid_orders_amtpaid_orders_cntu_id
2025-09-0122025-09-01 02:03:0010011
2025-09-010NULL002
2025-08-3112025-08-31 23:59:59002

Expected output

The first row compares the aggregate counts and amounts of qualifying paid orders in the deduplicated raw data vs the daily_user_metrics fact for dt = '2025-09-01'. Because the fact table still counts order o1 as paid while the latest raw status is 'refunded', raw_count/raw_amount are 0/0.00 and fact_count/fact_amount are 1/100.00. The second row reports that there are zero duplicate event_ids on 2025-09-01 after deduplication.

check_nameduplicate_event_idsfact_amountfact_countraw_amountraw_count
orders_vs_factNULL100100
duplicate_events_on_d0NULLNULLNULLNULL

Hints

  1. Aggregate paid orders from a deduplicated orders_raw CTE and compare against SUM() of the corresponding metrics in daily_user_metrics.
  2. To detect duplicates, group by event_id on the deduplicated events CTE for the D window and count how many have COUNT(*) > 1.