Incremental Models: Beginner

materialized='incremental' is the third value the materialized config accepts, beside view and table. On its first build the model creates its table from the SELECT, as a table model does. On every later build it runs the SELECT and inserts the rows that come back into the table that already exists. Nothing is dropped, and a row the table already holds is never read again or rewritten.

The lesson follows 1 source table, app.stream_events, with 1 row per play and 4 columns: event_id, handed out in order as the app records plays, track_id, played_at, and ms_played, the milliseconds the listener heard. On the first night it holds 3 rows.

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.

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:

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:

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.

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.

is_incremental() decides whether a filter exists; the filter decides what counts as new. dbt keeps no record of which source rows it has loaded. On every run the model's own SQL has to look at the table it holds, find the newest row there, and ask the source for rows newer than it.

The subquery inside the if block returns 1 value, the latest played_at the table holds, and the outer WHERE keeps the source rows past it. That value is a watermark: the line the last run reached. For the filter to be right the column has to grow with every new row, so that a row arriving later always carries a larger value than every row already loaded.

Columns of 3 kinds can do that, and they're not equally safe. An id handed out in order as rows are written, such as event_id, grows with arrival by construction. So does a load timestamp, a time the source stamps on the row when it writes it. played_at is an event time: when the listener pressed play. It grows with arrival only while plays reach the source in the order they happened, and the end of this section shows a play that does not. A column like updated_at, which a later edit pushes upward, sends old rows back through the filter, and append writes them a second time.

Swapping the column changes 1 line: WHERE event_id > (SELECT MAX(event_id) FROM {{ this }}) inside the same if block. An id works when the source hands ids out in arrival order, and it has 1 advantage over a timestamp: an integer comparison has no time zone and no fractional second to get wrong. A timestamp is the choice when no such id exists, or when the source is a union of feeds whose ids overlap.

The first time dbt runs an incremental model, no table of its name exists in the target schema. is_incremental() finds the table missing, returns false, and Jinja drops the if block before rendering finishes. The SQL sent is the plain SELECT with no WHERE, and dbt wraps it in a CREATE TABLE AS:

Suppose the block rendered anyway. The statement would contain SELECT MAX(played_at) FROM analytics.plays, a query over a table that does not exist. Rendering would finish without complaint, because to Jinja the name is a string, and the warehouse would answer with a database error: table not found. dbt returns false for a missing table so that the SQL it sends never names what is not there, and the model file doesn't have to know which run it is on.

dbt tells a first run from a later run by looking for the table, never by remembering. Nothing in the project records that a model has run before.

dbt run --full-refresh tells dbt to treat every incremental model in the run as though its table did not exist. is_incremental() returns false, the if block is dropped, and dbt drops the existing table and creates it again from the full SELECT, reading every source row. dbt build --full-refresh does the same and runs the tests after.