[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

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