dbt Turns a Pile of SQL Scripts Into a Project

Every analytics team has a folder of SQL scripts that build the tables the dashboards read. Nobody is sure which script has to run before which, the one that broke last quarter was fixed by hand in the warehouse and the file was never updated, and the question “where does this column come from” takes an afternoon. dbt is a small tool with one idea for that folder: treat the scripts as a software project. Each query becomes a model that refers to the others by name, the project knows the order, and tests, documentation and a lineage graph come from the same files. This post sets that up end to end on Amazon Redshift, as of early 2025.

A model is a SELECT that knows what it depends on

dbt runs inside the warehouse: it does no transformation itself, it just sends SQL and materialises the results as tables or views. That is the ELT shape, load first and transform in place, and it is why dbt is thin. The project is a directory:

pip install dbt-redshift
dbt init my_dbt_project && cd my_dbt_project

my_dbt_project/
├── models/            # one .sql file per model
├── dbt_project.yml    # project settings
└── profiles.yml       # the warehouse connection (kept out of git)

The connection is a profile; dbt debug proves it works before anything else:

my_dbt_project:
  target: dev
  outputs:
    dev:
      type: redshift
      host: my-cluster.xxxx.region.redshift.amazonaws.com
      port: 5439
      user: my_user
      password: my_password
      dbname: my_database
      schema: analytics
      threads: 4

The raw table dbt reads is declared once, as a source, so the boundary between what dbt owns and what it only reads is explicit:

# models/sources.yml
version: 2
sources:
  - name: raw_data
    tables:
      - name: orders

A model is a SELECT. The first one stages the source under a name the project controls, and everything downstream refers to that name through ref(): a model names its inputs through it, and dbt resolves the name to the right table for the target environment and, more importantly, learns the dependency:

-- models/orders.sql
SELECT * FROM {{ source('raw_data', 'orders') }}

-- models/orders_summary.sql
SELECT
    user_id,
    COUNT(*)          AS total_orders,
    SUM(order_value)  AS total_spent
FROM {{ ref('orders') }}
WHERE status = 'completed'
GROUP BY user_id

dbt run builds the two models in dependency order, orders then orders_summary, and creates both under analytics. The earlier version of this post read from raw_data.orders directly while claiming to use ref(); the claim was right and the code was wrong, and with the hard-coded table dbt would not have known the order.

Tests and docs come from the same file

Next to the model sits a YAML file that says what must be true of it. dbt ships four tests (unique, not_null, accepted_values, relationships) and each is one line:

version: 2
models:
  - name: orders_summary
    description: One row per user who has completed at least one order.
    columns:
      - name: user_id
        tests: [unique, not_null]
      - name: total_orders
        tests: [not_null]

dbt test runs them as queries against the built tables and fails the run when a row violates one, which is the difference between a broken dashboard discovered on Monday and a failed build discovered on Sunday night. The same YAML carries the descriptions, and dbt docs generate && dbt docs serve renders a site with every model, every column, and the lineage graph that ref() built, so “where does this column come from” is a click.

Two other pieces are worth knowing before they are needed. An incremental model processes only new rows on each run instead of rebuilding the table, which is what keeps a large fact table’s build time flat. And because the project is files, it lives in git and runs in CI like any other code: a pull request builds the models and runs the tests against a development schema before it touches production.

Scheduling is someone else’s job

dbt builds; it does not schedule. In production the run is triggered by an orchestrator, and on AWS that is usually Managed Airflow calling either the dbt CLI or dbt Cloud’s API:

from airflow.providers.dbt.cloud.operators.dbt import DbtCloudRunJobOperator

run_models = DbtCloudRunJobOperator(
    task_id="run_dbt",
    dbt_cloud_conn_id="dbt_cloud_default",
    job_id=12345,
)

Airflow is the post on what the orchestrator adds; here it is enough that dbt is one task in that graph, and that its tests failing is what stops the downstream tasks.

Where it stops holding

dbt is SQL. Transformations that need Python, loops over files, or calls to an API belong somewhere else, and the Python-model support that exists runs on a warehouse’s own Python runtime rather than in dbt. It also assumes the raw data is already in the warehouse; getting it there is loading, not dbt’s problem. Within those lines, the question is not whether to use dbt but how soon: the second script that depends on the first is the moment the pile becomes a project.

SQL. Alone. Doesn’t. Scale. Models. Test. Document. Themselves. Lineage. Comes. Free.

References

dbt documentation
dbt-redshift adapter
Airflow on this blog, for the orchestrator around it