October 14, 20264 min read

Read a ClickHouse Schema as a Columnar Design

Run Ptah migrations on ClickHouse, inspect MergeTree ordering and defaults, and verify the SQL export and migration replay.

Simon HartSolutions Engineer

Ptah helps you plan, review, and apply database migrations. Try in your browser

A repeated key can be valid in ClickHouse. A newly added column can return a default for old rows without proving that every old data part was rewritten. Reading its schema as if it were an OLTP table can lead to the wrong review.

We used Ptah 0.13.0 and ClickHouse 26.8.2.7 to migrate a synthetic event table, query existing rows, and verify its exported SQL. We then replayed the migrations into an empty database and compared the live tables. The complete fixtures and recorded output are in the example files.

Review the storage design with the columns

Our first migration creates a MergeTree table:

CREATE TABLE events (event_date Date, event_id UInt64, amount_cents UInt32) ENGINE = MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, event_id);

PARTITION BY groups data by month. ORDER BY determines the sorting key; without a separate primary-key declaration, MergeTree uses that expression for its primary index too. It does not enforce row uniqueness. These properties are documented in the MergeTree reference.

We hashed the migration directory and applied its first pending migration with explicit nontransactional execution:

ptah migrations hash --dir migrations
ptah migrations up --db-url "$DB_URL" --migrations-dir migrations --verify-sum --tx-mode none --limit 1

--limit 1 leaves the currency migration pending, so the next inserts reach the original table before the new column exists.

The fixture inserts the same (event_date, event_id) twice. The native ClickHouse client returned the engine version followed by each ID’s count:

26.8.2.7
1  2
2  1

ID 1 has two rows. The sorting key helped define storage and lookup, but it did not reject the duplicate. If event deduplication is a business requirement, this plain MergeTree definition has not implemented it.

Add a default for the historical events

The next migration adds a low-cardinality currency column:

ALTER TABLE events ADD COLUMN currency LowCardinality(String) DEFAULT 'USD';

The synthetic dataset contains only USD amounts, so the default is a valid interpretation of its historical rows. That would be a business assumption to verify before applying the same change to real event data.

ptah migrations hash --dir migrations
ptah migrations up --db-url "$DB_URL" --migrations-dir migrations --verify-sum --tx-mode none

A native query grouped the old events by the new column. This is the exact aggregate result, extracted from the client output:

USD  3  6985

All three rows read as USD and total 6,985 cents. ClickHouse documents that an added column can be absent from existing data parts and have its default computed when read. Explicit materialization is a separate operation. Our query proves the returned values; it does not prove a physical rewrite of every old part. See the column alteration reference.

Inspect the actual table

Ptah can read the live table back as SQL:

ptah schema inspect --db-url "$DB_URL" --format sql --include events > inspected.sql

CREATE TABLE events (
  event_date Date,
  event_id UInt64,
  amount_cents UInt32,
  currency LowCardinality(String) DEFAULT 'USD'
) ENGINE = MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, event_id) SETTINGS index_granularity = 8192;

The result retains MergeTree, the monthly partition expression, the sorting key, the low-cardinality type, and the server’s index-granularity setting. Those details belong in review alongside column names. Changing a sorting or partitioning strategy is a different operation from this column addition.

Verify the SQL export and the migration replay

We then fed that inspected SQL into schema compare. Ptah 0.13.0 reports no differences for this table and exits zero:

ptah schema compare --db-url "$DB_URL" --schema-file inspected.sql --exit-code

Comparing schema from inspected.sql with database clickhouse://ptah:***@127.0.0.1:9000/analytics
=== SCHEMA COMPARISON ===

No schema differences detected.

The SQL source retains the key columns and MergeTree clauses. Ptah’s own migration history tables do not produce DROP statements in this comparison. This checks whether the declared schema matches the live table; it does not execute a change or compare event data.

For an independent structural check, we applied the same migrations to an empty analytics_replay database and compared the two live event tables:

ptah migrations up --db-url "$REPLAY_DB_URL" --migrations-dir migrations --verify-sum --tx-mode none
ptah schema diff --from "$DB_URL" --to "$REPLAY_DB_URL" --include events --format json

The recorded JSON contains an empty statements array and no reported changes. --include events keeps both sides focused on the application table. The replay database has the same table definition and zero event rows: migration replay recreates the schema, not the original dataset.

Make the engine’s behavior part of the example

For this table, review had to account for duplicate events, the meaning of the sorting key, and values synthesized from a column default. None of those questions is answered by seeing that an ALTER TABLE command exited zero.

Use the ClickHouse guide and review the engine and table design you intend to deploy. Keep the historical-data assumption beside the default, and verify the live schema after the migration.

Put Ptah to work

Example files

← All articles