dbt Sources

Sources are dbt's way of declaring and describing the raw tables that your pipeline starts from. Instead of hardcoding database and schema names directly inside your SQL files, you define sources in a YAML file and reference them using the source() function. This approach makes your project more maintainable and unlocks important features like data freshness checks.

The Problem Without Sources

Imagine you have twenty staging models that all query raw tables directly:

-- stg_orders.sql
from raw_data.public.orders

-- stg_customers.sql
from raw_data.public.customers

-- stg_products.sql
from raw_data.public.products

One day your data team renames the schema from public to raw_schema. Now you must open every file and update it. If you miss one file, that model breaks silently. Sources solve this by putting the database and schema name in exactly one place.

Defining a Source

You define sources in any .yml file inside your models/ folder. Convention is to put it in models/staging/sources.yml.

# models/staging/sources.yml
version: 2

sources:
  - name: crm                         ← source name (used in source() calls)
    database: raw_data                ← actual database name in warehouse
    schema: public                    ← actual schema name in warehouse
    description: "Raw CRM data loaded by Fivetran"

    tables:
      - name: customers               ← actual table name
        description: "One row per customer account"
        columns:
          - name: id
            description: "Primary key"
          - name: email
            description: "Customer email"

      - name: orders
        description: "One row per placed order"
        columns:
          - name: order_id
            description: "Primary key"
          - name: customer_id
            description: "Foreign key to customers.id"

Using source() in Models

After defining the source, reference it in SQL using the source() function:

-- models/staging/stg_customers.sql

select
    id          as customer_id,
    name        as full_name,
    email,
    created_at  as signup_date
from {{ source('crm', 'customers') }}

The first argument is the source name from your YAML (crm). The second argument is the table name (customers). dbt compiles this to the actual raw_data.public.customers reference at run time.

Before and After Compilation

Your model:
  from {{ source('crm', 'customers') }}

After dbt compile:
  from raw_data.public.customers

Now if the schema changes, update only sources.yml. All models that use source('crm', 'customers') pick up the change automatically.

Multiple Source Schemas

A real project pulls raw data from multiple systems. Define each system as a separate source:

sources:
  - name: crm
    database: raw_data
    schema: crm_raw
    tables:
      - name: customers
      - name: contacts

  - name: ecommerce
    database: raw_data
    schema: shopify_raw
    tables:
      - name: orders
      - name: order_items
      - name: products

  - name: marketing
    database: raw_data
    schema: hubspot_raw
    tables:
      - name: campaigns
      - name: email_events

Each staging model pulls from the correct source:

-- stg_orders.sql
from {{ source('ecommerce', 'orders') }}

-- stg_campaigns.sql
from {{ source('marketing', 'campaigns') }}

Source Freshness Checks

One powerful feature of sources is freshness checking. You tell dbt how often a source table should receive new data. dbt checks whether the latest row in that table is recent enough.

sources:
  - name: ecommerce
    schema: shopify_raw
    tables:
      - name: orders
        loaded_at_field: _fivetran_synced   ← column with row's load timestamp
        freshness:
          warn_after: {count: 6, period: hour}
          error_after: {count: 24, period: hour}

Run dbt source freshness to check all sources at once:

$ dbt source freshness

Found 3 sources

1 of 3 START freshness of ecommerce.orders .............. [RUN]
1 of 3 WARN freshness of ecommerce.orders ............... [warn in 0.5s]
  Last record was 8 hours ago. Warn after 6h.

2 of 3 START freshness of crm.customers ................. [RUN]
2 of 3 PASS freshness of crm.customers .................. [OK in 0.3s]

A warning tells you the data is getting stale. An error tells you the pipeline may have stopped working.

Source Tests

You can add data quality tests to source tables the same way you add them to models:

sources:
  - name: ecommerce
    schema: shopify_raw
    tables:
      - name: orders
        columns:
          - name: order_id
            tests:
              - not_null
              - unique
          - name: customer_id
            tests:
              - not_null
              - relationships:
                  to: source('ecommerce', 'customers')
                  field: customer_id

Running dbt test --select source:ecommerce runs all tests defined on ecommerce source tables.

Diagram: Sources in the DAG

Sources (declared in sources.yml)
   raw_data.crm_raw.customers
   raw_data.shopify_raw.orders
          |
          v
Staging Models (read via source())
   stg_customers
   stg_orders
          |
          v
Mart Models (read via ref())
   fct_orders
   dim_customers
          |
          v
Dashboards / Reports

Sources always sit at the very top of the DAG. They represent data that exists in the warehouse but was not created by dbt. Everything downstream is a dbt model.

Viewing Sources in dbt Docs

When you run dbt docs generate and dbt docs serve, all sources you define appear in the documentation site. The lineage graph shows which models depend on which sources. Analysts can see exactly where each piece of data originates, down to the column level.

Best Practices for Sources

• Define every raw table you use as a source — never hardcode schema names in SQL
• Put source definitions in models/staging/sources.yml near the staging models that use them
• Add freshness checks to tables loaded by automated pipelines
• Add descriptions to every source table and important columns
• Use one source block per source system, not one per table