testing-dbt-models

Name: testing-dbt-models
Author: AltimateAI

23installs7forks

How to install testing-dbt-models?

altimateai/data-engineering-skills/testing-dbt-models

$npx -y skills add AltimateAI/data-engineering-skills --skill testing-dbt-models

Installs into the current project.

Summary

Adds schema tests and data quality validation to dbt models. Use when working with dbt tests for: (1) Adding or modifying tests in schema.yml files (2) Task mentions "test", "validate", "data quality", "unique", "not_null", or "accepted_values" (3) Ensuring data integrity - prima

Files · 1

View on GitHub

SKILL.md

1	# dbt Testing
2
3	Every model deserves at least one test. Primary keys need unique + not_null.
4
5	## Workflow
6
7	### 1. Study Existing Test Patterns
8
9	CRITICAL: Match the project's existing testing style before adding new tests.
10
11	```bash
12	# Find all schema.yml files with tests
13	find . -name "schema.yml" -exec grep -l "tests:" {} \;
14
15	# Read existing tests to learn patterns
16	cat models/staging/schema.yml \| head -100
17	cat models/marts/schema.yml \| head -100
18
19	# Check for custom tests or dbt packages
20	ls tests/
21	cat packages.yml 2>/dev/null
22	```
23
24	Extract from existing tests:
25	- YAML formatting style (indentation, spacing)
26	- Test coverage depth (all columns vs key columns only)
27	- Use of custom tests (dbt_utils, dbt_expectations, custom macros)
28	- Description style (brief vs detailed)
29	- Severity levels used (warn vs error)
30
31	### 2. Read Model SQL
32
33	```bash
34	cat models/<path>/<model_name>.sql
35	```
36
37	Identify: primary keys, foreign keys, categorical columns, date columns, business-critical fields.
38
39	### 3. Check Existing Tests for This Model
40
41	```bash
42	cat models/<path>/schema.yml \| grep -A 50 "<model_name>"
43	# or
44	find . -name "schema.yml" -exec grep -l "<model_name>" {} \;
45	```
46
47	### 4. Identify Testable Columns
48
49	\| Column Type \| Recommended Tests \|
50	\|-------------\|-------------------\|
51	\| Primary key \| `unique`, `not_null` \|
52	\| Foreign key \| `not_null`, `relationships` \|
53	\| Categorical \| `accepted_values` (ask user for valid values) \|
54	\| Required field \| `not_null` \|
55	\| Date/timestamp \| `not_null` \|
56	\| Boolean \| `accepted_values: [true, false]` \|
57
58	### 5. Write Tests in schema.yml
59
60	Match the existing style from step 1. Example format (adapt to project):
61
62	```yaml
63	version: 2
64
65	models:
66	- name: model_name
67	description: "Brief description of what this model contains"
68	columns:
69	- name: primary_key_column
70	description: "Unique identifier for this record"
71	tests:
72	- unique
73	- not_null
74
75	- name: foreign_key_column
76	description: "Reference to related_model"
77	tests:
78	- not_null
79	- relationships:
80	to: ref('related_model')
81	field: related_key_column
82
83	- name: status
84	description: "Current status of the record"
85	tests:
86	- not_null
87	- accepted_values:
88	values: ['pending', 'active', 'completed', 'cancelled']
89
90	- name: created_at
91	description: "Timestamp when record was created"
92	tests:
93	- not_null
94	```
95
96	### 6. Run Tests
97
98	```bash
99	# Test specific model
100	dbt test --select <model_name>
101
102	# Test with upstream
103	dbt test --select +<model_name>
104	```
105
106	### 7. Fix Failing Tests
107
108	Common failures and fixes:
109
110	\| Failure \| Likely Cause \| Fix \|
111	\|---------\|--------------\|-----\|
112	\| `unique` fails \| Duplicate records \| Add deduplication in model \|
113	\| `not_null` fails \| NULL values in source \| Add COALESCE or filter \|
114	\| `relationships` fails \| Orphan records \| Add WHERE clause or fix upstream \|
115	\| `accepted_values` fails \| New/unexpected values \| Update accepted values list \|
116
117	## Test Types Reference
118
119	### Generic Tests (built-in)
120
121	```yaml
122	tests:
123	- unique
124	- not_null
125	- accepted_values:
126	values: ['a', 'b', 'c']
127	- relationships:
128	to: ref('other_model')
129	field: id
130	```
131
132	### Custom Generic Tests
133
134	```yaml
135	tests:
136	- dbt_utils.expression_is_true:
137	expression: "amount >= 0"
138	- dbt_utils.recency:
139	datepart: day
140	field: created_at
141	interval: 1
142	```
143
144	### Singular Tests
145
146	Create `tests/<test_name>.sql`:
147	```sql
148	-- tests/assert_positive_revenue.sql
149	select *
150	from {{ ref('orders') }}
151	where revenue < 0
152	```
153
154	## Anti-Patterns
155
156	- Adding tests without checking existing project patterns first
157	- Using different YAML formatting style than existing tests
158	- Models without any tests
159	- Primary keys without both unique AND not_null
160	- Testing only obvious columns, ignoring business-critical ones
161	- Hardcoding accepted_values without confirming with stakeholders
162	- Adding dbt_utils tests when project doesn't use that package

People who liked this also viewed

6 picks

Type

testing-dbt-models

23installs7forks

View on GitHub

How to install testing-dbt-models?

altimateai/data-engineering-skills/testing-dbt-models

$npx -y skills add AltimateAI/data-engineering-skills --skill testing-dbt-models

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# dbt Testing
2
3	Every model deserves at least one test. Primary keys need unique + not_null.
4
5	## Workflow
6
7	### 1. Study Existing Test Patterns
8
9	CRITICAL: Match the project's existing testing style before adding new tests.
10
11	```bash
12	# Find all schema.yml files with tests
13	find . -name "schema.yml" -exec grep -l "tests:" {} \;
14
15	# Read existing tests to learn patterns
16	cat models/staging/schema.yml \| head -100
17	cat models/marts/schema.yml \| head -100
18
19	# Check for custom tests or dbt packages
20	ls tests/
21	cat packages.yml 2>/dev/null
22	```
23
24	Extract from existing tests:
25	- YAML formatting style (indentation, spacing)
26	- Test coverage depth (all columns vs key columns only)
27	- Use of custom tests (dbt_utils, dbt_expectations, custom macros)
28	- Description style (brief vs detailed)
29	- Severity levels used (warn vs error)
30
31	### 2. Read Model SQL
32
33	```bash
34	cat models/<path>/<model_name>.sql
35	```
36
37	Identify: primary keys, foreign keys, categorical columns, date columns, business-critical fields.
38
39	### 3. Check Existing Tests for This Model
40
41	```bash
42	cat models/<path>/schema.yml \| grep -A 50 "<model_name>"
43	# or
44	find . -name "schema.yml" -exec grep -l "<model_name>" {} \;
45	```
46
47	### 4. Identify Testable Columns
48
49	\| Column Type \| Recommended Tests \|
50	\|-------------\|-------------------\|
51	\| Primary key \| `unique`, `not_null` \|
52	\| Foreign key \| `not_null`, `relationships` \|
53	\| Categorical \| `accepted_values` (ask user for valid values) \|
54	\| Required field \| `not_null` \|
55	\| Date/timestamp \| `not_null` \|
56	\| Boolean \| `accepted_values: [true, false]` \|
57
58	### 5. Write Tests in schema.yml
59
60	Match the existing style from step 1. Example format (adapt to project):
61
62	```yaml
63	version: 2
64
65	models:
66	- name: model_name
67	description: "Brief description of what this model contains"
68	columns:
69	- name: primary_key_column
70	description: "Unique identifier for this record"
71	tests:
72	- unique
73	- not_null
74
75	- name: foreign_key_column
76	description: "Reference to related_model"
77	tests:
78	- not_null
79	- relationships:
80	to: ref('related_model')
81	field: related_key_column
82
83	- name: status
84	description: "Current status of the record"
85	tests:
86	- not_null
87	- accepted_values:
88	values: ['pending', 'active', 'completed', 'cancelled']
89
90	- name: created_at
91	description: "Timestamp when record was created"
92	tests:
93	- not_null
94	```
95
96	### 6. Run Tests
97
98	```bash
99	# Test specific model
100	dbt test --select <model_name>
101
102	# Test with upstream
103	dbt test --select +<model_name>
104	```
105
106	### 7. Fix Failing Tests
107
108	Common failures and fixes:
109
110	\| Failure \| Likely Cause \| Fix \|
111	\|---------\|--------------\|-----\|
112	\| `unique` fails \| Duplicate records \| Add deduplication in model \|
113	\| `not_null` fails \| NULL values in source \| Add COALESCE or filter \|
114	\| `relationships` fails \| Orphan records \| Add WHERE clause or fix upstream \|
115	\| `accepted_values` fails \| New/unexpected values \| Update accepted values list \|
116
117	## Test Types Reference
118
119	### Generic Tests (built-in)
120
121	```yaml
122	tests:
123	- unique
124	- not_null
125	- accepted_values:
126	values: ['a', 'b', 'c']
127	- relationships:
128	to: ref('other_model')
129	field: id
130	```
131
132	### Custom Generic Tests
133
134	```yaml
135	tests:
136	- dbt_utils.expression_is_true:
137	expression: "amount >= 0"
138	- dbt_utils.recency:
139	datepart: day
140	field: created_at
141	interval: 1
142	```
143
144	### Singular Tests
145
146	Create `tests/<test_name>.sql`:
147	```sql
148	-- tests/assert_positive_revenue.sql
149	select *
150	from {{ ref('orders') }}
151	where revenue < 0
152	```
153
154	## Anti-Patterns
155
156	- Adding tests without checking existing project patterns first
157	- Using different YAML formatting style than existing tests
158	- Models without any tests
159	- Primary keys without both unique AND not_null
160	- Testing only obvious columns, ignoring business-critical ones
161	- Hardcoding accepted_values without confirming with stakeholders
162	- Adding dbt_utils tests when project doesn't use that package

People who liked this also viewed

6 picks

Type