dbt + Airflow, explained like you've only ever written SQL scripts

A data analyst I work with had a folder. You know the folder. final_report.sql, final_report_v2.sql, final_report_USE_THIS_ONE.sql. Every morning he opened his SQL client, ran four queries in a specific order he kept in his head, copied results into a fifth, and pasted the output into a spreadsheet the leadership team read at 9am. It worked — right up until the Monday he was off sick and nobody else could reproduce it.

That's the conversation that brings most people to data engineering. Not "I want to learn Airflow." It's "this thing I do by hand has become load-bearing and I'm scared of it." So let's fix that fear with two tools, explained for someone whose entire data career has been writing SQL by hand. No prior pipeline experience assumed.

The short version: dbt makes your SQL trustworthy. Airflow makes it run without you. They solve two genuinely different problems, and the magic is in how unglamorous each one is once you see it.

First, the one-paragraph theory: ETL vs ELT

You'll hear both. ETL — Extract, Transform, Load — is the old way: pull data out, reshape it on some middle server, then load the clean result into the warehouse. ELT — Extract, Load, Transform — flips the last two: dump the raw data into the warehouse first, then transform it inside the warehouse with SQL. Modern warehouses (BigQuery, Snowflake, Databricks, Postgres at smaller scale) are fast and cheap enough that this won. dbt is built for the T in ELT — it assumes your raw data already landed somewhere, and its whole job is turning that raw stuff into clean, tested tables using SQL you already know how to write. That's the entire mental model. Hold onto it.

What dbt actually does

dbt looks almost suspiciously simple at first. You write SELECT statements. That's it. You don't write CREATE TABLE, you don't manage INSERTs, you don't write DDL. You write a query that describes what a table should contain, and dbt handles building it.

But three things turn that into something worth adopting:

1. Models with dependencies. Each .sql file is a "model." When one model needs another, you reference it with {{ ref('other_model') }} instead of hard-coding a table name. dbt reads all those refs and works out the order to build everything — the dependency graph my analyst friend was keeping in his head. You stop remembering "run query 2 before query 4." dbt knows.

2. Tests. You can assert things about your data — this column is never null, this ID is unique — and dbt checks them every run. Ships with not_null, unique, accepted_values, and relationships out of the box. This is the part that would've saved that analyst: the day an upstream system started sending duplicate order IDs, a test fails loudly instead of a wrong number sliding into the 9am spreadsheet.

3. Documentation and lineage. Because dbt understands the dependency graph, it generates a lineage diagram and docs from your project for free. New teammate asks "where does revenue come from?" — there's a clickable map instead of a shrug.

One bit of housekeeping that trips people reading older tutorials: dbt comes in two flavours. dbt Core is the free, open-source command-line tool — that's what we'll use here, and it's what runs on your machine or your own server. dbt Cloud is dbt Labs' paid, managed service with a browser IDE and built-in scheduling. For learning, and for plenty of production setups, Core plus your own scheduler (hello, Airflow) is completely legitimate. You are not missing anything essential by starting with Core.

A tiny worked example: source → staging → mart

Let me make this concrete with the smallest real pipeline I can. Imagine one raw table that landed in your warehouse: raw.orders. We'll build a clean staging model, then a small "mart" that aggregates it — the standard dbt layering.

Staging model — models/staging/stg_orders.sql. Staging models do light cleanup: rename columns, fix types, nothing fancy.

-- models/staging/stg_orders.sql
select
    id              as order_id,
    customer_id,
    cast(amount as numeric)        as amount,
    cast(created_at as timestamp)  as ordered_at
from {{ source('raw', 'orders') }}
where amount is not null

Notice {{ source('raw', 'orders') }} — that points at the raw table we declared as a source. (Sources get defined once in a YAML file; I'm keeping the focus on the flow here.)

Mart model — models/marts/daily_revenue.sql. This is the business-facing table — the thing the 9am spreadsheet would read.

-- models/marts/daily_revenue.sql
select
    date(ordered_at)    as order_date,
    count(*)            as num_orders,
    sum(amount)         as revenue
from {{ ref('stg_orders') }}
group by 1

The {{ ref('stg_orders') }} is the whole trick. We never named a physical table. dbt now knows daily_revenue depends on stg_orders, and will always build staging first. Add ten more models and it sorts the order for you.

The test — models/marts/_marts.yml. Here's where we assert the data is sane:

version: 2

models:
  - name: daily_revenue
    description: "One row per day with order count and revenue."
    columns:
      - name: order_date
        data_tests:
          - not_null
          - unique

A quick honest note, because this exact thing confuses people copying from blogs: the property key is data_tests:. It used to be just tests:, and dbt renamed it when unit tests arrived and the old word got ambiguous. Both keys still work, but you can't mix them on the same model — so I use the current data_tests: and save you a future headache.

Now the entire pipeline runs with two commands:

dbt run    # builds stg_orders, then daily_revenue, in dependency order
dbt test   # checks order_date is unique and never null

That's it. No CREATE TABLE, no remembering which query goes first, and a tripwire if the data goes weird. You just turned three loose SQL scripts into a tested, ordered, self-documenting transformation. What you haven't done yet is make it run on its own at 6am. That's the other tool.

What Airflow actually does

dbt builds your tables correctly. It does not decide when to do that — run dbt run and it runs once, right now, because you typed it. Something still has to wake up every morning and type it for you. That something is an orchestrator, and Airflow is the most common one.

Airflow's core idea is a DAG — a Directed Acyclic Graph. Sounds academic; it just means "a set of tasks with arrows showing what runs before what, and no loops." A DAG is a Python file describing your workflow: do this, then that, on this schedule, and here's what to do if a step fails. Airflow handles the scheduling, the retries, the logging, and the "email me if it broke at 3am" — the operational scaffolding around your actual work.

Two version notes worth getting right, because the API genuinely changed:

• Airflow 3 is the current major line. Version 3.0 went generally available in 2025 — the project itself called it the biggest release in its history — and the series has moved on through 3.x releases since. If you're starting today, you're starting on 3.x.
• In Airflow 3, the modern decorators come from the Task SDK: from airflow.sdk import dag, task. Loads of older tutorials show from airflow.decorators import dag, task — that's the 2.x path. Use the SDK import on 3.x and you'll avoid a confusing afternoon.

The 10-line DAG that runs it nightly

Here's the orchestration layer for our dbt project. It does one job: run the models, then test them, every night, and shout if either fails.

from datetime import datetime
from airflow.sdk import dag
from airflow.providers.standard.operators.bash import BashOperator


@dag(schedule="@daily", start_date=datetime(2026, 1, 1), catchup=False)
def nightly_dbt():
    run = BashOperator(task_id="dbt_run", bash_command="dbt run")
    test = BashOperator(task_id="dbt_test", bash_command="dbt test")
    run >> test   # run first, then test


nightly_dbt()

Walk through it, because every line earns its place:

• @dag(...) declares the workflow. schedule="@daily" is the nightly cron; catchup=False says "don't retroactively run for every day since January" (a notorious first-DAG faceplant — leave it False until you mean it).
• BashOperator just runs a shell command — here, the same dbt run and dbt test you typed by hand. One more version note: in Airflow 3, BashOperator lives in the standard provider (airflow.providers.standard.operators.bash), not the old top-level airflow.operators.bash path that 2.x tutorials show.
• run >> test is the arrow. It tells Airflow: do run, and only if it succeeds, do test. That >> is the dependency graph, in one character.

Drop that file in Airflow's dags folder and you have a pipeline that builds and validates your data every night, retries on transient failures, and logs every run — no human, no folder of USE_THIS_ONE.sql, no panic when someone's off sick.

(For bigger projects people graduate from BashOperator to purpose-built integrations like Astronomer's Cosmos, which renders each dbt model as its own Airflow task so a single broken model is obvious. Start with the two-line Bash version — it's honest, and you'll feel exactly when you've outgrown it.)

What this replaces — and when it's overkill

Here's the before-and-after, plainly:

What it replaces, in three words: cron and hope.

But — and I mean this — not everything is a pipeline. If you pull one CSV a month and eyeball it in a spreadsheet, standing up Airflow is like buying a forklift to carry your groceries. The honest threshold is roughly: more than one data source, and it runs on a schedule, and someone downstream depends on it being right and on time. Below that bar, a single tidy SQL script and a calendar reminder is the correct engineering answer, and reaching for this stack is just résumé-driven complexity. Good data engineering includes knowing when not to build the machine.

Recap

• ELT loads raw data into the warehouse first, then transforms it with SQL. dbt owns the T.
• dbt turns SQL scripts into models with automatic dependency ordering ({{ ref() }}), tests (data_tests:), and free docs/lineage. dbt Core is the free open-source CLI; Cloud is the paid managed service.
• Airflow schedules and orchestrates via DAGs. On Airflow 3.x, import decorators from airflow.sdk and BashOperator from airflow.providers.standard.
• A ~10-line DAG runs dbt run >> dbt test nightly with retries and logging — replacing cron + hope.
• One CSV a month is not a pipeline. Match the tool to the load.

If your own version of "the folder" has started keeping you up at night, this is squarely what I help people untangle one-on-one — we'd take your actual scripts and turn them into a small dbt project with a schedule, at whatever pace makes it stick. There's more on how I approach it on my data engineering tutoring page, and the first session is free, so you can find out if it clicks before committing to anything.