Data pipeline orchestration

Choosing a transformation tool

Short rules:

• Data in BigQuery and the transform is SQL: Dataform or a scheduled query.
• Real-time or very large pipelines: Dataflow.
• Spark code already exists: Dataproc.
• No-code visual: Data Fusion.
• Coordinate several jobs in order: Cloud Composer (heavy) or Workflows (light).

Dataflow vs Dataproc is a favourite question: Dataflow is serverless and Beam-based; Dataproc is cluster-based and for Spark/Hadoop ecosystem jobs you already have.

ELT vs ETL use cases

• ELT: raw data lands in BigQuery first, transformations are SQL (Dataform, scheduled queries). Fits analytics where the warehouse is powerful and cheap, and where you want raw history kept.
• ETL: transform first with Dataflow, Data Fusion or Dataproc. Fits streaming, masking before storage, sources that need parsing, or a destination that isn’t a SQL engine.
• A common design: Pub/Sub, Dataflow (light cleaning), BigQuery raw table, Dataform models into reporting tables. This is ETLT.

Basic pipeline building blocks

Scheduling and automating

Scheduled queries

BigQuery scheduled queries run SQL on a repeating schedule through the BigQuery Data Transfer Service. You choose frequency (hourly, daily, custom cron-like), a destination table with append or overwrite, and a service account to run as. Use them for simple recurring SQL and summary tables. They don’t support complex dependencies between jobs.

Cloud Scheduler

A fully managed cron service. It triggers an HTTP endpoint, a Pub/Sub topic or an App Engine target on a schedule. Use it to start a Cloud Run job, call a Workflow, publish a Pub/Sub message that starts a pipeline, or launch a Dataflow template through its REST API.

Cloud Composer

Managed Airflow. You write DAGs in Python made of tasks (operators) with dependencies, retries, schedules, sensors and alerting. There are operators for BigQuery, Dataflow, Dataproc, Cloud Storage, Dataform and many others. Pick it for pipelines with many dependent steps or cross-system orchestration. It has a cost floor because an environment runs continuously (Composer 3 reduces management overhead), so it is overkill for one scheduled query.

Workflows

A serverless orchestrator that calls Google Cloud APIs and HTTP services in a YAML or JSON defined sequence, with conditions, loops, error handling and retries. Pick it for lightweight orchestration with no environment to run; trigger it from Cloud Scheduler or Eventarc.

Dataproc Workflow Templates

A reusable definition of a graph of Dataproc jobs that runs on an ephemeral or existing cluster and deletes the cluster when done. Use it to orchestrate only Dataproc jobs without Composer.

Choosing an orchestrator

Event-driven pipelines

Pub/Sub to BigQuery

Pub/Sub is the asynchronous messaging service that decouples producers and consumers, with at-least-once delivery by default and retention up to 31 days on topics. Ways to get messages into BigQuery:

• BigQuery subscription: a subscription type that writes messages directly to a table with no code, using the topic schema or raw data columns. Best when no transformation is needed.
• Dataflow with the Pub/Sub to BigQuery template (or your own Beam code): when you need parsing, enrichment, windowing or deduplication.
• Use a dead-letter topic for messages that repeatedly fail.

Use event-driven ingestion when data should be available within seconds, when producers emit events continuously (clickstream, IoT, logs), or when many consumers need the same stream.

Eventarc

Eventarc routes events from Google Cloud sources (Cloud Storage object finalised, audit-log events, Pub/Sub messages) and third parties to targets: Cloud Run, Cloud Run functions (Cloud Functions), Workflows, GKE. For data pipelines:

• File lands in Cloud Storage, Eventarc triggers a Cloud Run function that loads it to BigQuery or starts a Dataflow template.
• A BigQuery job completes (through audit logs), Eventarc triggers a Workflow that starts the next step.
• Triggering Dataform or Cloud Composer runs is done by an Eventarc-triggered function or workflow calling their APIs.

Event-driven vs scheduled: events react immediately and process only when data exists; schedules are simpler and predictable but can run with nothing to do or add delay.

Monitoring and troubleshooting

• Dataflow job UI (job graph and job metrics): see each stage, its throughput, system lag, data freshness, worker count and autoscaling, and per-step logs. Use it to spot slow stages, hot keys and failed steps.
• Cloud Logging: central logs; filter by resource type, severity and text; create log-based metrics and sinks that export to BigQuery, Cloud Storage or Pub/Sub.
• Cloud Monitoring: metrics, dashboards and alerting policies with notification channels (email, Slack, PagerDuty, Pub/Sub). Alert on failed jobs, high system lag, Pub/Sub oldest unacked message age, and Composer task failures.
• Error handling: retries with backoff in Composer and Workflows, dead-letter queues in Pub/Sub and Dataflow, and idempotent writes so retries don’t duplicate rows.
• For BigQuery: job history, INFORMATION_SCHEMA.JOBS views for slot use and bytes billed, and scheduled query run history.
• Data lineage (Dataplex) shows where data came from and what depends on it, helping with impact analysis.