clickhouse-architecture-advisor

어떤 스킬인가요?

MUST USE when designing ClickHouse architectures, selecting between ingestion or modeling patterns, or translating best practices into workload-specific system designs. Complements clickhouse-best-practices with decision frameworks and explicit provenance labels.

설치 방법

프로젝트 폴더에서 아래 명령어를 실행하고, 설치할 에이전트를 선택하세요.

설치 명령을 직접 실행해야 적용됩니다. 지원 에이전트와 필요한 권한·라이선스는 제작자의 안내를 확인하세요.

스킬 원문 파일

# ClickHouse Architecture Advisor

**Version 0.1.0**  
ClickHouse Inc  
April 2026  
ClickHouse 24.1+

## Abstract

This skill complements `clickhouse-best-practices` by adding a workload-aware architecture layer for ClickHouse. It is optimized for advisory, workshop, and system design workflows where a user needs more than a rule check. It provides decision frameworks for ingestion strategy, time-series partitioning, enrichment paths, late-arriving data, and real-time pre-aggregation.

## Core principle

Official documentation is the source of truth. Every recommendation must be labeled as:

- `official`
- `derived`
- `field`

## Decision areas

### 1. Ingestion strategy
Use when deciding between:
- direct inserts
- async inserts
- Kafka engine + MV
- upstream buffering

### 2. Time-series partitioning
Use when deciding:
- whether to partition
- partition granularity
- how retention and TTL affect design
- how to avoid excessive partition counts

### 3. Enrichment path selection
Use when deciding between:
- runtime JOINs
- dictionaries
- denormalization
- materialized enrichment

### 4. Late-arriving data and mutable state
Use when reasoning about:
- immutable append-only events
- latest-state queries
- replacing or collapsing semantics
- whether frequent mutations should be avoided

### 5. Real-time pre-aggregation
Use when deciding:
- raw-only design
- incremental materialized views
- refreshable MVs
- rollup tables

## Output standard

A valid architecture response should include:
- workload summary
- key decisions
- recommendations with provenance labels
- suggested target architecture
- example DDL or SQL
- validation approach

## Required recommendation schema

See `schemas/recommendation_schema.yaml`.

## Rule index

1. `decision-ingestion-strategy`
2. `decision-partitioning-timeseries`
3. `decision-join-enrichment`
4. `decision-late-arriving-upserts`
5. `decision-real-time-preaggregation`

## Implementation notes

This skill is intentionally narrow:
- it does not replace low-level rule enforcement
- it does not make commercial recommendations
- it does not claim field heuristics are official policy

Its purpose is to translate documented ClickHouse capabilities into workload-specific architecture decisions.

# Example: Financial Services — Real-time market surveillance

## Scenario

- Workload: order and execution event stream
- Ingest rate: 80M events/day
- Query pattern:
  - latest order state
  - time-bounded compliance scans
  - intraday anomaly and pattern detection
- Freshness target: sub-second to low-single-digit seconds
- Additional requirement: late-arriving corrections and cancels

## Workload Summary

This is not classic OLTP. It is a high-throughput analytical event pipeline with mutable business state derived from ordered events. The architecture should preserve append-only facts and compute latest-state views rather than forcing row-by-row transactional mutations.

## Key Decisions

1. Keep a raw append-only event table
2. Model current state separately
3. Avoid using ClickHouse like a row store
4. Use pre-aggregation only for repeated surveillance views

## Recommendations

### 1. Raw event table plus latest-state projection

**What**  
Store all order lifecycle events immutably, then derive current order state.

**Why**  
This preserves auditability and handles late-arriving business events without relying on heavy mutations.

**Category**  
derived

**Confidence**  
medium

**Source**
- https://clickhouse.com/docs/en/guides/replacing-merge-tree

### 2. Use ReplacingMergeTree for current-state table if version semantics are clean

**What**  
Maintain a latest-state table keyed by order identifier and version timestamp.

**Why**  
If corrections naturally replace prior state, ReplacingMergeTree is often the cleanest documented pattern.

**Category**  
official

**Confidence**  
high

**Source**
- https://clickhouse.com/docs/en/guides/replacing-merge-tree

### 3. Use dictionaries for small reference data used in surveillance rules

**What**  
Use dictionaries for symbol metadata, venue mappings, or account-tier lookups if they are read constantly and update slowly.

**Why**  
Repeated runtime joins in hot surveillance logic are often more expensive than key-based dictionary lookup.

**Category**  
official

**Confidence**  
high

**Source**
- https://clickhouse.com/docs/en/sql-reference/dictionaries

## Example raw events table

```sql
CREATE TABLE order_events
(
    event_time DateTime64(3),
    trade_date Date,
    order_id String,
    account_id String,
    symbol LowCardinality(String),
    venue LowCardinality(String),
    event_type LowCardinality(String),
    qty UInt64,
    px Decimal(18, 6),
    version_ts DateTime64(3)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(trade_date)
ORDER BY (trade_date, symbol, order_id, event_time);
```

## Example current-state table

```sql
CREATE TABLE order_state_latest
(
    order_id String,
    symbol LowCardinality(String),
    venue LowCardinality(String),
    status LowCardinality(String),
    qty UInt64,
    px Decimal(18, 6),
    version_ts DateTime64(3)
)
ENGINE = ReplacingMergeTree(version_ts)
ORDER BY (order_id);
```

## Caveat

This pattern is architectural, not transactional. If the requirement is strict OLTP locking semantics with many point updates per key, ClickHouse should not be the system of record for that path.

# Example: Observability — High-throughput event ingestion

## Scenario

- Workload: observability / logs
- Ingest rate: 300K events/sec
- Producer shape: many agents, uneven bursts
- Query pattern: time-range scans, grouped aggregations, service-level dashboards
- Freshness target: under 5 seconds

## Workload Summary

This is a high-ingest, append-friendly, time-series workload. The main architectural risks are:
- excessive small parts
- merge pressure
- slow tail queries if rollups are not used for dashboards

## Key Decisions

1. Use a decoupled ingestion path
2. Partition conservatively
3. Preserve raw data while introducing focused rollups

## Recommendations

### 1. Kafka engine + materialized view for ingestion

**What**  
Use Kafka as the decoupling layer and load ClickHouse through Kafka engine tables and downstream MVs.

**Why**  
The producer fleet is bursty and distributed. This pattern improves replayability and isolates producers from storage behavior.

**How**  
- Kafka topic per stream family
- Kafka engine source table
- MV into MergeTree raw table

**Category**  
derived

**Confidence**  
medium

**Source**
- https://clickhouse.com/docs/engines/table-engines/integrations/kafka
- https://clickhouse.com/docs/materialized-view/incremental-materialized-view

### 2. Monthly partitions on event time

**What**  
Use `PARTITION BY toYYYYMM(event_time)` for the main raw table.

**Why**  
This workload is time-bounded and retention-based, but daily partitioning would likely create unnecessary operational overhead at scale.

**Category**  
derived

**Confidence**  
medium

**Source**
- https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/custom-partitioning-key

### 3. Incremental MVs for hot service dashboards

**What**  
Create rollup tables for repeated service-health queries.

**Why**  
Dashboards and alerts should not repeatedly scan the raw log corpus.

**Category**  
official

**Confidence**  
high

**Source**
- https://clickhouse.com/docs/materialized-view/incremental-materialized-view

## Example raw table

```sql
CREATE TABLE logs_raw
(
    event_time DateTime64(3),
    service LowCardinality(String),
    level LowCardinality(String),
    host String,
    message String,
    attrs JSON
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (service, event_time, host);
```

## Example rollup table

```sql
CREATE TABLE logs_rollup_1m
(
    bucket DateTime,
    service LowCardinality(String),
    level LowCardinality(String),
    count_state AggregateFunction(count)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(bucket)
ORDER BY (service, level, bucket);
```

# Example workload pack

This folder demonstrates how the architecture advisor should respond for representative real-time and enterprise workloads.

## Included

- `observability-high-throughput.md`
- `finserv-market-surveillance.md`
- `siem-security-analytics.md`

## Why examples matter

The examples show how the skill should:
- classify the workload
- separate key architectural decisions from low-level rules
- provide official documentation links
- label derived and field guidance explicitly

# Example: SIEM / security analytics

## Scenario

- Workload: endpoint, identity, cloud, and network telemetry
- Query pattern:
  - repeated detection logic
  - time-bounded investigations
  - lookup-heavy enrichments
- Freshness target: near real-time

## Workload Summary

This workload is time-series heavy, multi-source, and often enrichment-bound. The two common failure modes are:
- expensive runtime JOINs on slow-changing dimension data
- micro-batched ingest that creates excessive parts

## Key Decisions

1. Use append-friendly event storage
2. Replace repeated small-dimension JOINs where possible
3. Protect hot detections with precomputation or lookup structures

## Recommendations

### 1. Enable async inserts for small-batch telemetry senders

**What**  
Use async inserts when many agents or producers write very small batches.

**Why**  
This reduces small-part pressure without rewriting every upstream sender.

**Category**  
official

**Confidence**  
high

**Source**
- https://clickhouse.com/docs/en/operations/settings/settings#async_insert
- https://clickhouse.com/docs/optimize/asynchronous-inserts

### 2. Use dictionaries for slow-changing asset and identity lookups

**What**  
Move repeated device-owner or asset-lookup enrichment out of runtime joins where appropriate.

**Why**  
Security detections often execute continuously; repeated joins on slow-changing dimensions waste CPU.

**Category**  
official

**Confidence**  
high

**Source**
- https://clickhouse.com/docs/en/sql-reference/dictionaries

### 3. Use incremental MVs for repeated aggregated detection views

**What**  
Precompute common counts, rates, and rollups that power dashboards or recurring detections.

**Why**  
Not every threat-hunting query should hit the same raw telemetry tables repeatedly.

**Category**  
official

**Confidence**  
high

**Source**
- https://clickhouse.com/docs/materialized-view/incremental-materialized-view

async_inserts:
  - https://clickhouse.com/docs/en/operations/settings/settings#async_insert
  - https://clickhouse.com/docs/optimize/asynchronous-inserts

partitioning:
  - https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/custom-partitioning-key
  - https://clickhouse.com/docs/partitions

joins:
  - https://clickhouse.com/docs/guides/joining-tables
  - https://clickhouse.com/docs/best-practices/minimize-optimize-joins

dictionaries:
  - https://clickhouse.com/docs/en/sql-reference/dictionaries

incremental_materialized_views:
  - https://clickhouse.com/docs/materialized-view/incremental-materialized-view

refreshable_materialized_views:
  - https://clickhouse.com/docs/materialized-view/refreshable-materialized-view

replacing_merge_tree:
  - https://clickhouse.com/docs/en/guides/replacing-merge-tree

collapsing_merge_tree:
  - https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/collapsingmergetree

query_optimization:
  - https://clickhouse.com/docs/optimize/query-optimization

time_series:
  - https://clickhouse.com/docs/use-cases/time-series/basic-operations

{
  "version": "0.1.0",
  "organization": "ClickHouse Inc",
  "date": "April 2026",
  "clickhouseVersion": "24.1+",
  "abstract": "Architecture decision skill for ClickHouse workloads. Complements clickhouse-best-practices with workload-aware decision frameworks for real-time ingestion, time-series partitioning, enrichment joins, upsert patterns, and pre-aggregation. Recommendations are explicitly labeled as official, derived, or field guidance.",
  "references": [
    "https://clickhouse.com/docs/best-practices",
    "https://clickhouse.com/docs/en/operations/settings/settings#async_insert",
    "https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/custom-partitioning-key",
    "https://clickhouse.com/docs/en/sql-reference/dictionaries",
    "https://clickhouse.com/docs/materialized-view/incremental-materialized-view",
    "https://clickhouse.com/docs/en/guides/replacing-merge-tree"
  ]
}

# ClickHouse Architecture Advisor

Agent skill providing workload-aware architecture guidance for ClickHouse.

This skill is intended to complement `clickhouse-best-practices`, not replace it.

## What it adds

The existing best-practices skill is rule-first and documentation-first. This skill adds:

- workload classification
- decision frameworks
- architecture tradeoff guidance
- broader system design suggestions
- explicit separation of doc-backed guidance from field heuristics

## Recommendation categories

Every recommendation must be labeled as exactly one of:

- `official` — directly backed by official ClickHouse documentation
- `derived` — reasoned from official documentation and core ClickHouse behavior
- `field` — practice-based guidance from field experience, explicitly flagged as non-authoritative

## When this skill should activate

Use this skill when the user is:
- designing a real-time architecture
- choosing between ingestion patterns
- deciding whether to use joins, dictionaries, denormalization, or MVs
- planning for late-arriving data or upserts
- reasoning about time-series modeling
- building a POC or workshop design
- asking for “what should the architecture look like?”

## Relationship to `clickhouse-best-practices`

Use `clickhouse-best-practices` for:
- concrete schema and query rule checks
- low-level design validation
- docs-backed enforcement

Use this skill for:
- when / why / how decisioning
- architecture shape
- system-level tradeoffs
- converting best practices into a target design

## Included decision frameworks

- ingestion strategy for throughput and latency
- time-series partitioning and retention design
- enrichment path selection: JOIN vs dictionary vs denormalization
- late-arriving data and mutable-state patterns
- real-time pre-aggregation with incremental MVs

## Output contract

Responses should typically include:
1. workload summary
2. key decisions
3. recommendations with provenance labels
4. suggested target architecture
5. example DDL and query patterns
6. caveats and validation steps

---
title: Choose an ingestion strategy based on throughput, latency, and producer shape
impact: CRITICAL
tags:
  - ingestion
  - kafka
  - async_insert
  - real-time
---

# Choose an ingestion strategy based on throughput, latency, and producer shape

## Principle

Do not recommend a single ingestion pattern for every workload. The right approach depends on:
- events per second
- rows per insert
- acceptable buffering latency
- whether producers can batch
- whether decoupling is required

## Decision framework

| Condition | Recommended path | Category |
|---|---|---|
| Producers can batch to 10K-100K rows and latency tolerance is moderate | Direct inserts | official |
| Producers send many small inserts and cannot batch effectively | Async inserts | official |
| Producers are bursty, many independent writers exist, or decoupling is needed | Kafka engine + materialized view | derived |
| Reliability, replay, and ingestion fan-out are primary concerns | Upstream queue or log broker before ClickHouse | field |

## Guidance

### Recommendation: direct batched inserts
Use when the application can naturally batch inserts into healthy sizes.

**Why**
The existing best-practices guidance already favors appropriately sized insert batches.

**Official sources**
- `insert-batch-size`
- https://clickhouse.com/docs/best-practices/selecting-an-insert-strategy

### Recommendation: async inserts
Use when producers emit many small writes and the application cannot easily batch.

**Why**
Async inserts let ClickHouse buffer small writes server-side to reduce part pressure.

**Official sources**
- https://clickhouse.com/docs/en/operations/settings/settings#async_insert
- https://clickhouse.com/docs/optimize/asynchronous-inserts

### Recommendation: Kafka engine + materialized view
Use when a queue-based, decoupled ingest path is needed.

**Why**
This is typically the right design when multiple producers, burst handling, or replayability matter.

**Category**
derived

**Sources**
- https://clickhouse.com/docs/engines/table-engines/integrations/kafka
- https://clickhouse.com/docs/materialized-view/incremental-materialized-view

## Validation

- Check average rows per insert
- Check part creation rate
- Check whether insert latency spikes correlate with small batch behavior

---
title: "Choose the right enrichment path: JOIN, dictionary, denormalization, or precomputed enrichment"
impact: CRITICAL
tags:
  - joins
  - dictionaries
  - denormalization
  - enrichment
---

# Choose the right enrichment path: JOIN, dictionary, denormalization, or precomputed enrichment

## Principle

Not every dimension lookup should remain a runtime JOIN. The right design depends on dimension volatility, cardinality, and the cost profile of repeated enrichment.

## Decision framework

| Condition | Recommendation | Category |
|---|---|---|
| Small, slowly changing lookup table used in many queries | Dictionary | official |
| Dimension is naturally embedded and storage duplication is acceptable | Denormalize | derived |
| Join logic is complex and refreshed on a schedule | Refreshable MV | official |
| Query is exploratory or infrequent and dimensions change often | Runtime JOIN | official |

## Guidance

### Recommendation: dictionaries for repeated low-latency lookups
**Why**
Dictionaries are often the best fit for repeated key-based enrichment when the lookup data is relatively static.

**Official sources**
- https://clickhouse.com/docs/en/sql-reference/dictionaries
- `query-join-consider-alternatives`

### Recommendation: denormalize when operationally simple
**Why**
If the dimension is stable and queried constantly, denormalization may outperform repeated joins.

**Category**
derived

**Official context**
- https://clickhouse.com/docs/best-practices/minimize-optimize-joins

### Recommendation: use refreshable or incremental MVs for structured enrichment
**Why**
Precomputed enrichment is often better than expensive runtime joins for recurring production queries.

**Official sources**
- https://clickhouse.com/docs/materialized-view/incremental-materialized-view
- https://clickhouse.com/docs/materialized-view/refreshable-materialized-view

## Validation

- Identify top CPU-consuming JOIN patterns
- Compare runtime JOIN cost vs dictionary lookup or precomputed enrichment
- Check dimension update frequency before choosing dictionary lifetime

---
title: Handle late-arriving data and mutable state without defaulting to heavy mutations
impact: CRITICAL
tags:
  - upserts
  - late-arriving
  - replacingmergetree
  - collapsingmergetree
  - mutable-state
---

# Handle late-arriving data and mutable state without defaulting to heavy mutations

## Principle

Frequent `ALTER TABLE UPDATE` and `ALTER TABLE DELETE` operations are usually the wrong first answer. Prefer append-friendly patterns and engines designed for state evolution.

## Decision framework

| Condition | Recommendation | Category |
|---|---|---|
| Immutable event log with latest-state queries | Raw append table + latest-state query or MV | derived |
| Natural replacement semantics with version ordering | ReplacingMergeTree | official |
| Explicit row-state transitions are modeled | CollapsingMergeTree or VersionedCollapsingMergeTree | official |
| Small correction workload, infrequent and operationally bounded | Targeted mutation may be acceptable | field |

## Guidance

### Recommendation: prefer append + latest-state logic for event streams
**Why**
Many real-time systems do not need in-place updates if the application can compute current state from ordered events.

**Category**
derived

**Official context**
- https://clickhouse.com/docs/en/guides/replacing-merge-tree

### Recommendation: use ReplacingMergeTree for replacement semantics
**Why**
ReplacingMergeTree is the standard documented pattern for row replacement based on version ordering.

**Official sources**
- https://clickhouse.com/docs/en/guides/replacing-merge-tree
- `insert-mutation-avoid-update`

### Recommendation: avoid defaulting to mutations
**Why**
Heavy mutation usage often becomes the bottleneck in otherwise append-friendly systems.

**Official sources**
- `insert-mutation-avoid-update`
- `insert-mutation-avoid-delete`

## Validation

- Measure mutation volume per day
- Check whether the workload is actually latest-state, not true OLTP
- Confirm whether late-arriving records can be handled by version semantics

---
title: Choose time-series partitioning for retention, pruning, and operational hygiene
impact: HIGH
tags:
  - partitioning
  - time-series
  - retention
  - ttl
---

# Choose time-series partitioning for retention, pruning, and operational hygiene

## Principle

Partitioning should primarily support lifecycle management and bounded pruning. It should not be used casually or at excessively fine granularity.

## Decision framework

| Workload condition | Recommendation | Category |
|---|---|---|
| Early-stage or modest data volume with unclear retention needs | Start without partitioning | official |
| Time-bounded workload with month-scale retention windows | Monthly partitioning | derived |
| Very short retention and strictly day-bounded queries | Daily partitioning only if partition count stays reasonable | derived |
| High-scale time-series with TTL and bulk expiration needs | Partition by time unit aligned to retention operations | official |

## Guidance

### Recommendation: start without partitioning when unsure
**Why**
The best-practices skill already notes that teams often over-partition too early.

**Official sources**
- `schema-partition-start-without`
- https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/custom-partitioning-key

### Recommendation: monthly partitions for many real-time systems
**Why**
For observability, SIEM, telemetry, and many financial workloads, monthly partitions often balance lifecycle management with manageable partition counts.

**Category**
derived

**Source**
- https://clickhouse.com/docs/partitions

### Recommendation: align partitioning with TTL boundaries
**Why**
If retention deletes are a primary operational concern, partitioning should make those drops efficient.

**Official sources**
- `schema-partition-lifecycle`
- https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/custom-partitioning-key

## Validation

- Count active partitions
- Verify common queries align to the partition key
- Confirm retention actions operate at partition granularity where possible

---
title: Choose raw-only vs incremental materialized views vs refreshable materialized views
impact: HIGH
tags:
  - materialized_views
  - preaggregation
  - rollups
  - real-time
---

# Choose raw-only vs incremental materialized views vs refreshable materialized views

## Principle

Real-time workloads should not be forced into either “everything raw” or “precompute everything.” The correct choice depends on freshness, query repetition, and transformation complexity.

## Decision framework

| Condition | Recommendation | Category |
|---|---|---|
| Queries are ad hoc and freshness matters most | Query raw tables | derived |
| Repeated aggregation pattern over append-only data | Incremental MV | official |
| Complex joins or scheduled batch recomputation | Refreshable MV | official |
| Very hot dashboard or alerting path | Incremental rollup table plus raw table fallback | derived |

## Guidance

### Recommendation: incremental MVs for repeated real-time aggregation
**Why**
Incremental MVs are the documented best fit for continuously maintained rollups over insert streams.

**Official sources**
- https://clickhouse.com/docs/materialized-view/incremental-materialized-view
- `query-mv-incremental`

### Recommendation: refreshable MVs for heavier joins or scheduled transforms
**Why**
Refreshable MVs better fit complex transformations that do not need per-row trigger semantics.

**Official sources**
- https://clickhouse.com/docs/materialized-view/refreshable-materialized-view
- `query-mv-refreshable`

### Recommendation: dual-path design for hot dashboards
**Why**
A raw table preserves flexibility while a rollup path protects latency-sensitive workloads.

**Category**
derived

**Official context**
- https://clickhouse.com/docs/use-cases/time-series/basic-operations

## Validation

- Identify repeated dashboard queries
- Compare raw scan cost against incremental aggregation maintenance
- Confirm whether the source is append-only enough for incremental MV semantics

recommendation:
  title: string
  what: string
  why: string
  how: string
  decision_context: string
  category:
    enum:
      - official
      - derived
      - field
  confidence:
    enum:
      - high
      - medium
      - heuristic
  source_links:
    type: array
    items:
      type: string
  caveats:
    type: array
    items:
      type: string
  validation:
    type: array
    items:
      type: string
  example_sql:
    type: string

---
name: clickhouse-architecture-advisor
description: MUST USE when designing ClickHouse architectures, selecting between ingestion or modeling patterns, or translating best practices into workload-specific system designs. Complements clickhouse-best-practices with decision frameworks and explicit provenance labels.
license: Apache-2.0
metadata:
  author: ClickHouse Inc
  version: "0.1.0"
---

# ClickHouse Architecture Advisor

This skill adds workload-aware architecture decisioning on top of `clickhouse-best-practices`.

> **Official docs remain the source of truth.**
> This skill must always prefer official ClickHouse documentation when available.

## Required behavior

Before producing recommendations:

1. Identify the workload shape
   - observability
   - security / SIEM
   - product analytics
   - IoT / telemetry
   - market data / financial services
   - mixed OLAP with point-lookups
2. Read the relevant decision rule files in `rules/`
3. Use `mappings/doc_links.yaml` to attach official documentation
4. Classify every recommendation as:
   - `official`
   - `derived`
   - `field`
5. Never present field guidance as official guidance
6. If a recommendation is uncertain, say so explicitly

## Provenance rules

### `official`
Use this when the recommendation is directly backed by official docs.

### `derived`
Use this when the recommendation is not stated verbatim in docs but follows logically from documented ClickHouse behavior.

### `field`
Use this only for experience-based guidance that may be situational.
When using `field`, include:
- a disclaimer that the advice is heuristic
- a relevant official doc if one partially applies
- the reason the advice depends on workload context

## Read these rule files by scenario

### Real-time ingestion design
1. `rules/decision-ingestion-strategy.md`
2. `rules/decision-real-time-preaggregation.md`
3. Relevant best-practices insert rules

### Time-series and retention design
1. `rules/decision-partitioning-timeseries.md`
2. Relevant best-practices schema partition rules

### Enrichment and dimension lookups
1. `rules/decision-join-enrichment.md`
2. Relevant best-practices query join rules

### Mutable state / late-arriving events
1. `rules/decision-late-arriving-upserts.md`
2. Relevant best-practices mutation avoidance rules

## Output format

Structure responses like this:

```markdown
## Workload Summary
- workload:
- latency target:
- data shape:
- primary query patterns:
- operational constraints:

## Key Decisions
- ...
- ...

## Recommendations

### <Recommendation title>

**What**
...

**Why**
...

**How**
...

**Category**
official | derived | field

**Confidence**
high | medium | heuristic

**Source**
- doc link(s)

**Validation**
- concrete SQL, metric, or smoke test
```

## Architecture-specific guidance

Prefer decision frameworks over generic advice. Good responses should:
- explain tradeoffs
- identify the likely operating bottleneck
- separate immediate actions from structural redesign
- provide target architecture patterns, not just isolated settings

## Full reference

See `AGENTS.md` for the compiled version and `examples/` for sample outputs.