Integrating dbt and ClickHouse

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

ClickHouse Supported

## The dbt-clickhouse Adapter[​](#dbt-clickhouse-adapter "Direct link to The dbt-clickhouse Adapter")

**dbt** (data build tool) enables analytics engineers to transform data in their warehouses by simply writing select statements. dbt handles materializing these select statements into objects in the database in the form of tables and views - performing the T of [Extract Load and Transform (ELT)](https://en.wikipedia.org/wiki/Extract,_load,_transform). You can create a model defined by a SELECT statement.

Within dbt, these models can be cross-referenced and layered to allow the construction of higher-level concepts. The boilerplate SQL required to connect models is automatically generated. Furthermore, dbt identifies dependencies between models and ensures they're created in the appropriate order using a directed acyclic graph (DAG).

dbt is compatible with ClickHouse through a [ClickHouse-supported adapter](https://github.com/ClickHouse/dbt-clickhouse).

## Related pages[​](#related-pages "Direct link to Related pages")

|  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Page Description|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | [Features and configurations](/docs/integrations/dbt/features-and-configurations) Description of the features and general configurations available|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [Materializations](/docs/integrations/dbt/materializations) Materializations available and their configurations|  |  |  |  | | --- | --- | --- | --- | | [Guides](/docs/integrations/dbt/guides) Guides for using dbt with ClickHouse|  |  | | --- | --- | | [Materialization: materialized\_view](/docs/integrations/dbt/materialization-materialized-view) Specific documentation for the materialized\_view materialization | | | | | | | | | |

## Supported features[​](#supported-features "Direct link to Supported features")

List of supported features:

- Table materialization
- View materialization
- Incremental materialization
- Microbatch incremental materialization
- Materialized View materializations (uses the `TO` form of MATERIALIZED VIEW, experimental)
- Seeds
- Sources
- Docs generate
- Tests
- Snapshots
- Most dbt-utils macros (now included in dbt-core)
- Ephemeral materialization
- Distributed table materialization (experimental)
- Distributed incremental materialization (experimental)
- Contracts
- ClickHouse-specific column configurations (Codec, TTL...)
- ClickHouse-specific table settings (indexes, projections...)

All features up to dbt-core 1.10 are supported, including `--sample` flag and all deprecation warnings fixed for future releases. **Catalog integrations** (e.g., Iceberg) introduced in dbt 1.10 aren't yet natively supported in the adapter, but workarounds are available. See the [Catalog Support section](/docs/integrations/dbt/features-and-configurations#catalog-support) for details.

This adapter is still not available for use inside [dbt Cloud](https://docs.getdbt.com/docs/dbt-cloud/cloud-overview), but we expect to make it available soon. Please reach out to support to get more information on this.

## dbt concepts and supported materializations[​](#concepts-and-supported-materializations "Direct link to dbt concepts and supported materializations")

dbt introduces the concept of a model. This is defined as a SQL statement, potentially joining many tables. A model can be "materialized" in a number of ways. A materialization represents a build strategy for the model's select query. The code behind a materialization is boilerplate SQL that wraps your SELECT query in a statement in order to create a new or update an existing relation.

dbt provides 5 types of materialization. All of them are supported by `dbt-clickhouse`:

- **view** (default): The model is built as a view in the database. At ClickHouse this is built as a [view](/docs/sql-reference/statements/create/view).
- **table**: The model is built as a table in the database. At ClickHouse this is built as a [table](/docs/sql-reference/statements/create/table).
- **ephemeral**: The model isn't directly built in the database but is instead pulled into dependent models as CTEs (Common Table Expressions).
- **incremental**: The model is initially materialized as a table, and in subsequent runs, dbt inserts new rows and updates changed rows in the table.
- **materialized view**: The model is built as a materialized view in the database. At ClickHouse this is built as a [materialized view](/docs/sql-reference/statements/create/view#materialized-view).

Additional syntax and clauses define how these models should be updated if their underlying data changes. dbt generally recommends starting with the view materialization until performance becomes a concern. The table materialization provides a query time performance improvement by capturing the results of the model's query as a table at the expense of increased storage. The incremental approach builds on this further to allow subsequent updates to the underlying data to be captured in the target table.

The [current adapter](https://github.com/silentsokolov/dbt-clickhouse) for ClickHouse supports also support **dictionary**, **distributed table** and **distributed incremental** materializations. The adapter also supports dbt [snapshots](https://docs.getdbt.com/docs/building-a-dbt-project/snapshots#check-strategy) and [seeds](https://docs.getdbt.com/docs/building-a-dbt-project/seeds).

The following are [experimental features](https://clickhouse.com/docs/en/beta-and-experimental-features) in `dbt-clickhouse`:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Type Supported? Details|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Materialized View materialization Yes. Creation with explicit target is in Beta Creates a [materialized view](/docs/sql-reference/statements/create/view#materialized-view).| Distributed table materialization Yes, Experimental Creates a [distributed table](/docs/engines/table-engines/special/distributed).| Distributed incremental materialization Yes, Experimental Incremental model based on the same idea as distributed table. Note that not all strategies are supported, visit [their docs section](/docs/integrations/dbt/materializations#materialization-distributed-incremental) for more info.| Dictionary materialization Yes, Experimental Creates a [dictionary](/docs/engines/table-engines/special/dictionary). | | | | | | | | | | | | | | |

## Setup of dbt and the ClickHouse adapter[​](#setup-of-dbt-and-the-clickhouse-adapter "Direct link to Setup of dbt and the ClickHouse adapter")

### Install dbt-core and dbt-clickhouse[​](#install-dbt-core-and-dbt-clickhouse "Direct link to Install dbt-core and dbt-clickhouse")

dbt provides several options for installing the command-line interface (CLI), which are detailed [here](https://docs.getdbt.com/dbt-cli/install/overview). We recommend using `pip` to install both dbt and dbt-clickhouse.

```
pip install dbt-core dbt-clickhouse
```

### Provide dbt with the connection details for our ClickHouse instance.[​](#provide-dbt-with-the-connection-details-for-our-clickhouse-instance "Direct link to Provide dbt with the connection details for our ClickHouse instance.")

Configure the `clickhouse-service` profile in the `~/.dbt/profiles.yml` file and provide the schema, host, port, user, and password properties. The full list of connection configuration options is available in the [Features and configurations](/docs/integrations/dbt/features-and-configurations) page:

```
clickhouse-service:
  target: dev
  outputs:
    dev:
      type: clickhouse
      schema: [ default ] # ClickHouse database for dbt models