Materializations

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

```
models:
  - name: table_column_configs
    description: 'Testing column-level configurations'
    config:
      contract:
        enforced: true
    columns:
      - name: ts
        data_type: timestamp
        codec: ZSTD
      - name: x
        data_type: UInt8
        ttl: ts + INTERVAL 1 DAY
```

#### Adding complex types[​](#adding-complex-types "Direct link to Adding complex types")

dbt automatically determines the data type of each column by analyzing the SQL used to create the model. However, in some cases this process may not accurately determine the data type, leading to conflicts with the types specified in the contract `data_type` property. To address this, we recommend using the `CAST()` function in the model SQL to explicitly define the desired type. For example:

```
{{
    config(
        materialized="materialized_view",
        engine="AggregatingMergeTree",
        order_by=["event_type"],
    )
}}

select
  -- event_type may be infered as a String but we may prefer LowCardinality(String):
  CAST(event_type, 'LowCardinality(String)') as event_type,
  -- countState() may be infered as `AggregateFunction(count)` but we may prefer to change the type of the argument used:
  CAST(countState(), 'AggregateFunction(count, UInt32)') as response_count, 
  -- maxSimpleState() may be infered as `SimpleAggregateFunction(max, String)` but we may prefer to also change the type of the argument used:
  CAST(maxSimpleState(event_type), 'SimpleAggregateFunction(max, LowCardinality(String))') as max_event_type
from {{ ref('user_events') }}
group by event_type
```

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

A dbt model can be created as a [ClickHouse view](/docs/sql-reference/table-functions/view)
and configured using the following syntax:

Project File (`dbt_project.yml`):

```
models:
  <resource-path>:
    +materialized: view
```

Or config block (`models/<model_name>.sql`):

```
{{ config(materialized = "view") }}
```

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

A dbt model can be created as a [ClickHouse table](/docs/operations/system-tables/tables) and
configured using the following syntax:

Project File (`dbt_project.yml`):

```
models:
  <resource-path>:
    +materialized: table
    +order_by: [ <column-name>, ... ]
    +engine: <engine-type>
    +partition_by: [ <column-name>, ... ]
```

Or config block (`models/<model_name>.sql`):

```
{{ config(
    materialized = "table",
    engine = "<engine-type>",
    order_by = [ "<column-name>", ... ],
    partition_by = [ "<column-name>", ... ],
      ...
    ]
) }}
```

### Data skipping indexes[​](#data-skipping-indexes "Direct link to Data skipping indexes")

You can add [data skipping indexes](/docs/optimize/skipping-indexes) to `table` materializations using the `indexes` configuration:

```
{{ config(
        materialized='table',
        indexes=[{
          'name': 'your_index_name',
          'definition': 'your_column TYPE minmax GRANULARITY 2'
        }]
) }}
```

### Projections[​](#projections "Direct link to Projections")

You can add [projections](/docs/data-modeling/projections) to `table` and `distributed_table` materializations using the `projections` configuration:

```
{{ config(
       materialized='table',
       projections=[
           {
               'name': 'your_projection_name',
               'query': 'SELECT department, avg(age) AS avg_age GROUP BY department'
           }
       ]
) }}
```

**Note**: For distributed tables, the projection is applied to the `_local` tables, not to the distributed proxy table.

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

Table model will be reconstructed for each dbt execution. This may be infeasible and extremely costly for larger result sets or complex transformations. To address this challenge and reduce the build time, a dbt model can be created as an incremental ClickHouse table and is configured using the following syntax:

Model definition in `dbt_project.yml`:

```
models:
  <resource-path>:
    +materialized: incremental
    +order_by: [ <column-name>, ... ]
    +engine: <engine-type>
    +partition_by: [ <column-name>, ... ]
    +unique_key: [ <column-name>, ... ]
    +inserts_only: [ True|False ]
```

Or config block in `models/<model_name>.sql`:

```
{{ config(
    materialized = "incremental",
    engine = "<engine-type>",
    order_by = [ "<column-name>", ... ],
    partition_by = [ "<column-name>", ... ],
    unique_key = [ "<column-name>", ... ],
    inserts_only = [ True|False ],
      ...
    ]
) }}
```

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

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

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Option Description Required?|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `unique_key` A tuple of column names that uniquely identify rows. For more details on uniqueness constraints, see [here](https://docs.getdbt.com/docs/build/incremental-models#defining-a-unique-key-optional). Required. If not provided altered rows will be added twice to the incremental table.|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `inserts_only` It has been deprecated in favor of the `append` incremental `strategy`, which operates in the same way. If set to True for an incremental model, incremental updates will be inserted directly to the target table without creating intermediate table. . If `inserts_only` is set, `incremental_strategy` is ignored. Optional (default: `False`)| `incremental_strategy` The strategy to use for incremental materialization. `delete+insert`, `append`, `insert_overwrite`, or `microbatch` are supported. For additional details on strategies, see [here](#incremental-model-strategies) Optional (default: 'default')|  |  |  | | --- | --- | --- | | `incremental_predicates` Additional conditions to be applied to the incremental materialization (only applied to `delete+insert` strategy Optional | | | | | | | | | | | | | | |

### Incremental Model Strategies[​](#incremental-model-strategies "Direct link to Incremental Model Strategies")

`dbt-clickhouse` supports three incremental model strategies.

#### The Default (Legacy) Strategy[​](#default-legacy-strategy "Direct link to The Default (Legacy) Strategy")

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