dbt & the Transform Layer | Data Engineering Reference

What dbt does

dbt (data build tool) manages SQL transforms as versioned code:

Models (SELECT → table/view)
Tests (unique, not_null, relationships)
Documentation and lineage
Jinja macros for reuse

Runs against warehouse/lake SQL engine (Snowflake, BigQuery, Databricks SQL, Redshift).

Project structure

models/
  staging/      — 1:1 with sources, light cleaning
  intermediate/ — business logic joins
  core/         — conformed dimensions (dim_customer, dim_date)
  marts/        — consumer-facing fact tables (fct_*)

Staging = silver-adjacent. Marts = gold star-schema facts and dimensions. Spark may upstream; dbt owns SQL-native transforms.

Assumes Dimensional & Analytical Modeling and Conformed Dimensions & Mart Design for schema theory — here we cover dbt mechanics.

Materializations

Type	Cost	Use
`view`	Query time	Staging, thin layers
`table`	Full rebuild	Medium size
`incremental`	Delta only	Large fact tables

Incremental models need a unique key and filter on new data (where updated_at > max(...)).

Testing as contract

- name: orders
  columns:
    - name: order_id
      tests: [unique, not_null]

dbt tests are pipeline gates — run in Airflow after transform. Link to Topic 7 for deeper data contracts.

Spark + dbt division of labor

Spark	dbt
Heavy joins on lake files	Warehouse SQL on curated tables
Semi-structured parsing (JSON)	Business metrics
ML feature prep	Analyst-facing marts

Databricks: Spark writes silver Delta → dbt reads via Unity Catalog.

Interview answer template

"Spark lands and dedupes events to silver Delta. dbt builds staging views, intermediate session models, and incremental fct_orders mart with tests on grain and referential integrity to dim_customer."

Hands-On Tasks (Optional)

Pipeline design drills and whiteboard exercises — DAG sketches, partition plans, backfill strategies. Assumes Databases and SQL fundamentals are in place.

Sketch a dbt model DAG
E-commerce: staging → core conformed dimensions → `fct_order_items` mart (star schema). Note materialization choices (view vs table vs incremental) per layer and one referential test between fact and dimension.
15m