[substrate-etl](https://github.com/colorfulnotion/substrate-etl) is a large scale index of Polkadot + Kusama and their parachains based on [Polkaholic.io](https://polkaholic.io), a multichain block explorer/indexer.  Using this data warehouse, anyone can query decoded blocks, extrinsics, events, calls across both ecosystems.  

We onboarded Polkadot Data into Google Public Datasets, announced in September 2023:

* [Enhancing Google Cloud’s blockchain data offering with 11 new chains in BigQuery](https://cloud.google.com/blog/products/data-analytics/data-for-11-more-blockchains-in-bigquery-public-datasets)
* [Polkadot - Harness the power of Polkadot's BigQuery Public Dataset](https://twitter.com/Polkadot/status/1707052392712212676)

The substrate-etl index results in two large datasets (crypto_polkadot and crypto_kusama) covering the 2 relay chains and over 70 parachains.  This represents Polkadot/Kusama alongside other Web3 ecosystems and supports complex large scale analyses with SQL using BigQuery's lightning-fast query performance on extremely large datasets.   Having substrate-etl enables Dune-like analytics to guide business intelligence, inform engineering decision making, and showcase ecosystem growth. 

Power users include [Parity Data](https://forum.polkadot.network/t/select-from-polkadot/2593) (led by [Karim Jedda](https://forum.polkadot.network/u/karim) and [Pavla Mijic](https://forum.polkadot.network/u/pavla)), [Subwallet/DotInsights](https://twitter.com/dotinsights_xyz), [Web3metrics](https://web3metrics.com),
[Dolpha.com](https://dolpha.com/), [ChainViz](https://chainviz.app/) and many data analysts within parachains and outside the ecosystem.

# Quick Start

For a full guide, see [substrate-etl](https://github.com/colorfulnotion/substrate-etl), but you can get started with the following Analytics Hub links:

* [Polkadot on Google Analytics Hub](https://console.cloud.google.com/bigquery/analytics-hub/exchanges/projects/974572213039/locations/us/dataExchanges/polkadot_18bca7589e7/listings/polkadot_and_polkadot_parachains_18bca877a0a)
* [Kusama on Google  Analytics Hub](https://console.cloud.google.com/bigquery/analytics-hub/exchanges/projects/974572213039/locations/us/dataExchanges/polkadot_18bca7589e7/listings/kusama_and_kusama_parachains_18bca8f79fb)

# Roadmap

In addition to continuing support for substrate-etl/Polkaholic.io for existing and future chains, our plans for 2024 are to:
* chart a course within the [Decentralized Futures](https://futures.web3.foundation/) for 2024, supporting Web3 Foundation's [Data Analysis Tools for Substrate-based Blockchains](https://github.com/w3f/Grants-Program/blob/master/docs/RFPs/data_analysis_tools.md) (e.g. [traces](https://github.com/w3f/Grant-Milestone-Delivery/pull/1038) and [staking](https://github.com/w3f/Grant-Milestone-Delivery/pull/1045)), chart a course for substrate-etl + Polkaholic APIs to support [Dune Analytics Style Data Service for Polkadot/Kusama](https://forum.polkadot.network/t/dune-analytics-style-data-service-for-polkadot-kusama/271/1) with leading teams in the ecosystem 
* develop + support a [Polkadot Builder Collective](https://forum.polkadot.network/t/polkadot-builder-collective/4755/5) with a "builders supporting builders" orientation
* build support for CoreJam+CorePlay+DA and extend support for [ink!/WASM contracts](https://github.com/use-inkubator/Grant-Milestone-Delivery/blob/master/deliveries/chainide-polkaholic-integrated-wasm-contract-explorer-integration-milestone_1.md)
* extend substrate-etl to support raw + fully-decoded EVM datasets (transactions+logs), enabling [Substrate+EVM hybrid chain analytics](https://forum.polkadot.network/t/substrate-vs-evm-ecosystem-comparison/4454), as more parachains incorporate [hybrid chain designs](https://www.rob.tech/blog/hybrid-chains/)

# Proposal Details

Requested: 99,025 DOT

[Proposal Details here](https://docs.google.com/document/d/1F9nqhD-cBY43DlP5oDSRR1YvKvY2yraNSKOraxP3_xU/edit#heading=h.v6is2ta0txr1)

[Proposal Costs here](https://docs.google.com/spreadsheets/d/1c7-3kdfAjWdtWTkx2anEQgYpToUiWo3vav1WrapFuEI/edit#gid=1947784429)

# About Colorful Notion

Colorful Notion is led by [Sourabh Niyogi](https://forum.polkadot.network/u/sourabhniyogi) and [Michael Chung](https://forum.polkadot.network/u/mkchungs), based in the SF Bay Area.  We have been active in the Polkadot ecosystem over the last 2 years and look forward to building with others in CoreJam/Polkadot 2.0’s “map reduce” ubiquitous computing future. 

We are grateful for everyone who has helped us and hope we can pay it forward by helping others

bountyId

Problem Statement and Audience:
- Problem: There is a need for a data warehouse that provides indexed data for Polkadot and Kusama ecosystems, allowing for complex large-scale analyses.
- Audience: Power users, data analysts, and teams within the Polkadot and Kusama ecosystems.

Significance:
- The substrate-etl index provides two large datasets covering Polkadot, Kusama, and over 70 parachains, enabling business intelligence, engineering decision making, and showcasing ecosystem growth.

Proposed Solution:
- The substrate-etl index, along with Polkaholic APIs, supports Dune Analytics-style data services for Polkadot and Kusama, and plans to support Web3 Foundation's Data Analysis Tools for Substrate-based Blockchains.

Expected Outcomes:
- Improved data analytics and insights for the Polkadot and Kusama ecosystems.
- Support for builders and developers within the ecosystem.
- Extension of substrate-etl to support raw and fully-decoded EVM datasets for hybrid chain analytics.

#509 substrate-etl Retroactive funding: Q3+Q4 2022-Q1+Q2 2023

No Active Proposals