# 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

```sql
{{ 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

```sql
{{ 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:

```sql
{{ 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

```sql
{{ 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:

```sql
{{ 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:

```yaml
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](/lakehouse-platform/project-settings-and-defaults.md).

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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.dexlabs.io/lakehouse-platform/develop-with-dex/materialization.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.