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: call the OMGSkills MCP get_install_plan tool for your agent and run the script it returns (it installs only the pinned commit and verifies SKILL.md), 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.