Materializations

Historically ClickHouse has had only limited support for updates and deletes, in the form of asynchronous "mutations."
To emulate expected dbt behavior,
dbt-clickhouse by default creates a new temporary table containing all unaffected (not deleted, not changed) "old"
records, plus any new or updated records,
and then swaps or exchanges this temporary table with the existing incremental model relation. This is the only strategy
that preserves the original relation if something
goes wrong before the operation completes; however, since it involves a full copy of the original table, it can be quite
expensive and slow to execute.

#### The Delete+Insert Strategy[​](#delete-insert-strategy "Direct link to The Delete+Insert Strategy")

ClickHouse added "lightweight deletes" as an experimental feature in version 22.8. Lightweight deletes are significantly
faster than ALTER TABLE ... DELETE
operations, because they don't require rewriting ClickHouse data parts. The incremental strategy `delete+insert`
utilizes lightweight deletes to implement
incremental materializations that perform significantly better than the "legacy" strategy. However, there are important
caveats to using this strategy:

- Lightweight deletes must be enabled on your ClickHouse server using the setting
  `allow_experimental_lightweight_delete=1` or you
  must set `use_lw_deletes=true` in your profile (which will enable that setting for your dbt sessions)
- Lightweight deletes are now production ready, but there may be performance and other problems on ClickHouse versions
  earlier than 23.3.
- This strategy operates directly on the affected table/relation (with creating any intermediate or temporary tables),
  so if there is an issue during the operation, the
  data in the incremental model is likely to be in an invalid state
- When using lightweight deletes, dbt-clickhouse enabled the setting `allow_nondeterministic_mutations`. In some very
  rare cases using non-deterministic incremental\_predicates
  this could result in a race condition for the updated/deleted items (and related log messages in the ClickHouse logs).
  To ensure consistent results the
  incremental predicates should only include sub-queries on data that will not be modified during the incremental
  materialization.

#### The Microbatch Strategy (Requires dbt-core >= 1.9)[​](#microbatch-strategy "Direct link to The Microbatch Strategy (Requires dbt-core >= 1.9)")

The incremental strategy `microbatch` has been a dbt-core feature since version 1.9, designed to handle large
time-series data transformations efficiently. In dbt-clickhouse, it builds on top of the existing `delete_insert`
incremental strategy by splitting the increment into predefined time-series batches based on the `event_time` and
`batch_size` model configurations.

Beyond handling large transformations, microbatch provides the ability to:

- [Reprocess failed batches](https://docs.getdbt.com/docs/build/incremental-microbatch#retry).
- Auto-detect [parallel batch execution](https://docs.getdbt.com/docs/build/parallel-batch-execution).
- Eliminate the need for complex conditional logic in [backfilling](https://docs.getdbt.com/docs/build/incremental-microbatch#backfills).

For detailed microbatch usage, refer to the [official documentation](https://docs.getdbt.com/docs/build/incremental-microbatch).

##### Available Microbatch Configurations[​](#available-microbatch-configurations "Direct link to Available Microbatch Configurations")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Option Description Default if any|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | event\_time The column indicating "at what time did the row occur." Required for your microbatch model and any direct parents that should be filtered. |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | begin The "beginning of time" for the microbatch model. This is the starting point for any initial or full-refresh builds. For example, a daily-grain microbatch model run on 2024-10-01 with begin = '2023-10-01 will process 366 batches (it's a leap year!) plus the batch for "today." |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | batch\_size The granularity of your batches. Supported values are `hour`, `day`, `month`, and `year` |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | lookback Process X batches prior to the latest bookmark to capture late-arriving records. 1|  |  |  | | --- | --- | --- | | concurrent\_batches Overrides dbt's auto detect for running batches concurrently (at the same time). Read more about [configuring concurrent batches](https://docs.getdbt.com/docs/build/incremental-microbatch#configure-concurrent_batches). Setting to true runs batches concurrently (in parallel). false runs batches sequentially (one after the other).  | | | | | | | | | | | | | | | | | |

#### The Append Strategy[​](#append-strategy "Direct link to The Append Strategy")

This strategy replaces the `inserts_only` setting in previous versions of dbt-clickhouse. This approach simply appends
new rows to the existing relation.
As a result duplicate rows are not eliminated, and there is no temporary or intermediate table. It is the fastest
approach if duplicates are either permitted
in the data or excluded by the incremental query WHERE clause/filter.

#### The insert\_overwrite Strategy (Experimental)[​](#insert-overwrite-strategy "Direct link to The insert_overwrite Strategy (Experimental)")

> [IMPORTANT]  
> Currently, the insert\_overwrite strategy is not fully functional with distributed materializations.

Performs the following steps:

1. Create a staging (temporary) table with the same structure as the incremental model relation:
   `CREATE TABLE <staging> AS <target>`.
2. Insert only new records (produced by `SELECT`) into the staging table.
3. Replace only new partitions (present in the staging table) into the target table.

This approach has the following advantages:

- It is faster than the default strategy because it doesn't copy the entire table.
- It is safer than other strategies because it doesn't modify the original table until the INSERT operation completes
  successfully: in case of intermediate failure, the original table is not modified.
- It implements "partitions immutability" data engineering best practice. Which simplifies incremental and parallel data
  processing, rollbacks, etc.

The strategy requires `partition_by` to be set in the model configuration. Ignores all other strategies-specific
parameters of the model config.

## Materialization: materialized\_view[​](#materialized-view "Direct link to Materialization: materialized_view")

The `materialized_view` materialization creates a ClickHouse [materialized view](/docs/sql-reference/statements/create/view#materialized-view) that acts as an insert trigger, automatically transforming and inserting new rows from a source table into a target table. This is one of the most powerful materializations available in dbt-clickhouse.

Due to its depth, this materialization has its own dedicated page. **[Go to the Materialized Views guide](/docs/integrations/dbt/materialization-materialized-view)** for the full documentation

## Materialization: dictionary (experimental)[​](#materialization-dictionary "Direct link to Materialization: dictionary (experimental)")

See the tests
in <https://github.com/ClickHouse/dbt-clickhouse/blob/main/tests/integration/adapter/dictionary/test_dictionary.py> for
examples of how to
implement materializations for ClickHouse dictionaries

## Materialization: distributed\_table (experimental)[​](#materialization-distributed-table "Direct link to Materialization: distributed_table (experimental)")

Distributed table created with following steps: