migrating-sql-to-dbt

Name: migrating-sql-to-dbt
Author: AltimateAI

23installs7forks

How to install migrating-sql-to-dbt?

altimateai/data-engineering-skills/migrating-sql-to-dbt

$npx -y skills add AltimateAI/data-engineering-skills --skill migrating-sql-to-dbt

Installs into the current project.

Summary

Converts legacy SQL to modular dbt models. Use when migrating SQL to dbt for: (1) Converting stored procedures, views, or raw SQL files to dbt models (2) Task mentions "migrate", "convert", "legacy SQL", "transform to dbt", or "modernize" (3) Breaking monolithic queries into modu

Files · 1

View on GitHub

SKILL.md

1	# dbt Migration
2
3	Don't convert everything at once. Build and validate layer by layer.
4
5	## Workflow
6
7	### 1. Analyze Legacy SQL
8
9	```bash
10	cat <legacy_sql_file>
11	```
12
13	Identify all tables referenced in the query.
14
15	### 2. Check What Already Exists
16
17	```bash
18	# Search for existing models/sources that reference the table
19	grep -r "<table_name>" models/ --include=".sql" --include=".yml"
20	find models/ -name "*.sql" \| xargs grep -l "<table_name>"
21	```
22
23	For each table referenced in the legacy SQL:
24	1. Check if an existing model already references this table
25	2. Check if a source definition exists
26	3. If neither exists, ask user: "Table X not found - should I create it as a source?"
27
28	Only proceed to intermediate/mart layers after all dependencies exist.
29
30	### 3. Create Missing Sources
31
32	```yaml
33	# models/staging/sources.yml
34	version: 2
35
36	sources:
37	- name: raw_database
38	schema: raw_schema
39	tables:
40	- name: orders
41	description: Raw orders from source system
42	- name: customers
43	description: Raw customer records
44	```
45
46	### 4. Build Staging Layer
47
48	One staging model per source table. Follow existing project naming conventions.
49
50	Build before proceeding:
51	```bash
52	dbt build --select <staging_model>
53	```
54
55	### 5. Build Intermediate Layer (if needed)
56
57	Extract complex joins/logic into intermediate models.
58
59	Build incrementally:
60	```bash
61	dbt build --select <intermediate_model>
62	```
63
64	### 6. Build Mart Layer
65
66	Final business-facing model with aggregations.
67
68	### 7. Validate Migration
69
70	```bash
71	# Build entire lineage
72	dbt build --select +<final_model>
73	dbt show --select <final_model>
74	```
75
76	## Migration Checklist
77
78	- [ ] All source tables identified and documented
79	- [ ] Sources.yml created with descriptions
80	- [ ] Staging models: 1:1 with sources, renamed columns
81	- [ ] Intermediate models: business logic extracted
82	- [ ] Mart models: final aggregations
83	- [ ] Each layer compiles successfully
84	- [ ] Each layer builds successfully
85	- [ ] Row counts match original (manual validation)
86	- [ ] Tests added for key constraints
87
88	## Common Migration Patterns
89
90	- Nested subqueries → Separate models (staging → intermediate → mart)
91	- Temp tables → Ephemeral materialization `{{ config(materialized='ephemeral') }}`
92	- Hardcoded values → Variables `{{ var("name") }}`
93
94	## Anti-Patterns
95
96	- Converting entire legacy query to single dbt model
97	- Skipping the staging layer
98	- Not validating each layer before proceeding
99	- Keeping hardcoded values instead of using variables
100	- Not documenting business logic during migration

People who liked this also viewed

6 picks

Type

migrating-sql-to-dbt

23installs7forks

View on GitHub

How to install migrating-sql-to-dbt?

altimateai/data-engineering-skills/migrating-sql-to-dbt

$npx -y skills add AltimateAI/data-engineering-skills --skill migrating-sql-to-dbt

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# dbt Migration
2
3	Don't convert everything at once. Build and validate layer by layer.
4
5	## Workflow
6
7	### 1. Analyze Legacy SQL
8
9	```bash
10	cat <legacy_sql_file>
11	```
12
13	Identify all tables referenced in the query.
14
15	### 2. Check What Already Exists
16
17	```bash
18	# Search for existing models/sources that reference the table
19	grep -r "<table_name>" models/ --include=".sql" --include=".yml"
20	find models/ -name "*.sql" \| xargs grep -l "<table_name>"
21	```
22
23	For each table referenced in the legacy SQL:
24	1. Check if an existing model already references this table
25	2. Check if a source definition exists
26	3. If neither exists, ask user: "Table X not found - should I create it as a source?"
27
28	Only proceed to intermediate/mart layers after all dependencies exist.
29
30	### 3. Create Missing Sources
31
32	```yaml
33	# models/staging/sources.yml
34	version: 2
35
36	sources:
37	- name: raw_database
38	schema: raw_schema
39	tables:
40	- name: orders
41	description: Raw orders from source system
42	- name: customers
43	description: Raw customer records
44	```
45
46	### 4. Build Staging Layer
47
48	One staging model per source table. Follow existing project naming conventions.
49
50	Build before proceeding:
51	```bash
52	dbt build --select <staging_model>
53	```
54
55	### 5. Build Intermediate Layer (if needed)
56
57	Extract complex joins/logic into intermediate models.
58
59	Build incrementally:
60	```bash
61	dbt build --select <intermediate_model>
62	```
63
64	### 6. Build Mart Layer
65
66	Final business-facing model with aggregations.
67
68	### 7. Validate Migration
69
70	```bash
71	# Build entire lineage
72	dbt build --select +<final_model>
73	dbt show --select <final_model>
74	```
75
76	## Migration Checklist
77
78	- [ ] All source tables identified and documented
79	- [ ] Sources.yml created with descriptions
80	- [ ] Staging models: 1:1 with sources, renamed columns
81	- [ ] Intermediate models: business logic extracted
82	- [ ] Mart models: final aggregations
83	- [ ] Each layer compiles successfully
84	- [ ] Each layer builds successfully
85	- [ ] Row counts match original (manual validation)
86	- [ ] Tests added for key constraints
87
88	## Common Migration Patterns
89
90	- Nested subqueries → Separate models (staging → intermediate → mart)
91	- Temp tables → Ephemeral materialization `{{ config(materialized='ephemeral') }}`
92	- Hardcoded values → Variables `{{ var("name") }}`
93
94	## Anti-Patterns
95
96	- Converting entire legacy query to single dbt model
97	- Skipping the staging layer
98	- Not validating each layer before proceeding
99	- Keeping hardcoded values instead of using variables
100	- Not documenting business logic during migration

People who liked this also viewed

6 picks

Type