Features and Configurations

table and incremental materializations with non-replicated engine won't be affected by `cluster` setting (model would
be created on the connected node only).

**Compatibility**

If a model has been created without a `cluster` setting, dbt-clickhouse will detect the situation and run all DDL/DML
without `on cluster` clause for this model.

#### Read-after-write Consistency[​](#read-after-write-consistency "Direct link to Read-after-write Consistency")

dbt relies on a read-after-insert consistency model. This isn't compatible with ClickHouse clusters that have more than one replica if you can't guarantee that all operations will go to the same replica. You may not encounter problems in your day-to-day usage of dbt, but there are some strategies depending on your cluster to have this guarantee in place:

- If you're using a ClickHouse Cloud cluster, you only need to set `select_sequential_consistency: 1` in your profile's `custom_settings` property. You can find more information about this setting [here](/docs/operations/settings/settings#select_sequential_consistency).
- If you're using a self-hosted cluster, make sure all dbt requests are sent to the same ClickHouse replica. If you have a load balancer on top of it, try using some `replica aware routing`/`sticky sessions` mechanism to be able to always reach the same replica. Adding the setting `select_sequential_consistency = 1` in clusters outside ClickHouse Cloud is [not recommended](/docs/operations/settings/settings#select_sequential_consistency).

## Additional ClickHouse macros[​](#additional-clickhouse-macros "Direct link to Additional ClickHouse macros")

### Model materialization utility macros[​](#model-materialization-utility-macros "Direct link to Model materialization utility macros")

The following macros are included to facilitate creating ClickHouse specific tables and views:

- `engine_clause` -- Uses the `engine` model configuration property to assign a ClickHouse table engine. dbt-clickhouse
  uses the `MergeTree` engine by default.
- `partition_cols` -- Uses the `partition_by` model configuration property to assign a ClickHouse partition key. No
  partition key is assigned by default.
- `order_cols` -- Uses the `order_by` model configuration to assign a ClickHouse order by/sorting key. If not specified
  ClickHouse will use an empty tuple() and the table will be unsorted
- `primary_key_clause` -- Uses the `primary_key` model configuration property to assign a ClickHouse primary key. By
  default, primary key is set and ClickHouse will use the order by clause as the primary key.
- `on_cluster_clause` -- Uses the `cluster` profile property to add an `ON CLUSTER` clause to certain dbt-operations:
  distributed materializations, views creation, database creation.
- `ttl_config` -- Uses the `ttl` model configuration property to assign a ClickHouse table TTL expression. No TTL is
  assigned by default.

### s3Source helper macro[​](#s3source-helper-macro "Direct link to s3Source helper macro")

The `s3source` macro simplifies the process of selecting ClickHouse data directly from S3 using the ClickHouse S3 table
function. It works by
populating the S3 table function parameters from a named configuration dictionary (the name of the dictionary must end
in `s3`). The macro
first looks for the dictionary in the profile `vars`, and then in the model configuration. The dictionary can contain
any of the following
keys used to populate the parameters of the S3 table function:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Argument Name Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | bucket The bucket base url, such as `https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi`. `https://` is assumed if no protocol is provided.| path The S3 path to use for the table query, such as `/trips_4.gz`. S3 wildcards are supported.| fmt The expected ClickHouse input format (such as `TSV` or `CSVWithNames`) of the referenced S3 objects.| structure The column structure of the data in bucket, as a list of name/datatype pairs, such as `['id UInt32', 'date DateTime', 'value String']` If not provided ClickHouse will infer the structure.| aws\_access\_key\_id The S3 access key id.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | aws\_secret\_access\_key The S3 secret key.|  |  |  |  | | --- | --- | --- | --- | | role\_arn The ARN of a ClickhouseAccess IAM role to use to securely access the S3 objects. See this [documentation](/docs/cloud/data-sources/secure-s3) for more information.| compression The compression method used with the S3 objects. If not provided ClickHouse will attempt to determine compression based on the file name. | | | | | | | | | | | | | | | | | |

See
the [S3 test file](https://github.com/ClickHouse/dbt-clickhouse/blob/main/tests/integration/adapter/clickhouse/test_clickhouse_s3.py)
for examples of how to use this macro.

### Cross database macro support[​](#cross-database-macro-support "Direct link to Cross database macro support")

dbt-clickhouse supports most of the cross database macros now included in `dbt Core` with the following exceptions:

- The `split_part` SQL function is implemented in ClickHouse using the splitByChar function. This function requires
  using a constant string for the "split" delimiter, so the `delimeter` parameter used for this macro will be
  interpreted as a string, not a column name
- Similarly, the `replace` SQL function in ClickHouse requires constant strings for the `old_chars` and `new_chars`
  parameters, so those parameters will be interpreted as strings rather than column names when invoking this macro.

## Catalog Support[​](#catalog-support "Direct link to Catalog Support")

### dbt Catalog Integration Status[​](#dbt-catalog-integration-status "Direct link to dbt Catalog Integration Status")

dbt Core v1.10 introduced catalog integration support, which allows adapters to materialize models into external catalogs that manage open table formats like Apache Iceberg. **This feature isn't yet natively implemented in dbt-clickhouse.** You can track the progress of this feature implementation in [GitHub issue #489](https://github.com/ClickHouse/dbt-clickhouse/issues/489).

### ClickHouse Catalog Support[​](#clickhouse-catalog-support "Direct link to ClickHouse Catalog Support")

ClickHouse recently added native support for Apache Iceberg tables and data catalogs. Most of the features are still `experimental`, but you can already use them if you use a recent ClickHouse version.

- You can use ClickHouse to **query Iceberg tables stored in object storage** (S3, Azure Blob Storage, Google Cloud Storage) using the [Iceberg table engine](/docs/engines/table-engines/integrations/iceberg) and [iceberg table function](/docs/sql-reference/table-functions/iceberg).
- Additionally, ClickHouse provides the [DataLakeCatalog database engine](/docs/engines/database-engines/datalakecatalog), which enables **connection to external data catalogs** including AWS Glue Catalog, Databricks Unity Catalog, Hive Metastore, and REST Catalogs. This allows you to query open table format data (Iceberg, Delta Lake) directly from external catalogs without data duplication.

### Workarounds for Working with Iceberg and Catalogs[​](#workarounds-iceberg-catalogs "Direct link to Workarounds for Working with Iceberg and Catalogs")

You can read data from Iceberg tables or catalogs from your dbt project if you have already defined them in your ClickHouse cluster with the tools defined above. You can leverage the `source` functionality in dbt to reference these tables in your dbt projects. For example, if you want to access your tables in a REST Catalog, you can:

1. **Create a database pointing to an external catalog:**

```
-- Example with REST Catalog
SET allow_experimental_database_iceberg = 1;