03. Avoiding Recomputation
Avoiding Recomputation in dbt
Avoiding recomputation ensures that dbt does not unnecessarily reprocess unchanged data, improving performance and reducing warehouse costs.
This is especially important for large datasets and incremental workflows.
Key Techniques
Incremental models – only process new or changed data
Ephemeral models – inlined subqueries, no table creation
Using snapshots – track changes without recomputing full datasets
Materialization strategies – incremental, table, view
Caching and result reuse – leverage warehouse caching when possible
1. Incremental Models
- Use
is_incremental()to filter only new or updated rows
{{ config(
materialized='incremental',
unique_key='order_id'
) }}
select *
from {{ source('app', 'orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}
- Avoids scanning the entire table for every run
1.1 Understanding is_incremental()
is_incremental() is activated (returns True) only when all of these conditions are met:
- Model is configured as incremental
{{ config(materialized='incremental') }}
- The target table already exists in the database
- You are running without
--full-refresh
Example 1: First Run
Suppose the table doesn't exist yet.
dbt run --select customer_orders
dbt creates the table for the first time.
Result:
is_incremental() = False
So this block is skipped:
{% if is_incremental() %}
AND o.ORDER_DATE > (
SELECT MAX(ORDER_DATE)
FROM {{ this }}
)
{% endif %}
Example 2: Second Run
The table already exists.
dbt run --select customer_orders
Result:
is_incremental() = True
Now the incremental filter is added to the SQL.
Example 3: Full Refresh
Even if the table exists:
dbt run --full-refresh
Result:
is_incremental() = False
Because dbt is rebuilding everything from scratch.
Example 4: Materialized as Table
{{ config(materialized='table') }}
Run:
dbt run
Result:
is_incremental() = False
because the model is not incremental.
Easy way to remember
| Scenario | is_incremental() |
|---|---|
| First run (table doesn't exist) | False |
| Incremental model + table exists | True |
dbt run --full-refresh | False |
Materialized as table | False |
Materialized as view | False |
So in your model:
{% if is_incremental() %}
AND o.ORDER_DATE > (
SELECT MAX(ORDER_DATE)
FROM {{ this }}
)
{% endif %}
- First run → loads all completed orders.
- Later runs → loads only new orders.
- Full refresh → loads all orders again.
2. Ephemeral Models
- Ephemeral models are inlined in SQL queries and not materialized
{{ config(materialized='ephemeral') }}
select *
from {{ source('app', 'customers') }}
where status = 'active'
- Helps avoid creating unnecessary intermediate tables
3. Snapshots
- Snapshots allow you to track historical changes without recomputing the full dataset
{% snapshot customers_snapshot %}
{{
config(
target_schema='snapshots',
unique_key='customer_id',
strategy='check',
check_cols=['email', 'first_name', 'last_name']
)
}}
select * from {{ source('app', 'customers') }}
{% endsnapshot %}
- Only changes trigger new snapshot records
4. Materialization Strategy
- Use incremental for large tables
- Use views or ephemeral for lightweight transformations
- Avoid full-refresh unless necessary
Best Practices
Implement incremental models for large tables
Use ephemeral models for intermediate logic
Schedule snapshots for historical tracking
Leverage warehouse caching and clustering
Avoid unnecessary full-refreshes to save compute and time