Materializations

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/etl-tools/dbt/materializations.md)

ClickHouse Supported

This section covers all the materializations available in dbt-clickhouse, including experimental features.

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

## General materialization configurations[​](#general-materialization-configurations "Direct link to General materialization configurations")

The following table shows configurations shared by some of the available materializations. For in-depth information about general dbt model configurations, see the [dbt documentation](https://docs.getdbt.com/category/general-configs):

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Option Description Default if any|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | engine The table engine (type of table) to use when creating tables `MergeTree()`| order\_by A tuple of column names or arbitrary expressions. This allows you to create a small sparse index that helps find data faster. `tuple()`| partition\_by A partition is a logical combination of records in a table by a specified criterion. The partition key can be any expression from the table columns. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | primary\_key Like order\_by, a ClickHouse primary key expression. If not specified, ClickHouse will use the order by expression as the primary key |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | settings A map/dictionary of "TABLE" settings to be used to DDL statements like 'CREATE TABLE' with this model |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | query\_settings A map/dictionary of ClickHouse user level settings to be used with `INSERT` or `DELETE` statements in conjunction with this model |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | ttl A TTL expression to be used with the table. The TTL expression is a string that can be used to specify the TTL for the table. |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | sql\_security The ClickHouse user to use when executing the view's underlying query. [Accepted values](/docs/sql-reference/statements/create/view#sql_security): `definer`, `invoker`. |  |  |  | | --- | --- | --- | | definer If `sql_security` was set to `definer`, you have to specify any existing user or `CURRENT_USER` in the `definer` clause.  | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

### Supported table engines[​](#supported-table-engines "Direct link to Supported table engines")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Type Details|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | MergeTree (default) <https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/>.| HDFS <https://clickhouse.com/docs/en/engines/table-engines/integrations/hdfs>| MaterializedPostgreSQL <https://clickhouse.com/docs/en/engines/table-engines/integrations/materialized-postgresql>| S3 <https://clickhouse.com/docs/en/engines/table-engines/integrations/s3>| EmbeddedRocksDB <https://clickhouse.com/docs/en/engines/table-engines/integrations/embedded-rocksdb>| Hive <https://clickhouse.com/docs/en/engines/table-engines/integrations/hive> | | | | | | | | | | | | | |

**Note**: for materialized views, all \*MergeTree engines are supported.

#### Experimental supported table engines[​](#experimental-supported-table-engines "Direct link to Experimental supported table engines")

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Type Details|  |  |  |  | | --- | --- | --- | --- | | Distributed Table <https://clickhouse.com/docs/en/engines/table-engines/special/distributed>.| Dictionary <https://clickhouse.com/docs/en/engines/table-engines/special/dictionary> | | | | | |

If you encounter issues connecting to ClickHouse from dbt with one of the above engines, please report an
issue [here](https://github.com/ClickHouse/dbt-clickhouse/issues).

### A note on model settings[​](#a-note-on-model-settings "Direct link to A note on model settings")

ClickHouse has several types/levels of "settings". In the model configuration above, two types of these are
configurable. `settings` means the `SETTINGS`
clause used in `CREATE TABLE/VIEW` types of DDL statements, so this is generally settings that are specific to the
specific ClickHouse table engine. The new
`query_settings` is use to add a `SETTINGS` clause to the `INSERT` and `DELETE` queries used for model materialization (
including incremental materializations).
There are hundreds of ClickHouse settings, and it's not always clear which is a "table" setting and which is a "user"
setting (although the latter are generally
available in the `system.settings` table.) In general the defaults are recommended, and any use of these properties
should be carefully researched and tested.

### Column Configuration[​](#column-configuration "Direct link to Column Configuration")

> ***NOTE:*** The column configuration options below require [model contracts](https://docs.getdbt.com/docs/collaborate/govern/model-contracts) to be enforced.

|  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Option Description Default if any|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | codec A string consisting of arguments passed to `CODEC()` in the column's DDL. For example: `codec: "Delta, ZSTD"` will be compiled as `CODEC(Delta, ZSTD)`. |  |  |  | | --- | --- | --- | | ttl A string consisting of a [TTL (time-to-live) expression](https://clickhouse.com/docs/guides/developer/ttl) that defines a TTL rule in the column's DDL. For example: `ttl: ts + INTERVAL 1 DAY` will be compiled as `TTL ts + INTERVAL 1 DAY`.  | | | | | | | | |

#### Example of schema configuration[​](#example-of-schema-configuration "Direct link to Example of schema configuration")