Skip to main content

Schema Management for dbt Projects

dbt builds models. Every dbt run issues the CREATE, REPLACE, and DROP statements for the tables and views its models materialize.

The schema layer underneath those models is a different matter. Databases, the source tables ingestion writes into, lookup tables other systems read, and the roles and grants dbt itself connects with are all outside any model's materialization. Nothing in a dbt project plans changes to them, reviews them, or tells you when the warehouse stopped matching what your code expects.

This guide manages that layer with Atlas against ClickHouse using versioned migrations. At the end, dbt run and Atlas are both able to operate on the same warehouse without conflicts.

Prerequisites​

  1. Docker
  2. Atlas installed:

To download and install the latest release of the Atlas CLI, simply run the following in your terminal:

curl -sSf https://atlasgo.sh | sh
  1. dbt with the ClickHouse adapter. The transcripts below come from dbt-core 1.11.12 with dbt-clickhouse 1.10.1
  2. An Atlas Pro account
Atlas Pro Feature

ClickHouse support, roles, users, and permissions are available to Pro users. Run atlas login to authenticate.

Start a Warehouse​

Run two containers with Docker. The first is the warehouse, while the second will serve as the dev database Atlas uses to compute diffs.

docker run -d --name warehouse \
  -e CLICKHOUSE_PASSWORD=pass \
  -e CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1 \
  -p 9000:9000 -p 8123:8123 \
  --ulimit nofile=262144:262144 \
  clickhouse/clickhouse-server:24.8-alpine

docker run -d --name warehouse-dev \
  -e CLICKHOUSE_PASSWORD=pass \
  -e CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1 \
  -p 9001:9000 \
  --ulimit nofile=262144:262144 \
  clickhouse/clickhouse-server:24.8-alpine
warning

CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1 is required. Without it the default user cannot manage access control, and the first migration fails on CREATE ROLE:

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

Export the two URLs:

export CLICKHOUSE_URL="clickhouse://default:pass@localhost:9000"
export CLICKHOUSE_DEV_URL="clickhouse://default:pass@localhost:9001"

Neither includes a database path. ClickHouse roles and users are server-level objects, so Atlas needs a server-scoped connection to manage them alongside the databases.

tip

Instead of running the second container yourself, you can point dev at docker://clickhouse/23.11 in the Atlas configuration file and let Atlas start and clean up the dev database per command. A long-lived container keeps the dev database on the same version as the warehouse.

Define the Schema Layer​

To start managing your schema as code with Atlas, begin by defining the desired state.

With dbt, the desired state will hold the objects dbt depends on, but not those it creates. Note that there are no model tables or views. The analytics schema is declared because dbt needs somewhere to build, and its contents are left empty on purpose.

The grants are on schema.analytics because dbt's model tables cannot be enumerated ahead of time. Schema-scope grants let Atlas manage dbt's access without knowing anything about dbt's models.

schema.ch.hcl
schema "default" {
}

schema "raw" {
}

schema "analytics" {
}

// Source tables: loaded by ingestion, read by dbt.

table "events" {
  schema = schema.raw
  engine = MergeTree
  column "event_id" {
    type = UUID
    null = false
  }
  column "user_id" {
    type = UInt64
    null = false
  }
  column "event_type" {
    type = String
    null = false
  }
  column "occurred_at" {
    type = DateTime
    null = false
  }
  primary_key {
    columns = [column.occurred_at, column.event_id]
  }
}

table "customers" {
  schema = schema.raw
  engine = MergeTree
  column "customer_id" {
    type = UInt64
    null = false
  }
  column "email" {
    type = String
    null = false
  }
  column "country_code" {
    type = String
    null = false
  }
  column "created_at" {
    type = DateTime
    null = false
  }
  primary_key {
    columns = [column.customer_id]
  }
}

// Lookup table: schema and rows both live in version control.

table "country_codes" {
  schema = schema.raw
  engine = MergeTree
  column "code" {
    type = String
    null = false
  }
  column "name" {
    type = String
    null = false
  }
  primary_key {
    columns = [column.code]
  }
}

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

// Access: dbt connects as a least-privilege user.

role "dbt_runner" {
}

role "bi_reader" {
}

user "dbt" {
  member_of = [role.dbt_runner]
}

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

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.analytics
  to         = role.bi_reader
  privileges = [SELECT]
}

Configure the Boundary​

Atlas's configuration file points to all the pieces needed to plan schema changes, including the URLs to our database and dev database, the path to our schema definition, linting behaviors, and so on.

Three details in this configuration file carry the dbt integration:

  • exclude uses the glob patterns documented for project files. Pattern format depends on the connection's scope. With the server-scoped URL, analytics matches the database itself and analytics.* matches the objects inside it. Excluding only analytics.* keeps the database under management while its contents are left to dbt.
  • mode turns on role and permission management, which is off by default.
  • data.include scopes lookup-data sync to the one table that has a data block. Without it, mode = SYNC also plans DELETE FROM raw.events for rows that no data block describes.
atlas.hcl
env "local" {
  url = getenv("CLICKHOUSE_URL")
  dev = getenv("CLICKHOUSE_DEV_URL")

  schema {
    src = "file://schema.ch.hcl"
    mode {
      roles       = true
      permissions = true
    }
  }

  // The boundary: dbt owns every object it materializes inside "analytics",
  // so Atlas ignores them. The "analytics" database itself stays managed.
  exclude = [
    "analytics.*",              // every object dbt builds
    "atlas_schema_revisions",   // Atlas migration history
    "atlas_schema_revisions.*",
  ]

  // Lookup table rows are part of the schema layer, not of any dbt model.
  data {
    mode     = SYNC
    include  = ["raw.country_codes"]
    max_rows = 1000
  }

  migration {
    dir = "file://migrations"
  }

  lint {
    destructive {
      error = true
    }
  }
}

Plan the First Migration​

Create the initial migration file:

atlas migrate diff init_warehouse --env local

Atlas writes one file covering the whole layer:

migrations/20260812073852_init_warehouse.sql
-- Create role "dbt_runner"
CREATE ROLE `dbt_runner`;
-- Create user "dbt"
CREATE USER `dbt` NOT IDENTIFIED;
-- Grant role "dbt_runner" to "dbt"
GRANT `dbt_runner` TO `dbt`;
-- Create role "bi_reader"
CREATE ROLE `bi_reader`;
-- Add new schema named "analytics"
CREATE DATABASE `analytics` ENGINE Atomic;
-- Grant on schema "analytics" to "bi_reader"
GRANT SELECT ON `analytics`.* TO `bi_reader`;
-- Grant on schema "analytics" to "dbt_runner"
GRANT ALTER, CREATE TABLE, CREATE VIEW, DROP TABLE, DROP VIEW, INSERT, OPTIMIZE, SELECT, TRUNCATE ON `analytics`.* TO `dbt_runner`;
-- Add new schema named "raw"
CREATE DATABASE `raw` ENGINE Atomic;
-- Grant on schema "raw" to "dbt_runner"
GRANT SELECT ON `raw`.* TO `dbt_runner`;
-- Create "country_codes" table
CREATE TABLE `raw`.`country_codes` (
  `code` String,
  `name` String
) ENGINE = MergeTree
PRIMARY KEY (`code`) ORDER BY (`code`) SETTINGS index_granularity = 8192;
-- Create "customers" table
CREATE TABLE `raw`.`customers` (
  `customer_id` UInt64,
  `email` String,
  `country_code` String,
  `created_at` DateTime
) ENGINE = MergeTree
PRIMARY KEY (`customer_id`) ORDER BY (`customer_id`) SETTINGS index_granularity = 8192;
-- Create "events" table
CREATE TABLE `raw`.`events` (
  `event_id` UUID,
  `user_id` UInt64,
  `event_type` String,
  `occurred_at` DateTime
) ENGINE = MergeTree
PRIMARY KEY (`occurred_at`, `event_id`) ORDER BY (`occurred_at`, `event_id`) SETTINGS index_granularity = 8192;

The NOT IDENTIFIED user is fine for a local warehouse. Authentication for a real deployment is out of scope here. See the user block reference for its attributes, and keep credentials in your secret store rather than in the schema file.

Apply the migration:

atlas migrate apply --env local

The lookup rows are a second migration, planned from the data block:

atlas migrate diff seed_country_codes --env local
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');

Apply the second migration and the grants are in place in ClickHouse:

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

Point dbt at the Layer​

dbt connects as the user Atlas created and builds into the database Atlas created. Nothing in the dbt project describes the source tables' DDL, only where to find them.

profiles.yml
analytics_demo:
  target: dev
  outputs:
    dev:
      type: clickhouse
      host: localhost
      port: 8123
      user: dbt
      password: ""
      schema: analytics
      secure: False

dbt debug confirms the connection the Atlas-managed grants allow:

adapter type: clickhouse
adapter version: 1.10.1
Connection:
  host: localhost
  port: 8123
  user: dbt
  schema: analytics
  Connection test: [OK connection ok]
All checks passed!

Build the models with dbt run:

Running with dbt=1.11.12
Registered adapter: clickhouse=1.10.1
Found 2 models, 3 sources, 530 macros
Concurrency: 1 threads (target='dev')
1 of 2 START sql view model `analytics`.`stg_events` ........................... [RUN]
1 of 2 OK created sql view model `analytics`.`stg_events` ...................... [OK in 0.05s]
2 of 2 START sql table model `analytics`.`daily_active_users` .................. [RUN]
2 of 2 OK created sql table model `analytics`.`daily_active_users` ............. [OK in 0.21s]
Finished running 1 table model, 1 view model in 0 hours 0 minutes and 0.81 seconds (0.81s).
Completed successfully
Done. PASS=2 WARN=0 ERROR=0 SKIP=0 NO-OP=0 TOTAL=2

The warehouse now holds objects from both tools:

analytics	daily_active_users	MergeTree
analytics	stg_events	View
raw	country_codes	MergeTree
raw	customers	MergeTree
raw	events	MergeTree

Prove the Boundary​

Confirm that even after running dbt run, Atlas does not find any discrepancies in the schema layer that would make it plan any more changes:

atlas schema diff --env local --from "$CLICKHOUSE_URL" --to "file://schema.ch.hcl"
Schemas are synced, no changes to be made.

Remove the single "analytics.*" line from exclude and run the same command against the same warehouse, and it is a different story:

-- Drop "stg_events" view
DROP VIEW `analytics`.`stg_events`;
-- Drop "daily_active_users" table
DROP TABLE `analytics`.`daily_active_users`;

This is the failure mode the exclude list prevents. Without it, every dbt model looks like an object that drifted into the warehouse, and Atlas plans to remove it.

Because dbt drops and recreates its models on each run, this is worth checking after a rebuild.

Change the Source Layer​

Ingestion starts sending a session identifier session_id. Add this column to the desired state:

schema.ch.hcl
table "events" {
  schema = schema.raw
  engine = MergeTree
  // ...
  column "session_id" {
    type = String
    null = false
  }
  primary_key {
    columns = [column.occurred_at, column.event_id]
  }
}

Create a new migration file:

atlas migrate diff add_session_id --env local
migrations/20260812081822_add_session_id.sql
ALTER TABLE `raw`.`events` ADD COLUMN `session_id` String;

Lint the migration:

atlas migrate lint --env local --latest 1
Analyzing changes from version 20260812073945 to 20260812081822 (1 migration in total):

  -- analyzing version 20260812081822
    -- no diagnostics found
  -- ok (36.945742ms)

  -------------------------
  -- 5.657337607s
  -- 1 version ok
  -- 1 schema change

Apply the migration and confirm the database's state:

atlas migrate apply --env local
atlas migrate status --env local
Migration Status: OK
  -- Current Version: 20260812081822
  -- Next Version:    Already at latest version
  -- Executed Files:  3
  -- Pending Files:   0

Destructive changes are blocked​

Removing the email column from raw.customers would produce a migration that lint refuses because the env sets destructive { error = true }:

migrations/20260812082333_drop_email.sql
ALTER TABLE `raw`.`customers` DROP COLUMN `email`;
atlas migrate lint --env local --latest 1
Analyzing changes from version 20260812081822 to 20260812082333 (1 migration in total):

  -- analyzing version 20260812082333
    -- destructive changes detected:
      -- L1: Dropping non-virtual column "email" https://atlasgo.io/lint/analyzers#DS103
    -- suggested fix:
      -> Add a pre-migration check to ensure column "email" is NULL before dropping it
  -- ok (5.696686ms)

  -------------------------
  -- 3.160935437s
  -- 1 version with errors
  -- 1 schema change
  -- 1 diagnostic

The command exits non-zero, which is what turns this into a CI gate. A source column that models still select is exactly the change worth stopping in review.

Wire into CI​

The migration directory is the artifact CI reviews. atlas migrate lint is the same command run above, so a pull request touching migrations/ gets the diagnostics as a comment:

.github/workflows/ci-atlas.yaml
name: Atlas
on:
  push:
    branches:
      - master
    paths:
      - 'migrations/*'
  pull_request:
    paths:
      - 'migrations/*'
permissions:
  contents: read
  pull-requests: write
jobs:
  atlas:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
        with:
          fetch-depth: 0
      - uses: ariga/setup-atlas@v0
        with:
          cloud-token: '${{ secrets.ATLAS_CLOUD_TOKEN }}'
      - uses: ariga/atlas-action/migrate/lint@v1
        with:
          dir: 'file://migrations'
          dir-name: 'warehouse'
          dev-url: 'docker://clickhouse/23.11'
        env:
          GITHUB_TOKEN: '${{ github.token }}'

This workflow gates schema-layer changes, and your existing dbt job keeps building models. They meet only where a model reads a source column, which is why the lint gate above matters more than any coordination between the two pipelines.

For push and deploy steps, and for the other platforms, see CI/CD on GitHub and the GitHub Actions reference.

What Atlas Does Not Do Here​

  • Atlas does not manage your models. Materializations belong to dbt, and Atlas is configured to ignore them. If you want a model's table under Atlas management, that means moving it out of dbt.
  • Atlas does not read dbt's manifest. Atlas does not know about ref(), source(), or the model DAG, so it cannot tell you which models a source-column change breaks. dbt build after the migration is what answers that.
  • Atlas does not run dbt. Atlas applies schema changes, your scheduler runs dbt.
  • Atlas does not replace dbt tests. dbt tests assert on data. Atlas schema tests assert on schema objects and migration behavior.

Next Steps​

Atlas vs dbt

Why these are not competing tools, and where the line falls

ClickHouse and dbt

Sort order on source tables, grants for dbt, and dimension data as code

Snowflake and dbt

Schemas, the bronze landing zone, and a role for dbt and its grants

Safe DDL on Large Tables

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

Warehouse Drift Detection

Detect a warehouse that stopped matching its schema while dbt rebuilds models

ClickHouse Security as Code

Manage roles, users, and grants through versioned migrations

Migration Linting

The analyzers behind the destructive change check

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