Materializations

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

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:

Supported table engines

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

Experimental supported table engines

If you encounter issues connecting to ClickHouse from dbt with one of the above engines, please report an issue here.

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

NOTE: The column configuration options below require model contracts to be enforced.

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

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

A dbt model can be created as a ClickHouse 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

A dbt model can be created as a ClickHouse table 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

You can add data 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

You can add projections to table and distributed_table materializations using the projections configuration. Each projection entry requires either a query or an index key (not both).

Note: For distributed tables, the projection is applied to the _local tables, not to the distributed proxy table. Note: Specifying both query and index on the same projection entry raises a compile-time error.

Query projections

Use query to define a full projection query:

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

Index projections

Use index as syntax sugar for lightweight index projections that use the _part_offset virtual column. Pass a single column name or a list of columns to order by:

{{ config(
       materialized='table',
       projections=[
           {
               'name': 'proj_by_age',
               'index': 'age'
           }
       ]
) }}

{{ config(
       materialized='table',
       projections=[
           {
               'name': 'proj_by_dept_age',
               'index': ['department', 'age']
           }
       ]
) }}

dbt-clickhouse generates version-appropriate DDL automatically:

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

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

Incremental Model Strategies

dbt-clickhouse supports three incremental model strategies.

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

The delete+insert strategy uses lightweight deletes to remove the affected rows and then inserts the new ones. Because it doesn’t copy the whole table, it performs significantly better than the “legacy” strategy. Setting use_lw_deletes: true in your profile makes delete+insert the default incremental strategy.

There are important caveats to using this strategy:

• It operates directly on the affected table without 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.
• It requires the ClickHouse setting allow_nondeterministic_mutations. The adapter enables it automatically for its own sessions when possible. When it can’t be enabled (for example, it’s read-only for your dbt user), the behavior depends on how the strategy was chosen: models relying on the default strategy silently fall back to the legacy strategy, models that explicitly set delete+insert or microbatch fail at runtime, and use_lw_deletes: true in the profile fails at connection time.
• In some very rare cases, using non-deterministic incremental_predicates could result in a race condition for the updated/deleted items. 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)

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.
• Auto-detect parallel batch execution.
• Eliminate the need for complex conditional logic in backfilling.

For detailed microbatch usage, refer to the official documentation.

Available Microbatch Configurations

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)

[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

The materialized_view materialization creates a ClickHouse 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 for the full documentation

Materialization: dictionary (experimental)

A dbt model can be created as a ClickHouse dictionary. On every dbt run the dictionary is replaced with the current model definition using CREATE OR REPLACE DICTIONARY.

Configurations

Example with a ClickHouse source

The model’s SQL becomes the dictionary source query:

{{ config(
       materialized='dictionary',
       fields=[
           ('id', 'UInt64'),
           ('name', 'String'),
       ],
       primary_key='id',
       layout='HASHED()',
       lifetime='MIN 0 MAX 300'
) }}

select id, name from {{ source('raw', 'people') }}

Example with an HTTP source

When using source_type='http' (or the table option), the model’s SQL is not used as the source, but dbt still requires a body — use a select 1 placeholder:

{{ config(
       materialized='dictionary',
       fields=[
           ('LocationID', 'UInt16 DEFAULT 0'),
           ('Borough', 'String'),
           ('Zone', 'String'),
       ],
       primary_key='LocationID',
       layout='HASHED()',
       lifetime='MIN 0 MAX 0',
       source_type='http',
       url='https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv',
       format='CSVWithNames'
) }}

select 1

See the dictionary tests for more examples, including range and direct dictionaries.

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

{{
    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

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

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

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

{{
    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

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

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 reference page.

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