Data Pipeline Orchestration Fundamentals

Data Engineering & Modern Data Platforms · Lesson 19 · Data Ingestion, ETL and Orchestration

Lesson Overview

The cleanse and merge from Data Cleansing and Transformation are safe to run twice. They are not safe to run in the wrong order, at the wrong time, or on top of each other. Last Tuesday the learner dimension load started four minutes late. The fact load had already resolved surrogates, missed the new country version, and published. A second cron entry, left over from a manual backfill, started the same merge while the first was still writing. The unique key prevented duplicate order_line_ids. It did not prevent the two sessions from blocking each other until one was killed, and the killed session left the run record saying success because the shell script checked the wrong exit code.

That is an orchestration failure. The SQL was fine. Nothing in the system knew that facts depend on dimensions, that only one run may own fact_order_line at a time, that "yesterday" means a business date rather than the wall clock of the machine, or that finance's 07:00 UTC promise is a deadline with a name.

Orchestration is the control plane for pipeline work. It decides when a unit of work becomes eligible, which units must finish first, what "a run" means, what to do when a unit fails, how to reprocess a past interval, and how to record the state so the next decision is based on evidence. The transforms stay in the data plane: Python, SQL, and later Spark. The orchestrator should schedule them, pass them a business date and a run id, and store their status. It should not become the place where revenue is calculated.

This lesson stays independent of any one product. Cron, a cloud scheduler, Airflow, Dagster, and Prefect are implementations of the same ideas: a graph of tasks, a definition of time, a state store, a retry policy, and a freshness promise. Apache Airflow Architecture and DAGs maps these ideas onto Airflow 2.x. If the ideas are fuzzy, the product knobs look arbitrary. If the ideas are sharp, the knobs are just names.

Learning Objectives

By the end of this lesson, you should be able to:

1. Define orchestration as a control plane and keep heavy transforms out of it.
2. Model a pipeline as a directed acyclic graph of tasks with explicit dependencies.
3. Distinguish a schedule, a data interval, a logical date, and the wall clock.
4. Explain why orchestrators offer at-least-once task execution and why tasks must be idempotent.
5. Specify retry, backoff, and concurrency limits without hiding a data defect behind infinite retries.
6. Define backfill as reprocessing historical intervals, and state what must be true for that to be safe.
7. Write a freshness SLA in business language and separate it from "the task exited zero."
8. Choose a time trigger or a data trigger, and say when a wait-for-data step is the right dependency.
9. Describe the run states an orchestrator must store and who is allowed to change them.
10. Place a fan-out and a fan-in in the TechLearningPro daily warehouse graph.
11. Identify the failure modes of cron for a dependency-heavy load.
12. Hand the next lesson a graph that Airflow can implement without inventing new business rules.

Prerequisites

• Data Cleansing and Transformation: idempotent merges, quarantine, and SCD change rows. Orchestration retries those jobs.
• ETL and ELT Architecture: where the SQL runs. The orchestrator calls that step. It does not replace it.
• Dimensional Modeling and Data Warehousing: dimensions before facts, and the reconciliation that gates publish.
• Building Batch Data Pipelines with Python: a job with a run id and a landing path.
• A working sense of cron syntax is useful and is not sufficient, which this lesson will make concrete.

Detailed Core Concepts

The control plane and the data plane

A task is a unit of work with a start, a finish, and a status. "Load dim_learner for business date D" is a task. The orchestrator stores that the task is queued, running, successful, or failed. The database session that merges the dimension is the work. Mixing them looks convenient: a scheduler script that also parses the CSV. It becomes the only copy of the business rule, it cannot be tested without the scheduler, and it cannot be moved to Spark later without rewriting the clock logic.

Keep the boundary sharp.

The orchestrator passes parameters across the boundary: business_date, run_id, input and output locations. It receives a status and a small summary: rows inserted, rows quarantined, reconciliation difference. It does not receive the file contents. Large payloads in the control database are an operational incident waiting for the next upgrade.

A pipeline is a directed acyclic graph

Each node is a task. Each edge means "the downstream task may start only after the upstream task has succeeded," unless you explicitly choose a weaker rule. Cycles are illegal because a cycle has no start. If two tasks truly need each other's output, they are one task or they share a table written in a defined order across runs, not a cycle inside one run.

TechLearningPro's daily warehouse graph for one business date:

1. land_reseller_file — copy the file and record the checksum.
2. land_learner_feed — copy the change feed.
3. cleanse_orders — parse, quarantine, dedup. Depends on the reseller file.
4. build_learner_changes — SCD-friendly change rows. Depends on the learner feed.
5. load_dim_learner and load_dim_course — may run in parallel after their inputs exist.
6. load_fact_order_line — depends on both dimension loads and on cleanse_orders.
7. reconcile_revenue — depends on the fact load. Failure here means the date is not published.
8. publish_freshness — records that business date D is ready. Depends on reconciliation.

Steps 1 and 2 fan out from the start: they do not depend on each other. Step 6 is a fan-in: it waits for three parents. Step 8 is the single leaf that means "done" to finance. A graph with several leaves makes "the pipeline succeeded" ambiguous. Pick a publish task and mean it.

Skipping an edge because "the file is usually there by 05:00" is a hope, not a dependency. The fact load depends on the dimension load even when the dimension load is fast.

Time is three different clocks

People say "run yesterday's pipeline at 06:00." That sentence hides three clocks.

• Wall clock. When the process actually starts. 06:00 UTC is a wall-clock trigger. If the machine was down until 09:00, the wall clock of the start is 09:00.
• Data interval. The business period the run is responsible for. A nightly job triggered at 06:00 UTC on 2 March is responsible for the data of 1 March, often expressed as the half-open interval [1 March 00:00 UTC, 2 March 00:00 UTC) if the business date is a UTC date. Partner files stamped in America/New_York need a written rule for which instants belong to which business date. The cleanse lesson already separated event time from load time. The orchestrator must pass the interval in, not let the job call date.today().
• Logical date. A name for the interval, often the start of it. Schedulers use it so two tasks in the same run agree on "which day." It is not the wall clock, and it is not automatically the date you should print on a report if your business date is defined in another zone.

A job that computes the business date as "today minus one day" at the moment of execution will process the wrong date when the run starts late, when someone backfills, or when daylight-saving time makes "minus one day" ambiguous. Pass the interval as a parameter. Inside the task, use only that parameter.

Catch-up is the policy for intervals that never ran. If the scheduler was down from Monday to Wednesday, a catch-up policy creates a run per missed interval, in order or under a concurrency cap. A "latest only" policy skips Monday and Tuesday and runs Wednesday. Finance usually wants catch-up for facts. A machine-learning feature job might want latest only. Write the policy per pipeline. The default in many schedulers is catch-up, and a new pipeline with a start date in 2020 will try to run years of intervals the moment you deploy it. Set the start of scheduling at the first date you intend to own.

At-least-once execution

Orchestrators crash. Workers die after the task committed and before the status was saved. The honest guarantee is at-least-once: a task that you asked to run may run more than once. Exactly-once delivery of a side effect is a property you build in the task, usually by the idempotent merge from lesson 18, not a property the scheduler promises.

Design every task for a second execution with the same parameters.

• Reads should be repeatable for that interval: the landing file is immutable, or the query is bounded by the data interval.
• Writes merge or overwrite the interval's partition.
• External side effects that are not idempotent (sending email, charging a card) do not belong in the data graph. Publish a row that a separate, carefully designed notifier consumes. Do not send the mail from a task that retries.

At-most-once, by skipping retries, avoids double writes and loses the run when a worker dies. That is acceptable for a cache refresh you can miss. It is a poor fit for revenue.

Retries, timeouts, and concurrency

A retry repeats a failed task with the same parameters. Use it for transient failures: a lock timeout, a dropped connection, a file that appears a minute late if the task's failure mode is "not yet." Do not use it to grind through a data defect. CONFLICTING_MEASURE will fail the same way on every attempt. Cap retries, use a delay, and optionally increase the delay. A common pattern is a small number of retries with exponential backoff and a maximum delay, then a hard failure that pages a person.

A timeout stops a task that will never return. Without one, a stuck merge holds a concurrency slot until someone notices. Set the timeout from observed runtime plus headroom, not from a guess of "24 hours" that hides a deadlock.

Concurrency limits protect the thing the graph can overwhelm. Two fact merges on the same table should not run. Five Spark jobs on a small cluster should not run if the cluster accepts three. The limit is a property of the shared resource, often called a pool: a pool of warehouse slots, a pool of file-ingest workers. Tasks declare how many slots they need. The orchestrator starts a task only when the slots are free. This is separate from the graph. Two tasks with no edge between them are allowed to run together until a pool says otherwise.

max active runs of a pipeline is the cap on how many business dates process at once. During a backfill you may want two dates at a time and not twenty. Dimensions for date D and facts for date D must still respect the graph inside each run. Across runs, a fact for Tuesday must not assume Wednesday's dimension load has happened, and a type-2 load that is not partitioned by date must be serialized or it will close the same current row twice. TechLearningPro serializes load_dim_learner globally with a pool of one, and allows fact loads for different open dates only if each merge is confined to its order_line_id set and does not rewrite the shared dimension.

Backfill

A backfill reprocesses one or more historical intervals. You need it when a rule was wrong, a file arrived late, or the scheduler was down. A backfill is safe when:

• Each run receives its own interval as a parameter.
• Writes are idempotent for that interval.
• Closed periods are protected, as in the lesson 18 merge predicate.
• Downstream aggregates for those dates are included in the graph or explicitly rebuilt.
• The concurrency cap keeps the warehouse upright.

A backfill is not "run the script in a loop from the laptop." That loop will use date.today(), will not record state, and will not respect pools. The orchestrator should create one run per interval and show their statuses.

Clearing a single failed task and running it again is a narrower operation than backfill. Use it when the interval already exists and one node failed. Use backfill when the interval was skipped or must be recomputed under a new rule.

Freshness promises

A service level agreement, in the business sense, is a promise about data: "business date D of fact_order_line is published by 07:00 UTC on D+1, with reconciliation difference zero and quarantined revenue under the threshold." A task that exits zero at 10:00 has succeeded as a process and missed the promise. A task that fails at 06:50 has missed it too. Track both.

Define, for each published table:

• Grain and date column the promise refers to.
• Deadline in a named timezone.
• Completeness condition: reconciliation matched, quarantine under threshold, publish task succeeded.
• Who is paged and what they are allowed to do (rerun, quarantine, delay the report).

An SLO is the internal target you alert on, often tighter than the external promise so you still have time to rerun. The 07:00 promise might have a 06:30 SLO on reconcile_revenue. Alert on the SLO. Report on the SLA.

"The scheduler ran" is not a freshness metric. Measure the maximum published business date and the timestamp of publish_freshness. A run that succeeded for an empty interval will look green and be late in business terms. The reconcile task is what ties the status to rows.

Time triggers and data triggers

A time trigger starts a run when the clock says so. It is right when the source is supposed to exist at a known time and lateness should be an incident: the reseller file by 05:00 UTC.

A data trigger starts downstream work when an upstream dataset is updated. It is right when the producer is another pipeline you own, or when arrival time varies. "Run the fact load when the typed staging table for date D is published" does not sit in a sleep loop. The producer records a publication, and the consumer becomes eligible.

A sensor, or wait task, polls for a condition and then succeeds. It is a dependency on the outside world: an upstream system you do not schedule. Use it for the reseller file if the producer cannot emit an event. Give it a timeout that matches the SLO, and do not let it occupy a worker for hours if the orchestrator can suspend and resume it. The next lessons show how Airflow implements sensors and dataset-style triggers. The concept you need now is the choice: know the deadline (time trigger plus a wait with a timeout) or know the predecessor (data trigger).

State the orchestrator must store

For each pipeline definition: the graph version, the schedule, the start of ownership, the catch-up policy, and the concurrency limits.

For each run: the interval, the state, the start and end wall-clock times, and who triggered it (schedule, backfill, or a person).

For each task attempt: state, try number, worker, log location, and the summary counts the task reported.

States you will actually use:

A success that is recorded because the script printed a message and ignored the SQL error is a broken task, not a scheduler mystery. The task process exit code must be the exit code of the work. Shell scripts should use set -euo pipefail or an equivalent, and the SQL client should be in a mode that fails on error.

Nobody should edit state to green without a record. Marking a failed reconcile as success publishes a date the sums did not match. If you must skip a task, skip it explicitly and write the reason. The audit trail is part of the control plane.

What cron gets wrong, and what it still does well

Cron starts a process on a wall-clock pattern on one machine. It is a good trigger for a single idempotent job with no dependencies: compact a log, or kick a health check. It does not store a graph, does not know intervals, does not limit concurrency across machines, does not have attempt history, and does not distinguish "process killed" from "process succeeded" unless the job itself is careful.

The TechLearningPro failure at the start of this lesson is the standard cron pile-up: overlapping invocations, no fan-in, and a business date computed from the wall clock. You can bolt on lock files and a metadata table. At that point you are building an orchestrator. Use one that already stores runs.

Cloud schedulers that invoke a function on a cron expression have the same shape as cron, with better availability. They are still not a graph. A workflow engine is the point at which the graph, the interval, the retries, and the state live in one place. Airflow is the engine this course implements next. Others exist. The graph you draw here should be portable.

Ownership

Name an owner for the pipeline and an owner for the freshness promise. The data platform owns the graph and the merge. Finance owns the definition of reconciled revenue. A page at 06:30 goes to the platform. A dispute about the cents goes to the contract between platform and finance, with the reconciliation query as the artifact. Orchestration does not settle the dispute. It makes sure the query ran, and it refuses to publish when the query fails.

Architecture and Design Diagrams

The daily graph is the contract between platform and finance. Edges are success dependencies. The pool around the dimension load is a resource constraint, not an edge: even a backfill cannot run two learner-dimension loads at once.

mermaidCopy
flowchart TD
    startNode[Schedule at 06:00 UTC] --> landFile[land_reseller_file]
    startNode --> landFeed[land_learner_feed]
    landFile --> cleanse[cleanse_orders]
    landFeed --> changes[build_learner_changes]
    changes --> dimLearner[load_dim_learner]
    catalog[land_course_catalog] --> dimCourse[load_dim_course]
    cleanse --> fact[load_fact_order_line]
    dimLearner --> fact
    dimCourse --> fact
    fact --> reconcile[reconcile_revenue]
    reconcile --> publish[publish_freshness]

land_course_catalog can be its own small pipeline on a slower cadence if titles change rarely. The fact load still waits for a successful course dimension that is at least as fresh as the run's contract says. A stale catalog is a data dependency, represented either by an edge or by a freshness check inside load_dim_course.

mermaidCopy
flowchart LR
    subgraph control [Control plane]
        Def[Pipeline definition]
        State[Run and attempt state]
        Limits[Pools and active-run cap]
    end
    subgraph data [Data plane]
        Py[Parse and cleanse]
        Sql[Dimension and fact merges]
        Recon[Reconciliation query]
    end
    Def --> State
    State -->|interval and run id| Py
    State -->|interval and run id| Sql
    Py --> Sql
    Sql --> Recon
    Recon -->|status and counts| State
    Limits --> State

A run that misses the SLO is still a run with states. The freshness record is written only by publish_freshness after reconciliation succeeds. Dashboards read that record. They do not infer freshness from "a process ran today."

Technical Explanation

Parameterizing a run

Every task entry point should accept the interval explicitly. This Python shape is the data-plane side of the contract. No scheduler is imported.

pythonCopy
from dataclasses import dataclass
from datetime import date

@dataclass(frozen=True)
class RunWindow:
    business_date: date
    run_id: str

def load_fact_order_line(window: RunWindow) -> dict:
    """Merge winners for one business date. Idempotent on order_line_id."""
    # SQL uses window.business_date as a bind parameter.
    # Return counts the orchestrator will store. Do not return row payloads.
    return {
        "business_date": window.business_date.isoformat(),
        "run_id": window.run_id,
        "inserted": 0,
        "updated": 0,
        "unchanged": 0,
        "quarantined": 0,
        "recon_diff_cents": 0,
    }

The orchestrator creates RunWindow. A manual run for 1 March passes 1 March even if the operator clicks on 4 March. A backfill from 1 February through 28 February creates one window per date. Nothing in load_fact_order_line calls date.today().

Dependency rules beyond "all parents succeeded"

The default rule is the right default: every parent succeeded. Weaker rules exist and should be rare.

• All parents finished, regardless of status. Useful for a notification task. Dangerous for a load.
• At least one parent succeeded. Useful for a fan-in of optional sources. Dangerous for revenue, because a missing dimension looks optional.
• Branch on a condition. The file was empty, so skip the merge and still succeed the run. Skipping must be an explicit state, and the publish task must know that "empty and expected" is different from "empty because the land task failed."

TechLearningPro uses all-parents-succeeded for every load and for reconcile. The only skip is cleanse_orders when the partner sent a manifest declaring zero rows and the manifest checksum matches. publish_freshness then records row_count = 0 with reason EMPTY_MANIFEST, which finance can distinguish from a failure.

Retry policy as a table

Retries on reconcile hide a real cents gap behind a green second attempt that still fails, or worse, behind a flaky query that sometimes times out and sometimes returns a false match if you "simplify" it under pressure. Do not retry business failures. Split transient database errors from recon_diff_cents != 0 in the task code: the first raises a retryable error, the second raises a failure marked non-retryable if the orchestrator supports that, or the task is configured with zero retries.

Backfill procedure without a product

1. Choose the closed interval of business dates. Exclude frozen periods unless finance opened them.
2. Fix the rule first, and deploy the new task code, or you will recompute the old bug.
3. Cap active runs so the warehouse stays available to interactive queries.
4. Serialize the type-2 dimension load.
5. Create one run per date. Do not pass "the whole month" into a job that deletes the month and inserts, unless that job is one run by design and is still idempotent.
6. Watch quarantine counts and reconciliation per date. A backfill that loads bad cents quickly is worse than a late pipeline.
7. Rebuild aggregates that read those dates. An aggregate outside the graph will quietly keep the old number.
8. Record who started the backfill and why.

If step 7 is easy to forget, the aggregate is another node in the graph with an edge from publish_freshness, and the backfill includes it automatically.

Freshness query

The promise is checkable with SQL after the control plane has done its job:

sqlCopy
SELECT
    business_date,
    published_at,
    recon_diff_cents,
    quarantined_revenue_cents
FROM ops.dataset_freshness
WHERE dataset_name = 'analytics.fact_order_line'
ORDER BY business_date DESC
LIMIT 7;

publish_freshness upserts this row. A separate checker, running every few minutes, compares published_at to the deadline for the newest expected business date and pages if the row is absent or recon_diff_cents is not zero. That checker can be a tiny time-triggered job. It is the SLO alarm. It does not load data.

Failure and partial progress

A fact load that commits in one transaction per business date leaves either the old rows or the new rows. A fact load that commits every ten thousand rows can leave a mixture if the worker dies. Orchestration cannot see inside the transaction. The task author chooses atomicity for the interval: one transaction, or a stage-and-swap, or a partition overwrite that becomes visible in one metadata operation. The orchestrator then records success only after that atomic publish. Lesson 18's merge in one transaction is the version this graph assumes.

Idempotency keys and the run id

The run id belongs in run_id on the row for traceability. It is not the merge key. If it were, a retry with a new run id would insert a second fact. The merge key stays order_line_id. The run id of the last writer is updated only when the hash changes, which is the rule you already wrote. Orchestration generates the run id. The data plane decides which column is identity.

Pools in the abstract

A pool has a name, a slot count, and a description of the resource. warehouse_write might have 4 slots because the database allows four heavy sessions before interactive queries suffer. load_dim_learner takes all 4, or you give it its own pool of 1 to serialize type-2 writes. cleanse_orders takes 1. The scheduler's queue is not infinite capacity. It is a queue plus these limits. When you move the cleanse to Spark, the pool moves with it: the limit becomes cluster slots, and the graph edges stay.

Practical Implementation or Design Walkthrough

Problem. Specify the daily TechLearningPro warehouse pipeline so a later Airflow DAG can implement it without new business decisions.

Sources and deadlines.

Freshness promise. analytics.fact_order_line for business date D is published by 07:00 UTC on D+1. Reconciliation difference is zero cents. Quarantined revenue is under 0.5 percent of file-declared revenue, or the run fails closed.

Graph. The diagram in the previous section, with load_dim_learner in a global pool of one slot, max active runs of 1 for scheduled operation, and 2 during an approved backfill.

Task contract. Each task is a Python entry point fn(window: RunWindow) -> dict or a SQL file executed with business_date bound. Exit code 0 only when the work succeeds. The returned dict is small.

Empty-file behavior. A manifest with row_count = 0 and a matching checksum skips cleanse and fact load, writes freshness with EMPTY_MANIFEST, and succeeds. A missing file at 06:20 fails land_reseller_file after retries and does not publish.

Backfill of 2026-02-01 through 2026-02-07. Frozen dates at or before the finance close are excluded. The dimension pool stays at one. Fact merges stay idempotent. After the seven publish tasks succeed, the weekly aggregate task runs for the week of 2 February because it is downstream of publish.

What this walkthrough deliberately omits. Operator classes, DAG files, and server processes. Those are lesson 20. If you can hand a teammate the graph, the clocks, the retry table, the pool, and the freshness SQL, you have finished the orchestration design.

Enterprise Real-World Scenarios

The 07:00 finance promise

Finance closes an internal flash report at 07:15 UTC using fact_order_line. The platform promise is 07:00 so there is a fifteen-minute buffer. The SLO alarm fires at 06:30 if publish_freshness is missing for the expected business date. On a normal day the file lands at 05:04, cleanse finishes by 05:25, dimensions by 05:40, the fact merge by 05:55, and reconcile by 06:05. The graph makes the 06:30 alarm meaningful: if land is still retrying at 06:20, a person knows which node is late without reading five logs. When the file is absent, the run fails and the flash report is labeled delayed. Nobody marks the task successful to keep a dashboard green. The cost of a late report is smaller than the cost of a wrong one, and the orchestrator's job is to make those two outcomes look different.

A second pipeline builds a learning-progress snapshot at 08:00 UTC. It does not sit downstream of revenue. It has its own promise. Sharing one cron host and one "data is done" flag would delay progress when revenue is late and would let revenue look done when only progress had run. Separate graphs, separate freshness rows.

Backfill after a cents bug, with the warehouse still online

A rounding bug stored truncated cents for eight open days. The fix is deployed in the parse function. A backfill creates eight runs, max active runs 2, learner dimension serialized. Each fact merge is idempotent and touches only those order_line_ids. Closed months are absent from the list, so September does not move. The weekly finance aggregate is in the graph and reruns for the affected week. During the backfill, interactive queries slow down. The pool of four warehouse slots leaves two slots for analysts because each fact task requests two, and only one fact task runs even though two pipeline runs are active. That is a resource decision expressed as pool slots, agreed with the warehouse owner before the backfill starts. The audit log says who launched it and which code version ran. A laptop loop would have used the deployer's credentials, no pool, and no record.

A data trigger between platform jobs

The experiment team owns a feature table built from fact_order_line. They do not know or care that finance's deadline is 07:00. They care that their job runs after a successful publish and does not run on a failed reconcile. A data trigger on the freshness of analytics.fact_order_line expresses that. A time trigger at 07:30 expresses a hope that publish usually made it. When revenue is late and then succeeds at 08:10, the data trigger still fires. The 07:30 cron has already skipped or, worse, has run on yesterday's leftover table and stamped today's run id on stale features. Cross-team dependencies want data triggers. Clock deadlines want time triggers plus an explicit miss. Most production platforms need both, on different edges.

Best Practices

• Draw the graph before you pick a product. Edges are success dependencies.
• Pass the data interval into every task. Forbid date.today() in the data plane.
• Treat execution as at-least-once. Keep merges idempotent and side effects out of retrying tasks.
• Retry transient failures a small number of times. Do not retry reconciliation mismatches.
• Set timeouts from observed runtimes.
• Cap active runs, and use pools for shared resources. Serialize type-2 dimension loads.
• Give the pipeline one publish task that records freshness.
• Write the SLA in business dates and timezone, and alarm on an earlier SLO.
• Backfill through the orchestrator, excluding frozen periods, and include downstream aggregates in the graph.
• Store attempt history and log locations. Do not mark failures successful by hand.
• Keep payloads out of the control database. Pass locations and counts.
• Name an owner for the graph and an owner for the metric definition.
• Prefer a data trigger for cross-team predecessors and a time trigger for an external deadline.
• When you later move cleanse to Spark, keep this graph. Change the worker, not the edges.

Common Mistakes and Anti-Patterns

Computing the business date from the wall clock. Late runs and backfills process the wrong day, sometimes twice, sometimes not at all. Pass the interval.

A cron entry per task, ordered by hoping the minutes line up. The fact job is scheduled at 05:40 because the dimension job "usually" ends at 05:30. One slow day publishes facts against old dimensions. Draw an edge.

Overlapping runs with no lock. Two merges deadlock or interleave. Cap active runs and take a pool slot.

Infinite retries. A poison file occupies a worker all night. Cap retries and page on the final failure.

Retrying the reconcile task until it passes. Someone weakens the query, or a timeout becomes a false green. Zero retries on business-rule failures.

Backfill from a laptop. No state, no pool, no frozen-period guard. Create runs in the orchestrator.

Several successful leaves. "Did it finish?" depends on who you ask. End on one publish task.

Stuffing the file into the orchestrator payload. The control database grows, workers time out passing messages, and secrets end up in task arguments. Pass a path.

Using orchestration state as a business ledger. Green tasks are not revenue. The ledger is fact_order_line plus ops.dataset_freshness.

A cycle made of "just this once" cross-calls. The graph cannot schedule. Split the tasks or sequence them across runs with a data trigger.

Marking a failed publish successful so the dashboard stays green. You have published a lie. Skip with a reason, or fail.

Troubleshooting and Design Review

Design review questions:

1. What is the data interval of one run, in which timezone, and which parameter carries it?
2. Which edges are required for correctness, not just for speed?
3. What is idempotent on a second attempt, and which side effects are outside the retry path?
4. Which failures retry, and which page immediately?
5. What pool and active-run cap protect the warehouse?
6. What exact condition does publish_freshness write, and what is the SLO time?
7. How does a seven-day backfill behave, including frozen dates and aggregates?
8. Can a teammate implement this graph in Airflow without inventing a business rule?

Interview Questions and Answers

Q1. What is the orchestrator's job, and what should it refuse to do?

It decides eligibility, order, retries, concurrency, and recorded state for tasks. It passes a data interval and a run id and stores status and small counts. It should refuse to own business rules: parsing cents, choosing a dedup winner, or computing revenue. Those belong in the data plane, where they can be tested and later moved to another engine. A scheduler script that also transforms data becomes untestable and unportable.

Q2. Why is a directed acyclic graph a better model than a list of cron times?

Edges express "facts wait for dimensions" even when a task runs long. Cron times express a hope about duration. A cycle cannot be scheduled, which is useful: it forces you to break a circular design. Fan-out and fan-in are visible. A list of clock times hides them until the morning the fact load starts too early.

Q3. Explain wall clock, data interval, and logical date.

The wall clock is when the process actually runs. The data interval is the business period that run is responsible for, such as the UTC day being loaded. The logical date is the scheduler's name for that interval, shared by every task in the run. A job that derives the business date from the wall clock mishandles late starts, backfills, and daylight-saving boundaries. The interval should arrive as a parameter.

Q4. Orchestrators often deliver at-least-once execution. What do you build in response?

Tasks whose second execution with the same interval leaves the same business state. For TechLearningPro that is the order_line_id merge that updates only when the row hash changes. Exactly-once side effects are not a promise to demand from a scheduler that can crash after a commit and before it records success. Non-idempotent effects, such as sending mail, stay outside the retrying task.

Q5. When should a task retry, and when must it not?

Retry transient infrastructure faults: connection drops, lock timeouts, a file that is a minute late if the task is the landing wait. Cap the count and back off. Do not retry a deterministic parse error or a revenue reconciliation mismatch. Those are data outcomes. Retrying them wastes slots and can tempt someone to weaken the check until it passes.

Q6. What is a backfill, and what has to be true before you start one?

A backfill reprocesses historical data intervals through the orchestrator, one run per interval, with state and concurrency limits. The code that fixes the bug is already deployed. Writes are idempotent. Frozen periods are excluded. Downstream aggregates are in the graph or listed explicitly. A loop from a laptop is not a backfill. It is an untracked write against the warehouse.

Q7. A task exits zero at 09:00. Did you meet a 07:00 freshness SLA?

You met a process check and missed the promise. The SLA is about the published business date being ready by 07:00 UTC with a zero reconciliation difference. Record that in ops.dataset_freshness from a single publish task, and alarm on an earlier SLO. Green task state at 09:00 is a late pipeline, which is a different incident from a failed reconcile, and both are different from a successful empty manifest.

Q8. When do you use a time trigger, a data trigger, and a wait-for-data task?

A time trigger fits an external deadline: start at 06:00 UTC because the report has a clock. A data trigger fits a predecessor you can observe: run the feature job when fact_order_line publishes, even if that is 08:10. A wait-for-data task fits a producer you do not control, such as the reseller file, with a timeout aligned to the SLO so the wait does not hang until the next day. Polling forever is not a dependency. It is an outage.

Q9. Why can two tasks with no edge between them still be forbidden from running together?

The graph says they do not depend on each other's output. The resource they share may still allow only one heavy writer. Pools encode that limit: a single slot for type-2 load_dim_learner, a handful of slots for warehouse writes. Without a pool, a backfill with a wide graph will launch every date's merge at once and stall the database. Edges and pools answer different questions.

Q10. What state must you store for an auditable run?

The definition version, the interval, who triggered the run, each task attempt's state, try number, start and end, log location, and the small counts the task returned. You also store freshness separately as business data. Editing a failed reconcile to success without a skip reason destroys the audit. The control plane is the history of decisions, not a sticker you apply to the dashboard.

Q11. How should a type-2 dimension load behave during a multi-date backfill?

It should be serialized. Two runs closing "the current row" at the same time will corrupt effective dates. Facts for different dates may run in parallel only if they do not rewrite that shared dimension and their merges are confined to their own keys. TechLearningPro puts the learner dimension task in a pool of one and caps active fact merges so analysts keep some database capacity.

Q12. What should you hand to the Airflow implementation in the next lesson?

The graph and the success rule on each edge, the data interval and timezone, the catch-up policy, the retry and timeout table, the pools and active-run cap, the idempotent task entry points, the freshness row the publish task writes, and the frozen-period rule for backfills. Airflow will name these as DAGs, operators, pools, and datasets. It should not be where you discover that facts depend on dimensions.

Hands-On Exercises

Exercise 1. Draw the graph and the clocks

Problem. Specify the daily warehouse pipeline on paper or in Markdown.

Tasks.

1. List tasks and edges for land, cleanse, both dimensions, fact, reconcile, and publish.
2. Write the data interval for the run that starts at 06:00 UTC on 2026-03-02.
3. Mark which tasks may run in parallel and which pool prevents parallel learner-dimension loads.
4. Define the freshness SLA in one sentence a finance partner can read.
5. Say what happens if the reseller file is missing at 06:20.

Acceptance. A fact load cannot start before both dimensions and the cleanse for that interval. The business date is 2026-03-01, not "whenever the job runs."

Exercise 2. Backfill and retry policy

Problem. A cents bug affected 3 through 9 March 2026. September is frozen. The reconcile task failed yesterday on a real mismatch.

Tasks.

1. List the runs you will create and the ones you will refuse.
2. Set retries for land, cleanse, fact load, and reconcile, with a one-line reason each.
3. State the active-run cap and the dimension pool during the backfill.
4. Name the downstream aggregate that must rerun and the edge that forces it.
5. Describe the attempt history you expect if the fact load hits a lock timeout once and then succeeds.

Rubric.

Lesson Summary

Orchestration is the control plane that runs an acyclic graph of tasks against a named data interval, stores every attempt, retries only the failures that deserve it, and limits how much work hits a shared database. The data plane still owns cents, grain, and SCD history. Because execution is at-least-once, the idempotent merge you already built is part of the orchestration design, not a separate courtesy.

Cron can start a process. It cannot express "facts after dimensions," a seven-day backfill, or a 07:00 freshness promise. A workflow engine can. The next lesson implements this graph in Apache Airflow 2.x: the same edges, the same interval, and a metadata database that stores the states this lesson listed. The business rules do not get redesigned on the way in.

Further Reading

• cron, the standard time-based job syntax. Useful as a contrast: a pattern and a process, and no graph. Any local crontab manual will do.
• Kimball and Ross, The Data Warehouse Toolkit, on load order: dimensions before facts. The graph in this lesson is that rule made operational.
• NIST or your finance partner's own control language for what a published number means. The freshness row should be something an auditor can re-read.
• TechLearningPro, Data Cleansing and Transformation, for the idempotent merge this control plane is allowed to retry.
• Next: Apache Airflow Architecture and DAGs, which maps runs, tasks, pools, and intervals onto Airflow 2.x without moving revenue logic into the DAG file.
• Later, Big Data Fundamentals: when cleanse no longer fits one process, the graph stays and the worker changes.