dbt: Materializations, Tests & Project Structure

Materializations: view, table, incremental

-- view: query re-runs on every read, always reflects current data,
-- but potentially slower to query
{{ config(materialized='view') }}

-- table: SQL runs once, result persisted as a physical table --
-- faster to query, but a snapshot as of the last dbt run
{{ config(materialized='table') }}

-- incremental: only processes NEW/CHANGED rows on subsequent runs --
-- avoids reprocessing years of history on every run for large tables
{{ config(materialized='incremental') }}

SELECT * FROM {{ source('raw', 'events') }}
{% if is_incremental() %}
  -- {{ this }} refers to the model's OWN previously-built table
  WHERE event_time > (SELECT max(event_time) FROM {{ this }})
{% endif %}

Data Quality Tests

# models/marts/_fct_orders.yml
version: 2

models:
  - name: fct_orders
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: id

dbt test runs these checks against real materialized data -- catching data quality issues (duplicate order IDs, orphaned foreign keys) distinct from code-level unit tests.

Macros for Reusable SQL

-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name) %}
  ({{ column_name }} / 100)::numeric(16, 2)
{% endmacro %}

-- usage in any model
SELECT {{ cents_to_dollars('price_in_cents') }} AS price_usd
FROM {{ ref('stg_products') }}

Conventional Project Layers

• staging/ -- minimal 1:1 cleanup of raw source data (renaming columns, type casting).

• intermediate/ -- combines staging models into reusable building blocks.

• marts/ -- final, business-logic-rich, analysis-ready tables meant for direct BI consumption.

• seeds/ -- small static CSVs (like a country-code-to-region mapping) loaded via dbt seed.

• snapshots/ -- tracks history for mutable source tables that get overwritten in place (Type 2 SCD).