The ref() Function

Reference Resolution Architecture

Dependency Graph

Package Resolution

+-----------------------------------------------------------------------------+
|                     PACKAGE REFERENCE RESOLUTION                            |
+-----------------------------------------------------------------------------+
|                                                                             |
|  +---------------------------------------------------------------------+    |
|  |                    PACKAGES.YML                                     |    |
|  |                                                                     |    |
|  |  packages:                                                          |    |
|  |    - package: dbt-labs/dbt_utils                                    |    |
|  |      version: [">=1.0.0", "<2.0.0"]                                |     |
|  |    - package: calogica/dbt_expectations                            |     |
|  |      version: [">=0.10.0"]                                         |     |
|  |    - git: "https://github.com/org/repo.git"                        |     |
|  |      revision: main                                                |     |
|  +---------------------------------------------------------------------+    |
|                              |                                              |
|                              v                                              |
|  +---------------------------------------------------------------------+    |
|  |                    DBT_PACKAGES/                                     |   |
|  |                                                                     |    |
|  |  +-- dbt_utils/                                                    |     |
|  |  |   +-- macros/                                                   |     |
|  |  |   |   +-- date_spine.sql                                        |     |
|  |  |   |   +-- generate_series.sql                                   |     |
|  |  |   |   +-- pivot.sql                                             |     |
|  |  |   +-- dbt_project.yml                                           |     |
|  |  +-- dbt_expectations/                                             |     |
|  |      +-- macros/                                                   |     |
|  |      |   +-- schema_tests.sql                                      |     |
|  |      |   +-- row_count.sql                                         |     |
|  |      +-- dbt_project.yml                                           |     |
|  +---------------------------------------------------------------------+    |
|                                                                             |
+-----------------------------------------------------------------------------+

Detailed Explanation

The ref() function is the most fundamental and powerful function in dbt. It serves as the primary mechanism for referencing other models, packages, and sources within your dbt project.

How does ref() work?

When you write {{ ref('model_name') }} in a dbt model, dbt performs several operations:

1. Parsing: During the parse phase, dbt identifies all ref() calls in your SQL files
2. Graph Construction: dbt builds a Directed Acyclic Graph (DAG) based on these references
3. Resolution: When compiling, dbt resolves ref() calls to the actual database object references
4. Execution: The resolved SQL is executed against your data warehouse

What are the reference types?

• Local References: Point to models within the same dbt project, resolving to database.schema.model_name
• Package References: Point to models in installed packages
• Cross-project References: Allow referencing models in other dbt projects, enabling data mesh architectures

What is the resolution process?

1. Check local project: Look for the model in your current project
2. Check packages: Look for the model in installed packages
3. Check cross-project: If enabled, look for the model in external projects
4. Error handling: If not found, raise a compilation error

What are the benefits of using ref()?

1. Graph awareness: dbt automatically resolves dependencies
2. Environment flexibility: Works across dev, staging, and production
3. Package management: Clean references to package models
4. Lineage tracking: Complete data lineage from source to mart
5. Testing integration: Models can be tested in isolation
6. Documentation: Automatic documentation of dependencies

Key Takeaway: The ref() function is essential for building maintainable, scalable data pipelines by providing a standardized way to reference models across projects and packages.

Code Examples

Basic Local Reference

-- models/marts/fct_orders.sql
with orders as (
    select * from {{ ref('stg_orders') }}
),

customers as (
    select * from {{ ref('dim_customers') }}
),

final as (
    select
        orders.order_id,
        orders.order_date,
        customers.customer_name,
        orders.amount
    from orders
    left join customers on orders.customer_id = customers.customer_id
)

select * from final

Package Reference

-- models/marts/dim_dates.sql
{{
    config(
        materialized='table'
    )
}}

with date_spine as (
    {{ dbt_utils.date_spine(
        start_date="cast('2020-01-01' as date)",
        end_date="cast('2025-12-31' as date)",
        datepart="day"
    )}}
),

final as (
    select
        date_day as date_date,
        {{ dbt_utils.date_part("year", "date_day") }} as date_year,
        {{ dbt_utils.date_part("month", "date_day") }} as date_month,
        {{ dbt_utils.date_part("day", "date_day") }} as date_day_of_month,
        {{ dbt_utils.date_part("dayofweek", "date_day") }} as date_day_of_week
    from date_spine
)

select * from final

Cross-Project Reference

-- models/marts/fct_company_metrics.sql
{{
    config(
        materialized='incremental',
        unique_key='company_id'
    )
}}

with companies as (
    select * from {{ ref('analytics', 'dim_companies') }}
),

revenue as (
    select * from {{ ref('finance', 'fct_revenue') }}
),

final as (
    select
        companies.company_id,
        companies.company_name,
        sum(revenue.amount) as total_revenue,
        current_timestamp() as updated_at
    from companies
    left join revenue on companies.company_id = revenue.company_id
    group by 1, 2
)

select * from final

Advanced Reference Patterns

-- models/staging/stg_orders.sql
{{
    config(
        materialized='view',
        schema='staging'
    )
}}

{% set source_relation = source('raw', 'orders') %}

with source as (
    select * from {{ source_relation }}
),

renamed as (
    select
        id as order_id,
        customer_id,
        status,
        created_at,
        updated_at
    from source
)

select * from renamed

Dynamic Reference with Variables

-- models/marts/fct_orders.sql
{{
    config(
        materialized='incremental'
    )
}}

{% set source_model = var('order_source', 'stg_orders') %}

with orders as (
    select * from {{ ref(source_model) }}
),

final as (
    select
        order_id,
        customer_id,
        order_date,
        amount,
        current_timestamp() as dbt_updated_at
    from orders
)

select * from final

{% if is_incremental() %}
where dbt_updated_at > (select max(dbt_updated_at) from {{ this }})
{% endif %}

Performance Metrics

Best Practices

1. Use ref() for all model references - Never hardcode table names
2. Organize models in layers - staging -> intermediate -> marts
3. Use package references for shared logic across projects
4. Implement cross-project refs for data mesh architectures
5. Document all references with descriptions in YAML files
6. Test referenced models to ensure data quality
7. Use source() for external data - Never ref() external tables
8. Version control your packages with specific version constraints

See Also

• dbt Core Architecture — Manifest, DAG, and compilation pipeline
• Models, Seeds, and Snapshots — Core dbt components for data transformation
• Jinja Templating in dbt — Template syntax, macros, and dynamic SQL
• dbt Project Configuration — Packages, profiles, and project setup
• Snowflake Database Objects — Tables, views, and schemas in Snowflake