Seeds for static reference data in dbt - Deep Dive

Overview - Seeds for static reference data

What is it?

Seeds in dbt are simple CSV files that hold static reference data. This data does not change often and is used to enrich or join with other datasets during transformations. Instead of storing this data in a database table manually, dbt loads these CSV files automatically into your data warehouse. This makes managing small, fixed datasets easier and keeps your project organized.

Why it matters

Without seeds, teams often manually create and maintain static reference tables in the database, which can lead to errors and inconsistencies. Seeds automate this process, ensuring that static data is version-controlled, easy to update, and always in sync with your dbt project. This saves time and reduces mistakes when working with important reference data like country codes, product categories, or status lists.

Where it fits

Before learning seeds, you should understand basic dbt project structure and how models work. After seeds, you can explore more advanced dbt features like snapshots and incremental models. Seeds fit early in the data transformation workflow as a foundation for joining static data with dynamic datasets.

Mental Model

Core Idea

Seeds are like small, fixed lookup tables stored as CSV files that dbt loads into your warehouse to use as reliable reference data.

Think of it like...

Imagine a recipe book where some ingredients are always the same, like salt or sugar. Instead of writing them down every time, you keep a small list of these staple ingredients handy. Seeds are that list for your data transformations.

┌───────────────┐       ┌───────────────┐
│  seeds/       │  -->  │  CSV files    │
│  (folder)     │       └───────────────┘
└──────┬────────┘              │
       │                       ▼
       │               ┌───────────────┐
       │               │ dbt loads CSV │
       │               │ into database │
       │               └──────┬────────┘
       │                      │
       ▼                      ▼
┌───────────────┐       ┌───────────────┐
│  dbt project  │       │  Reference    │
│  transformations│     │  tables in    │
└───────────────┘       │  warehouse    │
                        └───────────────┘

Build-Up - 7 Steps

1

Foundation

What are dbt seeds

Concept: Seeds are CSV files stored in a special folder in your dbt project that dbt can load into your data warehouse as tables.

In your dbt project, you create a folder named 'data' or 'seeds' and place CSV files inside. Each CSV file represents a small table of static data. When you run 'dbt seed', dbt reads these files and creates tables in your warehouse with the same names.

Result

Static tables appear in your warehouse matching the CSV files, ready to be used in models.

Understanding seeds as CSV files that become tables helps you manage static data alongside your transformations without manual database work.

2

Foundation

Why use seeds for static data

Concept: Seeds automate loading and version control of static reference data, making your project more reliable and maintainable.

Static data like country codes or product categories rarely change but are essential for joins and filters. Instead of manually creating these tables in the warehouse, seeds let you keep this data in your project files. This means changes are tracked in version control and deployed consistently.

Result

Static reference data is always up-to-date and consistent across environments.

Knowing seeds reduce manual errors and improve collaboration by keeping static data in code is key to professional dbt workflows.

3

Intermediate

How to configure and run seeds

🤔Before reading on: do you think you need to write SQL to load seeds or does dbt handle it automatically? Commit to your answer.

Concept: dbt automatically loads seeds when you run 'dbt seed', but you can configure options like file paths, schema, and quoting in the project config.

You run 'dbt seed' in your terminal, and dbt reads all CSV files in the seeds folder. You can configure seed behavior in 'dbt_project.yml' to control where tables are created and how columns are quoted. This flexibility helps match your warehouse's requirements.

Result

Seeds load into the warehouse with your specified settings, ready for use in models.

Understanding configuration options lets you adapt seeds to different warehouses and naming conventions without extra SQL.

4

Intermediate

Using seeds in dbt models

🤔Before reading on: do you think seeds are referenced like regular tables or do they require special syntax? Commit to your answer.

Concept: Seeds become regular tables in your warehouse and can be referenced in dbt models just like any other table.

In your SQL models, you can join or select from seed tables using the usual ref() function, e.g., ref('my_seed'). This makes it easy to combine static reference data with dynamic data in transformations.

Result

Models can enrich data with static info from seeds seamlessly.

Knowing seeds behave like normal tables in SQL lets you integrate static data naturally without special handling.

5

Intermediate

Managing seed updates and version control

🤔Before reading on: do you think changing a seed CSV requires manual database updates or just updating the file? Commit to your answer.

Concept: Updating seeds is as simple as editing the CSV file and rerunning 'dbt seed'; dbt handles syncing changes to the warehouse.

When you change a seed CSV, you commit the change to version control. Running 'dbt seed' again updates the warehouse table to match. This keeps static data consistent and auditable over time.

Result

Seed tables in the warehouse always reflect the latest CSV files under version control.

Understanding that seeds are version-controlled files means you treat static data like code, improving reliability and traceability.

6

Advanced

Seed limitations and performance considerations

🤔Before reading on: do you think seeds are suitable for very large datasets or only small static tables? Commit to your answer.

Concept: Seeds are best for small, static datasets; large or frequently changing data should use other dbt models or sources.

Because seeds load entire CSV files into tables, very large files can slow down dbt runs and consume warehouse resources. Also, seeds do not support incremental updates; the whole table is replaced each time. For large or dynamic data, use incremental models or external sources instead.

Result

You avoid performance issues and maintain efficient workflows by using seeds appropriately.

Knowing seeds' limits prevents misuse that can cause slow builds or data inconsistencies in production.

7

Expert

Advanced seed usage and customization

🤔Before reading on: do you think you can customize column data types or apply transformations directly on seeds? Commit to your answer.

Concept: While seeds load raw CSV data, you can customize column types and apply transformations in downstream models for flexibility.

dbt seeds load data as strings by default, but you can configure column types in 'dbt_project.yml' to match warehouse types. For complex transformations, you create models that select from seeds and cast or clean data. This separation keeps seeds simple and transformations flexible.

Result

You maintain clean, typed reference data without complicating seed files.

Understanding that seeds are raw data inputs and transformations belong in models helps maintain clear, maintainable dbt projects.

Under the Hood

When you run 'dbt seed', dbt reads each CSV file in the seeds folder and generates SQL commands to create or replace tables in your warehouse. It uploads the CSV data directly, handling quoting and escaping as needed. The tables created match the CSV filenames and are placed in the configured schema. dbt does not transform seed data during loading; it simply loads it as-is.

Why designed this way?

Seeds were designed to simplify managing static reference data by keeping it in version-controlled files rather than manual database tables. This approach reduces errors, improves collaboration, and fits naturally into dbt's code-centric workflow. Loading raw CSVs without transformation keeps seeds simple and focused on data delivery, leaving transformations to models.

┌───────────────┐
│ CSV files in  │
│ seeds folder  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ dbt seed      │
│ command reads │
│ CSV files     │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Generate SQL  │
│ to create or  │
│ replace table │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Load CSV data │
│ into warehouse│
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think seeds automatically update incrementally when CSV changes? Commit yes or no.

Common Belief: Seeds update only the changed rows when you rerun 'dbt seed'.

Reality: Seeds replace the entire table each time you run 'dbt seed'; there is no incremental update.

Why it matters: Assuming incremental updates can cause unexpected data loss or performance issues when large seed tables are replaced fully.

Quick: Do you think you can write SQL inside seed CSV files? Commit yes or no.

Common Belief: You can include SQL expressions or formulas inside seed CSV files to transform data on load.

Reality: Seed files are plain CSVs with raw data; dbt does not process SQL inside them. Transformations must happen in models.

Why it matters: Expecting SQL in seeds leads to confusion and errors; transformations belong in dbt models, not seed files.

Quick: Do you think seeds can be used for large, frequently changing datasets? Commit yes or no.

Common Belief: Seeds are suitable for any size of data and frequent updates.

Reality: Seeds are intended for small, static datasets; large or frequently changing data should use incremental models or external sources.

Why it matters: Using seeds for large data causes slow builds and inefficient resource use.

Quick: Do you think seed tables are created in the same schema as your models by default? Commit yes or no.

Common Belief: Seed tables always appear in the same schema as dbt models by default.

Reality: Seeds load into a separate schema configurable in 'dbt_project.yml', often different from models' schema.

Why it matters: Assuming seeds share model schema can cause confusion in table references and permissions.

Expert Zone

1

Seeds load data as strings by default; explicit type casting in models is needed for correct data types.

2

Seed tables are fully replaced on each run, so managing dependencies and run order is important to avoid race conditions.

3

You can configure quoting and delimiter options per seed file to handle special characters or formats in CSVs.

When NOT to use

Avoid seeds for large datasets or data that changes frequently. Instead, use incremental models, snapshots, or external sources for better performance and flexibility.

Production Patterns

In production, seeds are used for small lookup tables like country codes, status lists, or fixed mappings. Teams version control seed files and include 'dbt seed' in CI/CD pipelines to ensure static data consistency across environments.

Connections

Version Control Systems (e.g., Git)

Seeds are stored as CSV files in version control alongside dbt code.

Treating static data as code enables tracking changes, collaboration, and rollback, improving data reliability.

Data Warehousing

Seeds load static reference data directly into the warehouse as tables.

Understanding how seeds create tables helps grasp how data warehouses organize and store reference data for efficient querying.

Software Configuration Management

Seeds configuration in 'dbt_project.yml' controls how static data is loaded and managed.

Managing seeds like configuration files shows the importance of declarative setups in reproducible data workflows.

Common Pitfalls

#1 Trying to update seed data by editing the warehouse table directly.

Wrong approach:

UPDATE seeds_table SET country_name = 'NewName' WHERE country_code = 'US';

Correct approach:

Edit the CSV file in the seeds folder and run 'dbt seed' to apply changes.

Root cause:

Misunderstanding that seeds are managed by dbt and changes must come from source CSV files, not manual database edits.

#2 Using seeds for large datasets that change often.

Wrong approach:

Placing a 10 million row CSV in seeds and running 'dbt seed' daily.

Correct approach:

Use incremental models or external tables for large or frequently updated data.

Root cause:

Not recognizing seeds are designed for small, static data, leading to performance and maintenance issues.

#3 Referencing seed tables without using the ref() function in models.

Wrong approach:

SELECT * FROM seeds_table WHERE id = 1;

Correct approach:

SELECT * FROM {{ ref('seeds_table') }} WHERE id = 1;

Root cause:

Not following dbt best practices for dependency management and environment portability.

Key Takeaways

Seeds in dbt are CSV files that load static reference data into your warehouse as tables.

They simplify managing small, fixed datasets by keeping them version-controlled and integrated with your dbt project.

Seeds replace entire tables on each run and are best suited for small, rarely changing data.

You reference seeds in models like regular tables using the ref() function for seamless integration.

Proper use of seeds improves data consistency, reduces manual errors, and fits naturally into automated data workflows.