Guides

### Delete and insert mode (experimental)[​](#deleteinsert-mode-experimental "Direct link to Delete and insert mode (experimental)")

Historically ClickHouse has had only limited support for updates and deletes, in the form of asynchronous [Mutations](/docs/sql-reference/statements/alter). These can be extremely IO-intensive and should generally be avoided.

ClickHouse 22.8 introduced [lightweight deletes](/docs/sql-reference/statements/delete) and ClickHouse 25.7 introduced [lightweight updates](/docs/sql-reference/statements/update). With the introduction of these features, modifications from single update queries, even when being materialized asynchronously, will occur instantly from the user's perspective.

This mode can be configured for a model via the `incremental_strategy` parameter i.e.

```
{{ config(order_by='(updated_at, id, name)', engine='MergeTree()', materialized='incremental', unique_key='id', incremental_strategy='delete+insert') }}
```

This strategy operates directly on the target model's table, so if there is an issue during the operation, the data in the incremental model is likely to be in an invalid state - there is no atomic update.

In summary, this approach:

1. The adapter creates a temporary table `actor_sumary__dbt_tmp`. Rows that have changed are streamed into this table.
2. A `DELETE` is issued against the current `actor_summary` table. Rows are deleted by id from `actor_sumary__dbt_tmp`
3. The rows from `actor_sumary__dbt_tmp` are inserted into `actor_summary` using an `INSERT INTO actor_summary SELECT * FROM actor_sumary__dbt_tmp`.

This process is shown below:

### insert\_overwrite mode (experimental)[​](#insert_overwrite-mode-experimental "Direct link to insert_overwrite mode (experimental)")

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 isn't modified.
- It implements "partitions immutability" data engineering best practice. Which simplifies incremental and parallel data processing, rollbacks, etc.

## Creating a snapshot[​](#creating-a-snapshot "Direct link to Creating a snapshot")

dbt snapshots allow a record to be made of changes to a mutable model over time. This in turn allows point-in-time queries on models, where analysts can "look back in time" at the previous state of a model. This is achieved using [type-2 Slowly Changing Dimensions](https://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2:_add_new_row) where from and to date columns record when a row was valid. This functionality is supported by the ClickHouse adapter and is demonstrated below.

This example assumes you have completed [Creating an Incremental Table Model](#creating-an-incremental-materialization). Make sure your actor\_summary.sql doesn't set inserts\_only=True. Your models/actor\_summary.sql should look like this:

```
{{ config(order_by='(updated_at, id, name)', engine='MergeTree()', materialized='incremental', unique_key='id') }}

with actor_summary as (
    SELECT id,
        any(actor_name) as name,
        uniqExact(movie_id)    as num_movies,
        avg(rank)                as avg_rank,
        uniqExact(genre)         as genres,
        uniqExact(director_name) as directors,
        max(created_at) as updated_at
    FROM (
        SELECT {{ source('imdb', 'actors') }}.id as id,
            concat({{ source('imdb', 'actors') }}.first_name, ' ', {{ source('imdb', 'actors') }}.last_name) as actor_name,
            {{ source('imdb', 'movies') }}.id as movie_id,
            {{ source('imdb', 'movies') }}.rank as rank,
            genre,
            concat({{ source('imdb', 'directors') }}.first_name, ' ', {{ source('imdb', 'directors') }}.last_name) as director_name,
            created_at
    FROM {{ source('imdb', 'actors') }}
        JOIN {{ source('imdb', 'roles') }} ON {{ source('imdb', 'roles') }}.actor_id = {{ source('imdb', 'actors') }}.id
        LEFT OUTER JOIN {{ source('imdb', 'movies') }} ON {{ source('imdb', 'movies') }}.id = {{ source('imdb', 'roles') }}.movie_id
        LEFT OUTER JOIN {{ source('imdb', 'genres') }} ON {{ source('imdb', 'genres') }}.movie_id = {{ source('imdb', 'movies') }}.id
        LEFT OUTER JOIN {{ source('imdb', 'movie_directors') }} ON {{ source('imdb', 'movie_directors') }}.movie_id = {{ source('imdb', 'movies') }}.id
        LEFT OUTER JOIN {{ source('imdb', 'directors') }} ON {{ source('imdb', 'directors') }}.id = {{ source('imdb', 'movie_directors') }}.director_id
    )
    GROUP BY id
)
select *
from actor_summary

{% if is_incremental() %}

-- this filter will only be applied on an incremental run
where id > (select max(id) from {{ this }}) or updated_at > (select max(updated_at) from {{this}})

{% endif %}
```

1. Create a file `actor_summary` in the snapshots directory.

   ```
    touch snapshots/actor_summary.sql
   ```
2. Update the contents of the actor\_summary.sql file with the following content:

   ```
   {% snapshot actor_summary_snapshot %}

   {{
   config(
   target_schema='snapshots',
   unique_key='id',
   strategy='timestamp',
   updated_at='updated_at',
   )
   }}

   select * from {{ref('actor_summary')}}

   {% endsnapshot %}
   ```

A few observations regarding this content:

- The select query defines the results you wish to snapshot over time. The function ref is used to reference our previously created actor\_summary model.
- We require a timestamp column to indicate record changes. Our updated\_at column (see [Creating an Incremental Table Model](#creating-an-incremental-materialization)) can be used here. The parameter strategy indicates our use of a timestamp to denote updates, with the parameter updated\_at specifying the column to use. If this isn't present in your model you can alternatively use the [check strategy](https://docs.getdbt.com/docs/building-a-dbt-project/snapshots#check-strategy). This is significantly more inefficient and requires the user to specify a list of columns to compare. dbt compares the current and historical values of these columns, recording any changes (or doing nothing if identical).

3. Run the command `dbt snapshot`.

   ```
   clickhouse-user@clickhouse:~/imdb$ dbt snapshot
   13:26:23  Running with dbt=1.1.0
   13:26:23  Found 1 model, 0 tests, 1 snapshot, 0 analyses, 181 macros, 0 operations, 0 seed files, 3 sources, 0 exposures, 0 metrics
   13:26:23
   13:26:25  Concurrency: 1 threads (target='dev')
   13:26:25
   13:26:25  1 of 1 START snapshot snapshots.actor_summary_snapshot...................... [RUN]
   13:26:25  1 of 1 OK snapshotted snapshots.actor_summary_snapshot...................... [OK in 0.79s]
   13:26:25
   13:26:25  Finished running 1 snapshot in 2.11s.
   13:26:25
   13:26:25  Completed successfully
   13:26:25
   13:26:25  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
   ```

Note how a table actor\_summary\_snapshot has been created in the snapshots db (determined by the target\_schema parameter).

4. Sampling this data you will see how dbt has included the columns dbt\_valid\_from and dbt\_valid\_to. The latter has values set to null. Subsequent runs will update this.

   ```
   SELECT id, name, num_movies, dbt_valid_from, dbt_valid_to FROM snapshots.actor_summary_snapshot ORDER BY num_movies DESC LIMIT 5;
   ```