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:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| 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[​](#refreshable-implicit-example "Direct link to 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[​](#refreshable-explicit-example "Direct link to 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[​](#refreshable-limitations "Direct link to 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](#target-table-management)- [Materialization with implicit target](#implicit-target)
    - [Multiple materialized views](#multiple-materialized-views)- [How to iterate the target table schema](#how-to-iterate-the-target-table-schema)- [Data catch-up](#data-catch-up)- [Materialization with explicit target (Beta)](#explicit-target)
      - [Benefits](#explicit-target-benefits)- [Limitations](#explicit-target-limitations)- [Usage](#explicit-target-usage)- [Configuration options](#explicit-target-configuration)- [Common operations](#explicit-target-common-operations)- [Troubleshooting common issues](#explicit-target-troubleshooting)- [Debugging techniques](#debugging-techniques)- [Migrating from implicit to explicit target](#migration-implicit-to-explicit)- [Behavior comparison between implicit and explicit target approaches](#behavior-comparison)
        - [How they behave in general](#general-behavior)- [Behavior during active ingestion](#behavior-during-active-ingestion)- [Refreshable Materialized Views](#refreshable-materialized-views)
          - [Example with implicit target](#refreshable-implicit-example)- [Example with explicit target](#refreshable-explicit-example)- [Limitations](#refreshable-limitations)

Was this page helpful?