How to Connect ClickHouse to dbt in PostgreSQL

Galaxy Glossary

How do I connect ClickHouse to dbt?

Connect ClickHouse to dbt by installing the dbt-clickhouse adapter and adding a ClickHouse profile in profiles.yml.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What prerequisites are required?

Install Python ≥3.8, dbt-core ≥1.5, and have network access to your ClickHouse server. Confirm you can log in with the native client or HTTP interface.

How do I install the ClickHouse adapter?

Run pip install dbt-clickhouse. The command adds the adapter, dependencies, and a new clickhouse target type to dbt.

What profile settings connect ClickHouse?

Create ~/.dbt/profiles.yml and add a type: clickhouse output with host, port, user, password, database, schema, and time-outs.Each key matches a ClickHouse client argument.

Can I use environment variables?

Yes. Wrap any sensitive value in {{ env_var('VAR_NAME') }} to avoid committing credentials.

How do I test the connection?

Run dbt debug --target dev. A green success message confirms dbt can open and close a ClickHouse session.

How do I run models?

Place models in /models, reference tables like Orders and OrderItems, then execute dbt run.dbt creates or replaces materialized views in ClickHouse.

Example: daily revenue model

SELECT order_date, sum(total_amount) AS daily_revenue FROM {{ source('public','Orders') }} GROUP BY order_date;

Best practices for ClickHouse + dbt

Partition large tables by date, set materialized_view configs for fast refresh, and limit threads to ClickHouse’s CPU cores.

What mistakes should I avoid?

Using the HTTP port 8123 instead of the native port 9000 causes TLS errors.Quoting case-sensitive identifiers forces full table scans.

Next steps

Add tests (unique, not_null) and schedule dbt run in CI/CD to keep ClickHouse models current.

Why How to Connect ClickHouse to dbt in PostgreSQL is important

How to Connect ClickHouse to dbt in PostgreSQL Example Usage


-- models/customer_lifetime_value.sql
SELECT  c.id                     AS customer_id,
        c.name                   AS customer_name,
        sum(o.total_amount)      AS lifetime_value
FROM     c
JOIN        o ON o.customer_id = c.id
GROUP BY customer_id, customer_name;

How to Connect ClickHouse to dbt in PostgreSQL Syntax


profiles.yml
clickhouse_dbt:
  target: dev
  outputs:
    dev:
      type: clickhouse
      host: localhost
      port: 9000          # native port, not HTTP 8123
      user: default
      password: ""
      database: myshop
      schema: analytics
      threads: 4
      connect_timeout: 10
      read_timeout: 300

CLI commands
# validate profile
$ dbt debug --target dev
# materialize models
$ dbt run --select orders_*

Common Mistakes

Using port 8123 instead of 9000. The HTTP port works for curl but the dbt adapter expects the native TCP port. Switch the port and rerun <code>dbt debug</code>.
Quoting identifiers like "Orders" in models. ClickHouse stores names in lowercase; quoting creates case-sensitive objects that break joins. Remove quotes or use lowercase.