developing-incremental-models

Name: developing-incremental-models
Author: AltimateAI

22installs7forks

How to install developing-incremental-models?

altimateai/data-engineering-skills/developing-incremental-models

$npx -y skills add AltimateAI/data-engineering-skills --skill developing-incremental-models

Installs into the current project.

Summary

Develops and troubleshoots dbt incremental models. Use when working with incremental materialization for: (1) Creating new incremental models (choosing strategy, unique_key, partition) (2) Task mentions "incremental", "append", "merge", "upsert", or "late arriving data" (3) Troub

Files · 1

View on GitHub

SKILL.md

1	# dbt Incremental Model Development
2
3	Choose the right strategy. Design the unique_key carefully. Handle edge cases.
4
5	## When to Use Incremental
6
7	\| Scenario \| Recommendation \|
8	\|----------\|----------------\|
9	\| Source data < 10M rows \| Use `table` (simpler, full refresh is fast) \|
10	\| Source data > 10M rows \| Consider `incremental` \|
11	\| Source data updated in place \| Use `incremental` with `merge` strategy \|
12	\| Append-only source (logs, events) \| Use `incremental` with `append` strategy \|
13	\| Partitioned warehouse data \| Use `insert_overwrite` if supported \|
14
15	Default to `table` unless you have a clear performance reason for incremental.
16
17	## Critical Rules
18
19	1. ALWAYS test with `--full-refresh` first before relying on incremental logic
20	2. ALWAYS verify unique_key is truly unique in both source and target
21	3. If merge fails 3+ times, check unique_key for duplicates
22	4. Run full refresh periodically to prevent data drift
23
24	## Workflow
25
26	### 1. Confirm Incremental is Needed
27
28	```bash
29	# Check source table size
30	dbt show --inline "select count(*) from {{ source('schema', 'table') }}"
31	```
32
33	If count < 10 million, consider using `table` instead. Incremental adds complexity.
34
35	### 2. Understand the Source Data Pattern
36
37	Before choosing a strategy, answer:
38	- Is data append-only? (new rows added, never updated)
39	- Are existing rows updated? (need merge/upsert)
40	- Is there a reliable timestamp? (for filtering new data)
41	- What's the unique identifier? (for merge matching)
42
43	```bash
44	# Check for timestamp column
45	dbt show --inline "
46	select
47	min(updated_at) as earliest,
48	max(updated_at) as latest,
49	count(distinct date(updated_at)) as days_of_data
50	from {{ source('schema', 'table') }}
51	"
52	```
53
54	### 3. Choose the Right Strategy
55
56	\| Strategy \| Use When \| How It Works \|
57	\|----------\|----------\|--------------\|
58	\| `append` \| Data is append-only, no updates \| INSERT only, no deduplication \|
59	\| `merge` \| Data can be updated \| MERGE/UPSERT by unique_key \|
60	\| `delete+insert` \| Data updated in batches \| DELETE matching rows, then INSERT \|
61	\| `insert_overwrite` \| Partitioned tables (BigQuery, Spark) \| Replace entire partitions \|
62
63	Default: `merge` is safest for most use cases.
64
65	Note: Strategy availability varies by adapter. Check the [dbt incremental strategy docs](https://docs.getdbt.com/docs/build/incremental-strategy) for your specific warehouse.
66
67	### 4. Design the Unique Key
68
69	CRITICAL: unique_key must be truly unique in your data.
70
71	```bash
72	# Verify uniqueness BEFORE creating model
73	dbt show --inline "
74	select {{ unique_key_column }}, count(*)
75	from {{ source('schema', 'table') }}
76	group by 1
77	having count(*) > 1
78	limit 10
79	"
80	```
81
82	If duplicates exist:
83	- Add more columns to make composite key
84	- Add deduplication logic in model
85	- Use `delete+insert` instead of `merge`
86
87	### 5. Write the Incremental Model
88
89	```sql
90	{{
91	config(
92	materialized='incremental',
93	incremental_strategy='merge', -- or append, delete+insert
94	unique_key='id', -- MUST be unique
95	on_schema_change='append_new_columns' -- handle new columns
96	)
97	}}
98
99	select
100	id,
101	column_a,
102	column_b,
103	updated_at
104	from {{ source('schema', 'table') }}
105
106	{% if is_incremental() %}
107	where updated_at > (select max(updated_at) from {{ this }})
108	{% endif %}
109	```
110
111	### 6. Build with Full Refresh First
112
113	ALWAYS verify with full refresh before trusting incremental logic.
114
115	```bash
116	# First run: full refresh to establish baseline
117	dbt build --select <model_name> --full-refresh
118
119	# Verify output
120	dbt show --select <model_name> --limit 10
121	dbt show --inline "select count(*) from {{ ref('model_name') }}"
122	```
123
124	### 7. Test Incremental Logic
125
126	```bash
127	# Run incrementally (no --full-refresh)
128	dbt build --select <model_name>
129
130	# Verify row count changed appropriately
131	dbt show --inline "select count(*) from {{ ref('model_name') }}"
132	```
133
134	### 8. Handle Schema Changes
135
136	Set `on_schema_change` based on your needs:
137
138	\| Setting \| Behavior \|
139	\|---------\|----------\|
140	\| `ignore` (default) \| New columns in source are ignored \|
141	\| `append_new_columns` \| New columns added to target \|
142	\| `sync_all_columns` \| Target schema matches source exactly \|
143	\| `fail` \| Error if schema changes \|
144
145	## Common Incremental Problems
146
147	### Problem: Merge Fails with Duplicate Key
148
149	Symptom: "Cannot MERGE with duplicate values"
150
151	Cause: Multiple rows with same unique_key in source or target.
152
153	*Fix:

People who liked this also viewed

6 picks

Type

developing-incremental-models

22installs7forks

View on GitHub

How to install developing-incremental-models?

altimateai/data-engineering-skills/developing-incremental-models

$npx -y skills add AltimateAI/data-engineering-skills --skill developing-incremental-models

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# dbt Incremental Model Development
2
3	Choose the right strategy. Design the unique_key carefully. Handle edge cases.
4
5	## When to Use Incremental
6
7	\| Scenario \| Recommendation \|
8	\|----------\|----------------\|
9	\| Source data < 10M rows \| Use `table` (simpler, full refresh is fast) \|
10	\| Source data > 10M rows \| Consider `incremental` \|
11	\| Source data updated in place \| Use `incremental` with `merge` strategy \|
12	\| Append-only source (logs, events) \| Use `incremental` with `append` strategy \|
13	\| Partitioned warehouse data \| Use `insert_overwrite` if supported \|
14
15	Default to `table` unless you have a clear performance reason for incremental.
16
17	## Critical Rules
18
19	1. ALWAYS test with `--full-refresh` first before relying on incremental logic
20	2. ALWAYS verify unique_key is truly unique in both source and target
21	3. If merge fails 3+ times, check unique_key for duplicates
22	4. Run full refresh periodically to prevent data drift
23
24	## Workflow
25
26	### 1. Confirm Incremental is Needed
27
28	```bash
29	# Check source table size
30	dbt show --inline "select count(*) from {{ source('schema', 'table') }}"
31	```
32
33	If count < 10 million, consider using `table` instead. Incremental adds complexity.
34
35	### 2. Understand the Source Data Pattern
36
37	Before choosing a strategy, answer:
38	- Is data append-only? (new rows added, never updated)
39	- Are existing rows updated? (need merge/upsert)
40	- Is there a reliable timestamp? (for filtering new data)
41	- What's the unique identifier? (for merge matching)
42
43	```bash
44	# Check for timestamp column
45	dbt show --inline "
46	select
47	min(updated_at) as earliest,
48	max(updated_at) as latest,
49	count(distinct date(updated_at)) as days_of_data
50	from {{ source('schema', 'table') }}
51	"
52	```
53
54	### 3. Choose the Right Strategy
55
56	\| Strategy \| Use When \| How It Works \|
57	\|----------\|----------\|--------------\|
58	\| `append` \| Data is append-only, no updates \| INSERT only, no deduplication \|
59	\| `merge` \| Data can be updated \| MERGE/UPSERT by unique_key \|
60	\| `delete+insert` \| Data updated in batches \| DELETE matching rows, then INSERT \|
61	\| `insert_overwrite` \| Partitioned tables (BigQuery, Spark) \| Replace entire partitions \|
62
63	Default: `merge` is safest for most use cases.
64
65	Note: Strategy availability varies by adapter. Check the [dbt incremental strategy docs](https://docs.getdbt.com/docs/build/incremental-strategy) for your specific warehouse.
66
67	### 4. Design the Unique Key
68
69	CRITICAL: unique_key must be truly unique in your data.
70
71	```bash
72	# Verify uniqueness BEFORE creating model
73	dbt show --inline "
74	select {{ unique_key_column }}, count(*)
75	from {{ source('schema', 'table') }}
76	group by 1
77	having count(*) > 1
78	limit 10
79	"
80	```
81
82	If duplicates exist:
83	- Add more columns to make composite key
84	- Add deduplication logic in model
85	- Use `delete+insert` instead of `merge`
86
87	### 5. Write the Incremental Model
88
89	```sql
90	{{
91	config(
92	materialized='incremental',
93	incremental_strategy='merge', -- or append, delete+insert
94	unique_key='id', -- MUST be unique
95	on_schema_change='append_new_columns' -- handle new columns
96	)
97	}}
98
99	select
100	id,
101	column_a,
102	column_b,
103	updated_at
104	from {{ source('schema', 'table') }}
105
106	{% if is_incremental() %}
107	where updated_at > (select max(updated_at) from {{ this }})
108	{% endif %}
109	```
110
111	### 6. Build with Full Refresh First
112
113	ALWAYS verify with full refresh before trusting incremental logic.
114
115	```bash
116	# First run: full refresh to establish baseline
117	dbt build --select <model_name> --full-refresh
118
119	# Verify output
120	dbt show --select <model_name> --limit 10
121	dbt show --inline "select count(*) from {{ ref('model_name') }}"
122	```
123
124	### 7. Test Incremental Logic
125
126	```bash
127	# Run incrementally (no --full-refresh)
128	dbt build --select <model_name>
129
130	# Verify row count changed appropriately
131	dbt show --inline "select count(*) from {{ ref('model_name') }}"
132	```
133
134	### 8. Handle Schema Changes
135
136	Set `on_schema_change` based on your needs:
137
138	\| Setting \| Behavior \|
139	\|---------\|----------\|
140	\| `ignore` (default) \| New columns in source are ignored \|
141	\| `append_new_columns` \| New columns added to target \|
142	\| `sync_all_columns` \| Target schema matches source exactly \|
143	\| `fail` \| Error if schema changes \|
144
145	## Common Incremental Problems
146
147	### Problem: Merge Fails with Duplicate Key
148
149	Symptom: "Cannot MERGE with duplicate values"
150
151	Cause: Multiple rows with same unique_key in source or target.
152
153	*Fix:

People who liked this also viewed

6 picks

Type