Materialization

Materializations

Materializations define how a model is persisted in your data warehouse. In dex, you can configure each model to be materialized as a table, view, incremental, or ephemeral depending on your use case and performance needs.

By choosing the right materialization strategy, you can optimize speed, cost, storage, and refresh behavior across your data pipeline.

Available Materializations

Table

Creates a physical table in your warehouse. The table is rebuilt each time the model runs, fully replacing its contents.

• Best for: Stable datasets that don’t change frequently but need to be queried often.

• Pros: Fast queries, predictable behavior

• Cons: Full refresh on every run

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

View

Creates a logical view—the query is re-executed every time you query the model. No data is stored; only the query definition is saved in your warehouse.

• Best for: Lightweight transformations or early development

• Pros: Always up-to-date, no storage cost

• Cons: Slower performance, depends on upstream availability

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

Incremental

Creates or updates a table that only refreshes new or changed records. This is ideal for large datasets where full refreshes would be too costly or slow.

• Best for: Fact tables, event logs, large historical data

• Pros: Efficient, scalable

• Cons: Requires careful logic to avoid duplicates

To use incremental, you must define a unique_key or insert/update logic with filters:

{{ config(materialized='incremental', unique_key='order_id') }}

select *
from staging.orders
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

Ephemeral

The model is not materialized at all. Instead, its logic is inlined as a CTE (Common Table Expression) in downstream models. It exists only during compilation.

• Best for: Lightweight logic reused across models (e.g., deduplication, filters)

• Pros: No warehouse object created, fast compile

• Cons: Can be hard to debug, only usable as part of other models

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

Setting Default Materializations

You can set materializations:

1. At the model level

Use the config() block at the top of your .sql file:

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

select * from {{ ref('customers') }}

2. At the folder level in dbt_project.yml

This defines defaults for every model in a folder:

models:
  my_project:
    1.raw:
      +materialized: view
    2.cleaned:
      +materialized: table
    3.trusted:
      +materialized: incremental

You can override this per model using config(). Read more about configuring defaults here.

Best Practices

• Use view for early-stage dev and quick iteration

• Use table for finalized models with frequent reuse

• Use incremental for high-volume, slowly changing datasets

• Use ephemeral for modular, reusable logic