Exactly-Once Streaming Guarantees | Dataford Interview Questions

How do you ensure Exactly-Once processing in a streaming pipeline?

Using stream_events, processing_attempts, and accounts, write a query that treats the latest processing attempt for each event as authoritative. Include only active accounts and attempts whose latest status is COMMITTED.

Output

One row per active account, including accounts with no committed events
Columns: account_id, account_name, exactly_once_event_count, and exactly_once_amount
Sort by account_id ascending

Schema

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
account_name	VARCHAR(100)	Account display name
is_active	BOOLEAN	Whether the account should appear in reporting

stream_events

Column	Type	Description
delivery_idPK	INT	Unique physical delivery identifier
event_id	VARCHAR(50)	Logical event identifier, repeated for duplicate deliveries
account_id	INT	Account associated with the event
amount	NUMERIC(12,2)	Signed event amount
event_time	TIMESTAMPTZ	Time the event entered the stream

processing_attempts

Column	Type	Description
attempt_idPK	INT	Unique processing attempt identifier
delivery_id	INT	Physical delivery being processed
status	VARCHAR(20)	Processing outcome
attempted_at	TIMESTAMPTZ	Time of the processing attempt

Your question is Exactly-Once Streaming Guarantees. Start with the requirements and the three tables 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