Broken incremental: UNION ALL without dedup

SQL data engineering interview problem. Difficulty: advanced. Pattern: Deduplication. About 30 minutes. Part of the Pro drill bank.

Ticket from analytics engineering: 'Row counts doubled last night and the dbt unique test on event_id is failing.' A re-run of the incremental job loaded the full bronze table twice with UNION ALL, and nothing removes the repeated rows afterwards.

The incremental silver job copies ecommerce_events twice with UNION ALL (it simulates a bad re-run) and writes the result with no cleanup. That makes 2 copies of every row and breaks the unique event_id test. Fix the query. You may keep the UNION ALL as a bronze simulation, for example inside a CTE or a subquery, but remove the duplicates from the combined data. Keep 1 row per event_id, the one with the latest ingest_time. Return event_id, user_id, session_id, event_type, product_id, device, country, event_time, ordered by event_id. If the job runs twice on the same bronze data, the output must be the same.

Requirements

  • Keep the UNION ALL that simulates the duplicated bronze data.
  • Deduplicate after the UNION ALL, by event_id.
  • Keep the row with the latest ingest_time for each event.
  • Return the 8 columns, without ingest_time, ordered by event_id.

Constraints

  • The bronze simulation is a UNION ALL of ecommerce_events with itself.
  • The result must have exactly 1 row for every event_id.
  • Running the fixed query twice must give identical output.

Examples

Input: bronze reload (UNION ALL same events twice) event_id | ingest_time E00011 | 2026-08-11 03:32:35 E00011 | 2026-08-11 03:32:35 E00011 | 2026-08-11 03:43:35 E00011 | 2026-08-11 03:43:35 Output: event_id | user_id | session_id | event_type | product_id | device | country | event_time E00011 | U035 | S035-002 | purchase | SKU-008 | web | DE | 2026-08-11 03:32:35 Why this passes: the window/dedup keeps the intended row per partition and drops the rest.

Topics: debugging, incremental, idempotency.

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

advanced

Broken incremental: UNION ALL without dedup

Production ticket: Re-running the incremental load UNION ALLs bronze twice and skips dedup.

The incremental silver job simulates a bad re-run: it UNION ALLs the full `ecommerce_events` bronze twice and writes without dedup. dbt unique on `event_id` fails; row count is roughly 2x. 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**: running twice on the same bronze must not change the grain Keep the UNION ALL bronze simulation in a CTE if needed, but dedup the combined stream.