Seeds for static reference data in dbt - Mini Project: Build & Apply

Seeds for Static Reference Data in dbt

📖 Scenario: You are working on a data project where you need to use static reference data, like country codes and names, inside your dbt models. Instead of hardcoding these values in SQL, you will use dbt seeds to manage this static data easily and keep your project organized.

🎯 Goal: Learn how to create a seed file in dbt, configure it, use it in a model, and finally query the seeded data to see the results.

📋 What You'll Learn

Create a CSV seed file with country codes and names

Configure dbt to recognize the seed file

Write a dbt model that selects from the seed data

Run dbt commands to load and query the seed data

💡 Why This Matters

🌍 Real World

Static reference data like country codes, product categories, or status lists are common in data projects. Using dbt seeds helps keep this data organized and version controlled.

💼 Career

Data analysts and engineers often need to manage static data efficiently. Knowing how to use dbt seeds is a valuable skill for building maintainable data pipelines.

1

Create the seed CSV file

Create a CSV file named countries.csv inside the data folder of your dbt project. The file should have two columns: country_code and country_name. Add these exact rows:
country_code,country_name
US,United States
CA,Canada
MX,Mexico

dbt

# Create the file data/countries.csv with the specified content

Need a hint?

Make sure the file is named exactly countries.csv and placed inside the data folder.

2

Configure dbt to load the seed file

In your dbt_project.yml file, add or update the seeds section to include your project name and set quote_columns to false. For example, if your project is named my_dbt_project, add:
seeds:
my_dbt_project:
quote_columns: false

dbt

# Add the seeds configuration to dbt_project.yml

Need a hint?

Replace my_dbt_project with your actual dbt project name exactly.

3

Create a dbt model to select from the seed data

Create a new model file named country_list.sql inside the models folder. Write a SQL query that selects all columns from the seed table countries. Use the exact code:
select * from {{ ref('countries') }}

dbt

# Write the SQL query in models/country_list.sql

Need a hint?

Use the ref function to refer to the seed table named countries.

4

Run dbt seed and query the model output

Run the command dbt seed to load the seed data into your warehouse. Then run dbt run to build the model. Finally, query the model country_list in your warehouse to see the output. Print the results showing the country codes and names exactly as:
US | United States
CA | Canada
MX | Mexico

dbt

# Run dbt seed and dbt run commands, then query the country_list model to print results

Need a hint?

Make sure to run dbt seed before dbt run to load the seed data.