Incremental Strategies & When to Use Them

Incremental materialization is an advanced and powerful feature of dbt; however, you don’t need to use it in every single model of your project. Understanding when and how to leverage incremental materialization can be a game-changer for your data workflows.

In this blog post, I’ll delve into the benefits and trade-offs of incremental and full refresh approaches and guide you through the decision-making process, helping you maximize the efficiency of your data models.

1.1. Incremental vs. Full Refresh

Using the full refresh strategy, dbt will discard the current destination table and create a new one from the whole source transformed data. While using incremental models, you can transform and insert into your tables only recent data.

When choosing between Incremental and Full Refresh, we need to consider the following factors:

The tradeoff between complexity and processing cost/time

Full Refresh has low complexity, you don’t need to worry about any incremental rules and configurations, but it can spend more time and cost more money rebuilding the whole table. However, the transformations are not very costly if the table is not large (a few million rows or less). There is no problem in sticking to full refresh.

Incremental is helpful if data tables have millions, or even billions, of rows or the transformations on the data are computationally expensive (that is, take a long time to execute), for example, complex Regex functions, or UDFs are being used to transform data.

The data change frequency

If the data in the model doesn’t change that much, you don’t take much advantage of the incremental strategies, you can make periodic full refreshes.

But suppose the historical data of the model changes a lot, the incremental strategy will not help to capture the change because it only worries about recent data. In that case, a full refresh could be a better option.

1.2. Incremental Strategies

1.2.1. Append

The append strategy is very straightforward; it just inserts the selected records into the destination table. It can’t update or delete records, just insert. You can only use this strategy if duplicates aren’t your problem.