Features and Configurations

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

ClickHouse Supported

In this section, we provide documentation about some of the features available for dbt with ClickHouse.

- [Profile.yml configurations](#profile-yml-configurations)
  - [Schema vs Database](#schema-vs-database)- [SET Statement Warning](#set-statement-warning)- [Setting `quote_columns`](#setting-quote_columns)- [About the ClickHouse Cluster](#about-the-clickhouse-cluster)- [Additional ClickHouse macros](#additional-clickhouse-macros)
    - [Model materialization utility macros](#model-materialization-utility-macros)- [s3Source helper macro](#s3source-helper-macro)- [Cross database macro support](#cross-database-macro-support)- [Catalog Support](#catalog-support)
      - [dbt Catalog Integration Status](#dbt-catalog-integration-status)- [ClickHouse Catalog Support](#clickhouse-catalog-support)- [Workarounds for Working with Iceberg and Catalogs](#workarounds-iceberg-catalogs)- [Notes on the Workarounds](#benefits-workarounds)

## Profile.yml configurations[​](#profile-yml-configurations "Direct link to Profile.yml configurations")

To connect to ClickHouse from dbt, you'll need to add a [profile](https://docs.getdbt.com/docs/core/connect-data-platform/connection-profiles) to your `profiles.yml` file. A ClickHouse profile conforms to the following syntax:

```
your_profile_name:
  target: dev
  outputs:
    dev:
      type: clickhouse

      # Optional
      schema: [default] # ClickHouse database for dbt models
      driver: [http] # http or native.  If not set this will be autodetermined based on port setting
      host: [localhost] 
      port: [8123]  # If not set, defaults to 8123, 8443, 9000, 9440 depending on the secure and driver settings 
      user: [default] # User for all database operations
      password: [<empty string>] # Password for the user
      cluster: [<empty string>] # If set, certain DDL/table operations will be executed with the `ON CLUSTER` clause using this cluster. Distributed materializations require this setting to work. See the following ClickHouse Cluster section for more details.
      verify: [True] # Validate TLS certificate if using TLS/SSL
      secure: [False] # Use TLS (native protocol) or HTTPS (http protocol)
      client_cert: [null] # Path to a TLS client certificate in .pem format
      client_cert_key: [null] # Path to the private key for the TLS client certificate
      retries: [1] # Number of times to retry a "retriable" database exception (such as a 503 'Service Unavailable' error)
      compression: [<empty string>] # Use gzip compression if truthy (http), or compression type for a native connection
      connect_timeout: [10] # Timeout in seconds to establish a connection to ClickHouse
      send_receive_timeout: [300] # Timeout in seconds to receive data from the ClickHouse server
      cluster_mode: [False] # Use specific settings designed to improve operation on Replicated databases (recommended for ClickHouse Cloud)
      use_lw_deletes: [False] # Use the strategy `delete+insert` as the default incremental strategy.
      check_exchange: [True] # Validate that clickhouse support the atomic EXCHANGE TABLES command.  (Not needed for most ClickHouse versions)
      local_suffix: [_local] # Table suffix of local tables on shards for distributed materializations.
      local_db_prefix: [<empty string>] # Database prefix of local tables on shards for distributed materializations. If empty, it uses the same database as the distributed table.
      allow_automatic_deduplication: [False] # Enable ClickHouse automatic deduplication for Replicated tables
      tcp_keepalive: [False] # Native client only, specify TCP keepalive configuration. Specify custom keepalive settings as [idle_time_sec, interval_sec, probes].
      custom_settings: [{}] # A dictionary/mapping of custom ClickHouse settings for the connection - default is empty.
      database_engine: '' # Database engine to use when creating new ClickHouse schemas (databases).  If not set (the default), new databases will use the default ClickHouse database engine (usually Atomic).
      threads: [1] # Number of threads to use when running queries. Before setting it to a number higher than 1, make sure to read the [read-after-write consistency](#read-after-write-consistency) section.
      
      # Native (clickhouse-driver) connection settings
      sync_request_timeout: [5] # Timeout for server ping
      compress_block_size: [1048576] # Compression block size if compression is enabled
```

### Schema vs Database[​](#schema-vs-database "Direct link to Schema vs Database")

The dbt model relation identifier `database.schema.table` isn't compatible with Clickhouse because Clickhouse doesn't
support a `schema`.
So we use a simplified approach `schema.table`, where `schema` is the Clickhouse database. Using the `default` database
isn't recommended.

### SET Statement Warning[​](#set-statement-warning "Direct link to SET Statement Warning")

In many environments, using the SET statement to persist a ClickHouse setting across all DBT queries isn't reliable
and can cause unexpected failures. This is particularly true when using HTTP connections through a load balancer that
distributes queries across multiple nodes (such as ClickHouse cloud), although in some circumstances this can also
happen with native ClickHouse connections. Accordingly, we recommend configuring any required ClickHouse settings in the
"custom\_settings" property of the DBT profile as a best practice, instead of relying on a pre-hook "SET" statement as
has been occasionally suggested.

### Setting `quote_columns`[​](#setting-quote_columns "Direct link to setting-quote_columns")

To prevent a warning, make sure to explicitly set a value for `quote_columns` in your `dbt_project.yml`. See the [doc on quote\_columns](https://docs.getdbt.com/reference/resource-configs/quote_columns) for more information.

```
seeds:
  +quote_columns: false  #or `true` if you have CSV column headers with spaces
```

### About the ClickHouse Cluster[​](#about-the-clickhouse-cluster "Direct link to About the ClickHouse Cluster")

When using a ClickHouse cluster, you need to consider two things:

- Setting the `cluster` setting.
- Ensuring read-after-write consistency, especially if you're using more than one `threads`.

#### Cluster Setting[​](#cluster-setting "Direct link to Cluster Setting")

The `cluster` setting in profile enables dbt-clickhouse to run against a ClickHouse cluster. If `cluster` is set in the profile, **all models will be created with the `ON CLUSTER` clause** by default—except for those using a **Replicated** engine. This includes:

- Database creation
- View materializations
- Table and incremental materializations
- Distributed materializations

Replicated engines will **not** include the `ON CLUSTER` clause, as they're designed to manage replication internally.

To **opt out** of cluster-based creation for a specific model, add the `disable_on_cluster` config:

```
{{ config(
        engine='MergeTree',
        materialized='table',
        disable_on_cluster='true'
    )
}}
```

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: