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. 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.

1. 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;
   

2. 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.

3. Masking & anonymization — expose a CREATE VIEW that sipHash64-hashes identifiers and shows only email domains, gated by a dictionary allowlist.

4. DSAR export & delete — the TypeScript exportUserData / deleteUserData helpers loop every table for one user_id and log each deletion.

5. Audit trail — an immutable, TTL-free audit_log table partitioned by month so retention actions are provable.

6. Retention monitoring — a system.tables/system.parts join that reports size, age span, and any MergeTree table missing a TTL.

Data Classification

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

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;   -- force the cleanup now

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

• TTL for Data Management
• DELETE Statement
• Mutations
• references/implementation.md — full SQL + TypeScript for all six steps
• references/examples.md — end-to-end GDPR / retention scenarios

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.