Nightly batch SLO breach after upstream volume spike
A retail team runs a nightly MaxCompute pipeline in DataWorks. Raw orders land in an OSS-backed external table; a SQL task joins it to a 2 TB internal dimension table and writes a partitioned result table (daily partition, ~150 GB/day). The pipeline must finish by 06:00. For six weeks it finished by 04:30. Last week marketing onboarded a new channel that doubled order volume, and now the job finishes at 07:10. The SQL task shows heavy shuffle spill and one reduce-stage straggler. Cluster quotas are shared with three other teams, and the team cannot raise its CU quota this quarter. The 06:00 deadline is contractual for a downstream BI dashboard.
1. The team proposes simply adding more reducers. Evaluate this as the first move and describe a better diagnostic ordering.
Read the worked answer
Adding reducers treats a symptom. The spill and single straggler point to skew or a bad join strategy, not raw reducer shortage. Better ordering:
- Confirm the bottleneck is compute, not I/O or scheduling. Check task instance timing breakdown: queue wait vs. execution vs. data transfer. If queue wait grew because shared quota is contended, more reducers won't help and may worsen contention.
- Inspect the join. A 2 TB dimension joined to a now-larger fact table is a classic candidate for a map-side join if the dimension fits the broadcast threshold, or for a skew-aware join if a few dimension keys dominate. Look at key distribution before touching parallelism.
- Check partition pruning. Confirm the external-table scan is pruned to the needed dates and the result write only touches today's partition. A doubled scan because a filter was lost is common after upstream schema changes.
- Then tune parallelism once skew is understood.
Adding reducers blindly can leave the straggler unchanged (skew persists) while increasing small-file output and shuffle overhead.
2. Given the fixed quota and hard deadline, propose a concrete redesign that decouples the deadline from the doubled volume.
Read the worked answer
Decouple work from the single nightly window:
- Incremental processing: ensure the fact side only reads new/changed partitions rather than rescanning history. The result table is daily-partitioned, so each run should touch one partition.
- Pre-aggregate the dimension join: if the 2 TB dimension is slowly changing and reused, materialize a slimmer joined/aggregated intermediate once per day (or per dimension change) and let the heavy job read the smaller artifact. This trades storage for shuffle.
- Split the pipeline into stages with independent SLAs: land raw, then transform, then publish. Run the transform as soon as upstream data is complete rather than waiting for a fixed clock, so a volume spike starts earlier.
- Skew handling: salt the dominant keys or use a skew join hint so the straggler disappears; measure the reduce-stage distribution after the change.
- Fallback: if the BI dashboard tolerates it, publish a partial/prior-day snapshot at 05:30 and refresh when complete, so the contractual deadline is met by data availability, not job completion.
Validate each change against the 06:00 deadline with headroom, not just 'faster than before.'