In Mixpanel, a retried import job double-counted events — how do you make the import idempotent?

## The failure A backfill streams events into Mixpanel, a batch times out mid-flight, the job's retry policy re-sends the batch, and the funnel now shows twice the conversions for that period. This is the ordinary at-least-once delivery problem, and Mixpanel gives you exactly one lever against it. ## $insert_id `$insert_id` is a reserved event property that acts as the event's identity for deduplication purposes. Mixpanel treats events sharing the same `$insert_id`, `distinct_id` and timestamp as the same event and keeps one, within a bounded recency window. If the property is absent, Mixpanel cannot tell a retry from a genuine second occurrence of the same action, and both are counted. The property only does its job when it is **deterministic**. The identifier must be a pure function of the source record: ```text $insert_id = hash(source_table + source_row_id + event_name) ``` Generate it from the row you are exporting, not from the wall clock, a random generator, or the position in the batch. A job that calls a UUID generator per send produces a fresh identity on every attempt and defeats the mechanism entirely — which is the most common way teams end up with duplicates despite having read that Mixpanel deduplicates. Equally, if a rebuild of the upstream table changes surrogate keys, your identifiers change with them and the reload will not deduplicate against the original load. ## The window is not forever Deduplication is scoped to recent ingestion, not to the whole history of the project. Re-importing a month of two-year-old events with the same identifiers will not silently collapse into the existing rows. For a genuine historical correction the sequence is: delete the affected event range through Mixpanel's deletion facility, confirm the range is empty, then import the corrected data. Treat that as a deliberate, reviewed operation — deletion is irreversible and a mis-specified range removes data you still needed. ## /import versus /track The real-time ingestion endpoint used by the client libraries is designed for events happening now and rejects events timestamped well in the past. Historical loads go through the import endpoint, which is authenticated with a service account rather than a client-side project token — a distinction that also matters for security, since the import credential must never be shipped to a browser. Check the current API reference for the exact payload shape before writing the client, including whether the `time` field is expected in seconds or milliseconds; sending the wrong unit lands your events decades away from where you meant, and the fix is a delete-and-reload. The import endpoint validates each event and reports per-event failures. A batch is therefore not simply 'succeeded' or 'failed': some events land and some are rejected for malformed properties or unparseable timestamps. A retry policy that re-sends the entire batch on any non-2xx response, without deterministic identifiers, is the direct cause of the duplication in this question. The correct handler records which events failed, fixes or quarantines them, and retries only those. ## Designing the loader A robust Mixpanel backfill looks like any other idempotent sink writer: - **Extract with a stable key.** Every source row must have an immutable identifier you can hash. - **Derive $insert_id deterministically** from that key plus the event name, so one source row producing two distinct events still yields two identities. - **Batch and checkpoint.** Record the highest source watermark actually confirmed, so a crash resumes rather than restarts. - **Handle partial failures per event**, not per batch. - **Make reruns safe by design** — assume the job will be run twice by a human at some point, because it will be. ## Verifying the fix After the load, do not trust the absence of errors. Count events per day in the source system and in Mixpanel over the loaded range and compare; a duplicated load shows as a clean multiple, a partially-failed one as a shortfall concentrated in particular batches. Spot-check a handful of source rows end to end — the right event name, the right `distinct_id`, the right timestamp, the properties intact — before anyone builds a report on the data. Reconciliation counts caught after a dashboard has been shared are far more expensive than the same counts run before.