Materialized Views - ClickHouse Documentation

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 for more details on how materialized views work in ClickHouse.

For general materialization concepts and shared configurations (engine, order_by, partition_by, etc.), see the Materializations page.

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

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

Create a target table with the model name
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 for additional examples.

You can also define column-level codec and ttl on the target table by enforcing a model contract. See Column Configuration for details.

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

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

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

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 RefreshUsing 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)

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

Fully separated resources: Now each resource can be defined separately, improving readability
1:1 resources between dbt and CH: Now you can use dbt tooling to manage and iterate them separately.
Different configurations now available: Now a different configuration can be applied to each one.
No more need to keep naming conventions: Now all resources are created using the name you give, not the custom one added with the _mv for MVs.

Limitations

Target table definition is not natural to dbt: it’s not a SQL that will read from a source table, so you lose dbt validations here. MV’s SQL will still get validated using dbt utilities and its compatibility with the target table’s columns will be validated at CH level.
We found some problems related to limitations to the ref() function: We need to use it to reference models between them but it can only be used to reference upstream models, not downstream. This causes some problems for this implementation. We have created an issue in the dbt-core repo and we are currently talking with them to look for possible solutions (dbt-labs/dbt-core#12319):
- When ref() is called from inside the config block, it returns the current model, not the one shared. This blocks us from defining it in the config() section, forcing us to use a comment to add this dependency. We are following the same pattern as defined in the dbt docs with the “—depends_on:” approach.
- ref() works for us as it forces the target table to be created first, but in the dependency chart in the generated documentation, the target table will be drawn as another upstream dependency, not downstream, making it a bit difficult to understand.
- unit-test also forces us to define some data for the target table even when the idea is not to read from it. The workaround is just to leave the data for this table empty.

Usage

Step 1: Define the target table as a regular table model Model events_daily.sql:

{{
    config(
        materialized='table',
        engine='SummingMergeTree()',
        order_by='(event_date, event_type)',
        partition_by='toYYYYMM(event_date)'
    )
}}

SELECT
    toDate(now()) AS event_date,
    '' AS event_type,
    toUInt64(0) AS total
WHERE 0  -- Creates empty table with correct schema

This is the workaround we mention in the limitations section. You may lose some dbt validations here, but the schema will still be checked at ClickHouse level. Step 2: Define materialized views pointing to the target table For example, you can define different MVs in different models like this, even pointing to the same target table. Note the new {{ materialization_target_table(ref('events_daily')) }} macro call, which configures the target table for the MV. Model page_events_aggregator.sql:

{{ config(materialized='materialized_view') }}
{{ materialization_target_table(ref('events_daily')) }}

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

Model mobile_events_aggregator.sql:

{{ config(materialized='materialized_view') }}
{{ materialization_target_table(ref('events_daily')) }}

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

Configuration options

When using explicit target tables, apart from the general materialization configurations and the table-specific configurations, the following configurations apply: On the target table (materialized='table'):

Option	Description	Default
`mv_on_schema_change`	How to handle schema changes when the table is used by dbt-managed MVs. Follows the same behavior as the `on_schema_change` config in incremental models.	Caution: A `materialized='table'` model will behave as usual if it doesn’t have MVs pointing to it, so even if this setting is defined, it will be ignored. If the table is target of MVs, this config will have the default value of `mv_on_schema_change='fail'` to protect the data inside these tables.
`repopulate_from_mvs_on_full_refresh`	On `--full-refresh`, instead of running the table’s SQL, rebuild the table by executing INSERT-SELECTs using the SQL from all MVs pointing to it.	`False`

On the materialized view (materialized='materialized_view'):

Option	Description	Default
`catchup`	Whether to backfill historical data when the MV is created.	`True`

You’ll usually only want to set catchup to True in MVs or repopulate_from_mvs_on_full_refresh to True in their target tables. If you set both to True, it may duplicate data.

Common operations

Full refresh with explicit targets

When using --full-refresh, explicit target tables will be recreated (so you may lose data if ingestion is happening during this process). This will behave in different ways depending on your configurations: Option 1: default --full-refresh behavior. All gets recreated, but during the recreation of the MVs, the target table will be empty or partially loaded. All gets dropped and recreated. If you want to reinsert the data using the MVs SQL, keep the setting catchup=True:

-- models/page_events_aggregator.sql
{{ config(
    materialized='materialized_view',
    catchup=True  -- this is the default value so you don't need to actually set it.
) }}
{{ materialization_target_table(ref('events_daily')) }}
...

Option 2: I want to recreate the target table and I don’t want to read empty data while the MVs are being recreated. If you need to update the sql of the MVs first, you can set in them catchup=False and then do a dbt run or dbt run --full-refresh on the MVs. Make sure that the MVs are created before running --full-refresh on the target table, as it uses the MV definitions from ClickHouse. Set repopulate_from_mvs_on_full_refresh=True on the target table model. On a dbt run --full-refresh, this will:

Create a new temporary table
Execute INSERT-SELECT using each MV’s SQL
Atomically swap the tables

So you will not see empty data in your table while the MVs are being recreated.

-- models/events_daily.sql
{{
    config(
        materialized='table',
        engine='SummingMergeTree()',
        order_by='(event_date, event_type)',
        repopulate_from_mvs_on_full_refresh=True
    )
}}
...

Changing the target table

You cannot change the target table of an MV without a --full-refresh. If you try to run a regular dbt run after changing the materialization_target_table() reference, the build will fail with an error message indicating that the target has changed. To change the target:

Update the materialization_target_table() call
Run dbt run --full-refresh -s your_mv_model

Troubleshooting common issues

Target table is empty while/after `run` is executed

There are a few reasons why this can happen:

Materialized views may be configured with catchup=False or the target table may be configured with repopulate_from_mvs_on_full_refresh=False, so no backfill is executed when the materialized views are created or when the target table is recreated. This is the expected behavior, so if you want to reinsert the data using the materialized views SQL, make sure to set catchup=True in the materialized view (this is the default value) or repopulate_from_mvs_on_full_refresh=True in the target table. Make sure you are not activating both at the same time to avoid duplicates. Check the configuration section for more details.
While a dbt run --full-refresh is executed, if the materialized views use the catchup=True default, the target will get recreated and the MVs will reinsert the data sequentially. To avoid this situation, check the Full refresh with explicit targets.

`dbt run --full-refresh` in a target table with `repopulate_from_mvs_on_full_refresh=True` uses the logic from old materialized view versions, not from the SQL that is currently in the project

repopulate_from_mvs_on_full_refresh=True uses the existing MV SQL that’s already defined in ClickHouse. To make sure the new materialized view definition is used, do a dbt run for each materialized view before doing a dbt run --full-refresh in the target table.

There’s duplicate data after a run is executed

Possible reasons:

Both catchup=True on the materialized views and repopulate_from_mvs_on_full_refresh=True on the target table may be enabled: Keep only one of them depending on the operations you want to run. Check the configuration section for more details.
Target table is not defined with WHERE 0: target table should be created empty, but the internal query may insert data if the WHERE 0 is not included. Make sure the clause is included.

Data loss during active ingestion after a `dbt run --full-refresh` is executed

Some rows from the source table are missing in the target table after a dbt run --full-refresh is executed. ClickHouse materialized views act as insert triggers — they only capture data while they exist. During a full refresh, there is a brief window where the MV is dropped and recreated (the “blind window”). Any rows inserted into the source table during this window are not captured. Check the Behavior during active ingestion section for more details.

Debugging techniques

Check the current target of an MV in ClickHouse

Query system.tables to see where a materialized view is writing:

SELECT
    name as mv_name,
    replaceRegexpOne(
        create_table_query,
        '.*TO\\s+`?([^`\\s(]+)`?\\.`?([^`\\s(]+)`?.*',
        '\\1.\\2'
    ) AS target_table
FROM system.tables
WHERE database = 'your_schema'
  AND engine = 'MaterializedView'

Check if dbt recognizes a table as a materialized view target

During a dbt run, look for this log message:

Table <table_name> is used as a target by a dbt-managed materialized view. Defaulting mv_on_schema_change to “fail” to prevent data loss.

If this message appears, dbt has detected that the table is targeted by at least one dbt-managed materialized view. If you expect this message but don’t see it, verify that:

The materialized view model defines {{ materialization_target_table(ref('your_target')) }} correctly
The materialized view model has materialized='materialized_view' in its config
Both the materialized view and the target table have been run at least once

Migrating from implicit to explicit target

If you have existing materialized view models using the implicit target approach and want to migrate to the explicit target approach, follow these steps: 1. Create the target table model Create a new model file with materialized='table' that defines the same schema as the current MV target table. Use a WHERE 0 clause to create an empty table. Use the same name as the current implicit materialized view model. You’ll be able to use this model now to iterate the target table.

-- models/events_daily.sql
{{
    config(
        materialized='table',
        engine='MergeTree()',
        order_by='(event_date, event_type)'
    )
}}

SELECT
    toDate(now()) AS event_date,
    '' AS event_type,
    toUInt64(0) AS total
WHERE 0

2. Update your MV models Create new models that will include each the MV SQL and the materialization_target_table() macro call pointing to the new target table. If you were previously using the UNION ALL remove that part and the comments. For the model names you’ll have to follow this naming convention:

if only one MV was defined, this will have the name: <old_model_name>_mv
if multiple MVs were defined, each will have the name: <old_model_name>_mv_<name_in_comments>

Before in my_model.sql (implicit target, single model with UNION ALL):

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

After (explicit target, separate model files):

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

Behavior comparison between implicit and explicit target approaches

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

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

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

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

Refreshable materialized views can be used with both the implicit target and 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:

Option	Description	Required	Default Value
refresh_interval	The interval clause (required)	Yes
randomize	The randomization clause, will appear after `RANDOMIZE FOR`
append	If set to `True`, each refresh inserts rows into the table without deleting existing rows. The insert is not atomic, just like a regular INSERT SELECT.		False
depends_on	A dependencies list for the refreshable mv. Please provide the dependencies in the following format `{schema}.{view_name}`
depends_on_validation	Whether to validate the existence of the dependencies provided in `depends_on`. In case a dependency doesn’t contain a schema, the validation occurs on schema `default`		False

Example with implicit target

{{
    config(
        materialized='materialized_view',
        engine='MergeTree()',
        order_by='(event_date)',
        refreshable={
            "interval": "EVERY 5 MINUTE",
            "randomize": "1 MINUTE",
            "append": True,
            "depends_on": ['schema.depend_on_model'],
            "depends_on_validation": True
        }
    )
}}

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

Example with explicit target

{{
    config(
        materialized='materialized_view',
        refreshable={
            "interval": "EVERY 1 HOUR",
            "append": False
        }
    )
}}
{{ materialization_target_table(ref('events_daily')) }}

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

Limitations

When creating a refreshable materialized view (MV) in ClickHouse that has a dependency, ClickHouse does not throw an error if the specified dependency does not exist at the time of creation. Instead, the refreshable MV remains in an inactive state, waiting for the dependency to be satisfied before it starts processing updates or refreshing. This behavior is by design, but it may lead to delays in data availability if the required dependency is not addressed promptly. You should ensure all dependencies are correctly defined and exist before creating a refreshable materialized view.
As of today, there is no actual “dbt linkage” between the mv and its dependencies, therefore the creation order is not guaranteed.
The refreshable feature was not tested with multiple mvs directing to the same target model.

​How the target table is managed

​Materialization with implicit target

​Multiple materialized views

​How to iterate the target table schema

​Data catch-up

​Materialization with explicit target (Beta)

​Benefits

​Limitations

​Usage

​Configuration options

​Common operations

​Full refresh with explicit targets

​Changing the target table

​Troubleshooting common issues

​Target table is empty while/after run is executed

​dbt run --full-refresh in a target table with repopulate_from_mvs_on_full_refresh=True uses the logic from old materialized view versions, not from the SQL that is currently in the project

​There’s duplicate data after a run is executed

​Data loss during active ingestion after a dbt run --full-refresh is executed

​Debugging techniques

​Check the current target of an MV in ClickHouse

​Check if dbt recognizes a table as a materialized view target

​Migrating from implicit to explicit target

​Behavior comparison between implicit and explicit target approaches

​How they behave in general

​Behavior during active ingestion

​Implicit target operations

​Explicit target operations

​Refreshable Materialized Views

​Example with implicit target

​Example with explicit target

​Limitations

How the target table is managed

Materialization with implicit target

Multiple materialized views

How to iterate the target table schema

Data catch-up

Materialization with explicit target (Beta)

Benefits

Limitations

Usage

Configuration options

Common operations

Full refresh with explicit targets

Changing the target table

Troubleshooting common issues

Target table is empty while/after `run` is executed

`dbt run --full-refresh` in a target table with `repopulate_from_mvs_on_full_refresh=True` uses the logic from old materialized view versions, not from the SQL that is currently in the project

There’s duplicate data after a run is executed

Data loss during active ingestion after a `dbt run --full-refresh` is executed

Debugging techniques

Check the current target of an MV in ClickHouse

Check if dbt recognizes a table as a materialized view target

Migrating from implicit to explicit target

Behavior comparison between implicit and explicit target approaches

How they behave in general

Behavior during active ingestion

Implicit target operations

Explicit target operations

Refreshable Materialized Views

Example with implicit target

Example with explicit target

Limitations