refactoring-dbt-models

Name: refactoring-dbt-models
Author: AltimateAI

24installs7forks

How to install refactoring-dbt-models?

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

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

Installs into the current project.

Summary

Safely refactors dbt models with downstream impact analysis. Use when restructuring dbt models for: (1) Task mentions "refactor", "restructure", "extract", "split", "break into", or "reorganize" (2) Extracting CTEs to intermediate models or creating macros (3) Modifying model log

Files · 1

View on GitHub

SKILL.md

1	# dbt Refactoring
2
3	Find ALL downstream dependencies before changing. Refactor in small steps. Verify output after each change.
4
5	## Workflow
6
7	### 1. Analyze Current Model
8
9	```bash
10	cat models/<path>/<model_name>.sql
11	```
12
13	Identify refactoring opportunities:
14	- CTEs longer than 50 lines → extract to intermediate model
15	- Logic repeated across models → extract to macro
16	- Multiple joins in sequence → split into steps
17	- Complex WHERE clauses → extract to staging filter
18
19	### 2. Find All Downstream Dependencies
20
21	CRITICAL: Never refactor without knowing impact.
22
23	```bash
24	# Get full dependency tree (model and all its children)
25	dbt ls --select model_name+ --output list
26
27	# Find all models referencing this one
28	grep -r "ref('model_name')" models/ --include="*.sql"
29	```
30
31	Report to user: "Found X downstream models: [list]. These will be affected by changes."
32
33	### 3. Check What Columns Downstream Models Use
34
35	BEFORE changing any columns, check what downstream models reference:
36
37	```bash
38	# For each downstream model, check what columns it uses
39	cat models/<path>/<downstream_model>.sql \| grep -E "model_name\.\w+\|alias\.\w+"
40	```
41
42	If downstream models reference specific columns, you MUST ensure those columns remain available after refactoring.
43
44	### 4. Plan Refactoring Strategy
45
46	\| Opportunity \| Strategy \|
47	\|-------------\|----------\|
48	\| Long CTE \| Extract to intermediate model \|
49	\| Repeated logic \| Create macro in `macros/` \|
50	\| Complex join \| Split into intermediate models \|
51	\| Multiple concerns \| Separate into focused models \|
52
53	### 5. Execute Refactoring
54
55	#### Pattern: Extract CTE to Model
56
57	Before:
58	```sql
59	-- orders.sql (200 lines)
60	with customer_metrics as (
61	-- 50 lines of complex logic
62	),
63	order_enriched as (
64	select ...
65	from orders
66	join customer_metrics on ...
67	)
68	select * from order_enriched
69	```
70
71	After:
72	```sql
73	-- customer_metrics.sql (new file)
74	select
75	customer_id,
76	-- complex logic here
77	from {{ ref('customers') }}
78
79	-- orders.sql (simplified)
80	with order_enriched as (
81	select ...
82	from {{ ref('raw_orders') }} orders
83	join {{ ref('customer_metrics') }} cm on ...
84	)
85	select * from order_enriched
86	```
87
88	#### Pattern: Extract to Macro
89
90	Before (repeated in multiple models):
91	```sql
92	case
93	when amount < 0 then 'refund'
94	when amount = 0 then 'zero'
95	else 'positive'
96	end as amount_category
97	```
98
99	After:
100	```sql
101	-- macros/categorize_amount.sql
102	{% macro categorize_amount(column_name) %}
103	case
104	when {{ column_name }} < 0 then 'refund'
105	when {{ column_name }} = 0 then 'zero'
106	else 'positive'
107	end
108	{% endmacro %}
109
110	-- In models:
111	{{ categorize_amount('amount') }} as amount_category
112	```
113
114	### 6. Validate Changes
115
116	```bash
117	# Compile to check syntax
118	dbt compile --select +model_name+
119
120	# Build entire lineage
121	dbt build --select +model_name+
122
123	# Check row counts (manual)
124	# Before: Record expected counts
125	# After: Verify counts match
126	```
127
128	### 7. Verify Output Matches Original
129
130	CRITICAL: Refactoring should not change output.
131
132	```bash
133	# Compare row counts before and after
134	dbt show --inline "select count(*) from {{ ref('model_name') }}"
135
136	# Spot check key values
137	dbt show --select <model_name> --limit 10
138	```
139
140	### 8. Update Downstream Models
141
142	If changing output columns:
143	1. Update all downstream refs
144	2. Update schema.yml documentation
145	3. Re-run downstream tests
146
147	## Refactoring Checklist
148
149	- [ ] All downstream dependencies identified
150	- [ ] User informed of impact scope
151	- [ ] One change at a time
152	- [ ] Compile passes after each change
153	- [ ] Build passes after each change
154	- [ ] Output validated (row counts match)
155	- [ ] Documentation updated
156	- [ ] Tests still pass
157
158	## Common Refactoring Triggers
159
160	\| Symptom \| Refactoring \|
161	\|---------\|-------------\|
162	\| Model > 200 lines \| Extract CTEs to models \|
163	\| Same logic in 3+ models \| Extract to macro \|
164	\| 5+ joins in one model \| Create intermediate models \|
165	\| Hard to understand \| Add CTEs with clear names \|
166	\| Slow performance \| Split to allow parallelization \|
167
168	## Anti-Patterns
169
170	- Refactoring without checking downstream impact
171	- Making multiple changes at once
172	- Not validating output matches after refactoring
173	- Extracting prematurely (wait for 3+ uses)
174	- Breaking existing tests without updating them

People who liked this also viewed

6 picks

Type

refactoring-dbt-models

24installs7forks

View on GitHub

How to install refactoring-dbt-models?

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

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

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# dbt Refactoring
2
3	Find ALL downstream dependencies before changing. Refactor in small steps. Verify output after each change.
4
5	## Workflow
6
7	### 1. Analyze Current Model
8
9	```bash
10	cat models/<path>/<model_name>.sql
11	```
12
13	Identify refactoring opportunities:
14	- CTEs longer than 50 lines → extract to intermediate model
15	- Logic repeated across models → extract to macro
16	- Multiple joins in sequence → split into steps
17	- Complex WHERE clauses → extract to staging filter
18
19	### 2. Find All Downstream Dependencies
20
21	CRITICAL: Never refactor without knowing impact.
22
23	```bash
24	# Get full dependency tree (model and all its children)
25	dbt ls --select model_name+ --output list
26
27	# Find all models referencing this one
28	grep -r "ref('model_name')" models/ --include="*.sql"
29	```
30
31	Report to user: "Found X downstream models: [list]. These will be affected by changes."
32
33	### 3. Check What Columns Downstream Models Use
34
35	BEFORE changing any columns, check what downstream models reference:
36
37	```bash
38	# For each downstream model, check what columns it uses
39	cat models/<path>/<downstream_model>.sql \| grep -E "model_name\.\w+\|alias\.\w+"
40	```
41
42	If downstream models reference specific columns, you MUST ensure those columns remain available after refactoring.
43
44	### 4. Plan Refactoring Strategy
45
46	\| Opportunity \| Strategy \|
47	\|-------------\|----------\|
48	\| Long CTE \| Extract to intermediate model \|
49	\| Repeated logic \| Create macro in `macros/` \|
50	\| Complex join \| Split into intermediate models \|
51	\| Multiple concerns \| Separate into focused models \|
52
53	### 5. Execute Refactoring
54
55	#### Pattern: Extract CTE to Model
56
57	Before:
58	```sql
59	-- orders.sql (200 lines)
60	with customer_metrics as (
61	-- 50 lines of complex logic
62	),
63	order_enriched as (
64	select ...
65	from orders
66	join customer_metrics on ...
67	)
68	select * from order_enriched
69	```
70
71	After:
72	```sql
73	-- customer_metrics.sql (new file)
74	select
75	customer_id,
76	-- complex logic here
77	from {{ ref('customers') }}
78
79	-- orders.sql (simplified)
80	with order_enriched as (
81	select ...
82	from {{ ref('raw_orders') }} orders
83	join {{ ref('customer_metrics') }} cm on ...
84	)
85	select * from order_enriched
86	```
87
88	#### Pattern: Extract to Macro
89
90	Before (repeated in multiple models):
91	```sql
92	case
93	when amount < 0 then 'refund'
94	when amount = 0 then 'zero'
95	else 'positive'
96	end as amount_category
97	```
98
99	After:
100	```sql
101	-- macros/categorize_amount.sql
102	{% macro categorize_amount(column_name) %}
103	case
104	when {{ column_name }} < 0 then 'refund'
105	when {{ column_name }} = 0 then 'zero'
106	else 'positive'
107	end
108	{% endmacro %}
109
110	-- In models:
111	{{ categorize_amount('amount') }} as amount_category
112	```
113
114	### 6. Validate Changes
115
116	```bash
117	# Compile to check syntax
118	dbt compile --select +model_name+
119
120	# Build entire lineage
121	dbt build --select +model_name+
122
123	# Check row counts (manual)
124	# Before: Record expected counts
125	# After: Verify counts match
126	```
127
128	### 7. Verify Output Matches Original
129
130	CRITICAL: Refactoring should not change output.
131
132	```bash
133	# Compare row counts before and after
134	dbt show --inline "select count(*) from {{ ref('model_name') }}"
135
136	# Spot check key values
137	dbt show --select <model_name> --limit 10
138	```
139
140	### 8. Update Downstream Models
141
142	If changing output columns:
143	1. Update all downstream refs
144	2. Update schema.yml documentation
145	3. Re-run downstream tests
146
147	## Refactoring Checklist
148
149	- [ ] All downstream dependencies identified
150	- [ ] User informed of impact scope
151	- [ ] One change at a time
152	- [ ] Compile passes after each change
153	- [ ] Build passes after each change
154	- [ ] Output validated (row counts match)
155	- [ ] Documentation updated
156	- [ ] Tests still pass
157
158	## Common Refactoring Triggers
159
160	\| Symptom \| Refactoring \|
161	\|---------\|-------------\|
162	\| Model > 200 lines \| Extract CTEs to models \|
163	\| Same logic in 3+ models \| Extract to macro \|
164	\| 5+ joins in one model \| Create intermediate models \|
165	\| Hard to understand \| Add CTEs with clear names \|
166	\| Slow performance \| Split to allow parallelization \|
167
168	## Anti-Patterns
169
170	- Refactoring without checking downstream impact
171	- Making multiple changes at once
172	- Not validating output matches after refactoring
173	- Extracting prematurely (wait for 3+ uses)
174	- Breaking existing tests without updating them

People who liked this also viewed

6 picks

Type