Managing SQL Transformations with dbt

Scenario

Example company: the analytics team keeps 60 SQL files in a folder. A cron job runs them in alphabetical order each night. A new model, active_customers.sql, reads from customer_orders, which is built by customer_orders.sql.

What you see in production

• active_customers always reflects yesterday's orders: it runs before the table it reads has been refreshed ("a" comes before "c").
• When one script fails, the following scripts still run on stale inputs and the failure goes unnoticed.
• Nobody knows which dashboards break if a column is renamed.

The naive code

for f in $(ls sql/*.sql | sort); do psql -f "$f"; done      # order = file names, errors ignored

Reproduce it

dbt reads ref('model_name') calls in each model to build a directed acyclic graph (DAG) and runs models in dependency order. The simulation compares alphabetical order with a topological sort.

models = {  # model -> models it reads via ref()
    "active_customers": ["customer_orders"],
    "customer_orders": ["stg_orders", "stg_customers"],
    "daily_revenue": ["stg_orders"],
    "stg_customers": [],
    "stg_orders": [],
}

def stale_reads(order):
    built, stale = set(), []
    for m in order:
        stale += [f"{m} read {dep} before it was rebuilt" for dep in models[m] if dep not in built]
        built.add(m)
    return stale

alphabetical = sorted(models)
print("alphabetical:", alphabetical)
for s in stale_reads(alphabetical):
    print("  stale:", s)

def topological(graph):
    order, done = [], set()
    def visit(m):
        if m in done:
            return
        for dep in graph[m]:
            visit(dep)
        done.add(m)
        order.append(m)
    for m in sorted(graph):
        visit(m)
    return order

dag = topological(models)
print("dependency order:", dag)
print("  stale reads:", len(stale_reads(dag)))

alphabetical: ['active_customers', 'customer_orders', 'daily_revenue', 'stg_customers', 'stg_orders']
  stale: active_customers read customer_orders before it was rebuilt
  stale: customer_orders read stg_orders before it was rebuilt
  stale: customer_orders read stg_customers before it was rebuilt
  stale: daily_revenue read stg_orders before it was rebuilt
dependency order: ['stg_orders', 'stg_customers', 'customer_orders', 'active_customers', 'daily_revenue']
  stale reads: 0

Root cause

Dependencies between transformations existed only in people's heads. Run order came from file names, failures didn't stop downstream steps, and there were no tests or lineage.

The fix

Manage transformations with dbt (or a similar tool such as SQLMesh or Dataform):

• Each model is a SELECT in its own file; dependencies are declared with ref() and raw inputs with source(). dbt builds the DAG and runs models in order, in parallel where possible, and skips models downstream of a failure.
• Tests (unique, not_null, relationships, accepted_values, plus custom SQL) run after models build.
• Incremental models process only new or changed rows, with a merge or partition strategy (see incremental models).
• Code review, version control, environments (dev, CI, prod) and generated docs with lineage graphs.

A typical layering is staging (clean and rename sources one-to-one) → intermediate (joins and business logic) → marts (facts and dimensions for consumers).

Trade-offs

• dbt orchestrates SQL inside the warehouse; extraction, loading and non-SQL steps still need other tools and an orchestrator.
• Incremental models need careful handling of late data and schema changes, and an occasional full refresh.
• Large projects need conventions (naming, layering, ownership) or they become a new tangle.

Detect it in production

• Fail the run, and alert, on test failures; track test failure counts over time.
• Use lineage to see which models and dashboards depend on a column before changing it.
• Track model build times to catch models that need to become incremental.

Key points

• Declare dependencies (ref(), source()) so the tool builds a DAG and runs models in the right order.
• Failures stop downstream models instead of feeding them stale data.
• Test models after each build; use incremental models for large tables.
• Treat transformations as code: review, CI, environments and lineage.