Integrating dbt and ClickHouse

### Understanding long-running operations[​](#understanding-long-running-operations "Direct link to Understanding long-running operations")

Some operations may take longer than expected due to specific ClickHouse queries. To gain more insight into which queries are taking longer, increase the [log level](https://docs.getdbt.com/reference/global-configs/logs#log-level) to `debug` — this will print the time used by each query. For example, this can be achieved by appending `--log-level debug` to dbt commands.

## Limitations[​](#limitations "Direct link to Limitations")

The current ClickHouse adapter for dbt has several limitations you should be aware of:

- The plugin uses syntax that requires ClickHouse version 25.3 or newer. We don't test older versions of Clickhouse. We also don't currently test Replicated tables.
- Different runs of the `dbt-adapter` may collide if they're run at the same time as internally they can use the same table names for the same operations. For more information, check the issue [#420](https://github.com/ClickHouse/dbt-clickhouse/issues/420).
- The adapter currently materializes models as tables using an [INSERT INTO SELECT](https://clickhouse.com/docs/sql-reference/statements/insert-into#inserting-the-results-of-select). This effectively means data duplication if the run is executed again. Very large datasets (PB) can result in extremely long run times, making some models unviable. To improve performance, use ClickHouse Materialized Views by implementing the view as `materialized: materialization_view`. Additionally, aim to minimize the number of rows returned by any query by utilizing `GROUP BY` where possible. Prefer models that summarize data over those that simply transform while maintaining row counts of the source.
- To use Distributed tables to represent a model, you must create the underlying replicated tables on each node manually. The Distributed table can, in turn, be created on top of these. The adapter doesn't manage cluster creation.
- When dbt creates a relation (table/view) in a database, it usually creates it as: `{{ database }}.{{ schema }}.{{ table/view id }}`. ClickHouse has no notion of schemas. The adapter therefore uses `{{schema}}.{{ table/view id }}`, where `schema` is the ClickHouse database.
- Ephemeral models/CTEs don't work if placed before the `INSERT INTO` in a ClickHouse insert statement, see <https://github.com/ClickHouse/ClickHouse/issues/30323>. This shouldn't affect most models, but care should be taken where an ephemeral model is placed in model definitions and other SQL statements.

## Fivetran[​](#fivetran "Direct link to Fivetran")

The `dbt-clickhouse` connector is also available for use in [Fivetran transformations](https://fivetran.com/docs/transformations/dbt), allowing seamless integration and transformation capabilities directly within the Fivetran platform using `dbt`.

- [The dbt-clickhouse Adapter](#dbt-clickhouse-adapter)- [Related pages](#related-pages)- [Supported features](#supported-features)- [dbt concepts and supported materializations](#concepts-and-supported-materializations)- [Setup of dbt and the ClickHouse adapter](#setup-of-dbt-and-the-clickhouse-adapter)
          - [Install dbt-core and dbt-clickhouse](#install-dbt-core-and-dbt-clickhouse)- [Provide dbt with the connection details for our ClickHouse instance.](#provide-dbt-with-the-connection-details-for-our-clickhouse-instance)- [Create a dbt project](#create-a-dbt-project)- [Test connection](#test-connection)- [Testing and Deploying your models (CI/CD)](#testing-and-deploying-your-models-ci-cd)- [Troubleshooting common issues](#troubleshooting-common-issues)
            - [Connections](#troubleshooting-connections)- [Understanding long-running operations](#understanding-long-running-operations)- [Limitations](#limitations)- [Fivetran](#fivetran)

Was this page helpful?