Index of every page on this site: llms.txt
All skills
jeremylongshore avatar

/clickhouse-data-handling

@820dfc8
Run it yourself

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

Use this Skill: https://skilld.dev/gh/jeremylongshore/tons-of-skills-marketplace/clickhouse-data-handling

Nothing lands on disk. Nothing to clean up.

Fork this Skill

Edit a local copy. It keeps the author and licence.

SKILL.md

≈111 tokens for metadata: the name and description. ≈1.4k when used: this file. ≈2.3k more on demand in 2 files.

Description uses 5.5% of example budget

Before choosing a Skill, your Agent reads its name and description. All available Skills share that space.

  • A shorter description leaves more room for other Skills. This entry exceeds our 1% size suggestion.

In our Claude Code example, all Skill names and descriptions share 8,000 characters. This Skill uses ≈443 characters, or 5.5%.

The 1% threshold is a size suggestion. Longer descriptions can still fit.

Your model, settings, and other Skills decide how much text your Agent can read.

Example settings and source

The example uses a 200k-token context and default Claude Code settings. The count includes the name, description, separators, and when_to_use when present. Codex also counts local file paths.

Skit's source and limits: Codex 0.160.1, Claude Code 2.1.292.

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

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

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.

Source: SKILL.md on GitHub

No rule matched.

skilld matched fixed text patterns in SKILL.md and file names. Patterns miss obfuscated code.

skilld run checks every file with the same patterns. It asks for approval before it loads a Skill with a behavior marked Needs approval.

No alerts2mo3 checks · Risk SAFE
  • Gen Agent Trust Hub2mo

    This skill provides standard SQL and TypeScript templates for managing ClickHouse data lifecycles and GDPR compliance. It is safe for use, with a minor vulnerability surface identified regarding how the agent processes data from external database tables.

  • Socket2mo

    No alerts

  • Snyk2mo

    Risk: LOW · No issues

Signed by skilld at 820dfc8. This ties the file your Agent reads to that commit on GitHub. It does not review the instructions.

Last checked against GitHub yesterday.

Activeupdated 4 months ago
What it can do
Reads files Edits files
version
1.7.0
author
Jeremy Longshore <jeremy@intentsolutions.io>
tags
[
  "saas",
  "database",
  "analytics",
  "clickhouse",
  "olap"
]
All 3 allowed tools
ReadWriteEdit
Other metadata
compatibility
Designed for Claude Code

README badge

README badge for jeremylongshore/tons-of-skills-marketplace/clickhouse-data-handling