Why dbt?

The problem dbt solves

Every analytics team accumulates SQL: a view someone built a year ago, a script that has to run before another script, a “FINAL_v3” table nobody is sure is safe to drop. The SQL itself is usually fine — the problems sit around it:

• Order of operations is manual. The summary table is only right if the reference data refreshed first, which is only right if the feed loaded — and the person who knows the sequence runs it by hand. One step out of order and the numbers are wrong, with nothing to say so.
• Nobody knows what depends on what. Renaming a column means guessing what might break.
• Nothing is tested. A duplicate patient row appears, and the first you hear is a dashboard looking wrong.
• Everything is built end-to-end, every time. Each script runs the whole journey from source to product privately — the cleaning, the lookups, the business logic, all repeated per output and reused by nothing. The same column gets cleaned a dozen different ways across a dozen scripts.
• Everyone works alone. Logic lives in personal worksheets and scripts, so five people hold five slightly different definitions of “current registration”, there is no history of who changed what, and work leaves when its author does.

None of this is anyone working badly. Each script was a sensible answer to a real request, built with the tools available — and if your current setup looks like the list above, it is because that is where every analytics team lands without shared machinery. These are properties of the system, not of the people in it; they only become visible as a problem when the team and the estate grow.

dbt (data build tool) addresses this with two ideas working together. First, a shared codebase: every transformation lives in one git repository, so there is one definition of each concept, full history of every change, and a review step before anything ships. Second, every transformation is a SELECT statement whose dependencies dbt can read — so the order of operations is derived from the code itself, computed fresh on every run, and never something a person has to remember.

What a dbt model actually is

A model is one .sql file containing one SELECT statement. That is the whole definition. This is a real (small) model from our project:

select
    person_id,
    pseudo_nhs_number as sk_patient_id
from {{ ref('raw_csds_bridging') }}

Notice what is missing: no CREATE TABLE, no DROP, no database or schema names. That is because you describe the result; dbt produces the object. When you run dbt run -s stg_csds_bridging, two things happen:

1. Compile. dbt renders the template parts — here, {{ ref('raw_csds_bridging') }} becomes the real database-qualified table name for whichever environment you are in (the DEV__ databases while developing, production after merge).
2. Run. dbt wraps the compiled SELECT in the right DDL and executes it in Snowflake. For this model that means, roughly:

create or replace view DEV__STAGING.CSDS.STG_CSDS_BRIDGING as (
    select
        person_id,
        pseudo_nhs_number as sk_patient_id
    from DEV__STAGING.DBT_RAW.RAW_CSDS_BRIDGING
);

Whether the wrapper is create view or create table, and which database it lands in, comes from project configuration — not from your file. Rebuilding is always safe because models are create or replace: run it again, get the same object again. You can see the compiled SQL for any model with dbt compile.

The {{ ref('…') }} call is the key mechanism — and remember, a model is just another .sql file in the project. So instead of hardcoding a table name, you point at a file: ref('raw_csds_bridging') means “the table that raw_csds_bridging.sql builds, wherever that is”. From those references dbt assembles the full dependency graph (the DAG) and always builds upstream models first.

Where dbt sits in the workflow

In this team the pipeline looks like:

1. Source data lands in Snowflake — in the DATA_LAKE database (the main source) and DATA_LAKE__NCL: SUS, CSDS, OLIDS GP data, reference files.
2. dbt transforms it through five layers, explained later in this sequence, into analytics-ready and published datasets.
3. Everything downstream consumes dbt outputs — dashboards, ad-hoc analysis, semantic views for AI tools.

You are still writing SELECT statements against Snowflake. What changes is that your SQL now lives in a repo where it is ordered, tested, reviewed and rerun automatically every day.

Couldn't we build this ourselves?

A fair question — Snowflake has stored procedures and tasks, and a determined team can replicate much of what dbt does with them: chained procedures, scheduled refreshes, even hand-rolled logging. The problem is not building it once; it is what you have after building it fifty times. Every procedure is bespoke — its own error handling, its own logging table, its own schedule, its own documentation (or none) — and each one adds to a pile that someone has to monitor, debug and remember.

This is the observability argument for dbt: visibility is a property of the platform, not something each author rebuilds. Every model run is logged and timed the same way; selected test results are recorded with their runs; lineage is derived rather than documented; one failure surfaces in one place, with the affected downstream models known immediately. The question “did last night's build work, and if not, what is affected?” has a single answer — not fifty procedures to check one by one.

The bigger picture: the analytics development lifecycle

The deeper idea — the one dbt was built around — is that analytics work follows the same lifecycle as software, often called the analytics development lifecycle (ADLC). It is a loop, not a line: delivering one product surfaces the next question, and each pass around begins with more settled meaning than the last. Every stage has a concrete counterpart in how this team works:

Diagram: dbt Labs' Analytics Development Lifecycle.

• Plan — agreeing the requirement and definitions before writing SQL.
• Develop — models on a branch, built into the DEV__ databases.
• Test — assertions run locally and in CI, before anything merges.
• Deploy — merge to main; changed models deploy to production automatically.
• Operate — scheduled builds rebuild every model on its cadence: daily, weekly, monthly.
• Observe — every run is logged; failures open issues naming the exact failed models.
• Discover — docs, lineage and contracts make what already exists findable.
• Analyze — dashboards and the semantic layer consume the outputs, raising the next question.

Before dbt, much analyst work lives almost entirely in develop — the other stages are manual, fragmented or missing. dbt supplies common machinery and evidence for the rest of the loop, but the work does not happen by itself. People still agree requirements, choose tests, review changes, respond to failures and validate analytical outputs. The rest of this handbook explains how analysts, engineers and domain owners share those responsibilities in this project. Later lessons make the loop concrete through model design, deployment and production observation.

Where the speed comes from

It would be fair to read everything above as overhead — conventions, reviews, tests, a process. So it is worth being direct about why working this way is faster, not slower, after the first week:

• You start from finished work. Demographics, disease registers, geography lookups, waiting lists — hundreds of tested models already exist. A new analysis usually begins by joining two or three ref()s, not by rebuilding a person spine from raw feeds. The weeks that used to go into “assemble the population” collapse into a SELECT.
• The boilerplate writes itself. Source declarations and raw models are generated by scripts; documentation YAML is scaffolded by a command; age bands and organisation-code cleaning are one-line macro calls. The typing you do is the interesting part.
• Mistakes surface in seconds, not days. The editor underlines a broken ref or a missing column as you type; compiling the whole project takes seconds. Compare that with discovering a typo when a scheduled script fails overnight.
• Refreshes stop being your job. Whatever you build reruns on its defined schedule without you. No more Monday mornings re-running a chain of worksheets so a report is current.

The honest cost profile: setup is a slow first day, your first PR is a slow first week — and the everyday loop after that (edit a model, build it, open a PR) is minutes.

What changes for you

In a worksheet you can do anything, immediately — and that freedom is exactly why worksheet logic ends up unordered, untested and unshared. Working in this project means working to its conventions: naming, layers, a review step, tests before merge. Those rules are the team's, not dbt's — dbt is just the machinery that makes them enforceable.

What you get back: your work runs on an agreed cadence without you, failures surface immediately rather than silently, and nobody has to reverse-engineer your logic from a worksheet — including you, six months from now.

And because everyone can see everyone else's code, dependencies are visible in both directions: change something here and you know immediately that it breaks something over there — in lineage, in compile errors, in CI on your pull request — before production is touched, rather than weeks later when a number looks wrong or a dashboard stops refreshing.