GCP-194 · Lesson 6 of 12 · 18 min
Review: Data pipeline orchestration
▶ Video coming soon A video version of this lesson is on the way.
Section 3 is the smallest section of the exam, at about 18 in 100, or 10 or 11 questions on a 60-question form. It was also the section Mateo had studied most recently, in his three weeks with Aisha in GCP-222, so Ravi expected it to be easy. The diagnostic said otherwise: Mateo answered 5 of 8 correctly, and two of his three misses were questions where he chose a tool Birchline uses instead of the one the scenario called for. Aisha took over this review. "Orchestration questions are mostly about choosing," she said. "Which tool transforms, which tool schedules, which tool reacts to an event. Get those three straight and most of the section answers itself."
The section has two objectives. Objective 3.1 is about designing and implementing simple pipelines: choosing a transformation tool, evaluating ELT and ETL use cases, and choosing products for a basic transformation pipeline. Objective 3.2 is about scheduling, automating and monitoring: scheduled queries and schedulers, monitoring Dataflow jobs, logs and metrics, choosing an orchestrator, event-driven ingestion from Pub/Sub, and Eventarc triggers.
Transforming, scheduling and reacting are different jobs
The first idea of the section is that a pipeline has separate concerns, and different tools specialize in each. A transformation tool does the work on the data: cleaning, joining, aggregating. A scheduler or orchestrator decides when work runs and in what order, and handles retries and dependencies. An event trigger starts work when something happens, such as a file arriving. Some tools do more than one, but the exam usually wants the tool whose main job matches the need.
Choosing a transformation tool
| Tool | What it is | Choose it when | Avoid it when |
|---|---|---|---|
| Dataform | SQL workflows inside BigQuery: SQLX files with dependencies, tests called assertions, and version control | Data is in BigQuery and transformations are SQL, as in most ELT designs | Data must be processed before it reaches BigQuery |
| Dataflow | A serverless service that runs Apache Beam pipelines, batch or streaming, with ready-made templates | Streaming data, very large batch jobs, complex logic, or a template fits | A few SQL statements would do |
| Dataproc | Managed Spark and Hadoop clusters, or serverless Spark | Existing Spark or Hadoop code must run with little change; the team knows Spark | Starting fresh with no Spark code or skills |
| Cloud Data Fusion | Visual pipeline building on CDAP, with many connectors | A team prefers drag-and-drop pipelines over code | The pipeline is a few SQL steps already inside BigQuery |
| Managed Service for Apache Airflow (Cloud Composer) | Managed Apache Airflow for orchestrating workflows defined as DAGs | Coordinating many steps across services, with dependencies and retries | You need a tool that transforms data itself; Airflow coordinates others |
Cloud Composer appears in the guide's list of transformation tools, but its real role is orchestration. Airflow tasks can run SQL or Python, but the heavy lifting is usually done by BigQuery, Dataflow or Dataproc, which Airflow starts and watches. An exam option that says "use Cloud Composer to transform the data" can still be right if the scenario stresses coordinating many dependent steps; it is usually wrong if the scenario is a single SQL transformation.
The look-alike pair that cost Mateo a question in the diagnostic was Dataflow against Dataproc. Both process large data. The clue is the code that already exists. A scenario with existing Spark jobs, Hadoop skills or a goal of moving without rewriting points to Dataproc. A scenario with streaming data, no existing code and a preference for serverless points to Dataflow. Birchline uses Dataflow for its checkout-error stream because there was no Spark code to keep.
ELT and ETL in pipelines
Lesson 4 covered ETL, ELT and ETLT as loading patterns. In a pipeline the same choice decides the tools. An ELT pipeline loads raw data into BigQuery, then runs Dataform or scheduled SQL to build clean tables. An ETL pipeline transforms first, usually in Dataflow or Cloud Data Fusion, and loads only the result. For a simple daily ELT pipeline, the exam often expects three parts: a load from Cloud Storage or a transfer service, a Dataform workflow, and something to trigger or schedule it.
Scheduling
The guide names three ways to schedule work: BigQuery scheduled queries, Cloud Scheduler, and Cloud Composer. Each fits a different size of job.
| Scheduler | What it does | Fits |
|---|---|---|
| BigQuery scheduled query | Runs one SQL statement on a schedule, optionally writing to a table | A single, simple SQL job, such as refreshing a summary table each morning |
| Cloud Scheduler | A managed cron service that sends an HTTP request or a Pub/Sub message on a schedule | Starting something else on a timetable: a Workflow, a Cloud Run job, a Dataflow template |
| Managed Airflow (Cloud Composer) | Runs DAGs on schedules, with dependencies between tasks | Many steps that depend on each other and on outside systems |
| Dataform workflow schedule | Runs a Dataform workflow on a schedule | A set of SQL transformations with dependencies, all inside BigQuery |
One operational lesson from GCP-222 is worth remembering for the exam. Birchline's promotions summary was a scheduled query created by an analyst who later left the company. When the analyst's account was removed, the scheduled query stopped running, because it ran with that person's credentials. The fix, and the good practice the exam rewards, is to run production scheduled queries and pipelines as service accounts with only the roles they need, not as individual people.
Worked problem
Scheduling around time zones
Store till files reach the landing bucket by 02:00 local time, and the summary must be ready by 06:00. Birchline schedules the summary job for 02:30 local time in Columbus, which is on Eastern time. Some scheduling tools take times in UTC. Eastern Standard Time is UTC−5 and Eastern Daylight Time is UTC−4. What UTC time should a UTC-based schedule use in January and in July?
Show solution
- In January, Eastern Standard Time applies: 02:30 + 5 hours = 07:30 UTC.
- In July, Eastern Daylight Time applies: 02:30 + 4 hours = 06:30 UTC.
- A schedule fixed at 07:30 UTC would run at 03:30 local time in summer, still before 06:00, so it is safe all year.
- A schedule fixed at 06:30 UTC would run at 01:30 local time in winter, before all the files arrive.
Answer: 07:30 UTC in January and 06:30 UTC in July. A single fixed UTC time of 07:30 is safe all year; 06:30 is not.
This is also the argument for the event-driven design Birchline moved to in GCP-222. A fixed schedule guesses when the data will arrive. An event trigger starts the work when it actually does.
Event-driven ingestion
The guide asks candidates to identify event-driven ingestion from Pub/Sub to BigQuery and to use Eventarc triggers with Dataform, Dataflow, Cloud Run functions, Cloud Run or Cloud Composer.
Pub/Sub is a messaging service: publishers send messages to a topic, and subscriptions deliver them. A BigQuery subscription writes messages from a topic straight into a BigQuery table, with no pipeline to run, which suits simple streaming ingestion when no transformation is needed. When messages need cleaning, joining or windowed aggregation first, a Dataflow streaming pipeline reads the subscription instead. Birchline's web events use both: a BigQuery subscription for the raw table and a Dataflow pipeline for checkout errors by minute.
Eventarc routes events from Google Cloud services to a destination. The most common data example is an object finalized event, which fires when a new file lands in a Cloud Storage bucket. Birchline's till pipeline from GCP-222 is the model: the event triggers a Workflow, which loads the file into BigQuery, records it in a control table by name and generation so it is never loaded twice, and then starts a Dataform workflow.
Choosing an orchestrator
The guide names four orchestration tools: Cloud Composer, scheduled queries, Dataproc Workflow Templates, and Workflows. The exam asks which fits a described pipeline, and the deciding factors are the number of steps, the services involved, and how much operational effort the team will accept.
| Orchestrator | Best for | Operational effort |
|---|---|---|
| Scheduled queries | One SQL statement on a timetable | Lowest |
| Workflows | A sequence of steps calling Google Cloud APIs and HTTP services, with simple branching and retries; serverless | Low; billed by steps run |
| Dataproc Workflow Templates | A fixed sequence of Dataproc jobs, creating and deleting a cluster around them | Low, for Spark and Hadoop work |
| Managed Airflow (Cloud Composer) | Complex DAGs across many services and outside systems, with rich dependencies, backfills and monitoring | Highest; an environment runs all the time |
Aisha's rule is to use the simplest tool that handles the dependencies. Birchline kept one Airflow environment for its warehouse exports, which have many steps and outside systems, and moved two simple DAGs to Workflows and Dataform in GCP-222, cutting the monthly cost. An exam question that describes two or three steps and stresses low cost or low overhead rarely wants Airflow.
Monitoring pipelines
Objective 3.2 also asks candidates to monitor Dataflow jobs in the job interface, to review logs in Cloud Logging, and to review metrics in Cloud Monitoring. The Dataflow job page shows the pipeline graph, each step's throughput, system lag and data freshness, and worker logs. Cloud Logging collects the logs of every service and lets a practitioner filter them, for example for errors from one job. Cloud Monitoring collects metrics, draws dashboards and sends alerts when a metric crosses a threshold.
| Problem | Where to look first |
|---|---|
| A streaming Dataflow job is falling behind | The job page: system lag and data freshness, and the slowest step |
| A scheduled query did not run | The scheduled query's run history, then Cloud Logging for the error |
| A pipeline "succeeded" but loaded far fewer rows than usual | A volume check and alert in Cloud Monitoring, or the control table |
| A query suddenly takes much longer | The job details: bytes read and whether a partition filter was lost |
The third row is the quiet failure from GCP-222. On October 26, a permission change made the till pipeline load about 3,050 rows instead of the usual 30,400, and every step reported success. Nothing in the logs looked wrong. Aisha added a volume check that compares each night's count with the recent average and alerts when it falls far below. The exam likes this point: success is not the same as correctness, and good monitoring checks the data, not just the job status.
Retries, idempotency and backfills
Orchestration questions often hide a reliability clue. Any scheduled or event-driven step can run twice: a retry after a timeout, a duplicate event, or someone rerunning a failed day. A step is idempotent if running it twice leaves the same result as running it once. Loading a file by appending rows is not idempotent, because a second run doubles the rows. Loading it with a MERGE on a key, or by replacing the one partition for that day, is idempotent, because a second run overwrites the same data. Aisha's rule for Birchline is that every load must be safe to rerun, and the GCP-222 till pipeline replaces one date partition per run for exactly that reason.
A backfill reruns a pipeline for past dates, for example after a bug in a transformation is fixed. Airflow supports backfills directly, because each DAG run has a logical date, and a well-written task uses that date rather than the current clock. A scheduled query can also be run for a range of past dates. On the exam, a scenario that mentions reprocessing the last thirty days, or rerunning safely after a failure, points to idempotent steps and to an orchestrator that understands dates. A scenario that mentions retries with growing delays points to the retry settings of the orchestrator, or to Pub/Sub's redelivery and dead-letter topics for messages that keep failing.
Common mistakes
MistakeChoosing Cloud Composer for a single nightly SQL statement.
InsteadA scheduled query or Dataform schedule is simpler and cheaper.
MistakeChoosing Dataflow to run existing Spark jobs unchanged.
InsteadDataproc runs Spark and Hadoop with little change.
MistakeRunning production scheduled queries as a person.
InsteadUse a service account with only the roles it needs.
MistakeUsing a fixed schedule when data arrives at uneven times.
InsteadTrigger the pipeline from an event, such as a file landing.
MistakeTreating a successful job as proof the data is right.
InsteadAdd data checks, such as row counts against recent averages, and alert on them.
Summary and key terms
- Section 3 is about 18 in 100 of the exam.
- Transforming, scheduling and reacting to events are separate jobs.
- Dataform runs SQL in BigQuery, Dataflow runs Beam pipelines for batch and streaming, Dataproc runs Spark and Hadoop, and Cloud Data Fusion is visual.
- Scheduled queries suit one SQL job, Cloud Scheduler triggers other services, Workflows sequences API calls, Dataproc Workflow Templates sequence Dataproc jobs, and Managed Airflow handles complex DAGs.
- Pub/Sub with a BigQuery subscription ingests streams simply; Eventarc starts pipelines when files land.
- Monitor jobs in their pages, logs in Cloud Logging, and metrics and alerts in Cloud Monitoring.
- Orchestrator
- A tool that decides when work runs, in what order, and what happens on failure.
- Dataform
- A BigQuery service for SQL transformation workflows with dependencies and assertions.
- DAG
- A directed acyclic graph: a set of tasks and the dependencies between them, as used by Airflow.
- Workflows
- A serverless service that runs sequences of steps calling APIs and services.
- Dataproc Workflow Template
- A reusable sequence of Dataproc jobs with a managed cluster.
- BigQuery subscription
- A Pub/Sub subscription that writes messages straight into a BigQuery table.
- Eventarc
- A service that routes events from Google Cloud sources to destinations such as Workflows or Cloud Run.
- Object finalized event
- The event that fires when a new object is written to a Cloud Storage bucket.
Practice problems
Work each problem on paper first, then reveal the answer to check.
A team has Spark jobs on premises and wants them on Google Cloud with minimal change. Which service?
Show answer
Dataproc.
A pipeline has three steps: load a file, run SQL, send a notification. Which orchestrator fits with low overhead?
Show answer
Workflows, possibly triggered by Eventarc.
Why did Birchline add a volume check?
Show answer
A permission change made the pipeline load a tenth of the usual rows while every step reported success.
Lesson quiz · 5 questions
Score 60% or more to complete this lesson.
Create a free account to save your progress, take quizzes and earn a certificate.