Design an idempotent SQL ETL for late data
Company: Stripe
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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_id | created_at | country | is_test
---- | -------------------- | ------- | -------
1 | 2025-08-28 10:00:00 | US | 0
2 | 2025-08-30 12:00:00 | CA | 0
3 | 2025-08-15 09:00:00 | US | 1
Table: events_raw
event_id | u_id | event_type | event_ts | ingested_at | source
-------- | ---- | ---------- | ------------------- | -------------------- | ------
e1 | 1 | view | 2025-09-01 02:03:00 | 2025-09-01 02:03:05 | web
e1 | 1 | view | 2025-09-01 02:03:00 | 2025-09-02 01:00:00 | replay (duplicate, late)
e2 | 1 | purchase | 2025-09-01 03:10:00 | 2025-09-01 03:10:04 | web
e3 | 2 | view | 2025-08-31 23:59:59 | 2025-09-01 00:00:01 | web (late arrival for 08-31)
Table: orders_raw
order_id | u_id | amount | status | order_ts | updated_at | ingested_at
-------- | ---- | ------ | -------- | ------------------- | ------------------- | -------------------
o1 | 1 | 100.00 | paid | 2025-09-01 03:10:00 | 2025-09-01 03:12:00 | 2025-09-01 03:12:05
o1 | 1 | 100.00 | refunded | 2025-09-01 03:10:00 | 2025-09-03 09:00:00 | 2025-09-03 09:00:10 (late status change)
o2 | 2 | 50.00 | pending | 2025-09-01 20:00:00 | 2025-09-01 20:01:00 | 2025-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.
Overview: 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.
Read the full Stripe Data Scientist interview experience this question came from
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.
Tables
users_dim(u_id BIGINT, created_at TIMESTAMP, country VARCHAR(2), is_test INT)
events_raw(event_id VARCHAR(20), u_id BIGINT, event_type VARCHAR(50), event_ts TIMESTAMP, ingested_at TIMESTAMP, source VARCHAR(20))
orders_raw(order_id VARCHAR(20), u_id BIGINT, amount DECIMAL(12,2), status VARCHAR(20), order_ts TIMESTAMP, updated_at TIMESTAMP, ingested_at TIMESTAMP)
Hints
- Use ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingested_at DESC) to pick the latest ingest per event_id.
- 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.
Tables
users_dim(u_id BIGINT, created_at TIMESTAMP, country VARCHAR(2), is_test INT)
events_raw(event_id VARCHAR(20), u_id BIGINT, event_type VARCHAR(50), event_ts TIMESTAMP, ingested_at TIMESTAMP, source VARCHAR(20))
orders_raw(order_id VARCHAR(20), u_id BIGINT, amount DECIMAL(12,2), status VARCHAR(20), order_ts TIMESTAMP, updated_at TIMESTAMP, ingested_at TIMESTAMP)
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))
Hints
- 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).
- 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.
Tables
users_dim(u_id BIGINT, created_at TIMESTAMP, country VARCHAR(2), is_test INT)
events_raw(event_id VARCHAR(20), u_id BIGINT, event_type VARCHAR(50), event_ts TIMESTAMP, ingested_at TIMESTAMP, source VARCHAR(20))
orders_raw(order_id VARCHAR(20), u_id BIGINT, amount DECIMAL(12,2), status VARCHAR(20), order_ts TIMESTAMP, updated_at TIMESTAMP, ingested_at TIMESTAMP)
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))
Hints
- Aggregate paid orders from a deduplicated orders_raw CTE and compare against SUM() of the corresponding metrics in daily_user_metrics.
- To detect duplicates, group by event_id on the deduplicated events CTE for the D window and count how many have COUNT(*) > 1.