Lesson · ⏱ 8 min read

🚰 Data pipelines: ETL, Airflow, dbt and Spark

Q: Yesterday's pipeline failed halfway. Why does it matter that it's idempotent?

You can safely re-run it without duplicating data. Re-runs are normal in data work, and idempotency prevents double-counting.

🏠 Real-life example

Mumbai's dabbawalas collect lunchboxes from homes, sort them at stations, and deliver them to offices, on schedule, every day, almost without mistakes. A data pipeline does the same for data: extract it from apps, transform and clean it, and load it where analysts and models need it, reliably and on time.

💡 In simple words

ETL / ELT means extract (from databases, APIs, files), transform (clean, join, aggregate) and load (into a warehouse or lake). Airflow orchestrates the steps: a DAG defines tasks, their order, schedules, retries and alerts. dbt writes transformations as tested SQL models inside the warehouse. Spark processes data too big for one machine by splitting work across a cluster. Good pipelines are idempotent (safe to re-run), monitored for freshness, and documented, because every AI system downstream depends on them.

🎬 See how it works

Like Mumbai's dabbawalas: every night, data is picked up from sources, cleaned and joined, then delivered to the warehouse that dashboards and models use.

🪜 Step by step

Extract: pull data from sources (databases, APIs, files) on a schedule or when it changes.
Load the raw data into a warehouse or lake unchanged, so you can always rebuild.
Transform with SQL (dbt) or Spark into clean, modelled tables.
Orchestrate the steps with Airflow (or similar): order, schedule, retries and alerts.
Make each run idempotent by processing one date partition at a time and overwriting it.

🔑 Key terms

ETL / ELT: Extract, transform, load: moving and shaping data.
DAG: A graph of tasks with dependencies, run on a schedule (Airflow).
Idempotent: Running it twice gives the same result as running it once.

👀 See it in action

python

from airflow.decorators import dag, task
from datetime import datetime

@dag(schedule="0 2 * * *", start_date=datetime(2026, 1, 1), catchup=False)
def daily_orders():
    @task(retries=3)
    def extract(): ...          # pull yesterday's orders from the app DB
    @task
    def transform(rows): ...    # clean, dedupe, join with customers
    @task
    def load(df): ...           # write to warehouse table fact_orders
    load(transform(extract()))

daily_orders()

Runs every night at 2 am with retries. The pattern behind most data platforms.

⚠️ Common mistakes to avoid

Appending data on every run, so a retry doubles the numbers.
No tests between steps, so bad data quietly reaches dashboards and models.

✅ Quick check

Yesterday's pipeline failed halfway. Why does it matter that it's idempotent?

You can safely re-run it without duplicating data
It doesn't need a schedule
It runs faster

Show answer

A. You can safely re-run it without duplicating data: Re-runs are normal in data work, and idempotency prevents double-counting.

🙋 Questions learners ask

Spark or SQL?

If your warehouse handles the volume, SQL with dbt is simpler. Use Spark for very large data, complex processing or unstructured data.

What changes for AI pipelines?

More unstructured data (PDFs, emails), steps that call LLMs or embedding models with rate limits and costs, and vector indexes to keep fresh.

📌 Remember

Extract → transform → load, orchestrated, tested, re-runnable and monitored.

📖 Words from this lesson (AI dictionary)

Learn this as part of a full course

Track your progress, take the quick checks and follow the lessons in the right order. This lesson is part of:

Data Engineer (AI focus)

Continue in the course