clickhouse-data-handling

# ClickHouse Data Handling ## Overview Manage the full data lifecycle in ClickHouse: TTL-based expiration, GDPR/CCPA deletion, data masking, partition management, and audit trails. ## Prerequisites - ClickHouse tables with data (see `clickhouse-core-workflow-a`) - Understanding of your data retention requirements ## Instructions ### Step 1: TTL-Based Data Expiration ```sql -- Add TTL to expire data automatically CREATE TABLE analytics.events ( event_id UUID DEFAULT generateUUIDv4(), event_type LowCardinality(String), user_id UInt64, 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 90 DAY; -- Auto-delete after 90 days -- Add TTL to existing table ALTER TABLE analytics.events MODIFY TTL created_at + INTERVAL 90 DAY; -- Tiered storage TTL (hot → cold → delete) ALTER TABLE analytics.events MODIFY TTL created_at + INTERVAL 7 DAY TO VOLUME 'hot', created_at + INTERVAL 30 DAY TO VOLUME 'cold', created_at + INTERVAL 365 DAY DELETE; -- Column-level TTL (null out PII after 30 days, keep the row) ALTER TABLE analytics.events MODIFY COLUMN email String DEFAULT '' TTL created_at + INTERVAL 30 DAY; -- Force TTL cleanup now (normally runs during merges) OPTIMIZE TABLE analytics.events FINAL; ``` ### Step 2: Data Deletion for GDPR/CCPA ```sql -- Option A: Lightweight DELETE...