Polkadot & Substrate Overview

The Polkadot Relay Chain is a proof-of-stake blockchain that provides shared security and secure interoperability to other blockchains, known as parachains. Parachain’s transactions are processed in parallel by Polkadot. Kusama is a sister relay chain, designed as a canary blockchain, largely to test new Polkadot functionality before being deployed on Polkadot.

Both relay chains and all their parachains are built with Substrate. Substrate is the primary blockchain SDK used by developers to create parachains that make up the Polkadot and Kusama networks. There also is a growing number of chains built with Substrate that are outside of Polkadot’s shared security infrastructure.

In Dune, Substrate tables model the activity of Substrate chains and are ingested hourly. All Substrate chains share identical schemas. For documentation purposes, we use polkadot.<tables> to showcase how to use all other <substrate_chain>.<tables>.

​

Onboarding Status

Both Polkadot and Kusama relay chains and several leading parachains have been onboarded as of March 2024. All Polkadot system chains and Polkadot parachains (as of early 2024) are being onboarded in early Q2 2024. Modeling EVM Activity within Substrate is planned in mid to late Q2 2024.

​

Substrate Tables

All tables are organized by a temporal variable (block_time or ts). Because Substrate is organized by pallets, several of the key tables (calls, events and extrinsics) are usually filtered by the pallet section and method (call_section and call_method for calls, or section and method for events and extrinsics). Decoded data is available in decoded_data for these tables in JSON form.

​

Sample Queries

As Substrate chains use SS58 Addresses, the following Dune function is very useful:

• SS58 functions: from_ss58(varchar) + to_ss58(varbinary, int)

​

overview of recent blocks

SELECT DATE(block_time) AS logDT,
       count(distinct number) numBlocks,
       MAX(block_time) AS lastBlockTime,
       MIN(number) AS b0,
       MAX(number) AS b1,
       (MAX(number) -MIN(number) - count(distinct number)) + 1 as missing
FROM polkadot.blocks
WHERE block_time > DATE('2024-01-01')
GROUP BY 1
order by 1 desc;

​

popular pallets

Query the most popular pallets and their methods.

select count(distinct signer_ss58) numUsers, count(*) numCalls, call_section, call_method from polkadot.calls
where call_section not in ('timestamp', 'utility', 'proxy')
group by call_section, call_method order by count(*) desc

​

frequent events

Query the most frequent events, excluding system events.

select  count(*) numEvents, concat(section, ':', method)  as sm from polkadot.events
where section not in ('system')
group by 2 order by count(*) desc

​

other chains

To query other chains, replace polkadot with the chain name, e.g. kusama, interlay, astar etc.

SELECT
  date_format(block_time, '%Y-%m') as monthDT,
  call_method,
  COUNT(DISTINCT signer_ss58) AS numUniqueActiveUsers,
  COUNT(Distinct extrinsic_id ) AS numCalls
FROM
  assethub.calls
WHERE
  call_section IN ('nfts')
  AND call_method IN ('buyItem', 'mint')
  AND block_time >= TIMESTAMP '2023-01-01'
GROUP BY
  1, 2
ORDER BY
  1, 2;

​

JSON extraction

You will often need to extract data from JSON fields. Here is an example of how to extract data from the events table.

SELECT
    E.block_time,
    JSON_VALUE(E.data, 'strict $[0]') as account,
    JSON_VALUE(E.data_decoded, 'strict $[0].address') as pub_key,
    CAST(JSON_VALUE(E.data, 'strict $[1]') AS SMALLINT) as asset_id_1,
    CAST(JSON_VALUE(E.data, 'strict $[2]') AS SMALLINT) as asset_id_2,
    (JSON_VALUE(E.data, 'strict $[3]'))as amount_1,
    (JSON_VALUE(E.data, 'strict $[4]'))as amount_2,
    (JSON_VALUE(E.data, 'strict $[5]'))as hub_in,
    (JSON_VALUE(E.data, 'strict $[6]'))as hub_out,
    (JSON_VALUE(E.data, 'strict $[7]'))as asset_fee,
    (JSON_VALUE(E.data, 'strict $[8]'))as protocol_fee,
    A1.decimals as decimals_1,
    A1.ticker as ticker_1,
    A2.decimals as decimals_2,
    A2.ticker as ticker_2,
    method
    FROM hydradx.events E
    LEFT JOIN query_3482301 A1 on A1.asset_id=CAST(JSON_VALUE(data, 'strict $[1]') as INT)
    LEFT JOIN query_3482301 A2 on A2.asset_id=CAST(JSON_VALUE(data, 'strict $[2]') as INT)
    WHERE section='omnipool' and method in('SellExecuted', 'BuyExecuted')