Managing BigQuery Workloads and Dataflow Jobs

Optimizing query performance and cost in BigQuery (opens in a new tab) means scanning less data and using less compute while keeping many concurrent queries reliable. BigQuery separates storage from compute and memory, so performance work focuses on the execution plan and slot consumption focuses on cost. Slots are virtual units of computational capacity that run queries and connect to remote distributed memory over a high-bandwidth network.

1.1How BigQuery Executes Queries in Stages

BigQuery query execution breaks each query into parallel stages that move through shared infrastructure before results are saved. When a query starts, BigQuery builds an execution tree that divides the work into stages, and each stage contains steps that run in parallel across workers. Stages exchange intermediate results through a fast distributed shuffle tier that stores worker output in RAM and moves it quickly to the next workers. BigQuery can adjust the plan dynamically as stage results arrive, and additional workers monitor overall progress.

When execution finishes, BigQuery writes results to persistent storage in a distributed file system and returns them to the user. Because results are persisted, BigQuery can serve cached results when the same query runs again. Storage is automatically compressed, encrypted, replicated, and distributed without using query slots, so storage can grow to dozens of petabytes independently from compute.

1.2Analyzing Query Plans and Slot Consumption

The query plan is the main tool for finding why a query is slow or expensive. The query plan describes stages and steps and can be viewed in the Google Cloud console as a timeline and as an execution graph with performance insights for running or completed queries. Execution details can also be read programmatically with INFORMATION_SCHEMA.JOBS views or the jobs.get REST API method. If one stage writes much more output than the other stages, that pattern signals that filtering should move earlier to reduce shuffled and scanned data.

Slot consumption shows how much computational capacity the stages required during execution. Google manages the machines behind each slot, so the main task is choosing enough slots through reservations and assigning projects, folders, or organizations to those reservations. Because compute, memory, and storage are separate, adding storage does not require adding compute, but many concurrent queries must share the available slots.

1.3Managing Concurrency with Interactive, Batch, and Reserved Queries

Query concurrency management in BigQuery keeps performance stable when many queries run at the same time. BigQuery runs many queries in parallel and can queue queries until resources are free. As queries start and finish, BigQuery redistributes resources fairly between new and running queries, so performance depends more on the number of concurrent queries than on submission order. Repeated queries on the same data can still vary because of shared resources, cached results, and dynamic plan changes.

Workload behavior is controlled through three options:

• Interactive queries execute as soon as possible and are the default setting.
• Batch queries are queued and start when idle resources are available in the shared pool.
• Slot reservations provide dedicated capacity through capacity-based pricing, where buckets of slots are assigned to projects, folders, or organizations.

Choose interactive queries when low latency matters most, batch queries when the work can wait for idle shared capacity, and slot reservations when the workload needs predictable throughput and isolation from shared-pool contention.

1.4Controlling Cost with Pricing Models and Monitoring

Query pricing in BigQuery uses two models that trade pay-per-use flexibility against dedicated capacity. On-demand pricing charges for bytes scanned by each query with fixed per-project capacity, while capacity-based pricing purchases dedicated slots through reservations. Project-level quotas limit running queries, and custom quotas plus billing alerts add cost control. Failed load, copy, and query jobs that write to a destination still count toward quotas, so repeated retries increase cost and quota pressure.

Ongoing monitoring connects plan behavior to cost decisions through usage metrics, audit logs, and metadata views. These signals help identify expensive scans, queueing, and slot contention. Use them to decide whether to rewrite a query to scan less, move work to batch, or buy reserved slots.

Configuring and monitoring data processing jobs in Google Cloud means setting correct startup parameters, watching health and cost during execution, and diagnosing failures from metrics and logs. Dataflow (opens in a new tab) provides a job monitoring interface and job metrics dashboards, Cloud Monitoring and Cloud Logging provide dashboards and alerts, and Managed Service for Apache Spark provides CLI, API, and console signals for Spark jobs.

2.1Monitoring Dataflow Jobs in the Console

The Dataflow monitoring interface is the console surface for viewing and interacting with Dataflow jobs. The jobs list shows running jobs and jobs from the last 30 days with status, region, and elapsed time. Each job view provides a job graph, execution details, metrics, cost, logs, and recommendations. Data flows from pipeline code and staged artifacts to Dataflow workers and then to sources and sinks, and the interface exposes lag and latency at each step so bottlenecks can be traced to a specific stage, source, or sink.

The interface uses complementary views at different scopes. The project dashboard monitors jobs at the project level, the jobs list covers recent and running jobs, and the job graph shows the pipeline graph with per-step progress. Execution details show execution stages, data freshness for streaming jobs, and worker progress for batch jobs. Additional views show job metrics (opens in a new tab) over time, estimated cost from resource usage, autoscaling behavior for streaming jobs, turnkey alerts for backlog and resource use, pipeline logs, sampled data at each step, and recommendations for performance and cost. When job data is briefly unavailable, monitoring charts show gaps rather than zero values.

2.2Understanding Job Metrics and Autoscaling Behavior

Dataflow job metrics are time-series charts on the Job metrics tab that help debug errors and improve performance. To view job metrics, first open Dataflow Jobs in the console, then select a job, then open the Job metrics tab and select a metric, and optionally select Explore data for deeper analysis in Metrics Explorer. Metrics are grouped into autoscaling, overview, streaming, resource, and input/output dashboards.

The Autoscaling dashboard plots the actual number of workers against the target number of workers over time, and for jobs that use Streaming Engine it also shows minimum and maximum workers. Overview metrics summarize data freshness, system latency, throughput, and worker errors. Streaming metrics expose backlog size, processing progress, parallelism, persistence operations, duplicate data, and timer behavior. Resource metrics track worker CPU and memory utilization, while input and output metrics track requests per second and response errors by error type for connectors. Some charts apply only to streaming pipelines, and reserved CPU time appears only after a job completes, so unbounded streaming jobs do not show reserved CPU until they are cancelled or fail.

2.3Alerting with Cloud Monitoring and Beam Custom Metrics

Cloud Monitoring extends Dataflow monitoring with dashboards, Metrics Explorer queries, and alerts on conditions such as long streaming lag or failed jobs. Turnkey alerts are preconfigured policies for streaming backlog and resource utilization, while custom alerts can be built from any Dataflow metric. The worker service account needs permission to write metrics, and some metrics are unavailable when a VPC Service Controls perimeter blocks the Monitoring API for workers outside the perimeter.

Any metric defined in an Apache Beam pipeline is reported to Monitoring as a custom metric. Beam defines Counter, Distribution, and Gauge metrics, but Dataflow reports only Counter and Distribution to Monitoring. A Distribution appears as four submetrics suffixed with _MAX, _MIN, _MEAN, and _COUNT, and Dataflow does not build a histogram from a Distribution. Updates arrive about every 30 seconds, all values are exported as double type with GAUGE kind, so a rate must be computed as a delta over a time window. Custom metrics appear as dataflow.googleapis.com/job/user_counter with metric_name and ptransform labels, with backward compatibility under custom.googleapis.com/dataflow/. Each project is limited to 100 Dataflow custom metrics in that namespace, and custom metrics are subject to cardinality limits and Monitoring charges.

2.4Troubleshooting Slow or Failed Dataflow Pipelines

Troubleshooting a Dataflow pipeline follows an ordered path from status to logs to root-cause stage. First check the pipeline status in the monitoring interface, then inspect job error messages and step logs, then determine whether the failure happened during graph construction, job validation, or worker execution, and then fix slow or stalled steps using lag signals. To check status, first select the project in the console, then select Dataflow to list jobs, then select the target job and read the Status field. The job lifecycle moves through Running, Succeeded, or Failed, with detailed history in the job view.

To find failure information, first open Job logs in the Logs panel, then filter by severity to show Error messages, then open step-level and worker logs for the failed step. Job logs contain messages from pipeline code and the Dataflow service, while worker logs and diagnostics isolate exceptions on specific workers. This separation matters because one failed step can create many downstream messages, so the earliest error in the failed step usually identifies the cause.

Slow pipelines or missing output need a lag-focused path rather than an error-focused path. The job graph and execution details show which steps accumulate lag, freshness and system-lag charts show whether sources or sinks are delayed, and autoscaling charts show whether worker count limits throughput. Data sampling then shows sampled records at each step to confirm whether data is blocked, malformed, or filtered unexpectedly.

2.5Configuring Execution Storage and Snapshots for Dataflow

Dataflow job execution depends on Cloud Storage staging and temporary locations that must be configured as separate buckets. Staged files store artifacts created at job start, such as pipeline code, and those artifacts can be reused during the job lifetime and across relaunches when a separate staging bucket is used. Temporary files hold intermediate job data and are not cleaned up automatically, so both buckets need lifecycle management.

For completed batch or streaming jobs, staged files can be deleted after the job finishes, but staged files for in-progress jobs must not be deleted even after a pipeline update. To control cost, configure a time to live (TTL) lifecycle policy on both buckets and disable soft delete on temporary and staging buckets. A practical starting point is a 7-day TTL for the temporary bucket, slightly longer than the longest-running job, and a 6-month TTL for the staging bucket so artifacts can be reused and startup stays fast.

A Dataflow snapshot saves the state of a running streaming pipeline so a new version can start without losing state. Snapshots support backup and recovery, testing and rollback of streaming updates, and migration to Streaming Engine. Any new job created from a snapshot uses Streaming Engine, so snapshots provide a low-downtime path to the more scalable streaming runtime.

2.6Monitoring Managed Service for Apache Spark Jobs

Job monitoring for Managed Service for Apache Spark uses the Google Cloud CLI, REST API, console, and Cloud Logging together. To check a running job with the CLI, run gcloud dataproc jobs describe with the job ID and region, and follow the job-output procedure to view driver output. The REST jobs.get method returns authoritative state fields, including JobStatus.State, JobStatus.Substate, JobStatus.details, and YarnApplication fields that show YARN application progress behind the Dataproc job.

In the console, driver output appears in the job-output view, while agent behavior appears in Logging under Managed Service for Apache Spark Cluster by cluster name and cluster UUID in google.dataproc.agent logs. Failed jobs are investigated through the job-logs procedure in Logging. Ownership appears in the submittedBy field, such as user@domain, because console display of the submitter is not fully supported. Disk exhaustion is a common Spark failure because Spark writes HDFS and scratch data to disk. The primary disk size appears under the Configuration tab on the Cluster details page, with recommended minimums of 1000 GB for n1-standard-4 machines and 2 TB for n1-standard-32 machines. If size is below the recommendation, recreate the cluster with at least the recommended size; if size meets the recommendation, connect by SSH to the master VM, run df -h, and check disk metrics to decide whether more space is needed.

2.7Diagnosing Slot and Reservation Bottlenecks for BigQuery Jobs

BigQuery jobs that share reservations can run slowly because of concurrency and slot exhaustion rather than query logic. Higher average concurrency during a slow run than during a fast run is evidence that contention contributed to the slowdown. Admin resource charts and BigQuery Cloud Monitoring show reservation utilization, and jobs in reservations with multiple assigned projects can receive different slot assignments at the same concurrency because of fair scheduling across projects.

The estimatedRunnableUnits field from the Job API, exposed as period_estimated_runnable_units in the INFORMATION_SCHEMA.JOBS_TIMELINE view, indicates whether more slots would have helped. When the value is greater than zero at a point in time, the job could have used more slots then. The constrained share can be estimated by dividing timeline periods with nonzero runnable units by total periods for the job ID, which yields a percentage such as 96.7 percent. A low percentage means slots were not a major factor, while a high percentage with a fully utilized reservation means the job was resource-constrained. When constraint is confirmed, spread intensive queries over time within or across reservations, allow use of idle slots, or increase reservation size. If the constrained percentage is high but the reservation was not fully utilized, further investigation by Cloud Customer Care is warranted.

Managing compute capacity and reservations in BigQuery (opens in a new tab) means controlling the slots that execute jobs and choosing how those slots are billed and allocated. A BigQuery slot is a virtual compute unit used to run SQL queries and other job types, and BigQuery automatically decides how many slots each query uses based on data size, query complexity, and available slots. Capacity strategy centers on on-demand billing, capacity-based billing, reservations, commitments, and autoscaling, which trade flexibility for predictability and cost control.

3.1Slots and the Jobs That Consume Capacity

BigQuery slots are the virtual compute units that power jobs that load, export, query, or copy data. When a user starts one of these tasks from the Google Cloud console, the bq command-line tool, a SQL statement, or an API call, BigQuery creates, schedules, and runs a job that tracks the task. Jobs run asynchronously and can be polled for status, while short actions such as listing resources or getting metadata are not managed as jobs. During execution, slots flow from the available pool to the running job, so the slots assigned to a project or reservation directly limit throughput and performance for concurrent queries.

3.2Choosing Between On-Demand and Capacity-Based Billing

Workload management models define how slots are provided and metered, and projects can switch models at any time or combine them across projects. With on-demand billing, queries use a shared pool of slots and billing is based on bytes processed after monthly free usage is consumed. With capacity-based billing, slots are allocated to the organization through autoscaling reservations and optional capacity commitments tied to a BigQuery edition, with each edition offering its own price point and feature set. These models apply per project, so one project can use on-demand billing while another uses capacity-based billing under centralized purchasing.

On-demand offers maximum flexibility with variable usage and a fixed feature set, while capacity-based offers more predictable billing through baselines and commitments plus centralized allocation. Throughput under on-demand is limited to a predefined shared slot quota per project, so steady high-volume workloads can gain more isolation and predictability from dedicated capacity.

3.3Creating Slot Reservations and Assignments

Slot reservations are pools of capacity-based slots that keep workloads from competing for the same compute. An administrator first creates a reservation in a region, then creates a reservation assignment that routes a project, folder, or organization to that reservation, and only after assignment do jobs from those projects consume slots from that pool. For example, a reservation named prod can serve production workloads while a separate reservation named test serves testing, so test jobs do not consume production capacity. Reservations can also separate departments or environments, which makes ownership, chargeback, and performance isolation explicit because each assignment maps a set of projects to a defined slot budget.

3.4Balancing Baselines with Commitments and Autoscaling

Capacity commitments and autoscaling control how much baseline capacity is guaranteed and how much can burst for peaks. A commitment is a longer-term purchase of slots for steady-state workloads, available as monthly or annual options, and commitments cost less than purely autoscaled capacity. An autoscaling reservation combines a baseline number of slots that are always available with additional autoscaled slots that BigQuery adds when demand rises, which preserves flexibility for variable patterns but bills for the extra slot-hours used. Opting in to BigQuery fluid scaling removes the default 1-minute minimum duration, so very short bursts do not incur the same minimum charge and the tradeoff shifts toward finer-grained elasticity versus predictable baseline cost.

3.5Sizing and Adjusting Capacity Over Time

Capacity estimation and monitoring connect historical workload patterns to reservation sizing and ongoing reassessment. The Slot estimator in Preview estimates organization capacity needs from historical performance metrics, which helps translate past slot usage, query complexity, and peak concurrency into a baseline plus autoscaling headroom. The Workload management page in the console shows capacity commitments and where slot reservations are assigned, while the Cloud Monitoring dashboard shows incidents, datasets, tables, projects, query times, and slot utilization for BigQuery. When quotas or limits constrain growth, administrators can view BigQuery quota usage in the console and request a higher quota, then resize reservations, adjust assignments, or move projects between on-demand and capacity-based models to rebalance cost, flexibility, and performance.

Key Facts

• BigQuery divides each query into an execution tree of parallel stages that exchange data through a shuffle tier, then writes results to persistent distributed storage.
• On-demand BigQuery pricing bills for bytes scanned with up to 2,000 shared slots per project, while capacity-based pricing bills for slot-hours with a minimum of 50 slots per reservation.
• A BigQuery slot reservation must be created in a region and then assigned to a project, folder, or organization before jobs consume slots from that pool.
• Dataflow job metrics distinguish missing data as chart gaps rather than zero values, and reserved CPU time appears only after a job completes.
• A Dataflow snapshot saves the state of a running streaming pipeline so a new job version can start without losing state and uses Streaming Engine.