In dbt, materialisations define how models are built and stored in the data warehouse. They determine whether the model will be built as a table, view, CTE, incremental model, or a snapshot, influencing performance, storage, and how the data is refreshed or updated.
Views
Builds a model as a view in the database. They are quick to build but can take longer periods of time to query data from. They can be configured as below:
{{ config(
materialized='view'
)}}
Tables
Builds a model as a table in the database. While they are slow to build they are fast to query once they are present in the database. They can be configured as below:
{{ config(
materialized='table'
)}}
Ephemeral Models
Builds a model as a CTE. The model is, therefore, not built in the final database, but can be used in models downstream. This increases the build time of models downstream, however, can help a user maintain a cleaner database. They can be configured as below:
{{ config(
materialized='ephemeral'
)}}
Incremental Models
On the first run builds a model as an entire table. On all runs after only new records are processed and appended. The model knows which records are new by configuring a where statement such as:
where timestamp >= (select max(timestampt) from {{ this }}
Snapshots
Builds a table in the database, usually in a separate dedicated schema. It can be used to scan underlying data and append any new records to your data. This allows to capture historical data and how it has changed and evolved over time.