Delivery Volume Doubles and Tracking Breaks
An e-commerce client doubled its daily email volume from 5M to 10M over a quarter. Since then, open-tracking rates reported in Campaign have fallen from 22% to 9%, but the client's ESP-side webhook feed shows opens are steady. Campaign's tracking workflow runs every 15 minutes and processes the tracking log table. The tracking log table has grown from 200M to 900M rows and is never purged. The workflow's processing time has grown from 4 minutes to 55 minutes per run, so runs now overlap. The DBA reports the tracking log table has no index on the timestamp column used by the workflow's incremental filter, and the workflow uses a "greater than last processed ID" pattern with no upper bound.
1. Explain how an ingestion problem produces a *reporting* symptom, and why the open rate specifically fell.
Read the worked answer
The open rate did not fall because people stopped opening; the ESP webhook confirms opens are steady. What fell is Campaign's visibility into those opens, because the tracking ingestion pipeline can no longer keep up.
The mechanism: the tracking workflow filters on a timestamp column with no index, so each incremental run scans the 900M-row table. Runtime grew from 4 min to 55 min, which exceeds the 15-minute schedule - runs now overlap. Overlapping runs of an unbounded "greater than last processed ID" workflow race each other: two runs can read overlapping ranges, and the effective processed watermark advances erratically. The result is that a growing share of tracking events is either processed late (after the reporting window the client looks at) or skipped when a later run's watermark jumps past events an earlier run had not yet committed.
Why open rate specifically: opens are the highest-volume tracked event, so they are the most affected by a lagging, racing pipeline. Clicks are lower volume and degrade more slowly. The 22%→9% drop is a measurement artifact concentrated in the highest-volume event type - consistent with ingestion loss, not behavior change.
Confirmation test: compare Campaign's raw tracking log row count per day against the ESP webhook count per day. If Campaign's ingested count is materially below the webhook count and the gap grows over time, ingestion loss is confirmed.
2. Give a remediation plan that fixes both the immediate reporting gap and the structural cause, and say how you'd prove the fix worked.
Read the worked answer
Structural fixes (in order):
- Index the timestamp column used by the incremental filter. This is the root cause of the 4→55 minute growth; without it, any volume increase re-breaks the pipeline.
- Bound each run. Replace the open-ended "greater than last processed ID" filter with an explicit upper bound per run (a fixed window), so overlapping runs cannot race over the same range and a watermark cannot skip uncommitted events.
- Serialize the workflow. Ensure a run cannot start while the previous run is active, or move to a scheduled window that comfortably exceeds worst-case runtime. Overlap must be made impossible, not just unlikely.
- Purge or archive. A 900M-row never-purged tracking table is the underlying scaling defect. Define a retention policy (e.g., archive raw events older than N days to a warehouse, keep aggregates in Campaign).
- Backfill the gap. Re-ingest the tracking events lost during the degraded period from the ESP webhook feed so historical reporting is corrected, clearly flagged as backfilled.
Proof the fix worked:
- Runtime per run returns to a small fraction of the schedule interval, with no overlapping executions observed over a full week.
- Daily ingested tracking row count matches the ESP webhook count within an agreed tolerance for 7 consecutive days.
- Open rate recovers toward the ESP-side figure; a persistent gap after ingestion is proven complete would indicate a separate reporting-definition issue, not ingestion.