Materialization types

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 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.

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

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