dbt source() Function

The source() function connects your dbt models to raw tables that exist in the warehouse but were not created by dbt. These raw tables typically arrive via data ingestion tools like Fivetran, Airbyte, or custom ETL scripts. Using source() instead of hardcoding table paths makes your project flexible, testable, and self-documenting.

Why source() Exists

Without source(), a staging model might look like this:

-- BAD: hardcoded path
select * from raw_database.shopify_production.orders

This string appears in every model that reads raw orders. If the schema name changes, you hunt through every file. You also cannot run freshness checks or view source lineage in the docs site.

With source():

-- GOOD: declared source
select * from {{ source('shopify', 'orders') }}

The actual path (raw_database.shopify_production.orders) lives in one YAML file. Change it there once and all models update automatically.

The Two Arguments

{{ source('source_name', 'table_name') }}
         ─────────────  ──────────────
              │                │
              │                └── The table name as declared in sources.yml
              └── The source name as declared in sources.yml

Defining the Source in YAML

Before using source() in SQL, you must declare the source in a YAML file:

# models/staging/sources.yml
version: 2

sources:
  - name: shopify               ← this is the first argument to source()
    database: raw_database      ← actual warehouse database
    schema: shopify_production  ← actual warehouse schema
    tables:
      - name: orders            ← this is the second argument to source()
      - name: customers
      - name: products
      - name: refunds

Now you can use {{ source('shopify', 'orders') }} in any model and dbt resolves it to raw_database.shopify_production.orders.

source() in a Staging Model

-- models/staging/stg_orders.sql

select
    id                          as order_id,
    customer_id,
    cast(created_at as date)   as order_date,
    total_price / 100.0        as total_dollars,
    financial_status            as payment_status,
    fulfillment_status
from {{ source('shopify', 'orders') }}
where cancelled_at is null

The staging model is the only place where source() appears for raw orders. Every downstream model uses ref('stg_orders') instead. This keeps raw table paths centralized in one place per table.

What source() Compiles To

Your SQL:
  from {{ source('shopify', 'orders') }}

After dbt compile:
  from raw_database.shopify_production.orders

Check the compiled version in target/compiled/ any time you want to verify the resolution.

Overriding Database or Schema Per Table

Sometimes different tables in the same source live in different schemas. You can override at the table level:

sources:
  - name: shopify
    database: raw_database
    schema: shopify_production     ← default schema for this source
    tables:
      - name: orders               ← uses shopify_production
      - name: customers            ← uses shopify_production
      - name: inventory
        schema: shopify_inventory  ← override: uses this schema instead

Now {{ source('shopify', 'inventory') }} resolves to raw_database.shopify_inventory.inventory while other tables in the same source use shopify_production.

Source Identifier Override

Sometimes the actual table name in the warehouse differs from the name you want to use in your models. The identifier field handles this:

sources:
  - name: crm
    schema: hubspot_raw
    tables:
      - name: contacts                ← name used in source('crm', 'contacts')
        identifier: tbl_contacts_v2  ← actual table name in warehouse

This lets you use clean names in your code while mapping to ugly warehouse table names.

Freshness Configuration with source()

Freshness checks require a timestamp column in the source table. You configure freshness thresholds in YAML and run them with dbt source freshness.

sources:
  - name: shopify
    schema: shopify_production
    tables:
      - name: orders
        loaded_at_field: _loaded_at   ← timestamp column name
        freshness:
          warn_after:  {count: 4,  period: hour}
          error_after: {count: 12, period: hour}

Freshness check output:
  PASS   crm.contacts      last loaded 1h ago   (within 4h warn threshold)
  WARN   shopify.orders    last loaded 5h ago   (past 4h, under 12h error)
  ERROR  marketing.events  last loaded 15h ago  (past 12h error threshold)

Source() in the Lineage Graph

Sources appear as a distinct node type in the dbt lineage graph (visible in dbt docs serve). They show as green nodes while dbt models show as blue nodes. This visual distinction helps anyone reading the graph immediately understand which nodes represent raw ingested data versus dbt-transformed data.

Lineage view:

[source: shopify.orders]  ──────────────────>  [stg_orders]  ──>  [fct_orders]
[source: crm.customers]   ──>  [stg_customers] ──────────────┘

Testing Sources

Add generic tests to source tables in the YAML definition:

tables:
  - name: orders
    columns:
      - name: id
        tests:
          - not_null
          - unique
      - name: customer_id
        tests:
          - not_null

Run only source tests with:

dbt test --select source:shopify
dbt test --select source:shopify.orders

source() Reference Rules

• Only use source() for tables that dbt did not create
• Every source table you read must be declared in a YAML file first
• Use source() only in staging models — downstream models use ref()
• Put sources.yml in the same folder as the staging models that use those sources
• One source declaration per source system keeps YAML organized