Naming and documenting a ClickHouse schema

Names in an analytical database are public API. They appear in dashboards, in other teams’ queries, in materialized views and in dictionaries, and unlike code they are very expensive to rename: a rename means a migration, a deprecation window and a hunt for every query nobody told you about. A convention written down on the first day is the cheapest piece of governance you will ever do. Here is one that works, and the argument for pairing it with comments, so that a name says what a thing is and a comment says what it means.

The rules

Databases

• Lowercase.
• One word where possible (analytics, billing, staging). A database name appears in every fully-qualified query, so every extra word is typed forever.

Tables and columns

• snake_case. Never mixed case: ClickHouse is case-sensitive for identifiers, and UserId versus userid becomes a bug somewhere.
• Tables are plural, as they hold many rows: events, users.
• Columns are singular, as each holds one value per row: user_id, not user_ids. (An actual array column is the exception, and then the plural tells you it is an array.)
• No abbreviations, unless everybody already knows the abbreviation.

A small, closed set of allowed abbreviations

Abbreviation	Means
id	identity
dt	datetime
num	number
desc	description
addr	address
tx	transaction

Keep this list short and written down. The question “is it tx_id or txid?” has one answer; whichever you choose, pick once. The underscore form is the better pick, because snake_case means words are separated, and tx_id is two words.

Why these and not others

• Plural tables, singular columns reads naturally in a query: SELECT user_id FROM users. Mixed up, it reads like a typo.
• A closed abbreviation list prevents qty, quantity, amt, amount and cnt all being in use in the same schema. People can search for a closed list; they cannot search for every possible abbreviation.
• Short database names matter more in ClickHouse than in many systems, because cross-database queries are routine and databases are a namespace, not a security boundary.

A name is not documentation

Even a perfect name leaves the important questions open. event_dt is a DateTime, but in which time zone? duration_ms is clearly milliseconds, but measured from what to what? user_id identifies a user, but is it the same identifier the billing database uses? None of this fits in a name, and putting it in a wiki page is how it gets lost: the page drifts from the schema within a quarter, and the person writing a query at 11 p.m. does not open the wiki.

ClickHouse lets you attach a comment to databases, tables, columns, views, materialized views and dictionaries. A comment has no effect on behaviour. Its value is that the documentation travels with the schema: it shows in SHOW CREATE, in DESCRIBE, and in system.tables and system.columns, and it survives backups and replication. Nobody has to find anything.

The mechanics are small. Column comments sit after the column definition and the table comment after the engine clauses; a database takes COMMENT in CREATE DATABASE:

CREATE DATABASE analytics COMMENT 'Product analytics';

CREATE TABLE analytics.events
(
    event_dt    DateTime COMMENT 'When the event happened (UTC)',
    user_id     UInt64   COMMENT 'Stable user identifier, not a session id',
    duration_ms UInt32   COMMENT 'Time spent on the page, milliseconds',
    Country     String
)
ENGINE = MergeTree
ORDER BY event_dt
COMMENT 'One row per user event';

Comments change later with metadata-only statements that finish instantly whatever the table size:

ALTER TABLE analytics.events MODIFY COMMENT 'One row per user event, written by the ingest pipeline';
ALTER TABLE analytics.events COMMENT COLUMN Country 'ISO 3166-1 alpha-2 country code';
ALTER DATABASE analytics MODIFY COMMENT 'Product analytics (events and sessions)';

The database form is recent: older servers treated a database comment as write-once, and ALTER DATABASE ... MODIFY COMMENT works on 26.4. Views have two quirks. The select must be wrapped in parentheses so the parser knows where the comment starts, and to comment a view’s columns you have to list them. A view is altered with ALTER TABLE, not ALTER VIEW:

CREATE VIEW analytics.recent_events
(
    user_id UInt64 COMMENT 'User who produced the event'
) AS (SELECT user_id FROM analytics.events)
COMMENT 'Events from the last day';

Materialized views and dictionaries take the same COMMENT clause at the end of their definition.

What a comment should say

• A column’s unit and time zone, not what its name already says.
• The scope of an identifier (“not a session id”) and any nullability or sentinel convention.
• Whether a column is derived or deprecated.
• For a table, what one row represents. This is the single most useful sentence in a schema.
• A pointer to an owner or a document when there is one. Comments are a catalogue, not an essay.

Make it enforceable

A convention nobody checks decays. The two halves of this essay meet in one query, because system.columns has every column on the server together with its comment:

SELECT database, `table`, name
FROM system.columns
WHERE database NOT IN ('system', 'information_schema', 'INFORMATION_SCHEMA')
  AND (name != lower(name)                       -- mixed case
       OR match(name, '[^a-z0-9_]')              -- anything but snake_case characters
       OR comment = '')                          -- no comment
ORDER BY database, `table`, name;

Run against the table above, it returns exactly one offender, the column that is both mixed case and undocumented:

┌─database──┬─table──┬─name────┐
│ analytics │ events │ Country │
└───────────┴────────┴─────────┘

The same table in system.tables catches objects without a table comment:

SELECT database, name FROM system.tables
WHERE database NOT IN ('system', 'information_schema', 'INFORMATION_SCHEMA') AND comment = '';

Run both in CI against a staging database, or on a schedule against production, and fail on a non-empty result. Add checks for your own rules: a regular expression for plural table names is hopeless, but a list of forbidden abbreviations is easy. A coverage report is a good dashboard for a data team too:

SELECT `table`, count() AS columns, countIf(comment != '') AS documented
FROM system.columns WHERE database = 'analytics' GROUP BY `table` ORDER BY `table`;

None of this is the only right answer. The right answer is the one that is written down, short enough to remember, kept in the schema where the queries are written, and checked by a machine.