The Daily Ingestion Pipeline, End to End

Level 8: Batch Pipelines & Orchestrationhard32 minend-to-end pipeline compositionstaging layeridempotent partition loadrun audit logscoped idempotency contractdesign articulation

Build the whole daily pipeline in one script, stage then load then audit, and rehearse the spoken design answer against a written model answer.

Composing the parts

Every component in this level has been built on its own: the parameterized run date, the deduplicated batch, the partition replacement, the manifest, the audit. This lesson puts them in one script, in the shape the most common pipeline-design question asks for.

Three stages, and the boundaries between them are the point.

Stage. Land the run's slice of the extract into stg_orders_daily, rebuilt from scratch every run. Staging is where dirt is absorbed: the cancelled orders are filtered out here, and the order the extract redelivered twice is collapsed to one row here. Nothing downstream should ever have to know those problems existed.
Load. Replace the run's partition in fct_orders_daily from staging. Delete the day, insert the day. This is the idempotent write, and it is the reason a retry is boring.
Audit. Append one row to pipeline_audit_log recording what this run loaded. That is how you answer "did it run, and what did it write" without opening the orchestrator, and it is the table that catches a green run that wrote zero rows.

Order of evaluation

run_configthe injected run_date, never a clock
stagefilter cancelled, dedupe the redelivery
loadreplace the run_date partition
auditappend one row per run

The daily pipeline. The highlighted stage is the one people skip, and it is where every dirty-data rule belongs.

The contract the audit log forces

Notice what the third stage does to the grading. Staging and the fact must be byte for byte identical after two runs. The audit log must grow after two runs, because that is what a ledger does. So the idempotency contract has to name the tables it covers: staging and the fact, not the log.

That is not a grading trick, it is the production distinction. Some tables are a projection of the current truth and you promise they are stable. Others are an append-only record of history and their whole value is that they keep growing. Confusing the two is how people end up truncating their own audit trail to make a check pass.

Design rehearsal

Attempt the build before you read the next part. The written model answer below is worth much more after you have tried to compose the script yourself, and reading it first will cost you the retrieval practice that makes it stick.

Check yourself

An interviewer says: design a daily pipeline that moves orders from a Postgres database into the warehouse. What is the first thing your answer should establish?

The full source schema, column by column, so nothing is ambiguous laterA scheduled run that is responsible for one date, with that date injected as a parameter rather than read from the clockThe exact warehouse you would use and why it beats the alternativesWhether the pipeline should be streaming instead, since streaming is more modern

Here is the full model answer to "design the daily pipeline from Postgres, an API, or CSV drops to the warehouse", in five beats. Say them in this order.

Orchestrator. A scheduled DAG with a daily data interval. Each run is responsible for exactly one date, and that date arrives as a parameter: an Airflow logical date, an ADF trigger window, a Glue job parameter. Never datetime.now(), because a job that reads the clock can only ever produce today's answer and can never be rerun.
Extraction with a watermark plus lookback. Pull rows newer than the stored watermark minus a lookback sized to the lateness you have actually measured. From an API, page through and land the raw payload before you parse it. From CSV drops, land the file as it arrived and key rows on filename plus row number so a redelivered file is recognizable.
Idempotent sink. For a partitioned fact, delete the run's partition and insert it. For a dimension, upsert on the business key. Then say the sentence out loud: at-least-once delivery plus an idempotent sink is effectively exactly-once. True exactly-once delivery does not exist, and knowing that is the point.
Backfill story. The same job, run once per date from a manifest, with the downstream aggregates recomputed for the affected windows. Bounded to the broken range. Never an unbounded catchup, which is how a backfill takes production down.
Two failure modes, raised before you are asked. Silent success: the run is green and wrote zero rows, caught by comparing this run's volume against the task's own history in the audit log. And late data below the watermark, caught by an anti-join audit and healed by a rolling reprocessing window.

Common mistake: describing the happy path beautifully and stopping. The flow is the junior bar; every prepared candidate has it.

Interview nuance: the junior bar is a sensible end-to-end flow with the right vocabulary. The separator is beat five. Naming the failure modes before the interviewer asks is the single clearest senior signal in a pipeline-design question, and it costs you two sentences.

On a real platform this differs. The audit log is your orchestrator's own run-results table, or dbt's run_results.json loaded into the warehouse after every invocation, and the staging layer is a schema (or a database) rather than a table-name prefix. The three stages usually become three tasks in the DAG so each one can be retried on its own, which is the atomic-task rule: separate extract, transform, and load so a failure restarts the smallest possible unit.

Sample data for this example

CREATE TABLE run_config (
  pipeline TEXT,
  run_date TEXT   -- the scheduler's logical date; the pipeline is responsible for this day only
);
INSERT INTO run_config (pipeline, run_date) VALUES
  ('orders_daily', '2026-03-14');

CREATE TABLE src_orders (
  order_id    INTEGER,
  customer_id INTEGER,
  amount_usd  REAL,
  status      TEXT,    -- placed | shipped | delivered | cancelled
  updated_at  TEXT
);
INSERT INTO src_orders (order_id, customer_id, amount_usd, status, updated_at) VALUES
  (8101, 401, 120.00, 'placed',    '2026-03-12 08:10:00'),
  (8102, 402,  75.50, 'shipped',   '2026-03-12 12:35:00'),
  (8103, 403, 240.00, 'placed',    '2026-03-12 17:20:00'),
  (8104, 404,  59.90, 'delivered', '2026-03-12 21:05:00'),
  (8105, 405, 310.25, 'placed',    '2026-03-13 07:45:00'),
  (8106, 406,  88.00, 'shipped',   '2026-03-13 10:15:00'),
  (8107, 407, 145.75, 'placed',    '2026-03-13 14:50:00'),
  (8108, 408,  62.40, 'delivered', '2026-03-13 18:30:00'),
  (8109, 409, 199.00, 'placed',    '2026-03-13 22:40:00'),
  (8110, 410, 130.00, 'placed',    '2026-03-14 06:20:00'),
  (8111, 411,  95.60, 'cancelled', '2026-03-14 07:55:00'),
  (8112, 412, 275.00, 'shipped',   '2026-03-14 09:30:00'),
  (8113, 413,  48.25, 'placed',    '2026-03-14 11:10:00'),
  (8114, 414, 162.80, 'cancelled', '2026-03-14 13:45:00'),
  (8115, 415, 210.50, 'placed',    '2026-03-14 15:05:00'),
  -- the same order redelivered by a partial extract; only the newest row may survive staging
  (8115, 415, 210.50, 'shipped',   '2026-03-14 16:40:00');

CREATE TABLE stg_orders_daily (
  order_id    INTEGER,
  dt          TEXT,
  customer_id INTEGER,
  amount_usd  REAL
);

CREATE TABLE fct_orders_daily (
  order_id    INTEGER,
  dt          TEXT,
  customer_id INTEGER,
  amount_usd  REAL
);
INSERT INTO fct_orders_daily (order_id, dt, customer_id, amount_usd) VALUES
  (8105, '2026-03-13', 405, 310.25),
  (8106, '2026-03-13', 406,  88.00),
  (8107, '2026-03-13', 407, 145.75),
  (8108, '2026-03-13', 408,  62.40),
  (8109, '2026-03-13', 409, 199.00),
  -- two rows a half-finished earlier run left behind on the current partition
  (8110, '2026-03-14', 410, 999.00),
  (8111, '2026-03-14', 411,  95.60);

CREATE TABLE pipeline_audit_log (
  pipeline    TEXT,
  run_date    TEXT,
  table_name  TEXT,
  rows_loaded INTEGER   -- append-only: one row per run, on purpose
);

Worked example (SQL)

-- What staging must absorb before anything reaches the fact: the run's own day only,
-- cancelled orders dropped, and the redelivered order collapsed to its newest row.
SELECT s.order_id, s.status, s.updated_at,
       CASE
         WHEN date(s.updated_at) <> (SELECT run_date FROM run_config WHERE pipeline = 'orders_daily')
           THEN 'other partition (not this run)'
         WHEN s.status = 'cancelled' THEN 'filtered by staging (cancelled)'
         WHEN s.updated_at < (SELECT MAX(s2.updated_at) FROM src_orders s2 WHERE s2.order_id = s.order_id)
           THEN 'superseded redelivery (older row for the same order)'
         ELSE 'stages'
       END AS staging_decision
FROM src_orders s
ORDER BY s.updated_at;

Apply

Your turn

The task this lesson builds to.

Write a script that stages the run_date rows from src_orders into stg_orders_daily excluding cancelled orders and keeping one row per order_id, replaces the run_date partition of fct_orders_daily from staging, and appends one pipeline_audit_log row recording the rows loaded.

The run's date is the run_config row for 'orders_daily', so read it rather than typing a date. A row belongs to the run when date(updated_at) equals that date; where an order_id appears twice, keep the newest updated_at. Staging holds (order_id, dt, customer_id, amount_usd) and must be rebuilt from scratch every run. The audit row is ('orders_daily', run_date, 'fct_orders_daily', rows loaded). The grader runs your script twice and compares the contents of stg_orders_daily and fct_orders_daily only, because the audit log grows one row per run by design.

2 hints and 7 automated checks are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, plus 2 bonus drills.

Write a script that leaves stg_orders_daily and fct_orders_daily correct for the run_config run date and appends one pipeline_audit_log row, given a source extract that redelivers the previous run's rows.

No steps are given this time. The goal state is: staging holds exactly the run date's non-cancelled orders, one row per order_id with the newest updated_at winning; the fact's run-date partition matches staging; every other partition of the fact is unchanged; and the audit log gains one row naming this run and the number of rows loaded. The grader checks that goal state, then runs your script a second time and checks that stg_orders_daily and fct_orders_daily are unchanged.

7 automated checks are waiting in the workspace.