Seeding a Database From CSV Files With dbt Seeds Safely

Every analytics project needs a few small tables that belong to no source system: a list of country codes, a mapping from internal plan names to reporting groups, a table of fiscal calendar weeks. They change a few times a year, someone maintains them in a spreadsheet, and the question is where they should live. dbt has an answer that is simple enough to use, which is to commit them as CSV files and let dbt seed load them. It works well for that job and badly for most others, and the difference is worth knowing before a seed grows to a hundred thousand rows. This article shows the mechanics with a run, the type trap that bites everyone once, and the limits. It is the declarative cousin of automating CSV imports into a database, and a good fit for the lookup tables that your daily loads join to.

The mechanics

A dbt project has a seeds/ folder. Any CSV in it becomes a table of the same name when you run dbt seed. The project file points dbt at the folder, and a profile names the database. Here is a project with one seed:

name: shop
version: "1.0"
profile: shop
seed-paths: ["seeds"]

seeds:
  shop:
    country_codes:
      +column_types:
        iso_code: varchar(2)
        dial_code: varchar(8)

iso_code,name,dial_code,eu_member
DE,Germany,49,true
FR,France,33,true
GB,United Kingdom,44,false
IE,Ireland,353,true

dbt seed --profiles-dir .

Running with dbt=1.12.6
Registered adapter: duckdb=1.11.0
Found 1 seed, 500 macros
1 of 1 START seed file main.country_codes ...................................... [RUN]
1 of 1 OK loaded seed file main.country_codes .................................. [INSERT 4 in 0.05s]
Finished running 1 seed in 0 hours 0 minutes and 0.13 seconds (0.13s).
Completed successfully
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1

The table main.country_codes now exists with four rows. Models reference it with {{ ref('country_codes') }}, which gives dbt the dependency graph for free: a model that joins to the seed runs after it. In the test database the table's columns were iso_code, name and dial_code as text and eu_member as a boolean, which dbt inferred from true and false.

The type trap

The inference is where seeds hurt. dbt reads the CSV and chooses a type per column from the values, and a column of digits becomes an integer, whatever it means. Here is a seed of plant sites with a five-digit zip code, loaded with no column_types:

site,zip,opened
A,02134,2020-03-01
B,90210,2021-07-15

The load succeeds, and the table has zip as INTEGER with the values 2134 and 90210. The leading zero is gone. Nothing warned about it, and every join on zip code now fails for the Boston site. This is the same conversion that pandas performs on zip codes and Excel on identifiers, in a different tool.

The fix is to say what the column is:

seeds:
  shop:
    plants:
      +column_types:
        zip: varchar(5)

Then rebuild the table. Changing a column type does not alter an existing seed table, so run dbt seed --select plants --full-refresh, which drops and recreates it. After that the rows read ('A', '02134', ...) and ('B', '90210', ...). The first run printed INSERT 2, and the full refresh printed CREATE 2, which is the visible sign that the table was rebuilt and not appended to.

Make column_types a habit for every identifier-like column: zip codes, phone numbers, account codes, SKUs, and any code that happens to be numeric today. A new row with a letter in it next quarter would fail an integer column in a confusing way. Adding a dbt test on the seed, such as not_null and unique on its key, catches a duplicated mapping before a model uses it.

Delimiters, quoting and dates

dbt reads the seed with the adapter's CSV loader, and a few options matter. Seeds expect a comma by default, and a delimiter config sets another, such as a semicolon, for a file exported from a European spreadsheet. The column names come from the header, and dbt quotes them according to quote_columns, so a header with a space needs that setting on. Dates are inferred like other types: 2020-03-01 becomes a date, and anything ambiguous (03/04/2020) is risky, so write dates in ISO 8601 in the file and declare the type when it matters.

Keep seed files in UTF-8 without a byte order mark. A BOM at the start of the header turns the first column name into something invisible and different, and the join on it will fail with a baffling "column not found" (the UTF-8 BOM in CSV files explains how to spot it).

What belongs in a seed

The dbt documentation is clear about the intent: seeds are for static data that changes infrequently, such as a list of country codes or employee IDs mapped to a department, and they are not a way to load raw data or large files. There is a mechanical reason as well. dbt stores a checksum of each seed file to detect changes, and it declines to do that for files above 1 MB, so very large seeds lose change detection and are slow to load row by row.

A practical test: would a person edit this file by hand, review the change in a pull request and understand the diff? If yes, it is a seed. A mapping of 300 product categories to reporting groups qualifies, and a 200,000-row export from a source system does not. For the second kind of data, load it with a real loader and treat it as a source (the PostgreSQL loader or a plain Airflow task).

Treat the CSV as code

The advantage of a seed is that the data is versioned with the models. That makes review matter. A mapping CSV changed by someone who owns the spreadsheet can alter every dashboard that joins to it, and a one-character edit in a plan name drops a customer segment from a report. Put the seeds in the same review process as SQL: a pull request, a diff that shows the rows that changed (a CSV diff in a text diff is readable when the file is sorted, see keeping a CSV sorted so diffs stay small) and tests that run in CI against the new data.

A pattern that works: tests on every seed for uniqueness of the key, not-null on required columns and accepted_values for category columns. When the spreadsheet owner adds a row with a typo in the group name, CI fails with a message that names the value.

When a seed is the wrong tool

Use something else for anything that changes daily or is large. Daily data is a load job with a ledger, which is what automating CSV imports into a database describes. Data that comes from another system should come from that system, through a connector or an extract, not from a person pasting an export into a repository. And data that is sensitive does not belong in a git repository at all: seeds are committed in plain text, and a customer list in version control is a long-lived copy.

If your reference data is bigger than a few thousand rows or edited by many people, a small table with an admin screen is a better home, and dbt reads it as a source. The dbt documentation on seeds is at docs.getdbt.com, and the DuckDB adapter used for the run above is described in its repository.