Skip to main content

Supported data formats for unit tests

Currently, mock data for unit testing in dbt supports three formats:

  • dict (default): Inline dictionary values.
  • csv: Inline CSV values or a CSV file.
  • sql: Inline SQL query or a SQL file. Note: For this format you must supply mock data for all rows.

dict

The dict data format is the default if no format is defined.

dict requires an inline dictionary for rows:


unit_tests:
- name: test_my_model
model: my_model
given:
- input: ref('my_model_a')
format: dict
rows:
- {id: 1, name: gerda}
- {id: 2, b: michelle}

csv

When using the csv format, you can use either an inline CSV string for rows:


unit_tests:
- name: test_my_model
model: my_model
given:
- input: ref('my_model_a')
format: csv
rows: |
id,name
1,gerda
2,michelle

Or, you can provide the name of a CSV file in the tests/fixtures directory (or the configured test-paths location) of your project for fixture:


unit_tests:
- name: test_my_model
model: my_model
given:
- input: ref('my_model_a')
format: csv
fixture: my_model_a_fixture

sql

Using this format:

  • Provides more flexibility for the types of data you can unit test
  • Allows you to unit test a model that depends on an ephemeral model

However, when using format: sql you must supply mock data for all rows.

When using the sql format, you can use either an inline SQL query for rows:


unit_tests:
- name: test_my_model
model: my_model
given:
- input: ref('my_model_a')
format: sql
rows: |
select 1 as id, 'gerda' as name, null as loaded_at union all
select 2 as id, 'michelle', null as loaded_at as name

Or, you can provide the name of a SQL file in the tests/fixtures directory (or the configured test-paths location) of your project for fixture:


unit_tests:
- name: test_my_model
model: my_model
given:
- input: ref('my_model_a')
format: sql
fixture: my_model_a_fixture

Note: Jinja is unsupported in SQL fixtures for unit tests.

0