Broken silver: late replays filtered out

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Late Data. About 20 minutes. Part of the Pro drill bank.

Ticket from the data platform team: 'Late events never reach silver. Our numbers are lower than the source.' The silver job has a filter that keeps only rows where the event time equals the ingest time, so any row that arrived later is thrown away, including every replay.

The silver query for ecommerce_events keeps only rows where event_time = ingest_time. Real traffic almost never has these two equal, because there is always a small delay, and replayed events are written again with a later ingest_time. As a result, most data is lost. Remove that filter and deduplicate at the event_id level instead. When an event appears more than once, keep the row with the latest ingest_time. Return event_id, user_id, session_id, event_type, product_id, device, country, event_time, ordered by event_id. The result must stay the same if the job is run again.

Requirements

  • Remove the event_time = ingest_time condition.
  • Keep 1 row for each event_id, the one with the latest ingest_time.
  • Return the 8 columns, without ingest_time.
  • Order by event_id.

Constraints

  • Do not filter on event_time = ingest_time in any form.
  • The latest ingest_time wins when the same event_id appears more than once.
  • The query must be safe to run again (idempotent).

Examples

Input: event_id | event_time | ingest_time E00012 | 2026-08-11 03:12:11 | 2026-08-11 03:12:11 E00012 | 2026-08-11 03:12:11 | 2026-08-11 03:19:48 Output: event_id | user_id | session_id | event_type | product_id | device | country | event_time E00012 | U014 | S014-002 | add_to_cart | SKU-006 | android | US | 2026-08-11 03:12:11 Why this passes: The replay row has a later ingest_time. The equality filter drops it and often drops first writes with ingest skew too. Latest ingest per event_id must win.

Topics: debugging, late data, dedup.

More SQL interview questions · All interview problems · Learn data engineering

intermediate

Broken silver: late replays filtered out

Production ticket: Silver clickstream keeps only rows where event_time equals ingest_time, dropping replays.

The silver job filters `ecommerce_events` to rows where `event_time = ingest_time`. Replay traffic writes the same `event_id` again with a later `ingest_time`, and most first writes also have ingest skew. Fix the SQL so: - event_id, user_id, session_id, event_type, product_id, device, country, event_time - 1 row per event_id (latest ingest_time wins) - ordered by event_id - idempotent on replay Remove the bad equality filter. Dedup at event_id grain.