clickhouse-data-handling

name	clickhouse-data-handling
description	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, fulfilling GDPR/CCPA deletion requests, or managing sensitive data in ClickHouse. Trigger with "clickhouse data retention", "clickhouse TTL", "clickhouse GDPR", "delete data clickhouse", "clickhouse data lifecycle", "clickhouse PII".
allowed-tools	Read, Write, Edit
version	1.7.0
license	MIT
author	Jeremy Longshore <[e-mail]>
tags	["saas","database","analytics","clickhouse","olap"]
compatibility	Designed for Claude Code

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.