You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by mo...@apache.org on 2026/05/28 22:40:00 UTC
(doris-skills) 01/01: Initial: Apache Doris agent skills (shared kernel)
This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/doris-skills.git
commit bf305c0a8fd0d2f8c8632fa13e919e458a9c8e98
Author: Mingyu Chen (Rayner) <yu...@selectdb.com>
AuthorDate: Fri May 29 06:11:47 2026 +0800
Initial: Apache Doris agent skills (shared kernel)
Brand-neutral doris-best-practices and doris-architecture-advisor skills,
extracted from velodb-agent-skills with 5 placeholder tokens ({{CLI}},
{{PRODUCT_NAME}}, {{CLOUD_PRODUCT_NAME}}, {{CLOUD_OPS_SKILL}},
{{CLI_PATH_ENV}}) for brand repos to render at install time.
Co-Authored-By: tomz-alt <he...@gmail.com>
---
.gitignore | 1 +
PLACEHOLDERS.md | 37 +
README.md | 47 +
scripts/lint-no-brand.sh | 22 +
scripts/render.sh | 87 ++
skills/doris-architecture-advisor/SKILL.md | 103 ++
.../references/decision-data-model-selection.md | 63 ++
.../references/decision-deployment-mode.md | 55 +
.../references/decision-ingestion-strategy.md | 67 ++
.../references/decision-mutable-state.md | 48 +
.../references/decision-query-acceleration.md | 76 ++
.../references/decision-sizing-matrix.md | 132 +++
.../references/decision-time-series-design.md | 148 +++
.../references/decision-workload-classification.md | 92 ++
.../references/example-adtech-marketing.md | 70 ++
.../references/example-cdc-operational-sync.md | 95 ++
.../references/example-gaming.md | 65 ++
.../references/example-iot-sensor-platform.md | 170 +++
.../references/example-log-observability.md | 118 ++
.../references/example-logistics-courier.md | 75 ++
.../references/example-payment-fintech.md | 79 ++
.../references/example-retail-fashion.md | 165 +++
.../references/example-securities-analytics.md | 262 +++++
.../references/example-web3-exchange.md | 99 ++
skills/doris-best-practices/AGENTS.md | 1167 ++++++++++++++++++++
skills/doris-best-practices/SKILL.md | 317 ++++++
.../references/cli-investigation.md | 258 +++++
.../schema-bucket-cloud-mandatory-hash.md | 24 +
.../references/schema-bucket-composite-for-skew.md | 40 +
.../references/schema-bucket-hash-vs-random.md | 43 +
.../schema-bucket-high-cardinality-key.md | 20 +
.../references/schema-bucket-target-size.md | 35 +
.../references/schema-cache-file-cache.md | 18 +
.../references/schema-cache-query-partition.md | 17 +
.../references/schema-ddl-gotchas.md | 155 +++
.../references/schema-index-bitmap.md | 16 +
.../references/schema-index-bloomfilter.md | 18 +
.../references/schema-index-inverted.md | 16 +
.../references/schema-index-ngram-for-like.md | 14 +
.../references/schema-index-text-search.md | 29 +
.../references/schema-index-vector.md | 23 +
.../references/schema-keys-avoid-float.md | 28 +
.../references/schema-keys-cluster-key-for-mow.md | 30 +
.../references/schema-keys-fixed-length-types.md | 26 +
.../references/schema-keys-prefix-index-limits.md | 39 +
.../references/schema-keys-selectivity-first.md | 54 +
.../schema-model-avoid-agg-for-updates.md | 44 +
.../references/schema-model-choose-for-workload.md | 45 +
.../references/schema-model-prefer-mow.md | 29 +
.../schema-model-sequence-col-for-cdc.md | 32 +
.../references/schema-mv-async-join.md | 61 +
.../references/schema-mv-async-limits.md | 31 +
.../references/schema-mv-sync-rollup.md | 36 +
.../references/schema-partition-auto-on-demand.md | 34 +
.../references/schema-partition-dynamic-ttl.md | 32 +
.../schema-partition-range-for-timeseries.md | 64 ++
.../references/schema-partition-skip-for-small.md | 29 +
.../references/schema-props-cloud-forced.md | 13 +
.../references/schema-props-compression.md | 16 +
.../schema-types-bitmap-count-distinct.md | 26 +
.../references/schema-types-doris-specifics.md | 19 +
.../references/schema-types-native-vs-string.md | 16 +
.../references/schema-types-variant-json.md | 31 +
.../references/schema-types-zonemap-limitations.md | 19 +
.../references/sizing-be-cloud.md | 10 +
.../references/sizing-be-integrated.md | 10 +
.../doris-best-practices/references/sizing-fe.md | 11 +
.../references/sizing-storage-formula.md | 16 +
.../references/start-self-hosted.md | 41 +
.../references/usecase-cdc-sync.md | 36 +
.../references/usecase-dashboard-metrics.md | 33 +
.../references/usecase-dimension-lookup.md | 25 +
.../references/usecase-log-event.md | 41 +
.../references/usecase-observability.md | 54 +
.../references/usecase-point-query.md | 35 +
.../references/usecase-star-schema-join.md | 60 +
76 files changed, 5512 insertions(+)
diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..e43b0f9
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1 @@
+.DS_Store
diff --git a/PLACEHOLDERS.md b/PLACEHOLDERS.md
new file mode 100644
index 0000000..75cf644
--- /dev/null
+++ b/PLACEHOLDERS.md
@@ -0,0 +1,37 @@
+# Placeholders
+
+The shared skills under `skills/` contain 5 placeholder tokens. Brand repositories supply concrete values via `brand.values.yaml` and use `scripts/render.sh` to produce final skill content.
+
+## Tokens
+
+| Placeholder | Type | Example (VeloDB) | Example (SelectDB) | Used in |
+|---|---|---|---|---|
+| `{{CLI}}` | string | `velocli` | `sdbcli` | All shared skills that mention CLI commands |
+| `{{CLI_PATH_ENV}}` | string | `VELOCLI_PATH` | `SDBCLI_PATH` | CLI binary detection in `doris-best-practices/SKILL.md` and `cli-investigation.md` |
+| `{{PRODUCT_NAME}}` | string | `VeloDB` | `SelectDB` | Product-name references in narrative text and customer-story examples |
+| `{{CLOUD_PRODUCT_NAME}}` | string | `VeloDB Cloud` | `SelectDB Cloud` | Cloud-product-specific behavior (e.g., "managed by … automatically") |
+| `{{CLOUD_OPS_SKILL}}` | string | `velodb-cloud` | `selectdb-cloud` | Skill-name cross-references to the brand's Cloud operations skill |
+
+## Versioning
+
+This file is the contract between the shared repo and brand repos. If a placeholder is added, renamed, or removed:
+
+1. Bump shared repo to a new git tag (semver MINOR for additions, MAJOR for renames/removals)
+2. Update this file's version history below
+3. Brand repos must update their `brand.values.yaml` to match before pulling the new shared version
+
+## Version history
+
+- **1.0** (initial) — `{{CLI}}`, `{{CLI_PATH_ENV}}`, `{{PRODUCT_NAME}}`, `{{CLOUD_PRODUCT_NAME}}`, `{{CLOUD_OPS_SKILL}}`
+
+## Banned bare strings (CI enforcement)
+
+`scripts/lint-no-brand.sh` rejects any of these literal strings appearing in `skills/**/*.md`:
+
+- `velocli`, `sdbcli`, `VELOCLI`, `SDBCLI`
+- `VeloDB`, `SelectDB`
+- `velodb.cloud`, `velodb.io`, `selectdb.com`, `selectdb.cn`
+- `~/.velodb`, `~/.selectdb`
+- `VELO_`, `SDB_` (as env-var prefixes — full identifiers like `$VELO_HOST`)
+
+These should all be expressed via placeholders or neutralized to "Apache Doris" / "Cloud mode".
diff --git a/README.md b/README.md
new file mode 100644
index 0000000..a400596
--- /dev/null
+++ b/README.md
@@ -0,0 +1,47 @@
+# doris-skills
+
+Brand-neutral Apache Doris agent skills for use with Claude Code. This repository contains the **kernel knowledge** that is identical across VeloDB and SelectDB (both built on Apache Doris): table design, sizing, query investigation, and architecture decisions.
+
+Cloud-platform operations (CLI auth, cluster lifecycle, billing, networking) are **brand-specific** and live in separate brand repositories that consume this shared content via a render step.
+
+## Skills
+
+| Skill | Description |
+|---|---|
+| `doris-best-practices` | Apache Doris table design, sizing, and runtime query investigation (37 rules, 7 use-case templates, 4 sizing guides) |
+| `doris-architecture-advisor` | Workload-aware architecture design (8 decision rules, 10 worked industry examples) |
+
+## How brand repositories consume this
+
+Brand repositories (e.g. `velodb-cloud-skills`, `selectdb-cloud-skills`) include this repo as a git submodule and run `scripts/render.sh` at install time:
+
+```bash
+# In a brand repo:
+./doris-skills/scripts/render.sh ./brand.values.yaml ~/.claude/skills/
+```
+
+`render.sh` substitutes the 5 placeholder tokens (see [PLACEHOLDERS.md](PLACEHOLDERS.md)) with brand-specific values and writes rendered skills to the target directory.
+
+## Authoring rules
+
+- **Never** write brand-specific bare strings (CLI names, hostnames, console URLs, env-var prefixes). Use the placeholders in [PLACEHOLDERS.md](PLACEHOLDERS.md), or neutral wording ("Apache Doris" / "Cloud mode").
+- Cloud-mode behavior (storage-compute separation, replication_num=1, file cache) is **Doris kernel architecture** — describe it neutrally without naming a specific Cloud product, unless the statement is specifically about brand-managed behavior (e.g., automatic node-count management).
+- Run `scripts/lint-no-brand.sh` before committing.
+
+## Layout
+
+```
+doris-skills/
+├── README.md
+├── PLACEHOLDERS.md # Placeholder catalog + version contract
+├── scripts/
+│ ├── render.sh # Substitutes placeholders for a brand
+│ └── lint-no-brand.sh # Fails CI if bare brand strings appear
+└── skills/
+ ├── doris-best-practices/
+ └── doris-architecture-advisor/
+```
+
+## License
+
+Apache-2.0
diff --git a/scripts/lint-no-brand.sh b/scripts/lint-no-brand.sh
new file mode 100755
index 0000000..c43f417
--- /dev/null
+++ b/scripts/lint-no-brand.sh
@@ -0,0 +1,22 @@
+#!/usr/bin/env bash
+# lint-no-brand.sh — fail if any brand-specific bare string leaks into
+# the shared skills/ tree. These should all be expressed via {{...}} placeholders
+# or neutralized to "Apache Doris" / "Cloud mode".
+
+set -euo pipefail
+
+SCRIPT_DIR=$(cd -- "$(dirname -- "${BASH_SOURCE[0]}")" &>/dev/null && pwd)
+REPO_ROOT=$(dirname -- "$SCRIPT_DIR")
+SKILLS_DIR=$REPO_ROOT/skills
+
+# Bare brand strings forbidden in shared content
+FORBIDDEN='velocli|sdbcli|VELOCLI|SDBCLI|VeloDB|SelectDB|velodb\.cloud|velodb\.io|selectdb\.com|selectdb\.cn|~/\.velodb|~/\.selectdb|\$VELO_|\$SDB_'
+
+if grep -rnE --include='*.md' "$FORBIDDEN" "$SKILLS_DIR"; then
+ echo ""
+ echo "lint-no-brand: FAIL — bare brand strings found above." >&2
+ echo "Replace with the appropriate {{PLACEHOLDER}} or use 'Apache Doris' / 'Cloud mode' wording." >&2
+ exit 1
+fi
+
+echo "lint-no-brand: ok"
diff --git a/scripts/render.sh b/scripts/render.sh
new file mode 100755
index 0000000..0ef3937
--- /dev/null
+++ b/scripts/render.sh
@@ -0,0 +1,87 @@
+#!/usr/bin/env bash
+# render.sh — render shared-repo skills with brand placeholder values.
+#
+# Usage:
+# render.sh <brand_values_yaml> <out_dir>
+#
+# The shared skill source is assumed to live at <repo_root>/skills/ where
+# <repo_root> is the parent directory of this script.
+
+set -euo pipefail
+
+if [[ $# -ne 2 ]]; then
+ echo "Usage: $0 <brand_values_yaml> <out_dir>" >&2
+ exit 2
+fi
+
+VALUES_FILE=$1
+OUT_DIR=$2
+
+SCRIPT_DIR=$(cd -- "$(dirname -- "${BASH_SOURCE[0]}")" &>/dev/null && pwd)
+REPO_ROOT=$(dirname -- "$SCRIPT_DIR")
+SHARED_SKILLS_DIR=$REPO_ROOT/skills
+
+if [[ ! -f $VALUES_FILE ]]; then
+ echo "error: values file not found: $VALUES_FILE" >&2
+ exit 2
+fi
+if [[ ! -d $SHARED_SKILLS_DIR ]]; then
+ echo "error: shared skills dir not found: $SHARED_SKILLS_DIR" >&2
+ exit 2
+fi
+
+# Extract values from yaml (simple grep — only top-level scalar keys supported)
+get_val() {
+ local key=$1
+ grep -E "^${key}:" "$VALUES_FILE" | head -1 | sed -E "s/^${key}:[[:space:]]*//; s/^['\"]//; s/['\"]$//"
+}
+
+CLI=$(get_val cli)
+CLI_PATH_ENV=$(get_val cli_path_env)
+PRODUCT_NAME=$(get_val product_name)
+CLOUD_PRODUCT_NAME=$(get_val cloud_product_name)
+CLOUD_OPS_SKILL=$(get_val cloud_ops_skill)
+
+# Validate all 5 placeholders are set
+missing=()
+[[ -z $CLI ]] && missing+=(cli)
+[[ -z $CLI_PATH_ENV ]] && missing+=(cli_path_env)
+[[ -z $PRODUCT_NAME ]] && missing+=(product_name)
+[[ -z $CLOUD_PRODUCT_NAME ]] && missing+=(cloud_product_name)
+[[ -z $CLOUD_OPS_SKILL ]] && missing+=(cloud_ops_skill)
+if (( ${#missing[@]} > 0 )); then
+ echo "error: brand.values.yaml missing keys: ${missing[*]}" >&2
+ exit 2
+fi
+
+mkdir -p "$OUT_DIR"
+
+# Render every .md under skills/ into the same path under $OUT_DIR
+find "$SHARED_SKILLS_DIR" -type f | while read -r src; do
+ rel=${src#"$SHARED_SKILLS_DIR/"}
+ dst=$OUT_DIR/$rel
+ mkdir -p "$(dirname "$dst")"
+ case $src in
+ *.md)
+ sed \
+ -e "s|{{CLI}}|${CLI}|g" \
+ -e "s|{{CLI_PATH_ENV}}|${CLI_PATH_ENV}|g" \
+ -e "s|{{PRODUCT_NAME}}|${PRODUCT_NAME}|g" \
+ -e "s|{{CLOUD_PRODUCT_NAME}}|${CLOUD_PRODUCT_NAME}|g" \
+ -e "s|{{CLOUD_OPS_SKILL}}|${CLOUD_OPS_SKILL}|g" \
+ "$src" > "$dst"
+ ;;
+ *)
+ cp "$src" "$dst"
+ ;;
+ esac
+done
+
+# Verify: no {{ residue in output
+if grep -rEln '\{\{[A-Z_]+\}\}' "$OUT_DIR" >/dev/null; then
+ echo "error: unrendered placeholder(s) found in output:" >&2
+ grep -rEn '\{\{[A-Z_]+\}\}' "$OUT_DIR" | head >&2
+ exit 3
+fi
+
+echo "render: ok -> $OUT_DIR"
diff --git a/skills/doris-architecture-advisor/SKILL.md b/skills/doris-architecture-advisor/SKILL.md
new file mode 100644
index 0000000..b60070e
--- /dev/null
+++ b/skills/doris-architecture-advisor/SKILL.md
@@ -0,0 +1,103 @@
+---
+name: doris-architecture-advisor
+description: >
+ Workload-aware architecture design for Apache Doris. MUST USE when
+ designing data architectures, choosing between data models, planning ingestion
+ strategies, sizing clusters, or translating business requirements into Apache Doris
+ system designs. Complements doris-best-practices with decision frameworks and
+ sizing-first workflow.
+ Use when user describes a workload involving: IoT, sensor data, telemetry,
+ real-time analytics, dashboard, log analysis, log search, CDC sync, time-series,
+ device monitoring, point query service, ad-hoc analytics, lakehouse federation,
+ ETL/ELT pipeline, report analytics, clickstream, user behavior, observability,
+ metrics, fleet tracking, or any OLAP workload requiring table design from scratch.
+ Also triggers on prompts like: "design a table for...", "how should I store...",
+ "build an architecture for...", "we have X devices sending data every Y seconds",
+ "recommend a cluster size for...", "what data model should I use for...",
+ "we need to ingest X GB/day", "migrate from MySQL/PostgreSQL to {{PRODUCT_NAME}}".
+ Also use for legacy analytics/search/serving stack consolidation prompts even
+ when {{PRODUCT_NAME}} is not named explicitly, including replacing or migrating from
+ Impala, Kudu, Elasticsearch/ES, Greenplum, Presto, HBase, Hive, Hadoop,
+ Redis, or Lambda-style multi-engine data platforms.
+---
+
+# Apache Doris Architecture Advisor
+
+> Workload-aware architecture design for Apache Doris.
+> 8 decision rules, 3 worked examples.
+> Complements `doris-best-practices` with sizing-first workflow.
+
+---
+
+## Workflow
+
+Follow these 5 steps in order:
+
+1. **DDL validation** — The `doris-best-practices` skill handles DDL correctness. Its Pre-Flight Checklist and DDL Gotchas apply to every CREATE TABLE. This advisor focuses on architecture decisions (which model, which partition strategy, which indexes), not DDL syntax. Always calculate explicit bucket counts. If volume is unknown, choose a conservative default: 3 for small dimensions, 8 for medium tables, 16-32 for large daily fact tables.
+
+2. **Classify workload** — Read `references/decision-workload-classification.md`. Match user's scenario to one or more of the 6 workload types. Composite workloads (e.g., IoT = time-series + device state + logs + dashboards) decompose into multiple sub-tables.
+
+3. **Size the cluster** — Read `references/decision-sizing-matrix.md`. Estimate write throughput, query QPS, latency target, and hot data volume. Output sizing as **total vCPU and total cache only** — never break down into per-node specs (node count is managed by {{CLOUD_PRODUCT_NAME}} automatically). Also read `references/decision-deployment-mode.md` if user hasn't specified cloud vs on-prem.
+
+4. **Design architecture** — Based on workload classification, read the relevant decision rules:
+
+ | Workload signal | Read these rules |
+ |----------------|-----------------|
+ | Append-only events, logs, time-series | `decision-data-model-selection`, `decision-time-series-design`, `decision-ingestion-strategy` |
+ | Updates, CDC, device state tracking | `decision-data-model-selection`, `decision-mutable-state`, `decision-ingestion-strategy` |
+ | Semi-structured / multi-protocol JSON | `decision-data-model-selection` (VARIANT section) |
+ | Dashboards, pre-aggregated metrics | `decision-query-acceleration` |
+ | Point query API, high-concurrency lookups | `decision-query-acceleration` (point query section) |
+ | Text search, log search, full-text | `decision-query-acceleration` (index section) |
+ | Vector / embedding search | `decision-query-acceleration` (vector section) |
+ | Warehouse layering (ODS/DWD/DWS/ADS) | `decision-workload-classification` (layering section), `decision-data-model-selection` |
+ | Multi-department / workload isolation | `decision-workload-classification` (isolation section) |
+ | Hot/cold tiering with data lake | `decision-workload-classification` (lakehouse section), `decision-deployment-mode` |
+
+ Output the architecture design: data flow diagram, table-per-sub-workload mapping, and the key design decisions (model, partition strategy, bucket key, indexes, compression, ingestion method) for each table.
+
+5. **Generate DDL** — Produce CREATE TABLE statements applying ALL constraints from step 1. Calculate explicit bucket counts with the formula in `decision-time-series-design.md`; use the fallback counts above when inputs are incomplete. For each table, cite the best-practices rule that drove the decision.
+
+---
+
+## Output Structure
+
+Responses should include these sections (adapt formatting to conversation):
+
+- **Workload Summary** — Classification, write rate, QPS, latency target, hot data volume
+- **Sizing Recommendation** — Warehouse tier, storage estimate, cache strategy
+- **Architecture Overview** — Data flow from sources → ingestion → {{PRODUCT_NAME}} → applications
+- **Table Designs** — CREATE TABLE with inline comments citing decision rules
+- **Rules Checked** — For each table, list the rules applied with exact file paths so users can look up the rule for troubleshooting. Format: `Per [rule-name](doris-best-practices/references/rule-name.md)`. Example:
+ ```
+ Table: sensor_readings
+ Rules Applied:
+ - [schema-model-choose-for-workload](doris-best-practices/references/schema-model-choose-for-workload.md) — DUPLICATE for append-only
+ - [schema-bucket-target-size](doris-best-practices/references/schema-bucket-target-size.md) — 10 buckets (21 GB / 2 GB)
+ - [schema-props-compression](doris-best-practices/references/schema-props-compression.md) — ZSTD for IoT data
+ ```
+- **Decision Provenance** — Each recommendation tagged: `official` (from Doris docs), `derived` (logical inference), or `field` (experience heuristic with disclaimer)
+
+---
+
+## Worked Examples
+
+For complete input → output examples, read:
+- `references/example-iot-sensor-platform.md` — IoT: 50K sensors, composite workload, 4 tables
+- `references/example-log-observability.md` — Logs + traces + metrics, inverted index, ZSTD
+- `references/example-cdc-operational-sync.md` — MySQL CDC, UNIQUE MoW, sequence column
+- `references/example-securities-analytics.md` — Securities firm: ODS→DWD→DWS→ADS layering, customer 360, compliance, lakehouse, workload isolation
+- `references/example-retail-fashion.md` — Retail/fashion: omnichannel inventory, wide+tall table for user profiling, BITMAP segmentation, multi-brand isolation, peak season scaling
+- `references/example-logistics-courier.md` — Logistics/courier: AGGREGATE for parcel status (MIN/MAX/REPLACE), vehicle GPS with GIS + cooldown_ttl, sorting center KPIs, platform consolidation (Presto+Kudu+ES+HBase→{{PRODUCT_NAME}})
+- `references/example-web3-exchange.md` — Web3/crypto: multi-chain VARIANT schema, custody monitoring, TVL/token async MVs, AML risk detection, wallet profiling, session analysis
+- `references/example-payment-fintech.md` — Payment/fintech: partial column update for tx lifecycle, acquiring row-column hybrid (100+ cols), merchant reconciliation, risk engine, log platform replacing ES, Lambda→unified architecture
+- `references/example-gaming.md` — Gaming: retention/funnel analysis, player profiling BITMAP, NL2SQL Agentic analytics via MCP, anti-cheat anomaly detection, lakehouse for offline data
+- `references/example-adtech-marketing.md` — AdTech/marketing: dual-path RTB serving + analytics, DSP/ADX, creative analysis with VARIANT + vector, cross-border multi-region, replacing Redis+MySQL+HBase+Hive
+
+---
+
+## When NOT to Use This Skill
+
+- **Reviewing existing DDL** → use `doris-best-practices` instead
+- **Optimizing a slow query** → use `doris-best-practices` query rules
+- **CLI / connection setup** → use `doris-best-practices` "Connection & CLI" section
diff --git a/skills/doris-architecture-advisor/references/decision-data-model-selection.md b/skills/doris-architecture-advisor/references/decision-data-model-selection.md
new file mode 100644
index 0000000..6af9a2c
--- /dev/null
+++ b/skills/doris-architecture-advisor/references/decision-data-model-selection.md
@@ -0,0 +1,63 @@
+# Data Model Selection
+
+The data model is immutable after table creation. Choose carefully.
+
+## Performance Ranking
+
+Query speed: **DUPLICATE > UNIQUE MoW > UNIQUE MoR ≈ AGGREGATE**
+
+- DUPLICATE: no merge overhead at query time — fastest full scans
+- UNIQUE MoW: pre-merged at write time — fast reads, slightly slower writes
+- UNIQUE MoR: merge-sort at query time — 2-10x slower reads
+- AGGREGATE: similar to MoR for scan queries
+
+Reference: [Data Model Overview](https://doris.apache.org/docs/table-design/data-model/overview)
+
+## Decision Framework
+
+| Condition | Model | Source |
+|-----------|-------|--------|
+| Append-only data (logs, events, clicks, sensor readings) | DUPLICATE | official |
+| Rows are updated or deleted (CDC, user profiles, device state) | UNIQUE MoW | official |
+| Pre-aggregated metrics only (counters, sums, never query raw rows) | AGGREGATE | official |
+| Need partial column updates | UNIQUE MoW with `enable_unique_key_partial_update` | official |
+| Need partial column updates on AGG table | AGGREGATE with REPLACE_IF_NOT_NULL | official |
+| Small dimension/lookup table (<1GB, rarely updated) | DUPLICATE with RANDOM bucketing | derived |
+| Small dimension table that receives updates | UNIQUE MoW | derived |
+| High-write, low-read workload (rare) | UNIQUE MoR (accept read penalty for write speed) | field |
+
+Reference: [Unique Key Model](https://doris.apache.org/docs/table-design/data-model/unique), [Aggregate Model](https://doris.apache.org/docs/table-design/data-model/aggregate)
+
+## Critical Constraints
+
+- **AGGREGATE model cannot UPDATE or DELETE.** No CDC compatibility. No `DELETE FROM`.
+- **UNIQUE MoW is default since Doris 2.1.** Always set explicitly for clarity.
+- **MoR blocks tiered storage** in some configurations. Prefer MoW unless write throughput is the sole concern.
+- **AGG model hides values from ZoneMap** — value columns cannot benefit from min/max pruning.
+
+## VARIANT Type for Semi-structured Data
+
+When device protocols vary or JSON schemas evolve, recommend VARIANT instead of JSON or STRING.
+
+Reference: [VARIANT Type](https://doris.apache.org/docs/sql-manual/data-types/semi-structured/VARIANT)
+
+**When to use VARIANT:**
+- Multi-protocol IoT (15+ device types, different schemas)
+- User event properties (variable JSON payloads)
+- Any semi-structured data where schema changes with firmware/app updates
+- 8x faster queries than JSON type, 65% less storage
+
+**When NOT to use VARIANT:**
+- All fields are known and stable — use typed columns (better ZoneMap pruning)
+- Field is a sort key or partition key — must be a typed column
+
+## Complex Type Limitations
+
+JSON, ARRAY, MAP, STRUCT, VARIANT, BITMAP, HLL columns:
+- **No ZoneMap** — cannot prune data pages by min/max
+- **No BloomFilter** — cannot skip pages by value lookup
+- Always cause full column scan when filtered
+
+**Workaround:** Extract frequently-filtered fields into typed columns. Keep complex types for projection only.
+
+For DDL implementation of these decisions, delegate to `doris-best-practices` rules: `schema-model-choose-for-workload`, `schema-model-prefer-mow`, `schema-types-variant-json`.
diff --git a/skills/doris-architecture-advisor/references/decision-deployment-mode.md b/skills/doris-architecture-advisor/references/decision-deployment-mode.md
new file mode 100644
index 0000000..fa21da9
--- /dev/null
+++ b/skills/doris-architecture-advisor/references/decision-deployment-mode.md
@@ -0,0 +1,55 @@
+# Deployment Mode Selection
+
+Choose between integrated (storage-compute coupled) and decoupled (storage-compute separated) before sizing.
+
+## Decision Framework
+
+| Condition | Recommendation | Source |
+|-----------|---------------|--------|
+| Public cloud with S3/GCS/OSS available | Decoupled (Cloud mode) | derived |
+| Private DC with reliable HDFS/Ceph/MinIO | Decoupled (self-hosted) | derived |
+| Private DC without shared storage | Integrated | official |
+| Write-heavy workload (ingest throughput is primary concern) | Decoupled — 2x write speedup (single replica vs 3-replica) | field |
+| Latency-critical point queries (<30ms SLA) | Integrated — local SSD avoids object storage latency | field |
+| Multi-tenant with workload isolation needed | Decoupled — independent compute groups share data | official |
+| Quick POC or dev/test environment | Integrated — simpler, no external dependencies | derived |
+| Need elastic scale-up/down by time of day | Decoupled — add/remove BEs without data migration | official |
+
+## Performance Characteristics
+
+### Write performance
+Decoupled is ~2x faster than integrated. Integrated writes 3 replicas with in-memory sort + compaction on each. Decoupled writes 1 replica to object storage.
+
+### Query performance
+- **Fully cached:** Same as integrated.
+- **Fully remote (cache miss):** 30%+ slower — depends on query and network bandwidth.
+- **Rule of thumb:** 60% of data cached locally → 95% cache hit rate → production-ready.
+
+### Cache strategy
+- Maintain cache hit rate ≥ 90% in production.
+- Set table-level cache TTL for hot/cold separation:
+
+```sql
+-- Dimension tables: cache forever
+ALTER TABLE dim_stores SET ("file_cache_ttl_seconds" = "0");
+
+-- Fact tables: cache last 7 days
+ALTER TABLE fact_orders SET ("file_cache_ttl_seconds" = "604800");
+```
+
+## Cloud Mode Forced Properties
+
+All Cloud-mode tables must set:
+```sql
+PROPERTIES ("replication_num" = "1");
+```
+
+Additional cloud constraints:
+- UNIQUE MoW tables must use HASH bucketing (not RANDOM)
+- Delete bitmap syncs via RPC in cloud mode (slight write overhead)
+- File cache controls query performance — monitor cache hit rate
+
+## Object Storage Requirements (self-hosted decoupled)
+- Single-bucket access latency: milliseconds
+- Single-bucket capacity: PB-scale
+- Network bandwidth (up + down): ≥ 15 Gbps each
diff --git a/skills/doris-architecture-advisor/references/decision-ingestion-strategy.md b/skills/doris-architecture-advisor/references/decision-ingestion-strategy.md
new file mode 100644
index 0000000..71fcdc8
--- /dev/null
+++ b/skills/doris-architecture-advisor/references/decision-ingestion-strategy.md
@@ -0,0 +1,67 @@
+# Ingestion Strategy
+
+Choose the ingestion path based on throughput, producer shape, and latency requirements.
+
+## Decision Framework
+
+| Condition | Recommendation | Source |
+|-----------|---------------|--------|
+| Application can batch 10K-100K rows per insert | Stream Load (HTTP) or INSERT INTO VALUES | official |
+| Many small inserts from distributed producers that cannot batch | Group Commit (`group_commit = async_mode`) | official |
+| Continuous streaming from MySQL/PostgreSQL with CDC | Flink CDC → Stream Load | official |
+| Continuous streaming from Kafka topics | Routine Load (built-in Kafka consumer) | official |
+| Bulk import from S3/HDFS/object storage | Broker Load or `SELECT ... FROM S3()` table function | official |
+| Bursty writes from many IoT gateways | Group Commit for real-time + Broker Load for batch backfill | derived |
+| Real-time CDC + business data from OLTP databases | Flink CDC with exactly-once semantics | derived |
+
+Reference: [Stream Load](https://doris.apache.org/docs/data-operate/import/stream-load-manual), [Routine Load](https://doris.apache.org/docs/data-operate/import/routine-load-manual), [Group Commit](https://doris.apache.org/docs/data-operate/import/group-commit-manual)
+
+## Stream Load (HTTP API)
+Best for application-controlled batch inserts.
+```bash
+curl -u user:pass -H "label:load_20250101" \
+ -T data.csv \
+ http://<fe_host>:8080/api/<db>/<table>/_stream_load
+```
+- Target batch size: 10K-100K rows or 10-100MB per request
+- Supports CSV, JSON, Parquet, ORC formats
+- Synchronous — caller knows immediately if load succeeded
+
+## Group Commit
+Best for high-frequency small writes that cannot batch client-side.
+```sql
+-- Server-side batching: {{PRODUCT_NAME}} accumulates small inserts
+-- and flushes them together
+SET group_commit = async_mode;
+INSERT INTO table VALUES (...);
+```
+- {{PRODUCT_NAME}} buffers small inserts server-side, flushes as one batch
+- Reduces part creation pressure from many small writers
+- IoT gateway pattern: each gateway sends small batches → Group Commit merges
+
+## Flink CDC
+Best for continuous MySQL/PostgreSQL replication.
+- Full snapshot + incremental binlog/WAL streaming
+- Exactly-once delivery with Flink checkpointing
+- Pair with UNIQUE MoW + `sequence_col` for correct ordering
+
+## Routine Load
+Best for continuous Kafka consumption.
+- Built-in Kafka consumer, no external framework needed
+- Supports JSON, CSV formats from Kafka topics
+- Auto-manages offsets and parallelism
+
+## Broker Load
+Best for bulk import from object storage.
+- Asynchronous — submit job and poll for completion
+- Supports S3, GCS, HDFS, OSS
+- Use for initial data migration or periodic batch loads
+
+## Write Throughput Reference
+
+{{PRODUCT_NAME}} achieves tens of GB/s write throughput with:
+- Time-series compaction (`compaction_policy = "time_series"`) — millisecond-level compaction, near-zero memory overhead
+- Columnar storage with optimized flush
+- Vectorized index construction
+
+Benchmark: 13 nodes (16c, 64GB) sustain 1 GB/s continuous write at <20% CPU.
diff --git a/skills/doris-architecture-advisor/references/decision-mutable-state.md b/skills/doris-architecture-advisor/references/decision-mutable-state.md
new file mode 100644
index 0000000..23ebea3
--- /dev/null
+++ b/skills/doris-architecture-advisor/references/decision-mutable-state.md
@@ -0,0 +1,48 @@
+# Mutable State & CDC Handling
+
+For workloads where rows are updated or deleted: CDC sync, device shadow, user profiles.
+
+Reference: [Unique Key Model](https://doris.apache.org/docs/table-design/data-model/unique), [Sequence Column](https://doris.apache.org/docs/table-design/data-model/unique#sequence-column)
+
+## Decision Framework
+
+| Condition | Recommendation | Source |
+|-----------|---------------|--------|
+| Replicate MySQL/PostgreSQL via CDC (Flink CDC, Debezium) | UNIQUE MoW + sequence_col | official |
+| Device shadow / digital twin (latest device state) | UNIQUE MoW + sequence_col on event timestamp | derived |
+| Out-of-order events from IoT sensors or distributed producers | sequence_col guarantees newest wins | official |
+| Need partial column updates (update only some fields) | UNIQUE MoW + `enable_unique_key_partial_update` | official |
+| Need partial updates on AGG model | REPLACE_IF_NOT_NULL aggregation (lighter than MoW) | official |
+| Immutable event log with latest-state queries | Append-only DUPLICATE + async MV for latest state | derived |
+
+## Sequence Column for Out-of-Order Data
+
+IoT data arrives out of order due to: sensor faults, network delays, clock sync drift. Without sequence_col, a delayed old event can overwrite a newer one.
+
+**How it works:** When two rows have the same primary key, Doris keeps the one with the higher sequence_col value, regardless of arrival order.
+
+## SHORT-CIRCUIT Point Query Optimization
+
+For high-concurrency point queries on UNIQUE MoW tables, all 4 prerequisites must be met:
+
+1. `enable_unique_key_merge_on_write = "true"` (MoW enabled)
+2. `store_row_column = "true"` (row-store mode)
+3. `light_schema_change = "true"` (default since 2.0)
+4. WHERE clause uses only key columns
+
+**Performance:** 16-core single node can achieve 30K QPS, avg 7ms, P99 17ms.
+
+Reference: [Prefix Index / Cluster Key](https://doris.apache.org/docs/table-design/index/prefix-index#cluster-key)
+
+## Cluster Key for UNIQUE Tables
+
+When queries filter on columns different from the primary key, use cluster_key to decouple sort order from dedup key. Dedup by primary key, but data sorted by cluster_key columns (faster for filtered queries).
+
+## Cloud Mode Considerations
+
+In Cloud mode (decoupled storage), UNIQUE MoW tables have additional overhead:
+- Delete bitmap synced via RPC (slight write latency)
+- HASH bucketing required (RANDOM not supported for MoW in cloud)
+- For snapshot-consistent point queries: `enable_snapshot_point_query = false` can skip version sync RPC
+
+For DDL implementation of these decisions, delegate to `doris-best-practices` rules: `schema-model-prefer-mow`, `schema-model-sequence-col-for-cdc`, `schema-keys-cluster-key-for-mow`, `usecase-cdc-sync`, `usecase-point-query`.
diff --git a/skills/doris-architecture-advisor/references/decision-query-acceleration.md b/skills/doris-architecture-advisor/references/decision-query-acceleration.md
new file mode 100644
index 0000000..232d51d
--- /dev/null
+++ b/skills/doris-architecture-advisor/references/decision-query-acceleration.md
@@ -0,0 +1,76 @@
+# Query Acceleration
+
+Covers materialized views, indexes, point query optimization, and caching.
+
+Reference: [Sync MV](https://doris.apache.org/docs/query-acceleration/materialized-view/sync-materialized-view), [Async MV](https://doris.apache.org/docs/query-acceleration/materialized-view/async-materialized-view), [Inverted Index](https://doris.apache.org/docs/table-design/index/inverted-index), [BloomFilter](https://doris.apache.org/docs/table-design/index/bloomfilter), [Data Distribution](https://doris.apache.org/docs/table-design/data-partitioning/data-distribution)
+
+## Materialized Views
+
+### Decision: Sync vs Async MV
+
+| Condition | Recommendation | Source |
+|-----------|---------------|--------|
+| Single-table aggregation on DUP/AGG table | Sync MV | official |
+| Multi-table JOIN acceleration | Async MV | official |
+| Must be fresh < 1 hour, base table updates ≤5/hr | Async MV with ON COMMIT | official |
+| Complex transform, freshness > 1 hour OK | Async MV with SCHEDULE | official |
+| UNIQUE KEY table needs aggregation | Async MV (sync MV not supported) | official |
+| Layer-to-layer ETL (ODS→DWD→DWS→ADS) | Async MV with partition_sync_limit | official |
+
+### Capacity Planning
+- ~20-30 active async MVs on a 3-node cluster (16 vCore, 128GB RAM)
+- Each MV costs ~10-15GB storage + refresh compute
+- Max 3 concurrent refresh tasks
+- Max 30 partitions per MV, max ~50M rows per MV
+- Resource ceiling: 40% of cluster for all MV refresh + 20% buffer
+- Use `partition_sync_limit` to refresh only recent partitions
+
+## Indexes
+
+### When to Use Which Index
+
+| Condition | Index type | Source |
+|-----------|-----------|--------|
+| Full-text search on text columns | Inverted Index (unicode/english/chinese parser) | official |
+| Equality/range filter on non-key column | Inverted Index (no parser) | official |
+| High-cardinality equality filter (≥5000 distinct, `=` or `IN`) | BloomFilter | official |
+| LIKE '%pattern%' substring search | NGram BloomFilter | official |
+| Medium-cardinality dimension (100-100K distinct) | Bitmap Index | official |
+| ANN vector search (<100M vectors, online) | HNSW | official |
+| ANN vector search (>100M vectors, batch) | IVF_FLAT or IVF_PQ | official |
+
+### Key Constraints
+- BloomFilter: not supported on TINYINT, FLOAT, DOUBLE. Only accelerates `=` and `IN`.
+- Bitmap: sweet spot 100-100K distinct values. Below 100 wastes space; above 100K degrades.
+- Vector HNSW memory: ~650MB per 1M vectors at 128-dim.
+- CDP/funnel analysis: use `bitmap_union()` for union, `bitmap_intersect()` for retention.
+
+## Point Query Optimization
+
+| Technique | Effect | Source |
+|-----------|--------|--------|
+| `store_row_column = "true"` | Row-store mode, single I/O per row | official |
+| PreparedStatement | Reuse query plan, reduce parse overhead | official |
+| BloomFilter on lookup key | Skip tablets without the key | official |
+| Row cache | Cache individual rows for repeated lookups | derived |
+
+See `decision-mutable-state.md` for the 4-prerequisite SHORT-CIRCUIT checklist.
+
+## Caching
+
+| Cache type | Use when | Source |
+|-----------|----------|--------|
+| SQL cache | Repeated identical queries (dashboard auto-refresh) | official |
+| Partition cache | Time-series with mostly historical data | official |
+| File cache (cloud) | Decoupled mode — 60% cached = 95% hit rate | derived |
+| Row cache | Point queries on MoW tables | derived |
+
+## Colocation Join
+
+For zero-shuffle JOINs, all 4 conditions must match:
+1. Same `colocate_with` group name
+2. Same bucket key column(s) and types
+3. Same bucket count
+4. Same replication_num
+
+For DDL implementation of these decisions, delegate to `doris-best-practices` rules: `schema-mv-sync-rollup`, `schema-mv-async-join`, `schema-mv-async-limits`, `schema-index-inverted`, `schema-index-bloomfilter`, `schema-index-bitmap`, `schema-index-vector`, `usecase-point-query`, `usecase-star-schema-join`.
diff --git a/skills/doris-architecture-advisor/references/decision-sizing-matrix.md b/skills/doris-architecture-advisor/references/decision-sizing-matrix.md
new file mode 100644
index 0000000..de739e6
--- /dev/null
+++ b/skills/doris-architecture-advisor/references/decision-sizing-matrix.md
@@ -0,0 +1,132 @@
+# Sizing Matrix
+
+Size the cluster BEFORE producing DDL. Match the user's workload characteristics to a tier.
+
+## CRITICAL: How to Read These Tables
+
+**All values are TOTAL CLUSTER resources, NOT per-node.**
+
+- "32 vCPU" means 32 vCPU total for the entire cluster
+- "1 TB cache" means 1 TB total cache across all nodes
+- {{CLOUD_PRODUCT_NAME}} manages node count automatically: ≤16 vCPU = 1 node, above 16 = multiples of 16 (e.g., 32 vCPU = 2 nodes of 16 vCPU each)
+- **Do not break down into per-node specs in the output.** Just state the total vCPU and cache.
+
+## Important Notes
+
+1. **Purpose:** Quick estimation for sales/marketing to scope opportunities. ~75% accuracy — detailed sizing requires confirmation with a {{PRODUCT_NAME}} Solutions Architect.
+2. **Cache:** Recommend caching ≥50% of hot data to maintain ≥90% cache hit rate.
+3. **Replicas:** Cloud mode uses single replica for both object storage and cache.
+
+Source: {{PRODUCT_NAME}} internal sizing guide.
+
+---
+
+## Report Analytics
+
+Multi-dimensional statistics and reporting on wide tables.
+
+| Total vCPU | Total Cache | Write MB/s | Write rows/s | Hot data | QPS | Latency | 80% query scan |
+|------------|-------------|-----------|-------------|----------|-----|---------|----------------|
+| 16 vCPU | 200 GB | 1-50 | 10K-100K | 300 GB | 50 | 50ms-1s | <50M rows |
+| 32 vCPU | 1 TB | 100-200 | 100K-200K | 1 TB | 200 | 50ms-1s | <200M rows |
+| 64 vCPU | 3 TB | 200-500 | 200K-500K | 3 TB | 300 | 1-2s | <500M rows |
+| 128 vCPU | 10 TB | 500-800 | 500K-1M | 10 TB | 400 | 1-3s | <3B rows |
+
+## Ad-hoc Analytics
+
+Exploratory analysis on detail tables or multi-table joins.
+
+| Total vCPU | Total Cache | Write MB/s | Write rows/s | Hot data | QPS | Latency | 80% query scan |
+|------------|-------------|-----------|-------------|----------|-----|---------|----------------|
+| 16 vCPU | 200 GB | 1-50 | 10K-100K | 500 GB | 50 | 50ms-1s | <50M rows & 100K |
+| 32 vCPU | 1 TB | 100-200 | 100K-200K | 2 TB | 80 | 1-2s | <100M rows & 2M |
+| 64 vCPU | 3 TB | 200-500 | 200K-500K | 4 TB | 100 | 1-3s | <300M rows & 5M |
+| 128 vCPU | 10 TB | 500-800 | 500K-1M | 10 TB | 150 | 1-3s | <500M rows & 20M |
+
+## Lakehouse Federation
+
+Interactive analysis on external data lake data (Iceberg, Hudi, Paimon).
+
+| Total vCPU | Total Cache | QPS | Latency | 80% query scan |
+|------------|-------------|-----|---------|----------------|
+| 16 vCPU | 200 GB | 20 | 1-3s | <200M rows & 1M |
+| 32 vCPU | 1 TB | 30 | 1-5s | <500M rows & 5M |
+| 64 vCPU | 3 TB | 40 | 1-8s | <800M rows & 10M |
+| 128 vCPU | 10 TB | 50 | 3-10s | <1.2B rows & 20M |
+
+## Log Storage & Search
+
+Log ingestion, full-text search, time-bounded queries.
+
+| Total vCPU | Total Cache | Write MB/s | Write rows/s | Hot data | QPS | Latency | 80% query scan |
+|------------|-------------|-----------|-------------|----------|-----|---------|----------------|
+| 16 vCPU | 1 TB | 1-50 | 10K-100K | 1 TB | 50 | 50ms-1s | <2B rows |
+| 32 vCPU | 3 TB | 100-200 | 100K-200K | 3 TB | 100 | 1-3s | <5B rows |
+| 64 vCPU | 5 TB | 200-500 | 200K-500K | 5 TB | 150 | 1-4s | <30B rows |
+| 128 vCPU | 10 TB | 500-800 | 500K-1M | 10 TB | 200 | 1-5s | <80B rows |
+
+## Real-time Data Warehouse (ETL/ELT)
+
+Incremental data processing and transformation within {{PRODUCT_NAME}}.
+
+| Total vCPU | Total Cache | Daily ETL jobs | 80% ETL time | 80% ETL scan |
+|------------|-------------|---------------|-------------|-------------|
+| 16 vCPU | 200 GB | 300 | 30s | <20M rows & 300K |
+| 32 vCPU | 1 TB | 800 | 30s-2min | <200M rows & 1M |
+| 64 vCPU | 3 TB | 1,000 | 30s-2min | <400M rows & 5M |
+| 128 vCPU | 10 TB | 2,000 | 30s-5min | <1B rows & 50M |
+
+## Point Query Service
+
+Key-value point lookups via API. **Note:** High QPS point query requires FE resource upgrades — coordinate with {{PRODUCT_NAME}} for FE sizing.
+
+| Total vCPU | Total Cache | Write MB/s | Write rows/s | Hot data | QPS | Latency | 80% query scan |
+|------------|-------------|-----------|-------------|----------|-----|---------|----------------|
+| 16 vCPU | 200 GB | 1-50 | 10K-100K | 500 GB | 50,000 | 30-100ms | <3B rows |
+| 32 vCPU | 1 TB | 100-200 | 100K-200K | 1 TB | 100,000 | 30-100ms | <10B rows |
+| 64 vCPU | 3 TB | 200-500 | 200K-500K | 3 TB | 150,000 | 30-100ms | <30B rows |
+| 128 vCPU | 10 TB | 500-800 | 500K-1M | 10 TB | 200,000 | 30-100ms | <50B rows |
+
+---
+
+## How to Select a Tier
+
+1. **Identify primary workload type** from the 6 categories above
+2. **Calculate write throughput:** `daily_bytes / active_hours / 3600`
+3. **Estimate hot data:** `daily_data x retention_days x compression_ratio`
+4. **Identify the dominant constraint** — usually hot data size or QPS
+5. **Pick the smallest tier** where ALL constraints fit
+6. **For composite workloads** (e.g., IoT = time-series + point query + logs), size for the most demanding sub-workload, or consider multiple compute groups
+
+## Storage Estimate
+
+```
+Required storage = raw_data x compression_ratio x 1 (single replica in cloud)
+```
+
+| Data type | Typical compression ratio |
+|-----------|--------------------------|
+| Structured numeric | 5-8x with ZSTD |
+| Logs / text | 10-20x with ZSTD |
+| JSON / semi-structured | 5-12x with ZSTD |
+
+Add 20-30% overhead for metadata, compaction temp space, and safety margin.
+
+## Cloud Node Scaling
+
+| Total cores | Nodes | Per-node |
+|-------------|-------|----------|
+| 4-16 | 1 | 4-16 cores |
+| 32 | 2 | 16 cores each |
+| 64 | 4 | 16 cores each |
+| 128 | 8 | 16 cores each |
+
+## FE Sizing
+
+| Cluster size | FE nodes | Memory | CPU |
+|-------------|----------|--------|-----|
+| Small (<10 BE) | 1 Leader + 2 Follower | 16 GB | 8 cores |
+| Medium (10-50 BE) | 1 Leader + 2 Follower | 32 GB | 16 cores |
+| Large (50+ BE) | 1 Leader + 4 Follower | 64 GB | 32 cores |
+
+Point query workloads with >50K QPS require FE upgrades — contact {{PRODUCT_NAME}} support.
diff --git a/skills/doris-architecture-advisor/references/decision-time-series-design.md b/skills/doris-architecture-advisor/references/decision-time-series-design.md
new file mode 100644
index 0000000..9bd84a5
--- /dev/null
+++ b/skills/doris-architecture-advisor/references/decision-time-series-design.md
@@ -0,0 +1,148 @@
+# Time-Series Design
+
+For append-only, time-ordered data: sensor readings, logs, events, metrics.
+
+## Decision Framework: Partition Strategy
+
+| Condition | Recommendation | Source |
+|-----------|---------------|--------|
+| Table < 1 GB total | No partition — just bucketing | official |
+| 1-100 GB, time-series | Consider RANGE partition if retention or pruning needed | derived |
+| > 100 GB, time-series | Always RANGE partition on time column | official |
+| Continuous data, predictable daily volume | Dynamic partition (auto-creates future, auto-drops old) | official |
+| Sporadic data (not every day has data) | AUTO PARTITION (creates on demand, no empty partitions) | official |
+| Need automated TTL / data lifecycle | Dynamic partition with `start` parameter | official |
+
+**Never combine AUTO PARTITION with dynamic_partition on the same table.**
+
+Reference: [Range Partition](https://doris.apache.org/docs/table-design/data-partitioning/range-partitioning), [Auto Partition](https://doris.apache.org/docs/table-design/data-partitioning/auto-partitioning), [Dynamic Partition](https://doris.apache.org/docs/table-design/data-partitioning/dynamic-partitioning)
+
+## Dynamic Partition for TTL
+
+```sql
+PROPERTIES (
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "DAY",
+ "dynamic_partition.start" = "-7", -- keep 7 days
+ "dynamic_partition.end" = "3", -- pre-create 3 future days
+ "dynamic_partition.prefix" = "p"
+);
+```
+
+**Granularity selection:**
+- HOUR: very high volume (>100GB/day) or sub-hour queries
+- DAY: standard time-series (1-100GB/day)
+- MONTH: low volume (<1GB/day) or long retention (years)
+
+**Multi-tier retention** (for large historical datasets):
+- Recent data: daily partitions (last 90 days)
+- Historical: monthly or yearly partitions
+- Implement with multiple tables or manual partition management
+
+## Time-Series Compaction
+
+For sustained high-throughput writes (tens of GB/s), enable time-series compaction:
+
+```sql
+PROPERTIES (
+ "compaction_policy" = "time_series"
+);
+```
+
+**How it works:** Time-series data is inherently ordered and steady-rate. The compaction skips traditional read-sort-merge and instead hard-links Rowset files into new Rowsets. Millisecond-level compaction with near-zero memory overhead.
+
+**When to use:** Append-only time-series with steady write rate and ordered timestamps.
+
+## Bucket Strategy for Time-Series
+
+| Condition | Bucket strategy | Source |
+|-----------|----------------|--------|
+| Queries always filter on a specific entity (device_id, user_id) | HASH on that entity | official |
+| Queries are full-scan time-range (log search, no entity filter) | RANDOM bucketing | official |
+| Need colocation JOIN with dimension table | HASH on join key, match bucket count | official |
+
+### CRITICAL: Always Calculate Explicit Bucket Count
+
+Always write a numeric bucket count in production DDL. Calculate from known data volumes; if inputs are incomplete, choose an explicit conservative fallback rather than automatic bucket sizing.
+
+**Formula:**
+```
+1. partition_data_GB = daily_raw_data_GB / partition_count_per_day
+ (usually 1 partition per day, so = daily_raw_data_GB)
+
+2. compressed_GB = partition_data_GB × compression_ratio
+ - CSV/text source data: use 0.3-0.5 (default 0.5)
+ - MySQL/OLTP source data: use 0.7
+ - ZSTD on logs: use 0.05-0.1
+ - ZSTD on structured numeric: use 0.1-0.2
+
+3. buckets = compressed_GB / target_tablet_GB
+ - Target: 1-10 GB per tablet (use 2 GB as default target)
+ - Max 64 buckets per partition
+ - Minimum 1 bucket
+
+4. Round to nearest power of 2 or reasonable number
+```
+
+**Worked examples:**
+
+```
+Example 1: IoT sensor readings — 210 GB/day raw, ZSTD, daily partitions
+ compressed = 210 × 0.1 = 21 GB/partition
+ buckets = 21 / 2 = ~10 buckets
+ → DISTRIBUTED BY HASH(device_id) BUCKETS 10
+
+Example 2: Application logs — 500 GB/day raw, ZSTD, daily partitions
+ compressed = 500 × 0.05 = 25 GB/partition
+ buckets = 25 / 2 = ~12 buckets
+ → DISTRIBUTED BY HASH(service_name) BUCKETS 12
+
+Example 3: CDC orders — 50 GB total, no partition, LZ4
+ compressed = 50 × 0.2 = 10 GB total
+ buckets = 10 / 2 = 5 buckets
+ → DISTRIBUTED BY HASH(order_id) BUCKETS 5
+
+Example 4: Small dimension table — 500 MB
+ → DISTRIBUTED BY RANDOM BUCKETS 3
+
+Example 5: Large fact table — 2 TB/day, ZSTD, daily partitions
+ compressed = 2000 × 0.1 = 200 GB/partition
+ buckets = 200 / 4 = 50 buckets (target 4 GB for large tablets)
+ → DISTRIBUTED BY HASH(user_id) BUCKETS 50
+```
+
+**Warning signs after table creation:**
+- `SHOW TABLETS FROM table_name` → check size distribution
+- Tablets < 100 MB → too many buckets, reduce
+- Tablets > 20 GB → too few buckets, increase
+
+Reference: [Data Distribution](https://doris.apache.org/docs/table-design/data-partitioning/data-distribution)
+
+## Sort Key for Time-Series
+
+Place columns in this order in DUPLICATE KEY:
+1. Most frequently filtered column (often entity_id for pruning)
+2. Time column (for range scans)
+3. Additional filter columns
+
+```sql
+-- Entity-first: queries usually filter by device
+DUPLICATE KEY(device_id, event_time, event_type)
+
+-- Time-first: queries usually scan time ranges across all devices
+DUPLICATE KEY(event_time, service_name, severity)
+```
+
+## Compression
+
+| Data type | Recommendation | Source |
+|-----------|---------------|--------|
+| Logs, text-heavy data | ZSTD (2-3x better ratio than LZ4) | official |
+| Hot analytical data, low-latency reads | LZ4 (faster decompression) | official |
+| IoT sensor numeric data | ZSTD (high redundancy in repeating values) | derived |
+
+```sql
+PROPERTIES ("compression" = "zstd");
+```
+
+ZSTD compresses log data to ~1/10 of raw size. LZ4 to ~1/5.
diff --git a/skills/doris-architecture-advisor/references/decision-workload-classification.md b/skills/doris-architecture-advisor/references/decision-workload-classification.md
new file mode 100644
index 0000000..1e01e89
--- /dev/null
+++ b/skills/doris-architecture-advisor/references/decision-workload-classification.md
@@ -0,0 +1,92 @@
+# Workload Classification
+
+Identify the user's workload type before any sizing or DDL. Many real-world systems are composite — decompose into sub-workloads and design a table per sub-workload.
+
+## 6 Workload Types
+
+### 1. Report Analytics
+Multi-dimensional statistics and reporting on wide tables.
+- **Signals:** dashboard, report, BI tool, Grafana, Superset, aggregation queries, GROUP BY
+- **Data shape:** Pre-joined wide tables, mostly read, moderate write
+- **Query pattern:** Fixed-dimension GROUP BY, filtered aggregations, low latency
+- **Typical model:** DUPLICATE (flexible ad-hoc) or AGGREGATE (pre-computed metrics)
+
+### 2. Ad-hoc Analytics
+Exploratory analysis on detail data, often with multi-table joins.
+- **Signals:** data exploration, data science, Jupyter, "we don't know the queries yet", flexible analysis
+- **Data shape:** Detail-level fact tables + dimension tables, star/snowflake schema
+- **Query pattern:** Unpredictable JOINs, variable filters, moderate latency (1-3s OK)
+- **Typical model:** DUPLICATE for facts, DUPLICATE/UNIQUE for dimensions
+
+### 3. Lakehouse Federation
+Interactive analysis on external data lake data (Hive, Iceberg, Hudi, Paimon).
+- **Signals:** data lake, Iceberg, Hudi, S3, external table, federated query, Trino replacement
+- **Data shape:** External catalogs + optional internal materialized tables
+- **Query pattern:** Cross-source JOINs, moderate latency (1-8s)
+- **Typical model:** External catalog + async MV for hot data acceleration
+
+### 4. Log Storage & Search
+High-volume log ingestion with full-text search and time-bounded queries.
+- **Signals:** logs, Elasticsearch replacement, log search, keyword search, MATCH, observability, traces, error tracking
+- **Data shape:** Append-only, semi-structured (text + structured fields), high volume
+- **Query pattern:** Time-range + keyword search, aggregated error rates, trace correlation
+- **Typical model:** DUPLICATE + inverted index + ZSTD compression
+
+### 5. Real-time Data Warehouse / ETL
+Incremental data processing and transformation within {{PRODUCT_NAME}}.
+- **Signals:** ETL, ELT, data pipeline, dbt, materialized view, incremental processing, ODS/DWD/DWS/ADS layers
+- **Data shape:** Layered warehouse model (ODS → DWD → DWS → ADS)
+- **Query pattern:** Scheduled transformations, INSERT INTO SELECT, async MV refresh
+- **Typical model:** Mix of DUPLICATE (ODS) + AGGREGATE (DWS) + async MV (cross-layer)
+
+### 6. Point Query Service
+High-concurrency, low-latency key-value lookups served via API.
+- **Signals:** API serving, user profile lookup, key-value, sub-100ms, high QPS (>10K), PreparedStatement
+- **Data shape:** Wide row, single-key lookup, moderate updates
+- **Query pattern:** `SELECT * FROM t WHERE id = ?`, single-row result
+- **Typical model:** UNIQUE MoW + `store_row_column = "true"` + BloomFilter
+
+## Composite Workload Examples
+
+| User scenario | Sub-workloads | Tables needed |
+|---------------|---------------|---------------|
+| IoT platform | Time-series (readings) + Mutable state (device shadow) + Logs (device logs) + Report (dashboards) + Point query (device status API) | 4-5 tables |
+| E-commerce analytics | CDC sync (orders) + Report (revenue dashboards) + Ad-hoc (customer behavior) | 2-3 tables |
+| Observability platform | Log search (logs) + Time-series (traces) + Pre-agg metrics (metrics) | 3 tables |
+| Fleet / vehicle tracking | Time-series (GPS events) + Mutable state (vehicle status) + Report (route analytics) | 3 tables |
+
+## Cross-cutting Patterns
+
+These patterns apply across multiple workload types:
+
+### Warehouse Layering (ODS → DWD → DWS → ADS)
+When building a real-time data warehouse, use different data models per layer:
+- **ODS (raw):** UNIQUE MoW — dedup on CDC replay. **If partitioned by time, the time column MUST be in UNIQUE KEY:** `UNIQUE KEY(id, event_time) PARTITION BY RANGE(event_time)`
+- **DWD (detail):** UNIQUE MoW — cleaned detail data, row-level updates. Same rule: partition column in key
+- **DWS (summary):** DUPLICATE — wide tables from joins, fastest scan for analytics
+- **ADS (application):** AGGREGATE — pre-computed KPIs, auto-rollup on ingest
+- **DIM (dimension):** UNIQUE MoW — slowly changing dimensions with updates
+- Layer-to-layer ETL via async MV or scheduled `INSERT INTO SELECT`
+
+### Workload Isolation (Multi-department / Multi-tenant)
+When different teams share the same cluster:
+- Use **Workload Groups** to isolate CPU/memory per department
+- Prevents risk team's heavy queries from impacting advisor dashboards
+- `CREATE WORKLOAD GROUP wg_risk PROPERTIES ("cpu_share"="30", "memory_limit"="30%")`
+
+### Lakehouse Federation (Hot/Cold Tiering)
+When historical data lives in a data lake (Hive/Iceberg/Hudi):
+- Hot data (recent 1 year): stored in {{PRODUCT_NAME}} for sub-second queries
+- Cold data (3+ years): stays in data lake, queried via Multi-Catalog
+- Accelerate repeated lake queries with async MV
+- `file_cache_ttl_seconds` controls per-table cache lifetime
+
+## Discovery Questions
+
+If the workload type is unclear, ask:
+
+1. **Data mutability:** Is data append-only, or do you need to update/delete rows?
+2. **Write volume:** How much data per day? How many rows per second?
+3. **Query pattern:** Fixed reports, or ad-hoc exploration? Single-row lookups or scans?
+4. **Latency target:** Sub-100ms (point query), sub-second (report), 1-3s (ad-hoc)?
+5. **Retention:** How long do you keep data? Is there a hot/cold boundary?
diff --git a/skills/doris-architecture-advisor/references/example-adtech-marketing.md b/skills/doris-architecture-advisor/references/example-adtech-marketing.md
new file mode 100644
index 0000000..0378b12
--- /dev/null
+++ b/skills/doris-architecture-advisor/references/example-adtech-marketing.md
@@ -0,0 +1,70 @@
+# Example: Programmatic Digital Marketing / AdTech
+
+## Scenario
+
+- **Workload:** Global DSP/ADX platform serving ads across Google, Meta, TikTok, Snapchat in 200+ countries
+- **Data sources:** SSP ad requests (Kafka), RTB bid logs, impression/click/conversion logs, user behavior (SDK), third-party attribution (AppsFlyer), ad creatives (images/video/text from S3)
+- **Data volume:** 10B+ ad requests/day, hundreds of TB daily, PB-scale total
+- **Query patterns:** Millisecond user profile lookups for real-time bidding (RTB), second-level campaign KPI dashboards (CTR, CVR, ROI), advertiser self-service reporting, creative performance analysis, fraud detection
+- **Special requirements:** Dual-path architecture (millisecond serving + second-level analytics), multi-modal data (structured + semi-structured JSON + unstructured creative assets), VARIANT for cross-platform schema differences, vector index for creative similarity search, global multi-region deployment
+
+## Workload Classification
+
+**Composite workload** — 6 sub-workloads:
+
+| Sub-workload | Type | Decision pattern |
+|-------------|------|-----------------|
+| User profile labels (RTB serving) | Point query | UNIQUE MoW + store_row_column for millisecond lookups during bidding |
+| Ad delivery detail logs | Time-series (append-only) | DUPLICATE + RANGE partition, DWD wide table joining impression+click+conversion |
+| Campaign KPI dashboards | Pre-aggregation | AGGREGATE + sync/async MV for CTR/CVR/ROI rollup |
+| Advertiser self-service reports | Report + Ad-hoc | Query on DWS/ADS layer with advertiser_id filter + workload isolation |
+| Creative metadata analysis | Report + Search | VARIANT for cross-platform JSON metadata + vector index for similarity |
+| Traffic quality / fraud detection | Log search | DUPLICATE + inverted index + sync MV for real-time fraud scoring |
+
+## AdTech-specific Design Patterns
+
+### Dual-path architecture: RTB serving + analytics
+The core challenge: the bidding engine needs millisecond access to user profiles, while analytics needs second-level aggregation on billions of logs.
+
+- **Serving path (millisecond):** User profile labels stored in UNIQUE MoW with `store_row_column = true`. Bidding engine queries by user_id via PreparedStatement at 30K+ QPS. Replaces Redis for profile lookups.
+- **Analytics path (second-level):** Impression/click/conversion logs stored in DUPLICATE model. Sync MV for real-time CTR/CVR. Async MV for campaign-level aggregations refreshed every 1-5 minutes.
+- **Bridge:** Flink computes real-time features (CTR prediction, budget deduction, frequency cap) and writes back to both the serving table and the analytics table.
+
+### Multi-modal data storage
+- **Structured:** Channel, device, advertiser master data — standard typed columns (Date, String, Int)
+- **Semi-structured:** User-Agent, request body, creative tags — VARIANT type + inverted index for full-text search
+- **Unstructured:** Creative assets (images, video, text) — vectorized via embedding, stored with vector index for similarity search (find creatives similar to top performers)
+
+### DWD + DWS + ADS for ad analytics
+- **DWD (detail):** Wide table joining ad request + impression + click + conversion logs. Supports multi-dimensional drill-down and attribution analysis.
+- **DWS (summary):** Sync MV for real-time CTR/CVR computation. Async MV for campaign/channel/creative-level rollups.
+- **ADS (application):** Pre-aggregated KPIs by advertiser × campaign × day. Sub-second dashboard queries for 10,000+ concurrent advertisers.
+
+### Platform consolidation: Redis + MySQL + HBase + Hive → {{PRODUCT_NAME}}
+| Old system | Role | {{PRODUCT_NAME}} replacement |
+|-----------|------|-------------------|
+| Redis | RTB user profile serving | UNIQUE MoW + store_row_column (30K QPS, <10ms) |
+| MySQL | Campaign management | Flink CDC → UNIQUE MoW |
+| HBase | User behavior history | DUPLICATE + partition + time_series compaction |
+| Hive/Spark | Offline attribution | Async MV + Multi-Catalog for lakehouse |
+
+Result: TCO reduction 30-50%, unified data layer, one SQL interface.
+
+### Cross-border / multi-region
+- {{CLOUD_PRODUCT_NAME}} supports AWS, GCP, Azure deployment in any region
+- SAAS and BYOC models for different compliance requirements (GDPR, CCPA)
+- Multi-currency conversion handled in DWS layer via dimension table JOINs
+- Each region can have its own compute group with workload isolation
+
+## Decision Provenance
+
+| Decision | Source |
+|----------|--------|
+| UNIQUE MoW + store_row_column for RTB serving | `official` — millisecond point queries replace Redis |
+| DUPLICATE for ad log detail | `official` — append-only impression/click/conversion events |
+| Sync MV for real-time CTR/CVR | `official` — zero-lag aggregation on DWD detail table |
+| VARIANT for cross-platform JSON | `official` — different ad platforms have different request schemas |
+| Vector index for creative similarity | `official` — HNSW for ANN search on creative embeddings |
+| Workload Groups for advertiser isolation | `official` — 10K concurrent advertisers, prevent cross-impact |
+| Dual-path (serving + analytics) | `derived` — same {{PRODUCT_NAME}} instance serves both RTB and dashboard workloads |
+| Multi-Catalog for offline attribution | `official` — Hive/S3 data queried without movement |
diff --git a/skills/doris-architecture-advisor/references/example-cdc-operational-sync.md b/skills/doris-architecture-advisor/references/example-cdc-operational-sync.md
new file mode 100644
index 0000000..49e5026
--- /dev/null
+++ b/skills/doris-architecture-advisor/references/example-cdc-operational-sync.md
@@ -0,0 +1,95 @@
+# Example: CDC Operational Sync
+
+## Scenario
+
+- **Workload:** Replicate MySQL order management system for real-time analytics
+- **Source:** MySQL orders table (~100M rows, 50GB), ~5000 updates/sec peak
+- **Query patterns:** order status reports, revenue by region, fulfillment metrics
+- **Latency target:** data freshness <10s, query latency <1s
+- **Retention:** full history (no TTL)
+
+## Workload Classification
+
+| Sub-workload | Type | Table |
+|-------------|------|-------|
+| Order data sync | CDC / Mutable state | `orders_sync` |
+| Revenue dashboards | Report analytics | Async MV or query on orders_sync |
+| Store dimension | Small lookup table | `dim_stores` |
+
+## Sizing Recommendation
+
+- **Write:** 5K updates/sec × ~500 bytes = ~2.5 MB/s (modest)
+- **Hot data:** 50GB × 0.2 (compression) = ~10 GB
+- **QPS:** ~200 (dashboard + reports)
+- **Recommendation:** 16 vCPU total cluster, 200 GB total cache is sufficient (Report Analytics matrix)
+
+## Table Designs
+
+### 1. orders_sync (UNIQUE MoW — CDC target)
+
+```sql
+CREATE TABLE orders_sync (
+ order_id BIGINT NOT NULL,
+ customer_id INT NOT NULL,
+ store_id INT NOT NULL,
+ order_date DATETIME NOT NULL,
+ update_time DATETIME NOT NULL,
+ status VARCHAR(20),
+ region VARCHAR(50),
+ amount DECIMAL(12,2),
+ quantity INT
+) ENGINE=OLAP
+UNIQUE KEY(order_id)
+DISTRIBUTED BY HASH(order_id) BUCKETS 5 -- 50 GB × 0.2 compression = 10 GB ÷ 2 GB target = 5 buckets
+PROPERTIES (
+ "enable_unique_key_merge_on_write" = "true",
+ "function_column.sequence_col" = "update_time",
+ "light_schema_change" = "true",
+ "replication_num" = "1"
+);
+-- No partition: 50GB is below the 100GB threshold.
+-- HASH on order_id: point lookups on PK prune to one tablet.
+-- sequence_col: Flink CDC events may arrive out of order.
+```
+
+### 2. dim_stores (DUPLICATE — small dimension table)
+
+```sql
+CREATE TABLE dim_stores (
+ store_id INT NOT NULL,
+ region VARCHAR(50),
+ city VARCHAR(50),
+ manager VARCHAR(100)
+) ENGINE=OLAP
+DUPLICATE KEY(store_id)
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
+);
+-- Small dimension (<1GB): RANDOM bucketing, 3 buckets for even distribution.
+-- DUPLICATE not UNIQUE: dimension data rarely changes.
+-- For colocation JOIN, switch to HASH(store_id) and match orders_sync bucket count.
+```
+
+## Ingestion Architecture
+
+```
+MySQL (orders) → Flink CDC → Stream Load → orders_sync (UNIQUE MoW)
+ → dim_stores (one-time bulk load)
+```
+
+**Flink CDC setup:**
+- Full snapshot on first run, then incremental binlog streaming
+- Exactly-once delivery via Flink checkpointing
+- `sequence_col = "update_time"` ensures correct ordering even on replay
+
+## Key Decisions
+
+| Decision | Why | Source |
+|----------|-----|--------|
+| UNIQUE MoW (not AGG) | CDC requires UPDATE/DELETE support | official |
+| No partition | 50GB total — below 100GB partition threshold | official |
+| sequence_col on update_time | CDC events may arrive out of order | official |
+| Flink CDC (not Routine Load) | MySQL binlog requires CDC framework, not Kafka consumer | derived |
+| RANDOM bucket for dim_stores | Small table, no dominant filter column | official |
+| No async MV for dashboards | With 100M rows and simple aggregations, direct query is fast enough on MoW | field |
diff --git a/skills/doris-architecture-advisor/references/example-gaming.md b/skills/doris-architecture-advisor/references/example-gaming.md
new file mode 100644
index 0000000..5929776
--- /dev/null
+++ b/skills/doris-architecture-advisor/references/example-gaming.md
@@ -0,0 +1,65 @@
+# Example: Gaming Industry — Real-time Player Analytics Platform
+
+## Scenario
+
+- **Workload:** Mobile/PC game with millions of DAU, real-time operations, player profiling, behavior analysis
+- **Data sources:** MySQL (operational data), Kafka (player behavior events, real-time tags), Hive/S3 (offline behavior, offline tags), third-party (ad attribution, channel data)
+- **Data volume:** 2B+ behavior events/day (TB-scale daily), 50M+ registered users, hundreds of TB total
+- **Query patterns:** Real-time KPI dashboards (DAU, retention, ARPU), player profiling with BITMAP segmentation, retention/funnel analysis, anomaly detection for anti-cheat, Agentic NL2SQL analytics
+- **Special requirements:** Sub-second dashboard refresh, 1-10s for complex behavioral queries, real-time tag updates within 10 seconds, lakehouse integration for offline data
+
+## Workload Classification
+
+**Composite workload** — 5 sub-workloads:
+
+| Sub-workload | Type | Decision pattern |
+|-------------|------|-----------------|
+| Player behavior events | Time-series (append-only) | DUPLICATE + RANGE partition by day + time_series compaction |
+| Player profiles (wide + tall table) | Point query + Segmentation | AGGREGATE wide table + BITMAP tall table (same as retail CDP) |
+| Game KPI dashboards | Pre-aggregation | AGGREGATE + sync/async MV |
+| Anti-cheat anomaly detection | Log search + Ad-hoc | DUPLICATE + inverted index on player_id + event_type |
+| Agentic analytics (NL2SQL) | Ad-hoc + Report | All tables queryable via MCP, natural language interface |
+
+## Gaming-specific Design Patterns
+
+### Retention and funnel analysis
+Doris has built-in `retention()` and `window_funnel()` functions for gaming analytics:
+- Retention: Day 1/7/30 retention by cohort (registration date × source channel)
+- Funnel: tutorial_complete → first_battle → first_purchase → first_social_interaction
+- Session analysis: average session length, sessions per day, inter-session gap
+
+### Player profiling: Wide + tall table (same pattern as retail/web3)
+- Wide table: one row per player, columns for each tag (AGGREGATE + REPLACE_IF_NOT_NULL)
+- Tall table: one row per tag value, BITMAP stores player IDs (AGGREGATE + BITMAP_UNION)
+- Real-time tag updates via Kafka → Flink → partial update on wide table
+- Offline tags from Hive/S3 via Catalog or Broker Load
+
+### Agentic analytics platform (NL2SQL + MCP)
+- {{PRODUCT_NAME}} as the data layer for AI Agent applications
+- MCP (Model Context Protocol) interface for AI agents to query data
+- Business domain agents: User Behavior Agent, Operations Agent, A/B Testing Agent, Retention Agent
+- Natural language → SQL → {{PRODUCT_NAME}} → visualization, no coding required
+
+### Anti-cheat: Anomaly detection on behavior events
+- DUPLICATE model for raw behavior events
+- Inverted index on event_type, player_id for fast filtering
+- Window functions to detect: unusual event frequency, impossible timing (1000 battles in 1 hour), suspicious gold transfer patterns
+- Real-time alerting via scheduled queries or async MV
+
+### Lakehouse integration for offline data
+- Real-time: MySQL → Flink CDC → {{PRODUCT_NAME}} (operational data)
+- Real-time: Kafka → Flink → {{PRODUCT_NAME}} (behavior events, real-time tags)
+- Offline: Hive/S3 → Catalog (lakehouse federation, no data movement)
+- Warehouse layers: ODS → DWD (async MV / ETL SQL) → DWS (async MV) → ADS
+
+## Decision Provenance
+
+| Decision | Source |
+|----------|--------|
+| DUPLICATE for behavior events | `official` — append-only, fastest scan for retention/funnel queries |
+| retention() and window_funnel() | `official` — built-in Doris functions for gaming analytics |
+| BITMAP for player segmentation | `official` — sub-second audience selection across 50M players |
+| MCP interface for Agentic analytics | `official` — {{PRODUCT_NAME}} supports MCP for AI agent integration |
+| Sync MV for real-time KPI (1-3s) | `official` — zero-lag aggregation for dashboard metrics |
+| Async MV for near-real-time (1-10min) | `official` — incremental refresh for complex multi-table analytics |
+| Multi-Catalog for offline data | `official` — Hive/Iceberg/Paimon federation without data movement |
diff --git a/skills/doris-architecture-advisor/references/example-iot-sensor-platform.md b/skills/doris-architecture-advisor/references/example-iot-sensor-platform.md
new file mode 100644
index 0000000..9ebedb9
--- /dev/null
+++ b/skills/doris-architecture-advisor/references/example-iot-sensor-platform.md
@@ -0,0 +1,170 @@
+# Example: IoT Sensor Platform
+
+## Scenario
+
+- **Workload:** IoT device monitoring and analytics
+- **Devices:** 50,000+ sensors across multiple protocols (MQTT, Modbus, OPC-UA)
+- **Ingest rate:** 10K readings/sec (50K devices × 1 reading/5s), ~40 MB/s JSON after parsing
+- **Data volume:** 210 GB/day raw, 75 TB/year
+- **Query patterns:** real-time device status, historical trend analysis, alert threshold monitoring, device log search
+- **Latency target:** dashboard queries 1-3s, point queries <100ms
+- **Retention:** 90 days hot, 1 year cold archive to object storage
+- **Special:** devices go offline/online, metadata updates, out-of-order data from network delays
+
+## Workload Classification
+
+**Composite workload** decomposed into:
+
+| Sub-workload | Type | Table |
+|-------------|------|-------|
+| Sensor time-series readings | Log/Time-series | `sensor_readings` |
+| Device state tracking (digital twin) | Mutable state / Point query | `device_shadow` |
+| Device logs (fault diagnosis) | Log/Search | `device_logs` |
+| Device statistics (connection counts, uptime) | Report / Pre-aggregation | `device_stats` |
+
+## Sizing Recommendation
+
+- **Write:** 40 MB/s sustained → fits 32 vCPU total cluster (1 TB total cache)
+- **Hot data:** 90 days × 210GB/day × 0.1 (ZSTD compression) = ~1.9 TB
+- **QPS:** Mixed — dashboard ~100 QPS + point query ~5K QPS
+- **Recommendation:** 32 vCPU total, 1 TB total cache for analytics. Consider separate compute group for point query if >10K QPS
+
+**Storage estimate:**
+```
+75 TB/year raw × ZSTD 10x compression × 1 replica = 7.5 TB/year stored
+3-year retention: ~22.5 TB
+```
+
+**FE:** 3 nodes for HA (managed in Cloud mode).
+
+## Architecture
+
+```
+Sensors → IoT Gateway → Kafka
+ ├─→ Stream Load → {{PRODUCT_NAME}} (sensor_readings, device_shadow)
+ └─→ Cold archive → Object Storage (Minio/S3)
+
+MySQL (ERP/CRM) → Flink CDC → {{PRODUCT_NAME}} (business data)
+
+{{PRODUCT_NAME}} → Grafana (dashboards)
+ → API Server (device status point queries)
+ → Flink (real-time alerts)
+```
+
+## Table Designs
+
+### 1. sensor_readings (DUPLICATE — append-only time-series)
+
+```sql
+CREATE TABLE sensor_readings (
+ device_id VARCHAR(64) NOT NULL, -- Per decision-time-series: entity as sort key first
+ reading_time DATETIME(3) NOT NULL, -- Millisecond precision for IoT
+ sensor_type VARCHAR(30) NOT NULL, -- temperature, humidity, voltage, etc.
+ value DOUBLE,
+ quality_flag TINYINT DEFAULT 0, -- 0=good, 1=suspect, 2=bad
+ data VARIANT -- Per decision-data-model: VARIANT for multi-protocol payloads
+) ENGINE=OLAP
+DUPLICATE KEY(device_id, reading_time, sensor_type)
+PARTITION BY RANGE(reading_time) ()
+DISTRIBUTED BY HASH(device_id) BUCKETS 10 -- 210 GB/day × 0.1 ZSTD = 21 GB/partition ÷ 2 GB target = ~10
+PROPERTIES (
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "DAY",
+ "dynamic_partition.start" = "-90", -- 90-day retention
+ "dynamic_partition.end" = "3",
+ "dynamic_partition.prefix" = "p",
+ "compression" = "zstd", -- Per decision-time-series: ZSTD for IoT numeric data
+ "compaction_policy" = "time_series", -- Per decision-time-series: optimized compaction
+ "replication_num" = "1" -- Cloud mode
+);
+```
+
+### 2. device_shadow (UNIQUE MoW — mutable device state)
+
+```sql
+CREATE TABLE device_shadow (
+ device_id VARCHAR(64) NOT NULL,
+ last_seen DATETIME(3) NOT NULL,
+ status VARCHAR(20) DEFAULT 'unknown', -- online, offline, maintenance
+ firmware_version VARCHAR(30),
+ location VARCHAR(100),
+ owner VARCHAR(100),
+ config VARIANT -- Device-specific config as semi-structured
+) ENGINE=OLAP
+UNIQUE KEY(device_id)
+DISTRIBUTED BY HASH(device_id) BUCKETS 3
+PROPERTIES (
+ "enable_unique_key_merge_on_write" = "true",
+ "function_column.sequence_col" = "last_seen", -- Per decision-mutable-state: handles out-of-order
+ "store_row_column" = "true", -- Per decision-mutable-state: enables point query
+ "light_schema_change" = "true",
+ "replication_num" = "1"
+);
+```
+
+### 3. device_logs (DUPLICATE — log search with inverted index)
+
+```sql
+CREATE TABLE device_logs (
+ device_id VARCHAR(64) NOT NULL,
+ log_time DATETIME NOT NULL,
+ log_level VARCHAR(10) NOT NULL, -- INFO, WARN, ERROR, FATAL
+ message TEXT,
+ INDEX idx_msg(message) USING INVERTED PROPERTIES("parser" = "unicode"),
+ INDEX idx_level(log_level) USING INVERTED
+) ENGINE=OLAP
+DUPLICATE KEY(device_id, log_time, log_level)
+PARTITION BY RANGE(log_time) ()
+DISTRIBUTED BY HASH(device_id) BUCKETS 3
+PROPERTIES (
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "DAY",
+ "dynamic_partition.start" = "-30", -- 30-day log retention
+ "dynamic_partition.end" = "3",
+ "dynamic_partition.prefix" = "p",
+ "compression" = "zstd",
+ "compaction_policy" = "time_series",
+ "replication_num" = "1"
+);
+```
+
+### 4. device_stats (AGGREGATE — pre-computed device metrics)
+
+```sql
+CREATE TABLE device_stats (
+ stat_date DATE NOT NULL,
+ device_type VARCHAR(50) NOT NULL,
+ region VARCHAR(50) NOT NULL,
+ device_count BIGINT SUM DEFAULT "0",
+ online_count BIGINT SUM DEFAULT "0",
+ total_readings BIGINT SUM DEFAULT "0",
+ error_count BIGINT SUM DEFAULT "0",
+ unique_devices BITMAP BITMAP_UNION -- Exact distinct device count
+) ENGINE=OLAP
+AGGREGATE KEY(stat_date, device_type, region)
+PARTITION BY RANGE(stat_date) ()
+DISTRIBUTED BY HASH(device_type) BUCKETS 3 -- aggregated stats, small per-month partition → 3 buckets
+PROPERTIES (
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "MONTH",
+ "dynamic_partition.start" = "-12",
+ "dynamic_partition.end" = "1",
+ "dynamic_partition.prefix" = "p",
+ "replication_num" = "1"
+);
+```
+
+## Decision Provenance
+
+| Decision | Source |
+|----------|--------|
+| DUPLICATE for sensor_readings | official — append-only, fastest scan |
+| VARIANT for multi-protocol data | official — 8x faster than JSON, auto schema evolution |
+| time_series compaction | derived — IoT write pattern matches (ordered, steady rate) |
+| UNIQUE MoW for device_shadow | official — needs upsert for state changes |
+| sequence_col on last_seen | official — handles out-of-order IoT events |
+| store_row_column for point queries | official — single I/O per device lookup |
+| Inverted index on log message | official — enables MATCH_ANY/MATCH_ALL search |
+| AGGREGATE + BITMAP_UNION | official — exact count-distinct without raw storage |
+| ZSTD compression | derived — IoT data has high redundancy |
+| 90-day / 30-day dynamic partition TTL | field — common IoT retention windows |
diff --git a/skills/doris-architecture-advisor/references/example-log-observability.md b/skills/doris-architecture-advisor/references/example-log-observability.md
new file mode 100644
index 0000000..20e6d56
--- /dev/null
+++ b/skills/doris-architecture-advisor/references/example-log-observability.md
@@ -0,0 +1,118 @@
+# Example: Log & Observability Platform
+
+## Scenario
+
+- **Workload:** Centralized observability — logs + traces + metrics from 200 microservices
+- **Ingest rate:** 500 GB/day logs, 50 GB/day traces, 10 GB/day metrics
+- **Query patterns:** keyword search on log body, trace ID lookup, service error rate dashboards
+- **Latency target:** log search 1-3s, trace lookup <1s, dashboard <1s
+- **Retention:** logs 7 days, traces 7 days, metrics 30 days
+
+## Workload Classification
+
+| Sub-workload | Type | Table |
+|-------------|------|-------|
+| Application logs | Log/Search | `otel_logs` |
+| Distributed traces | Log/Search (trace correlation) | `otel_traces` |
+| Service metrics | Report / Pre-aggregation | `otel_metrics` |
+
+## Sizing Recommendation
+
+- **Write:** ~560 GB/day ≈ 6.5 MB/s sustained, bursts to 50+ MB/s
+- **Hot data:** 7 days × 560GB × 0.1 (ZSTD) = ~390 GB (logs+traces) + 30 days × 10GB × 0.15 = ~45 GB (metrics)
+- **Total hot:** ~435 GB → fits 16 vCPU total cluster, 1 TB total cache (Log Storage & Search matrix)
+- **QPS:** ~100 (dashboard + search)
+
+## Table Designs
+
+### 1. otel_logs (DUPLICATE — full-text searchable logs)
+
+```sql
+CREATE TABLE otel_logs (
+ log_time DATETIME NOT NULL,
+ service_name VARCHAR(100) NOT NULL,
+ severity VARCHAR(10) NOT NULL,
+ trace_id VARCHAR(32),
+ span_id VARCHAR(16),
+ body TEXT,
+ resource_attrs VARIANT,
+ INDEX idx_body(body) USING INVERTED PROPERTIES("parser" = "unicode"),
+ INDEX idx_trace(trace_id) USING INVERTED
+) ENGINE=OLAP
+DUPLICATE KEY(log_time, service_name, severity)
+PARTITION BY RANGE(log_time) ()
+DISTRIBUTED BY HASH(service_name) BUCKETS 12 -- 500 GB/day × 0.05 ZSTD = 25 GB/partition ÷ 2 GB target ≈ 12 buckets
+PROPERTIES (
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "DAY",
+ "dynamic_partition.start" = "-7",
+ "dynamic_partition.end" = "3",
+ "dynamic_partition.prefix" = "p",
+ "compression" = "zstd",
+ "compaction_policy" = "time_series",
+ "replication_num" = "1"
+);
+```
+
+### 2. otel_traces (DUPLICATE — trace correlation)
+
+```sql
+CREATE TABLE otel_traces (
+ start_time DATETIME NOT NULL,
+ service_name VARCHAR(100) NOT NULL,
+ trace_id VARCHAR(32) NOT NULL,
+ span_id VARCHAR(16) NOT NULL,
+ parent_span_id VARCHAR(16),
+ operation_name VARCHAR(200),
+ duration_ms BIGINT,
+ status_code TINYINT,
+ INDEX idx_trace(trace_id) USING INVERTED
+) ENGINE=OLAP
+DUPLICATE KEY(start_time, service_name, trace_id)
+PARTITION BY RANGE(start_time) ()
+DISTRIBUTED BY HASH(service_name) BUCKETS 3 -- 50 GB/day × 0.1 ZSTD = 5 GB/partition ÷ 2 GB target ≈ 3 buckets
+PROPERTIES (
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "DAY",
+ "dynamic_partition.start" = "-7",
+ "dynamic_partition.end" = "3",
+ "dynamic_partition.prefix" = "p",
+ "compression" = "zstd",
+ "replication_num" = "1"
+);
+```
+
+### 3. otel_metrics (AGGREGATE — pre-aggregated service metrics)
+
+```sql
+CREATE TABLE otel_metrics (
+ metric_time DATETIME NOT NULL,
+ service_name VARCHAR(100) NOT NULL,
+ metric_name VARCHAR(200) NOT NULL,
+ value_sum DOUBLE SUM DEFAULT "0",
+ value_count BIGINT SUM DEFAULT "0"
+) ENGINE=OLAP
+AGGREGATE KEY(metric_time, service_name, metric_name)
+PARTITION BY RANGE(metric_time) ()
+DISTRIBUTED BY HASH(service_name) BUCKETS 3 -- 10 GB/day aggregated metrics, small per-partition → 3 buckets
+PROPERTIES (
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "DAY",
+ "dynamic_partition.start" = "-30",
+ "dynamic_partition.end" = "3",
+ "dynamic_partition.prefix" = "p",
+ "replication_num" = "1"
+);
+```
+
+## Key Decisions
+
+| Decision | Why | Source |
+|----------|-----|--------|
+| Shared bucket key `service_name` | Potential colocation JOINs across all 3 tables | derived |
+| Inverted index on `body` with unicode parser | Full-text log search via MATCH_ANY/MATCH_ALL | official |
+| Inverted index on `trace_id` | Fast trace lookup without sort key position | official |
+| ZSTD compression on logs/traces | Log data 10-20x compression ratio | official |
+| 7-day TTL logs, 30-day metrics | Logs are high-volume short-lived; metrics are compact long-lived | field |
+| AGGREGATE for metrics | Auto-aggregates on ingest; dashboard queries hit pre-computed values | official |
+| VARIANT for resource_attrs | OpenTelemetry resource attributes vary by service | derived |
diff --git a/skills/doris-architecture-advisor/references/example-logistics-courier.md b/skills/doris-architecture-advisor/references/example-logistics-courier.md
new file mode 100644
index 0000000..7eefa76
--- /dev/null
+++ b/skills/doris-architecture-advisor/references/example-logistics-courier.md
@@ -0,0 +1,75 @@
+# Example: Logistics & Courier — Real-time Operations Platform
+
+## Scenario
+
+- **Workload:** Top courier company with 100+ sorting centers, 10,000+ vehicles, billions of daily scan events
+- **Data sources:** OMS, WMS, TMS, CRM, vehicle GPS (IoT), handheld PDA scanners, sorting line sensors, weather/GIS data
+- **Data volume:** 10B+ scan records/day, 500GB/day GPS trajectories, PB-scale total
+- **Query patterns:** Real-time operations dashboard (sorting center throughput), parcel full-chain tracking (point query by waybill), vehicle fleet monitoring (GIS trajectory), delivery efficiency analytics, fraud detection
+- **Special requirements:** 3-year trajectory retention (regulatory compliance), peak season elasticity (Double 11 = 3x volume), replacing Presto + Kudu + ES + HBase with unified platform
+
+## Workload Classification
+
+**Composite workload** — 6 sub-workloads:
+
+| Sub-workload | Type | Decision pattern |
+|-------------|------|-----------------|
+| Parcel scan events (full chain) | Time-series (append-only) | DUPLICATE + RANGE partition by day |
+| Parcel current status | Mutable state / Point query | AGGREGATE with MIN/MAX/REPLACE_IF_NOT_NULL (not UNIQUE — see below) |
+| Sorting center KPIs | Pre-aggregation | AGGREGATE with SUM/BITMAP_UNION |
+| Vehicle GPS trajectories | Time-series (IoT) | DUPLICATE + time_series compaction + cooldown_ttl |
+| Warehouse inventory | Mutable state | UNIQUE MoW + sequence_col |
+| Anomaly detection logs | Log search | DUPLICATE + inverted index |
+
+## Logistics-specific Design Pattern: AGGREGATE for Parcel Status
+
+In logistics, the AGGREGATE model with MIN/MAX/REPLACE_IF_NOT_NULL is preferred over UNIQUE KEY for parcel status because:
+
+- Each scan event contributes a different metric: MIN(earliest_scan), MAX(latest_scan), REPLACE_IF_NOT_NULL(current_status)
+- No UPDATE/DELETE needed — just INSERT each scan, the AGGREGATE model auto-merges
+- Simpler than UNIQUE + sequence_col because you don't need to track event ordering
+- Used by SF Express, ZTO, YTO, and other top couriers in production
+
+Aggregation functions for logistics:
+
+| Function | Logistics use case |
+|----------|-------------------|
+| SUM | Package count, PV metrics |
+| MIN | Earliest arrival time at a hub, minimum package weight |
+| MAX | Latest departure time, maximum package weight |
+| REPLACE | Status update (overwrites with latest value) |
+| REPLACE_IF_NOT_NULL | Status update preserving existing fields when new data is partial |
+| BITMAP_UNION | Dedup package count, time-slice utilization |
+| HLL_UNION | Approximate distinct count (non-critical dedup) |
+
+## Vehicle Trajectory: GIS + Cold Data Tiering
+
+For fleet tracking with regulatory 3-year retention:
+
+- DUPLICATE model with time_series compaction (10MB+ per CPU core per second write throughput)
+- AUTO PARTITION by day with `date_trunc()`
+- GIS functions: `ST_CONTAINS()` for geofencing, `ST_DISTANCE_SPHERE()` for proximity
+- For cold data tiering, use deployment-specific lifecycle policy configured outside generic Cloud DDL.
+- Do not emit cold-tier storage properties in portable example DDL unless the target policy already exists.
+
+## Platform Consolidation Mapping
+
+| Current system | Role | {{PRODUCT_NAME}} replacement |
+|---------------|------|-------------------|
+| Presto (10,000 cores) | Ad-hoc analytics | {{PRODUCT_NAME}} MPP engine — 3x faster, 48% fewer resources (SF Express case) |
+| Kudu | Real-time updates | UNIQUE MoW or AGGREGATE with REPLACE_IF_NOT_NULL |
+| Elasticsearch | Waybill search, address search | Inverted index with unicode parser |
+| HBase | Parcel status point lookup | UNIQUE MoW + store_row_column (30K QPS on 16-core) |
+| Greenplum | Operational reports | AGGREGATE model — metric development 5 days → 2 days |
+
+## Decision Provenance
+
+| Decision | Source |
+|----------|--------|
+| AGGREGATE for parcel status (not UNIQUE) | `field` — production pattern at SF Express, ZTO; AGGREGATE auto-merges scan events without UPDATE statements |
+| DUPLICATE + time_series compaction for GPS | `official` — 10MB+/core/sec write throughput, millisecond compaction |
+| cooldown_ttl for 3-year trajectory retention | `official` — partition-level cold tier migration to S3/HDFS |
+| GIS functions for geofencing | `official` — ST_CONTAINS, ST_DISTANCE_SPHERE |
+| BITMAP_UNION for dock utilization time slicing | `official` — exact time-slot dedup |
+| Inverted index replacing ES | `official` — MATCH_ANY/MATCH_PHRASE for waybill/address search |
+| Presto → {{PRODUCT_NAME}} consolidation | `field` — SF Express: 3x perf improvement, 48% resource savings, 100% Presto migration |
diff --git a/skills/doris-architecture-advisor/references/example-payment-fintech.md b/skills/doris-architecture-advisor/references/example-payment-fintech.md
new file mode 100644
index 0000000..f26b446
--- /dev/null
+++ b/skills/doris-architecture-advisor/references/example-payment-fintech.md
@@ -0,0 +1,79 @@
+# Example: Payment / Fintech — Real-time Transaction Analytics Platform
+
+## Scenario
+
+- **Workload:** Payment company processing millions of daily transactions across acquiring, settlement, risk control, and merchant services
+- **Data sources:** Transaction DB (acquiring records, clearing, refunds), user behavior (app events, web payment trails), risk data (device fingerprints, IP, blacklists), account data (balances, card bindings), external (credit scoring, crawled data)
+- **Data volume:** 100M+ transactions/day, billions of rows per table (100+ columns for acquiring), 10-100TB/day logs
+- **Query patterns:** Real-time transaction dashboard, merchant reconciliation (P99 <2s at millions of daily queries), acquiring point lookups (10K+ QPS, millisecond, composite key), risk detection (multi-dimensional JOINs), security log search
+- **Special requirements:** Partial column updates (transaction status changes), row-column hybrid storage for acquiring (point lookup + analytics on same table), replacing Lambda architecture (Hive+HBase+ES+Oracle→{{PRODUCT_NAME}})
+
+## Workload Classification
+
+**Composite workload** — 6 sub-workloads:
+
+| Sub-workload | Type | Decision pattern |
+|-------------|------|-----------------|
+| Transaction detail (acquiring) | CDC + Point query | UNIQUE MoW + store_row_column + composite key |
+| Transaction status updates | Mutable state | UNIQUE MoW + partial column update (session variable) |
+| Real-time KPI dashboards | Pre-aggregation | AGGREGATE or async MV |
+| Merchant reconciliation | Report + Point query | Query on detail tables with merchant_id filter |
+| Risk detection | Ad-hoc + Log search | Multi-table JOIN with inverted index on risk labels |
+| Security/system logs | Log search | DUPLICATE + inverted index + ZSTD, replacing ES |
+
+## Payment-specific Design Patterns
+
+### Partial column update for transaction lifecycle
+Transaction status evolves: created → authorized → captured → settled → refunded. Each step updates only a few columns. Use UNIQUE MoW with partial update at write time (session variable, not table property):
+```
+SET enable_unique_key_partial_update = true;
+INSERT INTO transactions (tx_id, tx_time, status, settled_amount, settle_time) VALUES (...);
+```
+
+### Acquiring table: row-column hybrid for dual workload
+Tables with 100+ columns serving both point lookups (bank queries by composite key) and analytical queries (regional aggregations):
+- `store_row_column = "true"` enables row-store for point lookups (millisecond, single I/O)
+- Columnar storage still used for analytical scans (best of both worlds)
+- Inverted index on frequently filtered columns (merchant_id, status, card_type)
+- BloomFilter via `bloom_filter_columns` property for high-cardinality equality filters
+
+### Merchant reconciliation: high-concurrency multi-tenant queries
+- 500K merchants each querying their own data
+- Partition by date, bucket by merchant_id — queries prune to relevant tablets
+- P99 < 2s at millions of daily queries
+- Async MV for pre-computed daily/monthly settlement summaries
+
+### Log platform replacing Elasticsearch
+- 10-100TB/day log volume from security devices, middleware, business systems
+- ZSTD compression: 60-80% storage savings vs ES
+- Inverted index with unicode parser for full-text search
+- VARIANT for semi-structured JSON logs
+- Cold data tiering to S3 via cooldown_ttl
+- time_series compaction for sustained GB/s write with sub-second flush
+
+### Platform consolidation: Lambda → unified architecture
+Replacing 6 systems (Hive + Spark + HBase + ES + Oracle + TiDB) with one {{PRODUCT_NAME}} instance:
+
+| Old system | Role | {{PRODUCT_NAME}} replacement |
+|-----------|------|-------------------|
+| Hive + Spark | Batch ETL | Async MV + INSERT INTO SELECT |
+| HBase | Key-value lookups | UNIQUE MoW + store_row_column |
+| Elasticsearch | Log search, text search | Inverted index + VARIANT |
+| Oracle | Reports, reconciliation | MPP engine, sub-second on pre-aggregated tables |
+| TiDB / MySQL | Transaction queries | Flink CDC → UNIQUE MoW with real-time sync |
+
+Real customer result (Lakala, stock code 300773): replaced Lambda architecture, daily query volume exceeds 1M, query performance improved significantly.
+
+## Decision Provenance
+
+| Decision | Source |
+|----------|--------|
+| UNIQUE MoW for transaction detail | `official` — supports upsert for status lifecycle |
+| Partial column update via session variable | `official` — `SET enable_unique_key_partial_update = true` at write time, NOT as table property |
+| store_row_column for acquiring tables | `official` — dual workload: point lookup + analytics |
+| Inverted index replacing ES | `official` — full-text MATCH with unicode parser |
+| ZSTD + cooldown_ttl for logs | `official` — 60-80% storage savings, cold tier to S3 |
+| VARIANT for log schema flexibility | `official` — semi-structured JSON with auto schema evolution |
+| Async MV for reconciliation summaries | `official` — pre-computed daily/monthly rollups |
+| bloom_filter_columns for composite key | `official` — high-cardinality point lookup acceleration |
+| time_series compaction for log ingestion | `official` — GB/s sustained write, DUPLICATE only |
diff --git a/skills/doris-architecture-advisor/references/example-retail-fashion.md b/skills/doris-architecture-advisor/references/example-retail-fashion.md
new file mode 100644
index 0000000..e0bcd3e
--- /dev/null
+++ b/skills/doris-architecture-advisor/references/example-retail-fashion.md
@@ -0,0 +1,165 @@
+# Example: Retail & Fashion — Omnichannel Operations Platform
+
+## Scenario
+
+- **Workload:** Fashion/sportswear brand with 8,000+ stores, online channels (Tmall, JD, Douyin), and manufacturing (MES/SCM)
+- **Data sources:** CRM, ERP, SCM, OA, MES, WMS, POS (store sales), Taobao/JD/Douyin APIs, competitor data, trend data
+- **Data volume:** ~100M orders/year, 20M loyalty members, 50M SKU-location inventory records, billions of clickstream events
+- **Query patterns:** Real-time store dashboards, inventory monitoring, customer segmentation (BITMAP), product sell-through, promotion ROI, supply chain tracking, financial reporting
+- **Special requirements:** ODS→DWD→DWS→ADS layering, wide table + tall table for user profiling, multi-brand isolation, peak season scaling (Double 11 / 618)
+
+## Workload Classification
+
+**Composite workload** — 7 sub-workloads:
+
+| Sub-workload | Type | Tables |
+|-------------|------|--------|
+| Order/transaction data (omnichannel) | CDC sync + Time-series | `ods_orders`, `dwd_orders` |
+| Inventory state (real-time) | Mutable state | `inventory_state` |
+| Customer profile (wide table) | Mutable state + Point query | `customer_profile_wide` |
+| Customer tags (tall table / BITMAP) | Pre-aggregation | `customer_tag_bitmap` |
+| Product master + SKU attributes | Dimension | `dim_product`, `dim_store` |
+| Store/channel KPI dashboards | Report / Pre-aggregation | `ads_store_daily`, `ads_channel_daily` |
+| Promotion campaign analytics | Report + Ad-hoc | `ads_promotion_realtime` |
+
+## Sizing Recommendation
+
+- **Total vCPU:** 64 vCPU total, 3 TB total cache
+- **Hot data:** Orders 1 year (~2 TB compressed) + inventory (~10 GB) + profiles (~50 GB) + events (~5 TB compressed)
+- **Peak season:** Consider separate compute group for Double 11 real-time dashboards to isolate from batch ETL
+- **Storage:** ~10 TB/year compressed in object storage
+
+## Architecture
+
+```
+Internal Systems Ingestion {{PRODUCT_NAME}} (Real-time DW) Applications
+┌─────────────┐ ┌─────────────────────────┐
+│ CRM │── Flink CDC ────────────────────────────│ ODS (UNIQUE MoW) │
+│ ERP │ │ ods_orders, ods_members │
+│ SCM │ ├─────────────────────────┤
+│ WMS │ │ DWD (UNIQUE/DUPLICATE) │──→ Store Operations
+│ MES │── Flink CDC ────────────────────────────│ dwd_orders, dwd_inventory│──→ Product Analysis
+│ POS (stores)│ ├─────────────────────────┤
+├─────────────┤ │ DWS (DUPLICATE) │──→ Financial Reports
+│ Tmall API │ │ customer_profile_wide │──→ Member Services
+│ JD API │── Routine Load (Kafka) ────────────────│ inventory_model │──→ Replenishment
+│ Douyin API │ ├─────────────────────────┤
+│ Own website │ │ ADS (AGGREGATE) │──→ KPI Dashboards
+├─────────────┤ │ ads_store_daily │──→ Promotion Monitor
+│ Competitor │── Broker Load (batch) ─────────────────│ ads_channel_daily │──→ Management Cockpit
+│ Trends │ │ customer_tag_bitmap │──→ Marketing Campaigns
+└─────────────┘ └────────┬────────────────┘
+ │
+Data Lake (Hive/Iceberg) ─── Multi-Catalog ──────────────────────┘
+ Historical archive (3+ years, federated query)
+```
+
+**ODS/DWD table pattern for retail orders (CRITICAL — partition column must be in UNIQUE KEY):**
+```sql
+-- ODS order table: UNIQUE KEY includes BOTH order_id AND order_time
+-- because PARTITION BY RANGE(order_time) requires order_time in the key
+CREATE TABLE ods_orders (
+ order_id BIGINT NOT NULL,
+ order_time DATETIME(3) NOT NULL, -- MUST be in UNIQUE KEY when used as partition column
+ update_time DATETIME(3) NOT NULL,
+ ...
+) UNIQUE KEY(order_id, order_time)
+PARTITION BY RANGE(order_time) ()
+DISTRIBUTED BY HASH(order_id) BUCKETS 10
+PROPERTIES ("enable_unique_key_merge_on_write" = "true",
+ "function_column.sequence_col" = "update_time", "replication_num" = "1");
+```
+
+**DWD layer wide tables** (per the PDF architecture):
+- Retail wide table
+- Member wide table
+- Order wide table
+- Inventory wide table
+- Shipment wide table
+- In-transit wide table
+
+**DWS layer models:**
+- Inventory model
+- Retail model
+- Logistics model
+- Member model
+
+**ADS layer reports:**
+- Store reports, inventory reports, financial reports, member services, promotion services
+
+## Key Design Pattern: Wide Table vs Tall Table for User Profiling
+
+### Wide table (AGGREGATE + REPLACE_IF_NOT_NULL)
+
+One row per user, one column per tag. Best for point queries ("show me user X's full profile") and ad-hoc analysis.
+
+- AGGREGATE KEY with user_id, every tag column uses REPLACE_IF_NOT_NULL
+- Partial tag updates: insert only the changed columns, NULLs are ignored
+- Schema changes via `light_schema_change` when adding new tags
+- Best for: advisor lookups, detailed user drilldowns, wide analytics
+
+### Tall table (AGGREGATE + BITMAP_UNION)
+
+One row per tag value, BITMAP stores user IDs. Best for audience selection ("find users matching criteria A AND B").
+
+- AGGREGATE KEY with (tag_name, tag_value, date)
+- BITMAP_UNION stores set of user_ids per tag value
+- `bitmap_intersect()` for cross-tag audience intersection
+- `bitmap_union()` for audience union
+- `bitmap_count()` for audience sizing
+- Best for: marketing campaigns, BITMAP audience segmentation, cohort analysis
+
+### When to use which
+
+| Need | Wide table | Tall table |
+|------|-----------|-----------|
+| "Show me user X's full profile" | Fast (one row read) | Slow (scan all tags) |
+| "Find all users where tag A = X AND tag B = Y" | Slow (full table scan) | Fast (BITMAP intersect, sub-second) |
+| "How many users match criteria?" | Slow (COUNT with WHERE) | Fast (bitmap_count) |
+| Adding new tags | Schema change (light_schema_change) | Just insert new tag rows |
+| Tag count > 500 | Schema becomes unwieldy | No schema impact |
+
+**Best practice:** Use BOTH tables. Wide table for user-facing point queries (advisor/CRM lookups), tall table for marketing segmentation. Sync between them via async MV or scheduled ETL.
+
+## Retail-specific Decision Patterns
+
+### Inventory: UNIQUE MoW (not DUPLICATE)
+Inventory is mutable — every sale, return, transfer, and receiving changes the quantity. Use UNIQUE KEY on (sku_id, location_id) with sequence_col on update_time.
+
+### Product sell-through tracking
+- DWD: detailed sell-through per SKU-store-day (DUPLICATE, append daily snapshots)
+- ADS: aggregated sell-through rate = units_sold / units_received, pre-computed in AGGREGATE model
+- Alert when sell-through < threshold → trigger markdown decision
+
+### Multi-brand isolation
+Use Workload Groups to isolate brand teams:
+- Each brand team gets its own workload group (CPU/memory limits)
+- Shared data layer (ODS/DWD), brand-specific ADS views
+- Prevents one brand's heavy analysis from impacting others
+
+### Peak season (Double 11 / 618)
+- Pre-compute ADS tables before the event starts
+- Separate compute group for real-time promotion dashboards
+- Increase async MV refresh frequency to every 1-2 minutes during peak
+- After peak, scale back to normal refresh intervals
+
+### E-commerce multi-platform data
+- Each platform (Tmall, JD, Douyin) has different order/product schemas
+- Use VARIANT for platform-specific fields that vary
+- Standardize common fields (order_id, sku_id, amount, quantity) as typed columns in DWD
+- Deduplicate cross-platform orders by customer OneID in DWS
+
+## Decision Provenance
+
+| Decision | Source |
+|----------|--------|
+| ODS/DWD layering from 16+ source systems | `official` — standard real-time DW pattern |
+| UNIQUE MoW for inventory state | `official` — mutable quantity, needs upsert |
+| AGGREGATE + REPLACE_IF_NOT_NULL for wide profile | `official` — partial tag updates without full row replace |
+| AGGREGATE + BITMAP_UNION for tall tag table | `official` — sub-second audience segmentation |
+| Both wide + tall tables | `derived` — wide for point query, tall for BITMAP segmentation |
+| VARIANT for multi-platform e-commerce data | `official` — different schemas per platform |
+| Workload Groups for multi-brand isolation | `official` — CPU/memory isolation |
+| Separate compute group for peak season | `derived` — elastic scaling during Double 11 / 618 |
+| Async MV for DWD→DWS→ADS ETL | `official` — replaces Spark/Hive batch processing |
+| 64 vCPU / 3 TB cache | `field` — ~75% accuracy; confirm with {{PRODUCT_NAME}} SA |
diff --git a/skills/doris-architecture-advisor/references/example-securities-analytics.md b/skills/doris-architecture-advisor/references/example-securities-analytics.md
new file mode 100644
index 0000000..b5a93d4
--- /dev/null
+++ b/skills/doris-architecture-advisor/references/example-securities-analytics.md
@@ -0,0 +1,262 @@
+# Example: Securities & Fund Industry — Real-time Analytics Platform
+
+## Scenario
+
+- **Workload:** Securities firm building a unified real-time data warehouse replacing Impala + Kudu + ES + Greenplum
+- **Data sources:** Trading system (Oracle), account system (MySQL), CRM (PG), market quotes (Kafka), app behavior logs (Kafka), external data lake (Hive/Iceberg)
+- **Data volume:** 200 GB/day trading data, 500M+ behavior events/day, 10M customer profiles, 3+ years history in data lake
+- **Query patterns:** Real-time KPI dashboards (sub-second), customer 360 point queries (<100ms at 10K QPS), compliance log search, ad-hoc portfolio analysis, cross-department analytics
+- **Special requirements:** ODS → DWD → DWS → ADS warehouse layering, workload isolation between departments, hot/cold data tiering, BITMAP audience segmentation
+
+## Workload Classification
+
+**Composite workload** — 6 sub-workloads:
+
+| Sub-workload | Type | Table(s) |
+|-------------|------|----------|
+| Trading detail (ODS/DWD) | CDC sync | `ods_trade_detail`, `dwd_trade_detail` |
+| Customer profile (360 view) | Mutable state + Point query | `customer_profile` |
+| Behavior events | Time-series / Log | `customer_events` |
+| KPI dashboards (ADS) | Report / Pre-aggregation | `ads_revenue_daily`, `ads_aum_summary` |
+| Compliance log search | Log search | `trade_audit_logs` |
+| Historical data (lake) | Lakehouse federation | External catalog (Hive/Iceberg) |
+
+## Sizing Recommendation
+
+- **Total vCPU:** 64 vCPU total, 3 TB total cache
+- **Hot data:** 1 year trading (~200 GB/day × 365 × 0.15 ZSTD = ~11 TB) + profiles (~50 GB) + events (~500M/day × 365 × 0.05 = ~9 TB)
+- **Cache strategy:** Cache ≥50% of hot data. Set `file_cache_ttl_seconds` per table: dimension tables = 0 (forever), fact tables = 7776000 (90 days)
+- **Cold data:** 3+ years in Hive/Iceberg, queried via Multi-Catalog with 10-20% latency overhead
+
+## Architecture
+
+```
+Data Sources Data Collection Real-time DW ({{PRODUCT_NAME}}) Applications
+┌──────────┐ ┌─────────────────────┐
+│ Trading │──── Flink CDC ──────────────────────────────│ ADS (Aggregate) │──→ KPI Dashboards
+│ (Oracle) │ │ ads_revenue_daily │──→ Management Cockpit
+├──────────┤ ├─────────────────────┤
+│ Account │──── Flink CDC ──────────────────────────────│ DWS (Duplicate) │──→ Advisor Platform
+│ (MySQL) │ │ customer_profile │──→ Wealth Management
+├──────────┤ ├─────────────────────┤
+│ CRM (PG) │──── Flink CDC ──────────────────────────────│ DWD (Unique MoW) │──→ Risk Control
+├──────────┤ │ dwd_trade_detail │──→ Compliance Audit
+│ Quotes │──── Routine Load (Kafka) ──────────────────├─────────────────────┤
+│ (Kafka) │ │ ODS (Unique MoW) │
+├──────────┤ │ ods_trade_detail │
+│ App Logs │──── Routine Load (Kafka) ──────────────────├─────────────────────┤
+│ (Kafka) │ │ DIM (Unique MoW) │
+└──────────┘ │ dim_product │
+ └────────┬────────────┘
+Data Lake (Hive/Iceberg) ────── Multi-Catalog ───────────────────┘
+ (federated query, no data movement)
+```
+
+**Warehouse layering:**
+- **ODS:** Raw data, UNIQUE KEY for dedup, Flink CDC for real-time sync
+- **DWD:** Cleaned detail data, UNIQUE KEY for row-level updates, Doris SQL ETL for cleaning
+- **DWS:** Wide tables joined from DWD + DIM, DUPLICATE for fastest scan, async MV for cross-table joins
+- **ADS:** Pre-aggregated KPIs, AGGREGATE for auto-rollup, serves dashboards directly
+- **DIM:** Dimension tables (products, branches, advisors), UNIQUE MoW for updates
+
+## Table Designs
+
+### 1. `ods_trade_detail` — ODS layer (CDC from trading system)
+
+```sql
+CREATE TABLE ods_trade_detail (
+ trade_id BIGINT NOT NULL,
+ trade_time DATETIME(3) NOT NULL,
+ account_id VARCHAR(32) NOT NULL,
+ update_time DATETIME(3) NOT NULL,
+ symbol VARCHAR(20) NOT NULL,
+ direction VARCHAR(10), -- BUY, SELL
+ quantity BIGINT,
+ price DECIMAL(18,4),
+ amount DECIMAL(18,2),
+ status VARCHAR(20), -- pending, filled, cancelled
+ channel VARCHAR(20) -- app, web, api
+) ENGINE=OLAP
+UNIQUE KEY(trade_id, trade_time)
+PARTITION BY RANGE(trade_time) ()
+DISTRIBUTED BY HASH(trade_id) BUCKETS 10 -- 200 GB/day × 0.15 = 30 GB ÷ 2 GB = ~15, round to 10 for initial
+PROPERTIES (
+ "enable_unique_key_merge_on_write" = "true",
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "DAY",
+ "dynamic_partition.start" = "-365",
+ "dynamic_partition.end" = "3",
+ "dynamic_partition.prefix" = "p",
+ "function_column.sequence_col" = "update_time",
+ "replication_num" = "1"
+);
+-- UNIQUE MoW for ODS: prevents duplicate ingestion from Flink CDC replay
+-- sequence_col: trade status updates (pending → filled → settled) arrive in order
+-- PARTITION BY RANGE(trade_time): 200 GB/day exceeds 100GB threshold, daily partitions required
+-- trade_time included in UNIQUE KEY: required when using PARTITION BY RANGE
+```
+
+### 2. `customer_profile` — Customer 360 (point query + BITMAP segmentation)
+
+```sql
+CREATE TABLE customer_profile (
+ customer_id VARCHAR(32) NOT NULL,
+ update_time DATETIME(3) NOT NULL,
+ name VARCHAR(100),
+ risk_level VARCHAR(10), -- conservative, moderate, aggressive
+ total_assets DECIMAL(18,2),
+ trading_freq INT, -- trades per month
+ product_pref VARCHAR(200), -- fund, stock, bond, etc.
+ channel_pref VARCHAR(50),
+ advisor_id VARCHAR(32),
+ last_login DATETIME,
+ tags VARCHAR(500) -- comma-separated labels
+) ENGINE=OLAP
+UNIQUE KEY(customer_id)
+DISTRIBUTED BY HASH(customer_id) BUCKETS 8 -- 10M customers × ~500 bytes = ~5 GB ÷ 2 GB = ~3, use 8 for distribution
+PROPERTIES (
+ "enable_unique_key_merge_on_write" = "true",
+ "function_column.sequence_col" = "update_time",
+ "store_row_column" = "true",
+ "light_schema_change" = "true",
+ "replication_num" = "1"
+);
+-- store_row_column: advisor lookup by customer_id in <100ms
+-- Segmentation queries use standard SQL: WHERE risk_level = 'aggressive' AND total_assets > 1000000
+```
+
+### 3. `customer_events` — Behavior events (time-series)
+
+```sql
+CREATE TABLE customer_events (
+ customer_id VARCHAR(32) NOT NULL,
+ event_time DATETIME(3) NOT NULL,
+ event_type VARCHAR(50) NOT NULL, -- page_view, trade, login, product_click
+ page_url VARCHAR(500),
+ product_id VARCHAR(32),
+ data VARIANT, -- flexible event properties
+ INDEX idx_event_type(event_type) USING INVERTED
+) ENGINE=OLAP
+DUPLICATE KEY(customer_id, event_time, event_type)
+PARTITION BY RANGE(event_time) ()
+DISTRIBUTED BY HASH(customer_id) BUCKETS 8 -- 500M events/day × ~200 bytes = ~100 GB × 0.1 ZSTD = 10 GB ÷ 2 = 5, use 8
+PROPERTIES (
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "DAY",
+ "dynamic_partition.start" = "-90",
+ "dynamic_partition.end" = "3",
+ "dynamic_partition.prefix" = "p",
+ "compression" = "zstd",
+ "compaction_policy" = "time_series",
+ "replication_num" = "1"
+);
+```
+
+### 4. `ads_revenue_daily` — ADS layer (pre-aggregated KPIs)
+
+```sql
+CREATE TABLE ads_revenue_daily (
+ stat_date DATE NOT NULL,
+ branch_id VARCHAR(20) NOT NULL,
+ business_line VARCHAR(30) NOT NULL, -- brokerage, wealth, investment_banking
+ trade_count BIGINT SUM DEFAULT "0",
+ trade_amount DECIMAL(18,2) SUM DEFAULT "0",
+ commission DECIMAL(18,2) SUM DEFAULT "0",
+ new_customers BIGINT SUM DEFAULT "0",
+ active_customers BITMAP BITMAP_UNION
+) ENGINE=OLAP
+AGGREGATE KEY(stat_date, branch_id, business_line)
+PARTITION BY RANGE(stat_date) ()
+DISTRIBUTED BY HASH(branch_id) BUCKETS 3
+PROPERTIES (
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "MONTH",
+ "dynamic_partition.start" = "-24",
+ "dynamic_partition.end" = "1",
+ "dynamic_partition.prefix" = "p",
+ "replication_num" = "1"
+);
+-- AGGREGATE: dashboard queries read pre-computed KPIs, no scanning detail data
+-- BITMAP_UNION: exact distinct active customer count per branch per day
+```
+
+### 5. `trade_audit_logs` — Compliance log search
+
+```sql
+CREATE TABLE trade_audit_logs (
+ log_time DATETIME NOT NULL,
+ account_id VARCHAR(32) NOT NULL,
+ action VARCHAR(50) NOT NULL,
+ ip_address VARCHAR(45),
+ error_code VARCHAR(20),
+ message TEXT,
+ INDEX idx_msg(message) USING INVERTED PROPERTIES("parser" = "unicode"),
+ INDEX idx_action(action) USING INVERTED,
+ INDEX idx_error(error_code) USING INVERTED
+) ENGINE=OLAP
+DUPLICATE KEY(log_time, account_id, action)
+PARTITION BY RANGE(log_time) ()
+DISTRIBUTED BY HASH(account_id) BUCKETS 5 -- audit logs lower volume than trading
+PROPERTIES (
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "MONTH",
+ "dynamic_partition.start" = "-36", -- 3-year compliance retention
+ "dynamic_partition.end" = "1",
+ "dynamic_partition.prefix" = "p",
+ "compression" = "zstd",
+ "replication_num" = "1"
+);
+-- 3-year retention for compliance audit
+-- Inverted index on message + error_code for pattern detection
+```
+
+### 6. Lakehouse federation (no table creation — uses Multi-Catalog)
+
+```sql
+-- Access 3+ year historical data in Hive/Iceberg without data movement:
+CREATE CATALOG hive_catalog PROPERTIES (
+ "type" = "hms",
+ "hive.metastore.uris" = "thrift://metastore:9083"
+);
+
+-- Query historical data directly:
+SELECT * FROM hive_catalog.trading_db.trade_detail_history
+WHERE trade_date BETWEEN '2022-01-01' AND '2023-12-31';
+
+-- Accelerate with async MV if queries are repeated:
+CREATE MATERIALIZED VIEW mv_historical_summary
+REFRESH AUTO ON SCHEDULE EVERY 1 DAY
+AS SELECT trade_date, symbol, SUM(amount) AS total_amount
+FROM hive_catalog.trading_db.trade_detail_history
+GROUP BY trade_date, symbol;
+```
+
+## Workload Isolation
+
+For multi-department access (operations, risk, marketing, investment, customer service, finance), use Workload Groups:
+
+```sql
+-- Create isolated workload groups per department
+CREATE WORKLOAD GROUP wg_risk PROPERTIES ("cpu_share" = "30", "memory_limit" = "30%");
+CREATE WORKLOAD GROUP wg_marketing PROPERTIES ("cpu_share" = "20", "memory_limit" = "20%");
+CREATE WORKLOAD GROUP wg_advisor PROPERTIES ("cpu_share" = "25", "memory_limit" = "25%");
+
+-- Assign users to workload groups
+SET PROPERTY FOR 'risk_team' 'default_workload_group' = 'wg_risk';
+```
+
+## Decision Provenance
+
+| Decision | Source |
+|----------|--------|
+| ODS/DWD with UNIQUE MoW | `official` — CDC dedup, row-level updates for status changes |
+| DWS with DUPLICATE | `derived` — wide table joins produce read-optimized scan layer |
+| ADS with AGGREGATE + BITMAP | `official` — auto-rollup for dashboards, exact distinct counts |
+| store_row_column for customer_profile | `official` — sub-100ms advisor lookups |
+| Multi-Catalog for lakehouse | `official` — federated query on Hive/Iceberg without data movement |
+| Async MV for layer-to-layer ETL | `official` — scheduled refresh replaces Spark/Hive batch ETL |
+| Workload Group for dept isolation | `official` — CPU/memory isolation between departments |
+| Inverted index on audit logs | `official` — compliance pattern search on error_code + message |
+| ZSTD on events and logs | `derived` — high redundancy in behavioral and audit data |
+| 3-year partition retention for compliance | `field` — regulatory requirement; adjust per jurisdiction |
+| 64 vCPU / 3 TB cache | `field` — ~75% accuracy; confirm with {{PRODUCT_NAME}} SA |
diff --git a/skills/doris-architecture-advisor/references/example-web3-exchange.md b/skills/doris-architecture-advisor/references/example-web3-exchange.md
new file mode 100644
index 0000000..a3d60de
--- /dev/null
+++ b/skills/doris-architecture-advisor/references/example-web3-exchange.md
@@ -0,0 +1,99 @@
+# Example: Web3 / Crypto Exchange — Real-time On-chain Analytics
+
+## Scenario
+
+- **Workload:** Centralized crypto exchange (CEX) + on-chain analytics across multiple blockchains
+- **Data sources:** Order matching engine (internal DB), on-chain data services (blockchain nodes/APIs), Kafka streams, S3 historical archives
+- **Data volume:** 50M+ trades/day, 100M+ on-chain events/day across 5+ chains, TB-scale daily
+- **Query patterns:** Real-time trade analytics (swap, transfer, commission), asset custody monitoring, risk/AML detection, user wallet profiling, on-chain reporting (TVL, token metrics, NFT trends), marketing segmentation
+- **Special requirements:** Multi-chain schema differences (VARIANT), sub-second trade queries at 1000+ QPS, address reputation label JOINs, session analysis for user flows
+
+## Workload Classification
+
+**Composite workload** — 6 sub-workloads:
+
+| Sub-workload | Type | Decision pattern |
+|-------------|------|-----------------|
+| Trade/transaction detail (multi-chain) | Time-series (append-only) | DUPLICATE + VARIANT for chain-specific fields |
+| Asset custody state (portfolio balances) | Mutable state | UNIQUE MoW + sequence_col on block_height |
+| On-chain KPI dashboards (TVL, token metrics) | Pre-aggregation | AGGREGATE + async MV with 1-min refresh |
+| Risk/AML detection | Log search + Ad-hoc | DUPLICATE + inverted index on address + contract |
+| User wallet profiling (wide + tall table) | Point query + Segmentation | AGGREGATE wide table + BITMAP tall table |
+| On-chain reporting (multi-dimensional) | Report analytics | Layered: raw detail → subject models → reports |
+
+## Web3-specific Design Patterns
+
+### Multi-chain schema with VARIANT
+Different blockchains have different transaction fields. Use VARIANT for chain-specific data, typed columns for common fields:
+- Common: `tx_hash`, `block_number`, `block_time`, `from_address`, `to_address`, `value`, `chain_id`
+- Ethereum-specific: `gas_price`, `gas_used`, `input_data`
+- Solana-specific: `compute_units`, `program_id`
+- Store chain-specific fields in VARIANT column — 8x faster than JSON, auto schema evolution
+
+### Address reputation labels for risk JOINs
+- Dimension table of address labels (blacklist, whale, exchange, contract type)
+- UNIQUE MoW with frequent updates as new labels are discovered
+- JOIN with transaction detail for risk detection queries
+- Consider colocation on `address` column for zero-shuffle JOINs
+
+### TVL and token metrics: Async MV with partition_sync_limit
+- Base: multi-chain transaction detail (10B+ rows)
+- Async MV refreshes every 1-5 minutes with `partition_sync_limit` to process only recent partitions
+- Pre-aggregate by protocol/token/chain/hour for dashboard queries
+- Do NOT use sync MV (too much data for synchronous maintenance)
+
+### Session analysis for user behavior flows
+- Doris supports retention, funnel, and session analysis functions natively
+- Track user paths: wallet creation → first swap → bridge usage → withdrawal
+- Store as DUPLICATE event table, query with `window_funnel()` and `retention()`
+
+### Wallet profiling: Wide + tall table pattern (same as retail CDP)
+- Wide table: one row per wallet address, columns for each label (AGGREGATE + REPLACE_IF_NOT_NULL)
+- Tall table: one row per label value, BITMAP stores wallet addresses (AGGREGATE + BITMAP_UNION)
+- Wide for point queries ("show me this wallet's profile"), tall for segmentation ("find all whales on Ethereum")
+
+## Architecture
+
+```
+On-chain Nodes / APIs Ingestion {{PRODUCT_NAME}} Applications
+┌───────────────┐ ┌──────────────────────┐
+│ Blockchain │ │ Raw Detail │
+│ Event Streams │── Kafka → Flink ────────────────│ tx_events (DUP) │──→ Trade Analytics
+│ (ETH,BSC,SOL) │ │ + VARIANT for chain │──→ On-chain Reports
+├───────────────┤ ├──────────────────────┤
+│ Order Engine │── Kafka → Routine Load ─────────│ trades (DUP) │──→ Commission Calc
+│ (internal) │ │ │──→ P&L Dashboard
+├───────────────┤ ├──────────────────────┤
+│ S3 Historical │── Broker Load (batch) ──────────│ Subject Models │
+│ (archive) │ │ (async MV layer) │──→ TVL / Token KPIs
+└───────────────┘ ├──────────────────────┤
+ │ Custody State │
+ │ (UNIQUE MoW) │──→ Asset Monitoring
+ ├──────────────────────┤
+ │ Address Labels │──→ Risk / AML
+ │ (UNIQUE MoW, dim) │──→ Wallet Profiling
+ ├──────────────────────┤
+ │ User Segments │──→ Marketing
+ │ (AGG + BITMAP) │──→ Campaign Targeting
+ └──────────────────────┘
+```
+
+**Data modeling layers (from PDF):**
+- Raw data: transaction detail (DUPLICATE), contract event detail, token asset snapshots
+- Subject models: trade dimension model, contract dimension model, token granularity model
+- Reports: contract call TOP10, daily active addresses, token holder distribution, cross-chain bridge flow trends
+
+## Decision Provenance
+
+| Decision | Source |
+|----------|--------|
+| VARIANT for multi-chain schema | `official` — different chains have different fields, 8x faster than JSON |
+| DUPLICATE for transaction events | `official` — append-only on-chain events, fastest scan |
+| UNIQUE MoW for custody balances | `official` — balance updates with sequence_col on block_height |
+| Async MV for TVL/token metrics | `official` — 10B+ rows, partition_sync_limit for incremental refresh |
+| BITMAP tall table for wallet segmentation | `official` — sub-second audience selection across millions of addresses |
+| Inverted index for address/contract search | `official` — replaces Elasticsearch for on-chain address lookup |
+| Window functions for session analysis | `official` — retention(), window_funnel() for user behavior flows |
+| Kafka + Flink for on-chain ingestion | `derived` — standard Web3 data pipeline pattern |
+| S3 Broker Load for historical backfill | `official` — batch import for multi-year blockchain archive |
+| Colocation on address for risk JOINs | `derived` — zero-shuffle JOIN between tx detail and address labels |
diff --git a/skills/doris-best-practices/AGENTS.md b/skills/doris-best-practices/AGENTS.md
new file mode 100644
index 0000000..b0555bd
--- /dev/null
+++ b/skills/doris-best-practices/AGENTS.md
@@ -0,0 +1,1167 @@
+# Apache Doris — Complete Best Practice Reference
+
+> All 37 rules, 7 use case templates, and sizing guides.
+
+---
+
+## Use Case Templates
+
+## Use Case: CDC / Operational Data Sync
+For replicating operational databases (MySQL, PostgreSQL) where rows are frequently updated or deleted.
+### Template
+```sql
+CREATE TABLE users_sync (
+ user_id BIGINT NOT NULL,
+ tenant_id INT NOT NULL,
+ update_time DATETIME NOT NULL,
+ name VARCHAR(100),
+ email VARCHAR(200),
+ status TINYINT
+) ENGINE=OLAP
+UNIQUE KEY(user_id)
+DISTRIBUTED BY HASH(user_id) BUCKETS 5 -- CDC table: ~10 GB compressed
+PROPERTIES (
+ "enable_unique_key_merge_on_write" = "true",
+ "function_column.sequence_col" = "update_time",
+ "light_schema_change" = "true"
+);
+```
+### Why This Design
+| Decision | Choice | Why |
+|----------|--------|-----|
+| **Model** | UNIQUE (MoW) | Enables UPDATE/DELETE. MoW gives fast reads without merge-sort. |
+| **Sequence col** | `update_time` | Guarantees out-of-order CDC events don't overwrite newer data. |
+| **Bucket** | HASH on `user_id` (= primary key) | Point lookups on PK are pruned to one tablet. |
+| **Partition** | None | CDC tables are often small-to-medium; partition only if > 100GB. |
+### Customization Points
+- **Large CDC tables (> 100GB):** Add `PARTITION BY RANGE(update_time) ()` with dynamic partition
+- **Composite primary key:** Use `UNIQUE KEY(tenant_id, user_id)` for multi-tenant data
+- **Partial column updates:** Set `"enable_unique_key_partial_update" = "true"`
+
+---
+
+## Use Case: Dashboard / Pre-Aggregated Metrics
+For pre-computing dashboard numbers: daily sales, ad click totals, user activity summaries.
+### Template
+```sql
+CREATE TABLE daily_sales_metrics (
+ dt DATE NOT NULL, store_id INT NOT NULL,
+ total_revenue DECIMAL(15,2) SUM DEFAULT "0",
+ max_transaction DECIMAL(15,2) MAX DEFAULT "0",
+ order_count BIGINT SUM DEFAULT "0",
+ unique_buyers BITMAP BITMAP_UNION
+) ENGINE=OLAP AGGREGATE KEY(dt, store_id)
+PARTITION BY RANGE(dt) ()
+DISTRIBUTED BY HASH(store_id) BUCKETS 5 -- pre-aggregated metrics: ~10 GB compressed
+PROPERTIES ("dynamic_partition.enable"="true","dynamic_partition.time_unit"="MONTH",
+ "dynamic_partition.start"="-12","dynamic_partition.end"="1","dynamic_partition.prefix"="p");
+```
+### Why This Design
+| Decision | Choice | Why |
+|----------|--------|-----|
+| **Model** | AGGREGATE | Values auto-aggregate (SUM, MAX, BITMAP_UNION) on ingestion. |
+| **BITMAP** | `BITMAP_UNION` for unique_buyers | Exact count-distinct without storing raw user IDs. |
+### Anti-Pattern
+```sql
+-- BAD: Using DUPLICATE for dashboard data you'll always aggregate
+CREATE TABLE daily_sales_metrics (dt DATE, store_id INT, revenue DECIMAL(15,2))
+DUPLICATE KEY(dt, store_id);
+-- Every query must re-aggregate billions of rows at query time.
+```
+
+---
+
+## Use Case: Small Dimension / Lookup Table
+For small reference tables (< 1 GB): products, stores, countries, config values.
+### Template
+```sql
+CREATE TABLE stores (
+ store_id INT NOT NULL, region VARCHAR(20) NOT NULL,
+ city VARCHAR(50), manager_name VARCHAR(100)
+) ENGINE=OLAP DUPLICATE KEY(store_id)
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ("replication_num" = "3"); -- Use "1" for cloud mode
+```
+### Why This Design
+| Decision | Choice | Why |
+|----------|--------|-----|
+| **Model** | DUPLICATE | Dimension data is reference data — rarely updated. |
+| **Partition** | None | Table is tiny (< 1 GB). Partitioning adds overhead, not benefit. |
+| **Bucket** | RANDOM, 3 buckets | Perfectly even distribution for small data. |
+### Customization Points
+- **Updated dimensions:** Switch to `UNIQUE KEY(store_id)` with MoW if dimension data changes
+- **Colocation JOINs:** Switch to `HASH(store_id)` and match the fact table's bucket count
+
+---
+
+## Use Case: Time-Series Logs & Events
+For immutable, append-only data: application logs, click events, IoT sensor readings, audit trails.
+### Template
+```sql
+CREATE TABLE app_events (
+ event_time DATETIME NOT NULL,
+ app_id INT NOT NULL,
+ event_type VARCHAR(50) NOT NULL,
+ user_id BIGINT,
+ payload STRING
+) ENGINE=OLAP
+DUPLICATE KEY(event_time, app_id, event_type)
+PARTITION BY RANGE(event_time) ()
+DISTRIBUTED BY HASH(app_id) BUCKETS 10 -- high-volume logs: ~50 GB/day compressed
+PROPERTIES (
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "DAY",
+ "dynamic_partition.start" = "-7",
+ "dynamic_partition.end" = "3",
+ "dynamic_partition.prefix" = "p",
+ "compression" = "zstd"
+);
+```
+### Why This Design
+| Decision | Choice | Why |
+|----------|--------|-----|
+| **Model** | DUPLICATE | Logs are immutable — no updates/deletes needed. Fastest scan speed. |
+| **Partition** | Dynamic RANGE by DAY | Auto-creates daily partitions, auto-drops old ones (TTL via `start`). |
+| **Bucket** | HASH on `app_id` | High cardinality, commonly filtered in WHERE. |
+| **Sort Key** | `(event_time, app_id, event_type)` | Time-first enables range scans; app_id enables prefix pruning. |
+| **Compression** | ZSTD | Log data has high redundancy — ZSTD compresses 2-3x better than LZ4. |
+### Customization Points
+- **Retention:** Change `dynamic_partition.start` (e.g., `"-30"` for 30 days)
+- **Partition granularity:** `"HOUR"` for very high volume or `"MONTH"` for low volume
+- **Bucket key:** If you mostly filter by `user_id`, use `HASH(user_id)` instead
+- **Add text search:** Add `INDEX idx_payload(payload) USING INVERTED` for log message search
+
+---
+
+## Use Case: Observability Platform
+For building a full observability stack: logs + metrics + traces.
+### Table 1: Logs (DUPLICATE)
+```sql
+CREATE TABLE otel_logs (
+ log_time DATETIME NOT NULL, service_name VARCHAR(100) NOT NULL,
+ severity VARCHAR(10) NOT NULL, trace_id VARCHAR(32), span_id VARCHAR(16),
+ body TEXT, resource_attributes STRING,
+ INDEX idx_body(body) USING INVERTED PROPERTIES("parser" = "unicode")
+) ENGINE=OLAP DUPLICATE KEY(log_time, service_name, severity)
+PARTITION BY RANGE(log_time) ()
+DISTRIBUTED BY HASH(service_name) BUCKETS 10 -- high-volume logs: ~100 GB/day x 0.05 ZSTD / 2 GB
+PROPERTIES ("dynamic_partition.enable"="true","dynamic_partition.time_unit"="DAY",
+ "dynamic_partition.start"="-7","dynamic_partition.end"="3",
+ "dynamic_partition.prefix"="p","compression"="zstd",
+ "bloom_filter_columns"="trace_id");
+```
+### Table 2: Traces (DUPLICATE)
+```sql
+CREATE TABLE otel_traces (
+ start_time DATETIME NOT NULL, service_name VARCHAR(100) NOT NULL,
+ trace_id VARCHAR(32) NOT NULL, span_id VARCHAR(16) NOT NULL,
+ parent_span_id VARCHAR(16), operation_name VARCHAR(200),
+ duration_ms BIGINT, status_code TINYINT
+) ENGINE=OLAP DUPLICATE KEY(start_time, service_name, trace_id)
+PARTITION BY RANGE(start_time) ()
+DISTRIBUTED BY HASH(service_name) BUCKETS 3 -- traces: lower volume than logs, small per partition
+PROPERTIES ("dynamic_partition.enable"="true","dynamic_partition.time_unit"="DAY",
+ "dynamic_partition.start"="-7","dynamic_partition.end"="3",
+ "dynamic_partition.prefix"="p","compression"="zstd",
+ "bloom_filter_columns"="trace_id");
+```
+### Table 3: Metrics (AGGREGATE)
+```sql
+CREATE TABLE otel_metrics (
+ metric_time DATETIME NOT NULL, service_name VARCHAR(100) NOT NULL,
+ metric_name VARCHAR(200) NOT NULL,
+ value DOUBLE SUM DEFAULT "0", count BIGINT SUM DEFAULT "0"
+) ENGINE=OLAP AGGREGATE KEY(metric_time, service_name, metric_name)
+PARTITION BY RANGE(metric_time) ()
+DISTRIBUTED BY HASH(service_name) BUCKETS 3 -- pre-aggregated metrics: small per partition
+PROPERTIES ("dynamic_partition.enable"="true","dynamic_partition.time_unit"="DAY",
+ "dynamic_partition.start"="-30","dynamic_partition.end"="3","dynamic_partition.prefix"="p");
+```
+### Design Principles
+- **Shared bucket key:** All tables use `HASH(service_name)` for potential colocation JOINs
+- **Short retention for logs/traces:** 7-day TTL; **Longer for metrics:** 30-day TTL
+- **Text search on logs:** Inverted index on `body` with unicode parser
+- **Trace correlation:** BloomFilter on `trace_id` for fast trace lookup
+
+---
+
+## Use Case: User-Facing Point Query Analytics
+For serving real-time analytics through APIs with low-latency, high-concurrency requirements.
+### Template
+```sql
+CREATE TABLE user_profiles (
+ user_id BIGINT NOT NULL, tenant_id INT NOT NULL,
+ name VARCHAR(100), email VARCHAR(200), last_login DATETIME,
+ total_orders INT, lifetime_value DECIMAL(12,2)
+) ENGINE=OLAP UNIQUE KEY(user_id)
+DISTRIBUTED BY HASH(user_id) BUCKETS 5 -- user profiles: ~10 GB compressed
+PROPERTIES (
+ "enable_unique_key_merge_on_write" = "true",
+ "store_row_column" = "true",
+ "light_schema_change" = "true",
+ "bloom_filter_columns" = "tenant_id"
+);
+```
+### Why This Design
+| Decision | Choice | Why |
+|----------|--------|-----|
+| **Model** | UNIQUE MoW | Fast reads (no merge-sort at query time) |
+| **store_row_column** | `true` | Enables row-store mode for point queries — reads full row from one I/O |
+| **BloomFilter** | On `tenant_id` | Skips tablets that don't contain the tenant |
+### Optimized Point Query Pattern
+```sql
+SELECT * FROM user_profiles WHERE user_id = 12345;
+-- With Prepared Statement for high concurrency:
+PREPARE stmt FROM 'SELECT * FROM user_profiles WHERE user_id = ?';
+SET @uid = 12345; EXECUTE stmt USING @uid;
+```
+
+---
+
+## Use Case: Star Schema / JOIN-Heavy Analytics
+Uses colocation to ensure JOINs execute locally without network shuffle.
+### Fact Table
+```sql
+CREATE TABLE fact_orders (
+ order_date DATE NOT NULL, order_id BIGINT NOT NULL,
+ user_id INT NOT NULL, store_id INT NOT NULL, product_id INT NOT NULL,
+ amount DECIMAL(12,2), quantity INT
+) ENGINE=OLAP DUPLICATE KEY(order_date, order_id)
+PARTITION BY RANGE(order_date) ()
+DISTRIBUTED BY HASH(store_id) BUCKETS 16
+PROPERTIES ("dynamic_partition.enable"="true","dynamic_partition.time_unit"="MONTH",
+ "dynamic_partition.start"="-24","dynamic_partition.end"="1",
+ "dynamic_partition.prefix"="p",
+ "colocate_with"="group_orders");
+```
+### Dimension Table (colocated)
+```sql
+CREATE TABLE dim_stores (
+ store_id INT NOT NULL, region VARCHAR(20), city VARCHAR(50), manager_name VARCHAR(100)
+) ENGINE=OLAP DUPLICATE KEY(store_id)
+DISTRIBUTED BY HASH(store_id) BUCKETS 16
+PROPERTIES ("colocate_with" = "group_orders");
+```
+### Colocation Rules — ALL must match:
+1. Same `colocate_with` group name
+2. Same bucket key column(s) and same column types
+3. Same bucket count
+4. Same replication_num
+
+---
+
+## Data Model Rules
+
+## AGGREGATE Model Cannot UPDATE or DELETE
+
+**Impact: CRITICAL — AGG tables do not support UPDATE or DELETE statements.**
+
+The AGGREGATE model auto-merges rows with the same key using aggregation functions (SUM, MAX, REPLACE, etc). This means:
+- No `UPDATE` statement support
+- No `DELETE` statement support
+- No CDC pipeline compatibility (cannot reflect deletes)
+
+**Exception: REPLACE_IF_NOT_NULL** — The one case where AGG can do partial updates. Columns with `REPLACE_IF_NOT_NULL` will only update when the incoming value is non-NULL. This allows partial column updates in AGG model, but still no DELETE.
+
+**Incorrect:**
+
+```sql
+-- BAD: AGG for data that needs updates
+CREATE TABLE orders (
+ order_id BIGINT AGGREGATE KEY,
+ status VARCHAR(20) REPLACE,
+ amount DECIMAL(12,2) REPLACE
+) AGGREGATE KEY(order_id);
+-- Cannot DELETE cancelled orders!
+```
+
+**Correct:**
+
+```sql
+-- GOOD: UNIQUE MoW for updatable data
+CREATE TABLE orders (
+ order_id BIGINT NOT NULL,
+ status VARCHAR(20),
+ amount DECIMAL(12,2)
+) UNIQUE KEY(order_id)
+DISTRIBUTED BY HASH(order_id) BUCKETS 5 -- ~10 GB compressed
+PROPERTIES ("enable_unique_key_merge_on_write" = "true");
+```
+
+Reference: [Aggregate Model](https://doris.apache.org/docs/table-design/data-model/aggregate)
+
+---
+
+## Choose Data Model for Workload
+
+**Impact: CRITICAL — Data model cannot be changed after table creation.**
+
+| Model | Use When | Key Trait |
+|-------|----------|-----------|
+| DUPLICATE | Append-only data (logs, events, clicks) | Fastest scan, keeps all rows |
+| UNIQUE (MoW) | Rows are updated/deleted (CDC, user data) | Dedup on primary key, supports DELETE |
+| AGGREGATE | Pre-aggregated metrics only (counters, sums) | Auto-aggregates on ingest |
+
+**Decision rule:** If you need UPDATE or DELETE → UNIQUE MoW. If append-only → DUPLICATE. Only use AGGREGATE when you will *never* query raw rows.
+
+**Incorrect:**
+
+```sql
+-- BAD: Using AGGREGATE for a table that needs updates
+CREATE TABLE users (
+ user_id BIGINT AGGREGATE KEY,
+ name VARCHAR(100) REPLACE,
+ email VARCHAR(200) REPLACE
+) AGGREGATE KEY(user_id);
+-- Cannot DELETE rows. Cannot run UPDATE statements.
+```
+
+**Correct:**
+
+```sql
+-- GOOD: UNIQUE MoW for updatable data
+CREATE TABLE users (
+ user_id BIGINT NOT NULL,
+ name VARCHAR(100),
+ email VARCHAR(200)
+) UNIQUE KEY(user_id)
+DISTRIBUTED BY HASH(user_id) BUCKETS 5 -- ~10 GB compressed
+PROPERTIES ("enable_unique_key_merge_on_write" = "true");
+```
+
+Reference: [Data Model Overview](https://doris.apache.org/docs/table-design/data-model/overview)
+
+---
+
+## Always Use Merge-on-Write (MoW) for UNIQUE Tables
+
+**Impact: CRITICAL — MoR requires runtime merge-sort, making reads 2-10× slower.**
+
+Since Doris 2.1, MoW is the default. For older versions, always set explicitly:
+
+```sql
+PROPERTIES ("enable_unique_key_merge_on_write" = "true")
+```
+
+**MoW vs MoR:**
+
+| Feature | MoW | MoR |
+|---------|-----|-----|
+| Read speed | Fast (pre-merged) | Slow (merge at query) |
+| Write speed | Slightly slower | Faster writes |
+| DELETE support | Yes | Yes |
+| Partial update | Yes | Limited |
+
+**Always use MoW** unless you have a write-heavy workload with minimal reads.
+
+Reference: [Unique Key Model](https://doris.apache.org/docs/table-design/data-model/unique)
+
+---
+
+## Sequence Column for Out-of-Order CDC
+
+**Impact: HIGH — CDC events may arrive out of order; older records can overwrite newer ones.**
+
+When using UNIQUE KEY with CDC (Flink CDC, Debezium, Canal), set a sequence column to guarantee ordering:
+
+```sql
+CREATE TABLE users (
+ user_id BIGINT NOT NULL,
+ update_time DATETIME NOT NULL,
+ name VARCHAR(100),
+ email VARCHAR(200)
+) UNIQUE KEY(user_id)
+DISTRIBUTED BY HASH(user_id) BUCKETS 5 -- CDC table: ~10 GB compressed
+PROPERTIES (
+ "enable_unique_key_merge_on_write" = "true",
+ "function_column.sequence_col" = "update_time"
+);
+```
+
+**How it works:** When two rows have the same primary key, Doris keeps the one with the *higher* sequence column value, regardless of arrival order.
+
+**When to use:** Any CDC pipeline where events may arrive out of order (network delays, reprocessing, multiple sources).
+
+Reference: [Sequence Column](https://doris.apache.org/docs/table-design/data-model/unique#sequence-column)
+
+---
+
+## Partition Rules
+
+## AUTO PARTITION for Sporadic Data
+
+**Impact: HIGH — Avoids creating empty partitions for dates with no data.**
+
+Use AUTO PARTITION when data arrival is unpredictable (not every day/month has data):
+
+```sql
+CREATE TABLE sparse_events (
+ event_time DATETIME NOT NULL,
+ user_id BIGINT,
+ event_type VARCHAR(50)
+) DUPLICATE KEY(event_time, user_id)
+AUTO PARTITION BY RANGE(date_trunc(event_time, 'month'))
+()
+DISTRIBUTED BY HASH(user_id) BUCKETS 5; -- sporadic events: ~10 GB compressed
+```
+
+**When to use AUTO vs DYNAMIC:**
+- **Dynamic:** Continuous data (logs, metrics) — pre-creates future partitions
+- **Auto:** Sporadic data (user uploads, batch jobs) — creates only when data arrives
+
+Reference: [Auto Partition](https://doris.apache.org/docs/table-design/data-partitioning/auto-partitioning)
+
+---
+
+## Dynamic Partition for Automated Data Lifecycle
+
+**Impact: HIGH — Automates partition creation and TTL-based data cleanup.**
+
+Key properties:
+- `time_unit`: DAY, WEEK, MONTH
+- `start`: Negative number = how many units to keep (TTL). E.g., `"-7"` keeps 7 days.
+- `end`: Positive number = how many future partitions to pre-create
+- `buckets`: AUTO or fixed number per partition
+
+```sql
+PROPERTIES (
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "DAY",
+ "dynamic_partition.start" = "-7",
+ "dynamic_partition.end" = "3",
+ "dynamic_partition.prefix" = "p"
+);
+```
+
+**Warning: Do not use dynamic partition for tables with < 20 million rows.** For small tables, it creates wasteful empty partitions that go unnoticed and add unnecessary metadata overhead. Use a single partition or no partition instead.
+
+Reference: [Dynamic Partition](https://doris.apache.org/docs/table-design/data-partitioning/dynamic-partitioning)
+
+---
+
+## RANGE Partition on Time Column for Time-Series
+
+**Impact: CRITICAL — Partition key cannot be changed after table creation.**
+
+For any table with a time column (logs, events, metrics), use RANGE partition:
+
+```sql
+PARTITION BY RANGE(event_time) ()
+```
+
+Combined with dynamic partitioning, this enables:
+- **Partition pruning:** `WHERE event_time > '2025-01-01'` only scans relevant partitions
+- **TTL:** Old partitions are auto-dropped
+- **Parallel loading:** Each partition can be loaded independently
+
+**Incorrect:**
+
+```sql
+-- BAD: No partition on a time-series table
+CREATE TABLE events (
+ event_time DATETIME, user_id BIGINT, data STRING
+) DUPLICATE KEY(event_time)
+DISTRIBUTED BY HASH(user_id) BUCKETS 8;
+-- Every query scans ALL data, no TTL possible
+```
+
+**Correct:**
+
+```sql
+CREATE TABLE events (
+ event_time DATETIME NOT NULL, user_id BIGINT, data STRING
+) DUPLICATE KEY(event_time, user_id)
+PARTITION BY RANGE(event_time) ()
+DISTRIBUTED BY HASH(user_id) BUCKETS 10 -- time-series events: ~50 GB/day compressed
+PROPERTIES (
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "DAY",
+ "dynamic_partition.start" = "-30",
+ "dynamic_partition.end" = "3",
+ "dynamic_partition.prefix" = "p"
+);
+```
+
+Reference: [Range Partition](https://doris.apache.org/docs/table-design/data-partitioning/range-partitioning)
+
+---
+
+## Skip Partitioning for Small Tables (< 1 GB)
+
+**Impact: HIGH — Unnecessary partitioning on small tables wastes metadata and hurts performance.**
+
+If a table is under 1 GB total, do not partition it. Just use bucketing:
+
+```sql
+-- GOOD: Small dimension table, no partition needed
+CREATE TABLE dim_countries (
+ country_code VARCHAR(3) NOT NULL,
+ country_name VARCHAR(100),
+ region VARCHAR(50)
+) DUPLICATE KEY(country_code)
+DISTRIBUTED BY HASH(country_code) BUCKETS 3;
+```
+
+**Rule of thumb:**
+- < 1 GB → No partition
+- 1-100 GB → Consider partition if time-series
+- > 100 GB → Always partition
+
+Reference: [Data Partitioning](https://doris.apache.org/docs/table-design/data-partitioning/data-distribution)
+
+---
+
+## Bucket Rules
+
+## Cloud Mode Requires HASH Bucketing for MoW
+
+**Impact: HIGH — UNIQUE MoW tables in cloud mode must use HASH bucketing.**
+
+In Cloud mode (storage-compute separation), RANDOM bucketing is not supported for UNIQUE KEY tables with MoW enabled.
+
+```sql
+-- GOOD: Cloud MoW with HASH
+CREATE TABLE users (...)
+UNIQUE KEY(user_id)
+DISTRIBUTED BY HASH(user_id) BUCKETS 5 -- cloud MoW: ~10 GB compressed
+PROPERTIES (
+ "enable_unique_key_merge_on_write" = "true",
+ "replication_num" = "1" -- cloud mode
+);
+```
+
+
+---
+
+## Use Composite Bucket Key to Fix Data Skew
+
+**Impact: HIGH (Single skewed bucket key concentrates data; composite key distributes it evenly)**
+
+When a single column has uneven distribution (some values appear much more than others), use a composite bucket key.
+
+**Incorrect (single skewed key):**
+
+```sql
+-- BAD: site_id 54321 has 80% of traffic → bucket_4 is 80% of data
+CREATE TABLE site_access (
+ site_id INT, city_code INT, pv BIGINT
+) DUPLICATE KEY(site_id, city_code)
+DISTRIBUTED BY HASH(site_id) BUCKETS 16;
+```
+
+**Correct (composite key):**
+
+```sql
+-- GOOD: combining site_id + city_code distributes the hot site across cities
+CREATE TABLE site_access (
+ site_id INT, city_code INT, pv BIGINT
+) DUPLICATE KEY(site_id, city_code)
+DISTRIBUTED BY HASH(site_id, city_code) BUCKETS 16;
+-- Data for site_id=54321 is now spread across multiple buckets by city.
+```
+
+**When to use composite keys:**
+- One column dominates traffic (e.g., one large customer)
+- Single-column hash creates visible hot tablets
+- Detect with: `SHOW TABLETS FROM table_name` and check size distribution
+
+Reference: [Data Distribution](https://doris.apache.org/docs/table-design/data-partitioning/data-distribution)
+
+---
+
+## HASH Bucket for Pruning; RANDOM Only for DUP Full-Scans
+
+**Impact: CRITICAL — Bucket key cannot be changed after table creation.**
+
+- **HASH bucket:** Filters on the bucket column prune to a single tablet → fast point queries
+- **RANDOM bucket:** Guarantees perfectly even distribution but no query pruning
+
+| Feature | HASH | RANDOM |
+|---------|------|--------|
+| Query pruning | Yes (WHERE on bucket col) | No |
+| Data distribution | Depends on column cardinality | Always even |
+| Supported models | ALL | DUPLICATE only |
+
+**30% Skew Rule:** If your chosen bucket column has >30% data skew (one value dominates), you must either use RANDOM bucketing or switch to a composite key. Detect with `SHOW TABLETS FROM table_name` and check size distribution.
+
+**Incorrect:**
+
+```sql
+-- BAD: RANDOM on a UNIQUE table (not supported)
+CREATE TABLE users (...) UNIQUE KEY(user_id)
+DISTRIBUTED BY RANDOM BUCKETS 8;
+```
+
+**Correct:**
+
+```sql
+-- GOOD: HASH on primary key for UNIQUE
+CREATE TABLE users (...) UNIQUE KEY(user_id)
+DISTRIBUTED BY HASH(user_id) BUCKETS 8; -- ~15 GB compressed
+
+-- GOOD: RANDOM for DUP when no filter column is obvious
+CREATE TABLE raw_logs (...) DUPLICATE KEY(log_time)
+DISTRIBUTED BY RANDOM BUCKETS 3; -- small table or full-scan workload
+```
+
+Reference: [Data Distribution](https://doris.apache.org/docs/table-design/data-partitioning/data-distribution)
+
+---
+
+## Choose High-Cardinality, Low-NULL Bucket Key
+
+**Impact: HIGH — Low-cardinality keys cause uneven data distribution.**
+
+Good bucket key properties:
+1. **High cardinality** — many distinct values (user_id, order_id)
+2. **Low NULL rate** — NULLs all hash to the same bucket
+3. **Used in WHERE/JOIN** — enables partition pruning
+
+**Bad choices:** status (3 values), gender (2 values), country (200 values for global data)
+**Good choices:** user_id, order_id, device_id, session_id
+
+Reference: [Data Distribution](https://doris.apache.org/docs/table-design/data-partitioning/data-distribution)
+
+---
+
+## Target 1-10 GB Per Tablet
+
+**Impact: HIGH — Tablet size directly affects query parallelism and metadata overhead.**
+
+Rules:
+- **Target:** 1-10 GB per tablet (compressed)
+- **Max buckets per partition:** ≤ 64
+- **Preferred:** Calculate explicit bucket count: `daily_data_GB / target_tablet_GB`
+
+```sql
+-- GOOD: Explicit count from sizing math
+-- 14 GB/day partition, target ~2 GB/tablet → 8 buckets
+DISTRIBUTED BY HASH(user_id) BUCKETS 8
+
+-- 3 GB/day partition, target ~1 GB/tablet → 4 buckets
+DISTRIBUTED BY HASH(order_id) BUCKETS 4
+
+-- 136 GB/day partition, target ~4 GB/tablet → 32 buckets
+DISTRIBUTED BY HASH(conn_id) BUCKETS 32
+```
+
+Always write a numeric bucket count. Automatic bucket sizing obscures intent and may pick suboptimal counts; if volume is unknown, use a conservative explicit fallback such as 3, 8, 16, or 32 buckets based on expected table size.
+
+**Warning signs:**
+- Tablets < 100 MB → Too many buckets, merge or reduce
+- Tablets > 20 GB → Too few buckets, increase count
+
+Reference: [Data Distribution](https://doris.apache.org/docs/table-design/data-partitioning/data-distribution)
+
+---
+
+## Sort Key Rules
+
+## Avoid FLOAT/DOUBLE in Sort Key
+
+**Impact: HIGH — FLOAT/DOUBLE columns disable prefix index and ZoneMap pruning.**
+
+- FLOAT and DOUBLE **cannot participate** in the prefix index
+- They also **disable ZoneMap** statistics for that column
+- Use DECIMAL instead for numeric precision
+
+```sql
+-- BAD: FLOAT in key breaks prefix index
+DUPLICATE KEY(price, product_id) -- price is FLOAT
+
+-- GOOD: Use DECIMAL
+CREATE TABLE products (
+ product_id INT NOT NULL,
+ price DECIMAL(10,2) NOT NULL,
+ name VARCHAR(200)
+) DUPLICATE KEY(product_id, price);
+```
+
+Reference: [Prefix Index](https://doris.apache.org/docs/table-design/index/prefix-index)
+
+---
+
+## Cluster Key to Decouple Sort from Primary Key
+
+**Impact: HIGH — UNIQUE tables sort data by primary key, but queries may filter on other columns.**
+
+For UNIQUE MoW tables, the primary key determines dedup but the **Cluster Key** determines physical sort order:
+
+```sql
+CREATE TABLE users (
+ user_id BIGINT NOT NULL,
+ region VARCHAR(20),
+ name VARCHAR(100)
+) UNIQUE KEY(user_id)
+DISTRIBUTED BY HASH(user_id) BUCKETS 5 -- ~10 GB compressed
+PROPERTIES (
+ "enable_unique_key_merge_on_write" = "true",
+ "cluster_key" = "region, user_id"
+);
+-- Dedup by user_id, but data sorted by region first (better for region-filtered queries)
+```
+
+**When to use:** When your most common WHERE clause filters on columns that are NOT your primary key.
+
+Reference: [Cluster Key](https://doris.apache.org/docs/table-design/index/prefix-index#cluster-key)
+
+---
+
+## Fixed-Length Types (INT/DATE) Before VARCHAR in Key
+
+**Impact: HIGH — A VARCHAR column terminates the prefix index.**
+
+Prefix index rules:
+- Max 3 columns OR 36 bytes (whichever comes first)
+- **VARCHAR terminates the index** — only the first 20 bytes of the VARCHAR are included, and no more columns after it
+
+```sql
+-- GOOD: Fixed-length types first
+DUPLICATE KEY(user_id, event_date, event_type)
+-- INT(4B) DATE(4B) VARCHAR(→ terminates)
+
+-- BAD: VARCHAR first wastes the prefix index
+DUPLICATE KEY(event_type, user_id, event_date)
+-- VARCHAR(→ terminates after 20B, user_id and event_date NOT in index)
+```
+
+Reference: [Prefix Index](https://doris.apache.org/docs/table-design/index/prefix-index)
+
+---
+
+## Prefix Index Limits — 3 Columns, 36 Bytes
+
+**Impact: HIGH — Columns beyond the limit get no prefix index benefit.**
+
+| Rule | Detail |
+|------|--------|
+| Max columns | 3 |
+| Max bytes | 36 |
+| VARCHAR | Terminates the index (only first 20 bytes included) |
+| FLOAT/DOUBLE | **Cannot be in prefix index** — breaks ZoneMap too |
+
+**Example:**
+
+```sql
+-- user_id(BIGINT=8B) + event_date(DATE=4B) + app_id(INT=4B) = 16B, 3 cols ✓
+DUPLICATE KEY(user_id, event_date, app_id)
+
+-- If you need a 4th filter column, add a separate index (BloomFilter, Inverted)
+```
+
+Reference: [Prefix Index](https://doris.apache.org/docs/table-design/index/prefix-index)
+
+---
+
+## High-Selectivity Columns First in Sort Key
+
+**Impact: CRITICAL — Sort key order determines query performance through prefix index.**
+
+Place columns in this order:
+1. Most frequently filtered columns first
+2. Higher selectivity (more distinct values) before lower
+3. Equality filters before range filters
+
+```sql
+-- GOOD: user_id (high selectivity, common filter) first
+DUPLICATE KEY(user_id, event_time, event_type)
+
+-- BAD: event_type (low selectivity) first
+DUPLICATE KEY(event_type, event_time, user_id)
+```
+
+The first 3 columns (or first 36 bytes) form the **prefix index**, which is the primary lookup structure in Doris.
+
+Reference: [Sort Key](https://doris.apache.org/docs/table-design/index/prefix-index)
+
+---
+
+## Data Type Rules
+
+## BITMAP_UNION for Exact Count-Distinct and Funnel Analysis
+**Impact: HIGH — COUNT(DISTINCT col) on high-cardinality columns is extremely expensive. BITMAP gives exact results with O(1) merge.**
+```sql
+CREATE TABLE daily_uv (
+ dt DATE NOT NULL,
+ page VARCHAR(200) NOT NULL,
+ uv BITMAP BITMAP_UNION
+) AGGREGATE KEY(dt, page)
+DISTRIBUTED BY HASH(page) BUCKETS 5; -- ~10 GB compressed
+-- Insert with to_bitmap():
+INSERT INTO daily_uv SELECT '2025-01-01', '/home', to_bitmap(user_id) FROM events;
+-- Query exact UV:
+SELECT dt, bitmap_count(uv) AS unique_visitors FROM daily_uv GROUP BY dt;
+```
+**Funnel / Retention with bitmap_intersect():**
+```sql
+SELECT bitmap_count(bitmap_intersect(uv)) AS retained_users
+FROM daily_uv WHERE dt IN ('2025-01-01', '2025-01-02');
+```
+**Orthogonal analysis:** Use INTERSECT_COUNT for multi-dimensional user segmentation.
+Reference: [BITMAP](https://doris.apache.org/docs/sql-manual/data-types/aggregate/BITMAP)
+
+---
+
+## Doris-Specific Type Gotchas
+**DATETIME precision:** Default DATETIME is seconds. Use `DATETIME(3)` for milliseconds, `DATETIME(6)` for microseconds.
+**STRING vs VARCHAR:** STRING cannot be used as key, partition, or bucket column. Use VARCHAR for keyed columns.
+**VARCHAR(65533) has identical performance to VARCHAR(255)** — Doris uses variable-length storage, so there's no penalty for a larger max. When unsure, use VARCHAR(65533).
+**CHAR vs VARCHAR:** CHAR pads with spaces to fixed length. Only use CHAR for truly fixed-width codes (country_code CHAR(3)). Otherwise, prefer VARCHAR.
+```sql
+-- GOOD: Appropriate types
+CREATE TABLE t (
+ ts DATETIME(3), -- millisecond precision
+ country CHAR(3), -- fixed 3-char code
+ name VARCHAR(65533), -- variable, no perf penalty
+ big_text STRING -- only for non-key, non-partitioned columns
+);
+```
+
+---
+
+## Use Native Types, Not STRING for Everything
+**Impact: HIGH — STRING columns cannot be used as partition, bucket, or sort key columns.**
+Always use the most specific type: INT for numbers, DATE/DATETIME for timestamps, DECIMAL for money.
+```sql
+-- BAD
+CREATE TABLE t (id STRING, ts STRING, amount STRING);
+-- GOOD
+CREATE TABLE t (id BIGINT, ts DATETIME(3), amount DECIMAL(12,2));
+```
+Reference: [Data Types](https://doris.apache.org/docs/sql-manual/data-types/overview)
+
+---
+
+## VARIANT Type for Semi-Structured JSON Data
+**Impact: HIGH — VARIANT provides columnar storage for JSON with automatic type inference.**
+Use VARIANT instead of JSON or STRING for semi-structured data:
+```sql
+CREATE TABLE events (
+ event_time DATETIME NOT NULL,
+ event_id BIGINT NOT NULL,
+ data VARIANT
+) DUPLICATE KEY(event_time, event_id)
+DISTRIBUTED BY HASH(event_id) BUCKETS 5; -- ~10 GB compressed
+-- Query nested fields directly:
+SELECT data['user']['name'], data['action'] FROM events;
+```
+**Schema Template:** Pre-define expected fields for better columnar storage:
+```sql
+ALTER TABLE events SET ("variant_schema_template" = '{"user.name": "STRING", "action": "STRING", "amount": "DOUBLE"}');
+```
+**Inverted Index on VARIANT fields:**
+```sql
+ALTER TABLE events ADD INDEX idx_action(CAST(data['action'] AS VARCHAR)) USING INVERTED;
+```
+**MATCH search on VARIANT text fields:**
+```sql
+SELECT * FROM events WHERE CAST(data['message'] AS VARCHAR) MATCH 'error timeout';
+```
+Reference: [VARIANT Type](https://doris.apache.org/docs/sql-manual/data-types/semi-structured/VARIANT)
+
+---
+
+## JSON/ARRAY/Complex Types Disable ZoneMap
+**Impact: HIGH — ZoneMap statistics are not generated for JSON, ARRAY, MAP, STRUCT, or STRING columns.**
+ZoneMap stores min/max values per data page, enabling skip scanning. Complex types disable this optimization.
+**Workaround:** Extract frequently filtered fields into dedicated columns with native types.
+```sql
+-- BAD: Filtering on JSON field — no ZoneMap, full scan
+SELECT * FROM events WHERE payload->'$.status' = 'error';
+-- GOOD: Extract to a native column
+CREATE TABLE events (
+ ..., status VARCHAR(20), payload JSON,
+ INDEX idx_status(status) USING INVERTED
+);
+SELECT * FROM events WHERE status = 'error';
+```
+
+---
+
+## Table Properties
+
+## Properties Cloud Mode Forces
+In Cloud mode (storage-compute separation):
+- `replication_num` is forced to `1` (data stored in object storage)
+- HASH bucketing required for UNIQUE MoW
+- File cache controls performance (see `schema-cache-file-cache`)
+```sql
+PROPERTIES ("replication_num" = "1");
+```
+
+---
+
+## LZ4 Default vs ZSTD for Logs/Cold Data
+| Algorithm | Speed | Ratio | Use When |
+|-----------|-------|-------|----------|
+| LZ4 | Fastest | Lower | Default, hot data, low-latency |
+| ZSTD | Slower | 2-3× better | Logs, cold data, archival |
+```sql
+-- For log/event tables with high redundancy:
+PROPERTIES ("compression" = "zstd");
+-- For hot analytical tables (default):
+PROPERTIES ("compression" = "lz4");
+```
+
+---
+
+## Index Rules
+
+## Bitmap Index for Medium-Cardinality Dimensions (100-100K distinct values)
+**Impact: MEDIUM — Efficient for columns with moderate cardinality used in filters.**
+```sql
+CREATE INDEX idx_city ON table_name(city) USING BITMAP;
+```
+**Sweet spot:** 100 to 100,000 distinct values (status codes, cities, categories).
+**Restrictions:**
+- Only one bitmap index can be created at a time (sequential schema change)
+- `DROP INDEX` is also a schema change and takes significant time
+- Only on value columns, not key columns
+- Segment V2 format required
+
+---
+
+## BloomFilter for High-Cardinality Equality Filters
+**Impact: HIGH — Skips data pages that definitely don't contain the filtered value.**
+Use for columns with ≥ 5000 distinct values, filtered with `=` or `IN`.
+```sql
+-- Add BloomFilter index
+PROPERTIES ("bloom_filter_columns" = "trace_id, session_id");
+```
+**Constraints:**
+- NOT supported on TINYINT, FLOAT, or DOUBLE columns
+- Only accelerates `=` and `IN` filters (not LIKE, not range)
+- Minimum recommended cardinality: 5000+ distinct values
+- False positive rate ~1% (configurable via bloom_filter_fpp)
+- Do not use inline `INDEX ... USING BLOOM FILTER` in generated DDL; use the table property above.
+
+---
+
+## Inverted Index for Text Search and Range on Non-Key Columns
+**Impact: HIGH — Enables full-text search and efficient range filters without modifying the sort key.**
+```sql
+-- Text search with parser
+INDEX idx_body(body) USING INVERTED PROPERTIES("parser" = "unicode")
+-- Equality/range on non-key column
+INDEX idx_status(status) USING INVERTED
+```
+**Parser options:** `none` (exact), `english`, `unicode` (multilingual), `chinese` (CJK).
+**Supported filter types:** `=`, `IN`, `>`, `<`, `>=`, `<=`, `MATCH_ALL`, `MATCH_ANY`, `MATCH_PHRASE`.
+Reference: [Inverted Index](https://doris.apache.org/docs/table-design/index/inverted-index)
+
+---
+
+## NGram BloomFilter for LIKE '%pattern%' Queries
+**Impact: HIGH — LIKE '%pattern%' causes full table scan without NGram index.**
+```sql
+INDEX idx_url(url) USING NGRAM_BF PROPERTIES("gram_size" = "3", "bf_size" = "1024")
+```
+The NGram BloomFilter breaks text into 3-character grams and uses a bloom filter to skip non-matching pages.
+**When to use:** `LIKE '%keyword%'` queries on VARCHAR/STRING columns.
+**When NOT to use:** Exact match (`=`) → use regular BloomFilter. Full-text search → use Inverted Index.
+Reference: [NGram BloomFilter](https://doris.apache.org/docs/table-design/index/ngram-bloomfilter-index)
+
+---
+
+## Full-Text Search with MATCH Functions and BM25 Scoring
+**Impact: HIGH — Enables search-engine-like text queries with relevance ranking.**
+**7 MATCH operators:** MATCH_ALL, MATCH_ANY, MATCH_PHRASE, MATCH_PHRASE_PREFIX, MATCH_PHRASE_EDGE, MATCH_REGEXP, MATCH_ELEMENT_EQ.
+```sql
+-- Setup: Inverted index with parser
+INDEX idx_content(content) USING INVERTED PROPERTIES(
+ "parser" = "unicode", "support_phrase" = "true"
+)
+-- Queries (WHERE clause only):
+WHERE content MATCH_ALL 'database analytics' -- all terms
+WHERE content MATCH_ANY 'database analytics' -- any term
+WHERE content MATCH_PHRASE 'real time analytics' -- exact phrase
+```
+**SEARCH() unified DSL:** Combine operators in one function:
+```sql
+WHERE SEARCH(content, '"real time" +analytics -legacy', 'parser=unicode')
+```
+**BM25 scoring:** Rank results by relevance:
+```sql
+SELECT doc_id, score() AS relevance FROM docs
+WHERE content MATCH_ANY 'doris analytics' ORDER BY relevance DESC;
+```
+**Custom analyzers, hybrid text+vector search, and VARIANT text search** are all supported.
+Reference: [Full-Text Search](https://doris.apache.org/docs/table-design/index/inverted-index)
+
+---
+
+## HNSW/IVF Vector Index for ANN Search
+**Impact: HIGH — Enables approximate nearest neighbor search on embeddings.**
+Requires Doris 4.0+ with vector support.
+```sql
+CREATE TABLE embeddings (
+ doc_id BIGINT NOT NULL,
+ content VARCHAR(65533),
+ embedding ARRAY<FLOAT> NOT NULL
+) DUPLICATE KEY(doc_id)
+DISTRIBUTED BY HASH(doc_id) BUCKETS 5; -- ~10 GB compressed
+-- Add HNSW index:
+CREATE INDEX idx_vec ON embeddings(embedding) USING HNSW
+PROPERTIES("dim" = "768", "metric" = "cosine", "m" = "16", "ef_construction" = "200");
+-- Query:
+SELECT doc_id, l2_distance(embedding, [0.1, 0.2, ...]) AS dist
+FROM embeddings ORDER BY dist LIMIT 10;
+```
+**Index types:** HNSW (fast, memory-heavy), IVF_FLAT (balanced), IVF_PQ (compressed).
+
+---
+
+## Materialized Views
+
+## Async MV for Multi-Table JOIN Acceleration
+**Impact: HIGH — Pre-computes JOINs so queries read a flat table instead of joining at runtime.**
+```sql
+CREATE MATERIALIZED VIEW mv_order_details
+REFRESH AUTO ON SCHEDULE EVERY 10 MINUTE
+AS SELECT o.order_id, o.amount, p.product_name, c.customer_name
+FROM orders o
+JOIN products p ON o.product_id = p.product_id
+JOIN customers c ON o.customer_id = c.customer_id;
+```
+**Refresh modes:** AUTO or COMPLETE with ON SCHEDULE (periodic), ON COMMIT (on base table change, limit: ≤5 updates/hr), or manual refresh.
+Reference: [Async Materialized View](https://doris.apache.org/docs/query-acceleration/materialized-view/async-materialized-view)
+
+---
+
+## Async MV Operational Limits
+| Limit | Value |
+|-------|-------|
+| Max rows per MV | ~50 million |
+| Max JOINs | 2 |
+| Max partitions | 30 |
+| Max concurrent refreshes | 3 |
+| Cluster resource cap | 40% |
+| ON COMMIT limit | ≤ 5 updates/hour |
+**Capacity estimation:** ~20-30 active async MVs on a 3-node cluster.
+**Layered design pattern:** Build MVs on top of other MVs (Layer 1: base aggregations, Layer 2: cross-table joins).
+**partition_sync_limit:** Focus refresh on recent data only:
+```sql
+CREATE MATERIALIZED VIEW mv_recent
+PROPERTIES ("partition_sync_limit" = "7")
+REFRESH AUTO ON SCHEDULE EVERY 1 HOUR
+AS SELECT ... FROM orders ...;
+```
+
+---
+
+## Sync MV (Rollup) for Single-Table Aggregation
+**Impact: HIGH — Pre-aggregates data; optimizer rewrites queries automatically.**
+```sql
+CREATE MATERIALIZED VIEW mv_daily_sales AS
+SELECT dt, store_id, SUM(amount) AS total, COUNT(*) AS cnt
+FROM orders GROUP BY dt, store_id;
+```
+**Restriction: NOT supported on UNIQUE KEY tables.** Use async MVs instead.
+Sync MVs are maintained synchronously with the base table — zero lag.
+Reference: [Sync Materialized View](https://doris.apache.org/docs/query-acceleration/materialized-view/sync-materialized-view)
+
+---
+
+## Caching Rules
+
+## File Cache Strategies (Cloud / Storage-Compute Separation)
+**Key metric: 60% of data cached locally = 95% cache hit rate.** Maintain 90%+ hit rate.
+Cache modes:
+- **LRU (default):** Evicts least recently used. Good for uniform access.
+- **TTL:** Time-based eviction. Good for time-series with clear hot/cold boundary.
+**Table-level cache control:**
+```sql
+-- Keep dimension tables cached forever
+ALTER TABLE dim_stores SET ("file_cache_ttl_seconds" = "0"); -- 0 = never evict
+-- Hot window for fact tables
+ALTER TABLE fact_orders SET ("file_cache_ttl_seconds" = "604800"); -- 7 days
+```
+**IOPS guidance:** SSD cache: ~500 IOPS/disk. HDD: ~200 IOPS/disk.
+
+---
+
+## Query Cache and Partition Cache
+**Query cache:** Identical SQL → instant response (no computation).
+```sql
+SET enable_query_cache = true;
+```
+**Partition cache:** Only recomputes partitions with new data. Ideal for time-series dashboards where most partitions are historical and unchanged.
+```sql
+SET enable_partition_cache = true;
+```
+**When to use:**
+- Query cache: Repeated identical queries (dashboard auto-refresh)
+- Partition cache: Time-series with mostly historical data
+
+---
+
+## Sizing Guides
+
+## BE Sizing — Cloud / Storage-Compute Separation
+In cloud mode, data is in object storage (S3/GCS). BEs only cache hot data locally.
+**CPU:** Same as integrated — determines query parallelism.
+**Memory:** 32-128 GB. Used for query execution, not data storage.
+**Local disk:** SSD for file cache. Size based on hot data ratio (60% cached = 95% hit rate).
+**Elasticity:** Can scale BE nodes independently from data volume. Add BEs for more compute, not more storage.
+
+---
+
+## BE Sizing — Integrated Storage Mode
+In integrated mode, each BE stores data locally on disk.
+**CPU:** 16-64 cores per BE. More cores = more concurrent query/scan threads.
+**Memory:** 64-256 GB per BE. Rule of thumb: 4 GB RAM per 1 TB stored data.
+**Disk:** SSD recommended. 1-10 TB per BE. Use RAID or multiple disks for throughput.
+**Node count:** Start with 3 nodes for HA. Scale horizontally for more throughput.
+
+---
+
+## FE Node Sizing
+| Cluster Size | FE Nodes | Memory | CPU |
+|-------------|----------|--------|-----|
+| Small (< 10 BE) | 1 Leader + 2 Follower | 16 GB | 8 cores |
+| Medium (10-50 BE) | 1 Leader + 2 Follower | 32 GB | 16 cores |
+| Large (50+ BE) | 1 Leader + 4 Follower | 64 GB | 32 cores |
+FE nodes store metadata (table schemas, partitions, tablets) in memory. Scale FE memory with table count and partition count rather than data volume.
+
+---
+
+## Storage Calculation Formula
+```
+Raw data × compression ratio × replication_num = Required storage
+```
+**Compression ratios (typical):**
+| Data Type | LZ4 Ratio | ZSTD Ratio |
+|-----------|-----------|------------|
+| Structured (numeric) | 3-5× | 5-8× |
+| Logs/text | 5-10× | 10-20× |
+| JSON/semi-structured | 3-8× | 5-12× |
+**Example:** 1 TB/day raw logs × 30 days retention × ZSTD (10× compression) × 1 replica = 3 TB storage
+**Include overhead:** Add 20-30% for metadata, compaction temp space, and safety margin.
+
+---
+
+## Getting Started
+
+> For {{CLOUD_PRODUCT_NAME}} setup, see the `{{CLOUD_OPS_SKILL}}` skill.
+
+## Getting Started — Self-Hosted / BYOC / On-Prem
+### Prerequisites
+- FE nodes: Java 8+ runtime
+- BE nodes: Linux with sufficient disk, memory
+- Network: FE and BE nodes must be able to communicate
+### Deployment Steps
+1. Deploy FE nodes (1 Leader + 2 Followers for HA)
+2. Deploy BE nodes (3+ for production)
+3. Register BEs with FE: `ALTER SYSTEM ADD BACKEND "<be_host>:9050";`
+4. Create database and tables
+### Connect
+```bash
+mysql -h <fe_host> -P 9030 -u root
+```
+### Self-Hosted Properties
+```sql
+PROPERTIES ("replication_num" = "3"); -- 3 replicas for HA
+```
+Reference: [Apache Doris Installation](https://doris.apache.org/docs/install/cluster-deployment/standard-deployment)
+
+---
diff --git a/skills/doris-best-practices/SKILL.md b/skills/doris-best-practices/SKILL.md
new file mode 100644
index 0000000..26409f5
--- /dev/null
+++ b/skills/doris-best-practices/SKILL.md
@@ -0,0 +1,317 @@
+---
+name: doris-best-practices
+description: >
+ Apache Doris table design and cluster sizing best practices.
+ MUST USE when writing, reviewing, or optimizing Doris CREATE TABLE statements,
+ partition/bucket strategies, data models, or cluster configurations.
+ ALSO MUST USE whenever the doris-architecture-advisor skill produces DDL — apply
+ the Pre-Flight Checklist to every CREATE TABLE before output.
+ Also triggers on any workload design involving: IoT, analytics, dashboard, CDC,
+ time-series, log analysis, real-time warehouse, point query, data platform, or
+ any scenario where table design decisions are being made.
+ Also triggers on replacing or migrating from legacy analytics/search/serving
+ stacks such as Impala, Kudu, Elasticsearch/ES, Greenplum, Presto, HBase,
+ Hive, Hadoop, Redis, or Lambda-style multi-engine data platforms, even when
+ Apache Doris is not named explicitly.
+ Also use when user provides a {{PRODUCT_NAME}} connection string or asks to get started.
+ Also triggers on slow query investigation, query profiling, runtime performance
+ diagnosis, tablet skew analysis, and table health checks — any scenario where
+ runtime evidence (profile output, tablet distribution) informs optimization.
+ For Cloud operations (auth, cluster lifecycle, billing, networking), defer to
+ the {{CLOUD_OPS_SKILL}} skill.
+license: Apache-2.0
+metadata:
+ author: Apache Doris Community
+ version: "3.1.0"
+---
+
+# Apache Doris Best Practices
+
+> Problem-first table design intelligence for Apache Doris.
+> 37 rules, 7 use case templates, 4 sizing guides.
+> All details in `references/` directory and compiled `AGENTS.md`.
+
+---
+
+## 1 ▸ Problem-First Routing
+
+### I need to build…
+
+| Problem | Template(s) | Key Rules |
+|---------|-------------|-----------|
+| Real-time log/event analytics | `usecase-log-event` | DUPLICATE, RANGE partition, dynamic TTL, ZSTD |
+| CDC / MySQL sync to Doris | `usecase-cdc-sync` | UNIQUE MoW, sequence_col, HASH bucket |
+| Dashboard with pre-aggregated metrics | `usecase-dashboard-metrics` | AGGREGATE, BITMAP_UNION, sync MV |
+| User-facing API with low-latency point queries | `usecase-point-query` | UNIQUE MoW, store_row_column, BloomFilter |
+| Star schema with JOIN-heavy analytics | `usecase-star-schema-join` | Colocation, same bucket key/count |
+| Small dimension / lookup table | `usecase-dimension-lookup` | DUPLICATE, RANDOM bucket, 3 buckets |
+| Observability (logs + traces + metrics) | `usecase-observability` | 3 tables: DUP logs, DUP traces, AGG metrics |
+| Vehicle/fleet tracking | `usecase-log-event` + `usecase-point-query` | Time-series + point-query hybrid |
+| E-commerce order analytics | `usecase-star-schema-join` + `usecase-dashboard-metrics` | Star schema + AGG rollups |
+| Full-text search / content search | `schema-index-text-search` | Inverted index, MATCH, BM25 |
+| User behavior / funnel analysis | `schema-types-bitmap-count-distinct` | BITMAP_UNION, bitmap_intersect |
+| Semi-structured JSON data | `schema-types-variant-json` | VARIANT type, schema_template |
+
+### My query is slow after evidence shows…
+
+For live slow-query or runtime diagnosis, do **not** use this table as the first response. First read `references/cli-investigation.md` and collect or attempt evidence (`profile get`, `profile list`, `profile history`, `tablet`, `EXPLAIN`, or `auth status`). Use this table only after evidence points to the symptom.
+
+| Symptom | Check These Rules | Quick Fix |
+|---------|------------------|-----------|
+| Full table scan on WHERE clause | `schema-keys-selectivity-first` | Move filtered column to sort key position 1 |
+| JOINs are slow / shuffle | `usecase-star-schema-join` | Small dims (<1GB): broadcast + runtime filter. Large: colocation |
+| COUNT DISTINCT is slow | `schema-types-bitmap-count-distinct` | Switch to BITMAP_UNION aggregation |
+| LIKE '%keyword%' is slow | `schema-index-ngram-for-like` | Add NGram BloomFilter index |
+| Point query latency too high | `usecase-point-query` | Enable store_row_column + Prepared Statement |
+| Storage growing too fast | `schema-partition-auto-on-demand` + `schema-props-compression` | AUTO PARTITION + ZSTD compression + scheduled DROP PARTITION |
+| Sync MV not being used | `schema-mv-sync-rollup` | Use raw columns (not date_trunc) in MV GROUP BY; unique aliases |
+| Async MV rewrite fails | `schema-mv-async-join` + `schema-mv-async-limits` | Check State/RefreshState; query MV directly if predicate fails |
+| Data skew / hot tablets | `schema-bucket-composite-for-skew` | Composite bucket key or RANDOM |
+| Import fails / data version error | `schema-mv-async-limits` | Check concurrent MV refresh limit (max 3) |
+| VARCHAR in key kills perf | `schema-keys-fixed-length-types` | Move VARCHAR after fixed-length types |
+| Writes slow on UNIQUE table | `schema-model-prefer-mow` | Ensure MoW is enabled (not MoR) |
+
+---
+
+## 2 ▸ Pre-Flight Checklist (Before Any CREATE TABLE)
+
+Run through this checklist in order. Each step references the relevant rule:
+
+- [ ] **Data model** — UNIQUE (updates?) vs DUPLICATE (append?) vs AGGREGATE (pre-agg only?) → `schema-model-choose-for-workload`
+- [ ] **Partition strategy** — Time-series? AUTO PARTITION preferred. Small table? Skip. Do NOT combine AUTO with dynamic_partition. → `schema-partition-*`
+- [ ] **Bucket key + count** — HASH on JOIN key. Calculate explicit count: `daily_GB / target_tablet_GB`. Use explicit fallback counts when volume is unknown: 3 for small dimensions, 8 for medium tables, 16-32 for large daily fact tables. → `schema-bucket-*`
+- [ ] **Sort key order** — High-selectivity first, fixed-length before VARCHAR → `schema-keys-*`
+- [ ] **Data types** — Native types, not STRING. DECIMAL not FLOAT. → `schema-types-*`
+- [ ] **Indexes** — BloomFilter for equality, Inverted for text, NGram for LIKE → `schema-index-*`
+- [ ] **Properties** — MoW enabled? Compression? Cloud mode replication_num=1? → `schema-props-*`
+- [ ] **DDL hard constraints** (Apache Doris rejects DDL if any violated):
+ - UNIQUE KEY + PARTITION BY RANGE → partition column MUST be in the UNIQUE KEY: `UNIQUE KEY(id, dt) PARTITION BY RANGE(dt)`
+ - Key columns must be the FIRST N columns in schema, same order — put key cols first, non-key after. Example: `UNIQUE KEY(account_id, symbol)` means schema must start with `account_id, symbol, ...` — never place non-key columns between key columns
+ - `store_row_column = "true"` only works on UNIQUE MoW — NOT on AGGREGATE or DUPLICATE
+ - AUTO PARTITION requires `date_trunc()` AND empty parens: `AUTO PARTITION BY RANGE(date_trunc(col, 'day')) ()` — bare column name fails, missing `()` fails
+ - Dynamic partition requires explicit `PARTITION BY RANGE(col) ()` clause in DDL — properties alone are not enough
+ - Do not set `dynamic_partition.buckets`; put the numeric count only in `DISTRIBUTED BY HASH(col) BUCKETS N`
+ - `compaction_policy = "time_series"` only for DUPLICATE tables — fails on UNIQUE
+ - Async MV refresh: use `REFRESH AUTO ON SCHEDULE EVERY 10 MINUTE` or `REFRESH COMPLETE ON SCHEDULE EVERY 10 MINUTE` — NOT `REFRESH SCHEDULE EVERY`, NOT `REFRESH ASYNC EVERY(INTERVAL ...)`. Minimum interval: 1 MINUTE
+ - MV using `NOW()`/`CURDATE()`: add `PROPERTIES ("enable_nondeterministic_function" = "true")`
+ - BOOLEAN defaults must be quoted: `DEFAULT "true"` not `DEFAULT TRUE`
+ - BloomFilter index: use `PROPERTIES ("bloom_filter_columns" = "col1,col2")` — NOT inline `INDEX ... USING BLOOM FILTER`
+ - AGGREGATE column syntax: aggregation function BEFORE default: `col BIGINT SUM DEFAULT "0"` — NOT `col BIGINT DEFAULT "0" SUM`
+ - AGGREGATE `DEFAULT "null"` only works for VARCHAR — fails on INT, DATE, DECIMAL, BIGINT. Omit DEFAULT entirely for REPLACE_IF_NOT_NULL on non-string types: `vip_level INT REPLACE_IF_NOT_NULL` (not `DEFAULT "null"`)
+ - `enable_unique_key_partial_update` is a session variable, NOT a table property
+ - Full details: `schema-ddl-gotchas`
+
+---
+
+## 2b ▸ DDL Templates (copy the closest match, customize columns)
+
+For each CREATE TABLE, select the closest template below. Customize column names, types, bucket count, and partition settings. Do NOT write DDL from scratch.
+
+### T1: Append-only events/logs (DUPLICATE)
+```sql
+CREATE TABLE events (
+ entity_id VARCHAR(64) NOT NULL,
+ event_time DATETIME NOT NULL,
+ event_type VARCHAR(50) NOT NULL,
+ payload VARIANT
+) DUPLICATE KEY(entity_id, event_time, event_type)
+PARTITION BY RANGE(event_time) ()
+DISTRIBUTED BY HASH(entity_id) BUCKETS 10
+PROPERTIES (
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "DAY",
+ "dynamic_partition.start" = "-90",
+ "dynamic_partition.end" = "3",
+ "dynamic_partition.prefix" = "p",
+ "compression" = "zstd",
+ "compaction_policy" = "time_series",
+ "replication_num" = "1"
+);
+```
+
+### T2: Updatable with partition (UNIQUE MoW + CDC)
+```sql
+CREATE TABLE orders (
+ order_id BIGINT NOT NULL,
+ order_time DATETIME NOT NULL,
+ update_time DATETIME NOT NULL,
+ status VARCHAR(20),
+ amount DECIMAL(18,2)
+) UNIQUE KEY(order_id, order_time)
+PARTITION BY RANGE(order_time) ()
+DISTRIBUTED BY HASH(order_id) BUCKETS 5
+PROPERTIES (
+ "enable_unique_key_merge_on_write" = "true",
+ "function_column.sequence_col" = "update_time",
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "DAY",
+ "dynamic_partition.start" = "-365",
+ "dynamic_partition.end" = "3",
+ "dynamic_partition.prefix" = "p",
+ "replication_num" = "1"
+);
+```
+
+### T3: Small dimension / lookup (UNIQUE, no partition)
+```sql
+CREATE TABLE dim_product (
+ product_id INT NOT NULL,
+ name VARCHAR(200),
+ category VARCHAR(50)
+) UNIQUE KEY(product_id)
+DISTRIBUTED BY HASH(product_id) BUCKETS 3
+PROPERTIES (
+ "enable_unique_key_merge_on_write" = "true",
+ "replication_num" = "1"
+);
+```
+
+### T4: Pre-aggregated KPIs (AGGREGATE)
+```sql
+CREATE TABLE daily_kpi (
+ stat_date DATE NOT NULL,
+ dimension VARCHAR(50) NOT NULL,
+ metric_sum BIGINT SUM DEFAULT "0",
+ metric_max DOUBLE MAX DEFAULT "0",
+ unique_users BITMAP BITMAP_UNION
+) AGGREGATE KEY(stat_date, dimension)
+PARTITION BY RANGE(stat_date) ()
+DISTRIBUTED BY HASH(dimension) BUCKETS 3
+PROPERTIES (
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "MONTH",
+ "dynamic_partition.start" = "-12",
+ "dynamic_partition.end" = "1",
+ "dynamic_partition.prefix" = "p",
+ "replication_num" = "1"
+);
+```
+
+### T5: Point query / API serving (UNIQUE MoW + row store)
+```sql
+CREATE TABLE user_profiles (
+ user_id BIGINT NOT NULL,
+ update_time DATETIME NOT NULL,
+ name VARCHAR(100),
+ data VARIANT
+) UNIQUE KEY(user_id)
+DISTRIBUTED BY HASH(user_id) BUCKETS 5
+PROPERTIES (
+ "enable_unique_key_merge_on_write" = "true",
+ "function_column.sequence_col" = "update_time",
+ "store_row_column" = "true",
+ "light_schema_change" = "true",
+ "replication_num" = "1"
+);
+```
+
+---
+
+## 3 ▸ Connection & CLI
+
+### Detect CLI
+
+Before running any queries, detect the CLI binary:
+
+1. Check `{{CLI_PATH_ENV}}` env var — if set, use that binary path
+2. `command -v {{CLI}}` — use from PATH
+3. If none available: fall back to `mysql` client (see `references/start-*.md`)
+
+### When {{CLI}} is available, prefer it for all operations:
+
+| Task | {{CLI}} Command |
+|------|-----------------|
+| Run SQL | `{{CLI}} sql "SELECT ..."` |
+| DDL inspection | `{{CLI}} sql "SHOW CREATE TABLE db.t"` |
+| Table/tablet health | `{{CLI}} tablet db.t` (overview) or `{{CLI}} tablet db.t --detail` |
+| Profile a slow query | `{{CLI}} sql "SELECT ..." --profile` → captures query_id |
+| Get query profile | `{{CLI}} profile get <qid>` or `--full` for complete diagnosis |
+| Compare fast vs slow | `{{CLI}} profile diff <slow_qid> <fast_qid>` |
+| Performance trend | `{{CLI}} profile history <sql_pattern> --days 7` |
+| Test connection | `{{CLI}} auth status` |
+| Switch environment | `{{CLI}} use <name>` |
+
+### Runtime Query Investigation
+
+For slow queries or runtime performance issues, read `references/cli-investigation.md`.
+
+- **Evidence first is mandatory**: collect or attempt profile, tablet, DDL, stats, EXPLAIN, history, active-query, or connection evidence before forming hypotheses. If evidence cannot be collected locally, state that and provide the exact commands to run
+- **Prefer existing profiles**: use `profile get <query_id>`, `profile list`, or `profile history` before re-executing SQL
+- **Proactive discovery**: for vague slow-query reports, start with `auth status`, `profile list --active`, and recent `profile list` before asking the user for more context
+- **Safety gate**: before running user SQL with `--profile`, check whether it is safe (no DDL, no mutation, no unbounded scan). For unknown, peak-hour, or expensive SQL, run `{{CLI}} sql "EXPLAIN <query>" --format json` first and ask confirmation or request an existing query_id
+- **Hypotheses, not verdicts**: diagnostic mappings are heuristics. Present evidence, likely cause, what to check next, and when the conclusion may be wrong
+- If {{CLI}} is unavailable, fall back to SQL commands listed in the reference
+- Always use `--format json` for structured agent-readable output
+
+### Quick-start guides
+
+- `references/start-self-hosted.md` — Self-hosted / BYOC / on-prem
+- For {{CLOUD_PRODUCT_NAME}} setup, see the `{{CLOUD_OPS_SKILL}}` skill
+
+---
+
+## 4 ▸ Cluster Sizing
+
+Sizing guides are in:
+- `references/sizing-fe.md` — FE node sizing
+- `references/sizing-be-integrated.md` — BE sizing (integrated storage)
+- `references/sizing-be-cloud.md` — BE sizing (cloud / storage-compute)
+- `references/sizing-storage-formula.md` — Storage calculation formula
+
+---
+
+## 5 ▸ Rule Index by Category
+
+### Data Model — CRITICAL (4 rules)
+- `schema-model-choose-for-workload` — DUP vs UNIQUE vs AGG decision tree
+- `schema-model-prefer-mow` — Always MoW for UNIQUE tables
+- `schema-model-avoid-agg-for-updates` — AGG cannot UPDATE/DELETE
+- `schema-model-sequence-col-for-cdc` — Sequence column for out-of-order CDC
+
+### Partition Strategy — CRITICAL (4 rules)
+- `schema-partition-range-for-timeseries` — RANGE for time-series
+- `schema-partition-dynamic-ttl` — Dynamic partition for automated TTL
+- `schema-partition-auto-on-demand` — AUTO for sporadic data
+- `schema-partition-skip-for-small` — Skip partitioning under 1 GB
+
+### Bucket Strategy — CRITICAL (5 rules)
+- `schema-bucket-hash-vs-random` — HASH for pruning, RANDOM for DUP only
+- `schema-bucket-high-cardinality-key` — Choose high-cardinality column
+- `schema-bucket-composite-for-skew` — Composite key to fix data skew
+- `schema-bucket-target-size` — Target 1-10 GB per tablet
+- `schema-bucket-cloud-mandatory-hash` — Cloud MoW requires HASH
+
+### Sort Key — CRITICAL (5 rules)
+- `schema-keys-selectivity-first` — High selectivity first
+- `schema-keys-fixed-length-types` — Fixed-length before VARCHAR
+- `schema-keys-prefix-index-limits` — 36 bytes max, VARCHAR terminates it
+- `schema-keys-cluster-key-for-mow` — Cluster key for UNIQUE tables
+- `schema-keys-avoid-float` — No FLOAT/DOUBLE in sort key
+
+### Data Types — HIGH (5 rules)
+- `schema-types-native-vs-string` — Native types, not STRING
+- `schema-types-zonemap-limitations` — JSON/ARRAY disable ZoneMap
+- `schema-types-variant-json` — VARIANT for semi-structured JSON
+- `schema-types-bitmap-count-distinct` — BITMAP_UNION for exact count-distinct
+- `schema-types-doris-specifics` — DATETIME precision, VARCHAR vs STRING
+
+### Indexes — HIGH (7 rules)
+- `schema-index-bloomfilter` — BloomFilter for equality
+- `schema-index-inverted` — Inverted for text/range
+- `schema-index-ngram-for-like` — NGram for LIKE %pattern%
+- `schema-index-bitmap` — Bitmap for medium cardinality
+- `schema-index-vector` — HNSW/IVF for ANN search
+- `schema-index-text-search` — Full-text MATCH + BM25
+
+### Query Acceleration — HIGH (3 rules)
+- `schema-mv-sync-rollup` — Sync MV for single-table aggregation
+- `schema-mv-async-join` — Async MV for multi-table JOIN
+- `schema-mv-async-limits` — Operational limits (50M rows, 3 concurrent)
+
+### Table Properties — HIGH/MEDIUM (2 rules)
+- `schema-props-cloud-forced` — Cloud mode forced properties
+- `schema-props-compression` — LZ4 vs ZSTD compression
+
+### Caching — MEDIUM (2 rules)
+- `schema-cache-file-cache` — File cache for cloud mode
+- `schema-cache-query-partition` — Query and partition cache
diff --git a/skills/doris-best-practices/references/cli-investigation.md b/skills/doris-best-practices/references/cli-investigation.md
new file mode 100644
index 0000000..7c9d317
--- /dev/null
+++ b/skills/doris-best-practices/references/cli-investigation.md
@@ -0,0 +1,258 @@
+---
+title: CLI-Based Query Investigation
+tags: [cli, investigation, diagnostics, profiling, slow-query]
+---
+
+## CLI-Based Query Investigation
+
+Evidence-first runtime diagnosis using {{CLI}}. Collect profile, tablet, DDL, stats, EXPLAIN, or connection evidence before forming hypotheses. Do not jump to schema fixes.
+
+---
+
+### Binary Policy
+
+- Primary command: `{{CLI}}`
+- Detection order:
+ 1. `{{CLI_PATH_ENV}}` environment variable → use that binary path
+ 2. `command -v {{CLI}}` → use from PATH
+ 3. If none available: use fallback SQL (see end of this document)
+
+### Safety Policy
+
+- **Read-only evidence collection** may run proactively: `profile get`, `profile list`, `profile diff`, `profile history`, `tablet`, `auth status`, `SHOW CREATE TABLE`, `EXPLAIN`
+- **Before executing user SQL with `--profile`**: check whether the SQL is safe (no DDL, no mutation, no unbounded scan without LIMIT). For unknown or potentially expensive SQL, run `EXPLAIN` first and ask the user to confirm or request an existing `query_id` instead
+- **Never run** DDL, data mutations, cluster changes, or Cloud operations as part of diagnosis without explicit user confirmation
+- Always use `--format json` for agent-readable output
+
+### Evidence-First Hard Gate
+
+- Do not present root causes, DDL rewrites, materialized views, index changes, bucket changes, or tuning recommendations until at least one evidence source has been collected or attempted
+- Valid evidence sources: `profile get`, `profile diff`, `profile history`, `profile list --active`, `tablet`, `{{CLI}} sql "EXPLAIN ..."`, fallback SQL output, `auth status`, or an explicit user-provided profile/tablet/EXPLAIN snippet
+- If a command cannot be executed locally because `{{CLI}}` or credentials are unavailable, say that evidence could not be collected here, then provide the exact commands for the user to run. Do not stop at "install {{CLI}}"
+- If no evidence is available yet, the correct response is a short investigation plan plus the first read-only command to run, not a diagnosis
+- After collecting evidence, report findings as hypotheses with confidence and caveats, not as guaranteed root causes
+
+**Output ban when no evidence has been collected or attempted:** Do not include sections named or containing: "Likely causes", "Root cause", "Recommended fixes", "Quick wins", "Optimization plan", CREATE TABLE, CREATE MATERIALIZED VIEW, ALTER TABLE, SET session variables, or any tuning parameter recommendations. The only permitted output before evidence is: (1) investigation plan listing the commands to run, (2) the commands themselves, (3) a request for the user to provide output or con [...]
+
+### Connection-First Rule
+
+If the user reports that `{{CLI}} sql`, `{{CLI}} profile get`, or any CLI command **fails, times out, or returns an error**, treat it as a connection-layer problem first, not a query performance problem. The first command must be `{{CLI}} auth status --format json`. Do not suggest query timeout changes, session variables, DDL, or performance tuning until connectivity (mysql_status, http_status) is confirmed working. If the cluster is Cloud-managed, also check `{{CLI}} cloud cluster get - [...]
+
+---
+
+### Evidence Collection Workflows
+
+**Prefer existing profiles over re-executing SQL.** Real support workflows usually start with a query_id, a complaint about a known slow query, or output from monitoring. Re-executing expensive SQL wastes resources and may not reproduce the original conditions.
+
+#### Investigation ladder
+
+Use the most specific available path:
+
+1. User provides `query_id` → `profile get`
+2. User provides slow-vs-fast query IDs → `profile diff`
+3. User says query is running → `profile list --active`
+4. User provides SQL but no `query_id` → `profile list`, then `EXPLAIN`, then optional confirmed `sql --profile`
+5. User provides only a vague symptom → `auth status`, `profile list --active`, `profile list --limit 20`, then ask for SQL/query_id if nothing relevant appears
+6. No CLI available → use fallback SQL or provide exact command plan; do not diagnose without evidence
+
+#### When user provides a query_id
+
+1. `{{CLI}} profile get <query_id> --format json`
+2. If profile fetch fails, still treat the failed fetch as evidence. Check whether the failure looks like eviction, wrong HTTP port, or connectivity; see Failure Handling below
+3. For each table in `scanned_tables`, run `{{CLI}} tablet <db.table> --detail --format json`
+4. If `tablet` shows stale stats (zero rows on a known-populated table), note this as a caveat — metadata can lag on new or tiny tables
+5. Gather evidence, form hypotheses, recommend next checks
+
+#### When user provides SQL but no query_id
+
+1. Check recent runs first: `{{CLI}} profile list --format json` — scan for matching SQL text
+2. If a recent run exists, use that profile instead of re-executing
+3. If no recent run exists, run `{{CLI}} sql "EXPLAIN <query>" --format json`
+4. If SQL looks expensive, unfamiliar, during peak hours, or could scan/join large tables, show the EXPLAIN evidence and ask before profiled execution
+5. Only after the safety gate passes: `{{CLI}} sql "<query>" --profile --format json`
+6. Extract `query_id` from response → continue with profile workflow above
+
+#### When user gives only a vague symptom
+
+1. `{{CLI}} auth status --format json` — verify MySQL and HTTP connectivity before query diagnosis
+2. `{{CLI}} profile list --active --format json` — look for currently slow/running queries
+3. `{{CLI}} profile list --limit 20 --format json` — look for recent slow profiles
+4. If the user gives a workload keyword but not exact SQL, optionally use `{{CLI}} profile history "<keyword>" --days 7 --format json`
+5. If nothing relevant appears, ask for query_id, SQL text, dashboard/report name, time window, and environment. Do not invent a query or DDL
+
+#### When investigating a running query
+
+1. `{{CLI}} profile list --active --format json` — find active queries
+2. Wait for completion or ask user if they want to cancel/let it finish
+3. Then fetch the profile with `profile get`
+
+#### Regression comparison
+
+- `{{CLI}} profile diff <slow_qid> <fast_qid> --format json` — operator-by-operator delta
+- Focus on operators where time or rows changed significantly
+
+#### Trend analysis
+
+- `{{CLI}} profile history "<sql_pattern>" --days 7 --format json` — p50/p99 over time
+- Look for gradual growth (data volume) vs sudden jump (schema change, partition change, cluster change)
+
+#### When profile is unavailable or was not enabled
+
+1. Try `{{CLI}} profile list --limit 20 --format json` for recent profiles
+2. Try `{{CLI}} profile history "<sql_pattern>" --days 7 --format json` if a query pattern is known
+3. Use `{{CLI}} sql "EXPLAIN <query>" --format json` and `{{CLI}} tablet <db.table> --detail --format json` as fallback evidence
+4. Ask to rerun with `--profile` only after the safety gate passes
+
+---
+
+### Profile Output Fields
+
+Key fields in `profile get` response:
+
+| Section | Field | Meaning |
+|---------|-------|---------|
+| `summary` | `total_time_ms` | End-to-end latency |
+| `query_stats` | `total_scan_rows` | Total rows scanned across all fragments |
+| `query_stats` | `spilled_operators` | Count of operators that spilled to disk |
+| `query_stats` | `blocked_operators` | Count of operators blocked on upstream |
+| `operators[]` | `selectivity` | Input/output row ratio per operator |
+| `operators[]` | `spilled` | Whether this operator spilled |
+| `operators[]` | `shuffle_bytes` | Data shuffled across network |
+| `operators[]` | `join_type` | shuffle / broadcast / colocated |
+| `operators[]` | `peak_mem_bytes` | Peak memory for this operator |
+| `operators[]` | `blocked_on_upstream` | Wait exceeded 2x exec time |
+| `operators[]` | `cache_hit_pct` | Cache effectiveness |
+| `operators[]` | `runtime_filters` | Runtime filter applied |
+| `scanned_tables[]` | `tablet_skew` | Tablet size distribution ratio |
+| `time_breakdown` | `plan` | Planning phase duration |
+
+### Tablet Output Fields
+
+Key fields in `tablet` response:
+
+| Field | Meaning |
+|-------|---------|
+| `bucket_key` | Current HASH distribution column(s) |
+| `bucket_count` | Number of buckets |
+| `health.tablet_skew` | Ratio of max/avg tablet size |
+| `sort_key` | Current sort key column order |
+| `model` | DUPLICATE / UNIQUE / AGGREGATE |
+| `total_rows` | Approximate row count (may lag on new tables) |
+
+---
+
+### Diagnostic Hypotheses
+
+Each mapping below is a **hypothesis**, not a guaranteed root cause. Present as: evidence observed → likely explanation → what to check next → possible fix → when this conclusion may be wrong.
+
+#### High scan selectivity
+
+- **Evidence**: scan operator `selectivity` >> 100, or `total_scan_rows` vastly exceeds output rows
+- **Likely**: sort key does not match the query's primary filter columns — Doris scans more data than necessary
+- **Check next**: `{{CLI}} tablet <table>` → compare `sort_key` columns with the query's WHERE clause. Also check if an inverted index or BloomFilter could help
+- **Possible fix**: move the high-selectivity filter column to sort key position 1 → `schema-keys-selectivity-first`. Or add a secondary index
+- **Not always this**: high selectivity can also result from stale column stats causing bad partition pruning, or from querying across many partitions where the filter is only selective within each partition
+
+#### Tablet skew
+
+- **Evidence**: `health.tablet_skew` > 3.0, or `tablet --detail` shows one backend holding most data
+- **Likely**: bucket key has low cardinality or a skewed value distribution
+- **Check next**: `SHOW COLUMN STATS <table>` to verify cardinality of the bucket key. Check if a dominant value (e.g., NULL, default) concentrates data
+- **Possible fix**: switch to a higher-cardinality bucket key, use composite key, or switch to RANDOM (DUP tables only) → `schema-bucket-composite-for-skew`, `schema-bucket-high-cardinality-key`
+- **Not always this**: skew can also be caused by uneven partition sizes, recent data loading into a subset of partitions, or a tablet migration in progress
+
+#### Large shuffle on JOIN
+
+- **Evidence**: `shuffle_bytes` is large on a JOIN operator, or `join_type` = shuffle for a table that could use broadcast or colocation
+- **Likely**: tables are not colocated and the smaller side exceeds the broadcast threshold
+- **Check next**: check both tables' bucket keys and counts with `{{CLI}} tablet`. Check dimension table size (< 1GB usually broadcasts automatically)
+- **Possible fix**: for small dimensions (< 1GB), ensure broadcast join + runtime filter are working. For large repeated joins, align bucket keys and counts for colocation → `usecase-star-schema-join`
+- **Not always this**: shuffle is sometimes correct for large-large joins. The cost may also be dominated by the scan, not the shuffle itself
+
+#### Spill to disk
+
+- **Evidence**: `spilled` = true on aggregation, sort, or join operators; `spilled_operators` > 0
+- **Likely**: operator's intermediate state exceeds available memory — often caused by high-cardinality GROUP BY or large hash join build side
+- **Check next**: identify which operator spills and check its `peak_mem_bytes` and input row count. Check `exec_mem_limit` session variable
+- **Possible fix**: pre-aggregate with sync MV to reduce runtime cardinality (`schema-mv-sync-rollup`), increase `exec_mem_limit`, reduce `parallel_pipeline_task_num`, or use approximate aggregation (BITMAP for count distinct)
+- **Not always this**: occasional spill on very large queries is expected behavior, not a bug. Only investigate if spill correlates with unacceptable latency
+
+#### Cache miss on repeated queries
+
+- **Evidence**: `cache_hit_pct` near 0 on queries that run repeatedly
+- **Likely**: data is not cached — could be cold partition access, cache eviction, or query/partition cache disabled
+- **Check next**: is the query hitting time partitions that were just loaded? Is file cache enabled (storage-compute mode)? Check `cache_last_version_interval_second`
+- **Possible fix**: `schema-cache-file-cache`, `schema-cache-query-partition`
+- **Not always this**: the first run after data ingestion always misses. If ingestion is continuous, cache may never warm fully for the latest partition
+
+#### Planning overhead
+
+- **Evidence**: `time_breakdown.plan` is a large fraction of `total_time_ms`
+- **Likely**: too many partitions for the planner to evaluate, complex query with many joins, or Nereids optimizer overhead
+- **Check next**: count partitions with `SHOW PARTITIONS FROM <table>`. Check `time_breakdown.nereids_rewrite` and `nereids_analysis` sub-phases
+- **Possible fix**: reduce partition count (coarser granularity), add partition pruning predicates to WHERE clause, or use prepared statements for repeated queries
+- **Not always this**: complex multi-table queries naturally take longer to plan. Only a problem if plan time dominates execution time
+
+#### Blocked on upstream
+
+- **Evidence**: downstream operator has `blocked_on_upstream` flag or wait time >> exec time
+- **Likely**: the upstream operator (usually scan or shuffle) is the real bottleneck — the downstream operator is just waiting for data
+- **Check next**: identify the upstream operator and diagnose that one instead
+- **Possible fix**: fix the upstream issue (bad scan, slow shuffle, etc.) — the downstream wait will resolve automatically
+- **Not always this**: network issues or cluster resource contention can also cause temporary blocking
+
+---
+
+### Failure Handling
+
+#### Profile fetch fails
+
+- Profile may be evicted (Doris keeps profiles for a limited time, often 5-15 minutes)
+- HTTP port may be misconfigured: Cloud mode uses 8080, self-hosted Doris uses 8030. `{{CLI}} auth status` shows `http_status` and `http_probe`
+- FE may be unreachable: check the `served_by` and `fetch_attempts` fields in the error response
+- **Recovery**: if profile is evicted, try `profile history` for trend data and `profile list` for nearby recent runs. Ask user to re-run with `--profile` only after the safety gate passes. If HTTP port is wrong, guide user to fix with `{{CLI}} auth add` (re-add environment with correct `--http-port`)
+
+#### Tablet metadata lag
+
+- Newly created or recently loaded tables may show `total_rows = 0` or stale sizes in `tablet` output
+- This is normal — tablet metadata compaction and stats refresh have lag
+- **Recovery**: note the caveat in your analysis. Use the profile's `total_scan_rows` as the actual row count signal instead of tablet metadata
+
+#### Column stats unavailable
+
+- `SHOW COLUMN STATS` may return empty if ANALYZE hasn't been run
+- **Recovery**: note that cardinality estimates are unavailable. Recommend the user run `ANALYZE TABLE <table>` and revisit
+
+#### Connection fails during investigation
+
+- If `{{CLI}} auth status` shows `mysql_status: unreachable` or `http_status: unreachable`, diagnose the connection before continuing with query investigation
+- For Cloud environments: cluster may be Suspended — check with `{{CLI}} cloud cluster get`
+- For BYOC: may need SOCKS5 proxy
+
+---
+
+### Fallback SQL (When {{CLI}} is unavailable)
+
+Use these via `mysql` client or any MySQL-compatible tool:
+
+```sql
+-- Table structure
+SHOW CREATE TABLE db.table_name;
+
+-- Query plan (pre-execution, does not run the query)
+EXPLAIN <query>;
+
+-- Tablet distribution and skew
+SHOW DATA SKEW FROM db.table_name;
+
+-- Column statistics (cardinality)
+SHOW COLUMN STATS db.table_name;
+
+-- Recent queries (requires FE HTTP access)
+-- GET http://<fe_host>:<http_port>/rest/v2/manager/query/query_info?is_all_node=true
+
+-- Fetch a specific profile by query_id (requires FE HTTP access)
+-- GET http://<fe_host>:<http_port>/rest/v2/manager/query/profile/text/<query_id>?is_all_node=true
+```
+
+Limitations: no structured JSON, no `profile get` equivalent without HTTP API access, no `profile diff` or `profile history`. Analysis will be less precise — state this upfront.
diff --git a/skills/doris-best-practices/references/schema-bucket-cloud-mandatory-hash.md b/skills/doris-best-practices/references/schema-bucket-cloud-mandatory-hash.md
new file mode 100644
index 0000000..4ac34db
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-bucket-cloud-mandatory-hash.md
@@ -0,0 +1,24 @@
+---
+title: Cloud Mode Requires HASH Bucketing for MoW
+impact: HIGH
+impactDescription: "Cloud mode with UNIQUE MoW fails if RANDOM bucketing is used"
+tags: [schema, bucket, cloud, mow, hash]
+---
+
+## Cloud Mode Requires HASH Bucketing for MoW
+
+**Impact: HIGH — UNIQUE MoW tables in cloud mode must use HASH bucketing.**
+
+In Cloud mode (storage-compute separation), RANDOM bucketing is not supported for UNIQUE KEY tables with MoW enabled.
+
+```sql
+-- GOOD: Cloud MoW with HASH
+CREATE TABLE users (...)
+UNIQUE KEY(user_id)
+DISTRIBUTED BY HASH(user_id) BUCKETS 5 -- cloud MoW: calculate: data_per_partition_GB × compression / 2
+PROPERTIES (
+ "enable_unique_key_merge_on_write" = "true",
+ "replication_num" = "1" -- cloud mode
+);
+```
+
diff --git a/skills/doris-best-practices/references/schema-bucket-composite-for-skew.md b/skills/doris-best-practices/references/schema-bucket-composite-for-skew.md
new file mode 100644
index 0000000..452a291
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-bucket-composite-for-skew.md
@@ -0,0 +1,40 @@
+---
+title: Use Composite Bucket Key to Fix Data Skew
+impact: HIGH
+impactDescription: "Single skewed bucket key concentrates data; composite key distributes it evenly"
+tags: [schema, bucket, skew, composite, hotspot]
+---
+
+## Use Composite Bucket Key to Fix Data Skew
+
+**Impact: HIGH (Single skewed bucket key concentrates data; composite key distributes it evenly)**
+
+When a single column has uneven distribution (some values appear much more than others), use a composite bucket key.
+
+**Incorrect (single skewed key):**
+
+```sql
+-- BAD: site_id 54321 has 80% of traffic → bucket_4 is 80% of data
+CREATE TABLE site_access (
+ site_id INT, city_code INT, pv BIGINT
+) DUPLICATE KEY(site_id, city_code)
+DISTRIBUTED BY HASH(site_id) BUCKETS 16;
+```
+
+**Correct (composite key):**
+
+```sql
+-- GOOD: combining site_id + city_code distributes the hot site across cities
+CREATE TABLE site_access (
+ site_id INT, city_code INT, pv BIGINT
+) DUPLICATE KEY(site_id, city_code)
+DISTRIBUTED BY HASH(site_id, city_code) BUCKETS 16;
+-- Data for site_id=54321 is now spread across multiple buckets by city.
+```
+
+**When to use composite keys:**
+- One column dominates traffic (e.g., one large customer)
+- Single-column hash creates visible hot tablets
+- Detect with: `SHOW TABLETS FROM table_name` and check size distribution
+
+Reference: [Data Distribution](https://doris.apache.org/docs/table-design/data-partitioning/data-distribution)
diff --git a/skills/doris-best-practices/references/schema-bucket-hash-vs-random.md b/skills/doris-best-practices/references/schema-bucket-hash-vs-random.md
new file mode 100644
index 0000000..854feaf
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-bucket-hash-vs-random.md
@@ -0,0 +1,43 @@
+---
+title: HASH Bucket for Pruning; RANDOM Only for DUP Full-Scans
+impact: CRITICAL
+impactDescription: "Bucket key is irreversible; wrong choice prevents query pruning"
+tags: [schema, bucket, hash, random, pruning]
+---
+
+## HASH Bucket for Pruning; RANDOM Only for DUP Full-Scans
+
+**Impact: CRITICAL — Bucket key cannot be changed after table creation.**
+
+- **HASH bucket:** Filters on the bucket column prune to a single tablet → fast point queries
+- **RANDOM bucket:** Guarantees perfectly even distribution but no query pruning
+
+| Feature | HASH | RANDOM |
+|---------|------|--------|
+| Query pruning | Yes (WHERE on bucket col) | No |
+| Data distribution | Depends on column cardinality | Always even |
+| Supported models | ALL | DUPLICATE only |
+
+**30% Skew Rule:** If your chosen bucket column has >30% data skew (one value dominates), you must either use RANDOM bucketing or switch to a composite key. Detect with `SHOW TABLETS FROM table_name` and check size distribution.
+
+**Incorrect:**
+
+```sql
+-- BAD: RANDOM on a UNIQUE table (not supported)
+CREATE TABLE users (...) UNIQUE KEY(user_id)
+DISTRIBUTED BY RANDOM BUCKETS 8;
+```
+
+**Correct:**
+
+```sql
+-- GOOD: HASH on primary key for UNIQUE
+CREATE TABLE users (...) UNIQUE KEY(user_id)
+DISTRIBUTED BY HASH(user_id) BUCKETS 8; -- calculate: data_per_partition_GB × compression / 2
+
+-- GOOD: RANDOM for DUP when no filter column is obvious
+CREATE TABLE raw_logs (...) DUPLICATE KEY(log_time)
+DISTRIBUTED BY RANDOM BUCKETS 3; -- small table or full-scan workload
+```
+
+Reference: [Data Distribution](https://doris.apache.org/docs/table-design/data-partitioning/data-distribution)
diff --git a/skills/doris-best-practices/references/schema-bucket-high-cardinality-key.md b/skills/doris-best-practices/references/schema-bucket-high-cardinality-key.md
new file mode 100644
index 0000000..dc663ba
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-bucket-high-cardinality-key.md
@@ -0,0 +1,20 @@
+---
+title: Choose High-Cardinality, Low-NULL Bucket Key
+impact: HIGH
+impactDescription: "Low-cardinality bucket key causes data skew and hot tablets"
+tags: [schema, bucket, cardinality, skew]
+---
+
+## Choose High-Cardinality, Low-NULL Bucket Key
+
+**Impact: HIGH — Low-cardinality keys cause uneven data distribution.**
+
+Good bucket key properties:
+1. **High cardinality** — many distinct values (user_id, order_id)
+2. **Low NULL rate** — NULLs all hash to the same bucket
+3. **Used in WHERE/JOIN** — enables partition pruning
+
+**Bad choices:** status (3 values), gender (2 values), country (200 values for global data)
+**Good choices:** user_id, order_id, device_id, session_id
+
+Reference: [Data Distribution](https://doris.apache.org/docs/table-design/data-partitioning/data-distribution)
diff --git a/skills/doris-best-practices/references/schema-bucket-target-size.md b/skills/doris-best-practices/references/schema-bucket-target-size.md
new file mode 100644
index 0000000..c48937f
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-bucket-target-size.md
@@ -0,0 +1,35 @@
+---
+title: Target 1-10 GB Per Tablet
+impact: HIGH
+impactDescription: "Too many small tablets waste metadata; too few large tablets limit parallelism"
+tags: [schema, bucket, tablet, size, auto]
+---
+
+## Target 1-10 GB Per Tablet
+
+**Impact: HIGH — Tablet size directly affects query parallelism and metadata overhead.**
+
+Rules:
+- **Target:** 1-10 GB per tablet (compressed)
+- **Max buckets per partition:** ≤ 64
+- **Preferred:** Calculate explicit bucket count: `daily_data_GB / target_tablet_GB`
+
+```sql
+-- GOOD: Explicit count from sizing math
+-- 14 GB/day partition, target ~2 GB/tablet → 8 buckets
+DISTRIBUTED BY HASH(user_id) BUCKETS 8
+
+-- 3 GB/day partition, target ~1 GB/tablet → 4 buckets
+DISTRIBUTED BY HASH(order_id) BUCKETS 4
+
+-- 136 GB/day partition, target ~4 GB/tablet → 32 buckets
+DISTRIBUTED BY HASH(conn_id) BUCKETS 32
+```
+
+Always write a numeric bucket count. Automatic bucket sizing obscures intent and may pick suboptimal counts; if volume is unknown, use a conservative explicit fallback such as 3, 8, 16, or 32 buckets based on expected table size.
+
+**Warning signs:**
+- Tablets < 100 MB → Too many buckets, reduce count
+- Tablets > 20 GB → Too few buckets, increase count
+
+Reference: [Data Distribution](https://doris.apache.org/docs/table-design/data-partitioning/data-distribution)
diff --git a/skills/doris-best-practices/references/schema-cache-file-cache.md b/skills/doris-best-practices/references/schema-cache-file-cache.md
new file mode 100644
index 0000000..0b93813
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-cache-file-cache.md
@@ -0,0 +1,18 @@
+---
+title: File Cache Strategies for Cloud Mode
+impact: MEDIUM
+tags: [schema, cache, file-cache, cloud, storage-compute]
+---
+## File Cache Strategies (Cloud / Storage-Compute Separation)
+**Key metric: 60% of data cached locally = 95% cache hit rate.** Maintain 90%+ hit rate.
+Cache modes:
+- **LRU (default):** Evicts least recently used. Good for uniform access.
+- **TTL:** Time-based eviction. Good for time-series with clear hot/cold boundary.
+**Table-level cache control:**
+```sql
+-- Keep dimension tables cached forever
+ALTER TABLE dim_stores SET ("file_cache_ttl_seconds" = "0"); -- 0 = never evict
+-- Hot window for fact tables
+ALTER TABLE fact_orders SET ("file_cache_ttl_seconds" = "604800"); -- 7 days
+```
+**IOPS guidance:** SSD cache: ~500 IOPS/disk. HDD: ~200 IOPS/disk.
diff --git a/skills/doris-best-practices/references/schema-cache-query-partition.md b/skills/doris-best-practices/references/schema-cache-query-partition.md
new file mode 100644
index 0000000..3a8ef96
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-cache-query-partition.md
@@ -0,0 +1,17 @@
+---
+title: Query Cache and Partition Cache
+impact: MEDIUM
+tags: [schema, cache, query-cache, partition-cache]
+---
+## Query Cache and Partition Cache
+**Query cache:** Identical SQL → instant response (no computation).
+```sql
+SET enable_query_cache = true;
+```
+**Partition cache:** Only recomputes partitions with new data. Ideal for time-series dashboards where most partitions are historical and unchanged.
+```sql
+SET enable_partition_cache = true;
+```
+**When to use:**
+- Query cache: Repeated identical queries (dashboard auto-refresh)
+- Partition cache: Time-series with mostly historical data
diff --git a/skills/doris-best-practices/references/schema-ddl-gotchas.md b/skills/doris-best-practices/references/schema-ddl-gotchas.md
new file mode 100644
index 0000000..b7afd76
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-ddl-gotchas.md
@@ -0,0 +1,155 @@
+---
+title: DDL Syntax Gotchas — Common Apache Doris CREATE TABLE Mistakes
+impact: CRITICAL
+tags: [schema, ddl, gotchas, syntax, validation]
+---
+
+## DDL Syntax Gotchas
+
+**Check EVERY CREATE TABLE against these constraints before outputting DDL. These are hard errors — Apache Doris will reject the statement.**
+
+### 1. UNIQUE KEY (MoW) + PARTITION BY RANGE: partition column MUST be in key
+
+```sql
+-- FAILS:
+UNIQUE KEY(trade_id)
+PARTITION BY RANGE(trade_time) -- ERROR: trade_time not in key
+
+-- CORRECT:
+UNIQUE KEY(trade_id, trade_time)
+PARTITION BY RANGE(trade_time)
+```
+
+### 2. Key columns must be the FIRST N columns of the schema, in order
+
+```sql
+-- FAILS: key says (log_time, account_id, action) but schema has session_id at position 3
+CREATE TABLE t (log_time DATETIME, order_id BIGINT, account_id VARCHAR, session_id VARCHAR, action VARCHAR)
+DUPLICATE KEY(log_time, account_id, action) -- ERROR
+
+-- CORRECT: put key columns first, then everything else
+CREATE TABLE t (log_time DATETIME, account_id VARCHAR, action VARCHAR, order_id BIGINT, session_id VARCHAR)
+DUPLICATE KEY(log_time, account_id, action)
+```
+
+### 3. store_row_column only works on UNIQUE KEY (MoW) — NOT on AGGREGATE or DUPLICATE
+
+```sql
+-- FAILS:
+AGGREGATE KEY(user_id) PROPERTIES ("store_row_column" = "true") -- ERROR
+
+-- CORRECT: only on UNIQUE KEY with MoW
+UNIQUE KEY(user_id) PROPERTIES ("enable_unique_key_merge_on_write" = "true", "store_row_column" = "true")
+```
+
+### 4. AUTO PARTITION requires date_trunc() AND empty parens ()
+
+```sql
+-- FAILS:
+AUTO PARTITION BY RANGE(event_time) -- ERROR: must use date_trunc
+AUTO PARTITION BY RANGE(date_trunc(event_time, 'day')) -- ERROR: missing () at end
+
+-- CORRECT:
+AUTO PARTITION BY RANGE(date_trunc(event_time, 'day')) () -- note the () before DISTRIBUTED
+```
+
+The partition column must also be NOT NULL.
+
+### 5. Dynamic partition properties require explicit PARTITION BY RANGE clause
+
+```sql
+-- FAILS: has dynamic_partition properties but no PARTITION BY
+CREATE TABLE t (...) DISTRIBUTED BY HASH(id) BUCKETS 5
+PROPERTIES ("dynamic_partition.enable" = "true", "dynamic_partition.time_unit" = "DAY") -- ERROR
+
+-- CORRECT: add PARTITION BY RANGE(col) ()
+CREATE TABLE t (...) PARTITION BY RANGE(event_time) ()
+DISTRIBUTED BY HASH(id) BUCKETS 5
+PROPERTIES ("dynamic_partition.enable" = "true", "dynamic_partition.time_unit" = "DAY")
+```
+
+### 6. compaction_policy = "time_series" only for DUPLICATE tables
+
+```sql
+-- FAILS:
+UNIQUE KEY(id) PROPERTIES ("compaction_policy" = "time_series") -- ERROR on UNIQUE
+
+-- CORRECT: only on DUPLICATE
+DUPLICATE KEY(ts, id) PROPERTIES ("compaction_policy" = "time_series")
+```
+
+### 7. Async MV refresh syntax: must include method and ON SCHEDULE
+
+```sql
+-- FAILS:
+REFRESH SCHEDULE EVERY 10 MINUTES -- wrong
+REFRESH ASYNC EVERY(INTERVAL 1 DAY) -- wrong
+
+-- CORRECT:
+REFRESH AUTO ON SCHEDULE EVERY 10 MINUTE -- method + ON SCHEDULE; MINUTE not MINUTES
+```
+
+### 8. Async MV minimum refresh interval is 1 MINUTE
+
+```sql
+-- FAILS:
+REFRESH ON SCHEDULE EVERY 30 SECOND -- ERROR: SECOND not supported
+
+-- CORRECT:
+REFRESH ON SCHEDULE EVERY 1 MINUTE
+```
+
+### 9. Async MV using NOW() requires nondeterministic flag
+
+```sql
+-- FAILS:
+CREATE MATERIALIZED VIEW mv AS SELECT ... WHERE ts > NOW() - INTERVAL 7 DAY -- ERROR
+
+-- CORRECT: add property
+CREATE MATERIALIZED VIEW mv
+PROPERTIES ("enable_nondeterministic_function" = "true")
+REFRESH ON SCHEDULE EVERY 10 MINUTE
+AS SELECT ... WHERE ts > NOW() - INTERVAL 7 DAY
+```
+
+### 10. enable_unique_key_partial_update is a SESSION variable, not a table property
+
+```sql
+-- FAILS:
+PROPERTIES ("enable_unique_key_partial_update" = "true") -- not a valid table property
+
+-- CORRECT: set at write time
+SET enable_unique_key_partial_update = true;
+INSERT INTO table (key_col, partial_col) VALUES (...);
+```
+
+### 11. BOOLEAN default syntax
+
+```sql
+-- FAILS:
+is_active BOOLEAN DEFAULT TRUE -- ERROR
+is_active BOOLEAN DEFAULT "null" -- ERROR
+
+-- CORRECT:
+is_active BOOLEAN DEFAULT "true"
+is_active BOOLEAN DEFAULT "false"
+```
+
+### 12. AGGREGATE columns: DEFAULT "null" is invalid on non-string types
+
+In AGGREGATE tables, `REPLACE_IF_NOT_NULL DEFAULT "null"` works for VARCHAR but fails for INT, DATE, DECIMAL, BIGINT, etc. because `"null"` is not a valid number/date.
+
+```sql
+-- FAILS:
+vip_level INT REPLACE_IF_NOT_NULL DEFAULT "null" -- ERROR: Invalid number format: null
+last_login DATE REPLACE_IF_NOT_NULL DEFAULT "null" -- ERROR: Invalid date format
+
+-- CORRECT: omit DEFAULT entirely — NULL is already the default for REPLACE_IF_NOT_NULL
+vip_level INT REPLACE_IF_NOT_NULL
+last_login DATE REPLACE_IF_NOT_NULL
+
+-- Also CORRECT for VARCHAR (string can hold "null" literal, but prefer omitting):
+gender VARCHAR(10) REPLACE_IF_NOT_NULL
+```
+
+Reference: [Apache Doris DDL](https://doris.apache.org/docs/sql-manual/sql-statements/table-and-view/table/CREATE-TABLE)
diff --git a/skills/doris-best-practices/references/schema-index-bitmap.md b/skills/doris-best-practices/references/schema-index-bitmap.md
new file mode 100644
index 0000000..1ca4500
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-index-bitmap.md
@@ -0,0 +1,16 @@
+---
+title: Bitmap Index for Medium-Cardinality Dimensions
+impact: MEDIUM
+tags: [schema, index, bitmap, cardinality, dimension]
+---
+## Bitmap Index for Medium-Cardinality Dimensions (100-100K distinct values)
+**Impact: MEDIUM — Efficient for columns with moderate cardinality used in filters.**
+```sql
+CREATE INDEX idx_city ON table_name(city) USING BITMAP;
+```
+**Sweet spot:** 100 to 100,000 distinct values (status codes, cities, categories).
+**Restrictions:**
+- Only one bitmap index can be created at a time (sequential schema change)
+- `DROP INDEX` is also a schema change and takes significant time
+- Only on value columns, not key columns
+- Segment V2 format required
diff --git a/skills/doris-best-practices/references/schema-index-bloomfilter.md b/skills/doris-best-practices/references/schema-index-bloomfilter.md
new file mode 100644
index 0000000..1c82507
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-index-bloomfilter.md
@@ -0,0 +1,18 @@
+---
+title: BloomFilter for High-Cardinality Equality Filters
+impact: HIGH
+tags: [schema, index, bloomfilter, equality, high-cardinality]
+---
+## BloomFilter for High-Cardinality Equality Filters
+**Impact: HIGH — Skips data pages that definitely don't contain the filtered value.**
+Use for columns with ≥ 5000 distinct values, filtered with `=` or `IN`.
+```sql
+-- Add BloomFilter index
+PROPERTIES ("bloom_filter_columns" = "trace_id, session_id");
+```
+**Constraints:**
+- NOT supported on TINYINT, FLOAT, or DOUBLE columns
+- Only accelerates `=` and `IN` filters (not LIKE, not range)
+- Minimum recommended cardinality: 5000+ distinct values
+- False positive rate ~1% (configurable via bloom_filter_fpp)
+- Do not use inline `INDEX ... USING BLOOM FILTER` in generated DDL; use the table property above.
diff --git a/skills/doris-best-practices/references/schema-index-inverted.md b/skills/doris-best-practices/references/schema-index-inverted.md
new file mode 100644
index 0000000..f3e2a02
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-index-inverted.md
@@ -0,0 +1,16 @@
+---
+title: Inverted Index for Text Search and Range on Non-Key Columns
+impact: HIGH
+tags: [schema, index, inverted, text-search, range]
+---
+## Inverted Index for Text Search and Range on Non-Key Columns
+**Impact: HIGH — Enables full-text search and efficient range filters without modifying the sort key.**
+```sql
+-- Text search with parser
+INDEX idx_body(body) USING INVERTED PROPERTIES("parser" = "unicode")
+-- Equality/range on non-key column
+INDEX idx_status(status) USING INVERTED
+```
+**Parser options:** `none` (exact), `english`, `unicode` (multilingual), `chinese` (CJK).
+**Supported filter types:** `=`, `IN`, `>`, `<`, `>=`, `<=`, `MATCH_ALL`, `MATCH_ANY`, `MATCH_PHRASE`.
+Reference: [Inverted Index](https://doris.apache.org/docs/table-design/index/inverted-index)
diff --git a/skills/doris-best-practices/references/schema-index-ngram-for-like.md b/skills/doris-best-practices/references/schema-index-ngram-for-like.md
new file mode 100644
index 0000000..4890219
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-index-ngram-for-like.md
@@ -0,0 +1,14 @@
+---
+title: NGram BloomFilter for LIKE Queries
+impact: HIGH
+tags: [schema, index, ngram, like, pattern]
+---
+## NGram BloomFilter for LIKE '%pattern%' Queries
+**Impact: HIGH — LIKE '%pattern%' causes full table scan without NGram index.**
+```sql
+INDEX idx_url(url) USING NGRAM_BF PROPERTIES("gram_size" = "3", "bf_size" = "1024")
+```
+The NGram BloomFilter breaks text into 3-character grams and uses a bloom filter to skip non-matching pages.
+**When to use:** `LIKE '%keyword%'` queries on VARCHAR/STRING columns.
+**When NOT to use:** Exact match (`=`) → use regular BloomFilter. Full-text search → use Inverted Index.
+Reference: [NGram BloomFilter](https://doris.apache.org/docs/table-design/index/ngram-bloomfilter-index)
diff --git a/skills/doris-best-practices/references/schema-index-text-search.md b/skills/doris-best-practices/references/schema-index-text-search.md
new file mode 100644
index 0000000..ad93cc2
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-index-text-search.md
@@ -0,0 +1,29 @@
+---
+title: Full-Text Search with MATCH Functions
+impact: HIGH
+tags: [schema, index, text-search, match, search, bm25, inverted]
+---
+## Full-Text Search with MATCH Functions and BM25 Scoring
+**Impact: HIGH — Enables search-engine-like text queries with relevance ranking.**
+**7 MATCH operators:** MATCH_ALL, MATCH_ANY, MATCH_PHRASE, MATCH_PHRASE_PREFIX, MATCH_PHRASE_EDGE, MATCH_REGEXP, MATCH_ELEMENT_EQ.
+```sql
+-- Setup: Inverted index with parser
+INDEX idx_content(content) USING INVERTED PROPERTIES(
+ "parser" = "unicode", "support_phrase" = "true"
+)
+-- Queries (WHERE clause only):
+WHERE content MATCH_ALL 'database analytics' -- all terms
+WHERE content MATCH_ANY 'database analytics' -- any term
+WHERE content MATCH_PHRASE 'real time analytics' -- exact phrase
+```
+**SEARCH() unified DSL:** Combine operators in one function:
+```sql
+WHERE SEARCH(content, '"real time" +analytics -legacy', 'parser=unicode')
+```
+**BM25 scoring:** Rank results by relevance:
+```sql
+SELECT doc_id, score() AS relevance FROM docs
+WHERE content MATCH_ANY 'doris analytics' ORDER BY relevance DESC;
+```
+**Custom analyzers, hybrid text+vector search, and VARIANT text search** are all supported.
+Reference: [Full-Text Search](https://doris.apache.org/docs/table-design/index/inverted-index)
diff --git a/skills/doris-best-practices/references/schema-index-vector.md b/skills/doris-best-practices/references/schema-index-vector.md
new file mode 100644
index 0000000..88a37da
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-index-vector.md
@@ -0,0 +1,23 @@
+---
+title: HNSW/IVF Vector Index for ANN Search (Doris 4.0+)
+impact: HIGH
+tags: [schema, index, vector, hnsw, ivf, ann, embedding]
+---
+## HNSW/IVF Vector Index for ANN Search
+**Impact: HIGH — Enables approximate nearest neighbor search on embeddings.**
+Requires Doris 4.0+ with vector support.
+```sql
+CREATE TABLE embeddings (
+ doc_id BIGINT NOT NULL,
+ content VARCHAR(65533),
+ embedding ARRAY<FLOAT> NOT NULL
+) DUPLICATE KEY(doc_id)
+DISTRIBUTED BY HASH(doc_id) BUCKETS 5; -- calculate: data_per_partition_GB × compression / 2
+-- Add HNSW index:
+CREATE INDEX idx_vec ON embeddings(embedding) USING HNSW
+PROPERTIES("dim" = "768", "metric" = "cosine", "m" = "16", "ef_construction" = "200");
+-- Query:
+SELECT doc_id, l2_distance(embedding, [0.1, 0.2, ...]) AS dist
+FROM embeddings ORDER BY dist LIMIT 10;
+```
+**Index types:** HNSW (fast, memory-heavy), IVF_FLAT (balanced), IVF_PQ (compressed).
diff --git a/skills/doris-best-practices/references/schema-keys-avoid-float.md b/skills/doris-best-practices/references/schema-keys-avoid-float.md
new file mode 100644
index 0000000..0114551
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-keys-avoid-float.md
@@ -0,0 +1,28 @@
+---
+title: Avoid FLOAT/DOUBLE in Sort Key
+impact: HIGH
+impactDescription: "FLOAT/DOUBLE break both prefix index and ZoneMap statistics"
+tags: [schema, keys, float, double, zonemap]
+---
+
+## Avoid FLOAT/DOUBLE in Sort Key
+
+**Impact: HIGH — FLOAT/DOUBLE columns disable prefix index and ZoneMap pruning.**
+
+- FLOAT and DOUBLE **cannot participate** in the prefix index
+- They also **disable ZoneMap** statistics for that column
+- Use DECIMAL instead for numeric precision
+
+```sql
+-- BAD: FLOAT in key breaks prefix index
+DUPLICATE KEY(price, product_id) -- price is FLOAT
+
+-- GOOD: Use DECIMAL
+CREATE TABLE products (
+ product_id INT NOT NULL,
+ price DECIMAL(10,2) NOT NULL,
+ name VARCHAR(200)
+) DUPLICATE KEY(product_id, price);
+```
+
+Reference: [Prefix Index](https://doris.apache.org/docs/table-design/index/prefix-index)
diff --git a/skills/doris-best-practices/references/schema-keys-cluster-key-for-mow.md b/skills/doris-best-practices/references/schema-keys-cluster-key-for-mow.md
new file mode 100644
index 0000000..7be1bbf
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-keys-cluster-key-for-mow.md
@@ -0,0 +1,30 @@
+---
+title: Cluster Key to Decouple Sort from Primary Key
+impact: HIGH
+impactDescription: "UNIQUE tables sort by PK; Cluster Key lets you optimize scan order separately"
+tags: [schema, keys, cluster-key, mow, sort]
+---
+
+## Cluster Key to Decouple Sort from Primary Key
+
+**Impact: HIGH — UNIQUE tables sort data by primary key, but queries may filter on other columns.**
+
+For UNIQUE MoW tables, the primary key determines dedup but the **Cluster Key** determines physical sort order:
+
+```sql
+CREATE TABLE users (
+ user_id BIGINT NOT NULL,
+ region VARCHAR(20),
+ name VARCHAR(100)
+) UNIQUE KEY(user_id)
+DISTRIBUTED BY HASH(user_id) BUCKETS 5 -- calculate: data_per_partition_GB × compression / 2
+PROPERTIES (
+ "enable_unique_key_merge_on_write" = "true",
+ "cluster_key" = "region, user_id"
+);
+-- Dedup by user_id, but data sorted by region first (better for region-filtered queries)
+```
+
+**When to use:** When your most common WHERE clause filters on columns that are NOT your primary key.
+
+Reference: [Cluster Key](https://doris.apache.org/docs/table-design/index/prefix-index#cluster-key)
diff --git a/skills/doris-best-practices/references/schema-keys-fixed-length-types.md b/skills/doris-best-practices/references/schema-keys-fixed-length-types.md
new file mode 100644
index 0000000..d09fe89
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-keys-fixed-length-types.md
@@ -0,0 +1,26 @@
+---
+title: Fixed-Length Types Before VARCHAR in Key
+impact: HIGH
+impactDescription: "VARCHAR in sort key terminates the prefix index early"
+tags: [schema, keys, sort, varchar, fixed-length]
+---
+
+## Fixed-Length Types (INT/DATE) Before VARCHAR in Key
+
+**Impact: HIGH — A VARCHAR column terminates the prefix index.**
+
+Prefix index rules:
+- Max 3 columns OR 36 bytes (whichever comes first)
+- **VARCHAR terminates the index** — only the first 20 bytes of the VARCHAR are included, and no more columns after it
+
+```sql
+-- GOOD: Fixed-length types first
+DUPLICATE KEY(user_id, event_date, event_type)
+-- INT(4B) DATE(4B) VARCHAR(→ terminates)
+
+-- BAD: VARCHAR first wastes the prefix index
+DUPLICATE KEY(event_type, user_id, event_date)
+-- VARCHAR(→ terminates after 20B, user_id and event_date NOT in index)
+```
+
+Reference: [Prefix Index](https://doris.apache.org/docs/table-design/index/prefix-index)
diff --git a/skills/doris-best-practices/references/schema-keys-prefix-index-limits.md b/skills/doris-best-practices/references/schema-keys-prefix-index-limits.md
new file mode 100644
index 0000000..98fe1cb
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-keys-prefix-index-limits.md
@@ -0,0 +1,39 @@
+---
+title: Prefix Index — 36 Bytes, VARCHAR Stops It
+impact: CRITICAL
+impactDescription: "Only the first 36 bytes of the sort key form the prefix index; VARCHAR terminates it"
+tags: [schema, keys, prefix-index, limits, varchar]
+---
+
+## Prefix Index — 36 Bytes, VARCHAR Stops It
+
+**Impact: CRITICAL — Columns beyond the limit get no prefix index benefit.**
+
+| Rule | Detail |
+|------|--------|
+| Max bytes | 36 |
+| VARCHAR | **Terminates the index** — only first 20 bytes included, no columns after |
+| FLOAT/DOUBLE | **Cannot be in prefix index** — breaks ZoneMap too |
+
+**CRITICAL: Once a VARCHAR column appears, it is the LAST column in the prefix index.** All columns after the first VARCHAR are NEVER indexed, regardless of remaining byte budget.
+
+```sql
+-- GOOD: Fixed-length first, VARCHAR last
+DUPLICATE KEY(event_time, user_id, country)
+-- 8 (DATETIME) + 8 (BIGINT) + up to 20 (VARCHAR, STOPS) = 36B
+-- All 3 columns indexed ✓
+
+-- BAD: VARCHAR first — only 1 column indexed
+DUPLICATE KEY(session_id, event_time, country)
+-- VARCHAR takes up to 20B AND STOPS → event_time, country never indexed ✗
+
+-- GOOD for time-series with string dimensions
+DUPLICATE KEY(event_time, country, platform, connection_id)
+-- 8B (DATETIME) + country VARCHAR STOPS at 20B
+-- event_time + country in prefix index. platform, connection_id are NOT,
+-- but they benefit from ZoneMap on sorted data + use inverted indexes.
+```
+
+**Design rule:** Place fixed-length columns (DATETIME, INT, BIGINT) before VARCHAR. Put the most useful VARCHAR as the first VARCHAR column — it will be the last indexed column. Use inverted indexes or bloom filters for columns after the first VARCHAR.
+
+Reference: [Prefix Index](https://doris.apache.org/docs/table-design/index/prefix-index)
diff --git a/skills/doris-best-practices/references/schema-keys-selectivity-first.md b/skills/doris-best-practices/references/schema-keys-selectivity-first.md
new file mode 100644
index 0000000..bb8b1a0
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-keys-selectivity-first.md
@@ -0,0 +1,54 @@
+---
+title: High-Selectivity Columns First in Sort Key
+impact: CRITICAL
+impactDescription: "Sort key order determines prefix index effectiveness and ZoneMap pruning"
+tags: [schema, keys, sort, selectivity, prefix-index]
+---
+
+## High-Selectivity Columns First in Sort Key
+
+**Impact: CRITICAL — Sort key order determines query performance through prefix index.**
+
+Place columns in this order:
+1. Most frequently filtered columns first
+2. Higher selectivity (more distinct values) before lower
+3. Equality filters before range filters
+
+```sql
+-- GOOD: user_id (high selectivity, common filter) first
+DUPLICATE KEY(user_id, event_time, event_type)
+
+-- BAD: event_type (low selectivity) first
+DUPLICATE KEY(event_type, event_time, user_id)
+```
+
+The first 3 columns (or first 36 bytes) form the **prefix index**, which is the primary lookup structure in Doris.
+
+**CRITICAL CONSTRAINT: Key columns must be the FIRST N columns of the schema, in the SAME order.**
+
+This is a hard Doris constraint — DDL will fail if violated. When writing CREATE TABLE:
+1. Decide which columns go in the key
+2. Put those columns FIRST in the column list, in the same order as the key
+3. Put all non-key columns AFTER the key columns
+
+```sql
+-- BAD: key says (log_time, account_id, action) but schema has session_id between account_id and action
+CREATE TABLE t (
+ log_time DATETIME, -- key col 1 ✓
+ order_id BIGINT, -- NOT a key col, but appears before key col 2 ✗
+ account_id VARCHAR(32),-- key col 2, but at schema position 3 ✗
+ session_id VARCHAR(32),-- NOT a key col, but appears before key col 3 ✗
+ action VARCHAR(50) -- key col 3, but at schema position 5 ✗
+) DUPLICATE KEY(log_time, account_id, action) -- ERROR
+
+-- GOOD: key columns first, then everything else
+CREATE TABLE t (
+ log_time DATETIME, -- key col 1 ✓
+ account_id VARCHAR(32),-- key col 2 ✓
+ action VARCHAR(50), -- key col 3 ✓
+ order_id BIGINT, -- non-key, after all key cols ✓
+ session_id VARCHAR(32) -- non-key, after all key cols ✓
+) DUPLICATE KEY(log_time, account_id, action) -- OK
+```
+
+Reference: [Sort Key](https://doris.apache.org/docs/table-design/index/prefix-index)
diff --git a/skills/doris-best-practices/references/schema-model-avoid-agg-for-updates.md b/skills/doris-best-practices/references/schema-model-avoid-agg-for-updates.md
new file mode 100644
index 0000000..6bb854a
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-model-avoid-agg-for-updates.md
@@ -0,0 +1,44 @@
+---
+title: AGGREGATE Model Cannot UPDATE or DELETE
+impact: CRITICAL
+impactDescription: "Choosing AGG for updatable data is irreversible and blocks CDC pipelines"
+tags: [schema, model, aggregate, update, delete]
+---
+
+## AGGREGATE Model Cannot UPDATE or DELETE
+
+**Impact: CRITICAL — AGG tables do not support UPDATE or DELETE statements.**
+
+The AGGREGATE model auto-merges rows with the same key using aggregation functions (SUM, MAX, REPLACE, etc). This means:
+- No `UPDATE` statement support
+- No `DELETE` statement support
+- No CDC pipeline compatibility (cannot reflect deletes)
+
+**Exception: REPLACE_IF_NOT_NULL** — The one case where AGG can do partial updates. Columns with `REPLACE_IF_NOT_NULL` will only update when the incoming value is non-NULL. This allows partial column updates in AGG model, but still no DELETE.
+
+**Incorrect:**
+
+```sql
+-- BAD: AGG for data that needs updates
+CREATE TABLE orders (
+ order_id BIGINT AGGREGATE KEY,
+ status VARCHAR(20) REPLACE,
+ amount DECIMAL(12,2) REPLACE
+) AGGREGATE KEY(order_id);
+-- Cannot DELETE cancelled orders!
+```
+
+**Correct:**
+
+```sql
+-- GOOD: UNIQUE MoW for updatable data
+CREATE TABLE orders (
+ order_id BIGINT NOT NULL,
+ status VARCHAR(20),
+ amount DECIMAL(12,2)
+) UNIQUE KEY(order_id)
+DISTRIBUTED BY HASH(order_id) BUCKETS 5 -- calculate: data_per_partition_GB × compression / 2
+PROPERTIES ("enable_unique_key_merge_on_write" = "true");
+```
+
+Reference: [Aggregate Model](https://doris.apache.org/docs/table-design/data-model/aggregate)
diff --git a/skills/doris-best-practices/references/schema-model-choose-for-workload.md b/skills/doris-best-practices/references/schema-model-choose-for-workload.md
new file mode 100644
index 0000000..2191455
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-model-choose-for-workload.md
@@ -0,0 +1,45 @@
+---
+title: Choose Data Model for Workload
+impact: CRITICAL
+impactDescription: "Data model is irreversible after table creation"
+tags: [schema, model, duplicate, unique, aggregate]
+---
+
+## Choose Data Model for Workload
+
+**Impact: CRITICAL — Data model cannot be changed after table creation.**
+
+| Model | Use When | Key Trait |
+|-------|----------|-----------|
+| DUPLICATE | Append-only data (logs, events, clicks) | Fastest scan, keeps all rows |
+| UNIQUE (MoW) | Rows are updated/deleted (CDC, user data) | Dedup on primary key, supports DELETE |
+| AGGREGATE | Pre-aggregated metrics only (counters, sums) | Auto-aggregates on ingest |
+
+**Decision rule:** If you need UPDATE or DELETE → UNIQUE MoW. If append-only → DUPLICATE. Only use AGGREGATE when you will *never* query raw rows.
+
+**Incorrect:**
+
+```sql
+-- BAD: Using AGGREGATE for a table that needs updates
+CREATE TABLE users (
+ user_id BIGINT AGGREGATE KEY,
+ name VARCHAR(100) REPLACE,
+ email VARCHAR(200) REPLACE
+) AGGREGATE KEY(user_id);
+-- Cannot DELETE rows. Cannot run UPDATE statements.
+```
+
+**Correct:**
+
+```sql
+-- GOOD: UNIQUE MoW for updatable data
+CREATE TABLE users (
+ user_id BIGINT NOT NULL,
+ name VARCHAR(100),
+ email VARCHAR(200)
+) UNIQUE KEY(user_id)
+DISTRIBUTED BY HASH(user_id) BUCKETS 5 -- calculate: data_per_partition_GB × compression / 2
+PROPERTIES ("enable_unique_key_merge_on_write" = "true");
+```
+
+Reference: [Data Model Overview](https://doris.apache.org/docs/table-design/data-model/overview)
diff --git a/skills/doris-best-practices/references/schema-model-prefer-mow.md b/skills/doris-best-practices/references/schema-model-prefer-mow.md
new file mode 100644
index 0000000..a2133e7
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-model-prefer-mow.md
@@ -0,0 +1,29 @@
+---
+title: Always Use Merge-on-Write for UNIQUE Tables
+impact: CRITICAL
+impactDescription: "MoR (default before 2.1) causes slow reads due to merge-sort at query time"
+tags: [schema, model, unique, mow, mor]
+---
+
+## Always Use Merge-on-Write (MoW) for UNIQUE Tables
+
+**Impact: CRITICAL — MoR requires runtime merge-sort, making reads 2-10× slower.**
+
+Since Doris 2.1, MoW is the default. For older versions, always set explicitly:
+
+```sql
+PROPERTIES ("enable_unique_key_merge_on_write" = "true")
+```
+
+**MoW vs MoR:**
+
+| Feature | MoW | MoR |
+|---------|-----|-----|
+| Read speed | Fast (pre-merged) | Slow (merge at query) |
+| Write speed | Slightly slower | Faster writes |
+| DELETE support | Yes | Yes |
+| Partial update | Yes | Limited |
+
+**Always use MoW** unless you have a write-heavy workload with minimal reads.
+
+Reference: [Unique Key Model](https://doris.apache.org/docs/table-design/data-model/unique)
diff --git a/skills/doris-best-practices/references/schema-model-sequence-col-for-cdc.md b/skills/doris-best-practices/references/schema-model-sequence-col-for-cdc.md
new file mode 100644
index 0000000..6728484
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-model-sequence-col-for-cdc.md
@@ -0,0 +1,32 @@
+---
+title: Sequence Column for Out-of-Order CDC
+impact: HIGH
+impactDescription: "Without sequence_col, late-arriving CDC events can overwrite newer data"
+tags: [schema, model, cdc, sequence, unique]
+---
+
+## Sequence Column for Out-of-Order CDC
+
+**Impact: HIGH — CDC events may arrive out of order; older records can overwrite newer ones.**
+
+When using UNIQUE KEY with CDC (Flink CDC, Debezium, Canal), set a sequence column to guarantee ordering:
+
+```sql
+CREATE TABLE users (
+ user_id BIGINT NOT NULL,
+ update_time DATETIME NOT NULL,
+ name VARCHAR(100),
+ email VARCHAR(200)
+) UNIQUE KEY(user_id)
+DISTRIBUTED BY HASH(user_id) BUCKETS 5 -- CDC table: calculate: data_per_partition_GB × compression / 2
+PROPERTIES (
+ "enable_unique_key_merge_on_write" = "true",
+ "function_column.sequence_col" = "update_time"
+);
+```
+
+**How it works:** When two rows have the same primary key, Doris keeps the one with the *higher* sequence column value, regardless of arrival order.
+
+**When to use:** Any CDC pipeline where events may arrive out of order (network delays, reprocessing, multiple sources).
+
+Reference: [Sequence Column](https://doris.apache.org/docs/table-design/data-model/unique#sequence-column)
diff --git a/skills/doris-best-practices/references/schema-mv-async-join.md b/skills/doris-best-practices/references/schema-mv-async-join.md
new file mode 100644
index 0000000..5ac27ef
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-mv-async-join.md
@@ -0,0 +1,61 @@
+---
+title: Async MV for Multi-Table JOIN Acceleration
+impact: HIGH
+tags: [schema, mv, async, join, multi-table]
+---
+## Async MV for Multi-Table JOIN Acceleration
+**Impact: HIGH — Pre-computes JOINs so queries read a flat table instead of joining at runtime.**
+
+### Correct Syntax
+```sql
+CREATE MATERIALIZED VIEW mv_order_details
+BUILD DEFERRED REFRESH AUTO ON SCHEDULE EVERY 1 HOUR
+DISTRIBUTED BY HASH(order_id) BUCKETS 4
+PROPERTIES ('compression' = 'zstd')
+AS SELECT o.order_id, o.amount, p.product_name, c.customer_name
+FROM orders o
+JOIN products p ON o.product_id = p.product_id
+JOIN customers c ON o.customer_id = c.customer_id;
+```
+After creation, trigger first refresh: `REFRESH MATERIALIZED VIEW mv_order_details AUTO;`
+
+### State Requirements for Query Rewrite
+All must be true (check via `SELECT * FROM mv_infos('database'='db_name')`):
+- `State = NORMAL` (not SCHEMA_CHANGE)
+- `RefreshState = SUCCESS` (at least one successful refresh)
+- `SyncWithBaseTables = 1` (or `grace_period` allows staleness)
+
+### CRITICAL: Predicate Compensation Limitation
+Auto-rewrite **works** when WHERE predicates are on **fact table columns**:
+```sql
+-- This WILL auto-rewrite to use the async MV:
+SELECT ... FROM fact JOIN dim ON ... WHERE fact.country = 'US' GROUP BY ...
+```
+Auto-rewrite **fails** when WHERE predicates are on **dimension table columns** (joined columns):
+```sql
+-- This will NOT auto-rewrite ("Predicate compensate fail"):
+SELECT ... FROM fact JOIN dim ON ... WHERE dim.name = 'Partner_X' GROUP BY ...
+```
+The optimizer cannot trace column lineage from `dim.name` in the query to the MV's aliased column.
+
+### Practical Usage: Query Async MVs Directly
+Since most BI queries filter on dimension columns (partner name, account name), the reliable approach is to **query the async MV directly** as a pre-built mart table:
+```sql
+-- Instead of 3-table JOIN:
+SELECT date, SUM(revenue) FROM fact f
+ JOIN dim_conn c ON f.conn_id = c.id
+ JOIN dim_sf sf ON c.acct_id = sf.acct_id
+ WHERE sf.partner_name = 'X' GROUP BY 1;
+
+-- Query the async MV directly (8,000x less I/O):
+SELECT date, SUM(revenue) FROM mv_fact_partner
+ WHERE partner_name = 'X' GROUP BY 1;
+```
+
+### Session Variables
+| Variable | Default | Purpose |
+|---|---|---|
+| `enable_materialized_view_rewrite` | `true` | Master switch for async MV rewrite |
+| `materialized_view_rewrite_success_candidate_num` | `3` | Max rewrite candidates for CBO |
+
+Reference: [Async Materialized View](https://doris.apache.org/docs/query-acceleration/materialized-view/async-materialized-view)
diff --git a/skills/doris-best-practices/references/schema-mv-async-limits.md b/skills/doris-best-practices/references/schema-mv-async-limits.md
new file mode 100644
index 0000000..eeb102f
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-mv-async-limits.md
@@ -0,0 +1,31 @@
+---
+title: Async MV Operational Limits
+impact: HIGH
+tags: [schema, mv, async, limits, capacity]
+---
+## Async MV Operational Limits
+| Limit | Value |
+|-------|-------|
+| Max rows per MV | ~50 million |
+| Max JOINs | 2 |
+| Max partitions | 30 |
+| Max concurrent refreshes | 3 |
+| Cluster resource cap | 40% |
+| ON COMMIT limit | ≤ 5 updates/hour |
+**Capacity estimation:** ~20-30 active async MVs on a 3-node cluster.
+**Layered design pattern:** Build MVs on top of other MVs (Layer 1: base aggregations, Layer 2: cross-table joins).
+**partition_sync_limit:** Focus refresh on recent data only:
+```sql
+CREATE MATERIALIZED VIEW mv_recent
+BUILD DEFERRED REFRESH AUTO ON SCHEDULE EVERY 1 HOUR
+PROPERTIES ("partition_sync_limit" = "7")
+AS SELECT ... FROM orders ...;
+```
+
+### Diagnostic Checklist (when async MV rewrite not working)
+1. `SELECT Name, State, RefreshState, SyncWithBaseTables FROM mv_infos('database'='db_name')` — State must be NORMAL, RefreshState SUCCESS
+2. `EXPLAIN <query>` — check `MaterializedViewRewriteFail` section at bottom
+3. `"View struct info is invalid"` — benign; read the error AFTER it for the real cause
+4. `"Predicate compensate fail"` — WHERE on dimension column; query the MV directly instead
+5. `"The graph logic between query and view is not consistent"` — JOIN type or table order mismatch
+6. `SHOW VARIABLES LIKE '%materialized_view%'` — verify `enable_materialized_view_rewrite = true`
diff --git a/skills/doris-best-practices/references/schema-mv-sync-rollup.md b/skills/doris-best-practices/references/schema-mv-sync-rollup.md
new file mode 100644
index 0000000..a8f33c8
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-mv-sync-rollup.md
@@ -0,0 +1,36 @@
+---
+title: Sync MV (Rollup) for Single-Table Aggregation
+impact: HIGH
+tags: [schema, mv, sync, rollup, aggregation]
+---
+## Sync MV (Rollup) for Single-Table Aggregation
+**Impact: HIGH — Pre-aggregates data; optimizer rewrites queries automatically. Zero lag.**
+
+### Restrictions
+- **Single-table only.** JOINs, HAVING, LIMIT, LATERAL VIEW are NOT supported. For JOINs use async MVs.
+- **NOT supported on UNIQUE KEY tables.** Use async MVs instead.
+- **Column aliases must be unique** and must NOT collide with any base table column name. Use prefixes (e.g., `mv_`, `pa_`).
+
+### CRITICAL: Use Raw Columns, Not Derived Expressions
+Doris does **structural matching** — `date_trunc(event_time, 'day')` in the MV will NOT match `DATE_FORMAT(event_time, '%Y-%m-%d')` in a query. They are different functions.
+
+Store **raw columns** in the MV GROUP BY. The optimizer can then derive ANY function on top:
+```sql
+-- GOOD: raw event_time — matches DATE_FORMAT, HOUR, WEEKDAY, date_trunc, etc.
+CREATE MATERIALIZED VIEW mv_daily_sales AS
+SELECT event_time AS mv_time, store_id AS mv_store, country AS mv_country,
+ SUM(amount) AS mv_amount, SUM(qty) AS mv_qty
+FROM orders GROUP BY mv_time, mv_store, mv_country;
+
+-- BAD: date_trunc — only matches queries that also use date_trunc, nothing else
+CREATE MATERIALIZED VIEW mv_daily_sales AS
+SELECT date_trunc(event_time, 'day') AS mv_date, ...
+```
+
+### Matching Rules
+The optimizer auto-rewrites when:
+1. Query's GROUP BY columns are a **subset** of the MV's GROUP BY columns
+2. Query's aggregate functions (SUM, COUNT, MIN, MAX) exist in the MV
+3. Query expressions are **derived from** MV columns (e.g., `DATE_FORMAT(mv_time, ...)` derives from `event_time`)
+
+Reference: [Sync Materialized View](https://doris.apache.org/docs/query-acceleration/materialized-view/sync-materialized-view)
diff --git a/skills/doris-best-practices/references/schema-partition-auto-on-demand.md b/skills/doris-best-practices/references/schema-partition-auto-on-demand.md
new file mode 100644
index 0000000..2227ea3
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-partition-auto-on-demand.md
@@ -0,0 +1,34 @@
+---
+title: AUTO PARTITION for Sporadic Data
+impact: HIGH
+impactDescription: "Creates partitions on-demand when data arrives, avoiding empty partitions"
+tags: [schema, partition, auto, on-demand, sporadic]
+---
+
+## AUTO PARTITION for Sporadic Data
+
+**Impact: HIGH — Avoids creating empty partitions for dates with no data.**
+
+Use AUTO PARTITION when data arrival is unpredictable (not every day/month has data):
+
+```sql
+CREATE TABLE sparse_events (
+ event_time DATETIME NOT NULL,
+ user_id BIGINT,
+ event_type VARCHAR(50)
+) DUPLICATE KEY(event_time, user_id)
+AUTO PARTITION BY RANGE(date_trunc(event_time, 'month'))
+()
+DISTRIBUTED BY HASH(user_id) BUCKETS 5; -- sporadic events: calculate: data_per_partition_GB × compression / 2
+```
+
+**When to use AUTO vs DYNAMIC:**
+- **Auto:** Preferred for most cases — creates partitions on data arrival, no empty partitions
+- **Dynamic:** Only when you need TTL-based auto-deletion of old partitions AND data arrives predictably every period
+
+**WARNING: Do NOT combine AUTO PARTITION with dynamic_partition properties.** They are redundant and conflict:
+- AUTO PARTITION already creates partitions on arrival — `dynamic_partition.end` (pre-creation) is unnecessary
+- `dynamic_partition.prefix` conflicts with AUTO's naming scheme
+- If you need old partition cleanup, use a scheduled `ALTER TABLE ... DROP PARTITION` job instead
+
+Reference: [Auto Partition](https://doris.apache.org/docs/table-design/data-partitioning/auto-partitioning)
diff --git a/skills/doris-best-practices/references/schema-partition-dynamic-ttl.md b/skills/doris-best-practices/references/schema-partition-dynamic-ttl.md
new file mode 100644
index 0000000..d7d89ac
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-partition-dynamic-ttl.md
@@ -0,0 +1,32 @@
+---
+title: Dynamic Partition for Automated Data Lifecycle
+impact: HIGH
+impactDescription: "Without dynamic partition, old data accumulates and manual cleanup is error-prone"
+tags: [schema, partition, dynamic, ttl, lifecycle]
+---
+
+## Dynamic Partition for Automated Data Lifecycle
+
+**Impact: HIGH — Automates partition creation and TTL-based data cleanup.**
+
+Key properties:
+- `time_unit`: DAY, WEEK, MONTH
+- `start`: Negative number = how many units to keep (TTL). E.g., `"-7"` keeps 7 days.
+- `end`: Positive number = how many future partitions to pre-create
+- Bucket count belongs in `DISTRIBUTED BY ... BUCKETS N`, not in dynamic partition properties.
+
+```sql
+PROPERTIES (
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "DAY",
+ "dynamic_partition.start" = "-7",
+ "dynamic_partition.end" = "3",
+ "dynamic_partition.prefix" = "p"
+);
+```
+
+**Warnings:**
+- Do not use dynamic partition for tables with < 20 million rows. Creates wasteful empty partitions.
+- **Do NOT combine with AUTO PARTITION.** If using `AUTO PARTITION BY RANGE`, do not add `dynamic_partition.*` properties — they are redundant and conflict. AUTO PARTITION handles partition creation on data arrival. For TTL cleanup, use a scheduled `ALTER TABLE ... DROP PARTITION` job instead.
+
+Reference: [Dynamic Partition](https://doris.apache.org/docs/table-design/data-partitioning/dynamic-partitioning)
diff --git a/skills/doris-best-practices/references/schema-partition-range-for-timeseries.md b/skills/doris-best-practices/references/schema-partition-range-for-timeseries.md
new file mode 100644
index 0000000..21808d3
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-partition-range-for-timeseries.md
@@ -0,0 +1,64 @@
+---
+title: RANGE Partition on Time Column for Time-Series
+impact: CRITICAL
+impactDescription: "Partition key is irreversible; wrong choice prevents pruning and TTL"
+tags: [schema, partition, range, time-series, pruning]
+---
+
+## RANGE Partition on Time Column for Time-Series
+
+**Impact: CRITICAL — Partition key cannot be changed after table creation.**
+
+For any table with a time column (logs, events, metrics), use RANGE partition:
+
+```sql
+PARTITION BY RANGE(event_time) ()
+```
+
+Combined with dynamic partitioning, this enables:
+- **Partition pruning:** `WHERE event_time > '2025-01-01'` only scans relevant partitions
+- **TTL:** Old partitions are auto-dropped
+- **Parallel loading:** Each partition can be loaded independently
+
+**Incorrect:**
+
+```sql
+-- BAD: No partition on a time-series table
+CREATE TABLE events (
+ event_time DATETIME, user_id BIGINT, data STRING
+) DUPLICATE KEY(event_time)
+DISTRIBUTED BY HASH(user_id) BUCKETS 10; -- calculate: data_per_partition_GB × compression / 2
+-- Every query scans ALL data, no TTL possible
+```
+
+**Correct:**
+
+```sql
+CREATE TABLE events (
+ event_time DATETIME NOT NULL, user_id BIGINT, data STRING
+) DUPLICATE KEY(event_time, user_id)
+PARTITION BY RANGE(event_time) ()
+DISTRIBUTED BY HASH(user_id) BUCKETS 10 -- time-series: calculate: data_per_partition_GB × compression / 2
+PROPERTIES (
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "DAY",
+ "dynamic_partition.start" = "-30",
+ "dynamic_partition.end" = "3",
+ "dynamic_partition.prefix" = "p"
+);
+```
+
+**CRITICAL CONSTRAINT for UNIQUE KEY (MoW) tables:**
+The partition column MUST be included in the UNIQUE KEY. Otherwise CREATE TABLE fails with "Merge-on-Write table's partition column must be KEY column".
+
+```sql
+-- BAD: partition column not in key
+UNIQUE KEY(trade_id)
+PARTITION BY RANGE(trade_time) -- ERROR: trade_time not in key
+
+-- GOOD: include partition column in key
+UNIQUE KEY(trade_id, trade_time)
+PARTITION BY RANGE(trade_time) -- OK
+```
+
+Reference: [Range Partition](https://doris.apache.org/docs/table-design/data-partitioning/range-partitioning)
diff --git a/skills/doris-best-practices/references/schema-partition-skip-for-small.md b/skills/doris-best-practices/references/schema-partition-skip-for-small.md
new file mode 100644
index 0000000..20ee3a9
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-partition-skip-for-small.md
@@ -0,0 +1,29 @@
+---
+title: Skip Partitioning for Small Tables
+impact: HIGH
+impactDescription: "Partitioning tables under 1GB adds overhead without benefit"
+tags: [schema, partition, small, dimension]
+---
+
+## Skip Partitioning for Small Tables (< 1 GB)
+
+**Impact: HIGH — Unnecessary partitioning on small tables wastes metadata and hurts performance.**
+
+If a table is under 1 GB total, do not partition it. Just use bucketing:
+
+```sql
+-- GOOD: Small dimension table, no partition needed
+CREATE TABLE dim_countries (
+ country_code VARCHAR(3) NOT NULL,
+ country_name VARCHAR(100),
+ region VARCHAR(50)
+) DUPLICATE KEY(country_code)
+DISTRIBUTED BY HASH(country_code) BUCKETS 3;
+```
+
+**Rule of thumb:**
+- < 1 GB → No partition
+- 1-100 GB → Consider partition if time-series
+- > 100 GB → Always partition
+
+Reference: [Data Partitioning](https://doris.apache.org/docs/table-design/data-partitioning/data-distribution)
diff --git a/skills/doris-best-practices/references/schema-props-cloud-forced.md b/skills/doris-best-practices/references/schema-props-cloud-forced.md
new file mode 100644
index 0000000..69d9d24
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-props-cloud-forced.md
@@ -0,0 +1,13 @@
+---
+title: Properties Cloud Mode Forces
+impact: HIGH
+tags: [schema, properties, cloud, replication]
+---
+## Properties Cloud Mode Forces
+In Cloud mode (storage-compute separation):
+- `replication_num` is forced to `1` (data stored in object storage)
+- HASH bucketing required for UNIQUE MoW
+- File cache controls performance (see `schema-cache-file-cache`)
+```sql
+PROPERTIES ("replication_num" = "1");
+```
diff --git a/skills/doris-best-practices/references/schema-props-compression.md b/skills/doris-best-practices/references/schema-props-compression.md
new file mode 100644
index 0000000..da8ea36
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-props-compression.md
@@ -0,0 +1,16 @@
+---
+title: LZ4 Default vs ZSTD for Logs/Cold Data
+impact: MEDIUM
+tags: [schema, properties, compression, lz4, zstd]
+---
+## LZ4 Default vs ZSTD for Logs/Cold Data
+| Algorithm | Speed | Ratio | Use When |
+|-----------|-------|-------|----------|
+| LZ4 | Fastest | Lower | Default, hot data, low-latency |
+| ZSTD | Slower | 2-3× better | Logs, cold data, archival |
+```sql
+-- For log/event tables with high redundancy:
+PROPERTIES ("compression" = "zstd");
+-- For hot analytical tables (default):
+PROPERTIES ("compression" = "lz4");
+```
diff --git a/skills/doris-best-practices/references/schema-types-bitmap-count-distinct.md b/skills/doris-best-practices/references/schema-types-bitmap-count-distinct.md
new file mode 100644
index 0000000..f4eb995
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-types-bitmap-count-distinct.md
@@ -0,0 +1,26 @@
+---
+title: BITMAP_UNION for Exact Count-Distinct
+impact: HIGH
+tags: [schema, types, bitmap, count-distinct, funnel, retention]
+---
+## BITMAP_UNION for Exact Count-Distinct and Funnel Analysis
+**Impact: HIGH — COUNT(DISTINCT col) on high-cardinality columns is extremely expensive. BITMAP gives exact results with O(1) merge.**
+```sql
+CREATE TABLE daily_uv (
+ dt DATE NOT NULL,
+ page VARCHAR(200) NOT NULL,
+ uv BITMAP BITMAP_UNION
+) AGGREGATE KEY(dt, page)
+DISTRIBUTED BY HASH(page) BUCKETS 5; -- calculate: data_per_partition_GB × compression / 2
+-- Insert with to_bitmap():
+INSERT INTO daily_uv SELECT '2025-01-01', '/home', to_bitmap(user_id) FROM events;
+-- Query exact UV:
+SELECT dt, bitmap_count(uv) AS unique_visitors FROM daily_uv GROUP BY dt;
+```
+**Funnel / Retention with bitmap_intersect():**
+```sql
+SELECT bitmap_count(bitmap_intersect(uv)) AS retained_users
+FROM daily_uv WHERE dt IN ('2025-01-01', '2025-01-02');
+```
+**Orthogonal analysis:** Use INTERSECT_COUNT for multi-dimensional user segmentation.
+Reference: [BITMAP](https://doris.apache.org/docs/sql-manual/data-types/aggregate/BITMAP)
diff --git a/skills/doris-best-practices/references/schema-types-doris-specifics.md b/skills/doris-best-practices/references/schema-types-doris-specifics.md
new file mode 100644
index 0000000..2f977bc
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-types-doris-specifics.md
@@ -0,0 +1,19 @@
+---
+title: Doris-Specific Type Gotchas
+impact: HIGH
+tags: [schema, types, datetime, string, varchar, char]
+---
+## Doris-Specific Type Gotchas
+**DATETIME precision:** Default DATETIME is seconds. Use `DATETIME(3)` for milliseconds, `DATETIME(6)` for microseconds.
+**STRING vs VARCHAR:** STRING cannot be used as key, partition, or bucket column. Use VARCHAR for keyed columns.
+**VARCHAR(65533) has identical performance to VARCHAR(255)** — Doris uses variable-length storage, so there's no penalty for a larger max. When unsure, use VARCHAR(65533).
+**CHAR vs VARCHAR:** CHAR pads with spaces to fixed length. Only use CHAR for truly fixed-width codes (country_code CHAR(3)). Otherwise, prefer VARCHAR.
+```sql
+-- GOOD: Appropriate types
+CREATE TABLE t (
+ ts DATETIME(3), -- millisecond precision
+ country CHAR(3), -- fixed 3-char code
+ name VARCHAR(65533), -- variable, no perf penalty
+ big_text STRING -- only for non-key, non-partitioned columns
+);
+```
diff --git a/skills/doris-best-practices/references/schema-types-native-vs-string.md b/skills/doris-best-practices/references/schema-types-native-vs-string.md
new file mode 100644
index 0000000..955f005
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-types-native-vs-string.md
@@ -0,0 +1,16 @@
+---
+title: Use Native Types, Not STRING for Everything
+impact: HIGH
+impactDescription: "STRING disables ZoneMap, prefix index, and prevents use as key/bucket column"
+tags: [schema, types, string, native, performance]
+---
+## Use Native Types, Not STRING for Everything
+**Impact: HIGH — STRING columns cannot be used as partition, bucket, or sort key columns.**
+Always use the most specific type: INT for numbers, DATE/DATETIME for timestamps, DECIMAL for money.
+```sql
+-- BAD
+CREATE TABLE t (id STRING, ts STRING, amount STRING);
+-- GOOD
+CREATE TABLE t (id BIGINT, ts DATETIME(3), amount DECIMAL(12,2));
+```
+Reference: [Data Types](https://doris.apache.org/docs/sql-manual/data-types/overview)
diff --git a/skills/doris-best-practices/references/schema-types-variant-json.md b/skills/doris-best-practices/references/schema-types-variant-json.md
new file mode 100644
index 0000000..e3af26d
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-types-variant-json.md
@@ -0,0 +1,31 @@
+---
+title: VARIANT Type for Semi-Structured JSON Data
+impact: HIGH
+tags: [schema, types, variant, json, semi-structured, schema-template]
+---
+## VARIANT Type for Semi-Structured JSON Data
+**Impact: HIGH — VARIANT provides columnar storage for JSON with automatic type inference.**
+Use VARIANT instead of JSON or STRING for semi-structured data:
+```sql
+CREATE TABLE events (
+ event_time DATETIME NOT NULL,
+ event_id BIGINT NOT NULL,
+ data VARIANT
+) DUPLICATE KEY(event_time, event_id)
+DISTRIBUTED BY HASH(event_id) BUCKETS 5; -- calculate: data_per_partition_GB × compression / 2
+-- Query nested fields directly:
+SELECT data['user']['name'], data['action'] FROM events;
+```
+**Schema Template:** Pre-define expected fields for better columnar storage:
+```sql
+ALTER TABLE events SET ("variant_schema_template" = '{"user.name": "STRING", "action": "STRING", "amount": "DOUBLE"}');
+```
+**Inverted Index on VARIANT fields:**
+```sql
+ALTER TABLE events ADD INDEX idx_action(CAST(data['action'] AS VARCHAR)) USING INVERTED;
+```
+**MATCH search on VARIANT text fields:**
+```sql
+SELECT * FROM events WHERE CAST(data['message'] AS VARCHAR) MATCH 'error timeout';
+```
+Reference: [VARIANT Type](https://doris.apache.org/docs/sql-manual/data-types/semi-structured/VARIANT)
diff --git a/skills/doris-best-practices/references/schema-types-zonemap-limitations.md b/skills/doris-best-practices/references/schema-types-zonemap-limitations.md
new file mode 100644
index 0000000..84ebc59
--- /dev/null
+++ b/skills/doris-best-practices/references/schema-types-zonemap-limitations.md
@@ -0,0 +1,19 @@
+---
+title: JSON/ARRAY/Complex Types Disable ZoneMap
+impact: HIGH
+tags: [schema, types, zonemap, json, array, complex]
+---
+## JSON/ARRAY/Complex Types Disable ZoneMap
+**Impact: HIGH — ZoneMap statistics are not generated for JSON, ARRAY, MAP, STRUCT, or STRING columns.**
+ZoneMap stores min/max values per data page, enabling skip scanning. Complex types disable this optimization.
+**Workaround:** Extract frequently filtered fields into dedicated columns with native types.
+```sql
+-- BAD: Filtering on JSON field — no ZoneMap, full scan
+SELECT * FROM events WHERE payload->'$.status' = 'error';
+-- GOOD: Extract to a native column
+CREATE TABLE events (
+ ..., status VARCHAR(20), payload JSON,
+ INDEX idx_status(status) USING INVERTED
+);
+SELECT * FROM events WHERE status = 'error';
+```
diff --git a/skills/doris-best-practices/references/sizing-be-cloud.md b/skills/doris-best-practices/references/sizing-be-cloud.md
new file mode 100644
index 0000000..1814672
--- /dev/null
+++ b/skills/doris-best-practices/references/sizing-be-cloud.md
@@ -0,0 +1,10 @@
+---
+title: BE Sizing — Cloud / Storage-Compute Separation
+tags: [sizing, be, cloud, storage-compute]
+---
+## BE Sizing — Cloud / Storage-Compute Separation
+In cloud mode, data is in object storage (S3/GCS). BEs only cache hot data locally.
+**CPU:** Same as integrated — determines query parallelism.
+**Memory:** 32-128 GB. Used for query execution, not data storage.
+**Local disk:** SSD for file cache. Size based on hot data ratio (60% cached = 95% hit rate).
+**Elasticity:** Can scale BE nodes independently from data volume. Add BEs for more compute, not more storage.
diff --git a/skills/doris-best-practices/references/sizing-be-integrated.md b/skills/doris-best-practices/references/sizing-be-integrated.md
new file mode 100644
index 0000000..8345bef
--- /dev/null
+++ b/skills/doris-best-practices/references/sizing-be-integrated.md
@@ -0,0 +1,10 @@
+---
+title: BE Sizing — Integrated Storage Mode
+tags: [sizing, be, backend, integrated]
+---
+## BE Sizing — Integrated Storage Mode
+In integrated mode, each BE stores data locally on disk.
+**CPU:** 16-64 cores per BE. More cores = more concurrent query/scan threads.
+**Memory:** 64-256 GB per BE. Rule of thumb: 4 GB RAM per 1 TB stored data.
+**Disk:** SSD recommended. 1-10 TB per BE. Use RAID or multiple disks for throughput.
+**Node count:** Start with 3 nodes for HA. Scale horizontally for more throughput.
diff --git a/skills/doris-best-practices/references/sizing-fe.md b/skills/doris-best-practices/references/sizing-fe.md
new file mode 100644
index 0000000..0e7a2e8
--- /dev/null
+++ b/skills/doris-best-practices/references/sizing-fe.md
@@ -0,0 +1,11 @@
+---
+title: FE Node Sizing
+tags: [sizing, fe, frontend, metadata]
+---
+## FE Node Sizing
+| Cluster Size | FE Nodes | Memory | CPU |
+|-------------|----------|--------|-----|
+| Small (< 10 BE) | 1 Leader + 2 Follower | 16 GB | 8 cores |
+| Medium (10-50 BE) | 1 Leader + 2 Follower | 32 GB | 16 cores |
+| Large (50+ BE) | 1 Leader + 4 Follower | 64 GB | 32 cores |
+FE nodes store metadata (table schemas, partitions, tablets) in memory. Scale FE memory with table count and partition count rather than data volume.
diff --git a/skills/doris-best-practices/references/sizing-storage-formula.md b/skills/doris-best-practices/references/sizing-storage-formula.md
new file mode 100644
index 0000000..200d4d8
--- /dev/null
+++ b/skills/doris-best-practices/references/sizing-storage-formula.md
@@ -0,0 +1,16 @@
+---
+title: Storage Calculation Formula
+tags: [sizing, storage, formula, compression]
+---
+## Storage Calculation Formula
+```
+Raw data × compression ratio × replication_num = Required storage
+```
+**Compression ratios (typical):**
+| Data Type | LZ4 Ratio | ZSTD Ratio |
+|-----------|-----------|------------|
+| Structured (numeric) | 3-5× | 5-8× |
+| Logs/text | 5-10× | 10-20× |
+| JSON/semi-structured | 3-8× | 5-12× |
+**Example:** 1 TB/day raw logs × 30 days retention × ZSTD (10× compression) × 1 replica = 3 TB storage
+**Include overhead:** Add 20-30% for metadata, compaction temp space, and safety margin.
diff --git a/skills/doris-best-practices/references/start-self-hosted.md b/skills/doris-best-practices/references/start-self-hosted.md
new file mode 100644
index 0000000..8b922f0
--- /dev/null
+++ b/skills/doris-best-practices/references/start-self-hosted.md
@@ -0,0 +1,41 @@
+---
+title: Getting Started — Self-Hosted / BYOC
+tags: [start, self-hosted, byoc, on-prem, setup]
+---
+## Getting Started — Self-Hosted / BYOC / On-Prem
+
+### Where to Find Connection Details
+
+**MySQL host + port**: the FE node address and MySQL protocol port. Default port is `9030`. Check `fe.conf` → `mysql_service_port` or ask your DBA.
+
+**HTTP port**: FE HTTP port for profile fetching and REST API. Self-hosted Apache Doris uses `8030` by default; {{CLOUD_PRODUCT_NAME}} uses `8080`. Check `fe.conf` → `http_port`. If unsure, `{{CLI}} auth add` probes 8080/8030/8040 and shows suggestions.
+
+**User + password**: default root user is `root` with empty password on fresh installs. Production clusters should have a password set via `SET PASSWORD`.
+
+### Prerequisites
+- FE nodes: Java 8+ runtime
+- BE nodes: Linux with sufficient disk, memory
+- Network: FE and BE nodes must be able to communicate
+
+### Deployment Steps
+1. Deploy FE nodes (1 Leader + 2 Followers for HA)
+2. Deploy BE nodes (3+ for production)
+3. Register BEs with FE: `ALTER SYSTEM ADD BACKEND "<be_host>:9050";`
+4. Create database and tables
+
+### Connect with VeloCLI (Preferred)
+```bash
+{{CLI}} auth add local --host <fe_host> --port 9030 --http-port 8030 --user root --password "$DORIS_PASSWORD"
+{{CLI}} use local
+{{CLI}} auth status --format json
+```
+
+### Connect via MySQL Client
+```bash
+mysql -h <fe_host> -P 9030 -u root -p"$DORIS_PASSWORD"
+```
+### Self-Hosted Properties
+```sql
+PROPERTIES ("replication_num" = "3"); -- 3 replicas for HA
+```
+Reference: [Apache Doris Installation](https://doris.apache.org/docs/install/cluster-deployment/standard-deployment)
diff --git a/skills/doris-best-practices/references/usecase-cdc-sync.md b/skills/doris-best-practices/references/usecase-cdc-sync.md
new file mode 100644
index 0000000..8ac4bb0
--- /dev/null
+++ b/skills/doris-best-practices/references/usecase-cdc-sync.md
@@ -0,0 +1,36 @@
+---
+title: "Use Case: CDC / Operational Data Sync"
+impact: CRITICAL
+tags: [usecase, cdc, sync, mysql, flink, unique, mow, upsert]
+---
+## Use Case: CDC / Operational Data Sync
+For replicating operational databases (MySQL, PostgreSQL) where rows are frequently updated or deleted.
+### Template
+```sql
+CREATE TABLE users_sync (
+ user_id BIGINT NOT NULL,
+ tenant_id INT NOT NULL,
+ update_time DATETIME NOT NULL,
+ name VARCHAR(100),
+ email VARCHAR(200),
+ status TINYINT
+) ENGINE=OLAP
+UNIQUE KEY(user_id)
+DISTRIBUTED BY HASH(user_id) BUCKETS 5 -- CDC table: calculate: data_per_partition_GB × compression / 2
+PROPERTIES (
+ "enable_unique_key_merge_on_write" = "true",
+ "function_column.sequence_col" = "update_time",
+ "light_schema_change" = "true"
+);
+```
+### Why This Design
+| Decision | Choice | Why |
+|----------|--------|-----|
+| **Model** | UNIQUE (MoW) | Enables UPDATE/DELETE. MoW gives fast reads without merge-sort. |
+| **Sequence col** | `update_time` | Guarantees out-of-order CDC events don't overwrite newer data. |
+| **Bucket** | HASH on `user_id` (= primary key) | Point lookups on PK are pruned to one tablet. |
+| **Partition** | None | CDC tables are often small-to-medium; partition only if > 100GB. |
+### Customization Points
+- **Large CDC tables (> 100GB):** Add `PARTITION BY RANGE(update_time) ()` with dynamic partition. **CRITICAL:** The partition column must be in the UNIQUE KEY — change to `UNIQUE KEY(user_id, update_time)`
+- **Composite primary key:** Use `UNIQUE KEY(tenant_id, user_id)` for multi-tenant data
+- **Partial column updates:** Do NOT set `enable_unique_key_partial_update` as a table property — it is a session variable. Set it at write time: `SET enable_unique_key_partial_update = true;` before your INSERT/Stream Load.
diff --git a/skills/doris-best-practices/references/usecase-dashboard-metrics.md b/skills/doris-best-practices/references/usecase-dashboard-metrics.md
new file mode 100644
index 0000000..624ad27
--- /dev/null
+++ b/skills/doris-best-practices/references/usecase-dashboard-metrics.md
@@ -0,0 +1,33 @@
+---
+title: "Use Case: Dashboard / Pre-Aggregated Metrics"
+impact: CRITICAL
+tags: [usecase, dashboard, metrics, aggregate, rollup, bi]
+---
+## Use Case: Dashboard / Pre-Aggregated Metrics
+For pre-computing dashboard numbers: daily sales, ad click totals, user activity summaries.
+### Template
+```sql
+CREATE TABLE daily_sales_metrics (
+ dt DATE NOT NULL, store_id INT NOT NULL,
+ total_revenue DECIMAL(15,2) SUM DEFAULT "0",
+ max_transaction DECIMAL(15,2) MAX DEFAULT "0",
+ order_count BIGINT SUM DEFAULT "0",
+ unique_buyers BITMAP BITMAP_UNION
+) ENGINE=OLAP AGGREGATE KEY(dt, store_id)
+PARTITION BY RANGE(dt) ()
+DISTRIBUTED BY HASH(store_id) BUCKETS 5 -- pre-aggregated metrics: calculate: data_per_partition_GB × compression / 2
+PROPERTIES ("dynamic_partition.enable"="true","dynamic_partition.time_unit"="MONTH",
+ "dynamic_partition.start"="-12","dynamic_partition.end"="1","dynamic_partition.prefix"="p");
+```
+### Why This Design
+| Decision | Choice | Why |
+|----------|--------|-----|
+| **Model** | AGGREGATE | Values auto-aggregate (SUM, MAX, BITMAP_UNION) on ingestion. |
+| **BITMAP** | `BITMAP_UNION` for unique_buyers | Exact count-distinct without storing raw user IDs. |
+### Anti-Pattern
+```sql
+-- BAD: Using DUPLICATE for dashboard data you'll always aggregate
+CREATE TABLE daily_sales_metrics (dt DATE, store_id INT, revenue DECIMAL(15,2))
+DUPLICATE KEY(dt, store_id);
+-- Every query must re-aggregate billions of rows at query time.
+```
diff --git a/skills/doris-best-practices/references/usecase-dimension-lookup.md b/skills/doris-best-practices/references/usecase-dimension-lookup.md
new file mode 100644
index 0000000..8599377
--- /dev/null
+++ b/skills/doris-best-practices/references/usecase-dimension-lookup.md
@@ -0,0 +1,25 @@
+---
+title: "Use Case: Small Dimension / Lookup Table"
+impact: HIGH
+tags: [usecase, dimension, lookup, small-table, broadcast, random]
+---
+## Use Case: Small Dimension / Lookup Table
+For small reference tables (< 1 GB): products, stores, countries, config values.
+### Template
+```sql
+CREATE TABLE stores (
+ store_id INT NOT NULL, region VARCHAR(20) NOT NULL,
+ city VARCHAR(50), manager_name VARCHAR(100)
+) ENGINE=OLAP DUPLICATE KEY(store_id)
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ("replication_num" = "3"); -- Use "1" for cloud mode
+```
+### Why This Design
+| Decision | Choice | Why |
+|----------|--------|-----|
+| **Model** | DUPLICATE | Dimension data is reference data — rarely updated. |
+| **Partition** | None | Table is tiny (< 1 GB). Partitioning adds overhead, not benefit. |
+| **Bucket** | RANDOM, 3 buckets | Perfectly even distribution for small data. |
+### Customization Points
+- **Updated dimensions:** Switch to `UNIQUE KEY(store_id)` with MoW if dimension data changes
+- **Colocation JOINs:** Switch to `HASH(store_id)` and match the fact table's bucket count
diff --git a/skills/doris-best-practices/references/usecase-log-event.md b/skills/doris-best-practices/references/usecase-log-event.md
new file mode 100644
index 0000000..336aa43
--- /dev/null
+++ b/skills/doris-best-practices/references/usecase-log-event.md
@@ -0,0 +1,41 @@
+---
+title: "Use Case: Time-Series Logs & Events"
+impact: CRITICAL
+tags: [usecase, logs, events, clickstream, time-series, duplicate]
+---
+## Use Case: Time-Series Logs & Events
+For immutable, append-only data: application logs, click events, IoT sensor readings, audit trails.
+### Template
+```sql
+CREATE TABLE app_events (
+ event_time DATETIME NOT NULL,
+ app_id INT NOT NULL,
+ event_type VARCHAR(50) NOT NULL,
+ user_id BIGINT,
+ payload STRING
+) ENGINE=OLAP
+DUPLICATE KEY(event_time, app_id, event_type)
+PARTITION BY RANGE(event_time) ()
+DISTRIBUTED BY HASH(app_id) BUCKETS 10 -- high-volume logs: calculate: data_per_partition_GB × compression / 2
+PROPERTIES (
+ "dynamic_partition.enable" = "true",
+ "dynamic_partition.time_unit" = "DAY",
+ "dynamic_partition.start" = "-7",
+ "dynamic_partition.end" = "3",
+ "dynamic_partition.prefix" = "p",
+ "compression" = "zstd"
+);
+```
+### Why This Design
+| Decision | Choice | Why |
+|----------|--------|-----|
+| **Model** | DUPLICATE | Logs are immutable — no updates/deletes needed. Fastest scan speed. |
+| **Partition** | Dynamic RANGE by DAY | Auto-creates daily partitions, auto-drops old ones (TTL via `start`). |
+| **Bucket** | HASH on `app_id` | High cardinality, commonly filtered in WHERE. |
+| **Sort Key** | `(event_time, app_id, event_type)` | Time-first enables range scans; app_id enables prefix pruning. |
+| **Compression** | ZSTD | Log data has high redundancy — ZSTD compresses 2-3x better than LZ4. |
+### Customization Points
+- **Retention:** Change `dynamic_partition.start` (e.g., `"-30"` for 30 days)
+- **Partition granularity:** `"HOUR"` for very high volume or `"MONTH"` for low volume
+- **Bucket key:** If you mostly filter by `user_id`, use `HASH(user_id)` instead
+- **Add text search:** Add `INDEX idx_payload(payload) USING INVERTED` for log message search
diff --git a/skills/doris-best-practices/references/usecase-observability.md b/skills/doris-best-practices/references/usecase-observability.md
new file mode 100644
index 0000000..26fec6d
--- /dev/null
+++ b/skills/doris-best-practices/references/usecase-observability.md
@@ -0,0 +1,54 @@
+---
+title: "Use Case: Observability Platform"
+impact: CRITICAL
+tags: [usecase, observability, logs, traces, metrics, opentelemetry, grafana]
+---
+## Use Case: Observability Platform
+For building a full observability stack: logs + metrics + traces.
+### Table 1: Logs (DUPLICATE)
+```sql
+CREATE TABLE otel_logs (
+ log_time DATETIME NOT NULL, service_name VARCHAR(100) NOT NULL,
+ severity VARCHAR(10) NOT NULL, trace_id VARCHAR(32), span_id VARCHAR(16),
+ body TEXT, resource_attributes STRING,
+ INDEX idx_body(body) USING INVERTED PROPERTIES("parser" = "unicode")
+) ENGINE=OLAP DUPLICATE KEY(log_time, service_name, severity)
+PARTITION BY RANGE(log_time) ()
+DISTRIBUTED BY HASH(service_name) BUCKETS 10 -- high-volume logs: ~100 GB/day × 0.05 ZSTD / 2 GB ≈ 2.5, rounded up for growth
+PROPERTIES ("dynamic_partition.enable"="true","dynamic_partition.time_unit"="DAY",
+ "dynamic_partition.start"="-7","dynamic_partition.end"="3",
+ "dynamic_partition.prefix"="p","compression"="zstd",
+ "bloom_filter_columns"="trace_id");
+```
+### Table 2: Traces (DUPLICATE)
+```sql
+CREATE TABLE otel_traces (
+ start_time DATETIME NOT NULL, service_name VARCHAR(100) NOT NULL,
+ trace_id VARCHAR(32) NOT NULL, span_id VARCHAR(16) NOT NULL,
+ parent_span_id VARCHAR(16), operation_name VARCHAR(200),
+ duration_ms BIGINT, status_code TINYINT
+) ENGINE=OLAP DUPLICATE KEY(start_time, service_name, trace_id)
+PARTITION BY RANGE(start_time) ()
+DISTRIBUTED BY HASH(service_name) BUCKETS 3 -- traces: lower volume than logs, ~small per partition
+PROPERTIES ("dynamic_partition.enable"="true","dynamic_partition.time_unit"="DAY",
+ "dynamic_partition.start"="-7","dynamic_partition.end"="3",
+ "dynamic_partition.prefix"="p","compression"="zstd",
+ "bloom_filter_columns"="trace_id");
+```
+### Table 3: Metrics (AGGREGATE)
+```sql
+CREATE TABLE otel_metrics (
+ metric_time DATETIME NOT NULL, service_name VARCHAR(100) NOT NULL,
+ metric_name VARCHAR(200) NOT NULL,
+ value DOUBLE SUM DEFAULT "0", count BIGINT SUM DEFAULT "0"
+) ENGINE=OLAP AGGREGATE KEY(metric_time, service_name, metric_name)
+PARTITION BY RANGE(metric_time) ()
+DISTRIBUTED BY HASH(service_name) BUCKETS 3 -- pre-aggregated metrics: small per partition
+PROPERTIES ("dynamic_partition.enable"="true","dynamic_partition.time_unit"="DAY",
+ "dynamic_partition.start"="-30","dynamic_partition.end"="3","dynamic_partition.prefix"="p");
+```
+### Design Principles
+- **Shared bucket key:** All tables use `HASH(service_name)` for potential colocation JOINs
+- **Short retention for logs/traces:** 7-day TTL; **Longer for metrics:** 30-day TTL
+- **Text search on logs:** Inverted index on `body` with unicode parser
+- **Trace correlation:** BloomFilter on `trace_id` for fast trace lookup
diff --git a/skills/doris-best-practices/references/usecase-point-query.md b/skills/doris-best-practices/references/usecase-point-query.md
new file mode 100644
index 0000000..1f00b29
--- /dev/null
+++ b/skills/doris-best-practices/references/usecase-point-query.md
@@ -0,0 +1,35 @@
+---
+title: "Use Case: User-Facing Point Query Analytics"
+impact: CRITICAL
+tags: [usecase, point-query, user-facing, low-latency, api, high-concurrency]
+---
+## Use Case: User-Facing Point Query Analytics
+For serving real-time analytics through APIs with low-latency, high-concurrency requirements.
+### Template
+```sql
+CREATE TABLE user_profiles (
+ user_id BIGINT NOT NULL, tenant_id INT NOT NULL,
+ name VARCHAR(100), email VARCHAR(200), last_login DATETIME,
+ total_orders INT, lifetime_value DECIMAL(12,2)
+) ENGINE=OLAP UNIQUE KEY(user_id)
+DISTRIBUTED BY HASH(user_id) BUCKETS 5 -- user profiles: calculate: data_per_partition_GB × compression / 2
+PROPERTIES (
+ "enable_unique_key_merge_on_write" = "true",
+ "store_row_column" = "true",
+ "light_schema_change" = "true",
+ "bloom_filter_columns" = "tenant_id"
+);
+```
+### Why This Design
+| Decision | Choice | Why |
+|----------|--------|-----|
+| **Model** | UNIQUE MoW | Fast reads (no merge-sort at query time) |
+| **store_row_column** | `true` | Enables row-store mode for point queries — reads full row from one I/O |
+| **BloomFilter** | On `tenant_id` | Skips tablets that don't contain the tenant |
+### Optimized Point Query Pattern
+```sql
+SELECT * FROM user_profiles WHERE user_id = 12345;
+-- With Prepared Statement for high concurrency:
+PREPARE stmt FROM 'SELECT * FROM user_profiles WHERE user_id = ?';
+SET @uid = 12345; EXECUTE stmt USING @uid;
+```
diff --git a/skills/doris-best-practices/references/usecase-star-schema-join.md b/skills/doris-best-practices/references/usecase-star-schema-join.md
new file mode 100644
index 0000000..4c4f240
--- /dev/null
+++ b/skills/doris-best-practices/references/usecase-star-schema-join.md
@@ -0,0 +1,60 @@
+---
+title: "Use Case: Star Schema / JOIN-Heavy Analytics"
+impact: CRITICAL
+tags: [usecase, star-schema, join, colocation, fact-table, dimension]
+---
+## Use Case: Star Schema / JOIN-Heavy Analytics
+Uses colocation to ensure JOINs execute locally without network shuffle.
+### Fact Table
+```sql
+CREATE TABLE fact_orders (
+ order_date DATE NOT NULL, order_id BIGINT NOT NULL,
+ user_id INT NOT NULL, store_id INT NOT NULL, product_id INT NOT NULL,
+ amount DECIMAL(12,2), quantity INT
+) ENGINE=OLAP DUPLICATE KEY(order_date, order_id)
+PARTITION BY RANGE(order_date) ()
+DISTRIBUTED BY HASH(store_id) BUCKETS 16
+PROPERTIES ("dynamic_partition.enable"="true","dynamic_partition.time_unit"="MONTH",
+ "dynamic_partition.start"="-24","dynamic_partition.end"="1",
+ "dynamic_partition.prefix"="p",
+ "colocate_with"="group_orders");
+```
+### Dimension Table (colocated)
+```sql
+CREATE TABLE dim_stores (
+ store_id INT NOT NULL, region VARCHAR(20), city VARCHAR(50), manager_name VARCHAR(100)
+) ENGINE=OLAP DUPLICATE KEY(store_id)
+DISTRIBUTED BY HASH(store_id) BUCKETS 16
+PROPERTIES ("colocate_with" = "group_orders");
+```
+### Colocation Rules — ALL must match or CREATE TABLE fails:
+1. Same `colocate_with` group name
+2. Same bucket key column(s) and same column types
+3. **Same bucket count** — if the first table in a group uses BUCKETS 8, ALL other tables in the group MUST also use BUCKETS 8. Mismatched counts cause: `Colocate tables must have same bucket num`
+4. Same replication_num
+
+**Common mistake:** Sizing each table's buckets independently, then adding them to the same colocation group. Always decide the bucket count for the group FIRST, then use that count for ALL tables in the group.
+
+### Alternative: Broadcast JOIN + Runtime Filter (for small dims)
+When dimension tables are small (<1 GB), **skip colocation** — Doris auto-broadcasts them:
+```sql
+-- Fact table: HASH on the JOIN key for runtime filter bucket pruning
+CREATE TABLE fact_events (
+ event_time DATETIME NOT NULL, country VARCHAR(10), conn_id VARCHAR(255),
+ adv_spend DECIMAL(12,2), views BIGINT
+) DUPLICATE KEY(event_time, country)
+AUTO PARTITION BY RANGE(date_trunc(event_time, 'day'))()
+DISTRIBUTED BY HASH(conn_id) BUCKETS 8;
+
+-- Small dim: no colocation needed, 1 bucket
+CREATE TABLE dim_partners (
+ conn_id VARCHAR(255) NOT NULL, partner_name VARCHAR(255), region VARCHAR(50)
+) DUPLICATE KEY(conn_id)
+DISTRIBUTED BY HASH(conn_id) BUCKETS 1;
+```
+**How it works:** When a query filters `WHERE dim.partner_name = 'X'`, Doris:
+1. Scans the small dim table, applies the filter
+2. Generates a **runtime filter** from the matching `conn_id` values
+3. Pushes the runtime filter to the fact table scan → **HASH bucket pruning** (scans 1 bucket instead of all)
+
+This gives colocation-like performance without the operational overhead of matching bucket counts across tables. Use colocation only when dims are large (>1 GB) or when both sides of the JOIN are large fact tables.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org