Materialized Views

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

- **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[​](#explicit-target-limitations "Direct link to 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[​](#explicit-target-usage "Direct link to 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[​](#explicit-target-configuration "Direct link to Configuration options")

When using explicit target tables, 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` | | | | | |

Note

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[​](#explicit-target-common-operations "Direct link to Common operations")

#### Full refresh with explicit targets[​](#explicit-target-full-refresh "Direct link to 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:

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.

```
-- 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[​](#explicit-target-changing "Direct link to Changing the target table")