clickhouse-core-workflow-a

# ClickHouse Schema Design (Core Workflow A) ## Overview Design ClickHouse tables with correct engine selection, ORDER BY keys, partitioning, and codec choices for analytical workloads. ## Prerequisites - `@clickhouse/client` connected (see `clickhouse-install-auth`) - Understanding of your query patterns (what you filter and group on) ## Instructions ### Step 1: Choose the Right Engine | Engine | Best For | Dedup? | Example | |--------|----------|--------|---------| | `MergeTree` | General analytics, append-only logs | No | Clickstream, IoT | | `ReplacingMergeTree` | Mutable rows (upserts) | Yes (on merge) | User profiles, state | | `SummingMergeTree` | Pre-aggregated counters | Sums numerics | Page view counts | | `AggregatingMergeTree` | Materialized view targets | Merges states | Dashboards | | `CollapsingMergeTree` | Stateful row updates | Collapses +-1 | Shopping carts | **ClickHouse Cloud uses `SharedMergeTree`** — it is a drop-in replacement for `MergeTree` on Cloud. You do not need to change your DDL. ### Step 2: Design the ORDER BY (Sort Key) The `ORDER BY` clause is the single most important schema decision. It defines: - **Primary index** — sparse index over sort-key granules (8192 rows default) - **Data layout on disk** — rows sorted physically by these columns - **Query speed** — queries filtering on ORDER BY prefix columns hit fewer granules **Rules of thumb:** 1. Put low-cardinality filter columns first (`event_type`, `status`) 2. Then high-cardinal...