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.

referencesexamples.md

≈743 tokens on demand. Your agent reads this file only when SKILL.md points to it.

ClickHouse Data Handling — Worked Examples

End-to-end scenarios that chain the individual steps from implementation.md into complete, auditable workflows.

Example 1: Fulfilling a GDPR "right to erasure" request

A user (user_id 42) submits an erasure request. The compliant flow is export-then-delete-then-log so the request is provable after the fact.

-- 1. Confirm what data exists for the subject (scope the request)
SELECT partition, count() AS rows
FROM system.parts
WHERE database = 'analytics' AND table = 'events' AND active
GROUP BY partition;

-- 2. Delete across every table holding the subject's rows (mutation-based,
--    verifiable — the compliant path, not lightweight DELETE)
ALTER TABLE analytics.events   DELETE WHERE user_id = 42;
ALTER TABLE analytics.sessions DELETE WHERE user_id = 42;

-- 3. Verify the mutations completed before reporting the request as fulfilled
SELECT table, mutation_id, is_done, parts_to_do
FROM system.mutations
WHERE database = 'analytics' AND command LIKE '%user_id = 42%'
ORDER BY create_time DESC;

-- 4. Write an immutable audit record (this row has NO TTL and is never deleted)
INSERT INTO analytics.audit_log (action, actor, target, details)
VALUES ('DELETE_ALL', 'privacy-service', 'user:42',
        '{"reason":"gdpr_erasure","request_id":"DSAR-2026-0042"}');

Run the full delete + audit-log insert through the TypeScript deleteUserData helper in implementation.md Step 4 when the request comes from an application rather than a console.

Example 2: Standing up a retention-safe events table from scratch

-- Hot/cold/delete tiering plus PII column-TTL in one table definition
CREATE TABLE analytics.events (
    event_id    UUID DEFAULT generateUUIDv4(),
    event_type  LowCardinality(String),
    user_id     UInt64,
    email       String,
    properties  String CODEC(ZSTD(3)),
    created_at  DateTime DEFAULT now()
)
ENGINE = MergeTree()
ORDER BY (event_type, created_at)
PARTITION BY toYYYYMM(created_at)
TTL created_at + INTERVAL 7   DAY TO VOLUME 'hot',
    created_at + INTERVAL 30  DAY TO VOLUME 'cold',
    created_at + INTERVAL 365 DAY DELETE,
    -- null out the raw email at 30 days but keep the analytics row
    created_at + INTERVAL 30  DAY RECOMPRESS CODEC(ZSTD(1));

ALTER TABLE analytics.events
    MODIFY COLUMN email String DEFAULT ''
    TTL created_at + INTERVAL 30 DAY;

Example 3: Auditing which tables are missing a retention policy

-- Any MergeTree table in the analytics DB with no TTL is a compliance gap
SELECT database, name AS table, engine
FROM system.tables
WHERE database = 'analytics'
  AND engine LIKE '%MergeTree%'
  AND result_ttl_expression = '';

Feed the result into a review: business-metric tables may legitimately omit TTL, but any table holding PII that appears here needs a column-TTL or masking view added (see implementation.md Steps 1 and 3).

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