dbt Packages & Tests

Unit tests and seed type inference

A unit test that failed when 10.50 was cast to INTEGER 11, then passed after seed column types were declared.

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

Verification

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