Seeds for Small Lookups

Concepts covered: Sources and seeds

Data that lives in the repository

Some tables have no system behind them: a mapping of 4 plan codes to a tier and a monthly price, 12 country codes grouped into 3 sales regions, the 9 internal test accounts every mart must exclude. Nobody loads them, somebody types them, they change a few times a year, and each change should go through the same review a model goes through. dbt calls such a file a seed: a CSV saved under seeds/ that dbt loads into the warehouse as a table, and that models ref() like any other node.

seeds/plan_codes.csv: 1 header line and 4 data lines, saved as plain text beside the models.

dbt seed reads every CSV under seeds/, creates 1 table per file in the target schema, named for the file, and inserts the rows. Running it again replaces the table's rows with whatever the file holds now, so editing the CSV and committing it is how the table changes. The first line of the file becomes the column names. dbt build loads seeds too, in DAG order, so a seed is in place before any model that refs it runs.

Pinning the types

seeds:
  - name: plan_codes
    config:
      column_types:
        plan_code: varchar(16)
        plan_tier: varchar(16)
        monthly_price_usd: decimal(6,2)

Without this file dbt infers each column's type from the values it sees. A column of postal codes like 02134 becomes an integer and loses its zero; a price column can land as a floating point type, where a value like 19.99 has no exact binary form and a sum over thousands of rows drifts by fractions of a cent. Declaring column_types under the seed's config makes every load produce the same columns whatever the values, and a reviewer can see the types in the same pull request as the data.

Reading a seed

select
    s.subscription_id,
    s.user_id,
    s.plan_code,
    p.plan_tier,
    p.monthly_price_usd
from {{ ref('stg_billing__subscriptions') }} as s
left join {{ ref('plan_codes') }} as p
    on s.plan_code = p.plan_code

ref('plan_codes') points at the seed's table and draws the edge in the DAG, so this model runs after the file has been loaded. The join is many to 1: plan_codes has 1 row per plan_code, so the subscription grain holds. That depends on the CSV. A second line for basic, added by hand with a new price, turns the join into 1 to many and every basic subscription appears twice. The key column of a seed must stay unique, and a reviewer reading the diff is what keeps it so.

A seed sits beside staging in the layers. Like a source, it enters the project from outside the SQL; like a staging model, it is read by intermediate models and marts through ref(). Some teams write a staging model over each seed so marts only ever see stg_ and int_ names; others ref the seed from an intermediate model directly. Either way a seed refs nothing, and a seed never stands in for a source table.

When the price of plus rises to 15.00, the CSV changes in a pull request, the reviewer sees 1 line changed, the next dbt seed replaces the table, and every model that refs it carries the new price on its next build. Compare that with a price hard-coded in 6 marts, where the same change is 6 edits and a search to find them.

Seed or source

A seed

• Typed by a person and changed through pull requests
• Hundreds of rows, a few thousand at most
• No system owns it; the project is its home
• Versioned with the code that reads it

A source

• Owned and written by another system
• Loaded into the warehouse by something other than dbt
• Any size, from 10 rows to 10 billion
• Changes without a commit, so dbt only reads it

A seed is read by a human before it is read by a machine, so size is the first test. A file of 300 rows is reviewable in a pull request; a file of 300,000 rows is a data load pretending to be code, and every dbt build inserts all 300,000 rows again. Data a system owns belongs as a source even when it is small, because the system will change it without a commit and the seed will drift. Personal data and secrets never belong in a seed, because the repository is readable by everyone with access to the code.

About this lesson

This section is part of the Models: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.