Pipeline orchestration concepts

On Monday, October 26, 2026, Mateo Alvarez starts three weeks of morning sessions with Aisha Daniels, the data engineer who keeps Birchline's pipelines running. Ravi Menon set the arrangement up after the last two courses: Mateo can now prepare data and analyze it, but he has never been the person whose phone buzzes at 05:40 when the sales numbers are not ready. Each morning, after Aisha's on-call handover, they spend half an hour on one part of running data reliably. The first session starts with a question Aisha asks every new team member: what has to happen, in what order, before a store manager in Columbus opens the morning dashboard?

What a pipeline is, and what orchestration adds

A data pipeline is a series of steps that moves data from where it is created to where it is used, changing it along the way. Birchline's nightly sales pipeline collects the till files from 40 stores, loads them into the raw dataset, combines them with web orders, builds the curated sales table and refreshes the views that the dashboards read. Each step on its own is something Mateo already knows how to do from GCP-220 and GCP-221. What he has not done is make them happen every night, in the right order, without anyone pressing a button.

Orchestration is that missing part. An orchestrator decides when each step starts, waits for the steps it depends on, retries the ones that fail for temporary reasons, records what happened, and tells someone when it cannot recover. Aisha puts it simply: the steps do the work, and the orchestrator makes sure the work happens in the right order and that somebody knows when it did not.

Orchestration is different from transformation. A transformation tool changes data: BigQuery SQL, Dataform, Dataflow, Dataproc and Cloud Data Fusion all transform. An orchestration tool coordinates steps: BigQuery scheduled queries, Workflows, Eventarc and managed Apache Airflow all orchestrate. Some products do a little of both. Dataform, for example, runs SQL transformations and also works out their order from the dependencies between them. The exam often asks you to pick the right tool for a requirement, and the first question to ask is whether the requirement is about changing data or about coordinating steps.

Thinking in DAGs

Aisha draws the nightly pipeline on the whiteboard as boxes and arrows. Each box is a task, and each arrow means 'this task needs that one to finish first'. Because the arrows all point forward and no task can depend on itself, even indirectly, the drawing is a directed acyclic graph, or DAG. Almost every orchestrator, from Airflow to Dataform, represents a pipeline this way.

The DAG answers practical questions. Which tasks can run at the same time? The two loads, because neither depends on the other. What happens if the web orders extract fails? The curated build waits, and so does everything after it, while the till-file load can still finish. What is the critical path, the longest chain of dependent tasks that decides when the whole pipeline ends? Here it runs from the till files through the load, the curated build, the checks and the view refresh. Speeding up a task that is not on the critical path does not make the pipeline finish sooner.

An orchestrator works out a valid running order from the dependencies, a process called a topological sort. Mateo tries it on the nightly pipeline using Python's standard library, which includes a module made for exactly this.

from graphlib import TopologicalSorter

# each task: the set of tasks it depends on
dag = {
    "load_pos": {"till_files"},
    "load_web": {"web_extract"},
    "build_curated": {"load_pos", "load_web"},
    "quality_checks": {"build_curated"},
    "refresh_views": {"quality_checks"},
}
ts = TopologicalSorter(dag)
ts.prepare()
wave = 1
while ts.is_active():
    ready = sorted(ts.get_ready())
    print(f"wave {wave}: run together ->", ", ".join(ready))
    ts.done(*ready)
    wave += 1

wave 1: run together -> till_files, web_extract
wave 2: run together -> load_pos, load_web
wave 3: run together -> build_curated
wave 4: run together -> quality_checks
wave 5: run together -> refresh_views

Five waves for seven tasks: the first two waves each hold two tasks that can run in parallel, and the last three hold one each. If each task took 20 minutes, the pipeline would take about 100 minutes even with unlimited workers, because the five waves must follow one another.

ETL or ELT

Pipelines differ in where the transformation happens. In ETL, extract, transform, load, data is changed before it reaches the warehouse, often by a separate engine such as Dataflow or Cloud Data Fusion. In ELT, extract, load, transform, raw data is loaded first and transformed inside the warehouse with SQL. BigQuery makes ELT attractive: loading is free, storage is cheap, and SQL transformations scale without servers to manage. Birchline's till files follow ELT: they land in raw.pos_sales unchanged, and SQL builds everything else.

ETL still has its place. When data must be cleaned of something before it is stored, such as card numbers or personal details that must never reach the warehouse, the change has to happen first. When data arrives as a stream and must be aggregated as it flows, Dataflow transforms it on the way in. GCP-220 described the returns portal pipeline as ETLT: a small Cloud Run service drops the customer's phone number before loading, and SQL does the rest. The test is simple: if anything must not be stored, transform before loading.

Triggers: time, dependency or event

Every pipeline run starts with a trigger. A time-based trigger, such as 'every day at 03:30', is the simplest, and BigQuery scheduled queries and Cloud Scheduler both work this way. A dependency trigger starts a task when the tasks before it succeed, which is what a DAG provides. An event-based trigger starts work when something happens, such as a file arriving in a bucket or a message arriving on a Pub/Sub topic, and Eventarc is Google Cloud's service for routing those events.

Each has a weakness. A time trigger assumes the input is ready, and a late file leaves the job processing yesterday's data or nothing at all. A dependency trigger needs an orchestrator that understands the dependencies. An event trigger can fire twice for the same event, or fire for a half-finished upload if the pipeline is not careful about which events it listens to. Birchline uses all three: the Google Ads transfer runs at a fixed time, the curated build depends on the loads, and the till-file load starts when each store's file arrives.

Choosing an orchestration tool

Google Cloud offers several orchestration options, and Aisha's rule is to use the simplest one that meets the need. A single SQL statement that must run every night needs nothing more than a scheduled query. A set of related SQL transformations with dependencies and tests belongs in Dataform. A few calls to Google Cloud APIs in sequence, with retries and simple branching, suit Workflows. A pipeline that spans many systems, needs sensors that wait for external conditions, or must be backfilled over months of history may justify managed Airflow, which Google now offers as Managed Service for Apache Airflow, the service long known as Cloud Composer. Spark and Hadoop jobs in a fixed sequence can use Dataproc workflow templates.

The guide is a starting point, not a rule. Tools combine: an Eventarc trigger can start a Workflows execution that runs a Dataform workflow. The question to ask of any choice is what it costs to run and maintain. Managed Airflow is powerful, but an environment runs and is billed around the clock, needs upgrades and needs people who can write Python DAGs. A scheduled query costs nothing beyond the query itself.

Properties of a reliable pipeline

Aisha ends the session with the four properties she checks in every pipeline review. A pipeline should be idempotent: running the same step twice for the same day gives the same result, with no duplicated rows. It should be observable: every run leaves logs and metrics that show what it did. It should be recoverable: after a failure, someone can rerun one day or one step without rebuilding everything. And it should be owned: a named team is responsible for it, and the alert goes to them. The rest of this course works through how Google Cloud's tools support each property.

Backfills, reruns and the logical date

Two words come up in every pipeline discussion. A rerun repeats a run that failed or produced wrong results, usually for one day. A backfill runs the pipeline for a range of past dates, for example to load two years of history into a new table or to rebuild a table after a logic change. Both are only safe if each run knows which day it is processing, and that day is not simply 'today'.

Orchestrators handle this with a logical date, sometimes called the run date or data interval. The run that starts at 03:30 on October 27 processes the sales of October 26, and if it is rerun on October 30 it must still process October 26. Aisha shows Mateo the difference in one line of SQL: a query that filters on CURRENT_DATE() minus one gives a different answer every day it is rerun, while a query that filters on a run-date parameter always processes the day it was asked to process. Writing every step against a run date, and replacing that day's partition rather than appending to the table, is what makes reruns and backfills boring, which is exactly what Aisha wants them to be.

Backfills also need limits. Running 700 days of history at once can exhaust slots or quotas and crowd out the nightly work. Most orchestrators let you cap how many runs proceed at the same time, so a backfill of two years might run five days at a time over a weekend.