Materializations

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:

1. Creates temp view with sql query to get right structure
2. Create empty local tables based on view
3. Create distributed table based on local tables.
4. Data inserts into distributed table, so it is distributed across shards without duplicating.

Notes:

- dbt-clickhouse queries now automatically include the setting `insert_distributed_sync = 1` in order to ensure that
  downstream incremental
  materialization operations execute correctly. This could cause some distributed table inserts to run more slowly than
  expected.

### Distributed table model example[​](#distributed-table-model-example "Direct link to Distributed table model example")

```
{{
    config(
        materialized='distributed_table',
        order_by='id, created_at',
        sharding_key='cityHash64(id)',
        engine='ReplacingMergeTree'
    )
}}

select id, created_at, item
from {{ source('db', 'table') }}
```

### Generated migrations[​](#distributed-table-generated-migrations "Direct link to Generated migrations")

```
CREATE TABLE db.table_local on cluster cluster (
    `id` UInt64,
    `created_at` DateTime,
    `item` String
)
    ENGINE = ReplacingMergeTree
    ORDER BY (id, created_at);

CREATE TABLE db.table on cluster cluster (
    `id` UInt64,
    `created_at` DateTime,
    `item` String
)
    ENGINE = Distributed ('cluster', 'db', 'table_local', cityHash64(id));
```

### Configurations[​](#distributed-table-configurations "Direct link to Configurations")

Configurations that are specific for this materialization type are listed below:

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Option Description Default if any|  |  |  | | --- | --- | --- | | sharding\_key Sharding key determines the destination server when inserting into distributed engine table. The sharding key can be random or as an output of a hash function `rand()`) | | | | | |

## materialization: distributed\_incremental (experimental)[​](#materialization-distributed-incremental "Direct link to materialization: distributed_incremental (experimental)")

Incremental model based on the same idea as distributed table, the main difficulty is to process all incremental
strategies correctly.

1. *The Append Strategy* just insert data into distributed table.
2. *The Delete+Insert* Strategy creates distributed temp table to work with all data on every shard.
3. *The Default (Legacy) Strategy* creates distributed temp and intermediate tables for the same reason.

Only shard tables are replacing, because distributed table does not keep data.
The distributed table reloads only when the full\_refresh mode is enabled or the table structure may have changed.

### Distributed incremental model example[​](#distributed-incremental-model-example "Direct link to Distributed incremental model example")

```
{{
    config(
        materialized='distributed_incremental',
        engine='MergeTree',
        incremental_strategy='append',
        unique_key='id,created_at'
    )
}}

select id, created_at, item
from {{ source('db', 'table') }}
```

### Generated migrations[​](#distributed-incremental-generated-migrations "Direct link to Generated migrations")

```
CREATE TABLE db.table_local on cluster cluster (
    `id` UInt64,
    `created_at` DateTime,
    `item` String
)
    ENGINE = MergeTree;

CREATE TABLE db.table on cluster cluster (
    `id` UInt64,
    `created_at` DateTime,
    `item` String
)
    ENGINE = Distributed ('cluster', 'db', 'table_local', cityHash64(id));
```

## Snapshot[​](#snapshot "Direct link to 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 functionality is
supported by the ClickHouse connector and is configured using the following syntax:

Config block in `snapshots/<model_name>.sql`:

```
{{
   config(
     schema = "<schema-name>",
     unique_key = "<column-name>",
     strategy = "<strategy>",
     updated_at = "<updated-at-column-name>",
   )
}}
```

For more information on configuration, check out the [snapshot configs](https://docs.getdbt.com/docs/build/snapshots#snapshot-configs) reference page.

## Contracts and Constraints[​](#contracts-and-constraints "Direct link to Contracts and Constraints")

Only exact column type contracts are supported. For example, a contract with a UInt32 column type will fail if the model
returns a UInt64 or other integer type.
ClickHouse also support *only* `CHECK` constraints on the entire table/model. Primary key, foreign key, unique, and
column level CHECK constraints are not supported.
(See ClickHouse documentation on primary/order by keys.)

- [General materialization configurations](#general-materialization-configurations)
  - [Supported table engines](#supported-table-engines)- [A note on model settings](#a-note-on-model-settings)- [Column Configuration](#column-configuration)- [Materialization: view](#materialization-view)- [Materialization: table](#materialization-table)
      - [Data skipping indexes](#data-skipping-indexes)- [Projections](#projections)- [Materialization: incremental](#materialization-incremental)
        - [Configurations](#incremental-configurations)- [Incremental Model Strategies](#incremental-model-strategies)- [Materialization: materialized\_view](#materialized-view)- [Materialization: dictionary (experimental)](#materialization-dictionary)- [Materialization: distributed\_table (experimental)](#materialization-distributed-table)
              - [Distributed table model example](#distributed-table-model-example)- [Generated migrations](#distributed-table-generated-migrations)- [Configurations](#distributed-table-configurations)- [materialization: distributed\_incremental (experimental)](#materialization-distributed-incremental)
                - [Distributed incremental model example](#distributed-incremental-model-example)- [Generated migrations](#distributed-incremental-generated-migrations)- [Snapshot](#snapshot)- [Contracts and Constraints](#contracts-and-constraints)

Was this page helpful?