Materialized Views

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/etl-tools/dbt/materialization-materialized-view.md)

ClickHouse Supported

A `materialized_view` materialization should be a `SELECT` from an existing (source) table. Unlike PostgreSQL, a ClickHouse materialized view is not "static" (and has no corresponding REFRESH operation). Instead, it acts as an **insert trigger**, inserting new rows into a target table by applying the defined `SELECT` transformation on rows inserted into the source table. See the [ClickHouse materialized view documentation](/docs/materialized-views) for more details on how materialized views work in ClickHouse.

Note

For general materialization concepts and shared configurations (engine, order\_by, partition\_by, etc.), see the [Materializations](/docs/integrations/dbt/materializations) page.

## How the target table is managed[​](#target-table-management "Direct link to How the target table is managed")

When you use the `materialized_view` materialization, dbt-clickhouse needs to create both a **materialized view** and a **target table** where the transformed rows are inserted. There are two ways to manage the target table:

|  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Approach Description Status|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | **Implicit target** dbt-clickhouse creates and manages the target table automatically within the same model. The target table schema is inferred from the MV's SQL. Stable|  |  |  | | --- | --- | --- | | **Explicit target** You define the target table as a separate `table` materialization and reference it from your MV model using the `materialization_target_table()` macro. The MV is created with a `TO` clause pointing to that table. This functionality is available starting from **dbt-clickhouse version 1.10**. **Caution**: This feature is in beta and the API may change based on community feedback. **Beta** | | | | | | | | |

The approach you choose affects how schema changes, full refreshes, and multi-MV setups are handled. The following sections describe each approach in detail.

## Materialization with implicit target[​](#implicit-target "Direct link to Materialization with implicit target")

This is the default behavior. When you define a `materialized_view` model, the adapter will:

1. Create a **target table** with the model name
2. Create a ClickHouse **materialized view** with the name `<model_name>_mv`

The target table schema is inferred from the columns in the MV's `SELECT` statement. All resources (target table + MVs) share the same model configuration.

```
-- models/events_mv.sql
{{
    config(
        materialized='materialized_view',
        engine='SummingMergeTree()',
        order_by='(event_date, event_type)'
    )
}}

SELECT
    toStartOfDay(event_time) AS event_date,
    event_type,
    count() AS total
FROM {{ source('raw', 'events') }}
GROUP BY event_date, event_type
```

See the [test file](https://github.com/ClickHouse/dbt-clickhouse/blob/main/tests/integration/adapter/materialized_view/test_materialized_view.py) for additional examples.

### Multiple materialized views[​](#multiple-materialized-views "Direct link to Multiple materialized views")

ClickHouse allows more than one materialized view to write records to the same target table. To support this in dbt-clickhouse with the implicit target approach, you can construct a `UNION` in your model file, wrapping the SQL for each materialized view with comments of the form `--my_mv_name:begin` and `--my_mv_name:end`.

For example, the following will build two materialized views both writing data to the same destination table of the model. The names of the materialized views will take the form `<model_name>_mv1` and `<model_name>_mv2`:

```
--mv1:begin
select a,b,c from {{ source('raw', 'table_1') }}
--mv1:end
union all
--mv2:begin
select a,b,c from {{ source('raw', 'table_2') }}
--mv2:end
```

Note

When updating a model with multiple materialized views (MVs), especially when renaming one of the MV names,
dbt-clickhouse does not automatically drop the old MV. Instead,
you will encounter the following warning:

`Warning - Table <previous table name> was detected with the same pattern as model name <your model name> but was not found in this run. In case it is a renamed mv that was previously part of this model, drop it manually (!!!)`

### How to iterate the target table schema[​](#how-to-iterate-the-target-table-schema "Direct link to How to iterate the target table schema")

Starting with **dbt-clickhouse version 1.9.8**, you can control how the target table schema is iterated when `dbt run` encounters different columns in the MV's SQL.

```
{{config(
    materialized='materialized_view',
    engine='MergeTree()',
    order_by='(id)',
    on_schema_change='fail'  # this setting
)}}
```

By default, dbt will not apply any changes to the target table (`ignore` setting value), but you can change this setting to follow the same behavior as the `on_schema_change` config [in incremental models](https://docs.getdbt.com/docs/build/incremental-models#what-if-the-columns-of-my-incremental-model-change).

Also, you can use this setting as a safety mechanism. If you set it to `fail`, the build will fail if the columns in the MV's SQL differ from the target table that was created by the first `dbt run`.

### Data catch-up[​](#data-catch-up "Direct link to Data catch-up")

By default, when creating or recreating a materialized view (MV), the target table is first populated with historical data before the MV itself is created (`catchup=True`). You can disable this behavior by setting the `catchup` config to `False`.

```
{{config(
    materialized='materialized_view',
    engine='MergeTree()',
    order_by='(id)',
    catchup=False  # this setting
)}}
```

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Operation `catchup: True` (default) `catchup: False`|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Initial deployment (`dbt run`) Target table backfilled with historical data Target table created empty|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | Full refresh (`dbt run --full-refresh`) Target table rebuilt and backfilled Target table recreated empty, **existing data lost**| Normal operation Materialized view captures new inserts Materialized view captures new inserts | | | | | | | | | | | |

Data Loss Risk with Full Refresh

Using `catchup: False` with `dbt run --full-refresh` will **discard all existing data** in the target table. The table will be recreated empty and only capture new data going forward. Ensure you have backups if the historical data might be needed later.

## Materialization with explicit target (Beta)[​](#explicit-target "Direct link to Materialization with explicit target (Beta)")

Beta

This feature is in beta and available starting from **dbt-clickhouse version 1.10**. The API may change based on community feedback.

By default, dbt-clickhouse creates and manages both the target table and the materialized views within a single model (the [implicit target](#implicit-target) approach described above). This approach has some limitations:

- All resources (target table + MVs) share the same configuration. If multiple MVs are pointing to the same target table, they must be defined together using `UNION ALL` syntax.
- None of these resources can be iterated separately, all need to be managed using the same model file.
- You cannot easily control the name of each MV.
- All settings are shared between the target table and the MVs, making it difficult to configure each resource individually and to reason about which configuration belongs to each resource.

The **explicit target** feature allows you to define the target table separately as a regular `table` materialization and then reference it from your materialized view models.

### Benefits[​](#explicit-target-benefits "Direct link to Benefits")