Why incremental models save time and cost in dbt - Visual Breakdown

Concept Flow - Why incremental models save time and cost

Start Full Model Build

↓

Process All Data Rows

↓

Store Full Result

↓

Next Run: Incremental Model

↓

Identify New/Changed Rows

↓

Process Only New/Changed Rows

↓

Append/Update Incremental Result

↓

Save Time and Cost

↓

End

Incremental models first build full data, then on next runs process only new or changed data, saving time and cost.

Execution Sample

dbt

select * from source_table
where updated_at > (select max(updated_at) from target_table)

This query selects only new or updated rows since last run to process incrementally.

Execution Table

Step	Action	Data Processed	Time Taken	Cost Impact
1	Full model build	All rows (100,000)	10 minutes	High cost
2	Store full result	100,000 rows saved	Instant	No extra cost
3	Next run starts	Trigger incremental	Instant	No cost yet
4	Identify new rows	5,000 new rows	1 minute	Low cost
5	Process new rows only	5,000 rows processed	1 minute	Low cost
6	Append new rows	Result updated	Instant	No extra cost
7	End incremental run	Total rows 105,000	2 minutes total	Low cost

💡 Incremental run stops after processing only new rows, saving time and cost compared to full build.

Variable Tracker

Variable	Start (Full Build)	After Incremental Run
Rows Processed	100,000	5,000
Time Taken (minutes)	10	2
Cost	High	Low

Key Moments - 3 Insights

Why does the incremental model process fewer rows on subsequent runs?

Because it only processes rows that are new or have changed since the last run, as shown in execution_table steps 4 and 5.

How does processing fewer rows save cost?

Less data processed means less compute time and resources used, lowering cost as seen in the reduced time and cost columns in the execution_table.

What happens if the incremental model runs a full build instead?

It would process all rows again, taking more time and cost, like step 1 in the execution_table.

Visual Quiz - 3 Questions

Look at the execution table, how many rows are processed during the incremental run?

A 105,000

B 100,000

C 5,000

D 0

💡 Hint

Check the 'Data Processed' column at step 5 in the execution_table.

At which step does the incremental model identify new or changed rows?

A Step 2

B Step 4

C Step 6

D Step 1

💡 Hint

Look at the 'Action' column in the execution_table for identifying new rows.

If the incremental model processed all rows every time, how would the 'Time Taken' change?

A It would increase to 10 minutes

B It would stay the same as 2 minutes

C It would decrease to 1 minute

D It would be zero

💡 Hint

Compare 'Time Taken' at step 1 and step 7 in the execution_table.

Concept Snapshot

Incremental models process only new or changed data after the first full build.
This reduces the amount of data processed each run.
Less data means less compute time and lower cost.
Use a filter on updated timestamps to select new rows.
Incremental saves resources and speeds up data workflows.

Full Transcript

Incremental models in dbt start by building the full dataset once. On later runs, they only process new or updated rows since the last run. This is done by filtering data using a timestamp or similar marker. Processing fewer rows saves time and reduces compute costs. The execution table shows a full build processing 100,000 rows taking 10 minutes, while the incremental run processes only 5,000 rows in 2 minutes. This approach is efficient and cost-effective for large datasets.

Practice

1. What is the main reason incremental models save time and cost in dbt?

easy

A. They skip data validation steps to speed up execution.

B. They run all transformations in parallel automatically.

C. They use less memory by compressing data during processing.

D. They process only new or changed data instead of the entire dataset.

Solution

1. Step 1: Understand incremental model behavior

   Incremental models update only the new or changed data instead of reprocessing everything.

2. Step 2: Connect behavior to time and cost savings

   Processing less data means less compute time and lower cloud costs.

3. Final Answer:

   They process only new or changed data instead of the entire dataset. -> Option D

4. Quick Check:

   Incremental = process new data only [OK]

Hint: Incremental means update only new or changed data [OK]

Common Mistakes:

• Thinking incremental models reprocess all data
• Confusing incremental with parallel processing
• Assuming incremental skips validation

2. Which dbt function is used inside an incremental model to filter only new or changed data?

easy

A. is_incremental()

B. filter_new()

C. increment_only()

D. new_data_check()

Solution

1. Step 1: Recall dbt incremental syntax

   dbt provides the function is_incremental() to detect incremental runs.

2. Step 2: Identify correct function for filtering

   This function is used in the WHERE clause to filter new or changed data during incremental runs.

3. Final Answer:

   is_incremental() -> Option A

4. Quick Check:

   Use is_incremental() to filter incremental data [OK]

Hint: Use is_incremental() to filter new data in incremental models [OK]

Common Mistakes:

• Using non-existent functions like filter_new()
• Confusing function names with similar words
• Not using any function to filter incremental data

3. Given this dbt incremental model snippet, what rows will be processed during an incremental run?

select * from source_table
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

medium

A. All rows from source_table every time

B. Only rows with updated_at newer than the max in the target table

C. Only rows with updated_at older than the max in the target table

D. No rows are processed during incremental runs

Solution

1. Step 1: Analyze the WHERE clause inside is_incremental()

   The condition filters rows where updated_at is greater than the max updated_at in the target table.

2. Step 2: Understand incremental run behavior

   During incremental runs, only new or updated rows with newer timestamps are processed.

3. Final Answer:

   Only rows with updated_at newer than the max in the target table -> Option B

4. Quick Check:

   Incremental filters rows newer than max timestamp [OK]

Hint: Check updated_at > max(updated_at) for incremental rows [OK]

Common Mistakes:

• Thinking all rows are processed every time
• Confusing greater than with less than in filter
• Assuming no rows processed during incremental

4. This incremental model code has an error. What is the problem?

select * from source_table
{% if is_incremental() %}
where updated_at < (select max(updated_at) from {{ this }})
{% endif %}

medium

A. The filter uses < instead of >, so it excludes new data.

B. The is_incremental() function is misspelled.

C. The model is missing a primary key declaration.

D. The select statement is missing a GROUP BY clause.

Solution

1. Step 1: Check the filter condition logic

   The condition uses < (less than) which filters older rows, not new or changed ones.

2. Step 2: Understand incremental filtering needs

   Incremental models must filter rows newer than the max timestamp to add new data.

3. Final Answer:

   The filter uses < instead of >, so it excludes new data. -> Option A

4. Quick Check:

   Incremental filter must use > for new data [OK]

Hint: Use > in incremental filter to include new data [OK]

Common Mistakes:

• Using less than instead of greater than in filter
• Ignoring is_incremental() spelling
• Thinking primary key or GROUP BY is required here

5. You have a large sales table updated daily. You want to build a dbt incremental model that only adds new sales since the last run. Which approach best saves time and cost?

hard

A. Use an incremental model but do not filter any rows inside is_incremental() block.

B. Use a full-refresh model that rebuilds the entire sales table every run.

C. Use an incremental model with is_incremental() filtering rows where sale_date > max(sale_date) in the target table.

D. Use a model that only selects sales from the current day without checking previous data.

Solution

1. Step 1: Identify incremental model best practice

   Filtering new rows using is_incremental() with a condition on sale_date ensures only new data is processed.

2. Step 2: Compare other options

   Full-refresh rebuilds all data (slow, costly). No filtering processes all data each run. Selecting only current day misses data if runs are delayed.

3. Final Answer:

   Use an incremental model with is_incremental() filtering rows where sale_date > max(sale_date) in the target table. -> Option C

4. Quick Check:

   Filter new data with is_incremental() for efficiency [OK]

Hint: Filter new rows with is_incremental() and max date check [OK]

Common Mistakes:

• Choosing full-refresh for large tables
• Not filtering rows in incremental runs
• Selecting only current day without history check