ClickHouse Data Handling
Overview
Manage the full data lifecycle in ClickHouse: TTL-based expiration, GDPR/CCPA
deletion, data masking, partition management, and audit trails. This skill
produces migration SQL and TypeScript client code you write into your project,
then verifies the results against ClickHouse system.* tables.
The workflow below is the high-level path — each step links to the full,
copy-ready SQL/TypeScript in references/implementation.md,
with end-to-end scenarios in references/examples.md.
Prerequisites
Before starting, confirm you have:
- Populated ClickHouse tables to operate on (schema comes from the companion
skill
clickhouse-core-workflow-a).
- A written data-retention policy: how long each data class is kept, and which
columns hold PII. The Data Classification table maps
each class to its ClickHouse handling.
- ClickHouse 23.3+ if you plan to use lightweight
DELETE FROM; older versions
must use mutation-based ALTER TABLE ... DELETE.
- Access to
system.mutations and system.parts to verify deletions.
Instructions
Work the six steps in order for a new table, or jump to the one you need. Use
Write/Edit to place the generated SQL into a migration file (or the
TypeScript into your data-access layer), then run it against ClickHouse and
verify via the system.* queries. Full code for each step lives in
references/implementation.md.
-
TTL-based expiration — attach a TTL clause so data self-deletes, or use
tiered TO VOLUME storage (hot → cold → delete) and column-level TTL to null
out PII while keeping the row. Skeleton:
ALTER TABLE analytics.events
MODIFY TTL created_at + INTERVAL 90 DAY;
-
GDPR/CCPA deletion — choose lightweight DELETE FROM (23.3+), verifiable
ALTER TABLE ... DELETE (the compliant path), or DROP PARTITION for bulk.
Always confirm completion in system.mutations.
-
Masking & anonymization — expose a CREATE VIEW that sipHash64-hashes
identifiers and shows only email domains, gated by a dictionary allowlist.
-
DSAR export & delete — the TypeScript exportUserData / deleteUserData
helpers loop every table for one user_id and log each deletion.
-
Audit trail — an immutable, TTL-free audit_log table partitioned by
month so retention actions are provable.
-
Retention monitoring — a system.tables/system.parts join that reports
size, age span, and any MergeTree table missing a TTL.
Data Classification
| Category | Examples | Handling in ClickHouse |
|---|
| PII | Email, name, IP | Column-level TTL, masking views, deletion support |
| Sensitive | API keys, tokens | Never store in ClickHouse — use secret managers |
| Business | Event counts, metrics | Standard TTL, aggregate for long-term retention |
| Audit | Access logs | No TTL, immutable, partitioned by month |
Output
Applying this skill produces:
- Migration SQL —
CREATE TABLE/ALTER TABLE statements adding TTL clauses,
masking views, and the immutable audit_log table, ready to commit as a
migration file.
- TypeScript client code —
exportUserData and deleteUserData functions
for DSAR and erasure requests against @clickhouse/client.
- Verification queries —
system.mutations / system.parts / system.tables
SELECTs that prove a deletion finished and flag tables missing retention.
- An audit record — one immutable
audit_log row per compliance action.
Error Handling
| Issue | Cause | Solution |
|---|
| Mutation stuck | Large table rewrite | Check system.mutations, cancel if needed |
| TTL not expiring | No merges running | OPTIMIZE TABLE ... FINAL to force |
| DELETE not working | Old ClickHouse version | Use ALTER TABLE DELETE (mutation) |
| Export timeout | Too much user data | Add LIMIT or export in batches |
Examples
A minimal TTL attach — the smallest useful action:
ALTER TABLE analytics.events
MODIFY TTL created_at + INTERVAL 90 DAY;
OPTIMIZE TABLE analytics.events FINAL;
Full worked scenarios — a complete GDPR erasure (export → verifiable delete →
audit log), standing up a retention-safe table with tiered storage, and auditing
for tables missing a retention policy — are in
references/examples.md. The step-by-step SQL and
TypeScript each example composes lives in
references/implementation.md.
Resources
Next Steps
For role-based access control that restricts who can run these deletion and
export operations, see the companion skill clickhouse-enterprise-rbac. For the
table schemas these lifecycle rules attach to, see clickhouse-core-workflow-a.
