ClickHouse Audit

Operation

Inspect the selected behavior and report evidence-backed defects; do not change product code.

Goal and scope

Audit ClickHouse table engines, partition/order keys, ingestion/deduplication, query pruning, replication/mutations, and workload limits as one analytics contract.

Domain invariants

• Table engine, partition key, ORDER BY/primary key, sampling, TTL, codecs, and schema match dominant filters, data lifecycle, update model, and storage scale.
• Partition cardinality stays bounded and does not substitute for the ORDER BY key; queries can prune parts/granules using the actual predicates.
• Ingestion batch/async-insert, retries, dedup token/window, block identity, Distributed routing, and source offset define an explicit duplicate/loss contract.
• Replacing/Collapsing/Aggregating engines and materialized views encode their eventual merge semantics; users do not assume immediate uniqueness or final state without appropriate query logic.
• Queries bound scanned rows/bytes, joins, aggregation state, sorting, concurrency, timeout, memory, spill, and result size under representative cardinality/skew.
• Replication, quorum expectations, replica lag, Distributed tables, merges, part counts, disk, mutations, and failed inserts are observable and recoverable.
• Schema evolution/backfill/materialized-view changes account for existing parts, mixed writers/readers, mutation cost, and rollback/forward-fix.
• Connection pools, users/roles, quotas, settings profiles, workload scheduling, and read-only/tenant boundaries are deployment-wide and least privilege.

Audit method

1. Map source batch/offset → insert path → local/distributed table → parts/replicas/views → query/user/settings → export/consumer.
2. Inspect table DDL and representative EXPLAIN/query-log/system-table evidence for pruning, rows/bytes, memory, parts, merges, and latency.
3. Trace retry/duplicate windows, partial insert, async insert acknowledgement, replica failure, Distributed queue, and source checkpoint boundaries.
4. Evaluate partition/key choices and materialized-view semantics against real filters, time ranges, tenant skew, late data, and update/delete requirements.
5. Calculate concurrent connections/queries/ingestion, quotas, memory, disk, merge bandwidth, and mutation/backfill pressure.

Handoff

Retain DDL/settings, source checkpoint/batch identity, query and representative data shape. Identify the first mismatch between acknowledged ingestion, engine-visible rows, interpreted result and required budget. Feed that boundary and observable acceptance into Improve ClickHouse. A resource counter or delayed merge alone is not a defect until it violates the selected query/lifecycle contract.