Bringing Data-Driven Decision Making to the Shibarium Ecosystem: K9 Finance DAO’s Shibarium BigQuery Data Pipeline
February 13th, 2025

Preface

"Data is the new oil."
— Clive Humby

Blockchain technology’s real power comes not just from decentralization, but from the transparency of its public, immutable ledger. Still, transparency alone isn’t enough—data must be accessible and digestible to create true value.

K9 Finance is a decentralized, autonomous organization (DAO) in the true spirit of decentralization. The DAO is focused on producing decentralized finance solutions to the Shiba Inu layer-2 blockchain called Shibarium that settles on Ethereum.

At K9 Finance DAO, our community has spent the past couple of months digging into blockchain data. Our paws got a little dirty along the way, but we struck black gold: a comprehensive pipeline that unlocks Shibarium blockchain data using Google BigQuery. Today, we’re making this dataset publicly available so everyone can fetch a bone from this treasure trove.


K9 Finance DAO Helps Unlocking Shibarium Blockchain Data with Google Cloud
K9 Finance DAO Helps Unlocking Shibarium Blockchain Data with Google Cloud

Introduction & Context

K9 Finance DAO emerged in early 2024 when our community of blockchain enthusiasts spotted a gap in the Shibarium ecosystem: the absence of a liquid staking protocol. In recent years, liquid staking has become a cornerstone of DeFi and a vital component for all proof-of-stake (PoS) blockchains to bring better security, liquidity, and the ability to create more advanced decentralized financial products on top of this core infrastructure. Seizing the opportunity, we set out to build a solution.

Liquid Staking Explained

Liquid staking unlocks capital efficiency by allowing users to stake tokens for network security while still keeping them deployable in other DeFi applications. In the Shibarium network, BONE tokens—used both as gas and security bonds with Validators—are staked with validators, earning yield but tying up the principle. By issuing a receipt token (a Liquid Staking Derivative or LSD) for staked BONE, liquid staking removes that opportunity cost; allowing the user to both secure the network and explore other decentralized financial opportunities with their tokens.
Liquid staking unlocks capital efficiency by allowing users to stake tokens for network security while still keeping them deployable in other DeFi applications. In the Shibarium network, BONE tokens—used both as gas and security bonds with Validators—are staked with validators, earning yield but tying up the principle. By issuing a receipt token (a Liquid Staking Derivative or LSD) for staked BONE, liquid staking removes that opportunity cost; allowing the user to both secure the network and explore other decentralized financial opportunities with their tokens.

Since the community launched our validator in July 2024 and the liquid staking platform in September 2024, we’ve grown to become the largest project on Shibarium by total value locked (TVL), delegated BONE to our validator, and even earned an official partnership with Shiba Inu as displayed prominently on their core website.

The community’s success on-chain led us to discover another gap: while Ethereum boasts a rich ecosystem of SQL-queryable public datasets (think Dune Analytics), Shibarium’s data remained woof—until now. Using Google Cloud’s data warehousing and big data tools, we developed a robust solution for ingesting, decoding, analyzing, and visualizing Shibarium blockchain data. The result? A freely available public BigQuery dataset that brings unprecedented clarity to Shibarium’s on-chain activity.

In this post, we’ll walk you through our journey—from the challenges we faced to the solutions we developed using Google Cloud tools. Whether you’re a developer, analyst, or blockchain enthusiast, you’re about to see how K9 Finance DAO is making blockchain data as accessible as a well-thrown stick.


Choosing Our Tech Stack

Why Google Cloud?

Our adventure began at the Consensus 2024 Blockchain Conference in Austin, Texas. As we prepared to launch our K9 Finance DAO-managed validator, we needed an infrastructure partner that could match our ambition. While our developer, Shima, initially favored AWS, as they had a strong presence hosting the hackathon, a last-minute chat with the Google Cloud team on the main conference floor shifted our course. Google's expansive vision for Web3—with their public BigQuery blockchain datasets, Blockchain Node Engine, RPC services, and supportive startup program—fit our needs perfectly.

This decision was transformative. Google Cloud not only powers our core infrastructure (the K9 Validator, RPC Server, knBONE Bridge, and Protocol DApp) but also provides the big data tools essential to the project. With enterprise-grade security from Identity Aware Proxy (IAP) and an excellent developer experience via Colab Enterprise and Vertex AI, Google Cloud became our trusty sidekick on this journey.

Building the Pipeline

Like puppies chasing our tails, we initially found ourselves distracted by the complexities of Shibarium’s EVM-based transaction management. We had to refocus on our main goal: making Shibarium’s “transparent” blockchain truly accessible, through better data.

K9's Vertex AI Pipeline Tasks
K9's Vertex AI Pipeline Tasks

Taking a data scientist’s approach, we broke our pipeline into logical, testable components using Python notebooks. Our final ETL pipeline performs these key tasks:

  1. Blocks & Transactions: Ingests blocks and transactions from our RPC node.

  2. Receipts & Logs: Fetches transaction receipts and event logs.

  3. Function Call Decoding: Decodes 4-byte function selectors and parameters.

  4. Event Decoding: Parses event signatures and decodes event parameters.

  5. Error Decoding: Extracts meaningful error messages from failed transactions.

  6. Access Lists: Resimulates transactions to track state changes.

  7. Dune Analytics Integration: Filters and uploads K9-related transaction data.

  8. Monitoring: Sends status updates and HTML reports to our Telegram channel.

Google’s data science tools—especially Colab Enterprise with its seamless BigQuery Studio integration—made it a breeze to prototype queries and debug our data in real time. Our notebooks spun up faster than a caffeinated coder on a Monday morning!


Google Cloud Infrastructure in Action

ETL Pipeline Development Process
ETL Pipeline Development Process

Our infrastructure leverages several key Google Cloud components:

Colab Enterprise

Serving as our R&D lab, Colab Enterprise let us prototype and debug our data ingestion and decoding in a highly integrated environment. The side-by-side view of Python and SQL in BigQuery Studio was a game-changer compared to our previous Vertex AI Workbench setup.

Colab Enterprise (BigQuery Studio)
Colab Enterprise (BigQuery Studio)

Vertex AI Pipelines

Once our modules stabilized, we used Vertex AI Pipelines (with Kubeflow under the hood) to orchestrate our steps in a serverless manner. This gave us:

  • Automated Scheduling: The pipeline runs daily to provide fresh data.

  • Parallel Task Execution: Breaking the pipeline into components sped up our workflow.

  • Scalability & Cost Savings: A serverless approach meant minimal dev‑ops overhead and lower costs compared to dedicated orchestration tools.

Vertex AI
Vertex AI

BigQuery & BigFrames

BigQuery is the linchpin of our data strategy—paws-down the best big data warehouse out there (and we’re biased!). It stores Shibarium’s historical data, from blocks and transactions to logs and decoded events. With BigFrames, we treat BigQuery tables like Pandas DataFrames, pushing computation to BigQuery and avoiding local memory constraints.

BigQuery
BigQuery

Cloud Storage + Telegram

All logs, error reports, and HTML summaries are stored in Cloud Storage. A quick integration with the Telegram Bot API means our team gets real-time notifications with links to pipeline outputs—making debugging and performance tuning as easy as a walk in the park.

Cloud Storage & Telegram Bot API
Cloud Storage & Telegram Bot API

Challenges: Not Exactly a Walk in the Park

The Data Deluge

Shibarium generates over 17,000 blocks a day, with dozens of transactions and hundreds of logs per block. Our initial, naïve approach—processing each block individually—was as slow as a sleepy pup, clocking in at around 4 seconds per block. With a 5‑second block time, this meant our pipeline was woefully behind schedule.

Decoding Raw Blockchain Data

Our mission was to transform raw hex data into something human-readable. Leveraging APIs like ethersface.io and 4byte.directory, we decoded contract function calls, translated event signatures, and extracted useful error messages. This allowed us to turn gibberish into insights that even the uninitiated could understand.

Performance Bottlenecks & Optimizations

To speed up our pipeline, we:

  • Batched RPC Calls: We ditched web3.py’s single-call approach in favor of batching 100 RPC calls at a time using Python’s requests library.

  • Embraced Parallelization: We experimented with asyncio multi-threading, adn multi-processing. While asynchronous requests helped with I/O-bound tasks, multi-processing was essential for CPU-heavy decoding work.

  • Implemented Caching: A thread-safe cache manager reduced redundant API calls by storing decoded 4-byte signatures locally.

  • Reworked Table Modeling: We shifted from monthly to daily partitioning and added clustering on common join columns. Materialized views for frequently accessed data cut down on repetitive decoding tasks.

The result? We reduced block ingestion time from ~4 seconds per block to about 20 blocks per second, reducing daily pipeline runtime from ~20 hours to 30 minutes—turning our pipeline into a near real‑time data-fetching machine.


The Benefits: A Walk in the Park

With Google Cloud, our development process became as smooth as a leisurely stroll. Freed from infrastructure headaches, we could focus on uncovering insights for our products and community. By offloading heavy computations to BigQuery and using smart table partitioning and caching, we’re now serving up blockchain data with minimal lag—and at a fraction of the cost.

Paws for Thought: Our journey has not only optimized our data pipeline but has also enhanced transparency for the Shibarium community. Better data means better decision-making, and that’s something we’re excited to share with everyone. More importantly, this innovation speaks to the ethos of blockchains - transparent, human-readable data for everyone - not just the technically-minded.

Join the Pack: We invite developers and enthusiasts alike to explore Google Cloud’s suite of tools. Whether you’re digging into blockchain data or tackling another big data challenge, these tools can unleash new possibilities and make your journey as rewarding as ours.


Community Impact & Looking Ahead

K9 Finance DAO is a truly decentralized autonomous organization, with the community leading the charge. Since we announced this project and showcased initial dashboards on a Twitter space, DAO members have been actively crafting their own queries and insights. Other Shibarium projects have also shown keen interest in leveraging our public dataset for decision-making, growth, and transparent marketing efforts.

As we continue our journey with Google Cloud, we've only scratched the surface of what's possible with big data and AI. The original vision for K9 Finance DAO includes exciting AI-driven optimizations, and we're exploring collaborations with existing AI projects that leverage Vertex AI and Google Cloud tools. These advancements will play a pivotal role in our transition towards an even more decentralized organization, empowering the DAO with AI capabilities for future prioritization and decision-making. With exciting previews like Google’s Gemini 2.0 Flash model, the future looks bright—and we’re ready to fetch it.

So, whether you’re a developer, analyst, or a die-hard blockchain doggo, we hope you find our dataset as useful as a well-thrown stick. After all, at K9 Finance DAO, we believe that when data is accessible, everyone can have a ball!



Interested in learning more about K9 Finance DAO’s initiatives? Visit our official website, jump into our Telegram, or follow us on Twitter for the latest updates and community discussions.

Official Links:      🌐 Website      ➤ Telegram      𝕏 Twitter/X      🗳️ Governance Forum


Mint a copy of this blog post on Base Chain (free mint: any fees reflect mirror.xyz costs).

Subscribe to K9 Finance DAO | Shima
Receive the latest updates directly to your inbox.
Nft graphic
Mint this entry as an NFT to add it to your collection.
Verification
This entry has been permanently stored onchain and signed by its creator.
More from K9 Finance DAO | Shima

Skeleton

Skeleton

Skeleton