--- title: "clickhouse integration dbt — 3 Ways to Connect in 2026" excerpt: "Run dbt models against ClickHouse® via the dbt-clickhouse adapter, ClickHouse® Cloud, or replace the transformation layer with Tinybird Pipes. Pick the right clickhouse integration dbt path." authors: "Tinybird" categories: "AI Resources" createdOn: "2026-05-02 00:00:00" publishedOn: "2026-05-02 00:00:00" updatedOn: "2026-05-02 00:00:00" status: "published" --- These are the main options for a **clickhouse integration dbt** setup: 1. dbt → ClickHouse® via **dbt-clickhouse community adapter** (self-managed or ClickHouse® Cloud) 2. dbt → ClickHouse® **Cloud** via the official dbt Cloud + ClickHouse® Cloud connection 3. dbt → **Tinybird** as the ClickHouse®-powered transformation and serving layer **dbt (data build tool)** is the most widely adopted **SQL-based transformation framework** for data teams. ClickHouse® is a columnar OLAP [database](https://www.oracle.com/database/what-is-database/) that handles billions of rows in sub-second queries. Connecting the two lets data engineers run `dbt run` against ClickHouse® tables, materialise models as ClickHouse® views and tables, and serve fast analytical queries downstream. A **clickhouse integration dbt** pipeline lets teams use familiar dbt concepts — models, refs, tests, seeds — while gaining ClickHouse®'s columnar performance for the transformation layer. This is especially valuable for teams whose dbt models run against BigQuery or Snowflake today but need sub-second query performance on large datasets at lower cost. Before you pick a path, consider these questions: - Do you need **full dbt feature parity** (tests, docs, snapshots, seeds) or primarily the model + materialisation workflow? - Is your ClickHouse® **self-managed**, or are you using **ClickHouse® Cloud**? - Do you also need to **expose transformed data as REST APIs** for applications, or is the output consumed only by BI tools? ## **Three ways to implement clickhouse integration dbt** This section covers the three main integration paths, with configuration and model code for each. ### **Option 1: dbt → ClickHouse® — dbt-clickhouse community adapter** The most direct approach. The **`dbt-clickhouse`** community adapter translates dbt's materialisation patterns (view, table, incremental) into ClickHouse®-native DDL and DML. Install it alongside dbt-core, configure a ClickHouse® profile, and run `dbt run` against your ClickHouse® instance. **How it works:** the adapter connects to ClickHouse® via the HTTP interface (port 8443 for TLS) using `clickhouse-connect`. Each dbt model compiles to a ClickHouse® `CREATE VIEW`, `CREATE TABLE AS SELECT`, or `INSERT INTO … SELECT` depending on materialisation. ClickHouse®-specific materialisation settings are passed via `config()` blocks. **Installation:** ```bash pip install dbt-clickhouse ``` **`profiles.yml` configuration:** ```yaml clickhouse_project: target: dev outputs: dev: type: clickhouse schema: analytics host: your-clickhouse-host port: 8443 user: dbt_user password: "{{ env_var('CLICKHOUSE_PASSWORD') }}" secure: true verify: true connect_timeout: 10 send_receive_timeout: 300 sync_request_timeout: 5 compress_block_size: 1048576 compression: "" database: analytics database_engine: Atomic cluster: "" ``` **dbt model — incremental materialisation with ClickHouse®-specific settings:** ```sql -- models/events_daily.sql {{ config( materialized='incremental', engine='ReplacingMergeTree(updated_at)', order_by='(event_date, event_type, country)', partition_by='toYYYYMM(event_date)', unique_key='(event_date, event_type, country)', incremental_strategy='delete+insert' ) }} SELECT toDate(event_time) AS event_date, event_type, country, count() AS total_events, uniq(user_id) AS unique_users, sum(revenue) AS total_revenue, now() AS updated_at FROM {{ source('raw', 'events') }} WHERE event_time >= today() - INTERVAL 1 DAY {% if is_incremental() %} AND event_time >= (SELECT max(event_date) FROM {{ this }}) - INTERVAL 1 DAY {% endif %} GROUP BY event_date, event_type, country ``` **dbt source definition (`models/sources.yml`):** ```yaml version: 2 sources: - name: raw database: raw schema: raw tables: - name: events columns: - name: event_id tests: [not_null, unique] - name: event_time tests: [not_null] ``` **Running the pipeline:** ```bash dbt debug # verify connection dbt run # materialise all models dbt test # run data quality tests dbt docs generate # generate lineage docs dbt docs serve # view DAG in browser ``` **When this fits:** - You already use dbt and want to **migrate or add ClickHouse®** as the target without changing your workflow - Your team uses **dbt tests, seeds, snapshots, and docs** and needs them all to work against ClickHouse® - You run **self-managed ClickHouse®** and want full control over the connection **Trade-offs:** the `dbt-clickhouse` adapter is a community-maintained project — not all dbt features are supported at parity with the official BigQuery/Snowflake adapters. Check the [adapter changelog](https://github.com/ClickHouse/dbt-clickhouse) before using advanced features like snapshots, Python models, or unit tests. ClickHouse® does not support ACID transactions; incremental models use `delete+insert` or `append` strategies rather than true `MERGE`. **Prerequisites:** dbt-core 1.6+, `dbt-clickhouse`, ClickHouse® instance reachable on port 8123 or 8443, dbt profile configured. ### **Option 2: dbt → ClickHouse® Cloud — dbt Cloud + Cloud endpoint** For teams using **dbt Cloud** (the managed dbt hosting platform), ClickHouse® Cloud provides a native connection path. Configure a ClickHouse® Cloud endpoint in dbt Cloud's connection settings using the same `dbt-clickhouse` adapter — dbt Cloud installs the adapter and manages the environment. **How it works:** in dbt Cloud, create a new project, set the connection type to ClickHouse®, and provide your ClickHouse® Cloud hostname, port, database, and credentials. dbt Cloud handles adapter installation, environment isolation, and IDE access. You get the dbt Cloud UI (IDE, scheduler, CI/CD) with ClickHouse® as the execution engine. **dbt Cloud environment variables (set in dbt Cloud UI):** ```bash DBT_CLICKHOUSE_HOST=your-instance.clickhouse.cloud DBT_CLICKHOUSE_PORT=8443 DBT_CLICKHOUSE_USER=dbt_cloud_user DBT_CLICKHOUSE_PASSWORD= DBT_CLICKHOUSE_DATABASE=analytics ``` **`dbt_project.yml` with ClickHouse®-specific model defaults:** ```yaml name: 'clickhouse_project' version: '1.0.0' profile: 'clickhouse_cloud' models: clickhouse_project: staging: +materialized: view +schema: staging marts: +materialized: incremental +engine: ReplacingMergeTree(updated_at) +order_by: "(event_date, event_type)" +partition_by: toYYYYMM(event_date) ``` **dbt Cloud CI/CD integration — `dbt_cloud_job.yml` (via dbt Cloud API trigger):** ```bash # Trigger a dbt Cloud job via API (from CI pipeline or Airflow) curl -X POST \ -H "Authorization: Token YOUR_DBT_CLOUD_TOKEN" \ -H "Content-Type: application/json" \ -d '{"cause": "Triggered from CI", "git_branch": "main"}' \ "https://cloud.getdbt.com/api/v2/accounts/ACCOUNT_ID/jobs/JOB_ID/run/" ``` **When this fits:** - Your team uses **dbt Cloud** for managed scheduling, IDE, and CI/CD and wants ClickHouse® Cloud as the execution engine - You need **dbt Cloud features**: IDE autocomplete, lineage visualization, auto-scheduling, Slim CI, and the dbt Explorer - You run **ClickHouse® Cloud** and want the simplest managed integration path with both services **Trade-offs:** dbt Cloud adds cost on top of ClickHouse® Cloud. The `dbt-clickhouse` adapter must be supported by the dbt Cloud adapter registry for the version you need — check compatibility before upgrading dbt Core versions. For self-managed ClickHouse®, Option 1 is simpler. **Prerequisites:** dbt Cloud account (Team or Enterprise plan for custom adapters), ClickHouse® Cloud account, `dbt-clickhouse` adapter compatible with your dbt Cloud version, ClickHouse® Cloud credentials. ### **Option 3: dbt → Tinybird — ClickHouse®-powered transformation and serving** For teams that need both **SQL-based transformations and REST API serving** from the same ClickHouse® layer, **Tinybird** replaces dbt as the transformation framework. Tinybird Pipes are the equivalent of dbt models: SQL transformations that chain together, materialise incrementally, and publish as REST endpoints with a single command. **How it works:** you define `.datasource` files (equivalent to dbt sources) and `.pipe` files (equivalent to dbt models) in a git-managed project. `tb deploy` pushes definitions to Tinybird's ClickHouse®-backed platform. Materialized views update incrementally on ingest. The final pipe node can be published as a REST API endpoint. **dbt concept mapping:** | dbt concept | Tinybird equivalent | | --- | --- | | `sources.yml` | `.datasource` file (schema + engine + sort key) | | Model (`.sql` file) | Pipe node (`.pipe` file) | | `ref('model_name')` | `FROM pipe_name` | | `materialized='incremental'` | `TYPE materialized` pipe node | | `materialized='table'` | `TYPE copy` pipe node | | `dbt run` | `tb deploy` + automatic MV updates | | `dbt test` | `tb test` (endpoint response tests) | | API serving layer | `TYPE endpoint` node — no extra infra needed | **Example Tinybird `.datasource` file (equivalent to dbt source):** ```sql SCHEMA > event_id UInt64, user_id UInt64, event_type LowCardinality(String), event_time DateTime, country LowCardinality(String), revenue Float64 ENGINE "ReplacingMergeTree" ENGINE_PARTITION_KEY "toYYYYMM(event_time)" ENGINE_SORTING_KEY "user_id, event_id" ``` **Example Tinybird `.pipe` file — materialized view (equivalent to dbt incremental model):** ```sql NODE mv_node SQL > SELECT toDate(event_time) AS event_date, event_type, country, count() AS total_events, uniqState(user_id) AS unique_users_state, sumState(revenue) AS total_revenue_state FROM events GROUP BY event_date, event_type, country TYPE materialized DATASOURCE events_daily_mv ``` **Example Tinybird `.pipe` file — REST endpoint (no equivalent in dbt):** ```sql NODE query_node SQL > SELECT event_date, event_type, sumMerge(total_revenue_state) AS total_revenue, uniqMerge(unique_users_state) AS unique_users FROM events_daily_mv WHERE event_date >= {{ Date(start_date, '2026-01-01') }} AND event_date <= {{ Date(end_date, '2026-05-02') }} GROUP BY event_date, event_type ORDER BY event_date DESC TYPE endpoint ``` **Deploy and test:** ```bash tb --cloud deploy # push all definitions tb endpoint ls # list published endpoints tb test run # run endpoint tests curl "https://api.tinybird.co/v0/pipes/events_daily.json?start_date=2026-05-01&end_date=2026-05-02" \ -H "Authorization: Bearer YOUR_TOKEN" ``` **When this fits:** - You need the **same transformed data** in both analytical queries and **REST APIs for applications** — Tinybird serves both from one Pipe - You want **real-time incremental materialisation** without scheduling `dbt run`; Tinybird MVs update on every ingest event - You're willing to **replace dbt** (not extend it) and want a simpler, fully managed stack without a separate BI / API layer **Trade-offs:** Tinybird is not a drop-in dbt replacement — it has a different project structure, SQL dialect (ClickHouse®), and testing model. Teams with large existing dbt projects face a migration cost. dbt's ecosystem (hundreds of packages, adapters, community macros) does not carry over. Best for greenfield projects or teams specifically building real-time APIs. **Prerequisites:** Tinybird account, `tb` CLI installed (`pip install tinybird-cli`), familiarity with ClickHouse® SQL. ### **Summary: picking the right option** | Criterion | dbt-clickhouse adapter | dbt Cloud + CH Cloud | Tinybird | | --- | --- | --- | --- | | **Setup complexity** | Low (pip install) | Low (Cloud UI) | Medium (new project model) | | **dbt ecosystem (packages, macros)** | Yes | Yes | No | | **Managed platform** | No (self-run) | Yes (dbt Cloud) | Yes (Tinybird) | | **REST API publishing** | No | No | Yes (TYPE endpoint) | | **Real-time incremental updates** | No (scheduled runs) | No (scheduled runs) | Yes (MVs on ingest) | | **Self-managed ClickHouse®** | Yes | No | No (Tinybird-managed) | | **Ops burden** | Low–Medium | Low | Low | ## **Decision framework: what to choose for clickhouse integration dbt** Pick based on your **existing tooling**, **deployment model**, and **downstream consumers**: - **dbt-clickhouse adapter** if you already use dbt Core and want to run your existing models against ClickHouse® with minimal changes. Best for teams migrating from BigQuery or Snowflake and keeping their dbt workflows intact. - **dbt Cloud + ClickHouse® Cloud** if you use dbt Cloud for managed scheduling, IDE, and CI/CD and want the simplest fully-managed integration. Best for teams that don't want to manage dbt infrastructure. - **Tinybird** if you need both real-time materialisation and REST API serving from the same layer, and you're willing to adopt a different project model. Best for teams building [user-facing analytics](https://www.tinybird.co/blog/user-facing-analytics) where dbt's batch-first approach is a bottleneck. **Bottom line:** for teams invested in the dbt ecosystem, Options 1 or 2 preserve that investment while gaining ClickHouse® performance. For greenfield real-time API projects, Option 3 eliminates the dbt + separate API layer pattern entirely. ## **What does clickhouse integration dbt mean (and when should you care)?** A **clickhouse integration dbt** setup uses dbt's transformation framework to define, test, and materialise SQL models against ClickHouse® as the execution engine. dbt handles model dependency resolution, testing, documentation, and scheduling. ClickHouse® handles the fast columnar storage and query execution. You should care when your current dbt target (BigQuery, Snowflake, Redshift) is too slow or expensive for the query volumes you're running. ClickHouse® handles large analytical workloads — billions of rows, complex aggregations, time-series filters — at significantly lower cost and latency than cloud data warehouses for certain workloads. The integration also matters when you want to **keep the dbt workflow** (SQL models, refs, tests, CI/CD integration) but upgrade the execution layer. ClickHouse® is a natural fit for teams whose dbt models are primarily aggregations, time-series rollups, and filter-heavy queries over large event datasets. ## **Schema and model design** ### **Practical schema rules for dbt-managed ClickHouse® models** dbt compiles SQL models to DDL. Designing ClickHouse® tables with the right engine and sort key is critical — unlike BigQuery or Snowflake, ClickHouse® performance is highly sensitive to schema design choices made at table creation time. **Rule 1: always set `order_by` in dbt `config()` blocks.** ClickHouse® tables require an `ORDER BY` (sorting key). Never rely on defaults. Choose columns that match your most common `WHERE` and `GROUP BY` clauses. **Rule 2: set `partition_by` for time-series tables.** `toYYYYMM(event_time)` partitioning ensures dbt incremental runs and `WHERE` date-range filters scan only relevant partitions. **Rule 3: use `incremental_strategy='delete+insert'` for idempotent dbt runs.** ClickHouse® does not support `MERGE`-based upserts. The `delete+insert` strategy deletes the matching partition and re-inserts — combine with `unique_key` scoped to the partition. **Rule 4: use `LowCardinality(String)` for dimension columns.** dbt models frequently group by categorical dimensions. `LowCardinality` keeps `GROUP BY` fast for columns with bounded cardinality (event type, country, status). ### **Example: dbt-friendly ClickHouse® model chain** **Staging model (`models/staging/stg_events.sql`):** ```sql {{ config( materialized='view', schema='staging' ) }} SELECT event_id, user_id, lower(trim(event_type)) AS event_type, toDateTime(event_time) AS event_time, upper(country) AS country, coalesce(revenue, 0.0) AS revenue FROM {{ source('raw', 'events') }} WHERE event_time IS NOT NULL AND event_id IS NOT NULL ``` **Mart model (`models/marts/fct_events_daily.sql`):** ```sql {{ config( materialized='incremental', engine='ReplacingMergeTree(updated_at)', order_by='(event_date, event_type, country)', partition_by='toYYYYMM(event_date)', unique_key='(event_date, event_type, country)', incremental_strategy='delete+insert' ) }} SELECT toDate(event_time) AS event_date, event_type, country, count() AS total_events, uniq(user_id) AS unique_users, sum(revenue) AS total_revenue, now() AS updated_at FROM {{ ref('stg_events') }} {% if is_incremental() %} WHERE toDate(event_time) >= (SELECT max(event_date) - INTERVAL 1 DAY FROM {{ this }}) {% endif %} GROUP BY event_date, event_type, country ``` ### **Failure modes** 1. **Missing `order_by` in incremental models.** dbt-clickhouse requires an explicit `order_by` in the `config()` block for incremental models. Omitting it causes a DDL error or falls back to a non-performant default. Mitigation: set `order_by` in every model config; use project-level defaults in `dbt_project.yml`. 2. **`is_incremental()` filter scanning full history.** A common mistake is using `WHERE event_time >= now() - INTERVAL 1 DAY` instead of filtering against `max(this.event_date)`. The subquery version correctly tracks the last loaded partition. Mitigation: always use `SELECT max(date_col) FROM {{ this }}` in the incremental filter. 3. **`delete+insert` strategy deleting too broadly.** If `unique_key` spans multiple partitions, `delete+insert` may delete rows from non-target partitions. Mitigation: scope `unique_key` to the partition key column so deletes only touch the target partition. 4. **dbt test failures on ClickHouse® type mismatches.** dbt's generic tests (`not_null`, `unique`) compile to SQL that may fail on ClickHouse® with certain type combinations (e.g., `UInt64` vs `Nullable(UInt64)`). Mitigation: use explicit `CAST` in staging models to normalise types before tests run. 5. **Slow `dbt docs generate` on large schemas.** `dbt docs generate` runs `SHOW TABLES` and schema introspection queries. On ClickHouse® instances with thousands of tables, this can be slow. Mitigation: scope docs generation to specific models with `--select`, or run docs generation in CI rather than locally. ## **Why ClickHouse® for dbt analytics** ClickHouse® is a **columnar OLAP database** purpose-built for the aggregation-heavy, filter-intensive SQL patterns dbt models generate. **Vectorized execution** and **columnar compression** deliver sub-second aggregations on billions of rows — `INSERT INTO … SELECT` transformations that take minutes on Snowflake or BigQuery often complete in seconds on ClickHouse®. For dbt users, this means faster model runs, faster CI, and downstream queries that respond interactively even on large mart tables. ClickHouse®'s MergeTree engine supports **time-partitioned data**, **idempotent incremental loads via `ReplacingMergeTree`**, and **sorting keys** that map directly to dbt model `GROUP BY` and `WHERE` patterns. For teams whose dbt models are primarily time-series aggregations and event analytics, ClickHouse® is among the [fastest database for analytics](https://www.tinybird.co/blog/fastest-database-for-analytics) backends available. ## **Security and operational monitoring** - **Authentication:** dedicated dbt ClickHouse® user with `GRANT SELECT, INSERT, CREATE TABLE, DROP TABLE` on target schemas only. Never use the `default` admin user in dbt profiles. - **TLS:** enforce HTTPS on all connections. Port 8443 for Cloud; `https_port` for self-managed. Set `secure: true` and `verify: true` in `profiles.yml`. - **Credential storage:** use environment variables (`{{ env_var('CLICKHOUSE_PASSWORD') }}`) in `profiles.yml`. Never commit passwords. Use dbt Cloud's environment variable secrets for managed deployments. - **Query limits:** set `max_execution_time` and `max_memory_usage` on the dbt ClickHouse® user profile to prevent runaway model runs from impacting production query traffic. - **Token hygiene:** rotate Tinybird API tokens on schedule for Option 3; use scoped tokens with minimum permissions per pipeline. ## **Latency, caching, and freshness considerations** **dbt model runs** are batch operations — data freshness is the run schedule (hourly, daily). ClickHouse® executes each model's SQL synchronously; run duration depends on data volume and model complexity. Pre-aggregated materialized views in ClickHouse® reduce downstream query latency for BI tools that query mart tables. **dbt Cloud scheduler** manages run cadence. For near-real-time freshness, trigger dbt Cloud jobs via API from event-driven systems (Kafka consumer, webhook) rather than relying on fixed cron schedules. **Tinybird Pipe caching** (Option 3) provides configurable TTL freshness for API endpoints. API consumers receive sub-millisecond cached responses between ingestion events without a ClickHouse® query on each call. ## **Why Tinybird is a strong fit for clickhouse integration dbt** Most data teams using dbt with ClickHouse® eventually hit the same constraint: transformed data lives in ClickHouse® tables, but serving it to **product features**, **customer-facing dashboards**, or **internal APIs** requires a separate Flask/FastAPI service querying ClickHouse® directly. This adds infrastructure, duplicates SQL logic, and creates an operational gap between the dbt transformation layer and the serving layer. Tinybird solves this by combining a **ClickHouse®-powered analytics platform**, **SQL-based Pipes** (equivalent to dbt models), and **instant REST API publishing** in one managed service. Teams that adopt Tinybird alongside or instead of dbt eliminate the separate API layer — the same Pipe that materialises the transformation also publishes the endpoint. This is the [real-time data ingestion](https://www.tinybird.co/blog/real-time-data-ingestion) and serving pattern for teams that outgrow dbt's batch-first, warehouse-only model. Next step: identify your most latency-sensitive dbt mart model, replicate it as a Tinybird Pipe in staging, and compare run latency and API response time before committing to a migration. ## **Frequently Asked Questions (FAQs)** ### **What adapter do I need for clickhouse integration dbt?** Use **`dbt-clickhouse`** — install with `pip install dbt-clickhouse`. It supports dbt-core 1.6+ and connects to both self-managed ClickHouse® and ClickHouse® Cloud via the HTTP interface. For dbt Cloud, the adapter must be available in dbt Cloud's adapter registry for your dbt Core version. ### **Does clickhouse integration dbt support incremental models?** Yes. The `dbt-clickhouse` adapter supports `incremental` materialisation with two main strategies: `append` (inserts new rows only) and `delete+insert` (deletes matching rows in the target partition and re-inserts). ClickHouse® does not support `MERGE`-based upserts, so `delete+insert` is the recommended strategy for idempotent incremental runs. ### **Can I use dbt tests with ClickHouse®?** Yes. Generic dbt tests (`not_null`, `unique`, `accepted_values`, `relationships`) compile to SQL and run against ClickHouse®. Some type-sensitive tests may require explicit `CAST` in staging models to avoid ClickHouse® type mismatch errors. Custom data tests work as any SQL query returning zero rows on pass. ### **How do I handle ClickHouse®'s lack of ACID transactions in dbt?** Design incremental models with partition-scoped `unique_key` and `incremental_strategy='delete+insert'`. This scopes the delete to the target partition and re-inserts clean data — effectively idempotent at the partition level. Avoid cross-partition `unique_key` values. Use `ReplacingMergeTree` for last-write-wins deduplication as a secondary safety net. ### **How do I speed up slow dbt model runs on ClickHouse®?** Three approaches: (1) add a **sorting key** (`order_by` in config) that matches the model's `WHERE` and `GROUP BY` columns; (2) use **partition pruning** — ensure the incremental filter touches only the target partition; (3) **pre-aggregate** in a staging materialized view so the mart model reads from a pre-aggregated source rather than raw events. ### **What are the main limitations of clickhouse integration dbt?** The `dbt-clickhouse` adapter is community-maintained with partial feature parity — snapshots, Python models, and some advanced incremental strategies may not be supported. ClickHouse® does not support transactions or `MERGE` upserts. `dbt docs generate` can be slow on large schemas. The Tinybird option (Option 3) requires adopting a new project model and SQL dialect rather than reusing existing dbt models.