dbt as the de-facto transformation layer

In 2018, the way Razorpay's analytics team built their daily settlement table was a 1,200-line stored procedure inside Postgres, edited by four people, version-controlled by copy-pasting into a Confluence page when someone "did something big". By 2022 the same team had 380 dbt models, a dependency graph, automatic tests on every join key, and a static documentation site that the new joiner could read on day one. The intermediate technology — Airflow Python operators wrapping SQL strings, then Stitch + Looker derived tables, then a homegrown templating engine — all died. dbt did not win because it was clever. It won because it picked four small, boring decisions and refused to add a fifth.

What dbt actually is, in 70 words

You write a SQL SELECT in a file called models/marts/fct_orders.sql. dbt wraps it in CREATE OR REPLACE TABLE {{ this }} AS (...) based on the configured materialisation. It walks ref('stg_orders') calls to build a DAG. It runs the DAG in topological order against your warehouse. It runs declarative tests after each model. It renders Markdown + the DAG into a static site. That's it. There is no execution engine, no scheduler, no UI for query authoring — those are someone else's job.

The narrowness is the product. Earlier transformation tools — Matillion, Talend, Informatica — tried to be the execution engine themselves, the scheduler, and the GUI editor. They have hundreds of features. They lost because each feature became coupling that an analytics team eventually wanted to break.

The four decisions that made it stick

Every analytics-engineering tool before dbt had at least one of these four backwards. dbt got all four right, and the combination is what produced the network effect.

Decision 1 — ref() is the only way to reference another model. You never write FROM analytics.fct_orders in a dbt model. You write FROM {{ ref('fct_orders') }}. dbt compiles this into the fully-qualified name based on the target schema (dev vs prod) and uses it to build the DAG. Why this single decision is load-bearing: it forces every model dependency to be discoverable by static analysis. Without it, the DAG is a guess; with it, the DAG is a deterministic property of your code. Lineage, partial runs, and impact analysis all flow from this one constraint.

Decision 2 — Materialisations are config, not code. Whether fct_orders is a view, a table, an incremental table, or a snapshot is a one-line config change. The SQL stays the same. Why this matters: separating "what the data should be" (the SELECT) from "how it lives in the warehouse" (the materialisation) lets you change physical storage without rewriting business logic. Most pre-dbt tools mixed these and made the rewrite cost prohibitive.

Decision 3 — Tests are declarative, attached to columns. You don't write a test as a Python script. You add tests: [unique, not_null] under a column in schema.yml. dbt compiles this into a SQL query that returns failing rows. The test passes if the query returns zero rows. Why this is the right shape: tests as code drift from the model they test (lives in a different repo, different deploy cadence). Tests as YAML next to the model are version-controlled together, code-reviewed together, and visible in the docs site as part of the model's contract.

Decision 4 — The compiled SQL is your output. dbt compile produces the exact SQL that will run, into target/compiled/. You can open it, copy it, run it directly in the warehouse for debugging. There is no runtime magic the user can't inspect. Why this is anti-magic: tools that hide the generated SQL (looking at you, every BI semantic layer that compiles to undebuggable joins) lose user trust the first time something breaks. The "you can always read the SQL" property kept dbt usable when projects grew past 500 models.

These four decisions, taken together, form a tight kernel. Each one alone would be a feature; together they shape how the project is structured, code-reviewed, and debugged. That is why teams that adopt dbt rarely return to bespoke transformation tooling — the four decisions become institutional muscle memory.

Worked example: building the daily revenue model

A real shape — Razorpay's daily payment-volume rollup, simplified. Six files, three layers (staging → intermediate → marts), one test config. This is the canonical dbt project shape.

# show_compile.py — render what dbt will execute, without a real warehouse.
# Demonstrates ref-resolution, materialisation wrapping, and DAG construction.

from pathlib import Path
import re, json
from collections import defaultdict

PROJECT = {
  "models/staging/stg_payments.sql": """
    SELECT id AS payment_id, merchant_id, amount_paise, status, created_at
    FROM {{ source('raw', 'payments') }}
    WHERE created_at >= '2026-01-01'
  """,
  "models/staging/stg_merchants.sql": """
    SELECT id AS merchant_id, name AS merchant_name, gst_state
    FROM {{ source('raw', 'merchants') }}
  """,
  "models/intermediate/int_captured_payments.sql": """
    SELECT payment_id, merchant_id, amount_paise, created_at
    FROM {{ ref('stg_payments') }}
    WHERE status = 'captured'
  """,
  "models/marts/fct_daily_revenue.sql": """
    SELECT DATE(p.created_at) AS day, m.gst_state,
           SUM(p.amount_paise) / 100.0 AS revenue_inr
    FROM {{ ref('int_captured_payments') }} p
    LEFT JOIN {{ ref('stg_merchants') }} m USING (merchant_id)
    GROUP BY 1, 2
  """,
}

CONFIG = {
  "stg_payments": "view", "stg_merchants": "view",
  "int_captured_payments": "view",
  "fct_daily_revenue": "table",
}

REF = re.compile(r"\{\{\s*ref\(['\"]([^'\"]+)['\"]\)\s*\}\}")
SOURCE = re.compile(r"\{\{\s*source\(['\"]([^'\"]+)['\"]\s*,\s*['\"]([^'\"]+)['\"]\)\s*\}\}")

def compile_model(name, sql):
    deps = REF.findall(sql)
    sql = REF.sub(lambda m: f"analytics_dev.{m.group(1)}", sql)
    sql = SOURCE.sub(lambda m: f"{m.group(1)}.{m.group(2)}", sql)
    mat = CONFIG[name]
    if mat == "view":
        wrapped = f"CREATE OR REPLACE VIEW analytics_dev.{name} AS (\n{sql.strip()}\n);"
    else:
        wrapped = f"CREATE OR REPLACE TABLE analytics_dev.{name} AS (\n{sql.strip()}\n);"
    return deps, wrapped

graph = defaultdict(list)
compiled = {}
for path, sql in PROJECT.items():
    name = Path(path).stem
    deps, wrapped = compile_model(name, sql)
    graph[name] = deps
    compiled[name] = wrapped

# Topological sort
def topo(graph):
    seen, order = set(), []
    def visit(n):
        if n in seen: return
        seen.add(n)
        for d in graph.get(n, []): visit(d)
        order.append(n)
    for n in graph: visit(n)
    return order

print("DAG (model -> dependencies):")
print(json.dumps(dict(graph), indent=2))
print("\nExecution order:")
for n in topo(graph):
    print(f"  -> {n}")
print("\nCompiled SQL for fct_daily_revenue:\n")
print(compiled["fct_daily_revenue"])

# Sample run:
DAG (model -> dependencies):
{
  "stg_payments": [],
  "stg_merchants": [],
  "int_captured_payments": ["stg_payments"],
  "fct_daily_revenue": ["int_captured_payments", "stg_merchants"]
}

Execution order:
  -> stg_payments
  -> stg_merchants
  -> int_captured_payments
  -> fct_daily_revenue

Compiled SQL for fct_daily_revenue:

CREATE OR REPLACE TABLE analytics_dev.fct_daily_revenue AS (
SELECT DATE(p.created_at) AS day, m.gst_state,
           SUM(p.amount_paise) / 100.0 AS revenue_inr
    FROM analytics_dev.int_captured_payments p
    LEFT JOIN analytics_dev.stg_merchants m USING (merchant_id)
    GROUP BY 1, 2
);

The load-bearing pieces:

• {{ ref('stg_payments') }} resolution — the Jinja ref() call is replaced by analytics_dev.stg_payments. In production it would be analytics_prod.stg_payments. Why this enables dev/prod parity: the same code runs against both schemas. Devs can dbt run against their personal sandbox without touching the SQL.
• REF.findall(sql) for DAG construction — the regex pulls out every ref() call to build the dependency list. dbt-core does this with a real Jinja parser, but the principle is the same: the DAG is a static property of the source code.
• CREATE OR REPLACE TABLE vs VIEW — driven entirely by CONFIG[name], not by the SELECT. To change fct_daily_revenue from a table to a view, the analyst changes one line in dbt_project.yml; the SQL is untouched.
• Topological sort — execution order is stg_* then int_* then fct_*. The fct_daily_revenue table is built last because both its dependencies must exist first. dbt also parallelises within levels of the DAG; here stg_payments and stg_merchants would run concurrently.

The full schema.yml that goes alongside, with tests:

version: 2
models:
  - name: fct_daily_revenue
    description: "Daily payment revenue per GST state. Source of truth for finance dashboards."
    columns:
      - name: day
        tests: [not_null]
      - name: gst_state
        tests:
          - not_null
          - relationships:
              to: ref('stg_merchants')
              field: gst_state
      - name: revenue_inr
        tests: [not_null]

dbt test compiles each tests: block into a SQL query that returns offending rows. tests: [not_null] on day becomes SELECT * FROM analytics_dev.fct_daily_revenue WHERE day IS NULL. The test passes if zero rows return. The relationships test on gst_state becomes a left-anti-join checking that every state in fct_daily_revenue exists in stg_merchants. None of this is bespoke — every dbt project on earth uses these four built-in tests.

Where the lineage and docs come from

dbt's static documentation site is generated from three inputs: the YAML descriptions, the column-level metadata it can introspect from the warehouse (information_schema), and the DAG it built from ref() calls. The output is a single-page-app you can host on S3 or GitHub Pages — no server, no auth, no database. New joiner reads it on day one and understands the warehouse better than they would after three weeks of standups.

Common confusions

• "dbt is an ETL tool." dbt is the T. It does not extract from APIs, it does not load raw data into the warehouse. Tools like Fivetran, Stitch, Airbyte, or homegrown ingestion handle E and L; dbt picks up the data once it has landed and transforms it into modelled tables. Confusing this leads people to expect dbt to handle Kafka ingestion or REST API pulls — it doesn't.
• "Incremental models are just views with a WHERE clause." Incremental models are tables that dbt appends to (or merges into) on each run, using the is_incremental() macro to filter out already-processed rows. They require a unique key, a partition strategy, and careful thought about late-arriving data. Treating them as "view-with-filter" produces silent duplication or silent data loss.
• "dbt Cloud is the same as dbt-core." dbt-core is the open-source CLI you run locally or in CI. dbt Cloud is a managed orchestration + IDE + scheduler product on top of dbt-core. Most dbt logic works identically in both, but features like the IDE-based development environment, semantic-layer serving, and managed scheduler are Cloud-only. Adopting dbt-core for $0 is the path most large Indian engineering orgs (Razorpay, Swiggy, Zerodha) take; Cloud is a procurement decision later.
• "dbt tests are unit tests." They are data tests — assertions over rows of actual data in the warehouse. They run after the model is built and verify properties of the output. dbt has separately added "unit tests" (with mock inputs and expected outputs) in 2024, but the original tests: block in schema.yml is data tests, not unit tests. Conflating them produces test suites that don't catch the bugs they were meant to.
• "Macros let you do anything." Jinja macros are powerful enough to be dangerous. A 200-line macro that dynamically generates joins based on metadata is debuggable in theory and unmaintainable in practice. The teams that ship working dbt projects keep macros short, use them for repetitive snippets (generate_schema_name, dedupe_table), and resist using them as a code-generation framework.

Going deeper

Why dbt's compile-then-execute model beats execute-then-trace

Earlier transformation tools (Matillion, Talend) execute SQL fragments and trace dependencies at runtime by parsing query history. dbt does the opposite: it parses ref() calls statically before any SQL runs, builds the DAG up front, and only then executes. This means dbt knows the entire dependency graph before touching the warehouse — so partial runs (dbt run --select fct_orders+) can correctly determine which downstream models to rebuild. Runtime-trace tools cannot do this; they have to rely on heuristics that miss cases. The cost of dbt's approach is that you must use ref() religiously; the benefit is that lineage is a property of the code, not of an execution log.

Incremental models, late-arriving data, and the merge strategy choice

The incremental materialisation is where most Indian-scale dbt projects spend their debugging time. The model has three knobs: unique_key, incremental_strategy (append, merge, delete+insert, insert_overwrite), and the filter inside is_incremental(). A common Razorpay-shape bug: an incremental model on fct_payments with unique_key='payment_id' and is_incremental() filter WHERE created_at > (SELECT max(created_at) FROM {{ this }}). If a payment is captured at 23:59:50 but the row lands in the warehouse at 00:00:30 of the next day, the next dbt run filters on max(created_at) of 23:59:50 — and never picks up the late arrival. The fix is to widen the filter (created_at > max - INTERVAL '1 hour') and rely on merge to dedupe by unique_key. This is the kind of correctness reasoning the materialisation choice forces on you, which is half the value of the abstraction.

What dbt deliberately does NOT include

dbt has no scheduler. The community-blessed pattern is "run dbt from Airflow / Dagster / Prefect / Cron". dbt has no orchestrator across heterogeneous systems — it cannot trigger a Flink job after a model finishes. dbt has no UI for query authoring during development; you write SQL in your editor. dbt has no in-warehouse caching layer — each dbt run re-executes the SELECTs from scratch. Each of these omissions has been argued for as a feature; in practice each was a deliberate "we are not going to be that" decision that kept dbt small and let it integrate cleanly with the ecosystem. dbt Cloud added some of these (a managed scheduler, an IDE) but the open-source core remains narrow.

The semantic layer extension and where it sits

In 2023 dbt acquired Transform (the company behind MetricFlow) and shipped MetricFlow as the dbt semantic layer. This is Build 13's main thread: the metrics-layer registry of wau, wau_paying, revenue_inr_state lives in metrics: blocks of YAML, alongside the models. MetricFlow compiles a metric+dimensions request into SQL at query time, using the dbt project's lineage as the source of joinable tables. This extension is real, but optional — most production dbt projects in Indian orgs as of 2026 do not yet use the semantic layer; they expose the marts directly to BI tools and rely on convention. The metrics-layer adoption curve is the next chapter of dbt's story.

Where this leads next

The metrics-layer view of dbt is the previous chapter (/wiki/the-what-is-an-active-user-problem) — dbt is the substrate that the metrics layer sits on top of, and the registry of versioned metric definitions lives in dbt's YAML files. The cross-tool comparison (/wiki/lookml-cube-metricflow-the-landscape) walks through how MetricFlow compares to Cube and LookML on the seven encoding fields.

For the orchestration layer that runs dbt, see /wiki/airflow-vs-dagster-vs-prefect-the-real-design-differences. For the data-contract pattern that makes dbt staging models reliable, see /wiki/data-contracts-the-producer-consumer-boundary. And for the testing landscape that complements dbt's built-in tests, see /wiki/dbt-tests-great-expectations-soda-the-landscape.

Build 13 is about the layer that sits above transformation and turns table columns into business meaning. dbt is the floor of that layer — every metrics-layer tool either integrates with dbt or competes with it. Understanding why dbt's four design decisions stuck is the prerequisite for evaluating any tool that builds on top of it.

References

• dbt Documentation — How dbt works — canonical introduction; the four-decisions framing is implicit but visible.
• Tristan Handy: The Modern Data Stack — Past, Present, Future — the dbt founder's reading of how dbt fits into the broader stack.
• Claire Carroll: How we structure our dbt projects — the staging→intermediate→marts convention used in the worked example, written by an early dbt Labs engineer.
• dbt Labs: The Analytics Engineering Manifesto — the role-definition essay that paired with the tool to seed the analytics-engineering category.
• Jinja Documentation — the templating engine dbt uses for ref(), source(), and macros.
• /wiki/the-what-is-an-active-user-problem — the previous chapter; the metrics-definition problem dbt's semantic layer addresses.
• /wiki/airflow-vs-dagster-vs-prefect-the-real-design-differences — the orchestrator that triggers dbt runs in production.
• /wiki/dbt-tests-great-expectations-soda-the-landscape — how dbt's built-in tests compare to GE and Soda for data-quality coverage.