Seeds for static reference data in dbt

Introduction

Seeds let you add small, fixed tables to your project easily. They help keep important reference data ready to use without extra setup.

You have a list of countries or states that rarely change.

You want to store fixed product categories for your reports.

You need a small lookup table for user roles or statuses.

You want to share static data with your team inside the project.

You want to avoid creating separate tables in your database for simple reference data.

Syntax

dbt

1. Create a CSV file with your static data inside the 'seeds' folder.
2. Run 'dbt seed' to load the CSV as a table in your database.
3. Use the seeded table in your models like any other table.

The CSV file name becomes the table name in your database.

You can configure seed options like delimiter or header in your dbt_project.yml file.

Examples

This CSV file lists country codes and names. It will become a table named 'countries' after seeding.

dbt

# File: seeds/countries.csv
country_code,country_name
US,United States
CA,Canada
MX,Mexico

This command loads all CSV files in the 'seeds' folder into your database as tables.

dbt

dbt seed

This SQL query uses the seeded 'countries' table in a dbt model.

dbt

select * from {{ ref('countries') }}

Sample Program

This example shows how to add a small static table of colors using seeds. The CSV file is loaded as a table, then used in a model.

dbt

# Step 1: Create a CSV file named 'colors.csv' inside the 'seeds' folder with this content:
# color_id,color_name
# 1,Red
# 2,Green
# 3,Blue

# Step 2: Run the seed command in your terminal:
dbt seed

# Step 3: Create a model file 'models/color_names.sql' with this SQL:
select * from {{ ref('colors') }}

# Step 4: Run the model:
dbt run

# Step 5: Query the model output to see the seeded data:
select * from color_names;

Important Notes

Seeds are best for small, static datasets. Large or frequently changing data should use other methods.

Remember to run 'dbt seed' whenever you update your CSV files to refresh the tables.

You can customize seed behavior in dbt_project.yml, like setting file encoding or quoting.

Summary

Seeds let you add fixed reference data as tables using CSV files.

They are easy to update and use inside your dbt models.

Run 'dbt seed' to load or refresh the data in your database.