Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 37 ยท Projects and Practice

Project: A Daily ETL Pipeline in Airflow

Build a complete daily ETL DAG: extract, validate, transform, load into a database idempotently, with retries and a quality gate. Runnable pipeline stand-in with SQLite.

In this project you design a realistic daily pipeline: orders arrive as CSV text, are cleaned, loaded into a database table and summarised. You will write the real Airflow DAG (read-only here) and a runnable stand-in that executes the same steps with plain Python and SQLite, so you can see every stage and its data.

Airflow cannot run on this site. The DAG is read-only. The runnable blocks are stand-ins: they reproduce the logic of each task, not Airflow itself.

The plan

flowchart LR
  E["extract"] --> V["validate"] --> T["transform"] --> L["load (idempotent)"] --> S["summary"]

Each arrow is an XCom of a small value (a path or a count); the data itself lives in files and tables.

Requirements:

  1. Runs daily for the logical date (the day of data it covers), never "today".
  2. Idempotent: running a day twice leaves exactly one copy of that day's rows.
  3. Bad rows are separated, and an entirely bad file fails the run instead of loading garbage.
  4. Retries for transient errors and an alert after the final failure (alerting).

The Airflow DAG

Python
from datetime import timedelta
import pendulum
from airflow.sdk import dag, task

default_args = {"owner": "data-team", "retries": 2, "retry_delay": timedelta(minutes=5)}

@dag(
    schedule="@daily",
    start_date=pendulum.datetime(2025, 1, 1, tz="UTC"),
    catchup=False,
    default_args=default_args,
    tags=["etl", "orders"],
    max_active_runs=1,
)
def orders_etl():
    @task
    def extract(ds=None) -> str:
        path = f"/data/incoming/orders_{ds}.csv"
        download_to(path, day=ds)                  # your own helper
        return path

    @task
    def validate(path: str) -> dict:
        good, bad = split_rows(path)               # your own helper
        if not good:
            raise ValueError(f"no valid rows in {path}")
        write_rows(path + ".clean", good)
        return {"clean": path + ".clean", "good": len(good), "bad": len(bad)}

    @task
    def transform(info: dict) -> str:
        out = info["clean"].replace(".clean", ".parquet")
        to_parquet(info["clean"], out)
        return out

    @task
    def load(path: str, ds=None) -> int:
        hook = PostgresHook(postgres_conn_id="warehouse_db")
        hook.run("DELETE FROM orders WHERE order_date = %s", parameters=(ds,))
        return copy_into_orders(hook, path)

    @task
    def summary(rows: int, info: dict):
        print(f"loaded {rows} rows, skipped {info['bad']} bad rows")

    info = validate(extract())
    loaded = load(transform(info))
    summary(loaded, info)

orders_etl()

Notes: ds is the logical date string, taken from the context so reruns are repeatable. load first deletes that day's rows and then inserts, so it can run twice safely. Paths and counts pass through XComs. The helpers (download_to, split_rows ...) are your own code, tested separately (testing).

Running the logic: extract and validate

This stand-in uses an in-memory CSV text. Two rows are broken on purpose: a missing amount and a negative quantity.

Python Stand-in: extract and validate

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The validation step returns counts and keeps bad rows aside. If good were empty, the task raises and the run fails loudly, which is much better than silently loading nothing.

Running the logic: load idempotently and summarise

Now the load, with SQLite standing in for the warehouse. We run the whole day twice and check there are no duplicates.

Python Stand-in: idempotent load and summary

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Because the load deletes the day first, a retry after a crash in the middle of the insert cannot double-count. The same pattern works with MERGE or INSERT ... ON CONFLICT upserts, or by writing to a temporary table and swapping a partition.

Improvements to try

  1. Quality gate: fail the DAG if the bad-row share exceeds 5%. Add the check in validate and see how trigger rules keep summary from running.
  2. Sensor: wait for the file with a reschedule-mode sensor instead of failing when it is late.
  3. Branching: skip the load when the file is a holiday placeholder (branching).
  4. Multiple sources: use task mapping to process one file per store.
  5. Credentials: replace hard-coded paths and hosts with a connection and Variables.
  6. Alert on failure with a notifier and add a freshness check.

Design lessons from the project

  • Pass paths and counts, not data, between tasks.
  • Use the logical date from the context for every date calculation.
  • Make each task idempotent so reruns and retries are safe (next project).
  • Fail early on bad input; do not hide it.
  • Keep helpers in plain modules so they can be unit-tested.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does the load task delete the day's rows before inserting?

    Delete-then-insert for a partition makes the load idempotent.
  2. 2. What should the validate task do when no row is valid?

    Failing early avoids loading garbage and alerts you that the input is broken.
  3. 3. What do the tasks pass to each other in this project?

    XComs hold small messages; the data itself lives in files and tables.
  4. 4. Where should the date for the extract come from?

    Using the logical date makes reruns and backfills read the correct day's data.

Summary

  • A good ETL DAG is small tasks passing paths/counts, driven by the logical date.
  • Validation fails loudly on unusable input and sets bad rows aside.
  • Loading is idempotent (delete-then-insert, upsert or partition swap).
  • Retries, alerts, sensors and mapping harden the pipeline.

Next: Project: Idempotent Backfills