Filtering Only New Rows

Concepts covered: Incremental models

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.

A watermark column

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.

The second night, row by row

After the first night plays holds events 1 to 3, and MAX(played_at) over it is 2026-03-01 21:05:00. The source now holds 5 rows. The filter compares each of them against that value:

Row 3 sits on the watermark itself. With > it stays out, which is right, because the table already holds it. The 2 rows past the line are inserted, and the table ends the night with 5 rows and a watermark of 2026-03-02 12:15:00, which the third night's subquery will return.

Why > and not >=

Change the comparison to >= and row 3 passes on night 2, since its played_at equals the watermark. Append inserts it, and plays holds event 3 twice. On night 3 the newest row of night 2 passes the same way, so every run adds 1 duplicate, and a unique test on event_id fails from the second run on. Equality at the boundary belongs to the row that is already loaded.

Filtering on an id instead

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.

What the filter cannot see

The watermark assumes rows arrive in order. A play recorded at 20:00 on March 1 that reaches the source on March 2, after the run that moved the watermark to 21:05, never passes the filter: its played_at sits below the line, and no later run looks there. This lesson's model accepts that gap and counts on the source to deliver in order; widening the filter to catch rows that arrive late comes at the next tier.

Suppose a play from 2026-03-01 20:00:00, made on a phone that was offline, lands in the source after night 2's run, and the source hands it the next id, 6. Night 3's run judges it under each filter:

The id filter catches the late play because the source numbered it on arrival, while the event-time filter loses it for good. That holds only when the source hands out ids, or load timestamps, as rows land, so check how a source assigns them before trusting one as a watermark.

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.