Cardano on BigQuery: scalably querying Cardano’s authenticated blockchain data on BigQuery

[GENERAL] Name and surname of main applicant

Thomas Kaliakos

[GENERAL] Are you delivering this project as an individual or as an entity (whether formally incorporated or not)

Entity (Not Incorporated)

[GENERAL] Please specify how many months you expect your project to last (from 2-12 months)

12

[GENERAL] Please indicate if your proposal has been auto-translated into English from another language.

No

[GENERAL] Summarize your solution to the problem (200-character limit including spaces)

This project addresses the challenge of providing free and open data access to Cardano’s blockchain. We export all onchain data to BigQuery and back validate it creating a proof of data authenticity.

[GENERAL] Does your project have any dependencies on other organizations, technical or otherwise?

No

[GENERAL] If YES, please describe what the dependency is and why you believe it is essential for your project’s delivery. If NO, please write “No dependencies.”

No dependencies.

[GENERAL] Will your project’s output/s be fully open source?

Yes

[GENERAL] Please provide here more information on the open source status of your project outputs

All source code for the update and monitoring processes will be openly available under the Apache v2.0 license. Additionally, our documentation will be open source, featuring comprehensive guides on data querying, along with practical examples and code snippets for connecting to BigQuery. We will also make the results of our data validation publicly accessible, ensuring transparency and reliability.

[SOLUTION] Please describe your proposed solution.

This project addresses the challenge of providing free and open data access to Cardano’s blockchain which constantly grows and requires increasing technology to be thrown at the task. Because the network has agreed on the finalisation of the on-chain data, these data can be shared with everybody in its most practical and cheapest form, trustless. Our back validation procedure builds a proof of data authenticity of the exported data in BigQuery.

[IMPACT] Please define the positive impact your project will have on the wider Cardano community.

This project will have a positive impact on the Cardano community as it provides an accessible and affordable data basis to launch DApps, web3, NFT, transaction explorers, on-chain analytics, and other types of data driven projects with ease.

It can also empower any member of the Cardano community with basic SQL skills to interrogate the Cardano blockchain for specific information of interest.

[CAPABILITY & FEASIBILITY] What is your capability to deliver your project with high levels of trust and accountability? How do you intend to validate if your approach is feasible?

We have more than two years experience with our own developed Db-Sync Enterprise protocol which minimises downtime of a Db-Sync pipeline.

The export of the Cardano blockchain data to BigQuery has been running for ten continuous months without interruption, until June 2023.

The project team covers all aspects required to achieve the stated goals.

[Project Milestones] What are the key milestones you need to achieve in order to complete your project successfully?

M1: create infrastructure for the project (1 month)

This process entails setting up the servers to run the Cardano node, db-sync, and a Postgres database. The setup includes configuring a fully functional db-sync pipeline, including the mentioned components. The criteria for acceptance will focus on ensuring this pipeline runs smoothly, testing failover capabilities, and implementing comprehensive monitoring.

M2: setup of the export and its continuous processing (1 month)

This milestone involves setting up the export process from db-sync to BigQuery.

The export is split into 2 separate processes: exporting data at the end of every epoch and exporting data every 30 minutes.

The acceptance criteria will testing and validating that both the continuous and the end-of-epoch update, export successfully the data to BigQuery.

M3: deep comparison to create authoritative data equivalence with Cardano’s blockchain (1 month)

This milestone involves setting up the deep comparison process: we will compare the data exported in BigQuery with the data in db-sync using hashing and creating a proof of data authenticity.

Acceptance criteria would be running the deep comparison process successfully for all past epochs and having all the exported data BigQuery validated.

Final Milestone: fully document the update process and the data quality monitoring (1 month)

This milestone involves creating extensive and descriptive documentation of the schema, the export process as well as the monitoring process.

The milestone output will be:

• Documentation of all the data in BigQuery
• Documentation of the update process process
• Documentation of the deep comparison process
• Example code of connecting to BigQuery an querying the data
• Example dashboards/data analyses based on the data
• Pre-defined views for popular queries

[RESOURCES] Who is in the project team and what are their roles?

Alexander Diemand (cardanobigquery https://www.linkedin.com/in/axeld/): architecture & design, project management, communication, documentation

Thomas Kaliakos (thomaska - https://www.linkedin.com/in/tkaliakos/): data engineering, data quality responsibility, documentation

Bitseat Tadesse (bitseatt - https://www.linkedin.com/in/bitseat/): data science, social networks, documentation

[BUDGET & COSTS] Please provide a cost breakdown of the proposed work and resources.

B1: 50 PD for architecture, design, project management, documentation, communication

B1.1: 10 PD architecture & design

B1.2: 10 PD project management

B1.3: 10 PD documentation

B1.4: 20 PD communication

B2: 20 PD for systems engineering (devops)

B2.1: 10 PD infrastructure setup (redundant hardware, high-availability)

B2.2: 10 PD process monitoring, alerting, mitigation procedures

B3: 55 PD for data engineering

B3.1: 5 PD PostgreSQL optimisations

B3.2: 10 PD BigQuery maintenance

B3.3: 20 PD Update process

B3.4: 20 PD Deep comparison process (back validation)

B4: Infrastructure costs

B4.1: $680 per month for redundant server hardware

(PD = person day; 8 hrs/day; 1 hr = $90)

Sum PD = 125 person days

At rate $90/h, 8 hrs/day: Sum budget PD = $90,000

12 months running costs: Hardware $680 x 12 = $8160

Total Budget: $98,160

[VALUE FOR MONEY] How does the cost of the project represent value for money for the Cardano ecosystem?

Blockchain data is by its definition equal for all participants of the network. So it makes sense to share these data in their most practical form such that each participant can independently work with it. We believe that SQL is the most accessible way of querying data and everybody will find a way in their own setup to connect and query from the BigQuery dataset which is always on.

Trustless data querying is enabled by our back validation which proves that the data in BigQuery really represents the on-chain Cardano blockchain.

Running the complete Db-Sync pipeline amounts to costs of several hundred dollars per month. On the other hand, BigQuery offers a free monthly quota of 1 TB queried data and usually incurs no costs if used sparingly.

[IMPORTANT NOTE] The Applicant agrees to Fund Rules and also that data in the submission form and other data provided by the project team during the course of the project will be publicly available.

I Accept