Skip to main content

ClickHouse and dbt: Managing the Schema Layer

On ClickHouse, the schema layer under a dbt project carries decisions the models cannot express. A source table's engine and sort order determine how every downstream model reads it, grants are server-level objects no dbt profile creates, and dimension tables have rows that belong in version control.

This page covers those ClickHouse specifics. The setup guide sets up the project and the exclude patterns that keep Atlas out of dbt's models. Everything here assumes that setup and drops into the details that only matter on ClickHouse.

Sort Order Is a One-Time Decision​

A source table's primary_key fixes its sort order on disk. Getting it right matters more here than on an OLTP database, because changing it later is not an in-place operation. Take a source table Atlas manages:

schema.bench.hcl
table "events" {
  schema = schema.bench
  engine = MergeTree
  // ... columns
  primary_key {
    columns = [column.occurred_at, column.event_id]
  }
}

Reordering those two columns to [column.user_id, column.occurred_at] produces this plan:

atlas migrate diff change_order_by --env bench
-- Create "events_tmp" table
CREATE TABLE `events_tmp` (
  `event_id` UUID,
  `user_id` String,
  `event_type` LowCardinality(String),
  `occurred_at` DateTime
) ENGINE = MergeTree
PRIMARY KEY (`user_id`, `occurred_at`) ORDER BY (`user_id`, `occurred_at`) SETTINGS index_granularity = 8192;
-- Copy data from "events" to "events_tmp" table
INSERT INTO `events_tmp` SELECT * FROM `events`;
-- Swap table "events" with "events_tmp"
EXCHANGE TABLES `events` AND `events_tmp`;
-- Drop "events_tmp" table
DROP TABLE `events_tmp`;

Atlas plans the rebuild correctly, and EXCHANGE TABLES makes the swap atomic. It is still a full copy of the table.

warning

atlas migrate lint reported no diagnostics found for that four-statement migration. Lint checks for destructive and incompatible changes, not for how much data a change moves, so a sort-order change reaches CI looking as small as a comment edit. Review the planned SQL, not just the schema diff.

Set the sort order based on how the models actually filter, and treat a later change as a data migration with a maintenance window rather than a routine one.

Grants dbt Needs​

dbt creates and drops objects in its target database on every run, so its grants have to cover objects that do not exist yet. That rules out table-level grants and makes schema scope the only workable form:

schema.ch.hcl
permission {
  for        = schema.analytics
  to         = role.dbt_runner
  privileges = [SELECT, INSERT, CREATE_TABLE, CREATE_VIEW, DROP_TABLE, DROP_VIEW, ALTER, OPTIMIZE, TRUNCATE]
}

permission {
  for        = schema.raw
  to         = role.dbt_runner
  privileges = [SELECT]
}

Check what ClickHouse holds after applying these changes (migrate diff, migrate apply):

docker exec warehouse clickhouse-client --password pass --query "SHOW GRANTS FOR dbt_runner"
GRANT SELECT, INSERT, ALTER, CREATE TABLE, CREATE VIEW, DROP TABLE, DROP VIEW, TRUNCATE, OPTIMIZE ON analytics.* TO dbt_runner
GRANT SELECT ON raw.* TO dbt_runner

Read-only on the sources, read-write plus DDL on its own database, and nothing else.

warning

ClickHouse roles and users are server-level objects, so the connection URL must not include a database path, and the server must allow the connecting user to manage access. In Docker that means CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1. Without it the first migration fails:

Code: 497. DB::Exception: default: Not enough privileges. To execute this query, it's necessary to have the grant CREATE ROLE ON *.*. (ACCESS_DENIED)

Materializations Read, They Do Not Define​

Every dbt materialization on ClickHouse builds an object inside dbt's own database from tables Atlas manages. An incremental dbt model shows the split by first reading a source table it did not create, then appending to a table Atlas never touches.

models/events_incremental.sql
{{ config(
    materialized='incremental',
    engine='MergeTree()',
    order_by='(occurred_at, event_id)',
    incremental_strategy='append'
) }}

select
    event_id,
    user_id,
    event_type,
    occurred_at
from {{ source('raw', 'events') }}

{% if is_incremental() %}
where occurred_at > (select max(occurred_at) from {{ this }})
{% endif %}

The first run built the model with all 5 source rows. Two rows were then loaded into the Atlas-managed source table, and the second run appended exactly those two:

Found 3 models, 3 sources, 530 macros
Concurrency: 1 threads (target='dev')
1 of 1 START sql incremental model `analytics`.`events_incremental` ............ [RUN]
1 of 1 OK created sql incremental model `analytics`.`events_incremental` ....... [OK in 9.64s]
Finished running 1 incremental model in 0 hours 0 minutes and 12.73 seconds (12.73s).
Completed successfully
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 TOTAL=1
SELECT (SELECT count() FROM raw.events)                 AS source_rows,
       (SELECT count() FROM analytics.events_incremental) AS model_rows
7	7

The engine and order_by in that config are dbt's business, set per model. The same attributes on raw.events belong to Atlas. Neither tool reads the other's setting.

Dimension Data as Code​

Country codes, currency lists, and status enums are read by models but generated by nobody. On ClickHouse, Atlas manages both the table and its rows:

schema.ch.hcl
data {
  table = table.country_codes
  rows = [
    { code = "US", name = "United States" },
    { code = "CA", name = "Canada" },
    { code = "IL", name = "Israel" },
    { code = "VN", name = "Vietnam" },
  ]
}

With data { mode = SYNC, include = ["raw.country_codes"], max_rows = 1000 } in the env, the rows arrive as a reviewable migration:

migrations/20260812073945_seed_country_codes.sql
-- Insert into "country_codes" table
INSERT INTO `raw`.`country_codes` (`code`, `name`) VALUES
  ('CA', 'Canada'),
  ('IL', 'Israel'),
  ('US', 'United States'),
  ('VN', 'Vietnam');
warning

Scope the block with include. Without it, mode = SYNC planned DELETE FROM raw.events WHERE ... covering every row of a fact table that has no data block at all. include keeps data management on the tables you meant.

If a lookup table is loaded from a CSV in your dbt project, leave it to dbt seed. It is then one of dbt's objects, inside dbt's database, and outside Atlas's scope.

Next Steps​

Schema Management for dbt Projects

The full worked example this page builds on

Safe DDL on Large Tables

Which ALTERs rewrite data, and how to block a destructive one

ClickHouse Migrations

Declarative and versioned workflows for ClickHouse

ClickHouse Cluster Mode

How Atlas manages schema changes across a ClickHouse cluster

ClickHouse Cloud

Connecting Atlas to a ClickHouse Cloud service

ClickHouse Seed Data

Lookup and dimension tables as code, with sync modes

Have questions? Feedback? Find our team on our Discord server.