dbt on ClickHouse: Project Layout and the Gotchas

Almost every dbt tutorial assumes you're pointing it at Snowflake, BigQuery, or Postgres. I run dbt Core against ClickHouse, which is a different enough target that the generic advice quietly leads you astray. The adapter is good — it's maintained by ClickHouse themselves — but the project layout, the materializations, and a couple of sharp edges are ClickHouse-specific. This is how I structure it, and the gotchas I wish someone had written down first. (For where ClickHouse itself runs, see running ClickHouse on a dedicated server; for the wider platform, the stack write-up.)

dbt Core and the ClickHouse adapter

I use dbt Core, not Cloud — it's just a Python package in the pipeline, version-pinned alongside the dbt-clickhouse adapter, which ClickHouse Inc. maintains. That matters: because it's first-party, it understands ClickHouse's engines (it can materialise into ReplicatedMergeTree, drive materialized views, and query S3 directly), rather than pretending ClickHouse is a generic SQL warehouse. The adapter is younger than the Snowflake or BigQuery ones, so you'll occasionally hit an edge those never see — but for standard modelling it's solid.

Project layout: staging → intermediate → marts

The structure is the conventional dbt three-layer split, with a naming convention that makes every model's role obvious from its name: stg_<source>_<entity> for raw-to-typed staging, int_<domain>_<purpose> for the joins and enrichment in between, and mart_<domain>_<entity> for the tables the BI layer actually reads. Staging and intermediate are cheap views; only the marts get materialised as real tables.

models:
  my_project:
    staging:
      +materialized: view
      +schema: staging
    intermediate:
      +materialized: view
      +schema: intermediate
    marts:
      +materialized: incremental
      +schema: marts

Layer-per-database, not schema suffixes

Here's the first ClickHouse-specific choice. A lot of dbt setups keep everything in one database and separate layers with schema-name suffixes. I put each layer in its own ClickHouse database — staging, intermediate, marts as three databases. Two reasons, both practical: ClickHouse grants are per-database, so it's trivial to give the BI tool read access to marts only and never expose the raw staging layer; and the BI tool's schema browser shows three clean namespaces instead of one crowded list. It costs nothing and keeps the boundary between raw and presentation genuinely enforced.

Materializations: views cheap, marts incremental

Staging and intermediate as views means they cost no storage and always reflect the latest source — the work happens at query time, which is fine because ClickHouse is fast and these layers aren't queried directly by users. The marts are incremental, and this is where ClickHouse bites if you copy a generic dbt model: an incremental model on ClickHouse needs a table engine and a sort key, or you get either an error or a table that silently accumulates duplicates.

{{ config(
    materialized='incremental',
    engine='MergeTree()',
    order_by='(event_date, entity_id)',
    incremental_strategy='append'
) }}

select *
from {{ ref('int_events_enriched') }}
{% if is_incremental() %}
where event_date > (select max(event_date) from {{ this }})
{% endif %}

The order_by is the MergeTree sort key and matters for query speed later; pick the columns you'll filter and group on most. The incremental filter keeps each run cheap by only pulling rows newer than what's already there — a date cursor is the reliable choice. If your source can restate history, use the delete+insert strategy on a unique key instead of append, or a weekly full refresh, so late-arriving corrections don't get stranded.

dev vs prod without leaking secrets

Two targets in one profiles.yml, which is gitignored — only a profiles.yml.example is committed. Dev runs from my laptop against ClickHouse over an SSH tunnel; prod runs from the orchestrator against the ClickHouse host's internal address. Same models, different target, no credentials in git.

# dev: SSH tunnel  ->  ssh -L 8123:localhost:8123 clickhouse-host
my_project:
  target: dev
  outputs:
    dev:
      type: clickhouse
      host: localhost
      port: 8123
      schema: dev
    prod:
      type: clickhouse
      host: 10.0.0.10   # internal address, reachable from the orchestrator
      port: 8123
      schema: marts

The gotchas generic dbt tutorials miss

Three things cost me time. First, incremental without an engine + order_by — covered above, but it's the number-one surprise coming from Snowflake. Second, duplicates on re-runs: ClickHouse doesn't enforce primary keys, so an append-incremental that reprocesses overlapping rows will duplicate them silently — your cursor logic is the only thing protecting you, so test it. Third, not every dbt package works: some community packages assume Postgres or Snowflake SQL dialect and quietly emit invalid ClickHouse SQL; dbt-utils mostly works, but verify anything else against a real run rather than trusting it compiles.

Frequently asked questions

Is the dbt-clickhouse adapter production-ready?

Yes, for standard modelling — it's maintained by ClickHouse and supports the engines, incremental strategies and materialized views you actually need. It's younger than the Snowflake/BigQuery adapters, so you may hit an occasional rough edge, but nothing that blocks a normal staging/marts project.

Why views for staging instead of tables?

Views cost no storage and always reflect the current source, and staging isn't queried directly by users — so materialising it as a table would just add storage and a refresh step for no benefit. Reserve real tables (incremental) for the marts that get queried repeatedly.

How do I avoid duplicate rows in incremental models?

ClickHouse doesn't enforce uniqueness, so you can't rely on a primary key to dedup. Either keep the incremental filter strictly monotonic (a date/timestamp cursor with a clean > comparison), use the delete+insert strategy on a unique key, or run a periodic full refresh. Always test a re-run over overlapping data and count rows.

Can I use dbt Cloud with ClickHouse?

The adapter works the same underneath, but I run dbt Core because it's just a package the orchestrator invokes — no extra service, no per-seat cost, and the whole thing stays self-hosted. For a solo or small setup, Core plus an orchestrator is all you need.