Maintain Entry Numbers with Incremental Loads and Historical Backfills

# Maintain Entry Numbers with Incremental Loads and Historical Backfills 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. ### What a Strong Answer Covers - Per-context state and the assumptions under which appending local row numbers is correct. - An idempotent, atomic relationship between inserted entries and updated state. - A historical backfill based on state before the gap and shifts to affected later entries. - A correct fallback for interleaved late arrivals, tie ordering, and affected-context scope. ```hint 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?