Unit tests and seed type inference
The problem
A staging model is a one-line SELECT with a cast. You still want to know that amount survives as a decimal, not as an integer, without hitting the warehouse’s full sources. Official dbt documentation provides unit tests: YAML fixtures under model-paths (for example models/), not under tests/.
A working example
# models/unit_tests.yml
unit_tests:
- name: stg_orders_casts_amount
model: stg_orders
given:
- input: ref('raw_orders')
rows:
- {order_id: 1, customer_id: 1, status: completed, amount: "10.50", order_date: "2026-01-01"}
expect:
rows:
- {order_id: 1, customer_id: 1, status: completed, amount: "10.50", order_date: "2026-01-01"}
The staging model is:
select
order_id,
customer_id,
status,
amount::decimal(10,2) as amount,
order_date::date as order_date
from {{ ref('raw_orders') }}
First run (before seed types were declared): the unit test failed. Compiled fixture SQL included cast('10.50' as INTEGER) for amount, and the expected-vs-actual diff was 10.50 → 11.00. dbt inferred the seed column as an integer (every value in raw_orders.csv was a whole number with a trailing .00), then the unit-test fixture used that type, then 10.50 rounded to 11 on the integer cast, then the model’s decimal(10,2) display became 11.00.
After adding seed column_types so amount is decimal(10,2) and re-seeding, the same unit test passed on dbt-core 1.12.7 / dbt-duckdb 1.11.1.
seeds:
- name: raw_orders
config:
column_types:
amount: decimal(10,2)
order_date: date
That is the kind of failure a unit test is for: the model SQL looked fine, and the warehouse seed inference was the trap.
The gotcha
Official docs: unit test YAML must live under model-paths, not in tests/ (test-paths is for data tests). Put a unit test in tests/ and you should not expect dbt test --select test_type:unit to see it.
Fixture types follow the input relation. If that relation is a seed without column_types, you inherit CSV inference, which is often more aggressive than the spreadsheet looks.
YAML floats are also a poor way to express money. The first attempt used amount: 10.5 (a YAML float) and compiled to an integer cast as well. Quoted "10.50" plus an explicit seed type is the combination that passed here.
When not to use this
- You are asserting something about real warehouse data (orphaned keys, freshness). That is a data test.
- The model is a thin pass-through and the types are already enforced by a contract plus a seed schema — you may still want one unit test, but not twenty.
- You cannot express the input as a small fixture (huge JSON payloads, random UDFs). Use a narrowly scoped data test instead.
Both outcomes were executed: the unit test failed with 10.50 → 11.00 and cast('10.50' as INTEGER) in the compiled fixture, then passed after seed column_types were set. dbt-core 1.12.7, dbt-duckdb 1.11.1. The rule that unit tests live under model-paths is from official dbt documentation.
Official docs
- Unit tests
- Seed column_types
- Add data tests to your DAG (where unit tests may not live)