Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing

Broken DAG: silver_events unique test red

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

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: one 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 That collapses the whole table to one row.

Requirements

  • A given event_id may appear more than once in ecommerce_events (replay writes); the fix must keep exactly one row per event_id, not one row total.
  • One row per event_id; late-ingest replays disappear; second run identical.

Constraints

  • Running the fixed query twice on the same data must produce identical output (idempotent).
  • Return exactly these columns: event_id, user_id, session_id, event_type, product_id, device, country, event_time.
  • ecommerce_events.event_type: page_view | add_to_cart | purchase | bounce.

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: - one 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 one row.