dbt for SQL Server Developers: Models, Sources and Tests

For most of my career, transformation logic lived in stored procedures and SSIS packages. Dependencies were in someone's head or a scheduling spreadsheet, tests were manual row counts, and documentation was a Word document that was out of date the week it was written. I've started using dbt recently, and it fixes most of that without asking you to learn a new language. It's still SQL. It just adds the engineering practices around it.

This post introduces dbt from a SQL Server developer's point of view, using an insurance example: building premium reporting tables from a bordereaux feed. The examples use the dbt-databricks adapter, but almost everything is the same on Snowflake or any other warehouse.

What dbt is (and isn't)

• dbt doesn't move data. It runs SQL inside your warehouse or lakehouse, so the data must already be loaded.
• Each model is a SELECT statement in a .sql file. dbt wraps it in the right CREATE TABLE AS, CREATE VIEW or MERGE.
• Models reference each other with ref(), and dbt builds the dependency graph from those references. No more hand-maintained run order.
• Tests and documentation live next to the SQL, in YAML files, and are version-controlled with it.

Setting up

1: pip install dbt-databricks
2: dbt init analytics

Connection details go in ~/.dbt/profiles.yml, outside the project, so credentials never end up in Git:

 1: analytics:
 2:   target: dev
 3:   outputs:
 4:     dev:
 5:       type: databricks
 6:       host: adb-1234567890123456.7.azuredatabricks.net
 7:       http_path: /sql/1.0/warehouses/abcdef1234567890
 8:       catalog: dev
 9:       schema: dbt_dev
10:       token: "{{ env_var('DATABRICKS_TOKEN') }}"
11:       threads: 4

Project structure

I keep a simple three-folder layout:

1: models/
2:   staging/        -- one model per source table: rename, cast, light cleaning
3:   intermediate/   -- joins and business logic
4:   marts/          -- facts and dimensions for reporting

Sources

Raw tables that dbt doesn't build are declared as sources. That gives you one place to change if a table moves, plus freshness checks:

 1: # models/staging/_sources.yml
 2: version: 2
 3: 
 4: sources:
 5:   - name: feed
 6:     database: prod
 7:     schema: raw
 8:     tables:
 9:       - name: premium
10:         loaded_at_field: loaded_at
11:         freshness:
12:           warn_after: {count: 2, period: day}
13:           error_after: {count: 5, period: day}
14:       - name: contract_section
15:       - name: coverholder
16:       - name: broker

A staging model

Staging models do one job: make the source usable. Rename columns to a standard, cast types, and nothing else.

 1: -- models/staging/stg_premium.sql
 2: select
 3:     umr,
 4:     section_no,
 5:     certificate_ref,
 6:     upper(trim(transaction_type))           as transaction_type,
 7:     cast(bordereau_month as date)           as bordereau_month,
 8:     original_ccy,
 9:     cast(gross_premium  as decimal(18,2))   as gross_premium,
10:     cast(commission_pct as decimal(9,4))    as commission_pct,
11:     cast(brokerage_pct  as decimal(9,4))    as brokerage_pct,
12:     loaded_at
13: from {{ source('feed', 'premium') }}

A mart model

 1: -- models/marts/fct_premium.sql
 2: {{ config(materialized='table') }}
 3: 
 4: with premium as (
 5:     select * from {{ ref('stg_premium') }}
 6: ),
 7: sections as (
 8:     select * from {{ ref('dim_section') }}
 9: ),
10: roe as (
11:     select * from {{ ref('stg_fx_rates') }}
12: )
13: 
14: select
15:     s.contract_section_key,
16:     s.coverholder_key,
17:     s.broker_key,
18:     p.certificate_ref,
19:     p.transaction_type,
20:     p.bordereau_month,
21:     p.original_ccy,
22:     p.gross_premium,
23:     p.gross_premium * r.rate_to_gbp                              as gross_premium_gbp,
24:     p.gross_premium * p.commission_pct / 100 * r.rate_to_gbp     as commission_gbp,
25:     p.gross_premium * p.brokerage_pct  / 100 * r.rate_to_gbp     as brokerage_gbp
26: from premium p
27: join sections s
28:   on  s.umr = p.umr
29:   and s.section_no = p.section_no
30: left join roe r
31:   on  r.currency = p.original_ccy
32:   and r.roe_month = p.bordereau_month

Because this model uses ref('dim_section') and ref('stg_premium'), dbt knows to build those first. Run dbt docs generate and you get a browsable lineage graph of the whole project.

Materialisations

Materialisation	What dbt creates	Good for
view	A view	Staging models, light logic
table	A table, rebuilt every run	Dimensions, smaller facts
incremental	A table that only processes new or changed rows	Large transaction facts
ephemeral	Nothing. It's inlined as a CTE	Small reusable pieces of logic

Set defaults per folder in dbt_project.yml so individual models rarely need a config block:

1: models:
2:   analytics:
3:     staging:
4:       +materialized: view
5:       +schema: staging
6:     marts:
7:       +materialized: table
8:       +schema: reporting

Tests from day one

 1: # models/marts/_marts.yml
 2: version: 2
 3: 
 4: models:
 5:   - name: fct_premium
 6:     columns:
 7:       - name: contract_section_key
 8:         tests:
 9:           - not_null
10:           - relationships:
11:               to: ref('dim_section')
12:               field: contract_section_key
13:       - name: transaction_type
14:         tests:
15:           - accepted_values:
16:               values: ['NEW', 'RENEWAL', 'MTA', 'CANCELLATION']

The relationships test is the one I'd miss most. It catches premium rows whose contract section doesn't exist in the dimension, which in a stored procedure would quietly disappear in an inner join.

Running it

1: dbt build
2: dbt build --select fct_premium+
3: dbt source freshness

dbt build runs each model and then its tests before moving on. If a test fails, models downstream of it are skipped, so bad data doesn't flow into reports.

What changes for a SQL Server developer

• Write SELECTs, not DDL. dbt handles CREATE, DROP and swaps.
• Use ref() and source(), never hard-coded table names. That's what makes environments, lineage and run order work.
• Small models beat big procedures. A 600-line stored procedure becomes five or six models, each easy to test and understand.
• Everything is in Git. Code reviews for SQL become normal.

Next I'll look at incremental models, which is where dbt starts to matter for large transaction tables.