dbt vs SQL Scripts

Before dbt became popular, data teams managed transformations using standalone SQL scripts. These scripts worked, but they created serious problems as teams and data volumes grew. This topic compares both approaches so you understand exactly why dbt exists and when to use it.

The Old Way: Raw SQL Scripts

A typical analytics team without dbt might have a folder on a shared drive that looks like this:

/sql_scripts/
  monthly_revenue.sql
  monthly_revenue_v2.sql
  monthly_revenue_FINAL.sql
  monthly_revenue_FINAL_USE_THIS.sql
  customers_clean_john.sql
  customers_clean_mary.sql
  orders_june_FIXED.sql

Nobody knows which file is current. Nobody knows the order to run them. Somebody always runs the wrong file on the first day of the month.

Side-by-Side Comparison

Dependency Management

SQL Scripts: You manually decide which script to run first. You write notes in a README or rely on memory. One forgotten step breaks the entire pipeline.

dbt: dbt reads ref() calls and builds the dependency graph automatically. It always runs models in the correct order. You never manage run order manually.

SQL Scripts approach:
  Step 1: run customers.sql     ← must remember this order
  Step 2: run products.sql      ← must remember this order
  Step 3: run orders.sql        ← depends on 1 and 2

dbt approach:
  You write ref('customers') inside orders.sql
  dbt figures out the order automatically

Version Control

SQL Scripts: Teams store SQL files in shared folders or email them around. Tracking who changed what requires opening every file and reading it.

dbt: A dbt project is a folder you check into Git. Every change has a commit message, an author, and a timestamp. You can roll back to any previous state in seconds.

Testing

SQL Scripts: You write manual checks like SELECT COUNT(*) FROM orders WHERE customer_id IS NULL and look at the number yourself. If you forget to run the check, bad data reaches the dashboard.

dbt: You define tests in a YAML file. Running dbt test executes all checks automatically and fails loudly if any check finds problems.

# dbt test config in schema.yml
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - not_null
          - unique

Documentation

SQL Scripts: Documentation lives in comments inside SQL files or in a separate Word document that quickly becomes outdated. Nobody reads it.

dbt: You write descriptions for models and columns inside YAML files. Running dbt docs generate produces a full website with a lineage graph, search, and column-level descriptions.

Reusability

SQL Scripts: If you need the same logic in two scripts, you copy and paste. When the logic changes, you must update every copy. One missed copy causes inconsistent numbers.

dbt: You write a model once. Any other model that needs the same data uses ref() to pull from it. Change the source model and all downstream models automatically pick up the change.

Environment Management

SQL Scripts: Running scripts in development vs production requires manually changing schema names in every file. A mistake writes development data to production.

dbt: You define dev and prod targets in profiles.yml. dbt automatically prepends the correct schema to all table names based on your current target. Switch environments with one command flag.

dbt run --target dev    → writes to dev_schema.stg_orders
dbt run --target prod   → writes to prod_schema.stg_orders

A Real Transformation: Both Approaches

Suppose you need a clean orders table that joins raw orders with customers and filters out cancelled orders.

SQL Script Approach

-- File: clean_orders.sql
-- Run AFTER clean_customers.sql !!! (remember this)
-- Updated by: John on 2024-03-15

DROP TABLE IF EXISTS analytics.clean_orders;

CREATE TABLE analytics.clean_orders AS
SELECT
    o.order_id,
    c.name AS customer_name,
    o.amount
FROM raw.orders o
JOIN analytics.clean_customers c   -- hardcoded table name
  ON o.customer_id = c.customer_id
WHERE o.status != 'cancelled';

dbt Approach

-- File: models/fct_orders.sql
-- No comments about run order needed
-- No DROP TABLE needed
-- No hardcoded schema names

SELECT
    o.order_id,
    c.name AS customer_name,
    o.amount
FROM {{ ref('stg_customers') }} c   -- dbt resolves this
JOIN {{ source('raw', 'orders') }} o
  ON o.customer_id = c.customer_id
WHERE o.status != 'cancelled'

The dbt version is shorter, safer, and self-documenting. dbt handles the DROP and CREATE logic, resolves all table names, and enforces run order.

When SQL Scripts Are Still Acceptable

SQL scripts are fine when:

You run a one-time analysis that you will never repeat
Your team has only one analyst who works alone
Your warehouse has fewer than ten total transformation steps

Once a team grows beyond two people, runs the same transformations repeatedly, or needs to trust their data quality, dbt becomes the better choice. The upfront cost of learning dbt pays back quickly through saved debugging time and fewer data quality incidents.

Summary Table

Feature              SQL Scripts      dbt
-------------------  -----------      ---
Run order            Manual           Automatic (DAG)
Version control      Difficult        Git-native
Testing              Manual           Built-in
Documentation        Manual           Auto-generated
Environment switch   Error-prone      One flag
Reuse logic          Copy-paste        ref() function
Debugging            Read files        Compiled SQL in target/

Previous lesson

Back to course

Next lesson