The First Run Builds It All

Concepts covered: Incremental models

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:

CREATE TABLE analytics.plays AS
SELECT
    event_id,
    track_id,
    played_at,
    ms_played
FROM app.stream_events

This is the statement a table model with the same SELECT sends on every run. Whatever the if block holds, however narrow its filter, plays no part in the first build, because the block never reaches the warehouse. The first run reads every source row, and the table starts complete.

Why the block has to go

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.

An empty table shows the other half of the reason. If a script had created plays with its 4 columns and no rows before dbt ever ran, the table would exist, the block would render, and MAX over 0 rows would return NULL. Every comparison with NULL is unknown, no source row passes, and each run inserts 0 rows for as long as the table stays empty. Reading everything the first time is what gives the second run a watermark to read.

Each run repeats that table: the filter renders, every source row is compared against NULL, nothing is inserted, and the watermark never moves off NULL.

First run

• No table under the model's name
• is_incremental() returns false
• CREATE TABLE AS over the whole source
• Columns and types fixed from the SELECT

Every later run

• The table from the run before
• is_incremental() returns true
• INSERT INTO of the rows past the watermark
• Rows must fit the columns the table already has

What the first run costs

The first build reads every source row, so it costs what 1 table rebuild costs, in time and in rows scanned. The saving starts on the second run. A team converting the 90,000,000-row table model to incremental should expect the conversion run to take as long as the rebuild it replaces, and schedule it for an hour when nothing waits on the table.

Later runs insert

From the second run on, dbt runs the rendered SELECT into a temporary table, inserts that table's rows into plays with an INSERT INTO followed by a SELECT, and drops the temporary table. The columns are matched by name, so the SELECT has to produce the column names plays already has, in types the warehouse can insert.

INSERT INTO analytics.plays (
    event_id,
    track_id,
    played_at,
    ms_played
)
SELECT
    event_id,
    track_id,
    played_at,
    ms_played
FROM analytics.plays__dbt_tmp

The first run also fixes the table's shape: CREATE TABLE AS takes the columns and their types from the SELECT, and every later insert works inside that shape. Under dbt's default setting the insert lists the columns the table already has. A column added to the SELECT later is left out of every insert without an error, so the table never holds it. A column removed from the SELECT leaves the insert asking for a column the temporary table lacks, and the run fails. Settle the columns before the first run; changing them mid-life is a later lesson.

When a run becomes a first run again

• Someone dropped the table by hand in the warehouse: the next run creates it from scratch
• The model file was renamed: the new name has no table, and the old table stays behind unused
• The command carried --full-refresh: the next section
• An error stopped the first run before CREATE finished: the next run attempts the whole build again

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.

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.