Skip to content

chkit drift

Compares your local snapshot against the live ClickHouse schema and reports any differences. Requires both a snapshot file and a clickhouse configuration block.

chkit drift [flags]
FlagTypeDefaultDescription
-t, --table <selector>string—Scope drift detection to matching tables

Global flags documented on CLI Overview.

  1. Reads snapshot.json from metaDir. If no snapshot exists, the command fails with an error: “Snapshot not found. Run ‘chkit generate’ before drift checks.”
  2. Requires clickhouse config. If missing, fails with: “clickhouse config is required for drift checks.”
  3. Connects to ClickHouse. If the server’s MergeTree index_granularity default is not clickhouse.indexGranularity (8192 by default), fails with a message that names the value to set.
  4. Introspects the live schema
  5. Compares expected (snapshot) vs actual (live) objects, table shapes, and view and materialized view queries
  6. Reports drift at both the object level and the table level
  • Objects: every table, view, materialized view and dictionary in the snapshot exists in ClickHouse with the same kind. An object in the schema’s databases that the snapshot does not list reports extra_object.
  • Tables: columns matched by name, with their type, default, default kind, comment and codec; the engine, ORDER BY, PRIMARY KEY, PARTITION BY, unique key and TTL; skip indexes, projections and constraints; and the settings the schema declares.
  • Views and materialized views: the query, and a materialized view’s TO target; see View and materialized view queries.
  • Comments: the comment of every table, view, materialized view and dictionary, compared with system.tables.comment. A changed comment reports comment_mismatch with the schema’s comment in expected and the live one in actual. A changed column comment reports changed_column.

A server-side change to any of these reports no drift, so neither chkit drift nor chkit check --strict fails on it:

  • A view or materialized view query in which ClickHouse hid a secret as '[HIDDEN]', or that the server can’t format.
  • The REFRESH clause of a refreshable materialized view.
  • Column order: columns are matched by name.
  • Dictionary definitions: attributes, primary key, SOURCE, LAYOUT, LIFETIME, RANGE and settings.
  • Table settings the schema does not declare.

Check these properties against SHOW CREATE output.

When comparing engines, SharedMergeTree is normalized to MergeTree. This prevents false positives on managed environments (e.g. ObsessionDB) where the server transparently substitutes SharedMergeTree for MergeTree.

Column types are compared as rendered SQL types, in the spacing ClickHouse stores: Decimal(10,2) matches the stored Decimal(10, 2), while text in quotes (an Enum label, a time zone) is compared as written. type: 'Nullable(String)' and type: 'String', nullable: true both render Nullable(String) and do not report drift. Changes to nullability or the underlying type still report changed_column.

ClickHouse rewrites SQL expressions when it stores them — it spaces argument separators and operators, adds precedence parentheses, and rewrites INTERVAL 5 YEAR to toIntervalYear(5). So a skip index, PARTITION BY, ORDER BY, or TTL expression written as cityHash64(a,b) is stored as cityHash64(a, b). To avoid reporting these as drift, drift normalizes expressions through ClickHouse’s own formatter before comparing, so equivalent expressions match regardless of spelling. When the connected server can’t format an expression, comparison falls back to plain text matching.

The query of each view and materialized view in the snapshot is compared with the query ClickHouse stores for it (as_select in system.tables), and a materialized view’s TO target with the live one. ClickHouse stores a view’s query reformatted, so both queries go through ClickHouse’s own formatter first and a reformatted but equivalent query matches. ClickHouse also qualifies every unqualified table name with the database of the connection that created the view, so FROM events is stored as FROM analytics.events, and likewise the dictionary name in dictGet('users', …), dictHas and the other dict* functions, stored as dictGet('analytics.users', …); drift drops that qualifier for the database of its own connection, as the two spellings name the same object. When the server can’t format a query, drift does not compare it rather than report drift on a query that may be the same. Nor does it compare a query in which ClickHouse replaced a secret, such as a table function’s password, with '[HIDDEN]': that query never matches the schema, and formatting the schema’s query would print the secret in the drift output and log it in system.query_log.

A changed query reports query_mismatch and a changed target target_mismatch, with the compared values in expected and actual. A materialized view recreated on the server with an inner table instead of its TO target reports target_mismatch with an empty actual.

ORDER BY, PRIMARY KEY, the unique key and PARTITION BY are compared token by token with the clauses ClickHouse stores, ignoring whitespace, comments, one pair of parentheses around the whole clause, and how a name is quoted (`a\x41` and aA are the same column; a bare NULL is the literal, not a column named null). A quoted name keeps its commas and spaces: a key on a column named a, b is not the key on columns a and b, and a column named a b is not ab.

ClickHouse does not store SQL comments. chkit removes them from TTL, partition, skip index, and projection expressions, and from expression column defaults, before comparing them with the live table, so a comment that whitespace separates from the rest of the clause, such as one at the end of a line, does not read as drift. Comment markers inside a literal column default (default: 'a -- b') are part of the value. See SQL fragments.

Column defaults are compared token by token with default_expression in system.columns. chkit removes the comments from an expression default ({ expression } or fn:), then ignores whitespace, outer parentheses, and quotes around plain identifiers, so now()+1 matches the stored now() + 1. A literal default is compared as a value, as generate compares it: default: 'web' matches the stored 'web', default: 1e21 the stored 1e21, and a large integer compares exactly. ClickHouse stores some expressions in a canonical form, and an expression written another way reports changed_column although the default is the same: NOW() is stored as now(), x::String as CAST(x, 'String'), and now() + INTERVAL 1 DAY as now() + toIntervalDay(1). Write expression defaults in the stored form; see default.

Column codecs are compared with compression_codec in system.columns. ClickHouse fills in some codec arguments and drops others when it stores a codec: ZSTD is stored as ZSTD(1), LZ4HC as LZ4HC(0) (the default level, 9), DoubleDelta(4) as DoubleDelta outside verbatim chains, FPC(12, 8) as FPC(12) and T64('byte') as T64, and a bare Delta or Gorilla gets the byte width of the column’s values (Delta(8) on UInt64; still bare on a Tuple of mixed widths). chkit compares both sides in this stored form, the same way generate compares codecs, so these spellings do not report drift. A codec added, removed, or changed on the server reports changed_column, including a changed ZSTD window (ZSTD(3, 24) to ZSTD(3)) or Delta width. The atoms of a raw codec that name a typed codec are compared like it (codec.raw('Delta, Default') matches Delta(8), Default on a UInt64 column); other atoms are compared as written, without whitespace.

Object-level drift:

CodeMeaning
missing_objectExpected object not found in ClickHouse
extra_objectObject in ClickHouse not in snapshot
kind_mismatchObject exists but is a different kind (e.g. table vs view)
query_mismatchA view or materialized view query differs from the snapshot
target_mismatchA materialized view writes to a different TO table than the snapshot
comment_mismatchA table, view, materialized view or dictionary comment differs from the snapshot

Table-level drift:

CodeMeaning
missing_columnColumn in snapshot not found in live table
extra_columnColumn in live table not in snapshot
changed_columnColumn exists but type, default, column kind, comment, or codec differs
setting_mismatchTable setting value differs
index_mismatchIndex definition differs
ttl_mismatchTTL expression differs
engine_mismatchTable engine differs (after normalization)
primary_key_mismatchPrimary key expression differs
order_by_mismatchORDER BY expression differs
partition_by_mismatchPARTITION BY expression differs
unique_key_mismatchUnique key expression differs
projection_mismatchProjection definition differs
constraint_mismatchA CHECK or ASSUME constraint is missing, extra, or differs in kind or expression

Check for drift:

Terminal window
chkit drift

Check drift for a specific table:

Terminal window
chkit drift --table analytics.events

JSON output for CI:

Terminal window
chkit drift --json
CodeMeaning
0Always succeeds (drift is reported, not enforced)

Use chkit check with failOnDrift to enforce drift-free state in CI.

{
"command": "drift",
"schemaVersion": 1,
"scope": { "enabled": false },
"snapshotFile": "./chkit/meta/snapshot.json",
"expectedCount": 5,
"actualCount": 6,
"drifted": true,
"missing": ["table:analytics.sessions"],
"extra": ["table:analytics.temp_import"],
"kindMismatches": [],
"objectDrift": [
{ "code": "missing_object", "object": "analytics.sessions", "expectedKind": "table", "actualKind": null },
{
"code": "query_mismatch",
"object": "analytics.orders_mv",
"expected": "SELECT day, amount FROM orders WHERE status != 'Refunded'",
"actual": "SELECT day, amount FROM orders WHERE (status != 'Refunded') AND (amount > 0)"
}
],
"tableDrift": [
{
"table": "analytics.events",
"reasonCodes": ["extra_column", "setting_mismatch"],
"missingColumns": [],
"extraColumns": ["debug_flag"],
"changedColumns": [],
"settingDiffs": [{ "name": "index_granularity", "expected": "8192", "actual": "4096" }],
"indexDiffs": [],
"ttlMismatch": false,
"engineMismatch": false,
"primaryKeyMismatch": false,
"orderByMismatch": false,
"uniqueKeyMismatch": false,
"partitionByMismatch": false,
"projectionDiffs": [],
"constraintDiffs": []
}
]
}