is_incremental() and this

Concepts covered: Incremental models

An incremental model needs 1 SELECT that behaves 2 ways: read everything when there is no table, read only the new rows when there is. Jinja renders before any SQL reaches the warehouse, so the model wraps its filter in an if block and lets dbt decide, at render time, whether the filter is there at all.

{{ config(materialized='incremental') }}

SELECT
    event_id,
    track_id,
    played_at,
    ms_played
FROM {{ source('app', 'stream_events') }}
{% if is_incremental() %}
WHERE played_at > (SELECT MAX(played_at) FROM {{ this }})
{% endif %}

The file has 3 new parts: the config value incremental on the first line, the macro is_incremental() inside the if tag, and this inside the subquery. The rest is a SELECT over a source with each column named, the same shape as every model so far.

What is_incremental() checks

is_incremental() is a macro that ships with dbt, and it returns true only when all 3 of these hold at the moment the model renders:

• The model is configured with materialized='incremental'
• A table with the model's name already exists in the target schema
• The command was run without the --full-refresh flag

When any 1 of them fails it returns false, and Jinja drops everything between the if and the endif, so the SQL dbt sends has no WHERE in it at all. To settle the second condition the macro asks the warehouse whether the table exists, which is why the result changes from run to run with no edit to the file.

Here are the 3 conditions checked in 4 situations the plays model can meet:

1 file, 2 renderings

Compiled SQL is what dbt writes under target/compiled/ and sends to the warehouse. With the plays table present and no flag, the block stays, source() and this resolve to names, and the warehouse receives this statement:

SELECT
    event_id,
    track_id,
    played_at,
    ms_played
FROM app.stream_events
WHERE played_at > (
    SELECT MAX(played_at)
    FROM analytics.plays
)

With the table absent, or the flag set, the same file renders to the SELECT alone:

SELECT
    event_id,
    track_id,
    played_at,
    ms_played
FROM app.stream_events

this names the model's own table

this is a variable dbt sets while rendering each model, holding the relation the model writes to: schema and name. For plays built into the analytics schema it renders to analytics.plays. It renders to that name whether the table exists, because this is a name and nothing more; whether the name points at a table is the warehouse's business when the statement runs.

ref() can't do this job. ref('plays') inside plays would tell dbt that plays depends on plays, and dbt refuses the edge, since no node can be built before itself. this adds no edge: it names the table, dbt consults nothing in the DAG, and the subquery reads whatever sits under that name when the statement executes.

{{ this }}

• Renders to the current model's own relation
• Adds no node or edge to the DAG
• Used inside the model to read what it already holds
• Renders to the same name on every run

{{ ref('plays') }}

• Renders to another model's relation
• Adds an edge: that model builds first
• Used in a different model that reads plays
• Refused inside plays itself as a cycle

Same file, 2 materializations

The animation builds plays from the same 3 rows, then adds 2 rows and builds again. Here the materialization comes from dbt_project.yml, so the model file is identical in both versions and only +materialized changes. Switch between them: the lineage, the rows and the columns match after both runs. Under table the if block renders away on both runs, because is_incremental() returns false for any model not configured incremental, and run 2 rewrites all 5 rows. Under incremental the block renders away on run 1 and stays on run 2, which inserts 2.

SELECT
    event_id,
    track_id,
    played_at,
    ms_played
FROM {{ source('app', 'stream_events') }}
{% if is_incremental() %}
WHERE played_at > (SELECT MAX(played_at) FROM {{ this }})
{% endif %}

About this lesson

This section is part of the Incremental Models: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.