dbt Fundamentals

Master the building blocks of every dbt project — models, sources, refs, materializations, seeds, and project structure. These fundamentals make up the largest portion of the certification exam.

What Is dbt?

dbt (data build tool) is a transformation framework that enables analytics engineers to transform raw data in their warehouse using SQL SELECT statements. dbt handles the DDL/DML (CREATE TABLE, INSERT, etc.) automatically based on your configuration, letting you focus on the business logic.

Models

A dbt model is a SQL SELECT statement saved as a .sql file in the models/ directory. When you run dbt run, dbt compiles each model and executes it against your data warehouse, creating a table or view.

-- models/staging/stg_customers.sql
SELECT
    id AS customer_id,
    first_name,
    last_name,
    email,
    created_at
FROM {{ source('raw', 'customers') }}

Model Layers

A well-structured dbt project organizes models into layers:

• Staging (stg_): Light transformations on raw sources — renaming, casting, basic filtering. One staging model per source table.
• Intermediate (int_): Business logic that combines staging models. Not exposed to end users.
• Marts (fct_, dim_): Final business-facing models organized by department (finance, marketing, etc.).

Sources

Sources define your raw data tables loaded by your EL tool. They are declared in YAML files and referenced using the {{ source() }} function.

# models/staging/_sources.yml
version: 2
sources:
  - name: raw
    database: analytics
    schema: raw_data
    tables:
      - name: customers
        description: Raw customer data from the production database
        loaded_at_field: _etl_loaded_at
        freshness:
          warn_after: {count: 12, period: hour}
          error_after: {count: 24, period: hour}
      - name: orders
        description: Raw order transactions

The ref() Function

The {{ ref() }} function is the most important function in dbt. It creates dependencies between models, enabling dbt to build models in the correct order and generate the DAG (Directed Acyclic Graph).

-- models/marts/fct_orders.sql
SELECT
    o.order_id,
    o.customer_id,
    c.first_name,
    c.last_name,
    o.order_date,
    o.amount
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('stg_customers') }} c
    ON o.customer_id = c.customer_id

Materializations

Materializations determine HOW dbt builds a model in your warehouse. This is one of the most heavily tested topics on the exam.

-- Set materialization in the model file
{{ config(materialized='incremental', unique_key='order_id') }}

SELECT *
FROM {{ source('raw', 'orders') }}
{% if is_incremental() %}
WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

Seeds

Seeds are CSV files in your seeds/ directory that dbt loads into your warehouse as tables. Use seeds for static reference data like country codes, status mappings, or category lookups.

# Run: dbt seed
# File: seeds/country_codes.csv
# Result: Creates a table named country_codes in your warehouse

Project Structure

my_dbt_project/
  dbt_project.yml        # Project configuration
  profiles.yml           # Connection profiles (dbt Core only)
  models/
    staging/             # Staging models (1:1 with sources)
      _sources.yml       # Source definitions
      _stg_models.yml    # Schema tests for staging
      stg_customers.sql
      stg_orders.sql
    intermediate/        # Business logic
      int_order_items.sql
    marts/               # Final business models
      finance/
        fct_revenue.sql
      marketing/
        dim_customers.sql
  tests/                 # Custom data tests
  macros/                # Reusable Jinja macros
  seeds/                 # CSV reference data
  snapshots/             # SCD Type 2 snapshots
  analyses/              # Ad-hoc SQL analyses

Practice Questions

Question 1

Question 2

Question 3

Question 4

Question 5