Dataproc and BigQuery Cost Reduction Under a Hard SLA
TerraMetrics runs a nightly geospatial ETL pipeline from 22:00 to 04:00. It uses a fixed Dataproc cluster of 4,000 vCPUs and processes 2 PB of imagery in Cloud Storage; monthly Dataproc spend is about $180,000 and BigQuery on-demand queries add $60,000. Monitoring shows average CPU utilization of 28% and 70% idle capacity during the window. The SLA requires all jobs to finish by 06:00. Finance wants a 30% reduction in 90 days. The data team has strong Spark skills but limited FinOps experience. Some jobs are idempotent and can tolerate preemption; two regulatory-reporting jobs cannot. BigQuery tables are not partitioned by date, and dashboards scan full tables. A three-year committed-use purchase is under consideration for the baseline.
1. Quantify the main cost drivers and prioritize the changes needed to hit a 30% reduction in 90 days.
Read the worked answer
Quantify the obvious waste
A fixed 4,000-vCPU cluster for six hours per night is about 720,000 vCPU-hours per month. At 28% average utilization, roughly 70% of that capacity is idle. The first priority is not a discount; it is removing idle capacity. Ephemeral or autoscaling clusters can shrink the average footprint, but they must still finish by 06:00.
Priority order
- Right-size and autoscale Dataproc. Use ephemeral clusters with autoscaling and graceful decommission. Target a range rather than a fixed 4,000 vCPUs. Measure job duration at lower capacity before committing.
- Use Spot or preemptible workers for tolerant jobs. Most ETL jobs are idempotent; the two regulatory jobs should run on a small, reliable on-demand pool or a separate cluster. Spot can cut compute cost substantially, but only after checkpointing and retry behavior are proven.
- Fix BigQuery scan patterns. Partition tables by date and cluster by common filters. Replace full-table dashboard scans with partitioned queries, materialized views, or a BI layer that uses pre-aggregated tables. If dashboard usage is predictable, compare BigQuery editions with slot autoscaling against on-demand; do not assume slots are cheaper without measuring slot utilization.
- Commit only the proven baseline. A three-year commitment before right-sizing locks in waste. If a commitment is used, commit to the stable regulatory baseline after measuring it, not to the current peak.
90-day path
Weeks 1-2: baseline cost per job and per dashboard. Weeks 3-6: partition/cluster the largest BigQuery tables and move tolerant jobs to Spot. Weeks 7-10: run autoscaled Dataproc in parallel with the fixed cluster and compare duration and cost. Weeks 11-12: cut over and validate the 06:00 SLA. A 30% reduction is plausible from idle removal alone, but only if job runtimes do not stretch past the SLA.
2. Redesign the nightly pipeline to reduce cost without risking the 06:00 SLA. What scheduling, cluster, and data-layout changes do you make?
Read the worked answer
Separate critical and tolerant work
Split the DAG into two lanes. The two regulatory jobs run on a small, predictable on-demand Dataproc cluster or Dataproc Serverless configuration with no Spot dependency. Tolerant jobs use ephemeral Dataproc clusters with autoscaling, secondary Spot workers, and graceful decommission. This avoids a single Spot shortage delaying regulatory output.
Scheduling and orchestration
Use Workflow Templates or Cloud Composer only if the existing orchestration justifies it; Composer has its own cost. Start with dependency-aware scheduling so downstream jobs do not wait for unrelated upstream jobs. Add checkpoints and idempotent writes so a preempted task can resume. Set a hard deadline alert at 04:30 and an automatic fallback that adds on-demand capacity if the pipeline is behind.
Data layout and processing
Partition BigQuery tables by date and cluster by the most common geospatial or customer filter. Write intermediate Spark output in Parquet or ORC, not CSV. Use Cloud Storage lifecycle rules to delete temporary data. Prune input partitions and avoid SELECT * in dashboard queries. For repeated aggregations, create materialized views or scheduled aggregate tables.
Capacity plan
Benchmark the pipeline at 2,000, 3,000, and 4,000 vCPUs. If 2,500 vCPUs finish by 05:15, the extra capacity is insurance, not a requirement. Keep a burst policy that can add capacity before the deadline. The design goal is to pay for idle capacity only when the SLA requires it, not every night.
3. Define the metrics, guardrails, and operating cadence to keep savings from eroding after the first optimization.
Read the worked answer
Metrics that connect cost to outcomes
Track cost per successful job, Dataproc vCPU-hour utilization, Spot preemption rate, job duration p95, SLA adherence, BigQuery bytes scanned per dashboard, slot utilization if using editions, and spend versus budget by team. Add a simple unit metric such as cost per processed terabyte or cost per regulatory report. Without a unit metric, total spend can fall while unit economics worsen.
Guardrails
Use labels for team, pipeline, and environment. Set budget alerts at 80% and 100%, but also set anomaly alerts on daily spend. Enforce quotas on Dataproc vCPU and BigQuery bytes scanned where appropriate. Require a cost estimate in the pull request for new pipeline jobs. For commitments, track utilization monthly; an unused commitment is a fixed cost, not a saving.
Operating cadence
Hold a weekly 30-minute FinOps review with the data team and finance: top five cost changes, SLA misses, and upcoming pipeline changes. Do a monthly deep dive on one pipeline. After any performance change, compare before-and-after cost and duration. Keep a rollback plan for optimizations that threaten the SLA. The team should own the metrics, not receive a monthly bill after the fact.