Skip to main content

Referencing Seeds with ref()

Once you have executed the dbt seed command, your CSV file (e.g., country_codes.csv) has been successfully loaded into your data warehouse (e.g., Snowflake) as a physical table.

But how do you actually use that data in your SQL models?

Because dbt considers seeds to be a fundamental part of your DAG (Directed Acyclic Graph), you do not query the table directly using its hardcoded schema name. Instead, you use the Jinja {{ ref() }} function, exactly as you would for a standard dbt model!

Topics Covered​

  • The ref() Function
  • Hands-On: Joining a Seed to a Model
  • Why Not Use source()?

1. The ref() Function​

The ref() function is the most important concept in dbt. It tells dbt to look up the physical location of the object in the data warehouse, and it establishes a dependency in the DAG.

If you have a seed file named data/pincode_mapping.csv, you refer to it simply by its filename (without the .csv extension).

-- This is correct
SELECT * FROM {{ ref('pincode_mapping') }}

-- This is WRONG (Never hardcode the schema)
SELECT * FROM BEEMS_PROD.ANALYTICS.pincode_mapping

2. Hands-On: Joining a Seed to a Model​

Let's assume our Indian retail company receives a daily stream of raw orders. The raw data only contains a STORE_PINCODE, but the business analysts need the STATE_NAME and REGION for their Tableau dashboards.

We have a seed file (seeds/indian_pincodes.csv) that acts as a lookup table.

Step 1: View the Seed Data​

(In your Snowflake Worksheet, verify your role: SELECT CURRENT_ROLE();)

If you ran dbt seed, the table exists in Snowflake.

SELECT * FROM BEEMS_PROD.RAW.INDIAN_PINCODES;
-- 400001 | Mumbai | Maharashtra | West
-- 560001 | Bengaluru | Karnataka | South

Step 2: Create the dbt Model​

Create a new file: models/marts/fct_orders_enriched.sql

WITH raw_orders AS (
-- Let's assume we have a staging model for orders
SELECT
order_id,
customer_id,
store_pincode,
order_amount
FROM {{ ref('stg_orders') }}
),

pincode_lookup AS (
-- Here we reference the seed!
SELECT
pincode,
city_name,
state_name,
region
FROM {{ ref('indian_pincodes') }}
),

final AS (
SELECT
o.order_id,
o.customer_id,
o.order_amount,
l.city_name,
l.state_name,
l.region
FROM raw_orders o
LEFT JOIN pincode_lookup l
ON o.store_pincode = l.pincode
)

SELECT * FROM final

Step 3: Run the Model​

When you run dbt run --select fct_orders_enriched, dbt will compile the Jinja into this raw SQL:

-- Compiled SQL sent to Snowflake
SELECT ...
FROM BEEMS_PROD.STAGING.stg_orders o
LEFT JOIN BEEMS_PROD.RAW.indian_pincodes l
ON o.store_pincode = l.pincode

3. Why Not Use source()?​

You might be wondering: "Wait, in the previous modules we used {{ source() }} to read raw data. Why do we use {{ ref() }} for seeds?"

  • {{ source() }}: Used for data that is loaded into the warehouse by an external system (like Fivetran, Airbyte, or Snowpipe). dbt did not create this data; it is just reading it.
  • {{ ref() }}: Used for objects that dbt created. Because you ran dbt seed, dbt actually created the table in the warehouse. Therefore, dbt "owns" it, and treats it exactly like a model.

Because you used ref(), if you look at your dbt lineage graph, you will clearly see the indian_pincodes seed file acting as an upstream parent to your fct_orders_enriched model!

Summary​

  • Seeds are referenced using the {{ ref('filename') }} function, omitting the .csv extension.
  • Never hardcode the schema or table name of a seed.
  • Use ref(), not source(), because dbt "owns" the seeded table.
  • Using ref() ensures the seed appears correctly in the dbt Lineage Graph (DAG) as an upstream dependency.