Update to Allium

{
  "name": "sql-optimization",
  "description": "**Required reading before writing or running any SQL.** Patterns for Snowflake\nquery performance and chain-specific pitfalls.\n\nCovers: default time ranges, partition-pruning rules, CTE filtering, `QUALIFY`\nfor dedup, `UNION ALL` over `UNION`, `APPROX_COUNT_DISTINCT` for exploration,\npre-aggregated `*.metrics.*` tables vs raw aggregation, EVM address lowercasing\n(and when not to), per-chain vs `crosschain.*` tables, verifying guessed\ncategorical filter values with `SELECT DISTINCT`, and Solana voting/non-voting\noptimized views.\n\nCall `get_skill(name=\"sql-optimization\")` before writing SQL —\nespecially for large tables, long time ranges, or crosschain queries.",
  "included_files": [],
  "skill_md_contents": "---\nname: sql-optimization\ndescription: |\n  **Required reading before writing or running any SQL.** Patterns for Snowflake\n  query performance and chain-specific pitfalls.\n\n  Covers: default time ranges, partition-pruning rules, CTE filtering, `QUALIFY`\n  for dedup, `UNION ALL` over `UNION`, `APPROX_COUNT_DISTINCT` for exploration,\n  pre-aggregated `*.metrics.*` tables vs raw aggregation, EVM address lowercasing\n  (and when not to), per-chain vs `crosschain.*` tables, verifying guessed\n  categorical filter values with `SELECT DISTINCT`, and Solana voting/non-voting\n  optimized views.\n\n  Call `get_skill(name=\"sql-optimization\")` before writing SQL —\n  especially for large tables, long time ranges, or crosschain queries.\n---\n\n# SQL Optimization for Snowflake\n\nApply these patterns when writing or optimizing SQL against Allium's Snowflake\nwarehouse, especially for multi-billion-row tables (`*.raw.transactions`,\n`*.raw.transfers`, `*.raw.logs`, `*.dex.trades`).\n\n## Verify Guessed Categorical Filters\n\nBefore committing to guessed categorical values in a `WHERE` clause, run a\nquick exploration query to verify the values present in the table. Use schema\ndescriptions to choose the table and column; use data exploration when the\nexact stored value is not already confirmed.\n\n**For categorical/text columns** you plan to filter on (e.g., `coin`,\n`market_type`, `pair`, `status`, `type`, `dex_name`, `chain`):\n\n- Run `SELECT DISTINCT <column> FROM <table> [WHERE <recent_timestamp_filter>] LIMIT 50` first\n- On large tables, scope with a recent timestamp filter (e.g., `WHERE timestamp >= DATEADD('day', -7, CURRENT_DATE())`) to keep exploration cheap\n- For small metadata/dimension tables, a bare `SELECT DISTINCT` is fine\n\nIf schema content or sample rows show the exact stored value, use that spelling.\nIf they do not, verify the value from the table before using it as a filter.\n\n**If a query unexpectedly returns 0 rows, check categorical filters before\nconcluding the data is missing.** Re-run with `SELECT DISTINCT` or a `LIKE\n'%term%'` pattern.\n\n## SQL Pitfalls (Always Apply)\n\n**Date-filter every query on large tables.** Most large tables\n(`*.raw.transactions`, `*.raw.transfers`, `*.dex.trades`, `*.raw.logs`) are\nclustered on `block_timestamp::date` or `timestamp::date`. Without a date\nfilter, queries scan the entire table — billions of rows, slow and expensive.\nAlways include a bounded date range (e.g., `WHERE block_timestamp >=\n'2026-01-01'`). If the user didn't specify a range, pick a default (last 30\ndays for activity, last 1 day for current state) and state the assumption.\n\n**Default to recent time windows when the user is silent:**\n\n- **7 days** (1 week) — for exploratory queries, quick analysis\n- **30 days** (1 month) — for trend analysis, aggregations\n\n```sql\n-- 1 week (default for exploration)\nWHERE block_timestamp >= DATEADD(day, -7, CURRENT_DATE())\n\n-- 1 month (for trends)\nWHERE block_timestamp >= DATEADD(day, -30, CURRENT_DATE())\n```\n\nIf the user needs data beyond 30 days: confirm first (\"This query covers a\nlarge time range and may be slow. Is that okay?\"), consider breaking into\nsmaller batches (e.g., monthly), or use more aggressive filtering on other\ncolumns.\n\n**Lowercase EVM addresses before filtering or joining — but only EVM.** Allium\nstores EVM addresses (`0x...` hex) lowercase, while users and block explorers\noften paste them checksummed. `WHERE from_address = '0xAbC123...'` on an EVM\ntable silently returns 0 rows; apply `LOWER()` to user-supplied EVM addresses.\n**Do NOT lowercase non-EVM addresses.** Solana, Tron, Sui, and other non-EVM\nchains store addresses in their native case-sensitive form (e.g., Solana\nbase58 `HyperSPG8w4j...`); lowercasing changes the address's meaning. In\n`crosschain.*` tables, key on `chain` to decide whether to apply `LOWER()` per\nrow.\n\n**Prefer per-chain tables; crosschain joins need `(chain, address)`.** The\n`crosschain.*` schemas are convenient but significantly slower than per-chain\ntables, especially when Solana is involved. When the user's question targets a\nsingle chain, use the per-chain table (e.g., `ethereum.dex.trades`) instead of\n`crosschain.dex.trades`. Reach for `crosschain.*` only when actually comparing\nacross 3+ chains. When you do use crosschain tables, the same address (e.g.,\nUSDC) exists on many chains with different state — always join on `(chain,\naddress)`, not address alone, and lowercase the address only when the row's\nchain is EVM.\n\n## Prefer Pre-Aggregated Metrics Tables\n\nWhen a question can be answered from a `*.metrics.*` table, use it instead of\naggregating from raw. Daily aggregates (e.g., `hyperliquid.metrics.overview`,\n`ethereum.metrics.dex_overview`, `crosschain.metrics.overview`) are orders of\nmagnitude faster and cheaper than `SUM(...) GROUP BY day` over raw trades,\ntransfers, or transactions.\n\nWhen the user asks for a metric — volume, TVL, trade count, active users,\nfees, market cap, open interest, etc. — `search_schemas` for a `metrics` table\nfirst. Only fall back to raw tables when the metrics table lacks the dimension\nor granularity the question needs (e.g., per-wallet breakdown, sub-daily\nresolution).\n\n## Snowflake Performance Patterns\n\nApply these patterns when writing queries against multi-billion-row tables.\n\n### Filter in CTEs before joining\n\nPre-reduce each table in its own CTE before the join. Joining two\nalready-filtered datasets is vastly cheaper than filtering the joined result.\nEspecially effective when filtering on the timestamp column.\n\n```sql\n-- bad: query engine *might not* push block_timestamp down to table_b\nwith\ncte_a as (select * from table_a where block_timestamp between '2025-01-01' and '2025-01-02'),\ncte_b as (select * from table_b)\nselect * from cte_a join cte_b using (block_timestamp);\n\n-- good: filter both sides explicitly\nwith\ncte_a as (select * from table_a where block_timestamp between '2025-01-01' and '2025-01-02'),\ncte_b as (\n  select * from table_b\n  where block_timestamp between '2025-01-01' and '2025-01-02'\n)\nselect * from cte_a join cte_b using (block_timestamp);\n```\n\n### Partition pruning does NOT work with subquery values\n\nSnowflake's partition pruning only fires on literal values, not subquery\nresults ([snowflake docs](https://docs.snowflake.com/en/user-guide/tables-clustering-micropartitions#query-pruning)).\n\n```sql\n-- bad: pruning doesn't fire\nselect * from table_a where block_timestamp >= (select min(timestamp) from table_b);\n\n-- good: precompute and inline a literal\nselect * from table_a where block_timestamp >= '2025-01-01';\n```\n\n### Join the big table once\n\nWhen looking up multiple address sets against a large table, `UNION ALL` the\nlookups into a single CTE first, then join the big table only once — rather\nthan running multiple joins or repeated queries.\n\n### Use `QUALIFY` for latest-per-group / dedup\n\nSnowflake's `QUALIFY` filters window function results inline — no wrapper CTE\nneeded, often faster:\n\n```sql\nSELECT token_address, block_timestamp, price\nFROM ethereum.dex.trades\nWHERE block_timestamp >= DATEADD('day', -7, CURRENT_DATE())\nQUALIFY ROW_NUMBER() OVER (PARTITION BY token_address ORDER BY block_timestamp DESC) = 1;\n```\n\n### `UNION ALL`, not `UNION`\n\nPlain `UNION` runs an implicit `DISTINCT` across the full result — an\nexpensive shuffle. Use `UNION ALL` unless you specifically need to dedupe\nacross the two sides.\n\n### `APPROX_COUNT_DISTINCT` for exploration\n\nExact `COUNT(DISTINCT ...)` on billions of rows is slow (full shuffle). HLL\napproximation is orders of magnitude faster with ~1% error — fine for\nexploratory analysis where a ballpark is enough.\n\n### Batch large time ranges\n\nWhen querying a large time range, concurrent queries of smaller time intervals\nmight work better — e.g., when querying a year's data, 12 batches of 1 month\neach is probably faster than one yearly query.\n\n### Clustering and join conditions\n\nMost time-based tables are clustered by `block_timestamp::date`, so it's\nuseful to have `block_timestamp` in join conditions. Filter on the clustered\ncolumn first:\n\n```sql\n-- good: filters on clustered column first\nSELECT * FROM ethereum.raw.transactions\nWHERE block_timestamp >= '2024-01-01'\n  AND from_address = '0x...';\n```\n\n### Select only the columns you need\n\nAvoid `SELECT *` on wide tables. Snowflake is columnar — pruning unread\ncolumns directly reduces scan cost.\n\n## Ecosystem-specific tips\n\n### Solana\n\n1. When querying raw entities such as transactions, instructions,\n   inner_instructions, if you do not need voting data, use one of the\n   corresponding optimized views:\n\n   - transactions:\n     - `success_nonvoting_transactions`\n     - `nonvoting_transactions`\n   - instructions:\n     - `success_nonvoting_instructions`\n     - `nonvoting_instructions`\n   - inner_instructions:\n     - `success_nonvoting_inner_instructions`\n     - `nonvoting_inner_instructions`\n   - inner_outer_instructions:\n     - `success_nonvoting_inner_outer_instructions`\n     - `nonvoting_inner_outer_instructions`\n\n2. Filter out failed/success and voting/nonvoting records:\n\n   - for transactions, use `success` and `is_voting`\n   - for (inner)instructions, use `parent_tx_success` and `is_voting`\n\n3. For transaction meta columns, the rpc returns pre/post token/native\n   balances. We have reformatted these cols to be more user-friendly in the\n   columns `sol_amounts`, `mint_to_decimals`, `token_accounts` for your\n   convenience (only available for `success_nonvoting_transactions`). For\n   more info, refer to [transaction-level columns](/historical-data/supported-blockchains/solana#transaction-level-columns).\n\n4. For transaction fees, see [solana.raw.fees](https://docs.allium.so/historical-data/supported-blockchains/solana/raw/fees#fees);\n   they are also available in [transaction-level columns](/historical-data/supported-blockchains/solana#transaction-level-columns)\n   for convenience.\n\n## More Snowflake patterns\n\n### Join on clustered columns\n\nWhen a query `JOIN`s two tables, prefer joining on **clustered columns**. Most\ntime-based tables are clustered on `block_timestamp` (or `timestamp`), so\nincluding that column in the join condition lets Snowflake prune partitions on\nboth sides. Check the schema/metadata to confirm which columns are clustered\nbefore choosing a join key.\n\n### Prefer `ASOF JOIN` for nearest-time / padding joins\n\nWhen joining two tables on the nearest preceding value (e.g. attaching the most\nrecent price to each trade, or filling padding values across time), use\nSnowflake's `ASOF JOIN`. It is simpler and usually more efficient than\nhand-rolled window-function or range-partitioning approaches.\n\n### Avoid recursive CTEs\n\nAvoid recursion in SQL. Recursive queries are hard to reason about without\nknowledge of table cluster mappings and tend to perform poorly. Re-express the\nlogic with ordinary (non-recursive) CTEs instead.\n\n### Convert hex strings to numbers with the secure UDFs\n\nTo convert a hex string to a number in Snowflake:\n\n1. Strip the `0x` prefix from the hex string.\n2. Use `COMMON.UDFS.JS_HEXTOINT_SECURE(<varchar>)` to convert it.\n3. If the value is **little-endian** encoded, use\n   `COMMON.UDFS.JS_HEXTOINT_LITTLEENDIAN_SECURE(<varchar>)` instead.\n\n### dbt incremental models and partition pruning\n\nSnowflake partition pruning does **not** fire on values from a subquery. In a\ndbt model with `materialization='incremental'`, don't put\n`max(timestamp) from {{ this }}` directly in the incremental `WHERE` — precompute\nthe timestamp into a literal first (e.g. a macro that queries `{{ this }}` and\nreturns a single value), then use that literal everywhere a time filter applies.\n\n## SQL style conventions\n\nThese keep generated SQL correct and readable.\n\n### Always specify `NULLS LAST` in `ORDER BY`\n\nSnowflake sorts `NULL`s first by default for descending order, which surfaces\nempty rows at the top. Add `NULLS LAST` to `ORDER BY` clauses so nulls don't\nlead the result set:\n\n```sql\nORDER BY usd_amount DESC NULLS LAST\n```\n\n### Comment every CTE\n\nFor each CTE, add a short comment at the top describing its purpose, the data it\nprocesses, and any important transformations or filters. This makes multi-CTE\nqueries maintainable:\n\n```sql\nwith\n-- recent_trades: last 7 days of ethereum DEX trades, filtered early on the\n-- clustered block_timestamp column to keep the scan small\nrecent_trades as (\n    select * from ethereum.dex.trades\n    where block_timestamp >= dateadd(day, -7, current_date())\n)\nselect * from recent_trades;\n```\n"
}