adding-dbt-unit-test

Name: adding-dbt-unit-test
Author: dbt-labs

637stars55forks

How to install adding-dbt-unit-test?

dbt-labs/dbt-agent-skills/adding-dbt-unit-test

$npx -y skills add dbt-labs/dbt-agent-skills --skill adding-dbt-unit-test

Installs into the current project.

Summary

Creates unit test YAML definitions that mock upstream model inputs and validate expected outputs. Use when adding unit tests for a dbt model or practicing test-driven development (TDD) in dbt.

Files · 1

View on GitHub

SKILL.md

1	# Add unit test for a dbt model
2
3	## Additional Resources
4
5	- [Spec Reference](references/spec.md) - All required and optional YAML keys for unit tests
6	- [Examples](references/examples.md) - Unit test examples across formats (dict, csv, sql)
7	- [Incremental Models](references/special-cases-incremental-model.md) - Unit testing incremental models
8	- [Ephemeral Dependencies](references/special-cases-ephemeral-dependency.md) - Unit testing models depending on ephemeral models
9	- [Special Case Overrides](references/special-cases-special-case-overrides.md) - Introspective macros, project variables, environment variables
10	- [Versioned Models](references/special-cases-versioned-model.md) - Unit testing versioned SQL models
11	- [BigQuery Caveats](references/warehouse-bigquery-caveats.md) - BigQuery-specific caveats
12	- [BigQuery Data Types](references/warehouse-bigquery-data-types.md) - BigQuery data type handling
13	- [Postgres Data Types](references/warehouse-postgres-data-types.md) - Postgres data type handling
14	- [Redshift Caveats](references/warehouse-redshift-caveats.md) - Redshift-specific caveats
15	- [Redshift Data Types](references/warehouse-redshift-data-types.md) - Redshift data type handling
16	- [Snowflake Data Types](references/warehouse-snowflake-data-types.md) - Snowflake data type handling
17	- [Spark Data Types](references/warehouse-spark-data-types.md) - Spark data type handling
18
19	## What are unit tests in dbt
20
21	dbt unit tests validate SQL modeling logic on static inputs before materializing in production. If any unit test for a model fails, dbt will not materialize that model.
22
23	## When to use
24
25	You should unit test a model:
26	- Adding Model-Input-Output scenarios for the intended functionality of the model as well as edge cases to prevent regressions if the model logic is changed at a later date.
27	- Verifying that a bug fix solves a bug report for an existing dbt model.
28
29	More examples:
30	- When your SQL contains complex logic:
31	- Regex
32	- Date math
33	- Window functions
34	- `case when` statements when there are many `when`s
35	- Truncation
36	- Complex joins (multiple joins, self-joins, or joins with non-trivial conditions)
37	- When you're writing custom logic to process input data, similar to creating a function.
38	- Logic for which you had bugs reported before.
39	- Edge cases not yet seen in your actual data that you want to be confident you are handling properly.
40	- Prior to refactoring the transformation logic (especially if the refactor is significant).
41	- Models with high "criticality" (public, contracted models or models directly upstream of an exposure).
42
43	## When not to use
44
45	Cases we don't recommend creating unit tests for:
46	- Built-in functions that are tested extensively by the warehouse provider. If an unexpected issue arises, it's more likely a result of issues in the underlying data rather than the function itself. Therefore, fixture data in the unit test won't provide valuable information.
47	- common SQL spec functions like `min()`, etc.
48
49	## General format
50
51	dbt unit test uses a trio of the model, given inputs, and expected outputs (Model-Inputs-Outputs):
52
53	1. `model` - when building this model
54	2. `given` inputs - given a set of source, seeds, and models as preconditions
55	3. `expect` output - then expect this row content of the model as a postcondition
56
57	### Workflow
58
59	### 1. Choose the model to test
60
61	Self explanatory -- the title says it all!
62
63	### 2. Mock the inputs
64
65	- Create an input for each of the nodes the model depends on.
66	- Specify the mock data it should use.
67	- Specify the `format` if different than the default (YAML `dict`).
68	- See the "Data `format`s for unit tests" section below to determine which `format` to use.
69	- The mock data only needs include the subset of columns used within this test case.
70
71	Tip: Use `dbt show` to explore existing data from upstream models or sources. This helps you understand realistic input structures. However, always sanitize the sample data to remove any sensitive or PII information before using it in your unit test fixtures.
72
73	```shell
74	# Preview upstream model data
75	dbt show --select upstream_model --limit 5
76	```
77
78	### 3. Mock the output
79
80	- Specify the data that you expect the model to create given those inputs.
81	- Specify the `format` if different than the default (YAML `dict`).
82	- See the "Data `format`s for unit tests" section below to determine which `format` to use.
83	- The mock data only needs include the subset of columns used within this test case.
84
85	### 4. Ensure upstream models exist before running
86
87	Unit tests require direct parent models to exist in the warehouse. Before running unit tests standalone (`dbt test`), verify that upstream models already exist first:
88
89	```bash
90	#

People who liked this also viewed

6 picks

Type

adding-dbt-unit-test

637stars55forks

View on GitHub

How to install adding-dbt-unit-test?

dbt-labs/dbt-agent-skills/adding-dbt-unit-test

$npx -y skills add dbt-labs/dbt-agent-skills --skill adding-dbt-unit-test

Installs into the current project.

Summary

Creates unit test YAML definitions that mock upstream model inputs and validate expected outputs. Use when adding unit tests for a dbt model or practicing test-driven development (TDD) in dbt.

Files · 1

View on GitHub

SKILL.md

1	# Add unit test for a dbt model
2
3	## Additional Resources
4
5	- [Spec Reference](references/spec.md) - All required and optional YAML keys for unit tests
6	- [Examples](references/examples.md) - Unit test examples across formats (dict, csv, sql)
7	- [Incremental Models](references/special-cases-incremental-model.md) - Unit testing incremental models
8	- [Ephemeral Dependencies](references/special-cases-ephemeral-dependency.md) - Unit testing models depending on ephemeral models
9	- [Special Case Overrides](references/special-cases-special-case-overrides.md) - Introspective macros, project variables, environment variables
10	- [Versioned Models](references/special-cases-versioned-model.md) - Unit testing versioned SQL models
11	- [BigQuery Caveats](references/warehouse-bigquery-caveats.md) - BigQuery-specific caveats
12	- [BigQuery Data Types](references/warehouse-bigquery-data-types.md) - BigQuery data type handling
13	- [Postgres Data Types](references/warehouse-postgres-data-types.md) - Postgres data type handling
14	- [Redshift Caveats](references/warehouse-redshift-caveats.md) - Redshift-specific caveats
15	- [Redshift Data Types](references/warehouse-redshift-data-types.md) - Redshift data type handling
16	- [Snowflake Data Types](references/warehouse-snowflake-data-types.md) - Snowflake data type handling
17	- [Spark Data Types](references/warehouse-spark-data-types.md) - Spark data type handling
18
19	## What are unit tests in dbt
20
21	dbt unit tests validate SQL modeling logic on static inputs before materializing in production. If any unit test for a model fails, dbt will not materialize that model.
22
23	## When to use
24
25	You should unit test a model:
26	- Adding Model-Input-Output scenarios for the intended functionality of the model as well as edge cases to prevent regressions if the model logic is changed at a later date.
27	- Verifying that a bug fix solves a bug report for an existing dbt model.
28
29	More examples:
30	- When your SQL contains complex logic:
31	- Regex
32	- Date math
33	- Window functions
34	- `case when` statements when there are many `when`s
35	- Truncation
36	- Complex joins (multiple joins, self-joins, or joins with non-trivial conditions)
37	- When you're writing custom logic to process input data, similar to creating a function.
38	- Logic for which you had bugs reported before.
39	- Edge cases not yet seen in your actual data that you want to be confident you are handling properly.
40	- Prior to refactoring the transformation logic (especially if the refactor is significant).
41	- Models with high "criticality" (public, contracted models or models directly upstream of an exposure).
42
43	## When not to use
44
45	Cases we don't recommend creating unit tests for:
46	- Built-in functions that are tested extensively by the warehouse provider. If an unexpected issue arises, it's more likely a result of issues in the underlying data rather than the function itself. Therefore, fixture data in the unit test won't provide valuable information.
47	- common SQL spec functions like `min()`, etc.
48
49	## General format
50
51	dbt unit test uses a trio of the model, given inputs, and expected outputs (Model-Inputs-Outputs):
52
53	1. `model` - when building this model
54	2. `given` inputs - given a set of source, seeds, and models as preconditions
55	3. `expect` output - then expect this row content of the model as a postcondition
56
57	### Workflow
58
59	### 1. Choose the model to test
60
61	Self explanatory -- the title says it all!
62
63	### 2. Mock the inputs
64
65	- Create an input for each of the nodes the model depends on.
66	- Specify the mock data it should use.
67	- Specify the `format` if different than the default (YAML `dict`).
68	- See the "Data `format`s for unit tests" section below to determine which `format` to use.
69	- The mock data only needs include the subset of columns used within this test case.
70
71	Tip: Use `dbt show` to explore existing data from upstream models or sources. This helps you understand realistic input structures. However, always sanitize the sample data to remove any sensitive or PII information before using it in your unit test fixtures.
72
73	```shell
74	# Preview upstream model data
75	dbt show --select upstream_model --limit 5
76	```
77
78	### 3. Mock the output
79
80	- Specify the data that you expect the model to create given those inputs.
81	- Specify the `format` if different than the default (YAML `dict`).
82	- See the "Data `format`s for unit tests" section below to determine which `format` to use.
83	- The mock data only needs include the subset of columns used within this test case.
84
85	### 4. Ensure upstream models exist before running
86
87	Unit tests require direct parent models to exist in the warehouse. Before running unit tests standalone (`dbt test`), verify that upstream models already exist first:
88
89	```bash
90	#

People who liked this also viewed

6 picks

Type