Skip to main content

About ref function

select * from {{ ref("node_name") }}

Definition​

This function:

  • Returns a Relation for a model, seed, or snapshot
  • Creates dependencies between the referenced node and the current model, which is useful for documentation and node selection
  • Compiles to the full object name in the database

The most important function in dbt is ref(); it's impossible to build even moderately complex models without it. ref() is how you reference one model within another. This is a very common behavior, as typically models are built to be "stacked" on top of one another. Here is how this looks in practice:

model_a.sql
select *
from public.raw_data
model_b.sql
select *
from {{ref('model_a')}}

ref() is, under the hood, actually doing two important things. First, it is interpolating the schema into your model file to allow you to change your deployment schema via configuration. Second, it is using these references between models to automatically build the dependency graph. This will enable dbt to deploy models in the correct order when using dbt run.

The {{ ref }} function returns a Relation object that has the same table, schema, and name attributes as the {{ this }} variable.

  • Note — Prior to dbt v1.6, the Cloud IDE returns request as the result of {{ ref.identifier }}.

Advanced ref usage​

Versioned ref​

The ref function supports an optional keyword argument - version (or v). When a version argument is provided to the ref function, dbt returns to the Relation object corresponding to the specified version of the referenced model.

This functionality is useful when referencing versioned models that make breaking changes by creating new versions, but guarantees no breaking changes to existing versions of the model.

If the version argument is not supplied to a ref of a versioned model, the latest version is. This has the benefit of automatically incorporating the latest changes of a referenced model, but there is a risk of incorporating breaking changes.

Example:​

models/<schema>.yml

models:
  - name: model_name
    latest_version: 2
    versions:
      - v: 2
      - v: 1
 -- returns the `Relation` object corresponding to version 1 of model_name
select * from {{ ref('model_name', version=1) }}
 -- returns the `Relation` object corresponding to version 2 (the latest version) of model_name
select * from {{ ref('model_name') }}

Ref project-specific models​

You can also reference models from different projects using the two-argument variant of the ref function. By specifying both a namespace (which could be a project or package) and a model name, you ensure clarity and avoid any ambiguity in the ref. This is also useful when dealing with models across various projects or packages.

When using two arguments with projects (not packages), you also need to set cross project dependencies.

The following syntax demonstrates how to reference a model from a specific project or package:

select * from {{ ref('project_or_package', 'model_name') }}

We recommend using two-argument ref any time you are referencing a model defined in a different package or project. While not required in all cases, it's more explicit for you, for dbt, and future readers of your code.

We especially recommend using two-argument ref to avoid ambiguity, in cases where a model name is duplicated across multiple projects or installed packages. If you use one-argument ref (just the model_name), dbt will look for a model by that name in the same namespace (package or project); if it finds none, it will raise an error.

Note: The project_or_package should match the name of the project/package, as defined in its dbt_project.yml. This might be different from the name of the repository. It never includes the repository's organization name. For example, if you use the fivetran/stripe package, the package name is stripe, not fivetran/stripe.

Forcing dependencies​

In normal usage, dbt knows the proper order to run all models based on the use of the ref function. There are some cases where dbt doesn't know when a model should be run. For example, when a model only references a macro:

  • In this case, dbt thinks the model can run first because no explicit references are made at compilation time.
  • To address this, use a SQL comment along with the ref function — dbt will understand the dependency and the compiled query will still be valid:
 -- depends_on: {{ ref('upstream_parent_model') }}

 {{ your_macro('variable') }}

dbt will see the ref and build this model after the specified reference.

Another example is:

  • When a reference appears within an is_incremental() conditional block.
  • This is because the is_incremental() macro will always return false at parse time, so any references within it can't be inferred.
  • To handle this, you can use a SQL comment outside of the is_incremental() conditional:
-- depends_on: {{ source('raw', 'orders') }}

{% if is_incremental() %}
select * from {{ source('raw', 'orders') }}
{% endif %}
tip

To ensure dbt understands the dependency, use a SQL comment instead of a Jinja comment. Jinja comments ({# ... #}) don't work and are ignored by dbt's parser, meaning ref is never processed and resolved. SQL comments, however, (-- or /* ... */) do work because dbt still evaluates Jinja inside SQL comments.

0
Edit this page