dune-to-allium

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Dune → Allium Query Conversion Skill

Dune 转 Allium 查询转换技能

Convert Dune Analytics (Trino) SQL queries to Allium (Snowflake) SQL. SQL dialect conversions apply to all chains. Comprehensive Solana and EVM chain mappings included.

将Dune Analytics（Trino）SQL查询转换为Allium（Snowflake）SQL。SQL方言转换适用于所有区块链网络，同时包含完整的Solana和EVM链映射关系。

Prerequisites

前置条件

Allium API key in
```
~/.allium/credentials
```
:
```
API_KEY=allium_...
QUERY_ID=...
```
Get your key at https://app.allium.so/settings/api-keys. If
```
QUERY_ID
```
is missing, the
```
allium_query.py
```
script creates one automatically.
(Optional)
```
DUNE_API_KEY
```
in project
```
.env
```
file for automated result fetching via
```
dune_query.py
```
. Alternatively, run the original query in the Dune app and export results manually.

Allium API密钥需配置在
```
~/.allium/credentials
```
中：
```
API_KEY=allium_...
QUERY_ID=...
```
可在https://app.allium.so/settings/api-keys获取密钥。如果缺少`QUERY_ID`，`allium_query.py`脚本会自动创建一个。 -（可选）在项目
```
.env
```
文件中配置
```
DUNE_API_KEY
```
，以便通过
```
dune_query.py
```
自动获取结果。或者，也可以在Dune应用中运行原始查询并手动导出结果。

Conversion Workflow

转换流程

Step 1: Accept Dune SQL

步骤1：获取Dune SQL

Get the Dune query via one of:

Pasted SQL: User pastes Dune SQL directly

Saved query ID: Fetch results via Dune API for comparison:

bash

uv run ~/.claude/skills/dune-to-allium/scripts/dune_query.py QUERY_ID --json > dune_results.json

Note: Dune API free tier only supports fetching results of saved queries by ID. It does NOT support executing arbitrary SQL.

通过以下方式之一获取Dune查询：

粘贴SQL：用户直接粘贴Dune SQL代码

已保存的查询ID：通过Dune API获取结果用于对比：

bash

uv run ~/.claude/skills/dune-to-allium/scripts/dune_query.py QUERY_ID --json > dune_results.json

注意：Dune API免费版仅支持通过ID获取已保存查询的结果，不支持执行任意SQL。

Step 2: Identify Dune-Specific Tables and Syntax

步骤2：识别Dune特定表与语法

Scan the query for:

Dune tables — Look up each in SOLANA_MAPPINGS.md table mappings section
Dune column names — Map using the column mappings section
Trino-specific SQL — Identify array functions, date syntax, unnest patterns
Dune parameters —
```
{{param}}
```
syntax needs replacement

Common Dune tables and their Allium equivalents:

Dune Table	Allium Table	Special Handling
`solana.instruction_calls`	`solana.raw.instructions` UNION `solana.raw.inner_instructions`	Always UNION both
`solana.account_activity`	`solana.assets.transfers`	Different approach entirely
`tokens_solana.transfers`	`solana.assets.transfers`	Amount is pre-normalized
`jupiter_solana.aggregator_swaps`	`solana.dex.aggregator_trades`	Different granularity!
`prices.usd`	`common.prices.hourly`	Different column names
`solana.transactions`	`solana.raw.transactions`

扫描查询内容，找出：

Dune表 — 在SOLANA_MAPPINGS.md的表映射章节中查找对应关系
Dune列名 — 使用列映射章节进行转换
Trino特定SQL — 识别数组函数、日期语法、unnest用法
Dune参数 —
```
{{param}}
```
语法需要替换

常见Dune表及其Allium等价表：

Dune表	Allium表	特殊处理
`solana.instruction_calls`	`solana.raw.instructions` UNION `solana.raw.inner_instructions`	必须同时联合两个表
`solana.account_activity`	`solana.assets.transfers`	处理方式完全不同
`tokens_solana.transfers`	`solana.assets.transfers`	金额已预先标准化
`jupiter_solana.aggregator_swaps`	`solana.dex.aggregator_trades`	粒度不同！
`prices.usd`	`common.prices.hourly`	列名不同
`solana.transactions`	`solana.raw.transactions`

Step 3: Apply Table Mappings

步骤3：应用表映射

For each Dune table, apply the conversion from SOLANA_MAPPINGS.md:

为每个Dune表应用SOLANA_MAPPINGS.md中的转换规则：

Instructions (CRITICAL — most common conversion)

指令（关键转换——最常见场景）

sql

-- Dune: single table
FROM solana.instruction_calls
WHERE executing_account = '{program}' AND tx_success = true

-- Allium: UNION outer + inner, use parent_tx_success
WITH all_instructions AS (
    SELECT * FROM solana.raw.instructions
    WHERE program_id = '{program}' AND parent_tx_success = true
      AND block_timestamp >= '{start}' AND block_timestamp < '{end}'
    UNION ALL
    SELECT * FROM solana.raw.inner_instructions
    WHERE program_id = '{program}' AND parent_tx_success = true
      AND block_timestamp >= '{start}' AND block_timestamp < '{end}'
)

sql

-- Dune: 单表
FROM solana.instruction_calls
WHERE executing_account = '{program}' AND tx_success = true

-- Allium: 联合外部和内部指令，使用parent_tx_success
WITH all_instructions AS (
    SELECT * FROM solana.raw.instructions
    WHERE program_id = '{program}' AND parent_tx_success = true
      AND block_timestamp >= '{start}' AND block_timestamp < '{end}'
    UNION ALL
    SELECT * FROM solana.raw.inner_instructions
    WHERE program_id = '{program}' AND parent_tx_success = true
      AND block_timestamp >= '{start}' AND block_timestamp < '{end}'
)

Account Activity → Transfers

账户活动 → 转账记录

sql

-- Dune: balance changes
FROM solana.account_activity
WHERE address = '{addr}' AND tx_success = true

-- Allium: use transfers table (do NOT use balances with LAG)
FROM solana.assets.transfers
WHERE to_address = '{addr}'
  AND transfer_type IN ('spl_token_transfer', 'sol_transfer')

sql

-- Dune: 余额变化
FROM solana.account_activity
WHERE address = '{addr}' AND tx_success = true

-- Allium: 使用转账表（不要用LAG函数处理余额）
FROM solana.assets.transfers
WHERE to_address = '{addr}'
  AND transfer_type IN ('spl_token_transfer', 'sol_transfer')

Step 4: Apply SQL Dialect Conversions

步骤4：应用SQL方言转换

Apply these Trino → Snowflake transformations:

Find	Replace With
`account_arguments[N]`	`accounts[N-1]` (subtract 1)
`cardinality(arr)`	`ARRAY_SIZE(arr)`
`CROSS JOIN UNNEST(arr) AS t(val)`	`, LATERAL FLATTEN(input => arr) f` (use `f.value` )
`NOW()`	`CURRENT_TIMESTAMP()`
`INTERVAL '7' DAY`	`INTERVAL '7 days'`
`FROM_UTF8(data)`	`TRY_TO_VARCHAR(data, 'UTF-8')`
`block_time`	`block_timestamp`
`tx_id`	`txn_id`
`executing_account`	`program_id`
`tx_success`	`parent_tx_success` (instructions) or `success` (transactions)
`approx_distinct(col)`	`APPROX_COUNT_DISTINCT(col)`

Full reference: SOLANA_MAPPINGS.md SQL dialect section.

应用以下Trino → Snowflake转换规则：

查找内容	替换为
`account_arguments[N]`	`accounts[N-1]` （减1）
`cardinality(arr)`	`ARRAY_SIZE(arr)`
`CROSS JOIN UNNEST(arr) AS t(val)`	`, LATERAL FLATTEN(input => arr) f` （使用 `f.value` ）
`NOW()`	`CURRENT_TIMESTAMP()`
`INTERVAL '7' DAY`	`INTERVAL '7 days'`
`FROM_UTF8(data)`	`TRY_TO_VARCHAR(data, 'UTF-8')`
`block_time`	`block_timestamp`
`tx_id`	`txn_id`
`executing_account`	`program_id`
`tx_success`	`parent_tx_success` （指令表）或 `success` （交易表）
`approx_distinct(col)`	`APPROX_COUNT_DISTINCT(col)`

完整参考：SOLANA_MAPPINGS.md的SQL方言章节。

Step 5: Handle Structural Differences

步骤5：处理结构差异

Check KNOWN_DIFFERENCES.md for expected deltas:

Pricing: Allium prices more tokens → ~76% higher
```
transfers_usd_value
```
Jupiter swaps: Different granularity (legs vs aggregated swaps)

Transfer types: Filter

transfer_type IN ('spl_token_transfer', 'sol_transfer')

to exclude account closures

Amounts: Allium
```
amount
```
is pre-normalized — remove any
```
/ pow(10, decimals)
```
division

查看KNOWN_DIFFERENCES.md了解预期的结果差异：

定价：Allium支持更多代币的价格数据 →
```
transfers_usd_value
```
数值约高76%
Jupiter兑换：粒度不同（单腿交易 vs 聚合交易）

转账类型：过滤

transfer_type IN ('spl_token_transfer', 'sol_transfer')

以排除账户关闭操作

金额：Allium的
```
amount
```
字段已预先标准化 — 移除所有
```
/ pow(10, decimals)
```
除法操作

Step 6: Add Timestamp Filters

步骤6：添加时间戳过滤

CRITICAL: Solana tables are massive. Always add tight timestamp filters:

sql

WHERE block_timestamp >= '2024-01-01'::TIMESTAMP
  AND block_timestamp < '2024-01-02'::TIMESTAMP

Queries without timestamp filters on Solana tables will time out.

关键提示：Solana表数据量极大，务必添加严格的时间戳过滤：

sql

WHERE block_timestamp >= '2024-01-01'::TIMESTAMP
  AND block_timestamp < '2024-01-02'::TIMESTAMP

未添加时间戳过滤的Solana表查询会超时。

Step 7: Run Converted Query

步骤7：运行转换后的查询

Write the converted SQL to a

.sql

file in the project directory, then execute via the Allium Explorer API:

bash

uv run ~/.claude/skills/dune-to-allium/scripts/allium_query.py --file converted_query.sql

Or with inline SQL:

bash

uv run ~/.claude/skills/dune-to-allium/scripts/allium_query.py "SELECT * FROM ethereum.raw.blocks LIMIT 10"

Add

--json

to get machine-readable output:

bash

uv run ~/.claude/skills/dune-to-allium/scripts/allium_query.py --file converted_query.sql --json > /tmp/allium_results.json

将转换后的SQL写入项目目录下的

.sql

文件，然后通过Allium Explorer API执行：

bash

uv run ~/.claude/skills/dune-to-allium/scripts/allium_query.py --file converted_query.sql

或直接执行内联SQL：

bash

uv run ~/.claude/skills/dune-to-allium/scripts/allium_query.py "SELECT * FROM ethereum.raw.blocks LIMIT 10"

添加

--json

参数以获取机器可读格式的输出：

bash

uv run ~/.claude/skills/dune-to-allium/scripts/allium_query.py --file converted_query.sql --json > /tmp/allium_results.json

Step 8: Compare Results (Optional)

步骤8：对比结果（可选）

If you have Dune results to compare against:

bash

undefined

如果有Dune结果可用于对比：

bash

undefined

Fetch Dune results

获取Dune结果

uv run ~/.claude/skills/dune-to-allium/scripts/dune_query.py QUERY_ID --json > /tmp/dune_results.json

Run Allium query and save results

运行Allium查询并保存结果

uv run ~/.claude/skills/dune-to-allium/scripts/allium_query.py --file converted_query.sql --json > /tmp/allium_results.json

Compare

对比结果

uv run ~/.claude/skills/dune-to-allium/scripts/compare_results.py /tmp/dune_results.json /tmp/allium_results.json


The comparison tool auto-maps known column name differences (e.g., `tx_id` ↔ `txn_id`).

uv run ~/.claude/skills/dune-to-allium/scripts/compare_results.py /tmp/dune_results.json /tmp/allium_results.json


对比工具会自动映射已知的列名差异（如`tx_id` ↔ `txn_id`）。

Investigating Dune Spellbook Filters

排查Dune Spellbook过滤逻辑

When results differ due to Dune spellbook filtering logic (wash trading filters, hardcoded date exclusions, etc.), search the public spellbook repo:

Search
```
github.com/duneanalytics/spellbook
```
for the table or model name
Look for
```
WHERE
```
clauses, CTEs named
```
filter
```
, or hardcoded address/date exclusions
~90% of discrepancies come from spellbook filters, not data differences

当结果差异源于Dune Spellbook过滤逻辑（洗盘交易过滤、硬编码日期排除等）时，可在公开的Spellbook仓库中搜索：

在
```
github.com/duneanalytics/spellbook
```
中搜索表或模型名称
查找
```
WHERE
```
子句、名为
```
filter
```
的CTE，或硬编码的地址/日期排除规则
约90%的差异源于Spellbook过滤逻辑，而非数据本身的差异

Checklist

检查清单

Before finalizing a conversion, verify:

在完成转换前，需验证以下内容：

All Chains

所有区块链通用

```
block_time
```
→
```
block_timestamp
```
```
NOW()
```
→
```
CURRENT_TIMESTAMP()
```
```
INTERVAL '7' DAY
```
→
```
INTERVAL '7 days'
```

SUM(...) FILTER (WHERE ...)

→

SUM(CASE WHEN ... THEN ... ELSE 0 END)

```
CROSS JOIN UNNEST
```
→
```
LATERAL FLATTEN
```
```
cardinality()
```
→
```
ARRAY_SIZE()
```
Array indices shifted by -1 (Trino is 1-based, Snowflake is 0-based)
```
query_XXXXX
```
references identified and inlined or flagged as blocking
```
get_href()
```
calls removed (Dune UI function)
Dune parameters (
```
{{param}}
```
) replaced with values or Snowflake variables

```
block_time
```
→
```
block_timestamp
```
```
NOW()
```
→
```
CURRENT_TIMESTAMP()
```
```
INTERVAL '7' DAY
```
→
```
INTERVAL '7 days'
```

SUM(...) FILTER (WHERE ...)

→

SUM(CASE WHEN ... THEN ... ELSE 0 END)

```
CROSS JOIN UNNEST
```
→
```
LATERAL FLATTEN
```
```
cardinality()
```
→
```
ARRAY_SIZE()
```
数组索引减1（Trino是1-based索引，Snowflake是0-based索引）
识别
```
query_XXXXX
```
引用并内联或标记为阻塞项
移除
```
get_href()
```
调用（Dune UI专用函数）
将Dune参数（
```
{{param}}
```
）替换为实际值或Snowflake变量

Solana-Specific

Solana专属

All
```
instruction_calls
```
references use UNION of outer + inner instructions
```
tx_id
```
→
```
txn_id
```
Success filter uses
```
parent_tx_success = true
```
(not JOIN)
```
transfer_type
```
filter applied when using
```
solana.assets.transfers
```
Removed
```
/ pow(10, decimals)
```
if using Allium's
```
amount
```
column

所有
```
instruction_calls
```
引用均使用外部+内部指令的联合查询
```
tx_id
```
→
```
txn_id
```
成功状态过滤使用
```
parent_tx_success = true
```
（不要用JOIN）
使用
```
solana.assets.transfers
```
时添加
```
transfer_type
```
过滤
如果使用Allium的
```
amount
```
列，移除
```
/ pow(10, decimals)
```
操作

EVM-Specific

EVM专属

{chain}.transactions

→

{chain}.raw.transactions

```
{chain}.logs
```
→
```
{chain}.raw.logs
```

Decoded tables →

{chain}.decoded.logs

{chain}.decoded.traces

with filters

Spellbook table dependencies identified (e.g.,
```
staking_ethereum.info
```
)
ERC20 transfers:
```
amount
```
is pre-normalized — remove
```
/ pow(10, decimals)
```
DEX queries: combine
```
dex.orderflow
```
+
```
dex.trades
```
(exclude overlapping txs)

Column names:

TRANSACTION_HASH

(not

TX_HASH

USD_AMOUNT

(not

AMOUNT_USD

) in

dex.trades

BSC chain prefix:
```
bsc.*
```
(not
```
bnb.*
```
or
```
binance.*
```
)

{chain}.transactions

→

{chain}.raw.transactions

```
{chain}.logs
```
→
```
{chain}.raw.logs
```

解码表 →

{chain}.decoded.logs

{chain}.decoded.traces

并添加过滤条件

识别Spellbook表依赖（如
```
staking_ethereum.info
```
）
ERC20转账：
```
amount
```
已预先标准化 — 移除
```
/ pow(10, decimals)
```
操作
DEX查询：合并
```
dex.orderflow
```
+
```
dex.trades
```
（排除重叠交易）

列名：

dex.trades

中使用

TRANSACTION_HASH

（而非

TX_HASH

）、

USD_AMOUNT

（而非

AMOUNT_USD

）

BSC链前缀：使用
```
bsc.*
```
（而非
```
bnb.*
```
或
```
binance.*
```
）

Reference Files

参考文件

SOLANA_MAPPINGS.md — Solana table/column mappings + all SQL dialect conversions (Trino → Snowflake)
EVM_MAPPINGS.md — EVM chain table/column mappings (Ethereum, Arbitrum, Base, etc.)
KNOWN_DIFFERENCES.md — Expected result deltas and why they occur

SOLANA_MAPPINGS.md — Solana表/列映射关系 + 完整SQL方言转换（Trino → Snowflake）
EVM_MAPPINGS.md — EVM链表/列映射关系（Ethereum、Arbitrum、Base等）
KNOWN_DIFFERENCES.md — 预期的结果差异及其原因