Repository navigation
Add ClickHouse support in dbt Core 2.0 #660
Description
Issue created to specifically track the development of the ClickHouse adapter in dbt Core 2.0. Mirror issue from dbt Core repository: dbt-labs/dbt#14585
Context
Small clarification on dbt Core 1.X vs dbt Core 2.X vs dbt Fusion
- This repo contains the implementation for dbt Core 1.X (the Python version). This version will continue to be maintained and improved from this repository.
- dbt Core 2.0 is a full redesign of the dbt engine and lives alongside the adapter implementations in a different repository: https://github.com/dbt-labs/dbt-core. Once production-ready, upgrading will work just like before. Simply update (or build) your
dbtbinary and you can continue using your existing project! - dbt Fusion: This is dbt Core 2.0 plus a set of interesting features, including more intelligent SQL parsing and a VS Code extension https://docs.getdbt.com/docs/fusion/about-fusion. Fusion support is being tracked here: Support dbt Fusion #555.
Current status
ClickHouse is already supported in dbt Core 2.0 and most things are working!
That said, both dbt Core 2.0 and the ClickHouse adapter for that version are in alpha and not production-ready yet. Please be careful when testing so it doesn't interfere with your production workflows or files such as manifest.json.
You can find more details about the (upcoming) upgrade guides here: https://docs.getdbt.com/docs/dbt-versions/core-upgrade/upgrading-to-v2?version=2.0&name=Fusion
What you can expect right now
- Basic projects should work without issues, but expect some buggy behavior and some inconsistencies.
- Materialized views (MV) now have v1 feature parity (see the
materialized_viewsub-issue below).
We don't have firm dates yet, but we're aiming for a production-ready version of dbt Core 2.0 in the second half of 2026. We'll keep this issue updated. Feel free to ask any questions in the meantime.
Related resources
Issue depends on #555.
Known limitations
- Reading
UUIDcolumns requires a recent ClickHouse server (26.7+). dbt Core 2.0 receives query results in Arrow format, and older servers cannot convertUUIDcolumns to Arrow output: anySELECTreturning aUUIDcolumn fails withCode: 50. DB::Exception: The type 'UUID' of a column '<name>' is not supported for conversion into Arrow data format (UNKNOWN_TYPE). Use a recent ClickHouse version when testing dbt Core 2.0. This is a server-side limitation; there is no adapter-side workaround. - ClickHouse 26.9 and newer cannot be used yet. The ADBC driver that dbt Core 2.0 ships (adbc_clickhouse 0.1.1) fails every statement against ClickHouse 26.9+,
create databaseincluded, withExternal error: decompression error: incorrect magic number: 26.9 changed how HTTP result sets are compressed and the driver'sclickhousecrate predates the fix (Every read fails on ClickHouse 26.9+ with "decompression error: incorrect magic number" adbc_clickhouse#86). Use ClickHouse 26.8 or earlier when testing dbt Core 2.0 (26.7 or 26.8 if you also needUUIDcolumns) until a driver release with the fix is picked up by dbt Core 2.0. verify: falseis not honoured. dbt Core 2.0 always validates the server's TLS certificate; the profile key is accepted but ignored, so a server with a self-signed or private-CA certificate fails the TLS handshake where dbt Core 1.x withverify: falseconnects. Skipping validation keeps the encryption but drops the authentication, so anyone on the path can impersonate the server and read the credentials. Use a certificate signed by a CA the client trusts. Open an issue if you needverify: falsein dbt Core 2.0.- A source's ClickHouse database is set with
schema:.dbt-clickhouseon dbt Core 1.x also acceptsdatabase:on a source and, when the source has noschema:, uses it as the ClickHouse database. dbt Core 2.0 does not: the source resolves to<source name>.<table>and the model fails withCode: 81. DB::Exception: Database <source name> does not exist. Writeschema: <database>on the source; that form works on both engines.
Pending work for feature parity
Feature-parity tracking for the ClickHouse adapter in dbt Core 2.0 / Fusion, versus the Python dbt-clickhouse adapter. The work is split into one sub-issue per area — start here for the overall status, then rely on each sub-issue for the detailed feature tables and the actual pending changes. The sub-issue list on this issue shows live progress.
Status legend:
- ✅ implemented — merged and working on dbt-core 2.0 main
- 🟡 partial — partly working on main; see the sub-issue for what works vs fails
- 🚧 in progress — implemented in an open PR (linked in Notes)
- 🔴 not started — not working anywhere
| Area | Overall status | Sub-issue | Summary |
|---|---|---|---|
Materialization: table |
✅ | #702 | Everything table-specific works: v1 table skeleton, all DDL configs (engine/order_by/ttl/settings/query_settings/primary_key/partition_by), indexes/projections (incl. index-type), sql_header, EXCHANGE rebuilds, MV-target protection |
Materialization: view |
✅ | #703 | Everything view-specific works (create/replace, sql_security/definer, auto-created-MV cleanup) |
Materialization: incremental |
✅ | #704 | Merged: all strategies (legacy/append/delete_insert/insert_overwrite/microbatch), unique_key, incremental_predicates, Python-exact strategy resolution/validation, on_schema_change |
Materialization: materialized_view |
✅ | #705 | Everything MV-specific works: create (incl. multiple MVs), MODIFY QUERY on re-run, refreshable clause + refresh-schedule updates, depends_on validation, on_schema_change, target protection (mvs_pointing_to_it), multi-MV update/rename, refreshable: false, refreshable catch-up ordering (dbt-labs/dbt#16844). The proper upstream multi-MV design is ⚪️ future work |
Materialization: dictionary |
✅ | #706 | Done (byte-parity macro, all configs; existing dictionaries typed dictionary by get_relation and the relation cache, dbt-labs/dbt#16866) |
Materialization: snapshot |
✅ | #708 | Done for v1 parity (timestamp/check strategies, derivative upsert, tmp cleanup) |
Materialization: seed |
✅ | #709 | Seed flow works end-to-end, incl. ' and ? in CSV data and the ClickHouse + keys under seeds: in dbt_project.yml (dbt-labs/dbt#16869). Integer width inference and the format CSV insert path are ⚪️ future work |
Materializations: distributed_table / distributed_incremental |
✅ | #707 | Both materializations run end to end on a self-managed cluster (dbt-labs/dbt#16552: cluster machinery, sharding_key, v1 macro sync) |
Materialization: clone |
✅ | #730 | Done: MergeTree tables, seeds, snapshots and incrementals cloned with CREATE OR REPLACE TABLE … CLONE AS (ON CLUSTER when configured); views and non-MergeTree engines fall back to views (dbt-labs/dbt#16750) |
| Model contracts & constraints | ✅ | #729 | Complete on every runnable SQL materialization (dbt-labs/dbt#16082): DESCRIBE validation, CONSTRAINT_SUPPORT matrix, constraint DDL, TYPE_LABELS, column codec/ttl, on distributed models too |
| Tests / catalog / docs | ✅ | #710 | Merged: data tests, test extra configs, catalog macros (--write-catalog), persist_docs incl. descriptions containing ; (dbt-labs/dbt#16873), docs-generate suite, unit tests (dbt-labs/dbt#16153), unit-test render ordered after the seed backing a given source() (closes dbt-labs/dbt#16202); persist_docs.sql byte-identical to v1. docs generate command parity and generic unit-test schema inference are ⚪️ future work |
| Cross-materialization settings | 🟡 | #711 | Merged: model settings/query_settings, version gating, dedup defaults, config-key registration for model keys, connection settings + allow_nondeterministic_mutations (dbt-labs/dbt#16156), ON CLUSTER incl. disable_on_cluster (dbt-labs/dbt#16552), grants (dbt-labs/dbt#16750), the ClickHouse table options on seeds, snapshots, tests and unit tests (dbt-labs/dbt#16869). Missing: query_settings on seed inserts |
| General adapter behaviour | 🟡 | #712 | Merged: version probe, DESCRIBE column introspection, TLS, non-experimental onboarding, S3 (clickhouse_s3source), lw-delete probe, EXCHANGE probe (dbt-labs/dbt#16200), 0.1.1 driver with the literal-? fix (dbt-labs/dbt#16185), relation cache filled per schema via list_relations (dbt-labs/dbt#16429), view replace fallback without renameat2 (dbt-labs/dbt#16866). Missing: query comment, query_id, ClickHouse 26.9+ servers (driver release pending). verify: false, sync_request_timeout and compress_block_size are ⚪️ future work |
| jaffle-shop validation | ✅ | #734 | Silent seed data loss (splitter dropped unlexable statements, so seed INSERTs with ' in CSV data never ran) is fixed on main and the ClickHouse lexer routing + unit-test fix are merged (dbt-labs/dbt#16153); seed CSV values containing ? load byte-exact since dbt-labs/dbt#16156: seed, run and build pass |
| dbt v2 specific functionality | 🔴 | #736 | Fusion-only capabilities: the SQL frontend has no ClickHouse dialect, so static analysis is forced off and the engine produces no column lineage. dbt platform column lineage works only with quoting disabled in dbt_project.yml, because its fallback parser rejects backtick-quoted identifiers (fix requested in dbt-labs/dbt#16646). Metadata-based dbt source freshness is not implemented (panics). Record/replay rebuilds relations without their ClickHouse catalog stamps. dbt/<version> is in the HTTP User-Agent but not always first; the relation cache is filled lazily while nodes run (dbt-labs/dbt#16454) |
| Future work (explicitly deferred items) | ⚪️ | #731 | Collects every ⚪️-marked item, one table per area (integer width inference + get_csv_data insert path; multiple-MVs upstream fix; 1.10.1/1.10.2 feature ports; verify: false and the native-protocol profile keys; engine-side CLI/config policies) |
Tracking of issues from ADBC ClickHouse
Pending — each needs an adbc_clickhouse release and then a driver pin bump in dbt Core 2.0:
- Every read fails on ClickHouse 26.9+ with "decompression error: incorrect magic number" adbc_clickhouse#86 — every statement fails on ClickHouse 26.9+ with
decompression error: incorrect magic number; the fix is theclickhouse0.15.3 bump in fix: update clickhouse-rs to 0.15.3 for ClickHouse 26.9 compatibility adbc_clickhouse#87 - Align the driver with ADBC 0.24 and Arrow 59 adbc_clickhouse#85 — align the driver with ADBC 0.24 / Arrow 59 /
clickhouse-ext-arrow0.2.0; thatclickhouse-ext-arrowrelease carries the User-Agent ordering fix, sodbt/<version>comes first on Arrow reads only once the driver adopts it
Fixed in adbc_clickhouse 0.1.1, on dbt Core 2.0 main via dbt-labs/dbt#16185:
- Don't try to substitute
?in queries adbc_clickhouse#53 - Allow configuring a different database for the connection adbc_clickhouse#67
- Support arbitrary ClickHouse settings as driver options adbc_clickhouse#70
Tracking of issues from clickhouse-rs
- User-Agent: product info added by
clickhouse-ext-arrowends up ahead of the application's product info clickhouse-rs#469 —clickhouse-ext-arrowput its product info before dbt's in the User-Agent. Fixed (fix: enforce expected ordering in product info added byclickhouse-ext-arrowclickhouse-rs#484) and released inclickhouse-ext-arrow0.2.0; reaches dbt Core 2.0 through adbc_clickhouse#85 above
Tracking of pending issues from ClickHouse
Extra steps to improve Fusion cohesion
- Improve how MVs are integrated in dbt
- Move our integrations test suite to dbt Core 2.x
- Have the same tests as in
dbt-adapter-testsfor Core 2.0
Reacted by igorvoltaic, Jesper Bagge, Samuel LE GUEN, Bentsi Leviav and Eugenio Contreras
Activity
table#702view#703incremental#704materialized_view#705dictionary#706distributed_table/distributed_incremental#707snapshot#708seed#709dbt clonesupport #730