Maintaining and automating data workloads

Optimising resources and cost

Principles

• Meet the business need at the lowest cost: match capacity to the deadline and criticality of each workload. Not everything needs real time, premium machines or dedicated capacity.
• Right-size and schedule: shut down idle resources, use ephemeral compute, autoscale, and run non-urgent batch work in cheap or idle capacity.
• Use budgets and alerts (Cloud Billing), billing export to BigQuery with labels, labels on jobs, datasets and clusters for chargeback, and Recommender for idle or oversized resources.
• Committed use discounts (CUDs) for steady Compute Engine and Dataproc VM usage; slot commitments for BigQuery; Spot VMs for fault-tolerant workers.

Cost levers by service

Persistent vs job-based Dataproc clusters

• Cut start time with custom images and initialization actions baked in. A cluster pool or persistent small cluster plus autoscaling can serve many short jobs. Serverless for Apache Spark removes the choice altogether.

Ensuring capacity for critical work

• BigQuery: dedicate a reservation (baseline slots) to business-critical workloads so they aren’t starved by ad hoc queries; give lower-priority workloads an autoscaling or idle-slot reservation; use assignments at project, folder or organisation level.
• Dataflow and Dataproc: set max workers high enough, check quotas (CPUs, IP addresses, persistent disks) in the region ahead of time, and request increases; reservations of Compute Engine capacity guarantee VMs; avoid Spot for jobs that must finish.
• Pub/Sub, Bigtable, Spanner: provision nodes or autoscale with headroom; avoid single-key hotspots.

Automation and repeatability

Cloud Composer DAGs

• A DAG is Python that defines tasks (operators), dependencies (>>), schedule (schedule/cron), start_date, catchup, default_args (retries, retry_delay, email_on_failure), and optionally max_active_runs, SLA or timeouts.
• Operators for Google services: BigQueryInsertJobOperator, DataflowTemplatedJobStartOperator/BeamRunPythonPipelineOperator, DataprocCreateClusterOperator, DataprocSubmitJobOperator, DataprocDeleteClusterOperator (with trigger_rule='all_done' so the cluster is deleted even if the job fails), GCSToBigQueryOperator, CloudDataFusionStartPipelineOperator, DataformCreateWorkflowInvocationOperator, sensors (GCSObjectExistenceSensor, BigQueryTableExistenceSensor) and deferrable versions.
• Design for repeatability: idempotent tasks (overwrite a partition or use MERGE, not blind append), use logical date ({{ ds }}) rather than now(), keep task logic in the target service (Composer orchestrates, it doesn’t process data on the workers), small tasks, retries with exponential backoff, catchup/backfill for history, Variables, Connections, Secret Manager backend for configuration, task groups, XCom only for small metadata, dynamic task mapping, ExternalTaskSensor or datasets for cross-DAG dependencies.
• Composer environment: managed on GKE; DAGs, plugins and data in a Cloud Storage bucket; scale workers with autoscaling; private IP; snapshots for DR and upgrades; Airflow metadata database managed; use Cloud Logging for task logs and Cloud Monitoring metrics (dag_processing, scheduler heartbeat, task failures).
• Alternatives for simple cases: Workflows (HTTP steps with retries), Cloud Scheduler (cron triggers), BigQuery scheduled queries and Dataform workflow configs (SQL only), Dataflow templates scheduled by Scheduler, Eventarc triggers for event-driven runs.
• Repeatable deployment: Terraform for the infrastructure, CI/CD for DAG and pipeline code, parameterised environments, no manual console changes.

Organising workloads: capacity and BigQuery editions

Editions and slots

• On-demand (per TiB scanned; project has a shared pool, default about 2,000 slots) vs capacity (editions: per slot-hour, billed for slots allocated or autoscaled). Choose on-demand for spiky, low volume or exploratory use; capacity for predictable, heavy or business-critical workloads where cost predictability and guaranteed capacity matter.
• A reservation is a named pool of slots in a location and edition. Settings: baseline slots (always available, billed continuously), autoscale max (extra slots billed only while used, scaling in increments of 50), idle slot sharing across reservations in the same admin project. Commitments (monthly via pay as you go, annual, three-year) discount baseline slots.
• Assignments attach a reservation to a project, folder or organisation, per job type: QUERY, PIPELINE (load, export, copy), ML_EXTERNAL, BACKGROUND (such as search index and clustering maintenance), or CONTINUOUS. Load jobs run in the shared free pool unless you assign PIPELINE jobs to a reservation. A NONE assignment excludes a project from capacity and sends it back to on-demand-style billing (and NONE also stops usage).
• Admin project: centralise reservations in a project that holds only slot commitments (no data); give different reservations for prod, dev, ETL and BI; use the slot estimator and INFORMATION_SCHEMA.JOBS_TIMELINE/RESERVATIONS to size.
• Workload isolation: put interactive dashboards in one reservation, ETL in another, ad hoc in an autoscaling one; so a runaway query cannot starve critical work.

Interactive vs batch

• Interactive jobs (default) start immediately and count toward concurrency; batch priority queues the query until idle slots exist (the job starts within up to 24 hours otherwise it fails), doesn’t count toward concurrent-query quotas and runs in on-demand pools or idle slots. Use batch for non-urgent ETL and overnight reports.
• Job concurrency/query queue: dynamic queue lengths manage concurrent interactive queries; set max concurrency for reservations.
• Other job types: load, extract (export), copy, continuous queries (streaming SQL). Load, copy and export jobs are free on the shared pool.

Monitoring and troubleshooting

Observability

• Cloud Monitoring metrics and dashboards; alerting policies with notification channels; Cloud Logging (including log-based metrics, sinks to BigQuery or Pub/Sub); Error Reporting; Cloud Trace for APIs; Audit logs (Admin Activity always on, Data Access configurable).
• BigQuery: administrative resource charts and BigQuery Admin panel (slot usage, job concurrency, shuffle, jobs), INFORMATION_SCHEMA.JOBS, JOBS_BY_PROJECT/USER/ORGANIZATION, JOBS_TIMELINE, RESERVATIONS_TIMELINE, STREAMING_TIMELINE, TABLE_STORAGE, COLUMN_FIELD_PATHS; the query plan and execution graph; BigQuery audit logs (BigQueryAuditMetadata) for who ran what; Data Transfer run history.
• Dataflow: job graph, system lag, data freshness, watermark age, throughput, worker CPU and memory, autoscaling chart, Dataflow monitoring interface, logs per worker, Error and Diagnostics tab, profiling (Cloud Profiler). Alert on system lag and job state.
• Pub/Sub: subscription/num_undelivered_messages, oldest_unacked_message_age (alert when messages are stuck), ack latency, dead-letter volume.
• Composer/Airflow: task failures, DAG run duration, scheduler heartbeat, queued tasks, worker utilisation, SLA misses.
• Dataproc: YARN metrics, job logs, Spark history server through Component Gateway, cluster utilisation.
• Data quality: Dataplex scans, row-count and freshness checks, lineage for impact analysis.
• Monitor planned usage: compare actual vs forecast with budgets, quota dashboards (Quotas & System limits page) and alerts on quota usage.

Troubleshooting errors, billing and quotas

• Quotas are per project (and some per user); request increases ahead of launch. Rate limits are different from quotas: back off and retry.

Managing workloads

• Manage jobs (list, cancel, labels) with bq, the API, or the console; track long-running queries; cancel runaway jobs; label jobs for chargeback; use reservation assignments to steer compute; resize reservations for seasonal peaks (autoscale or scheduled commitments); use workload management policies and query queues.

Failures, fault tolerance and recovery

• Fault tolerance and restarts: design idempotent, restartable stages; checkpoint state (Dataflow streaming checkpoints automatically; Spark structured streaming needs a Cloud Storage checkpoint directory); persist progress outside workers; use retries with exponential backoff and jitter; dead-letter queues to isolate poison data; drain streaming jobs before maintenance; update jobs in place when the pipeline is compatible; keep Pub/Sub retention to replay after outage.
• Multi-region and multi-zone: Dataflow regional endpoints place workers across zones automatically within a region; run a second job in another region for DR of streaming (read from the same multi-region or replicated source, write to a replicated sink) and use Pub/Sub snapshot/seek to align; Dataproc clusters are zonal, so use auto-zone placement and recreate clusters in another zone (cheap with ephemeral clusters); Composer snapshots restore environments in another region.
• Data corruption and missing data: BigQuery time travel (FOR SYSTEM_TIME AS OF, 7 days), fail-safe (7 more days), table snapshots, cross-region replicas, Cloud Storage versioning/soft delete/object holds, Cloud SQL PITR and backups, Bigtable backups and managed backups with retention, Spanner backups and PITR (up to 7 days), AlloyDB continuous backup; reprocess from raw immutable data (keep raw in Cloud Storage); validate with row counts, checksums and Dataplex quality scans; late/missing data handled with watermarks, allowed lateness, reconciliation jobs and backfills in Composer (airflow dags backfill, catchup).
• Replication and failover:

• Test the plan: run failover drills, game days; document RTO and RPO; monitor replication lag (Cloud SQL replica lag, Spanner and Bigtable replication latency).