How to Build Incremental Models

dbt vs SQLMesh and what is happening beneath the shiny syntax

If you’re a Senior Analytics Engineer, or striving to become one, you need to have a good grasp of incremental models. In fact, this was one of the most popular interview topics touched on when looking for my current role!

One company even asked me to build an incremental model using dbt syntax during one of my technical interviews.

Incremental models can be confusing when you first start using them, so I want to break down exactly what they are doing under the hood and how to configure them in a tool like dbt.

What is an incremental model?

When you run a model that’s materialized as a table, the logic is computed on top of all the data in the data source. Behind the scenes, a new temporary table is being created, the old table is being dropped, and then the temporary table is renamed to the name of the old table.

This is also known as a swap strategy and looks like this:

-- create table with new code
CREATE TABLE my_schema.my_model__dbt_tmp AS (
  -- Your SELECT statement here
);

-- drop table with old code 
DROP TABLE IF EXISTS my_schema.my_model;

-- rename new temp table to old table name 
ALTER TABLE my_schema.my_model__dbt_tmp 
RENAME TO my_model;

While this works just fine for many models, it can cause issues when you are dealing with very large data sources with 10s of millions or billions of rows. It becomes expensive in both time and compute costs.

This is where incremental models come in.

Instead of recomputing logic on your entire data source, which comes with building, deleting, and altering tables, these only compute the logic on data that meets your specified conditions, which typically involves a time frame.

Behind the scenes, incremental models are built using a delete+insert or merge strategy.

With delete+insert, matching rows are removed from the existing table and then new ones are inserted in their place.

With merge, existing rows in the existing table are updated and new ones are added.

If using a delete+insert strategy, the command behind-the-scenes look something like this: