Understanding Incremental dbt Models

Lab Steps

lock
Understanding Incremental dbt Models
lock
Connecting to the dbt Web IDE
lock
Creating dbt Models Using Incremental Materialization
lock
Executing Incremental dbt Models
Need help? Contact our support team

Here you can find the instructions for this specific Lab Step.

If you are ready for a real environment experience please start the Lab. Keep in mind that you'll need to start from the first step.

Introduction

Incremental models are very helpful when you need to build tables containing events or facts that happened and that can't change such as bank transactions or clicks on a website.

In this lab step, you will learn more about incremental dbt models.

 

Incremental Models

In dbt, incremental models are materialized as tables, and can be implemented by using the incremental materialization type. Incremental models allow you to not fully-refresh tables on each dbt execution, otherwise to add only new records that are missing in the table. Depending on the database technology you are using, dbt performs this operation in different ways. In some cases, it leverages the merge operation; in others, it uses the plain insert operation.

When working with incremental models, you have mainly two ways to let dbt know which strategy to use in order to achieve the incremental load:

  • model primary key: You can configure the model's primary key by leveraging the field unique_key in the config model's function. This way dbt will add new rows that have values for the primary key that are not in the table
  • is_incremental() function with where condition: dbt offers you the is_incremental() function. The function returns true only if the table corresponding to the dbt model is already in place, and if the incremental load is enabled. By using it, you can create a where condition to let dbt only select new rows (ex. depending on the creation date)

You can refer to the official dbt docs in order to get deeper insights on incremental dbt models.

 

Changes in Incremental Models

When working with incremental models, you could need to change the structure of your model, because the incremental materialization is enabled, dbt won't delete and re-create the table automatically. If you want to do so also to backfill old records, you need to manually specify that the next dbt execution needs to be performed by following the full-refresh materialization with a command similar to the following:

Copy code
1
dbt run --full-refresh <models or prefixes>

If you don't want to perform a full-refresh but want to handle schema changes, you can do it by using the on_schema_change field available in the config model's function. Here you can find the official dbt reference to the field.