clickhouse-real-time-analytics

Name: clickhouse-real-time-analytics
Author: vaquarkhan

19stars4forks

How to install clickhouse-real-time-analytics?

vaquarkhan/data-engineering-agent-skills/clickhouse-real-time-analytics

$npx -y skills add vaquarkhan/data-engineering-agent-skills --skill clickhouse-real-time-analytics

Installs into the current project.

Summary

Guides agents through ClickHouse-based real-time analytics design. Use when building fast analytical serving layers, event aggregations, materialized views, or low-latency metric access patterns.

Files · 1

View on GitHub

SKILL.md

1	# ClickHouse Real Time Analytics
2
3	## Overview
4
5	Use this skill when `ClickHouse` is the target for low-latency analytical serving. It helps agents design ingestion, partitioning, materialized views, and query-ready schemas for fast reads while maintaining operational safety and cost control.
6
7	## When to Use
8
9	- designing or modifying `ClickHouse` tables for real-time analytics
10	- building event-heavy analytical aggregation layers
11	- creating materialized views for pre-computed metrics
12	- optimizing low-latency dashboards and metric APIs
13	- planning ingestion patterns for high-throughput event streams
14
15	Do not use this when the workload is better served by a transactional database or a batch-oriented warehouse with no latency requirement.
16
17	## Workflow
18
19	1. Define latency, freshness, and query access patterns.
20	Include:
21	- acceptable query latency targets (p50, p99)
22	- data freshness requirements (seconds, minutes, eventual)
23	- primary query patterns (point lookups, time-range scans, aggregations)
24	- expected concurrent query load and user base
25
26	2. Choose the right table engine and schema design.
27	- `MergeTree` family for most analytical workloads
28	- `ReplacingMergeTree` for deduplication on eventual consistency
29	- `AggregatingMergeTree` for pre-aggregated rollups
30	- `CollapsingMergeTree` or `VersionedCollapsingMergeTree` for mutable state
31	- define sort keys aligned with primary query filters
32	- choose partition keys for lifecycle management, not query speed
33
34	3. Design ingestion for throughput and merge safety.
35	- batch inserts over single-row writes (target 1000+ rows per insert)
36	- avoid too many partitions — high partition counts cause merge pressure
37	- use `Buffer` tables or async insert when write concurrency is high
38	- define deduplication strategy if at-least-once delivery is the source guarantee
39
40	4. Build materialized views with explicit contracts.
41	- materialized views are insert-triggered, not retroactive
42	- define what happens when the source schema changes
43	- document the lag between source insert and view availability
44	- test that view aggregations remain correct after merges
45
46	5. Plan retention, TTL, and storage tiering.
47	- use TTL expressions for automatic partition drops
48	- separate hot and cold storage tiers if cost is a concern
49	- document retention SLA for each table
50	- test that TTL does not silently drop data consumers still need
51
52	6. Make operations observable and recoverable.
53	- monitor merge backlog, parts count, and replication lag
54	- alert on query latency degradation and memory pressure
55	- plan for cluster scaling: shard count, replica count, and rebalancing
56	- define backup and restore procedures for critical tables
57
58	## Common Rationalizations
59
60	\| Rationalization \| Reality \|
61	\| --- \| --- \|
62	\| "ClickHouse is fast so we don't need to optimize schema." \| Sort keys, partition choices, and engine selection determine whether queries hit milliseconds or seconds. Speed is designed, not guaranteed. \|
63	\| "We can just insert one row at a time." \| Single-row inserts cause excessive parts, merge pressure, and eventual degradation. Batching is not optional at scale. \|
64	\| "Materialized views handle everything automatically." \| Views are insert-triggered and depend on merge behavior. Schema changes, backfills, and retroactive corrections require explicit planning. \|
65	\| "Retention is not urgent — storage is cheap." \| Unbounded growth increases merge overhead, backup time, and query scan ranges. TTL and lifecycle management are operational requirements. \|
66
67	## Red Flags
68
69	- single-row inserts in production without buffering
70	- partition key chosen for query speed instead of lifecycle management
71	- materialized views with no documentation of lag or schema change behavior
72	- no retention or TTL policy on high-volume tables
73	- sort key does not align with primary query patterns
74	- no monitoring of merge backlog or parts count
75	- cluster scaling plan is undefined despite growing data volumes
76	- backfill strategy assumes materialized views will retroactively process old data
77
78	## Verification
79
80	- [ ] Query latency, freshness, and access pattern requirements are documented
81	- [ ] Table engine, sort key, and partition key choices are justified
82	- [ ] Ingestion uses batched writes with explicit deduplication strategy
83	- [ ] Materialized views have documented contracts, lag expectations, and schema change plans
84	- [ ] TTL and retention policies are defined for all high-volume tables
85	- [ ] Operational monitoring covers merge backlog, parts count, replication lag, and query latency
86	- [ ] Scaling and recovery procedures are documented

People who liked this also viewed

6 picks

Type

clickhouse-real-time-analytics

19stars4forks

View on GitHub

How to install clickhouse-real-time-analytics?

vaquarkhan/data-engineering-agent-skills/clickhouse-real-time-analytics

$npx -y skills add vaquarkhan/data-engineering-agent-skills --skill clickhouse-real-time-analytics

Installs into the current project.

Summary

Guides agents through ClickHouse-based real-time analytics design. Use when building fast analytical serving layers, event aggregations, materialized views, or low-latency metric access patterns.

Files · 1

View on GitHub

SKILL.md

1	# ClickHouse Real Time Analytics
2
3	## Overview
4
5	Use this skill when `ClickHouse` is the target for low-latency analytical serving. It helps agents design ingestion, partitioning, materialized views, and query-ready schemas for fast reads while maintaining operational safety and cost control.
6
7	## When to Use
8
9	- designing or modifying `ClickHouse` tables for real-time analytics
10	- building event-heavy analytical aggregation layers
11	- creating materialized views for pre-computed metrics
12	- optimizing low-latency dashboards and metric APIs
13	- planning ingestion patterns for high-throughput event streams
14
15	Do not use this when the workload is better served by a transactional database or a batch-oriented warehouse with no latency requirement.
16
17	## Workflow
18
19	1. Define latency, freshness, and query access patterns.
20	Include:
21	- acceptable query latency targets (p50, p99)
22	- data freshness requirements (seconds, minutes, eventual)
23	- primary query patterns (point lookups, time-range scans, aggregations)
24	- expected concurrent query load and user base
25
26	2. Choose the right table engine and schema design.
27	- `MergeTree` family for most analytical workloads
28	- `ReplacingMergeTree` for deduplication on eventual consistency
29	- `AggregatingMergeTree` for pre-aggregated rollups
30	- `CollapsingMergeTree` or `VersionedCollapsingMergeTree` for mutable state
31	- define sort keys aligned with primary query filters
32	- choose partition keys for lifecycle management, not query speed
33
34	3. Design ingestion for throughput and merge safety.
35	- batch inserts over single-row writes (target 1000+ rows per insert)
36	- avoid too many partitions — high partition counts cause merge pressure
37	- use `Buffer` tables or async insert when write concurrency is high
38	- define deduplication strategy if at-least-once delivery is the source guarantee
39
40	4. Build materialized views with explicit contracts.
41	- materialized views are insert-triggered, not retroactive
42	- define what happens when the source schema changes
43	- document the lag between source insert and view availability
44	- test that view aggregations remain correct after merges
45
46	5. Plan retention, TTL, and storage tiering.
47	- use TTL expressions for automatic partition drops
48	- separate hot and cold storage tiers if cost is a concern
49	- document retention SLA for each table
50	- test that TTL does not silently drop data consumers still need
51
52	6. Make operations observable and recoverable.
53	- monitor merge backlog, parts count, and replication lag
54	- alert on query latency degradation and memory pressure
55	- plan for cluster scaling: shard count, replica count, and rebalancing
56	- define backup and restore procedures for critical tables
57
58	## Common Rationalizations
59
60	\| Rationalization \| Reality \|
61	\| --- \| --- \|
62	\| "ClickHouse is fast so we don't need to optimize schema." \| Sort keys, partition choices, and engine selection determine whether queries hit milliseconds or seconds. Speed is designed, not guaranteed. \|
63	\| "We can just insert one row at a time." \| Single-row inserts cause excessive parts, merge pressure, and eventual degradation. Batching is not optional at scale. \|
64	\| "Materialized views handle everything automatically." \| Views are insert-triggered and depend on merge behavior. Schema changes, backfills, and retroactive corrections require explicit planning. \|
65	\| "Retention is not urgent — storage is cheap." \| Unbounded growth increases merge overhead, backup time, and query scan ranges. TTL and lifecycle management are operational requirements. \|
66
67	## Red Flags
68
69	- single-row inserts in production without buffering
70	- partition key chosen for query speed instead of lifecycle management
71	- materialized views with no documentation of lag or schema change behavior
72	- no retention or TTL policy on high-volume tables
73	- sort key does not align with primary query patterns
74	- no monitoring of merge backlog or parts count
75	- cluster scaling plan is undefined despite growing data volumes
76	- backfill strategy assumes materialized views will retroactively process old data
77
78	## Verification
79
80	- [ ] Query latency, freshness, and access pattern requirements are documented
81	- [ ] Table engine, sort key, and partition key choices are justified
82	- [ ] Ingestion uses batched writes with explicit deduplication strategy
83	- [ ] Materialized views have documented contracts, lag expectations, and schema change plans
84	- [ ] TTL and retention policies are defined for all high-volume tables
85	- [ ] Operational monitoring covers merge backlog, parts count, replication lag, and query latency
86	- [ ] Scaling and recovery procedures are documented

People who liked this also viewed

6 picks

Type