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)?
Principal Engineer 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.
Reel Script
Setup: A nightly ETL job processes terabytes of data in a few hours and then sits idle the rest of the day — how do you avoid paying for a cluster sized for peak load around the clock?
Concept walkthrough: Introduce spot/preemptible compute as the obvious cost lever, then immediately name the catch — a spot instance can vanish mid-job. Walk through checkpointing and partitioning as the two mechanisms that turn "instance vanished" from a disaster into a cheap, localized retry.
Real example tie-in: Walk through a concrete failure: one worker processing a day's worth of a partition gets reclaimed 80% of the way through. Show how checkpointing means it resumes near where it left off, and how a small on-demand floor keeps the overall pipeline moving even if half the spot fleet disappears at once.
Tradeoffs & alternatives: Contrast an always-on reserved cluster (predictable, expensive, wasted most of the day) against the spot-plus-checkpointing design (cheaper, more operationally complex, requires the pipeline to be built for interruption from day one — you can't bolt this on later without a rewrite).
Principal Engineer takeaway: The spot-savings story only holds up because of resilience engineering underneath it — checkpointing and partitioning aren't cost features, they're reliability features that happen to be what makes the cost optimization safe. That's usually true of good cost engineering generally: the win comes from making the system robust enough to use the cheap option, not from finding a cheaper option and hoping it holds.