Overnight Chain: Duplicate Revenue, Worker Starvation, and a Cleared Sensor

Two things combine. First, retry_delay=5m is shorter than the tail of the task's runtime, so a retry can start while the previous attempt still holds locks, which is a plausible source of the serializable isolation violations in the first place. Second, the load is almost certainly an INSERT into the fact table: each re-execution appends the same day's rows again, and 2.1x is consistent with one duplicate run on a subset of days rather than a systematic doubling.

Fixes, in order of importance:

1. Set max_active_runs=1 on the DAG and use retry_exponential_backoff with a delay above the observed p99 runtime, so two attempts can never overlap.
2. Make the write a delete-then-insert or MERGE keyed on (order_id, updated_at) inside one transaction, or COPY into a staging table and swap partitions. Serializable isolation violations should be retried at statement level inside Redshift, not by re-running the whole load.
3. Add a data-quality gate that compares source and target row counts and sum(amount) for the business date within tolerance, and blocks publication on mismatch. The bad numbers reached executives because nothing checked.
4. Backfill the affected days from source and record which partition overwrite was used, so the correction is auditable.

A 40-second scheduler heartbeat delay on 3 x mw1.large is the clearest signal: the scheduler, webserver, and workers share a very small footprint, and the environment is saturated before any job logic is considered. Adding tasks will not help until that is addressed.

Changes:

• Move to a larger MWAA environment, or a deployment with autoscaling workers, and confirm the scheduler is not co-starved by sensor pokes.
• Sensors are the hidden cost. An S3 key sensor in poke mode holds a worker slot and calls ListObjects every few seconds; with 130 jobs that is thousands of requests and most of the slot pressure. Switch to mode=reschedule or deferrable operators, or better, replace polling with EventBridge-driven triggers.
• Flatten the critical path. Identify which of the 130 jobs are truly serialized; raise max_active_tasks where the warehouse can take the concurrency, and confirm Redshift WLM has a queue for the load phase and concurrency scaling if needed.
• Glue job startup alone is around a minute per job; reuse a small number of jobs driven by parameters instead of 130 distinct jobs where the logic is shared.

Measure per-task duration before and after: the deadline is a critical-path problem, not a total-runtime problem.

A job that reports success while writing nothing is the worst failure mode in the chain, because every downstream check that only looks at task state passes.

• Replace presence-based sensing with completeness-based sensing. The upstream producer should write a _SUCCESS or manifest object only after the full extract is committed, and the sensor should wait for that marker, not for the first object in the prefix.
• Have each Glue job assert a minimum row count and reject a zero-row read by raising, unless the job is explicitly parameterized to allow an empty day (weekends, holidays).
• Add a reconciliation task after load that compares source and target counts for the business date and fails the DAG if outside tolerance.
• Control manual intervention: require downstream tasks to be cleared together with the sensor, and log the operator identity for clears via CloudTrail/audit logs.

Finally, treat an unexpected success-with-zero-rows as an incident with an alarm on target table growth per day, so the next occurrence is caught by monitoring rather than by an executive noticing the revenue number.