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.

Practice

1. What is the main purpose of using seeds in dbt?

easy

A. To create dynamic tables based on SQL queries

B. To load static reference data from CSV files into your database

C. To schedule dbt runs automatically

D. To write Python scripts for data transformation

Solution

1. Step 1: Understand what seeds are in dbt

   Seeds are CSV files that contain static reference data you want to load into your database.

2. Step 2: Identify the main use of seeds

   Seeds let you easily add fixed data tables without writing SQL queries.

3. Final Answer:

   To load static reference data from CSV files into your database -> Option B

4. Quick Check:

   Seeds = static CSV data load [OK]

Hint: Seeds = fixed CSV data loaded as tables [OK]

Common Mistakes:

• Confusing seeds with models that run SQL
• Thinking seeds schedule dbt runs
• Assuming seeds are for dynamic data

2. Which command do you run to load or refresh seed data in your database?

easy

A. dbt test

B. dbt run

C. dbt seed

D. dbt compile

Solution

1. Step 1: Recall dbt commands related to seeds

   The command dbt seed loads CSV seed files into the database as tables.

2. Step 2: Differentiate from other commands

   dbt run runs models, dbt test runs tests, and dbt compile compiles SQL but does not load seeds.

3. Final Answer:

   dbt seed -> Option C

4. Quick Check:

   Load seeds = dbt seed [OK]

Hint: Use 'dbt seed' to load CSV data tables [OK]

Common Mistakes:

• Using 'dbt run' to load seeds
• Confusing 'dbt test' with loading data
• Thinking 'dbt compile' loads data

3. Given a seed CSV file countries.csv with columns id and name, what will be the output of this dbt model SQL?

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

medium

A. A table with all rows and columns from countries.csv

B. Only the id column from countries.csv

C. An empty table because seeds are not loaded automatically

D. An error because seeds cannot be referenced

Solution

1. Step 1: Understand how seeds are referenced in dbt

   Seeds become tables in the database and can be referenced using ref() like models.

2. Step 2: Predict the query output

   The query selects all columns and rows from the seed table countries, so it returns the full CSV data.

3. Final Answer:

   A table with all rows and columns from countries.csv -> Option A

4. Quick Check:

   ref(seed) = full seed table [OK]

Hint: ref(seed_name) returns full seed table [OK]

Common Mistakes:

• Thinking seeds cannot be referenced
• Assuming seeds load empty tables
• Expecting partial columns only

4. You ran dbt seed but your seed table did not update. Which of these is the most likely cause?

medium

A. You forgot to add the seed CSV file in the seeds folder

B. You ran dbt run instead of dbt seed

C. Your seed CSV file has syntax errors

D. You did not configure the seed in dbt_project.yml

Solution

1. Step 1: Check seed discovery mechanism

   dbt automatically discovers and loads CSV files from the seeds/ folder with dbt seed.

2. Step 2: Identify why table doesn't update

   If the CSV file is missing from the seeds/ folder, dbt seed runs successfully but skips that seed, leaving the table unchanged.

3. Final Answer:

   You forgot to add the seed CSV file in the seeds folder -> Option A

4. Quick Check:

   Seeds folder missing CSV = no update [OK]

Hint: Place seed CSVs in seeds/ folder for dbt seed [OK]

Common Mistakes:

• Thinking seeds require config in dbt_project.yml
• Confusing dbt run with dbt seed
• CSV syntax errors (would cause explicit failure)

5. You want to use a seed file currencies.csv with columns code and symbol inside a model to join with a transactions table on currency_code. Which is the correct way to write the join in your model SQL?

hard

A. select t.*, c.symbol from transactions t join currencies c on t.currency_code = c.code

B. select t.*, c.symbol from transactions t join currencies.csv c on t.currency_code = c.code

C. select t.*, c.symbol from transactions t join seed('currencies') c on t.currency_code = c.code

D. select t.*, c.symbol from transactions t join {{ ref('currencies') }} c on t.currency_code = c.code

Solution

1. Step 1: Recall how to reference seeds in dbt models

   Seeds are referenced using {{ ref('seed_name') }} to get the table name in SQL.

2. Step 2: Identify the correct join syntax

   Joining transactions with {{ ref('currencies') }} correctly uses the seed table in the join.

3. Final Answer:

   select t.*, c.symbol from transactions t join {{ ref('currencies') }} c on t.currency_code = c.code -> Option D

4. Quick Check:

   Join seed with ref() = correct [OK]

Hint: Use ref('seed_name') to join seed tables in models [OK]

Common Mistakes:

• Using raw CSV filename in SQL
• Forgetting to use ref() for seeds
• Trying to use a non-existent seed() function