Materialized Views

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

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

#### Target table is empty while/after `run` is executed[​](#target-table-empty "Direct link to target-table-empty")

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[​](#full-refresh-with-repopulate-from-mvs-on-full-refresh "Direct link to full-refresh-with-repopulate-from-mvs-on-full-refresh")

`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[​](#duplicate-data "Direct link to 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[​](#data-loss-active-ingestion "Direct link to data-loss-active-ingestion")

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[​](#debugging-techniques "Direct link to Debugging techniques")

#### Check the current target of an MV in ClickHouse[​](#check-mv-target "Direct link to 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[​](#check-dbt-recognition "Direct link to 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[​](#migration-implicit-to-explicit "Direct link to 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](#explicit-target) section.**

## Behavior comparison between implicit and explicit target approaches[​](#behavior-comparison "Direct link to Behavior comparison between implicit and explicit target approaches")

### How they behave in general[​](#general-behavior "Direct link to How they behave in general")