Google PDE: Workflow Orchestration and Pipeline Automation — Study Guide

By Laurynas Brazys · 21 August 2026 · #google #PDE #certification #exam-prep #study-guide

Part of the Google Professional Data Engineer — Study Guide. Practice with verified answers in the Google exam hub, or take timed practice tests on ExamRoll.io.

Overview

Workflow orchestration and pipeline automation coordinate data tasks across services so that ingestion, transformation, quality checks, and publishing happen reliably, securely, and cost‑effectively. In Google Cloud, orchestration must align with the execution model of each workload: scheduled batch, event‑driven stream, ad‑hoc, or long‑running jobs. The design goals are repeatability, idempotency, observability, least privilege, and safe promotion through environments.

Key choices:

• Code‑centric batch orchestration with Cloud Composer (Apache Airflow) for DAGs, task dependencies, and advanced scheduling.
• Serverless API choreography with Cloud Workflows for lightweight, event‑driven, cross‑service sequences.
• Execution endpoints such as Cloud Run jobs or Dataproc jobs, triggered by Cloud Scheduler for cron or Eventarc for events.
• SQL‑native orchestration with Dataform for BigQuery transformations, assertions, and release management.

The operating model emphasizes retries with bounded exponential backoff, timeouts, SLAs, catchup and backfills, idempotent task design for safe reruns, and robust failure handling with dead‑letter capture. Security is enforced via per‑pipeline service accounts, secrets isolation, parameterization, and least‑privilege IAM. CI/CD, infrastructure as code, and comprehensive telemetry complete a production‑ready approach.

Orchestration on Google Cloud: Tools and Patterns

Cloud Composer (Airflow)

• DAGs define directed acyclic execution graphs with explicit dependencies. Use TaskFlow API or operators (e.g., BigQuery, Dataflow, Dataproc, Cloud Run) to express tasks. Sensors and deferrable operators reduce scheduler load for wait conditions (e.g., object finalize in Cloud Storage or a partition appearing in BigQuery).

• Scheduling: cron expressions, start_date, end_date, and catchup control historical runs. Use catchup for backfills; disable for streaming‑adjacent or non‑idempotent targets. Limit concurrency with max_active_runs and pools to protect downstream systems.

• Dependencies: set_upstream/set_downstream or taskflow dependencies. For metadata‑driven orchestration, generate tasks dynamically from a BigQuery control table (e.g., list of clients/partitions) using dynamic task mapping, keeping DAG parse time stable and tasks data‑driven.

• Example (concise) DAG fragment: from airflow import DAG from datetime import datetime, timedelta from airflow.providers.google.cloud.operators.dataflow import DataflowStartFlexTemplateOperator

  default_args = dict(retries=3, retry_delay=timedelta(minutes=5), execution_timeout=timedelta(hours=2), sla=timedelta(hours=3)) with DAG(‘daily_csv_import’, start_date=datetime(2023,1,1), schedule_interval=‘0 2 * * *’, catchup=True, max_active_runs=1, default_args=default_args) as dag: import_job = DataflowStartFlexTemplateOperator( task_id=‘import’, body={’launchParameter’: {‘jobName’: ‘csv-import-{{ ds_nodash }}’, ‘parameters’: {‘dlq_table’: ‘bqproj.dlq.bad_rows’}}} )

Cloud Workflows, Cloud Scheduler, Cloud Run jobs, and event‑driven execution

• Cloud Workflows orchestrates Google APIs and HTTP endpoints with built‑in retries, loops, parallel branches, and compensation logic. It is ideal for light control flow across services such as BigQuery, Dataflow, Batch, and Cloud Run jobs.
• Cloud Scheduler triggers Workflows, Pub/Sub topics, or HTTP services for cron‑style automation. For daily batch at 02:00, schedule a Workflow that launches a Dataflow job or Dataproc job.
• Cloud Run jobs execute containerized batch steps with automatic retry and minimal ops. They pair well with Workflows for multi‑step data tasks or pre/post‑processing around Dataflow or BigQuery.
• Event‑driven: use Eventarc to route Cloud Storage object finalize, Pub/Sub messages, or Audit Logs to Cloud Run or Workflows. For BigQuery insert‑job notifications on a single table, create a Cloud Logging sink with an advanced filter to Pub/Sub, then trigger your consumer from that topic.

Dataform: SQL workflows for BigQuery

• Model dependency graphs with ref(), define tables/views/incrementals, and orchestrate builds by tags or schedules. Dataform compiles SQLX into ordered execution plans, enabling metadata‑driven orchestration from declarative definitions.
• Assertions ensure data quality. An assertion is a query that must return zero rows to pass. Example assertion: – definitions/assert_non_negative_prices.sqlx config { type: “assertion” } SELECT 1 FROM ${ref(‘prices_daily’)} WHERE price < 0 LIMIT 1
• Releases and repository controls: store code in a repository, use branches and reviews, and promote tagged releases to environments (e.g., dev, test, prod) with environment‑specific variables. Gate deploys via CI/CD checks and assertion results.

Dataproc, Dataflow, and storage patterns

• For Hadoop/Spark reuse with minimal ops, use Dataproc with the GCS connector to persist data beyond cluster lifetime and minimize persistent disk cost. Create ephemeral clusters per job for isolation and cost control; orchestrate with Composer or Workflows.
• For batch ingest with malformed rows, run Dataflow to write valid records to BigQuery and route parse/validation errors to a dead‑letter BigQuery table for inspection.

Reliability, Failure Handling, and Idempotency

Retries, timeouts, and backoff

• Use bounded exponential backoff for transient failures and cap total retry windows to the job’s SLA. For example, a frontend or task that polls a database every 15 minutes should retry with exponential backoff up to 15 minutes, then surface a controlled failure.
• Configure per‑task execution_timeout and global DAG SLAs in Airflow; in Workflows, set per‑step timeouts and retry policies with max_doublings and max_retry_duration. For Cloud Run jobs, set retry count and backoff.

Backfills, catchup, and failure handling

• Enable catchup for historical recompute when tasks are idempotent and sources are partitioned by date. For non‑deterministic outputs or external side‑effects, consider backfill‑only DAGs or write‑audit tables to track what has been produced.
• Use dead‑letter topics/tables for record‑level failures in streaming/batch transforms. For batch Dataflow, capture bad rows with error tags and aggregate error metrics; for streaming, use Pub/Sub DLQs.

Idempotent task design and reruns

• BigQuery: prefer MERGE or INSERT with de‑dupe keys; use insertId to deduplicate streaming inserts. For batch, write to a staging table then MERGE into the target inside a transactionally safe step to allow full reruns.
• Cloud Storage: use generation preconditions and deterministic object names (e.g., prefix/date/hash) so reruns overwrite safely only when expected.
• Pub/Sub and Dataflow: design for at‑least‑once delivery. Include message identifiers (e.g., Package ID, logical event timestamp) so downstream can deduplicate and reason about lateness. If business rules accept “first processed event wins” semantics, document that trade‑off and monitor for skew; otherwise, resolve winners by event time with tie‑breakers.
• Recovery from partial failure: partition outputs by run_id or date, write completion markers, and make downstream tasks depend on markers. Reprocess only partitions marked incomplete.

Troubleshooting and scalability

• When a streaming dashboard misses events but Pub/Sub shows them present, run a known fixed dataset through the Dataflow pipeline to isolate transformation defects. Validate windowing, triggers, and allowed lateness.
• Common failure mode: creating a streaming pipeline without appropriate windowing/triggers for unbounded sources or using a sharded window incorrectly can fail pipeline creation or cause state blowups.
• Scale Dataflow via max workers and autoscaling algorithm; for spikes (e.g., 50,000 installations), raise maximum workers to allow horizontal scaling during peaks.

Security, Parameterization, Environments, and CI/CD

Parameterization and configuration management

• Externalize configuration by environment. In Composer, use Variables, Connections, and environment variables; template DAG parameters by execution date or partition. In Workflows, use runtime arguments and separate workflows per environment or read config from Secret Manager.
• Use metadata‑driven orchestration by reading a control table (e.g., BigQuery config dataset) that lists clients, sources, or partitions. Generate tasks dynamically so code changes are decoupled from data‑driven changes.

Secrets, service accounts, and least privilege

• Store credentials in Secret Manager and reference them at runtime. Avoid embedding secrets in code or Airflow Variables.
• Assign a distinct service account per pipeline with the minimum IAM roles needed. For regulated BigQuery access, isolate client data into separate datasets, grant dataset‑specific roles only to approved users, and restrict BigQuery API access to approved principals. For multitenancy, create a dataset per client and bind only appropriate roles.

CI/CD and infrastructure as code

• Manage infrastructure (Composer environments, Workflows, Scheduler jobs, Pub/Sub topics, log sinks) with Terraform. Use modules to standardize project/environments, secrets, and service accounts.
• Build and test pipeline code with Cloud Build or GitHub Actions. Automate unit tests, SQL linting, Dataform dry‑runs, and Airflow DAG validation. Promote artifacts via tags; for Composer, package DAGs as deployable bundles; for Dataform, use release branches that promote after assertions pass.
• Deployment promotion: dev → test → prod via separate projects and parameterized configs. Use continuous delivery with manual approval gates and change windows for high‑risk promotions.

Observability, Alerting, and Runbooks

Telemetry and alerting

• Route all orchestration logs to Cloud Logging with structured fields (pipeline, dag_id, run_id, task_id, partition). Export error logs to Monitoring via log‑based metrics. Alert on:
  • Missed schedules or SLA misses
  • Consecutive task failures
  • Backlog growth (e.g., Pub/Sub unacked messages, Dataflow system lag)
  • Data quality assertion failures
• Cloud Composer: monitor DAG/task duration, success rate, queue depth, and scheduler health. Configure on_failure_callback for paging and remediation runbooks.
• Cloud Workflows: inspect Execution logs and step latencies; add explicit retries and error handlers; emit custom logs with correlation IDs.
• BigQuery table change notifications: create a project‑level Logging sink with an advanced filter for insert jobs targeting a specific table and export to Pub/Sub; your monitoring tool subscribes to the topic for instant alerts without noise from other tables.

Runbook design

• For each pipeline, document triggers, dependencies, SLAs, rollback/retry procedures, and safe backfill steps. Include “fixed dataset replay” for Dataflow, how to drain a streaming job, how to reprocess failed partitions, and how to remediate DLQ messages.
• Capture common failure signatures (e.g., permission denied, quota exceeded, schema mismatch) with decision trees and escalation paths.

Practical Problem Scenario

Acme Retail Analytics needs to ingest daily partner CSV drops that contain occasional malformed rows, transform and load valid data to BigQuery, and surface bad rows for investigation. They also want event‑driven enrichment for near‑real‑time pricing updates and safe promotion from dev to prod.

Approach:

1. Storage and event triggers

   • Create a dedicated Cloud Storage bucket with object versioning and uniform bucket‑level access. Enable object finalize notifications to Pub/Sub via Eventarc.
   • Rationale: Object finalization is a reliable event to trigger downstream ingest; versioning supports reruns and audits.

2. Batch ingest with dead‑letter handling

   • Use Cloud Composer to schedule a daily Airflow DAG at 02:00 with catchup enabled. The DAG launches a Dataflow batch job that parses CSVs, validates schema, and writes valid records to BigQuery using deterministic staging tables then MERGE into partitioned target tables. Route malformed/failed records to a BigQuery dead‑letter table.
   • Rationale: Dataflow scales parsing/validation; MERGE ensures idempotency; dead‑letter capture supports inspection without blocking the pipeline, matching the recommended pattern for malformed rows.

3. Event‑driven enrichment

   • Deploy a Cloud Run job to perform lightweight enrichment for incremental pricing updates. Trigger it via Cloud Workflows listening to Pub/Sub messages from Eventarc when small update files arrive during the day.
   • Rationale: Serverless containers with Workflows provide low‑latency, low‑ops orchestration for small events while keeping heavy transforms in batch.

4. Reliability controls

   • Configure retries with exponential backoff for transient failures in Dataflow and Cloud Run jobs, capping total retry time to the DAG SLA. Set per‑task execution timeouts and on_failure callbacks in Airflow; in Workflows, set max_doublings and max_retry_duration.
   • Rationale: Bounded backoff preserves SLAs and prevents runaway retries.

5. Security and least privilege

   • Run each component under a dedicated service account: Composer orchestrator SA, Dataflow worker SA, Cloud Run job SA. Grant only required roles: GCS read on the ingest bucket to Dataflow, BigQuery dataEditor on target datasets, and Viewer on logs. Store secrets in Secret Manager and reference them at runtime.
   • Rationale: Enforces least privilege and isolates blast radius.

6. Metadata‑driven orchestration

   • Maintain a BigQuery control table listing partner sources, file patterns, and target datasets. At DAG runtime, Airflow queries this table and uses dynamic task mapping to spawn per‑partner tasks.
   • Rationale: Adding a partner becomes a data change, not a code change, reducing deployment risk.

7. Observability and alerting

   • Emit structured logs with run_id and partner_id. Create alerting policies for DAG SLA misses, Dataflow system lag, and non‑empty dead‑letter counts. For BigQuery inserts into the target table, configure a Cloud Logging sink with an advanced filter for that table to a Pub/Sub topic consumed by Acme’s monitoring tool.
   • Rationale: Fine‑grained alerts enable rapid triage without noise.

8. CI/CD and promotion

   • Manage infrastructure (buckets, Pub/Sub, Eventarc, Composer, Workflows, BigQuery datasets) in Terraform. Use Cloud Build to validate Airflow DAG syntax, run unit tests, and deploy to a dev Composer environment. Promote to test and prod with parameterized configs and manual approval gates after Dataform assertions and integration tests pass.
   • Rationale: Declarative, repeatable deployments and safe promotion across environments.

9. Runbook and recovery

   • Document steps to replay a specific date: restore CSV from object versioning, rerun the Dataflow job for that partition, MERGE results, and review DLQ records. Include a “fixed dataset replay” procedure to isolate transformation bugs if discrepancies arise.
   • Rationale: Idempotent design and documented recovery streamline partial failure remediation.

← Data Ingestion · All domains · Machine Learning →

Practice these questions → · Timed practice on ExamRoll.io →

Browse Google →