Maintaining and automating data workloads · Topic 1 of 5
Optimizing Cost and Resources for Data Workloads
Cut BigQuery, Dataflow and Dataproc costs per business need, protect capacity for critical jobs, and choose persistent or job-based Dataproc clusters.
- 5 min
- Partition, cluster, select fewer columns and dry-run to cut BigQuery cost.
- Maximum bytes billed and custom quotas cap query spending.
- FlexRS for delay-tolerant Dataflow batch; Spot secondary workers for Dataproc.
What it is
Optimizing resources means paying only for what each business need requires, while making sure business-critical processes always get the capacity they need. For Hadoop and Spark workloads it also means choosing between persistent clusters that run all the time and job-based clusters created for each job.
How it works
Minimizing BigQuery costs
| Lever | How it saves |
|---|---|
| Partitioning and clustering | Queries read fewer bytes |
| Select only needed columns | Columnar storage bills only columns read; avoid SELECT * |
Table preview instead of SELECT * ... LIMIT | Preview reads no billed bytes |
| Dry run | Shows bytes a query would process before running it |
| Maximum bytes billed | Fails a query that would exceed a limit instead of charging for it |
| Custom quotas | Caps query bytes per day per project or per user |
| Materialized views and aggregate tables | Repeated dashboards read small results |
| Long-term storage | Unmodified tables cost about half after 90 days |
| On-demand versus capacity pricing | Pay per TiB scanned, or for slots when usage is high and steady |
bq query --use_legacy_sql=false --dry_run \
'SELECT customer_id, SUM(amount) FROM sales.order_facts GROUP BY customer_id'
bq query --use_legacy_sql=false --maximum_bytes_billed=1000000000000 \
'SELECT customer_id, SUM(amount) FROM sales.order_facts GROUP BY customer_id'Minimizing Dataflow and Dataproc costs
| Service | Levers |
|---|---|
| Dataflow batch | FlexRS (--flexrs_goal=COST_OPTIMIZED) for jobs that can start within a delay of up to 6 hours; autoscaling; right machine types |
| Dataflow streaming | Streaming Engine; a sensible maximum number of workers; avoid hot keys |
| Dataproc | Job-based clusters; autoscaling policies; Spot secondary workers; idle deletion |
| Cloud Storage | Storage classes and lifecycle rules matched to access |
"Nightly batch, results needed by morning, minimise cost" points to FlexRS for Dataflow or Spot secondary workers for Dataproc. Neither suits a job with a hard start time.
Ensuring resources for business-critical processes
| Risk | Protection |
|---|---|
| Ad hoc queries starve the nightly finance load | Separate BigQuery reservations, with baseline slots for the critical workload |
| Spot VMs reclaimed during a deadline job | Run the critical path on standard primary workers; use Spot only for extra capacity |
| Quotas hit during month-end peaks | Request quota increases ahead of time; monitor quota usage |
| Shared clusters contended by many teams | Separate job-based clusters per workload |
Persistent or job-based Dataproc clusters
| Job-based (ephemeral) | Persistent | |
|---|---|---|
| Lifetime | Created for a job or workflow, deleted after | Always running |
| Cost | Pay only while jobs run | Pay even when idle |
| Data | Must live in Cloud Storage or BigQuery | Can use HDFS, but Cloud Storage is still preferred |
| Fits | Scheduled batch jobs, per-team isolation | Interactive notebooks, continuous shared use, low start latency |
| Tools | Workflow templates with a managed cluster, Composer operators, Dataproc Serverless | Autoscaling policy, scheduled idle deletion as a safety net |
gcloud dataproc clusters create nightly-etl \
--region=europe-west1 \
--num-workers=2 \
--num-secondary-workers=8 --secondary-worker-type=spot \
--max-idle=30m--max-idle deletes the cluster after 30 minutes without jobs, so a forgotten cluster cannot run up a bill. Dataproc Serverless for Spark removes the cluster decision altogether for batch Spark jobs.
A worked example
A company runs one large persistent Dataproc cluster 24 hours a day, used for six hours of nightly jobs. Its BigQuery on-demand bill also spikes when analysts run SELECT * on a 40 TB table.
The engineer moves the nightly jobs to a workflow template that creates a cluster with Spot secondary workers and deletes it afterwards. For BigQuery, the table is partitioned by date and clustered, a custom quota caps each user's daily bytes, and dashboards use a materialized view.
How the exam asks about this
- "A Spark cluster sits idle 18 hours a day." Use job-based clusters created per workflow and deleted afterwards, keeping data in Cloud Storage.
- "Prevent any single query from accidentally costing more than a set amount." Set maximum bytes billed on the query or a custom quota.
- "A nightly Dataflow batch job is flexible on start time; reduce cost." Use FlexRS.
What to remember for the exam
- Partition, cluster, select fewer columns and dry-run to cut BigQuery cost.
- Maximum bytes billed and custom quotas cap query spending.
- FlexRS for delay-tolerant Dataflow batch; Spot secondary workers for Dataproc.
- Protect critical work with dedicated reservations and standard workers.
- Job-based Dataproc clusters for scheduled batch; persistent for interactive or constant use.
LIMIT on a query does not reduce bytes billed on a non-clustered table. Use table preview to look at data, and column selection and partition filters to cut cost.
Common mistakes
Running a persistent cluster for nightly jobs. You pay for idle hours; use job-based clusters.
Putting deadline-critical work entirely on Spot VMs. Reclaimed VMs delay the job; keep a standard core.
Buying capacity before fixing queries. Pruning and column selection often remove the need.
Check yourself
What does a BigQuery dry run tell you?
The number of bytes the query would process, without running it or incurring charges.
When is a persistent Dataproc cluster the better choice?
When it is used constantly or interactively, for example shared notebooks, where startup time matters and idle time is small.
What does Dataflow FlexRS trade for lower cost?
Start-time flexibility: the batch job may be delayed by up to 6 hours and runs on a mix of standard and preemptible resources.
Topic 1 of 5 in Maintaining and automating data workloads
Test yourself on Maintaining and automating data workloads
The full Google Cloud Professional Data Engineer question bank has 733 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Google Cloud. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.