Testing Seeds
We know that we must test our raw sources (to catch API bugs) and our transformed models (to catch SQL bugs).
But what about Seeds? Seed files are static CSVs stored in your Git repository. Since they are static, do they really need to be tested?
Yes. In fact, because seeds are usually created manually by humans (e.g., an analyst typing an Excel file), they are highly prone to typos, missing values, and duplicate rows. If you do not test your seeds, a single typo in a mapping table can ruin your aggregations.
Topics Covered
- Why test static seeds?
- Configuring tests for seeds
- The execution workflow
1. Why Test Static Seeds?
Let's assume your company uses a seed file called indian_state_codes.csv to map two-letter abbreviations to full state names.
state_code,state_name,region
MH,Maharashtra,West
KA,Karnataka,South
DL,Delhi,North
MH,Meghalaya,North East <-- HUMAN ERROR!
An analyst accidentally assigned the code MH to Meghalaya (it should be ML).
If you join this seed to your fct_orders table to calculate revenue by state, every single order from Maharashtra will suddenly double because the JOIN will hit two rows instead of one! This is a classic "fan-out" error caused by a dirty seed file.
2. Configuring Tests for Seeds
Testing seeds is identical to testing models. You define the tests in a YAML file.
By convention, if you have a seeds/ folder, you should create a seeds/schema.yml file to hold the definitions.
# seeds/schema.yml
version: 2
seeds:
- name: indian_state_codes
description: "Static mapping of Indian state abbreviations."
columns:
- name: state_code
description: "The two-letter abbreviation."
tests:
# This test would have caught the Meghalaya typo!
- unique
- not_null
- name: region
description: "The geographic region."
tests:
- not_null
- accepted_values:
values: ['North', 'South', 'East', 'West', 'North East', 'Central']
3. The Execution Workflow
Once you define tests on a seed, how do you run them?
Step 1: Load the Seed
First, you must load the physical data into the data warehouse.
dbt seed
Step 2: Test the Seed
You can test the seed individually using the dbt test command with a node selector.
dbt test --select indian_state_codes
Step 3: The Unified dbt build
In a production CI/CD environment, you don't run seed and test separately. You use dbt build.
When you run dbt build, dbt will automatically:
- Detect that
indian_state_codesis a seed and load it into Snowflake. - Immediately run the
uniqueandnot_nulltests on it. - If the tests pass, it proceeds to build the downstream models (
stg_orders,fct_orders). - If the tests fail (because of the
MHtypo), it immediately halts the pipeline, preventing the bad mapping table from corrupting your fact tables.
Summary
- Seeds are highly prone to human error (typos, duplicates, missing values).
- You must test your seeds to prevent SQL
JOINfan-outs in downstream models. - Configure seed tests in a
seeds/schema.ymlfile exactly as you would for models. - The
dbt buildcommand will automatically load the seed and test it before allowing downstream models to execute.