ClickHouse Data Handling — Worked Examples
End-to-end scenarios that chain the individual steps from implementation.md into complete, auditable workflows.
Example 1: Fulfilling a GDPR "right to erasure" request
A user (user_id 42) submits an erasure request. The compliant flow is export-then-delete-then-log so the request is provable after the fact.
-- 1. Confirm what data exists for the subject (scope the request)
SELECT partition, count() AS rows
FROM system.parts
WHERE database = 'analytics' AND table = 'events' AND active
GROUP BY partition;
-- 2. Delete across every table holding the subject's rows (mutation-based,
-- verifiable — the compliant path, not lightweight DELETE)
ALTER TABLE analytics.events DELETE WHERE user_id = 42;
ALTER TABLE analytics.sessions DELETE WHERE user_id = 42;
-- 3. Verify the mutations completed before reporting the request as fulfilled
SELECT table, mutation_id, is_done, parts_to_do
FROM system.mutations
WHERE database = 'analytics' AND command LIKE '%user_id = 42%'
ORDER BY create_time DESC;
-- 4. Write an immutable audit record (this row has NO TTL and is never deleted)
INSERT INTO analytics.audit_log (action, actor, target, details)
VALUES ('DELETE_ALL', 'privacy-service', 'user:42',
'{"reason":"gdpr_erasure","request_id":"DSAR-2026-0042"}');Run the full delete + audit-log insert through the TypeScript deleteUserData
helper in implementation.md Step 4 when the request comes
from an application rather than a console.
Example 2: Standing up a retention-safe events table from scratch
-- Hot/cold/delete tiering plus PII column-TTL in one table definition
CREATE TABLE analytics.events (
event_id UUID DEFAULT generateUUIDv4(),
event_type LowCardinality(String),
user_id UInt64,
email String,
properties String CODEC(ZSTD(3)),
created_at DateTime DEFAULT now()
)
ENGINE = MergeTree()
ORDER BY (event_type, created_at)
PARTITION BY toYYYYMM(created_at)
TTL created_at + INTERVAL 7 DAY TO VOLUME 'hot',
created_at + INTERVAL 30 DAY TO VOLUME 'cold',
created_at + INTERVAL 365 DAY DELETE,
-- null out the raw email at 30 days but keep the analytics row
created_at + INTERVAL 30 DAY RECOMPRESS CODEC(ZSTD(1));
ALTER TABLE analytics.events
MODIFY COLUMN email String DEFAULT ''
TTL created_at + INTERVAL 30 DAY;Example 3: Auditing which tables are missing a retention policy
-- Any MergeTree table in the analytics DB with no TTL is a compliance gap
SELECT database, name AS table, engine
FROM system.tables
WHERE database = 'analytics'
AND engine LIKE '%MergeTree%'
AND result_ttl_expression = '';Feed the result into a review: business-metric tables may legitimately omit TTL, but any table holding PII that appears here needs a column-TTL or masking view added (see implementation.md Steps 1 and 3).