Design a Cost-Optimized Batch Data Pipeline
Scenario prompt
Design a nightly batch pipeline that processes terabytes of data (e.g., analytics ETL feeding a data warehouse) for a company that wants to minimize compute spend. It needs to:
- Process a large, bursty daily volume inside an overnight SLA window
- Minimize cost per terabyte processed, not just hit the SLA at any cost
- Never fail silently — a partial run must be visible and cheaply re-runnable, not require a full rerun
- Scale down to near-zero cost during the many idle hours between runs
Mihir's attempt
[!todo] Write your own attempt here before reading the model solution below — your compute choice (reserved vs. on-demand vs. spot), how you'd make a partial failure recoverable, and how you'd keep idle cost near zero.
Model solution
Ephemeral, autoscaled compute — spot/preemptible instances as the default, not reserved capacity. A cluster sized and reserved for peak nightly load sits mostly idle the other 22 hours of the day; batch workloads are exactly the bursty, interruption-tolerant profile spot/preemptible pricing is built for (60-90% cheaper than on-demand in exchange for the provider being able to reclaim the instance with short notice). The cost math only works, though, if the job can survive losing a node mid-run.
Make spot interruption a non-event via checkpointing plus a small on-demand floor. Each worker checkpoints its progress (which partitions are done) frequently enough that losing a node costs minutes, not hours, of reprocessing. A small baseline of on-demand (or reserved) capacity runs alongside the spot fleet so the pipeline still makes forward progress — just more slowly — during a spot-capacity crunch, protecting the SLA from a total spot-market squeeze rather than betting the whole run on spot availability.
Partition data so any unit of work is independently replayable. Splitting the job by a natural key (date, customer, source table) means a failed or interrupted partition can be reprocessed in isolation — a single retry, not a full-pipeline rerun. This is what turns "partial failure" from an incident into a routine, cheap retry, and it's the same idempotency principle that makes the spot-interruption story tractable in the first place.
Decouple storage from compute. Data lives in object storage (cheap, durable, pay-for-what-you-store) rather than on disks attached to always-running compute. Compute spins up only for the processing window and reads/writes against that storage layer, so the idle 22 hours cost storage-only rates instead of paying for attached compute that's doing nothing.
Track cost and completion per partition, not just pipeline-level pass/fail. A DAG-level "succeeded" or "failed" status hides which specific partitions actually completed and what each one cost. Per-partition observability (status, duration, cost) is what makes a partial failure visible and targeted to fix, and it's also what makes cost attribution possible in the first place — you can't optimize spend on a pipeline you can't see the cost breakdown of.
Gaps to revisit
- Right-sizing the spot bid vs. SLA risk — how aggressively to lean on spot when the overnight window is tight enough that a bad spot-availability night could blow the SLA even with the on-demand floor?
- Data skew — a handful of oversized partitions (a single huge customer, a bursty source table) can blow the SLA even when average partition sizing looks fine; does the design need dynamic re-partitioning or per-partition timeouts?
- Chargeback/showback — once per-partition cost is visible, how does that get attributed back to the teams or datasets actually driving the spend, and does that visibility change upstream behavior (teams trimming unnecessary data)?
Engineering Lens
Cost is rarely a pillar you optimize in isolation — this design is really a resilience-for-cost trade dressed up as a batch job: every mechanism that makes spot interruption survivable (checkpointing, partitioning, a small on-demand floor) is the same mechanism that would make the pipeline resilient to any other kind of transient failure. Being able to say "we chose spot because the failure-recovery story was already solid enough to absorb it cheaply" — rather than picking spot first and hoping recovery works out — is the kind of ordering that reads as deliberate cost engineering in a review, not a corner cut. This also maps directly onto Fintech/Capital Markets overnight batch settlement and reconciliation jobs, where the SLA (books closed by market open) is non-negotiable but the spend on the compute that gets there very much is.