Materialization types

Materialization types

Overview

Materializations define how a particular model is persisted in the data warehouse. Y42 offers three materializations options: tables, views, and incremental models.

Materialization types

Views

Views persist only the query, not the data itself, using CREATE VIEW statements.

  • Pros: Cost-effective, no additional data storage, always reflects the latest records.
  • Cons: Slow when performing significant transformations or when stacked on other views.
  • Advice: Start with views for your models and switch to other materializations if performance issues arise. Ideal for models with minimal transformations, like renaming or recasting columns.

Tables

Tables are created with CREATE TABLE AS statements and can speed up data access.

  • Pros: Fast to query.
  • Cons: Rebuilding tables with the y42 build command, especially for complex transformations, can be time-consuming. New records in source data are not automatically added.
  • Advice: Opt for table materialization for models used by BI tools or for slower transformations impacting many downstream models.

Incremental

Built as tables, incremental models update only new or changed rows, using insert or update commands.

  • Pros: Reduces build time by processing only new records.
  • Cons: Requires extra configuration and is considered an advanced feature.
  • Advice: Ideal for event-style data or when Y42 runs are becoming too slow. Incremental models should not be the starting point.

For more in-depth information about incremental models, go to this page.

Ephemeral

Ephemeral materialization constructs your model as a Common Table Expression (CTE) within other models that reference it.

Configure materialization type

By default, Y42 models are materialized as views. However, you can customize this setting by updating the materialized configuration parameter in three ways:

  1. In the model sql file (impacts only the current model): Use the config block in your model's SQL file.
models/stg_purchases.sql

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

  1. In the model yml file (impacts only the current model): Modify the materialization settings in the corresponding model YML file.
models/stg_purchases.yml

_10
version: 2
_10
_10
models:
_10
- name: stg_purchases
_10
...
_10
config:
_10
materialized: table
_10
...

  1. In the root dbt_project.yml file (impacts all models in the folder)
dbt_project.yml

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