optimize-model

Files (14)

• SKILL.md
• clickhouse-playbook.md · references/
• dependency-resolution.md · references/
• research-sources.md · references/
• _load_env.sh · scripts/
• benchmark_query.py · scripts/
• compare_query_hash.py · scripts/
• introspect_tables.py · scripts/
• prepare_model_analysis.py · scripts/
• resolve_model_sql.py · scripts/
• run_benchmark.sh · scripts/
• run_candidate_benchmark.sh · scripts/
• run_hash_check.sh · scripts/
• run_prepare.sh · scripts/

SKILL.md (first lines)

# Optimize CBT Transformation Model (No Model Edits)

Analyze transformation model `$ARGUMENTS` and report high-impact ClickHouse performance improvements.

Never modify the model file. Only report findings and recommendations.

## Defaults To Confirm First

Before any analysis, collect and confirm (allow overrides):

Credentials and endpoint/database overrides are auto-loaded from the project `.env` by the run wrappers
(`EXTERNAL_USER`/`EXTERNAL_PASS` for the raw cluster, `TRANSFORM_USER`/`TRANSFORM_PASS` for the refined cluster).
Already-exported shell variables take precedence over `.env`. Note these are the PRODUCTION cluster credentials,
separate from the local docker `CLICKHOUSE_USERNAME`/`CLICKHOUSE_PASSWORD`.

- External models cluster:
  - `endpoint`: `http://chendpoint-clickhouse-raw.analytics.production.ethpandaops:8123`
  - `default_database`: `default`
  - `username`: `EXTERNAL_USER` (from `.env`, default empty)
  - `password`: `EXTERNAL_PASS` (from `.env`, default empty)
- Transformation models cluster:
  - `endpoint`: `http://chendpoint-clickhouse-refined.analytics.production.ethpandaops:8123`
  - `default_database`: `mainnet`
  - `username`: `TRANSFORM_USER` (from `.env`, default empty)
  - `password`: `TRANSFORM_PASS` (from `.env`, default empty)
  - `access_to_external_cluster`: `cluster('{raw}', database.table_name)`
  - Cluster substitution rule: when template uses `cluster(...)`, external dependencies are resolved to `<table>_local` automatically.

If any value is missing or ambiguous, ask follow-up questions before running benchmarks.

### Credential preflight (gate before any cluster query)

The raw and refined clusters require authentication, so empty credentials will fail with
`AUTHENTICATION_FAILED` rather than run anonymously. Before the first `run_prepare.sh`/benchmark call,
resolve the credentials exactly as the wrappers do and check whether they are populated:

```bash
bash -c '. .claude/skills/optimize-model/scripts/_load_env.sh; \
  echo "EXTERNAL=${EXTERNAL_USER:+set} TRANSFORM=${TRANSFORM_USER:+set}"'
```

If the credentials for a cluster you are about to query are empty, STOP and elicit a choice from the
user with `AskUserQuestion` (do not silently run and let it fail):

- **Add credentials** — ask the user to populate `EXTERNAL_USER`/`EXTERNAL_PASS` and/or
  `TRANSFORM_USER`/`TRANSFORM_PASS` in `.env` (or export them), then re-resolve and continue.
- **Continue unauthenticated** — proceed anyway. Only valid if the target cluster permits anonymous
  access; otherwise expect the run to fail with `AUTHENTICATION_FAILED`.

Only run the benchmark scripts once the user has chosen. Skip this gate (no prompt) when the needed
credentials are already present.

## Inputs

- Target transformation model (example): `models/transformations/fct_block.sql`
- Optional benchmark window overrides.
- Optional period mode:
  - `sane` (default): use frontmatter interval with protective caps to avoid timeouts.
  - `daring`: expand beyond sane range for deeper testing.

## Workflow

1. Resolve target model path
- Accept full path or model name.
- Normalize to `models/transformations/<name>.sql`.
- Stop with a clear error if not found.

2. Run one-shot preparation via wrapper (no flag discovery)
- Run:

```bash
SESSION_ID="$(date +%s)-$$"
PREP_OUTPUT="/tmp/optimize-model.${SESSION_ID}.prepare.json"

SESSION_ID="$SESSION_ID" PREP_OUTPUT="$PREP_OUTPUT" \
  .claude/skills/optimize-model/scripts/run_prepare.sh "$ARGUMENTS"
```

- This command generates session-isolated `/tmp` artifacts. Artifact names also
  include the bounds window, so multi-window runs (one prep per window) never
  overwrite each other's rendered SQL or bench/hash outputs; downstream
  wrappers resolve paths via the prep manifest (`paths`, `artifact_prefix`).
- Use `summary`, `period`, `unresolved_fragments`, and `summary.introspection_ok` from `$PREP_OUTPUT`.
- Confirm `period.bounds_start`/`period.bounds_end` are sane before benchmarking.
- If unresolved fragments remain, ask targeted follow-up questions and rerun.

3. Introspect dependency schemas
- This is already executed by prep wrapper.
- Always use `SHOW CREATE TABLE` for dependencies.
- If a dependency is `Distributed`, also inspect its target table (often `_local`) and reason from local engine/order/partition keys.
- Treat `_local` table `ORDER BY` as the effective primary key when `PRIMARY KEY` is not explicitly defined.
- For any `cluster(...)` table access, always target `_local` tables instead of Distributed table names.

4. Early version-aware research pass (recommended before benchmarking)
- Read ClickHouse versions from prep output first:
  - `jq '.clickhouse_versions' "$PREP_OUTPUT"`
- Use web search (explicitly) to review docs and changelog/release notes for the detected version before designing candidate rewrites.
- Prefer doing this before baseline/candidate benchmarking; if a quick baseline run already happened, continue and complete research before final recommendations.
- Use official ClickHouse docs first, then release/changelog pages relevant to the detected version and newer versions.
- Include version-aware search terms such as:
  - `clickhouse <major.minor> release notes`
  - `clickhouse <major.minor> query optimization`
  - `clickhouse replacingmergetree final performance`
- Use references:
  - `references/clickhouse-playbook.md`
  - `references/dependency-resolution.md`
  - `references/research-sources.md`

5. Benchmark rendered query (baseline, fast sample first)
- Run via wrapper:

```bash
SESSION_ID="$SESSION_ID" \
  .claude/skills/optimize-model/scripts/run_benchmark.sh "$PREP_OUTPUT"
```

- Wrapper output includes `BENCH_OUTPUT=...` path.
- Do not manually widen bounds unless user opts into `PERIOD_MODE=daring` or provides explicit bounds.
- The benchmark must disable query caches where supported.

From ethpandaops/xatu-cbt, MIT.

More from ethpandaops/xatu-cbt