skill: clickhouse-analytics

should_trigger:
  - prompt: "We're ingesting 2B clickstream events a day into ClickHouse — what ENGINE, ORDER BY and PARTITION BY should the table use?"
    why: Core schema-design request at scale; engine + sort-key + partition choice is the heart of this skill.
  - prompt: "Our ClickHouse dashboard queries scan the whole table, the primary key isn't pruning anything."
    why: Non-obvious — points at sparse-index/ORDER-BY-prefix pruning, not generic SQL tuning.
  - prompt: "After our hourly ingest job, parts keep piling up and merges can't keep up."
    why: Non-obvious — too-many-parts from tiny/over-partitioned inserts; the merge-pressure symptom maps to ingestion + partition rules here.
  - prompt: "Build a materialized view that keeps a rolling uniq-users-per-hour aggregate as events stream in."
    why: AggregatingMergeTree + -State/-Merge incremental pre-aggregation, the MV section of this skill.
  - prompt: "Montar ClickHouse para analítica de eventos a gran escala con ingesta continua y consultas sub-segundo."
    why: Spanish trigger for standing up a high-volume ClickHouse analytics server.
  - prompt: "Should the upserted order rows table be ReplacingMergeTree or AggregatingMergeTree?"
    why: MergeTree-family engine choice with the FINAL/dedup tradeoff — exactly the engine decision table.
  - prompt: "Consultes lentes a ClickHouse, l'index no poda particions — com l'optimitzo?"
    why: Catalan trigger for query optimization / sparse-index pruning on a ClickHouse server.

should_not_trigger:
  - prompt: "Query a folder of Parquet files on my laptop with SQL, no server to run."
    route_to: duckdb
    why: In-process, single-machine, file-based analytics with no server — DuckDB's space, not a ClickHouse cluster.
  - prompt: "Design the orders table with foreign keys and add an index to fix this slow OLTP point-lookup query."
    route_to: postgresdb
    why: Transactional CRUD schema, FKs, row-level point reads/updates — an OLTP relational job, not columnar OLAP.
  - prompt: "Install PostHog and instrument signup and checkout events in our web app."
    route_to: analytics
    why: Event-capture instrumentation in the app, not designing the columnar store the events land in.
  - prompt: "Lay out an executive KPI dashboard so the numbers fit on one screen."
    route_to: dashboard
    why: Visual layout/charting of metrics for humans, not the engine querying them.
  - prompt: "Decide which five metrics the business should track this quarter and set their targets."
    route_to: kpi-framework
    why: Defining which metrics matter and their targets is strategy, not ClickHouse schema/query work.

capability:
  - scenario: "A team has a high-volume web-events table feeding a dashboard whose GROUP BY query over the last 7 days is now too slow. They ask how to model the table, ingest the firehose, and serve sub-second aggregates on ClickHouse 26.3 LTS."
    must_include:
      - Picks a MergeTree-family engine and justifies it (MergeTree for append-only events; AggregatingMergeTree only behind an MV).
      - Proposes an ORDER BY ordered low-cardinality to high-cardinality, driven by WHERE/GROUP BY (not join keys), with the raw timestamp last; 3-5 columns.
      - Chooses a coarse PARTITION BY (toYYYYMM monthly, or daily only at extreme volume) and explains partitioning is for data lifecycle, not query speed.
      - Gives batched/async ingestion guidance — batches of 10k-100k+ rows or async inserts default-on since 26.3, and notes insert dedup default-on since 26.2 makes retries safe.
      - Builds an AggregatingMergeTree materialized view with uniqState/sumState and -Merge readback, GROUP BY matching the target ORDER BY, and explicitly avoids POPULATE on the big base table (backfill in bounded windows).
      - Adds a query-side accelerator (PREWHERE, projection, or a bloom_filter/minmax skip index) for a filter the ORDER BY prefix doesn't cover.
      - Names at least one anti-pattern it is avoiding (e.g. single-row INSERTs, per-hour partitioning, SELECT *, or FINAL in the hot path).

# Evals — clickhouse-analytics

These cases are prompts fed to the skill router. `should_trigger` entries expect this skill (`clickhouse-analytics`) to be selected; `should_not_trigger` entries each name a real sibling in `route_to` that should win instead (duckdb for file/in-process analytics, postgresdb for OLTP, analytics for event capture, dashboard for layout, kpi-framework for metric strategy). The single `capability` case is graded by a judge against its `must_include` rubric — a good answer designs a MergeTree-family table with a justified low→high-cardinality ORDER BY and coarse PARTITION BY, gives 26.x-correct batched/async ingestion with safe-retry dedup, builds an AggregatingMergeTree MV (no POPULATE) and a query-side accelerator, and names an anti-pattern avoided. Run them through the repo's eval harness the same way as other skills under `skills/*/evals/cases.yaml`; there is no live ClickHouse cluster involved — routing is judged on the prompt and capability on the produced answer.

# Ingestion and materialized views

Target version: 26.3 LTS. The 26.x line changed several insert defaults — note them per item below.

## Batch sizing

ClickHouse turns every `INSERT` into at least one part on disk. A part is a sorted, compressed unit that the background merge scheduler later combines. Too many small parts = the scheduler falls behind = "too many parts" errors and slow queries.

- Aim for **10k–100k+ rows per `INSERT`** when you batch client-side.
- Or rely on **async inserts** (default-on since 26.3) to batch server-side.
- Never one row per statement in a loop.

## Async inserts (default-on since 26.3 LTS)

The server holds incoming small inserts in an in-memory buffer and flushes them as one part when the first threshold is reached:

| Setting | Default | Meaning |
|---|---|---|
| `async_insert` | `1` (since 26.3) | Buffer inserts server-side |
| `async_insert_max_query_number` | `450` | Flush after this many buffered queries |
| `async_insert_busy_timeout_min_ms` | `50` | Lower bound of the adaptive flush timer |
| `async_insert_busy_timeout_max_ms` | data-rate driven | Upper bound; adaptive since 24.2 |
| `wait_for_async_insert` | `1` | Client waits for the flush to confirm durability |

The busy timeout is **adaptive**: at high data rates it shortens toward the min, at low rates it lengthens toward the max, balancing latency vs part count automatically.

Keep inserts **synchronous** (`async_insert=0`) when you need read-your-write consistency immediately, or when you already send large well-sized blocks and want zero buffering latency.

## Deduplication (default-on for all inserts since 26.2)

- Before 26.2, insert dedup was sync-only. **As of 26.2 it applies uniformly to sync and async inserts.**
- Since 26.1 dedup works **end-to-end across async inserts and dependent materialized views**.
- Net effect: an identical re-sent block is dropped, so **retrying a failed insert is safe** — no double counting.
- Control identity explicitly with `insert_deduplication_token`: same token = same logical insert, so you can dedup across differing row content or split a logical batch.

```sql
INSERT INTO events SETTINGS insert_deduplication_token = 'batch-2026-06-02-0007'
SELECT * FROM s3('https://bucket/events/2026-06-02/*.parquet', 'Parquet');
```

## Loading from external sources

```sql
-- S3 (Parquet / JSONEachRow / CSV auto-detected by extension or explicit format)
INSERT INTO events
SELECT * FROM s3('https://bucket.s3.amazonaws.com/events/2026/*.parquet', 'Parquet');

-- Local/served files via file() or the clickhouse-client --query with FORMAT
INSERT INTO events FROM INFILE 'events.csv.gz' FORMAT CSV;
```

```sql
-- Kafka: a Kafka engine table + an MV that drains it into the MergeTree table.
CREATE TABLE events_queue (raw String)
ENGINE = Kafka
SETTINGS kafka_broker_list = 'kafka:9092',
         kafka_topic_list  = 'events',
         kafka_group_name  = 'ch-ingest',
         kafka_format      = 'JSONEachRow';

CREATE MATERIALIZED VIEW events_queue_mv TO events AS
SELECT * FROM events_queue;          -- the Kafka engine itself never stores rows
```

## AggregatingMergeTree materialized view — end to end

Goal: a rolling **uniq users and revenue per tenant per hour** that stays current as events stream in.

```sql
-- 1. Target table storing partial aggregate STATES.
CREATE TABLE events_hourly
(
    tenant_id  UInt32,
    hour       DateTime,
    users      AggregateFunction(uniq, UInt64),
    revenue    AggregateFunction(sum, Float64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(hour)
ORDER BY (tenant_id, hour);

-- 2. MV that writes states on every new insert into events.
--    GROUP BY MUST equal the target ORDER BY. No POPULATE.
CREATE MATERIALIZED VIEW events_hourly_mv TO events_hourly AS
SELECT tenant_id,
       toStartOfHour(ts)  AS hour,
       uniqState(user_id) AS users,
       sumState(revenue)  AS revenue
FROM events
GROUP BY tenant_id, hour;

-- 3. Backfill history WITHOUT POPULATE: bounded windows so it never OOMs.
INSERT INTO events_hourly
SELECT tenant_id, toStartOfHour(ts), uniqState(user_id), sumState(revenue)
FROM events
WHERE ts >= '2026-01-01' AND ts < '2026-02-01'   -- one month at a time
GROUP BY tenant_id, hour;
-- repeat per month; dedup keeps reruns safe.

-- 4. Read with -Merge to collapse states into final values.
SELECT tenant_id, hour,
       uniqMerge(users)  AS uniq_users,
       sumMerge(revenue) AS rev
FROM events_hourly
GROUP BY tenant_id, hour
ORDER BY tenant_id, hour;
```

Why no `POPULATE`: it scans the entire base table in one shot, blocks the MV from capturing concurrent inserts during that scan, and can exhaust memory on a billion-row table. Creating the MV empty captures new rows from the moment of creation; the bounded backfill fills the past safely.

# Query optimization

## How the sparse index prunes

ClickHouse reads data in **granules** (8192 rows by default). It keeps one primary-index mark per granule — the `ORDER BY` column values at the granule boundary. A query whose `WHERE` constrains a **prefix of the `ORDER BY`** lets the engine binary-search the marks and read only matching granules. Everything else is skipped without touching disk.

Consequence: a filter on a column **not** in the `ORDER BY` prefix prunes nothing — the engine scans every granule. That is the usual cause of "the primary key isn't pruning." The fixes below all add a *secondary* way to prune.

Inspect what actually happened:

```sql
EXPLAIN indexes = 1
SELECT count() FROM events WHERE country = 'ES' AND ts >= now() - INTERVAL 7 DAY;
-- shows which indexes were used and granules selected vs total

-- or, run the query with trace logging to see granules/rows read:
SET send_logs_level = 'trace';
```

## PREWHERE

`PREWHERE` reads its columns *first*, evaluates the predicate, and only then reads the remaining columns for surviving rows. ClickHouse auto-moves predicates into `PREWHERE`, but an explicit `PREWHERE` on a **cheap, highly selective** column forces the order you want and cuts I/O on wide tables.

```sql
SELECT event_type, count() FROM events
PREWHERE country = 'ES'                 -- 1 narrow column read first, filters hard
WHERE ts >= now() - INTERVAL 7 DAY
GROUP BY event_type;
```

Use it when one filter column is small and eliminates most rows before the expensive columns are touched.

## Projections

A projection is a second physical copy of the table data with a different `ORDER BY` and/or pre-aggregation, stored alongside the table. The optimizer picks it transparently when it serves the query better.

```sql
ALTER TABLE events ADD PROJECTION by_country
( SELECT * ORDER BY (country, ts) );
ALTER TABLE events MATERIALIZE PROJECTION by_country;   -- builds it for existing data
```

Cost: extra storage and insert work (every insert maintains the projection). Worth it when **one** secondary access pattern is frequent and latency-critical.

## Data-skipping indexes

Cheaper and coarser than projections — they store summaries per block of granules and skip blocks that can't match.

| Index | Built for | Example |
|---|---|---|
| `minmax` | Columns correlated with the sort order (ranges) | `INDEX idx_amt revenue TYPE minmax GRANULARITY 4` |
| `set(N)` | Low distinct count per block | `INDEX idx_et event_type TYPE set(100) GRANULARITY 4` |
| `bloom_filter` | High-cardinality equality / `IN` | `INDEX idx_uid user_id TYPE bloom_filter GRANULARITY 4` |

```sql
ALTER TABLE events ADD INDEX idx_uid user_id TYPE bloom_filter GRANULARITY 4;
ALTER TABLE events MATERIALIZE INDEX idx_uid;   -- backfill for existing parts
```

## Which accelerator, when

- Query filters a column the PK prefix doesn't cover, **point/`IN` lookups**, high cardinality → `bloom_filter` skip index.
- Query filters a **range** on a side column loosely correlated with the sort → `minmax` skip index.
- You repeatedly query in a **whole different sort order** (and storage is acceptable) → projection.
- You repeatedly compute the **same aggregate** at read time → pre-aggregate with an `AggregatingMergeTree` MV instead (see `ingestion-and-mvs.md`).
- A cheap, selective filter column on a wide table → explicit `PREWHERE`.

## Common slow-query fixes

| Symptom | Likely cause | Fix |
|---|---|---|
| Full scan despite a `WHERE` | Filter not on `ORDER BY` prefix | Skip index / projection, or revisit the key |
| Slow `SELECT *` dashboards | Reading every column | Select only needed columns |
| `ReplacingMergeTree` query slow | `FINAL` merging at query time | Drop `FINAL` off hot path; dedup in the MV layer |
| Query fast cold, slow under load | Too many parts | Fix insert batching/partitioning (see schema ref) |
| Memory blows up on GROUP BY | Huge cardinality grouping | Pre-aggregate via MV; or `max_bytes_before_external_group_by` |

# Schema and engines (deep dive)

## Engine matrix

| Engine | Keeps | Merge action | Query pattern | When |
|---|---|---|---|---|
| `MergeTree` | Every inserted row | Just sorts/merges parts | Direct `SELECT ... GROUP BY` | Append-only events, logs, metrics — the default |
| `ReplacingMergeTree([ver][, is_deleted])` | Latest row per `ORDER BY` key | Drops older versions on merge | `SELECT ... FINAL` or app-side latest | Upserts, CDC sink, "current state per id" |
| `AggregatingMergeTree` | Partial aggregate states per key | Combines `-State` partials | `SELECT ...Merge(col)` behind an MV | Pre-aggregated uniq/quantile/argMax rollups |
| `SummingMergeTree([cols])` | Summed numerics per key | Adds numeric columns on merge | Direct `SELECT sum-already-done` | Simple additive counters only |
| `CollapsingMergeTree(sign)` | Rows with +1/-1 sign | Cancels +1/-1 pairs | Sum the sign column | Mutable rows with a known prior state |
| `Replicated<X>` | Same as `<X>` | Same + cross-replica sync via Keeper | Same | Any of the above, in production HA |

Rule of thumb: start at `MergeTree`. Add `AggregatingMergeTree` only behind a materialized view. Reach for `ReplacingMergeTree` only when you genuinely have upserts and can tolerate eventual dedup. Use the `Replicated` prefix in any clustered deployment.

## ORDER BY — the reasoning

The `ORDER BY` defines the **sparse primary index**: ClickHouse stores one index mark per granule (8192 rows by default), so the index is tiny and lives in memory. A query whose `WHERE` touches a *prefix* of the `ORDER BY` can skip whole granules.

Construction:

1. List the columns you filter and group by most. Ignore join keys — they don't help pruning.
2. Sort that list **low cardinality first, high cardinality last**. Low-cardinality leading columns produce long runs of equal values, which compress hard and let the index prune large contiguous ranges.
3. Keep it to **3–5 columns**. Extra columns past the useful prefix only cost insert-time sorting.
4. For timeseries, bucket the timestamp early and keep the raw timestamp last: `(tenant_id, toStartOfDay(ts), event_type, ts)`. The bucket prunes by day; the trailing raw `ts` orders within a granule for range scans.

A good key vs a bad key on the same data is routinely a ~100x query-time difference. This is the highest-leverage decision in the whole schema, and it is effectively immutable — changing it means a new table and an `INSERT ... SELECT` migration.

`PRIMARY KEY` may be a prefix of `ORDER BY` when you want a smaller index than the sort order (e.g. sort by `(a, b, c)` but index only `(a, b)`).

## PARTITION BY — sizing

Partitioning is for **data management**, not speed:

- TTL drops expired partitions during merges.
- `ALTER TABLE ... DROP PARTITION` removes a chunk instantly.
- Backfills and detaches operate per partition.

Budget: aim for **tens to low hundreds of active partitions**, not thousands. Each partition holds its own parts; merges never cross partitions, so over-partitioning multiplies tiny parts and starves the merge scheduler.

| Volume | Partition by |
|---|---|
| < ~100M rows/month | `toYYYYMM(ts)` (monthly) |
| Billions/month, time-bounded retention | `toYYYYMMDD(ts)` (daily) — only at this scale |
| Anything | never a raw high-cardinality column, never per-hour |

## Types and codecs

| Column shape | Type | Codec | Why |
|---|---|---|---|
| Enum-like string, < ~10k distinct | `LowCardinality(String)` | (built-in dict) | Dictionary-encodes; faster GROUP BY, smaller |
| Free-form string, high distinct | `String` | `ZSTD` | LowCardinality hurts above ~10k distinct |
| Monotonic timestamp / counter | `DateTime64(3)` / `UInt*` | `CODEC(Delta, ZSTD)` | Delta makes deltas tiny, ZSTD packs them |
| Float metric | `Float64` | `CODEC(ALP)` | ALP (26.3) beats Gorilla on many float series |
| Semi-structured payload | `JSON` (native, GA 26.3) | — | Real typed paths, not a `String` blob |
| Bounded fixed set | `Enum8` / `Enum16` | — | Stored as int, validated on insert |
| Integer | smallest that fits (`UInt8`…`UInt64`) | — | Narrower = less I/O |

`LowCardinality` threshold: under ~10k distinct values it wins; above that the dictionary overhead can cost more than it saves — fall back to plain `String` + `ZSTD`. Measure with `SELECT uniqExact(col) FROM table` on a sample.

## Migrating an ORDER BY / PARTITION BY

Because both are immutable, the migration is always: create a new table with the corrected key, `INSERT INTO new SELECT * FROM old` (ideally partition-by-partition to bound memory), verify counts, then `EXCHANGE TABLES new AND old` (atomic) and drop the old one.

#!/usr/bin/env bash
# verify.sh — static linter for ClickHouse DDL/queries emitted by the
# clickhouse-analytics skill. Read-only: it greps candidate .sql text for
# anti-patterns from SKILL.md. It does NOT connect to any cluster.
#
# Usage:
#   scripts/verify.sh [PATH ...]      # lint given .sql files
#   scripts/verify.sh                 # lint *.sql under the current dir
#   cat x.sql | scripts/verify.sh -   # lint stdin
#
# Exit: 0 when nothing flagged (also on empty/no input — no false failure).
#       1 when at least one anti-pattern line is found.

set -u

findings=0

# Emit a finding line and bump the counter.
flag() { # file line_no message line_text
  printf '%s:%s: %s\n    %s\n' "$1" "$2" "$3" "$(printf '%s' "$4" | sed 's/^[[:space:]]*//')"
  findings=$((findings + 1))
}

# Lint one already-collected buffer. Args: label, full-text-in-$2.
lint_buffer() {
  local label="$1" text="$2"
  [ -z "${text//[[:space:]]/}" ] && return 0   # empty buffer: nothing to flag

  local lineno=0 line lower
  while IFS= read -r line || [ -n "$line" ]; do
    lineno=$((lineno + 1))
    # Strip trailing line comments so they don't trip the matchers.
    local code="${line%%--*}"
    lower="$(printf '%s' "$code" | tr '[:upper:]' '[:lower:]')"
    [ -z "${lower//[[:space:]]/}" ] && continue

    # 1. Over-fine partitioning: per-day / per-hour buckets.
    case "$lower" in
      *partition\ by*toyyyymmdd*|*partition\ by*tostartofhour*|*partition\ by*tostartofminute*|*partition\ by*todate\(*)
        flag "$label" "$lineno" "fine PARTITION BY (per-day/hour) — partition coarsely (toYYYYMM); the sparse index does the speed" "$line" ;;
    esac

    # 2. Single-row INSERT ... VALUES (...) on one line — tiny parts.
    case "$lower" in
      *insert\ into*values*\(*\)*)
        flag "$label" "$lineno" "single-row INSERT ... VALUES — batch 10k-100k+ rows or rely on async inserts" "$line" ;;
    esac

    # 3. POPULATE on a materialized view — blocks/OOM on big tables.
    case "$lower" in
      *materialized\ view*populate*|*populate*as\ select*)
        flag "$label" "$lineno" "POPULATE on a materialized view — create it empty and backfill in bounded windows" "$line" ;;
    esac

    # 4. SELECT * — defeats columnar storage on wide tables.
    case "$lower" in
      *select\ \**) flag "$label" "$lineno" "SELECT * — name only the columns you need (columnar store reads per-column)" "$line" ;;
    esac

    # 5. FINAL — merges at query time, keep off hot paths.
    case "$lower" in
      *\ final\ *|*\ final\;*|*\ final|*\)final*)
        flag "$label" "$lineno" "FINAL — merges at query time; keep it off dashboard/hot-path queries" "$line" ;;
    esac
  done <<EOF
$text
EOF

  # 6. MergeTree-family ENGINE with no ORDER BY anywhere in the statement set.
  #    Whole-buffer check because ORDER BY may sit lines below ENGINE.
  if printf '%s' "$text" | grep -iqE 'engine[[:space:]]*=[[:space:]]*[a-z]*mergetree'; then
    if ! printf '%s' "$text" | grep -iqE 'order[[:space:]]+by[[:space:]]+[^t]'; then
      flag "$label" "0" "MergeTree-family ENGINE with no usable ORDER BY — define a 3-5 col sort key (ORDER BY tuple() leaves no sparse index)" "ENGINE = *MergeTree"
    fi
  fi
}

# Collect input sources.
inputs=()
if [ "$#" -eq 0 ]; then
  while IFS= read -r f; do inputs+=("$f"); done < <(find . -type f -name '*.sql' 2>/dev/null)
else
  for arg in "$@"; do inputs+=("$arg"); done
fi

# Nothing to lint at all: clean exit, no false failure.
if [ "${#inputs[@]}" -eq 0 ]; then
  echo "clickhouse-analytics verify: no .sql input found — nothing to check."
  exit 0
fi

for src in "${inputs[@]}"; do
  if [ "$src" = "-" ]; then
    lint_buffer "(stdin)" "$(cat)"
  elif [ -f "$src" ]; then
    lint_buffer "$src" "$(cat "$src")"
  else
    echo "clickhouse-analytics verify: skip (not a file): $src" >&2
  fi
done

if [ "$findings" -gt 0 ]; then
  echo "---"
  echo "clickhouse-analytics verify: $findings anti-pattern line(s) flagged. See SKILL.md."
  exit 1
fi

echo "clickhouse-analytics verify: clean — no anti-patterns found."
exit 0