Rebuild or Increment

Concepts covered: Incremental models

A table model sends the same statement on every build: drop the table that exists, create a new one from the SELECT. The SELECT reads every row of its source each time, whether that row arrived an hour ago or was already read on the build before. For a source that gains rows every day, the rebuild on day 400 reads 400 days of rows to add 1.

The arithmetic of a nightly rebuild

Take a source of stream events, 1 row per play, holding 90,000,000 rows and gaining 300,000 a night. A table model reads all of them to add the 300,000 that arrived since the last run. The table below follows 4 nights of that source under both materializations, counting the rows each run reads.

Night 1 reads every row under both, because no earlier table exists to add to. From night 2 the incremental run reads the rows that arrived since the night before and nothing else, while the rebuild reads the whole source again, and the gap widens every night the source grows.

• 301x
• 99.7%

What incremental changes

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 model saves work because it trusts the rows it already holds, which suits sources whose rows stay as written: plays, page views, sensor readings, log lines, ledger entries. A row that changes after it lands keeps its old values in the incremental table until someone rebuilds the table from the start.

The running example

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.

By the second night 2 more plays have arrived, events 4 and 5. A table model named plays reads 5 rows and writes 5. An incremental model named plays reads 2 and inserts them beside the 3 it wrote the night before. Both tables hold the same 5 rows afterward; the difference is the statement sent and the rows read to produce it.

• ref('plays') works the same in every model that reads it, whichever materialization plays uses
• The tests declared on plays run after it, over every row the table holds, old and new
• The table has the same columns, in the same types, as the table version would
• A failing test on plays still holds back its children in dbt build

Choosing

Increment it

• Rows are events that never change after they are written
• A column grows with every new row: a created timestamp or a counting id
• The source is large enough that reading all of it costs real time or money
• The SELECT keeps 1 row per source row, or groups rows that arrive together

Rebuild it

• Rows are edited after they land: an order's status, a user's plan
• The SELECT computes totals over all history: lifetime plays per track
• The source is small: a lookup of 200 categories rebuilds in a second
• No column tells old rows from new ones

The SELECT matters as much as the source. A model that counts lifetime plays per track has 1 row per track, and a new play changes a row that already exists, so appending can't express it. A model with 1 row per source row, or 1 row per batch that arrives whole, is the shape that append fits.

Put the 3 questions to 2 models over the same source: plays, and a model of lifetime plays per track.

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.