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 randbt 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.csvextension. - Never hardcode the schema or table name of a seed.
- Use
ref(), notsource(), because dbt "owns" the seeded table. - Using
ref()ensures the seed appears correctly in the dbt Lineage Graph (DAG) as an upstream dependency.