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;

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

1. Masking & anonymization — expose a CREATE VIEW that sipHash64-hashes

identifiers and shows only email domains, gated by a dictionary allowlist.

1. DSAR export & delete — the TypeScript exportUserData / deleteUserData

helpers loop every table for one user_id and log each deletion.

1. Audit trail — an immutable, TTL-free audit_log table partitioned by

month so retention actions are provable.

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