Deduplicate Streaming Records | Dataford Interview Questions

Write a query to deduplicate a streaming dataset where duplicate records can arrive up to 24 hours late.

Assume streaming_events contains a rolling input window. Use the latest ingested_at value as the watermark, include records ingested within the preceding 24 hours inclusively, and retain the earliest ingested row for each event_id. Use ingestion_id as the deterministic tie-breaker.

Output

One row per retained event_id, with columns event_id, event_time, ingested_at, and payload
Exclude records outside the 24-hour ingestion window and later duplicates
Order by event_id, then ingestion_id ascending

Schema

streaming_events

Column	Type	Description
ingestion_idPK	BIGINT	Unique identifier for the ingested row
event_id	VARCHAR(64)	Logical identifier used to identify duplicate events
event_time	TIMESTAMPTZ	Time at which the event occurred
ingested_at	TIMESTAMPTZ	Time at which the streaming system received the row
payload	TEXT	Optional event payload

Your question is Deduplicate Streaming Records. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Write a query to deduplicate a streaming dataset where duplicate records can arrive up to 24 hours late.

Output

One row per retained event_id, with columns event_id, event_time, ingested_at, and payload
Exclude records outside the 24-hour ingestion window and later duplicates
Order by event_id, then ingestion_id ascending

Schema

streaming_events

Column	Type	Description
ingestion_idPK	BIGINT	Unique identifier for the ingested row
event_id	VARCHAR(64)	Logical identifier used to identify duplicate events
event_time	TIMESTAMPTZ	Time at which the event occurred
ingested_at	TIMESTAMPTZ	Time at which the streaming system received the row
payload	TEXT	Optional event payload

Your question is Deduplicate Streaming Records. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.