## TL;DR
Define `unit_tests:` on the model with `given` inputs (mocked rows per upstream input) and `expect` rows (the required output). dbt runs the model SQL against the fixtures, so logic bugs surface in seconds without touching real data.

```text
dbt unit tests with input fixtures
```

## Use this when
- You want to test transformation logic, not data quality
- Tests must run fast without warehouse data
- A unit test fails and the fixture looks wrong

## Not for this skill when
- You are testing data already in production (use dbt tests)
- Seeds infer wrong column types
- You are choosing snapshot strategies

## Steps

1. Write the test in the model's YAML file:

```yaml
models:
  - name: order_totals
    unit_tests:
      - name: sums_line_items_per_order
        given:
          - input: ref('stg_line_items')
            rows:
              - {order_id: 1, amount: 10.00}
              - {order_id: 1, amount: 20.00}
              - {order_id: 2, amount: 5.00}
        expect:
          rows:
            - {order_id: 1, total: 30.00}
            - {order_id: 2, total: 5.00}
```
Expected output: the contract. `given` mocks every input the model reads; `expect` states the exact output rows.

2. Run just the unit tests:

```bash
dbt test --select test_type:unit
```
Expected output: pass/fail per unit test with a diff of expected vs actual rows on failure. Failures show the mismatched rows, which is usually enough to spot the bug.

3. Mock ALL inputs the model touches. The common failure:

```text
If the model refs stg_orders and stg_line_items but the test only
mocks stg_line_items, the test errors on the missing input.
Every ref, source, and macro input needs a given block.
```
Expected output: complete fixtures. Audit the model's refs when a test errors on setup rather than on logic.

4. Test the edge cases that break in production, not just the happy path:

```yaml
given:
  - input: ref('stg_line_items')
    rows:
      - {order_id: 1, amount: null}      # null handling
      - {order_id: 2, amount: -5.00}     # negative handling
      - {order_id: 3, amount: 0.00}      # zero handling
```
Expected output: fixtures covering nulls, negatives, zeros, and empty inputs. Unit tests earn their keep on exactly these rows.

5. Know what unit tests do not cover:

```text
Unit tests verify SQL logic against fixtures. They do not verify:
- that upstream models produce the fixture shape (use data tests),
- performance on real volumes,
- warehouse-specific function behavior differences.
Pair them with dbt data tests on the real tables.
```
Expected output: a testing strategy with both layers, not a false sense of coverage from one.

## Variant phrasings

### dbt unit test given expect example
Step 1 is the canonical shape. `given` per input, `expect` for the model output.

### dbt unit test failing fixture
The fixture rows do not match what the model actually reads (wrong columns, missing input). Compare the model's refs against the given blocks (step 3).

### dbt unit tests vs data tests
Unit tests mock inputs and test logic; data tests (unique, not_null, custom) run against real built tables. Use both.

## Why it happens
dbt compiles the model SQL and runs it against the fixture rows as ephemeral tables. This isolates the transformation logic from data availability, so tests run in CI in seconds and catch logic regressions before the warehouse is even involved.

## Edge cases
- Fixture column types follow the mock, not the real table; type-sensitive logic (e.g. integer division) can behave differently.
- Macros with side effects or adapter-specific SQL may not work in the unit-test context.
- `overrides` exist for env vars and macros when the model needs them; without overrides those tests error.
- Keep fixtures small; a 500-row fixture is a data test wearing a costume.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_pgF-mCCW_gHU5rtWFkHYvQ