Exactly-Once Streaming Guarantees

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

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

Output details

• When attempts share an attempted_at, treat the greatest attempt_id as the latest.
• Count each event_id at most once after selecting its authoritative attempt.
• Treat missing or entirely NULL committed amounts as numeric 0 in exactly_once_amount.