Safe DDL on Large Warehouse Tables

A dbt model can be rebuilt from its select, so a mistake costs a rerun. The source tables under it hold data nothing regenerates. On a warehouse, those tables are large, so the question before every change to one is which statements the engine can satisfy by editing metadata, and which ones rewrite every part on disk.

This page answers that on ClickHouse with a 5,000,000-row table, then shows the two Atlas controls that stop a bad change: migration linting in CI and a pre-migration check at apply time. Everything below was run through Atlas against that table.

Which ALTERs Rewrite Data

The table holds 5,000,000 rows in 5 active parts, 115.08 MiB on disk. Each change below was written into the desired state, planned with atlas migrate diff, and applied with atlas migrate apply. After each apply, system.mutations says whether ClickHouse rewrote data:

SELECT command, is_done, parts_to_do FROM system.mutations
WHERE database = 'bench' AND table = 'events'

Change in the schema file	Statement Atlas planned	Mutations created
New column session_id String	ALTER TABLE events ADD COLUMN session_id String	0
event_type to LowCardinality(String)	ALTER TABLE events MODIFY COLUMN event_type LowCardinality(String)	1
user_id from UInt64 to String	ALTER TABLE events MODIFY COLUMN user_id String	1

Adding a column created no mutation: ClickHouse recorded the new column in metadata and left the parts alone. Both type changes created a mutation that rewrote the column across all five parts (is_done = 1, parts_to_do = 0 once finished).

The two statements look equally small in a diff, and one of them rewrote 115 MiB.

note

This page publishes mutation counts rather than timings. On your own table, size the risk from the part count and bytes the mutation has to rewrite.

warning

ClickHouse mutations run in the background unless a statement sets mutations_sync. A migration that returns quickly has not necessarily finished rewriting data, so check system.mutations for is_done = 0 before treating a type change as complete. See the ClickHouse ALTER documentation for the semantics.

What Lint Catches, and What It Does Not

Dropping a column from the same table is caught before it reaches any warehouse. With linting configured to throw errors on destructive changes:

atlas.hcl

env "bench" {
  url = getenv("BENCH_URL")
  dev = getenv("BENCH_DEV_URL")

  schema {
    src = "file://schema.bench.hcl"
  }

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

  lint {
    destructive {
      error = true
    }
  }
}

atlas migrate lint exits non-zero:

atlas migrate lint --env bench --latest 1

Analyzing changes from version 20260812142324 to 20260812142457 (1 migration in total):

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

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

The lint is answering "does this destroy data or break compatibility", not "how much data does this rewrite". The type changes would produce no diagnostics found on a lint check. The mutation question stays yours, which is why the table in the previous section belongs in your review checklist.

Block It at Apply Time

Lint runs in CI against the migration file. A pre-migration check runs against the database that is about to be changed, which is the only place that can know whether the column still holds data. Rewrite the migration as a txtar archive with the assertion lint suggested:

migrations/20260812142457_drop_session_id.sql

-- atlas:txtar

-- checks.sql --
-- The column must hold no data before it is dropped.
SELECT NOT EXISTS(SELECT 1 FROM bench.events WHERE session_id != '');

-- migration.sql --
ALTER TABLE `events` DROP COLUMN `session_id`;

The check lives in the migration file, so there is nothing to add to atlas.hcl, and it runs only when this version is applied. Run atlas migrate hash after editing a migration file, then apply:

atlas migrate apply --env bench

With 50 of the 5,000,000 rows carrying a value, the check refuses the migration and the ALTER never runs:

Migrating to version 20260812142457 from 20260812142324 (1 migrations in total):

  -- checks before migrating version 20260812142457
    -> SELECT NOT EXISTS(SELECT 1 FROM bench.events WHERE session_id != '');
    check assertion "SELECT NOT EXISTS(SELECT 1 FROM bench.events WHERE..." returned false

  -------------------------
  -- 3.154923023s
  -- 1 migration with errors
  -- 1 check error

Once the column is genuinely empty, the same command passes the check and applies the drop:

  -- checks before migrating version 20260812142457
    -> SELECT NOT EXISTS(SELECT 1 FROM bench.events WHERE session_id != '');
  -- ok (3.847545858s)

  -- migrating version 20260812142457
    -> ALTER TABLE `events` DROP COLUMN `session_id`;
  -- ok (5.816381035s)

  -------------------------
  -- 6.951178169s
  -- 1 migration
  -- 1 check
  -- 1 sql statement

info

Adding the check does not clear the lint diagnostic: atlas migrate lint still reported DS103 with the checks.sql entry in place. Treat them as two independent gates. If you want CI to pass on a reviewed and guarded drop, decide it explicitly with a destructive-change policy rather than by removing the check.

Reviewing a Source-Table Change

1. Read the planned statement, not the schema diff. atlas migrate diff writes the exact SQL. Whether it is metadata-only or a mutation is visible there.
2. Let lint gate the destructive cases. Drops and narrowing changes fail CI on their own.
3. Add a pre-migration check when data decides safety. An assertion against the live table is the only gate that can see the rows.
4. Size the rewrite before applying. For a type change, the cost is the parts and bytes the mutation has to rewrite, which you can read from system.parts beforehand.
5. Rerun the dbt models that read the column. Atlas does not know your model DAG, so dbt build --select source_status:fresher+ or a targeted dbt run is what confirms the transformation side still works.

Next Steps

Schema Management for dbt Projects

The project these commands run in: source tables, lookup data, grants, CI

Pre-migration Checks

The txtar format, assertion policies, and multiple check files

Lint Analyzers

Every analyzer and diagnostic code, including DS103

Destructive Change Policy

Decide explicitly which destructive changes CI may accept

Custom Schema Policy

Write your own rules for what CI rejects

ClickHouse Migrations

Declarative and versioned workflows for ClickHouse

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