> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-home-button.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
> Specific documentation for the materialized_view materialization
# Materialized Views
export const ClickHouseSupportedBadge = () => {
return ;
};
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](/concepts/features/materialized-views) 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](/integrations/connectors/data-ingestion/etl-tools/dbt/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:
1. Create a **target table** with the model name
2. Create a ClickHouse **materialized view** with the 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.
```sql theme={null}
-- 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.
You can also define column-level `codec` and `ttl` on the target table by enforcing a model contract. See [Column Configuration](/integrations/connectors/data-ingestion/etl-tools/dbt/materializations#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 `_mv1` and `_mv2`:
```sql theme={null}
--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 was detected with the same pattern as 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.
```python theme={null}
{{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
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`.
```python theme={null}
{{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)
**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
* **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)](https://github.com/dbt-labs/dbt-core/issues/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](https://docs.getdbt.com/reference/dbt-jinja-functions/ref#forcing-dependencies).
* `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`:
```sql theme={null}
{{
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`:
```sql theme={null}
{{ 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`:
```sql theme={null}
{{ 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](/integrations/connectors/data-ingestion/etl-tools/dbt/materializations#general-materialization-configurations) and the [table-specific configurations](/integrations/connectors/data-ingestion/etl-tools/dbt/materializations#materialization-table), 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](https://docs.getdbt.com/docs/build/incremental-models#what-if-the-columns-of-my-incremental-model-change). | **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`:
```sql theme={null}
-- 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:
1. Create a new temporary table
2. Execute INSERT-SELECT using each MV's SQL
3. Atomically swap the tables
So you will not see empty data in your table while the MVs are being recreated.
```sql theme={null}
-- 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:
1. Update the `materialization_target_table()` call
2. 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](#explicit-target-configuration) 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](#explicit-target-full-refresh).
`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](#explicit-target-configuration) 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](#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:
```sql theme={null}
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 `` 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.
```sql theme={null}
-- 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: `_mv`
* if multiple MVs were defined, each will have the name: `_mv_`
Before in `my_model.sql` (implicit target, single model with UNION ALL):
```sql theme={null}
--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):
```sql theme={null}
-- 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') }}
```
```sql theme={null}
-- 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
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](/concepts/features/materialized-views/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.
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:
| 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
```python theme={null}
{{
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
```python theme={null}
{{
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.