Maintain Entry Numbers with Incremental Loads and Historical Backfills
OtherAnalytics Engineer · Technical ScreenLast updated
Design incremental per-context entry numbering with idempotent state, contiguous-gap backfills, and correct handling of interleaved late events.
A table contains entry_uuid, context_uuid, visitor_id, created_at, and platform. Every entry needs a one-based position within its context, ordered by (created_at, entry_uuid). The table is loaded incrementally each day.
Explain how to maintain these entry numbers without scanning all history for every daily load. Then explain how to repair numbering if a contiguous historical interval was missing even though later entries have already been processed. Distinguish that case from late data interleaved throughout the existing timeline. This is a pipeline-design discussion rather than a request to write a query.
Hints
Hint 1
Find the insertion point: Today’s maximum is a count after the already-processed suffix, not the number of entries before a historical gap.
Follow-up questions
- What goes wrong if a daily load is retried after entries were written but state was not updated?
- What if a late entry has the same created_at as an existing entry but sorts earlier by entry_uuid?
Solution walkthrough is for members
Get the full walkthrough with reference code and complexity analysis, plus Kitty Coach at 300 messages a day. Hints, the code runner and community answers stay free.