Skip to main content

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​

Scenariois_incremental()
First run (table doesn't exist)False
Incremental model + table existsTrue
dbt run --full-refreshFalse
Materialized as tableFalse
Materialized as viewFalse

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