Materialized Views

```
-- models/my_model_mv_mv1.sql
{{ config(materialized='materialized_view') }}
{{ materialization_target_table(ref('events_daily')) }}

select a, b, c from {{ source('raw', 'table_1') }}
```

```
-- models/my_model_mv_mv2.sql
{{ config(materialized='materialized_view') }}
{{ materialization_target_table(ref('events_daily')) }}

select a, b, c from {{ source('raw', 'table_2') }}
```

**3. Iterate them as needed following the instructions in the [explicit target](#explicit-target) section.**

## Behavior comparison between implicit and explicit target approaches[​](#behavior-comparison "Direct link to Behavior comparison between implicit and explicit target approaches")

### How they behave in general[​](#general-behavior "Direct link to How they behave in general")

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Operation Implicit target Explicit target|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | First dbt run All resources created All resources created|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | Next dbt run **Individual resources cannot be managed, all happen together:**  **target table**:   changes managed with the `on_schema_change` setting. By default, it has the setting `ignore`, so new columns are not processed.  **Materialized views**: all updated with `alter table modify query` operations **Changes can be applied individually:  target table**:  automatic detection to know if they are target tables from dbt defined materialized views. If they are, the column evolution is managed by default with the `mv_on_schema_change` setting with `fail` value, so it will fail if column changes. We added this default value as a protection layer  **Materialized views**: Their SQL gets updated with `alter table modify query` operations.| dbt run --full-refresh **Individual resources cannot be managed, all happen together:  target table**:  target table recreated empty. `catchup` available to configure a backfill with the SQL of all the materialized views together. `catchup` is `True` by default  **Materialized views**: all get recreated. **Changes will be applied individually:  target table:** will be recreated as usual.  **Materialized views**: drop and recreate. `catchup` available for an initial backfill. `catchup` is `True` by default.   **Note: During the process, the target table will be empty or partially loaded until the materialized views are recreated. To avoid this, check the next section about how to iterate the target table.** | | | | | | | | | | | |

### Behavior during active ingestion[​](#behavior-during-active-ingestion "Direct link to Behavior during active ingestion")

When iterating your models, you need to be aware of how the different operations interact with the data being inserted:

- As ClickHouse materialized views act as **insert triggers**, they only capture data while they exist. If a materialized view is dropped and recreated (e.g. during a `--full-refresh`), any rows inserted into the source table during that window will **not** be processed by the materialized view. This is referred to as the materialized view being "blind".
- The different `catchup` processes are all based on `INSERT INTO ... SELECT` operations using the materialized views SQL and are independent of how the materialized views work. Once the `INSERT` starts, new data is not captured by it, but it will be captured by the attached materialized view.

The following table summarizes the safety of each operation when inserts are actively happening on the source table.

#### Implicit target operations[​](#ingestion-implicit-target "Direct link to Implicit target operations")

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Operation Internal process Safety while inserts are happening|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | First `dbt run` 1. Create target table 2. Insert data (if `catchup=True`) 3. Create materialized views ⚠️ **Materialized view is blind between steps 1 and 3.** Any rows inserted into the source during this window are not captured.| Subsequent `dbt run` `ALTER TABLE ... MODIFY QUERY` ✅ Safe. The materialized view is updated atomically.|  |  |  | | --- | --- | --- | | `dbt run --full-refresh` 1. Create backup table 2. Insert data (if `catchup=True`) 3. Drop materialized views 4. Exchange tables 5. Recreate materialized views ⚠️ **Materialized view is blind during recreation.** Data inserted into the source between steps 3 and 5 will not appear in the new target table. | | | | | | | | | | | |

#### Explicit target operations[​](#ingestion-explicit-target "Direct link to Explicit target operations")

**Materialized view models:**

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Operation Internal process Safety while inserts are happening|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | First `dbt run` 1. Create MV (with `TO` clause) 2. Run catch-up (if `catchup=True`) ✅ MV is created first, so new inserts are captured immediately. ⚠️ **Catch-up may duplicate data** — the backfill query can overlap with rows already being processed by the MV. Safe if using a deduplicating engine (e.g. `ReplacingMergeTree`).| Subsequent `dbt run` `ALTER TABLE ... MODIFY QUERY` ✅ Safe. The MV is updated atomically.|  |  |  | | --- | --- | --- | | `dbt run --full-refresh` on MVs 1. Drop and recreate MV 2. Run catch-up (if `catchup=True`) ⚠️ **MV is blind during recreation** (between drop and create). ⚠️ **Catch-up may duplicate data** if inserts are happening concurrently. | | | | | | | | | | | |

**Target table model:**

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Operation Internal process Safety while inserts are happening|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `dbt run` Schema changes applied following the `mv_on_schema_change` setting ✅ Safe. No data movement.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `dbt run --full-refresh` (default) Recreate the table (leaves it empty) ⚠️ **Target table is empty** until MVs backfill it. MVs continue inserting into the new table once it exists.| `dbt run --full-refresh` with `repopulate_from_mvs_on_full_refresh=True` 1. Create backup table 2. Insert data using each MV's SQL 3. Exchange tables atomically ⚠️ **MV is blind during recreation.** Data inserted between steps 1 and 3 will not appear in the new table. **This may change in next versions**. | | | | | | | | | | | |

Recommendations for production environments with active ingestion

- **Pause the ingestion during dbt operations if possible**: This will make all operations safe and no data will be lost.
- **Use a deduplicating engine if possible** (e.g. `ReplacingMergeTree`) on the target table to handle potential duplicates from catch-up overlaps.
- **Prefer `ALTER TABLE ... MODIFY QUERY`** (regular `dbt run` without `--full-refresh`) when possible — this is always safe.
- **Be aware of problematic windows** during dbt operations.

## Refreshable Materialized Views[​](#refreshable-materialized-views "Direct link to Refreshable Materialized Views")

[Refreshable Materialized Views](/docs/materialized-view/refreshable-materialized-view) are a special type of materialized view in ClickHouse that periodically re-executes the query and stores the result, similar to how materialized views work in other databases. This is useful for scenarios where you want periodic snapshots or aggregations rather than real-time insert triggers.

Tip

Refreshable materialized views can be used with **both** the [implicit target](#implicit-target) and [explicit target](#explicit-target) approaches. The `refreshable` config is independent of how the target table is managed.

To use a refreshable materialized view, add a `refreshable` config object to your MV model with the following options: