A dbt seed of ZIP codes loads as integers and loses leading zeros — how do you fix it?

## Why the zeros vanish A CSV carries no type information. dbt reads the header for column names and then infers a type per column from the values it sees, handing that type to the adapter when it creates the table. A column containing `02134`, `01610`, `90210` looks like integers to any reasonable inference routine, so the column is created numeric and `02134` becomes `2134`. Nothing errors; the data is simply wrong, and it usually surfaces weeks later when a join against a properly-typed ZIP column returns no rows. The same failure hits several shapes: - product or account codes with leading zeros (`00742`) - phone numbers and postal codes generally - version strings like `1.10`, which infer as a float and become `1.1` - identifiers long enough to lose precision in a numeric type - ambiguous date-like strings that get parsed into dates you did not want ## The fix: column_types Declare the type instead of letting dbt guess. In `dbt_project.yml`: ```yaml seeds: my_project: zip_codes: +column_types: zip_code: varchar(10) state_code: varchar(2) ``` Or per seed in a properties YAML alongside the file: ```yaml version: 2 seeds: - name: zip_codes config: column_types: zip_code: varchar(10) ``` The values on the right are raw data types passed to your warehouse, so use the adapter's spelling — `varchar(10)`, `string`, `text` depending on the platform. Only the columns you name are overridden; the rest stay inferred. ## Why a plain reload is not enough This is the half that gets missed in interviews. `dbt seed` on an existing seed table truncates it and re-inserts the rows — the table's DDL, including column types, is untouched. So you edit `column_types`, re-run `dbt seed`, and the zeros are still gone, because the column is still an integer and the inserted strings are coerced right back. `dbt seed --full-refresh` drops the table and recreates it from the current configuration. The same applies whenever the CSV's columns change: adding, removing or renaming a column requires a full refresh, because truncate-and-insert cannot reshape a table. ## Related seed configs `quote_columns` decides whether dbt quotes column names in the generated DDL. It matters on warehouses that fold identifier case: unquoted names get folded to the platform's default, so a CSV header of `ZipCode` may become `zipcode` or `ZIPCODE`. Quote them if downstream SQL depends on the exact casing, and be consistent, since flipping it later needs a full refresh too. Newer dbt versions also expose a `delimiter` config for CSVs that are not comma-separated. Other useful ones: `+schema` to park reference seeds away from models, `+enabled` to switch one off, `+tags` for selection. ## Guarding against the next occurrence Inference bugs are silent, so add a test. A `not_null` plus a singular test asserting `length(zip_code) = 5` catches truncation immediately, and it runs on every `dbt build`. Documenting the column in the seed's properties file also gives the next person a reason to look before editing the CSV. A blunter guard some teams use is to pin `column_types` for every column of every seed as a matter of policy. It is verbose, but it removes an entire class of surprise, and seeds are small enough that the verbosity costs little. ## When to stop using a seed If a reference file needs careful typing across many columns, changes often, or is growing, the seed is being asked to do a loader's job. At that point the honest move is to land it through the ingestion pipeline as a real source with an explicit schema, and leave seeds for the handful of small mappings that genuinely belong in the repository. ## Answering this in an interview Name the cause (inference from values, not from the file), the config (`column_types` with an adapter-appropriate type), and the operational step (`--full-refresh`, because truncate-and-insert leaves the DDL alone). Mentioning a test that would have caught it turns a correct answer into a good one.