Handle data lifecycle in ClickHouse — TTL expiration, data deletion (GDPR),
column-level encryption, and audit logging with real ClickHouse SQL.
Use when implementing data retention, GDPR deletion requests,
or managing sensitive data in ClickHouse.
Trigger: "clickhouse data retention", "clickhouse TTL", "clickhouse GDPR",
"delete data clickhouse", "clickhouse data lifecycle", "clickhouse PII".
Handle data lifecycle in ClickHouse — TTL expiration, data deletion (GDPR),
column-level encryption, and audit logging with real ClickHouse SQL.
Use when implementing data retention, GDPR deletion requests,
or managing sensitive data in ClickHouse.
Trigger: "clickhouse data retention", "clickhouse TTL", "clickhouse GDPR",
"delete data clickhouse", "clickhouse data lifecycle", "clickhouse PII".
-- Column-level TTL (null out PII after 30 days, keep the row)
ALTER TABLE
COLUMN
DEFAULT
''
+
INTERVAL
30
DAY
-- Force TTL cleanup now (normally runs during merges)
TABLE
FINAL
Step 2: Data Deletion for GDPR/CCPA
-- Option A: Lightweight DELETE (ClickHouse 23.3+)-- Marks rows as deleted without rewriting parts immediatelyDELETEFROM analytics.events WHERE user_id =42;
-- Option B: ALTER TABLE DELETE (mutation — rewrites parts in background)ALTER TABLE analytics.events DELETEWHERE user_id =42;
-- Check mutation progressSELECT
database, table, mutation_id, command,
is_done, parts_to_do, create_time
FROM system.mutations
WHERENOT is_done
ORDERBY create_time DESC;
-- Option C: Drop entire partitions (fastest for bulk deletion)-- First, check what partitions existSELECTpartition, count() AS parts, sum(rows) ASrows,
min(min_time) AS from_time, max(max_time) AS to_time
FROM system.parts
WHERE database ='analytics'ANDtable='events'AND active
GROUPBYpartitionORDERBYpartition;
ALTER TABLE analytics.events DROPPARTITION'202401';
Important notes on ClickHouse deletions:
DELETE FROM is lightweight but still creates mutations internally
Mutations rewrite data parts in the background — not instant
For GDPR compliance, use ALTER TABLE DELETE and verify via system.mutations
Partitioned data is fastest to bulk-delete via DROP PARTITION
Step 3: Data Masking and Anonymization
-- Create a view that masks PII for analyst accessCREATEVIEW analytics.events_masked ASSELECT
event_id,
event_type,
sipHash64(user_id) AS user_id_hash, -- One-way hash
JSONExtractString(properties, 'url') AS url, -- Extract safe fields only-- Mask email: show domain only
concat('***@', substringAfter(email, '@')) AS masked_email,
created_at
FROM analytics.events;
-- Row-level masking with dictionariesCREATE DICTIONARY analytics.pii_allowlist (
user_id UInt64,
can_see_pii UInt8
)
PRIMARY KEY user_id
SOURCE(CLICKHOUSE(TABLE'pii_allowlist'))
LIFETIME(MIN 300 MAX 600)
LAYOUT(FLAT());
Step 4: User Data Export (DSAR)
import { createClient } from'@clickhouse/client';
asyncfunctionexportUserData(userId: number): Promise<Record<string, unknown[]>> {
const client = createClient({ url: process.env.CLICKHOUSE_HOST! });
// Export all user data from all tablesconst tables = ['events', 'sessions', 'purchases'];
constresult: Record<string, unknown[]> = {};
for (const table of tables) {
const rs = await client.query({
query: `SELECT * FROM analytics.${table} WHERE user_id = {uid:UInt64}`,
query_params: { uid: userId },
format: 'JSONEachRow',
});
result[table] = await rs.json();
}
return result;
}
// GDPR: Delete all user dataasyncfunctiondeleteUserData(userId: number): Promise<void> {
const client = createClient({ url: process.env.CLICKHOUSE_HOST! });
const tables = ['events', 'sessions', 'purchases'];
for (const table of tables) {
await client.command({
query: `ALTER TABLE analytics.${table} DELETE WHERE user_id = {uid:UInt64}`,
query_params: { uid: userId },
});
}
// Log the deletion for compliance audit trailawait client.insert({
table: 'analytics.gdpr_audit_log',
values: [{
user_id: userId,
action: 'DELETE_ALL',
tables_affected: tables.join(','),
requested_at: newDate().toISOString().replace('T', ' ').slice(0, 19),
}],
format: 'JSONEachRow',
});
}
Step 5: Audit Trail Table
-- Immutable audit log (no deletes, no TTL)CREATE TABLE analytics.audit_log (
log_id UUID DEFAULT generateUUIDv4(),
action LowCardinality(String), -- 'query', 'delete', 'export', 'schema_change'
actor String, -- User or service name
target String, -- Table or resource
details String CODEC(ZSTD(3)), -- JSON details
ip_address IPv4,
logged_at DateTime DEFAULT now()
)
ENGINE = MergeTree()
ORDERBY (action, logged_at)
PARTITIONBY toYYYYMM(logged_at);
-- No TTL — audit logs must be retained-- Query audit trailSELECT logged_at, actor, action, target, details
FROM analytics.audit_log
WHERE action ='DELETE_ALL'ORDERBY logged_at DESC
LIMIT 50;
Step 6: Retention Monitoring
-- Data retention overviewSELECT
database, table,
result_ttl_expression AS ttl,
formatReadableSize(sum(bytes_on_disk)) AS size,
min(p.min_time) AS oldest_data,
max(p.max_time) AS newest_data,
dateDiff('day', min(p.min_time), max(p.max_time)) AS days_span
FROM system.tables t
LEFTJOIN system.parts p ON t.database = p.database AND t.name = p.table AND p.active
WHERE t.database ='analytics'GROUPBY database, table, result_ttl_expression
ORDERBYsum(bytes_on_disk) DESC;
-- Find tables missing TTLSELECT database, name AStable, engine
FROM system.tables
WHERE database ='analytics'AND engine LIKE'%MergeTree%'AND result_ttl_expression ='';
