clickhouse-materialized-views

What this skill does

## Table of Contents

1. [Purpose](#purpose)
2. [Quick Start](#quick-start)
3. [Core Concepts](#core-concepts)
   - [Aggregation Patterns](#aggregation-patterns)
   - [Destination Table Engines](#destination-table-engines)
   - [State/Merge Functions](#statemerge-functions)
   - [View Chaining](#view-chaining)
4. [Instructions](#instructions)
   - [Step 1: Choose Your Aggregation Pattern](#step-1-choose-your-aggregation-pattern)
   - [Step 2: Select the Right Destination Engine](#step-2-select-the-right-destination-engine)
   - [Step 3: Design Your Aggregation Query](#step-3-design-your-aggregation-query)
   - [Step 4: Implement State/Merge Functions](#step-4-implement-statemerge-functions-for-complex-aggregates)
   - [Step 5: Chain Views for Multi-Level Aggregation](#step-5-chain-views-for-multi-level-aggregation)
   - [Step 6: Handle Schema Evolution](#step-6-handle-schema-evolution-and-versioning)
   - [Step 7: Monitor Performance](#step-7-monitor-and-optimize-view-performance)
   - [Step 8: Backfill Historical Data](#step-8-backfill-historical-data)
5. [Examples & Patterns](#examples--patterns)
   - [Real-Time Dashboards](#real-time-dashboard-metrics)
   - [User Segmentation](#user-segmentation)
   - [Multi-Destination Routing](#multi-destination-routing)
   - [Complete Examples](#complete-examples)
6. [References](#references)
   - [Engine Comparison](#engine-comparison)
   - [State/Merge Functions Reference](#statemerge-functions-reference)
   - [Kafka Streaming Pipeline](#kafka-streaming-pipeline)
   - [Troubleshooting Guide](#troubleshooting-guide)
7. [Requirements](#requirements)

# ClickHouse Materialized Views Skill

## Purpose

Materialized views in ClickHouse are incremental triggers that automatically transform and aggregate incoming data in real-time. Unlike traditional databases, ClickHouse views process only new data as it arrives, enabling efficient streaming analytics, real-time dashboards, and continuous aggregation without scheduled batch jobs.

## When to Use This Skill

Use when asked to:
- Build real-time data aggregation pipelines ("create real-time metrics")
- Implement streaming analytics or continuous aggregation
- Create pre-aggregated tables for fast dashboard queries
- Set up automatic data transformation as events arrive
- Chain multi-level aggregations (hourly → daily → monthly)

Do NOT use when:
- Data is batch-processed infrequently (use scheduled jobs instead)
- Aggregation logic changes often (materialized views are harder to modify)
- Source data is small and queries are already fast

## Quick Start

**Create a simple real-time aggregation pipeline:**

```sql
-- Step 1: Create source table (raw events)
CREATE TABLE events (
    user_id UInt32,
    event_type String,
    timestamp DateTime,
    revenue Decimal(10, 2)
) ENGINE = MergeTree()
ORDER BY (user_id, timestamp);

-- Step 2: Create destination table (aggregated)
CREATE TABLE daily_revenue (
    date Date,
    total_revenue Decimal(18, 2),
    event_count UInt64
) ENGINE = SummingMergeTree()
ORDER BY date;

-- Step 3: Create materialized view (automatic aggregation)
CREATE MATERIALIZED VIEW daily_revenue_mv TO daily_revenue AS
SELECT
    toDate(timestamp) as date,
    SUM(revenue) as total_revenue,
    COUNT() as event_count
FROM events
GROUP BY date;

-- Now every INSERT into events automatically updates daily_revenue
INSERT INTO events VALUES (12345, 'purchase', '2024-01-15 10:30:00', 99.99);
SELECT * FROM daily_revenue;  -- Result: 2024-01-15 | 99.99 | 1
```

## Instructions

### Step 1: Choose Your Aggregation Pattern

Materialized views support three main patterns depending on your use case:

**Incremental Views (Most Common)**
- Process only new data as it arrives
- Best for streaming event data and continuous updates
- Use with `SummingMergeTree` for automatic deduplication

**Refreshable Views**
- Recalculate entire view on a schedule (e.g., hourly)
- Best for complex queries that need full recalculation
- Syntax: `CREATE MATERIALIZED VIEW ... REFRESH EVERY 1 HOUR AS ...`

**Populate-Based Views**
- Backfill existing data when view is created
- Warning: Can be expensive on large tables
- Syntax: `CREATE MATERIALIZED VIEW ... POPULATE AS ...`

See `examples/aggregation-patterns.md` for complete pattern examples.

### Step 2: Select the Right Destination Engine

The destination table engine determines how your aggregated data behaves:

**SummingMergeTree** (Simple Aggregates)
- Use when aggregating with COUNT, SUM, AVG
- Automatically sums columns during background merges
- No duplicate rows in final results
- Example: Hourly sales totals, daily revenue

**AggregatingMergeTree** (Complex Aggregates)
- Use with State/Merge functions for uniq, quantile, topK
- Stores intermediate aggregation states
- Combine with `uniqState()`, `quantileState()`, `topKState()` in views
- Query with `uniqMerge()`, `quantileMerge()`, `topKMerge()` functions
- Example: Unique user counts, percentile calculations

**ReplacingMergeTree** (Deduplication)
- Use when you need to replace/update existing rows
- Maintains version column to determine latest record
- Example: User profile updates, entity state snapshots

**MergeTree** (Raw Events)
- Use when storing raw events without aggregation
- No automatic deduplication
- Example: Event audit trail, raw clickstream data

See `references/engine-comparison.md` for detailed comparison.

### Step 3: Design Your Aggregation Query

Follow these principles:

**GROUP BY Cardinality**
- Keep cardinality moderate (not millions of unique combinations)
- Example: Good = `GROUP BY date, product_id` | Bad = `GROUP BY user_id, timestamp`

**ORDER BY for Query Patterns**
- Design ORDER BY for your most common query filters
- If queries filter by date first: `ORDER BY (date, product_id)`
- If queries filter by product first: `ORDER BY (product_id, date)`

**Include Necessary Columns**
- Include columns needed for post-aggregation filtering
- Store raw values alongside aggregates when needed for flexibility
- Example: Store `timestamp` and `revenue` along with hourly aggregates

**Use Conditional Aggregation**
- Use `countIf()`, `sumIf()`, `avgIf()` for event filtering
- Filter within the aggregation rather than in views
- Example: `countIf(event_type = 'purchase') as purchase_count`

See `examples/aggregation-patterns.md` for SQL patterns.

### Step 4: Implement State/Merge Functions for Complex Aggregates

When using `AggregatingMergeTree`, use special State/Merge function pairs:

**In Materialized View (State Functions)**
```sql
CREATE MATERIALIZED VIEW user_stats_mv TO user_stats AS
SELECT
    toDate(timestamp) as date,
    uniqState(user_id) as unique_users,
    quantileState(0.95)(response_time) as p95_latency
FROM events
GROUP BY date;
```

**In Queries (Merge Functions)**
```sql
SELECT
    date,
    uniqMerge(unique_users) as total_unique_users,
    quantileMerge(0.95)(p95_latency) as latency_p95
FROM user_stats
GROUP BY date;
```

Common function pairs:
- `uniq()` ↔ `uniqState()` / `uniqMerge()`
- `sum()` ↔ `sumState()` / `sumMerge()`
- `avg()` ↔ `avgState()` / `avgMerge()`
- `quantile(0.95)()` ↔ `quantileState(0.95)()` / `quantileMerge(0.95)()`
- `topK(10)()` ↔ `topKState(10)()` / `topKMerge(10)()`

See `references/state-merge-reference.md` for all function pairs.

### Step 5: Chain Views for Multi-Level Aggregation

Build hierarchical aggregations to reduce redundant computation:

```sql
-- Level 1: Raw events (source)
CREATE TABLE events (...) ENGINE = MergeTree() ORDER BY (user_id, timestamp);

-- Level 2: Hourly aggregation
CREATE TABLE hourly_stats (...) ENGINE = SummingMergeTree() ORDER BY (hour, product_id);
CREATE MATERIALIZED VIEW hourly_stats_mv TO hourly_stats AS
SELECT
    toStartOfHour(timestamp) as hour,
    product_id,
    COUNT() as event_count,
    SUM(revenue) as total_revenue
FROM events
GROUP BY hour, product_id;

-- Level 3: Daily aggregation (chain from hourly, not raw events)
CREATE TABLE daily_stats (...) ENGI