While the table materialization also creates your models as tables, it rebuilds the table on each dbt run. It's often desirable to build models as tables in your data warehouse since downstream queries are more performant. Understanding incremental models When should I use an incremental model? It's also advisable to rebuild any downstream models, as indicated by the trailing +.įor detailed usage instructions, check out the dbt run documentation. $ dbt run -full-refresh -select my_incremental_model+ This flag will cause dbt to drop the existing target table in the database before rebuilding it for all-time. To force dbt to rebuild the entire incremental model from scratch, use the -full-refresh flag on the command line. In this case, you should rebuild your incremental model. If your incremental model logic has changed, the transformations on your new rows of data may diverge from the historical transformations, which are stored in your target table. Instead, the inclusion of the unique_key parameter ensures the existing row is updated instead. Where date_day >= ( select max ( date_day ) from )īuilding this model incrementally without the unique_key parameter would result in multiple rows in the target table for a single day – one row for each time dbt runs on that day.

this filter will only be applied on an incremental run If the unique_key is not present in the 'old' data, dbt will insert the entire row into the table.ĭate_trunc ( 'day', event_at ) as date_day ,Ĭount ( distinct user_id ) as daily_active_users.The exact mechanics of how that update/replace takes place will vary depending on your database and incremental strategy. If the same unique_key is present in the 'new' and 'old' model data, dbt will update/replace the old row with the new row of data.When you define a unique_key, you'll see this behavior for each row of 'new' data returned by your dbt model: Not specifying a unique_key will result in append-only behavior, which means dbt inserts all rows returned by the model's SQL into the preexisting target table without regard for whether the rows represent duplicates.

By using unique_key, you can ensure that each row from the source table is represented by a single row in your incremental model, without duplicates. In some warehouses, filtering your records early can vastly improve the run time of your query! Defining a uniqueness constraint (optional) Ī unique_key determines whether a record has new values and should be updated. Like the other materializations built into dbt, incremental models are defined with select statements, with the materialization defined in a config block.įor more complex incremental models that make use of Common Table Expressions (CTEs), you should consider the impact of the position of the is_incremental() macro on query performance. This improves warehouse performance and reduces compute costs. Using an incremental model limits the amount of data that needs to be transformed, vastly reducing the runtime of your transformations. As such, on each dbt run, your model gets built incrementally. Often, the rows you filter for on an incremental run will be the rows in your source data that have been created or updated since the last time dbt ran. On subsequent runs, dbt transforms only the rows in your source data that you tell dbt to filter for, inserting them into the target table which is the table that has already been built. The first time a model is run, the table is built by transforming all rows of source data. Incremental models are built as tables in your data warehouse. Configuring incremental models About incremental models

Existing universal database models