Sources and the Staging Layer

This lesson on Sources and the Staging Layer is hands-on and example-driven. You will configure raw data sources in YAML and reference them in staging models using the source() Jinja function. You will distinguish source() from ref() to build automated DAG lineage and execute end-to-end transformation pipelines from raw warehouse tables to downstream marts.

What You'll Be Able To Do

• Declare raw warehouse tables and schemas inside a dbt source YAML configuration file.
• Write a staging SQL model that queries upstream raw tables using the source() Jinja macro.
• Build downstream mart models that reference staging models using the ref() Jinja function.
• Differentiate between source() for raw ingestion and ref() for internal dbt model lineage.
• Execute transformation pipelines using dbt run to validate automated DAG dependency ordering.

Detailed Concept Walkthrough

1. Source Declaration in YAML

Sources represent raw data loaded into the data warehouse by external ETL tools outside of dbt direct management. Declaring them in YAML creates a centralized configuration layer and establishes the root nodes of your dbt Directed Acyclic Graph.

• Configuration Structure: Source declarations live in YAML files within the models directory under the top-level sources key, specifying the database, schema, and individual table names.
• Under the Hood: dbt parses source YAML files during compilation to register external warehouse objects into its manifest, enabling lineage graphing and source freshness checks before transformations run.
• Best Practice: Declare source tables with full descriptions and column metadata to implement documentation-as-code and provide a single point of update when upstream warehouse schemas shift.

version: 2

sources:
  - name: raw_jaffle_shop
    database: raw
    schema: jaffle_shop
    description: "Raw transactional data loaded via external ETL processes."
    tables:
      - name: orders
        description: "Raw customer orders with timestamps and statuses."
        columns:
          - name: id
            description: "Primary key for raw orders."

Key Takeaway: Source YAML files create a declarative entry point that decouples physical warehouse locations from dbt models.

2. Staging Models and the source() Function

Staging models act as the initial transformation boundary between raw database objects and downstream business logic. The source() Jinja function dynamically resolves the exact warehouse path defined in your source configuration.

• Mechanism: Staging SQL models use the source() macro with two arguments (source_name and table_name) in the FROM clause rather than hardcoded database.schema.table identifiers.
• Execution Flow: During compilation, dbt interpolates the source() call into the fully qualified SQL identifier mapped in the YAML config, decoupling SQL queries from warehouse naming shifts.
• Best Practice: Build 1-to-1 staging models on top of raw source tables to standardize column names, cast data types, and apply light filtering before exposing data to marts.

-- models/staging/stg_orders.sql
with source_data as (
    select * from {{ source('raw_jaffle_shop', 'orders') }}
)

select
    id as order_id,
    user_id as customer_id,
    order_date,
    status
from source_data

Key Takeaway: Using source() in staging models guarantees a single point of maintenance whenever upstream table locations change.

3. Lineage Architecture: source() vs ref()

dbt lineage relies on two distinct Jinja functions to construct dependency graphs: one marks the external warehouse entry point, and the other links internal dbt models.

• Entry Point Boundary: Use source() exclusively to ingest unmanaged, raw tables defined in YAML, marking the absolute beginning of your DAG.
• Internal Model Linking: Use ref() to query other dbt-managed SQL models, establishing parent-child relationships and controlling compilation order.
• Under the Hood: Calling ref() creates an internal node-to-node dependency edge that ensures upstream models finish building before downstream models compile and execute.

-- models/marts/fct_orders.sql
with orders as (
    -- Use ref() for dbt-managed models, never source()
    select * from {{ ref('stg_orders') }}
)

select
    order_id,
    customer_id,
    order_date,
    status
from orders

Key Takeaway: Use source() to bring external raw tables into dbt, and use ref() for all downstream transformations between dbt models.

4. Pipeline Execution and Dependency Ordering

The dbt CLI compiles the DAG and determines the topological execution sequence for all dependent models. Executing the project builds staging and mart tables in the warehouse in the exact order required by their lineage.

• Topological Sorting: Running dbt run instructs dbt to traverse the DAG, compiling staging models first and mart models subsequently based on ref() dependencies.
• Target Materialization: dbt executes DDL and DML statements against the target warehouse to create views or tables according to project and model configurations.
• Validation Flow: Successful CLI execution verifies that raw source mappings match physical database tables and downstream models receive clean transformed records.

# Compile and execute all models in DAG dependency order
dbt run

# Run only the staging layer and its immediate downstream children
dbt run --select stg_orders+

Key Takeaway: dbt run automatically executes models in topological dependency order established by source() and ref() macros.

Topics Covered in Sources and the Staging Layer

1. DBT Source Definition (0:05 - 0:40) — Introduces sources as the raw entry point for dbt transformations loaded by external ETL tools.
2. Source Configuration Mechanics (0:40 - 1:23) — Explains the YAML structure and file location used to define raw data sources declaratively.
3. Creating Raw Warehouse Data (1:23 - 2:31) — Demonstrates executing DDL and DML in Snowflake to simulate external raw data ingestion.
4. Source YAML Implementation (2:31 - 4:15) — Configures the source YAML file with database, schema, table mappings, and column metadata.
5. Staging Model with source() (4:16 - 5:07) — Builds a staging SQL model using the Jinja source function to query raw data.
6. source() vs ref() Distinctions (5:09 - 5:54) — Differentiates referencing external raw data with source versus internal models with ref.
7. Executing the Transformation Pipeline (5:55 - 7:06) — Runs the dbt project to validate DAG lineage and materialize staging and mart models.

dbt Cheat Sheet

• sources: — Defines raw external warehouse tables inside a YAML file

  sources:
    - name: raw
      tables:
        - name: orders
  

• {{ source('source_name', 'table_name') }} — References a declared raw external table in SQL

  select * from {{ source('raw', 'orders') }}
  

• {{ ref('model_name') }} — References another dbt model to build lineage

  select * from {{ ref('stg_orders') }}
  

• dbt run — Executes models across the warehouse in DAG order

  dbt run
  

• dbt docs generate — Generates documentation and DAG lineage from project metadata

  dbt docs generate
  

Comparison Table

Common Pitfalls

• Mistake: Hardcoding database and schema names in staging SQL models. Avoid: Always use the Jinja source function to reference external raw tables dynamically.
• Mistake: Using the ref function to query raw unmanaged warehouse tables. Avoid: Reserve ref strictly for dbt-managed models and use source for raw tables.
• Mistake: Mismatching source YAML identifiers with Snowflake database or schema names. Avoid: Ensure database and schema properties in YAML precisely match physical warehouse objects.
• Mistake: Building mart models directly on top of raw source tables. Avoid: Create dedicated staging models with source to clean data before building marts.

FAQs

• Where should source YAML files be stored in a dbt project? Place source YAML files anywhere within the models directory, commonly organized under models/staging/<source_name>/.
• What happens if an upstream raw table name changes in the data warehouse? Update the table name in the source YAML file once, and all staging models using source() update automatically upon compilation.
• Can you run tests directly on raw sources? Yes, dbt allows freshness checks and generic data tests defined directly on raw tables in the source YAML configuration.