sql-optimization

Files (1)

• SKILL.md

SKILL.md (first lines)

# SQL Optimization for Snowflake

Apply these patterns when writing or optimizing SQL against Allium's Snowflake
warehouse, especially for multi-billion-row tables (`*.raw.transactions`,
`*.raw.transfers`, `*.raw.logs`, `*.dex.trades`).

## Verify Guessed Categorical Filters

Before committing to guessed categorical values in a `WHERE` clause, run a
quick exploration query to verify the values present in the table. Use schema
descriptions to choose the table and column; use data exploration when the
exact stored value is not already confirmed.

**For categorical/text columns** you plan to filter on (e.g., `coin`,
`market_type`, `pair`, `status`, `type`, `dex_name`, `chain`):

- Run `SELECT DISTINCT <column> FROM <table> [WHERE <recent_timestamp_filter>] LIMIT 50` first
- On large tables, scope with a recent timestamp filter (e.g., `WHERE timestamp >= DATEADD('day', -7, CURRENT_DATE())`) to keep exploration cheap
- For small metadata/dimension tables, a bare `SELECT DISTINCT` is fine

If schema content or sample rows show the exact stored value, use that spelling.
If they do not, verify the value from the table before using it as a filter.

**If a query unexpectedly returns 0 rows, check categorical filters before
concluding the data is missing.** Re-run with `SELECT DISTINCT` or a `LIKE
'%term%'` pattern.

## Never Query a Deprecated Table

A `search_schemas` hit with `deprecated: true` is retired. It still returns
rows, but the data is frozen or incomplete, so the query succeeds and the
answer is wrong. A query that runs cleanly does not prove the table is
current.

- Call `search_docs` on the table name to get the reason the table was retired
  and the table that replaced it. Most deprecated tables have no
  `replacement_table` value, so the docs are usually the only place the
  current table is named. Use `replacement_table` when the hit has one.
- Check every table in the query, including the tables in `JOIN`s. If a table
  did not come from a `search_schemas` hit in this session, look it up with
  `search_schemas(id="<db>.<schema>.<table>")` before you use it.
- If the user asks for a deprecated table, tell them it is retired and why,
  then query the replacement.

## SQL Pitfalls (Always Apply)

**Date-filter every query on large tables.** Most large tables
(`*.raw.transactions`, `*.raw.transfers`, `*.dex.trades`, `*.raw.logs`) are
clustered on `block_timestamp::date` or `timestamp::date`. Without a date
filter, queries scan the entire table — billions of rows, slow and expensive.
Always include a bounded date range (e.g., `WHERE block_timestamp >=
'2026-01-01'`). If the user didn't specify a range, pick a default (last 30
days for activity, last 1 day for current state) and state the assumption.

**Default to recent time windows when the user is silent:**

- **7 days** (1 week) — for exploratory queries, quick analysis
- **30 days** (1 month) — for trend analysis, aggregations

```sql
-- 1 week (default for exploration)
WHERE block_timestamp >= DATEADD(day, -7, CURRENT_DATE())

-- 1 month (for trends)
WHERE block_timestamp >= DATEADD(day, -30, CURRENT_DATE())
```

If the user needs data beyond 30 days: confirm first ("This query covers a
large time range and may be slow. Is that okay?"), consider breaking into
smaller batches (e.g., monthly), or use more aggressive filtering on other
columns.

**Lowercase EVM addresses before filtering or joining — but only EVM.** Allium
stores EVM addresses (`0x...` hex) lowercase, while users and block explorers
often paste them checksummed. `WHERE from_address = '0xAbC123...'` on an EVM
table silently returns 0 rows; apply `LOWER()` to user-supplied EVM addresses.
**Do NOT lowercase non-EVM addresses.** Solana, Tron, Sui, and other non-EVM
chains store addresses in their native case-sensitive form (e.g., Solana
base58 `HyperSPG8w4j...`); lowercasing changes the address's meaning. In
`crosschain.*` tables, key on `chain` to decide whether to apply `LOWER()` per
row.

**Prefer per-chain tables; crosschain joins need `(chain, address)`.** The
`crosschain.*` schemas are convenient but significantly slower than per-chain
tables, especially when Solana is involved. When the user's question targets a
single chain, use the per-chain table (e.g., `ethereum.dex.trades`) instead of
`crosschain.dex.trades`. Reach for `crosschain.*` only when actually comparing
across 3+ chains. When you do use crosschain tables, the same address (e.g.,
USDC) exists on many chains with different state — always join on `(chain,
address)`, not address alone, and lowercase the address only when the row's
chain is EVM.

## Prefer Pre-Aggregated Metrics Tables

When a question can be answered from a `*.metrics.*` table, use it instead of
aggregating from raw. Daily aggregates (e.g., `hyperliquid.metrics.overview`,
`ethereum.metrics.dex_overview`, `crosschain.metrics.overview`) are orders of
magnitude faster and cheaper than `SUM(...) GROUP BY day` over raw trades,
transfers, or transactions.

When the user asks for a metric — volume, TVL, trade count, active users,
fees, market cap, open interest, etc. — `search_schemas` for a `metrics` table
first. Only fall back to raw tables when the metrics table lacks the dimension
or granularity the question needs (e.g., per-wallet breakdown, sub-daily
resolution).

One layer above that, the metrics catalog answers some questions with no SQL at
all. If `list_catalog_metrics` is available, call it before writing a query
about stablecoins, RWAs, issuers, lending, DEXes or payments, and read any
matching metric with `get_catalog_metric`. Write SQL when the catalog has no
metric for the subject, or when the question needs a dimension, granularity or
join the catalog does not expose.

## Snowflake Performance Patterns

Apply these patterns when writing queries against multi-billion-row tables.

### Filter in CTEs before joining

From allium-labs/allium-claude-plugin, MIT.

More from allium-labs/allium-claude-plugin