Broken DAG: silver_events unique test red

SQL data engineering interview problem. Difficulty: beginner. Pattern: Deduplication. About 20 minutes. Free to practice.

It is Monday morning. A data job called silver_events ran on Friday and Airflow (the scheduler that runs your jobs) shows it as green, which means 'finished without errors'. But a data quality test from dbt says the event_id column is no longer unique, so there are duplicate rows. The job did not crash. It just produced wrong data, and nobody noticed until the test failed.

The editor already contains the SQL that was shipped on Friday. Read it, find the bug and fix it. After your fix, the query must return exactly 1 row for each event_id, and when an event has several copies, the row with the latest ingest_time must win. Return the columns event_id, user_id, session_id, event_type, product_id, device, country and event_time, sorted by event_id. Your fix must also be safe to run again. If the job is run twice on the same data, the second result must be identical to the first.

Requirements

  • Return 1 row for each event_id, not 1 row for the whole table.
  • When an event has several copies, keep the one with the latest ingest_time.
  • Return the 8 columns listed above and sort by event_id.
  • The query must give the same result every time it runs on the same data. This is called being idempotent.

Constraints

  • The table is ecommerce_events. The same event_id can appear more than once because events are sent again.
  • The shipped query ranks rows with ROW_NUMBER() OVER (ORDER BY ingest_time DESC) and keeps rank 1. It has no PARTITION BY, so it ranks the whole table as one big group.
  • Do not fix the problem by removing the ranking and picking 1 row for the whole table. That would return a single row and still be wrong.

Examples

Input: -- the shipped query has no PARTITION BY: QUALIFY ROW_NUMBER() OVER (ORDER BY ingest_time DESC) = 1 -- ecommerce_events has 812 rows total Output: event_id | user_id | event_type | event_time E00617 | U031 | page_view | 2026-08-19 06:39:09 Why this passes: Each user's eligible order totals are aggregated; users with higher totals appear earlier in the ranked output.

Topics: debugging, dedup, idempotency.

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

beginner

Broken DAG: silver_events unique test red

Production ticket: On-call: dbt unique_silver_events_event_id failed after a 'successful' DAG run.

You inherited a broken silver job. Airflow marked `silver_events` green; dbt's unique test on `event_id` is red. QA pasted duplicate replay rows in the incident log. The editor starts from the **buggy SQL that shipped Friday**. Fix it so: - 1 row per event_id (latest ingest_time wins) - columns: event_id, user_id, session_id, event_type, product_id, device, country, event_time - ordered by event_id - **idempotent**: running the job twice must not change the grain Do not filter to a single global ROW_NUMBER. That collapses the whole table to 1 row.