sql-optimization
**Required reading before writing or running any SQL.** Patterns for Snowflake query performance and chain-specific pitfalls. Covers: never querying deprecated tables, default time ranges, partition-pruning rules, CTE filtering, `QUALIFY` for dedup, `UNION ALL` over `UNION`, `APPROX_COUNT_DISTINCT` for exploration, pre-aggregated `*.metrics.*` tables vs raw aggregation, EVM address lowercasing (and when not to), per-chain vs `crosschain.*` tables, verifying guessed categorical filter values with `SELECT DISTINCT`, and Solana voting/non-voting optimized views. Call `get_skill(name="sql-optimization")` before writing SQL — especially for large tables, long time ranges, or crosschain queries.
Install
With your agent
Install OMGSkills skill "allium-labs/allium-claude-plugin:skills/sql-optimization" into this agent's normal skills directory: use the OMGSkills MCP get_skill tool, install only the pinned_install commit and path, verify SKILL.md against pinned_install.skill_md_sha, and stop if MCP or the pin is unavailable. Do not overwrite or change existing skills without asking me.
Manual command
git clone https://github.com/allium-labs/allium-claude-plugin /tmp/allium-claude-plugin && ln -s /tmp/allium-claude-plugin/skills/sql-optimization ~/.claude/skills/sql-optimizationFrom README
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, markettype, pair, status, type, dexname, chain): Run SELECT DISTINCT FROM [WHERE ] LIMIT 50 first On large tables, scope with a recent timestamp filter (e.g., WHERE timestamp >= DATEADD('day', -7, CURRENTDATE())) 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.
More from this repo
allium-investigation
Investigate an onchain entity, protocol, market, or wallet question with Allium.
bridge-analysis
Methodology playbook for analyzing cross-chain bridge and messaging-protocol data on Allium — CCTP, LayerZero,...
dashboard-design
Used to build and update Allium Explorer dashboards with set_dashboard.