Pipelines, Orchestration, and Idempotency

From queries to pipelines

A single query is not a data platform. A pipeline is a set of jobs wired by their dependencies, run on a schedule, that move data from raw to useful. In Apache Airflow a pipeline is a DAG, a Directed Acyclic Graph of tasks: "clean events" runs after "ingest events," "daily metrics" runs after "clean events," and a scheduler runs the whole graph every day. Acyclic means no loops, so there is always a valid order to run the tasks in.

A very common way to layer a pipeline is the medallion architecture:

Order of evaluation

1. Bronzeraw, as received, append-only
2. Silvercleaned, validated, conformed
3. Goldaggregated, business-ready

• Bronze is the raw data exactly as it arrived, append-only, so you can always reprocess from source.
• Silver is cleaned, validated, deduplicated, and conformed.
• Gold is the business-level aggregates the dashboards read.

Each layer is a scheduled job that reads the one before it. You built exactly this medallion in Level 5's streaming capstone, one script that rebuilt Silver and Gold from Bronze with idempotent DELETE-then-INSERT loads; this lesson adds the orchestration layer that schedules those jobs as a DAG. Modern cloud pipelines favor ELT (load raw into the lake or warehouse, then transform there with SQL) over the older ETL (transform before loading), because storage is cheap and the warehouse is powerful.

Batch vs streaming. Everything so far is batch: a job runs on a schedule over a chunk of data. The other mode is streaming, where events are processed continuously as they arrive, usually off a message queue or log like Apache Kafka (an append-only, partitioned log whose consumers read by offset, and a common home for the Avro records from the last module). Micro-batch sits in between, running tiny batches every few seconds. Most analytics pipelines are batch or micro-batch; true streaming is for low-latency needs.

Two ideas that separate juniors from mid-levels

• Idempotency. A job will be retried after a failure, and a backfill will re-run last week. If re-running doubles the data, your pipeline is broken. An idempotent job produces the same result no matter how many times it runs, achieved by overwriting a partition, using MERGE (upsert), or delete-then-insert, never a blind append. This is the single most important reliability property of a batch job. You built exactly these idempotent rebuilds as SQL scripts in Level 5; here you reason about them operationally, and the duplicate-detection check in the next lesson is how you catch a re-run that was not idempotent.
• Backfill and freshness. When logic changes or late data arrives, you backfill: re-run the pipeline over historical partitions. Incremental loads process only new data since a high-water mark (the last-seen timestamp), usually with a small look-back window to catch late arrivals. Freshness is how recent the data is, and a freshness SLA ("gold is never more than 30 minutes behind") is what you page someone about. In this lesson freshness is proxied by whether a run finishes within its SLA minutes; real freshness also depends on when the run started, not just how long it took.

Operating a pipeline with SQL

You run a pipeline by watching its run log: which jobs succeeded, how many rows they wrote, how long they took, whether they met their SLA. Querying that operational metadata is a daily DE task. The capstone exercises query a pipeline_runs table to compute per-job reliability and to catch the runs that breached their freshness SLA.

Common mistake: treating a green run as proof of good data. A job can succeed and still write duplicates, nulls, or half the rows, so a success status tells you the job ran, not that the data is correct.

Interview nuance: "make this backfill safe to re-run" is a direct idempotency question: overwrite the target partition or MERGE on a key, so a second run replaces rather than duplicates. "How do you know the data is late" is a freshness/SLA question answered by comparing each run's duration or finish time to its SLA, which is exactly the capstone query.

On a real platform this differs. Real run metadata lives in Airflow's database, dbt's run results, or a warehouse audit table, with far more detail. The pipeline_runs table here keeps job, date, status, rows, duration, and SLA, which is enough to compute the reliability and freshness questions an on-call DE actually asks.