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.

We recommend avoiding ephemeral models, due to how Y42 abstracts the data warehouse layer.

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.

We do not recommend ephemeral materializations because they are more difficult to debug, cannot be tested and do not confer the benefits of Virtual Data Builds.

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:

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

models/stg_purchases.sql

{{ config(materialized = 'table') }}

select * from {{ source('src_supply_chain', 'purchases') }}

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

models/stg_purchases.yml

version: 2

models:
  - name: stg_purchases
    ...
    config:
      materialized: table
  ...

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

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

In Y42, configuration priority is determined by specificity. The order generally is: configuration blocks within individual files have the highest priority, followed by settings in .yml files, and finally, settings in the dbt_project.yml file. As a result, specific model materialization settings will override the broader configurations set in dbt_project.yml. For example, a default 'view' materialization for all models in a folder, as set in dbt_project.yml, can be individually altered using the model-level config block.

Create a dbt model Incremental models