CLICKHOUSE

CONNECT WITH A PASSWORD — THE COMMON CASE

One output in profiles.yml is all it takes. This is a complete, working profile over ClickHouse's native TCP protocol (port 9000) — the fast path, and the one to prefer:

# ~/.dbt/profiles.yml
my_project:
  target: ch_dev
  outputs:
    ch_dev:
      type: clickhouse
      host: ch.internal.com
      port: 9000                              # native TCP protocol
      user: analytics
      password: "{{ env_var('CH_PASSWORD') }}"
      schema: analytics                       # in ClickHouse, this IS the database
      threads: 4

Run dvt debugand DVT tests the connection on both of its layers — the dbt layer that builds models and the Sling layer that moves data. One green check means both work. If ClickHouse isn't installed as a driver yet, dvt sync reads this profile and installs everything the engine needs — for ClickHouse that's both transports, HTTP and native, so either protocol works out of the box.

HTTP INSTEAD OF NATIVE

Set driver: http and DVT talks to ClickHouse over its HTTP interface (port 8123) instead of native TCP. Useful when a proxy or load balancer only passes HTTP, or when 9000 is closed between you and the server:

ch_http:
  type: clickhouse
  driver: http                            # switch transports
  host: ch.internal.com
  port: 8123                              # optional — 8123 is the http default
  user: analytics
  password: "{{ env_var('CH_PASSWORD') }}"
  schema: analytics

Leave port out and DVT picks the right default for the transport: 9000 native, 8123 HTTP — and 8443 once TLS enters the picture below.

TLS — CLICKHOUSE CLOUD AND SECURED SERVERS

secure: true turns on TLS. For ClickHouse Cloud (or any server with a TLS-only native endpoint), set the port explicitly — secure native listens on 9440, and DVT keeps 9000 as the default unless you say otherwise:

ch_cloud:
  type: clickhouse
  host: abc123.us-east-1.aws.clickhouse.cloud
  port: 9440                              # secure native — set it explicitly
  user: default
  password: "{{ env_var('CH_CLOUD_PASSWORD') }}"
  schema: analytics
  secure: true

The same flag on the HTTP transport gives you HTTPS, and there the default port does follow along — 8443 the moment secure is set:

ch_cloud_https:
  type: clickhouse
  driver: http
  host: abc123.us-east-1.aws.clickhouse.cloud
  user: default
  password: "{{ env_var('CH_CLOUD_PASSWORD') }}"
  schema: analytics
  secure: true                            # https, port 8443 by default

Testing against a self-signed certificate? verify: false skips certificate verification. Keep it for dev boxes, not production.

ch_selfsigned:
  type: clickhouse
  host: ch.dev.internal
  user: analytics
  password: "{{ env_var('CH_PASSWORD') }}"
  schema: analytics
  secure: true
  verify: false                           # self-signed cert — dev only

HOW CLICKHOUSE BEHAVES IN DVT

Materializations. The full set — table, view, incremental, ephemeral, plus an experimental distributed_table. Incremental models have their own vocabulary of strategies on this engine, and their own default; the section below is that vocabulary in full.

Engine configs pass through. MergeTree engine choices, order_by, partition_by and TTLall work as model configs, exactly as dbt-clickhouse documents them — DVT's model-building lane is that adapter.

Case matters. ClickHouse identifiers are case-sensitive. events and Events are different tables; keep your source declarations spelled the way the server spells them.

Data movement. When federation extracts from or loads into ClickHouse, the transfer rides the exact connection you configured — same host, same transport, same TLS flags. For bulk movement the native protocol is measurably faster than HTTP, which is why the examples above lead with it.

FETCH FIRST becomes LIMIT. ClickHouse parses the ANSI FETCH FIRST n ROWS ONLY clause but refuses to run it without an ORDER BY — a runtime rule, not a syntax one. A model written in an Oracle- or Snowflake-flavored dialect carries that clause naturally, so when DVT renders such a model for ClickHouse it rewrites a plain row-count FETCH to LIMIT — the same operation, no ORDER BY demanded. Variants LIMIT can't express (PERCENT, WITH TIES) are passed through untouched so the server rejects them in its own words rather than DVT guessing at semantics.

Reading it live. To use this connection as a federation source, point a source at the output by name:

# models/sources.yml
sources:
  - name: clickhouse_events
    meta:
      connection: ch_dev      # the profiles.yml output above
    schema: analytics         # the ClickHouse database
    tables:
      - name: events
      - name: metrics

Local dev in one line. The official image exposes both ports, with user default and an empty password — the profile then needs only type, host, user and schema:

docker run -d --name ch -p 9000:9000 -p 8123:8123 \
  clickhouse/clickhouse-server

INCREMENTAL MODELS — NO MERGE, AND THE DEFAULT DEPENDS ON YOUR SERVER

ClickHouse has no MERGE statement, so the adapter never offers one. The strategies it accepts, spelled exactly as you write them in config, are legacy, append, delete_insert, insert_overwrite and microbatch. A + is normalized to _ before the name is checked, so delete+insert — the spelling the rest of the SQL world uses — is accepted and means delete_insert. merge is not on that list, and asking for it stops the run:

The incremental strategy 'merge' is not valid for ClickHouse.

Leave incremental_strategy out and the default is decided by the connection, not by a constant. On connect the adapter asks the server whether lightweight deletes are actually usable. If they are, the default is delete_insert. If they are not, it is legacy. The profile flag is use_lw_deletes and it is false unless you set it — and setting it is a request, not a guarantee: the server must let the session set both allow_experimental_lightweight_delete and allow_nondeterministic_mutations. Ask for lightweight deletes on a server that has those settings locked read-only and the connection fails with an explicit error rather than quietly proceeding. Ask for nothing, and legacy is what you get.

    ch_dev:
      type: clickhouse
      # ...
      use_lw_deletes: true    # delete_insert and microbatch require this

Four rules then decide whether the strategy you named will actually run, and all four are checked before any SQL is sent. delete_insert and microbatch need lightweight deletes and a non-empty unique_key. incremental_predicates are legal only with those two strategies. insert_overwrite is the mirror image: it requires partition_by, and it refuses unique_key outright. The messages are plain:

'delete_insert' strategy requires setting the profile config 'use_lw_deletes' to true.
'delete_insert' strategy requires a non-empty 'unique_key'.
Cannot apply incremental predicates with 'append' strategy.
'insert_overwrite' strategy requires non-empty 'partition_by'. Current partition_by is None.
'insert_overwrite' strategy does not support unique_key.

A working key-based incremental on ClickHouse, then, is a strategy name and a key — with use_lw_deletes: true in the profile above backing it:

{{ config(
    materialized='incremental',
    incremental_strategy='delete_insert',   -- 'delete+insert' is the same name
    unique_key='event_id'                   -- this strategy refuses to run without one
) }}

select event_id, user_id, event_time, payload
from {{ ref('stg_events') }}
{% if is_incremental() %}
where event_time > (select max(event_time) from {{ this }})
{% endif %}

There is one path where none of that is checked, and it is worth knowing about because it hides mistakes. If the model sets inserts_only: true, or if it has no unique_key and its strategy is anything other than insert_overwrite, the materialization does a plain insert and never reaches validation at all — a misspelled strategy name is ignored on that path rather than refused. Validation is skipped again on the build that creates the table (nothing exists yet, so it is a plain create) and on --full-refresh. The practical consequence, measured: a strategy ClickHouse cannot accept can pass build #1 and fail on build #2.

Two more quirks worth having in writing. insert_overwrite is implemented as partition replacement — the adapter finds the changed partitions in system.parts and issues alter table … replace partition id …; if it finds none, it writes nothing. And on the distributed incremental materialization only legacy, delete_insert, insert_overwrite and append are wired up, so a microbatch model there passes validation, matches no branch, and produces no incremental write. legacy also skips applying schema changes; on a distributed table a schema change under legacy is refused with Schema changes not supported with Distributed tables.

When ClickHouse is the target of a federated f_incremental model, a different layer answers. DVT computes the model and lands the rows through its data-movement layer, so the vocabulary shrinks to what that layer can express: append, merge and delete+insert. There, merge and delete+insert are the same operation — a key-based upsert on unique_key — while insert_overwrite and microbatch are refused by name with DVT025: has no Sling equivalent — f_incremental supports append, merge and delete+insert. A merge or delete+insert with no unique_key does not fail; it warns and falls back to a full refresh, and an append with no watermark_column does the same. ClickHouse is one of the engines admitted to the keyless-watermark set — postgres, redshift, mysql, duckdb, sqlite, sqlserver, oracle, clickhouse, trino — so an f_incremental carrying only a watermark_column and no key runs here. On starrocks, snowflake, databricks, bigquery, fabric and athena, whose connectors have not been verified to honour an update-key-only load, the same model is refused with DVT026.

One DVT behaviour is ClickHouse-only. ClickHouse is the single entry in DVT's native-strategy substitution map, so when dvt flip-target-to moves a project onto ClickHouse, a native incremental model carrying incremental_strategy='merge' is rewritten to 'delete+insert' — and the rewrite is announced, never silent:

   incremental_strategy 'merge' -> 'delete+insert' — ch_dev does not accept 'merge'; both are key-based upserts

That is the whole map: one engine, one pair, and only because both are key-based upserts, so a model carrying a unique_key behaves identically after the swap. It happens at flip time and nowhere else — DVT never rewrites a strategy during a run.

REFERENCE — EVERY PROFILE FIELD

Everything the ClickHouse profile reads. type, user and the database name are the only hard requirements — the rest has sensible defaults.