Materializations

Understand dbt model materializations.

Overview

Materializations define how a particular model is persisted in the data warehouse. Y42 offers two primary options for materialization: views and tables. It is not recommended to use the third option, ephemeral, due to how Y42 abstracts the data warehouse layer.

Materialization types

Views

Views are the most cost-effective materialization type as they only persist the query and not the data itself. For example. when a scheduled build configured with only materialized views runs, it executes CREATE VIEW statements against the data warehouse without processing any data.

Tables

Materializing models as tables can speed up data access, especially when model execution is slowing down. For example, creating tables at the end of a data pipeline enables faster queries as data consumers only need to run the last model, instead of all upstream models.

Incremental

Incremental models are built as tables, but unlike full-table materializations, incremental models update only new or changed rows in a data table, making data processing significantly faster and more efficient.

For more in-depth information about incremental models, please follow this link.

Ephemeral

Ephemeral materialization injects models as Common Table Expressions (CTEs) inside downstream tables.

Configure materialization type

By default, dbt models are materialized as views. However, you can customize this setting by introducing the materialized YAML configuration parameter:

dbt_project.yml

name: 'y42_project'
version: '1.0.0'
config-version: 2
# This setting configures which "profile" dbt uses for this project.
profile: 'dbt_project'
# ... 
# In this example config, we tell dbt to build all models in the example/ directory
# as tables. These settings can be overridden in the individual model files
# using the `{{ config(...) }}` macro.
models:
  y42_project:
    staging:
      # Materialize all models under models/staging/ as tables
      +materialized: table
    export:
      # Materialize all models under /models/export/ as views
      +materialized: view

You can also configure a model's materialization type using a configuration block in its .sql file:

models/stg_purchases.sql

{{ config(materialized = 'table') }}
select * from {{ source('src_faker', 'purchases') }}

Writing SQL queries Incremental models