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".
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
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 ='';
