ClickHouse Data Handling Skill

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.

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;
```
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.
Masking & anonymization — expose a CREATE VIEW that sipHash64-hashes identifiers and shows only email domains, gated by a dictionary allowlist.
DSAR export & delete — the TypeScript exportUserData / deleteUserData helpers loop every table for one user_id and log each deletion.
Audit trail — an immutable, TTL-free audit_log table partitioned by month so retention actions are provable.
Retention monitoring — a system.tables/system.parts join that reports size, age span, and any MergeTree table missing a TTL.

Data Classification

| Category | Examples | Handling in ClickHouse | |----------|----------|------------------------| | PII | Email, name, IP | Column-level TTL, masking views, deletion support | | Sensitive | API keys, tokens | Never store in ClickHouse — use secret managers | | Business | Event counts, metrics | Standard TTL, aggregate for long-term retention | | Audit | Access logs | No TTL, immutable, partitioned by month |

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

| Issue | Cause | Solution | |-------|-------|----------| | Mutation stuck | Large table rewrite | Check system.mutations, cancel if needed | | TTL not expiring | No merges running | OPTIMIZE TABLE ... FINAL to force | | DELETE not working | Old ClickHouse version | Use ALTER TABLE DELETE (mutation) | | Export timeout | Too much user data | Add LIMIT or export in batches |

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.

Agent Skills: ClickHouse Data Handling

Install this agent skill to your local

Skill Files