In this blog post, we aim to demystify the fundamental concepts necessary for utilizing Ortege Studio and Ortege Lakehouse effectively, focusing on extracting valuable insights from the Stacks blockchain. Our journey will navigate through the unique medallion layer architecture, crucial tables, views, and technical nuances, enriching your understanding of Stacks' Proof of Transfer consensus algorithm and its interplay with Bitcoin data. I will explain the basic concepts you need to know and teach you how to leverage Ortege Studio and the underlying Ortege Lakehouse to get any information you need from the Stacks blockchain.
Ortege Documentation and Studio Dashboard
For a hands-on learning experience, we'll leverage comprehensive Ortege documentation and a practical Dashboard in Ortege Studio, which houses all queries and charts discussed. This approach not only solidifies your learning but also equips you with the tools to explore and analyze Stacks data efficiently.
Understanding Ortege's Medallion Architecture
The Medallion architecture is a cornerstone of Ortege Lakehouse, designed to cater to diverse data requirements across different levels of complexity. It's structured into Bronze, Silver, and Gold layers, each tailored to specific data transformation and analysis needs. For an in-depth understanding, visit our Medallion architecture documentation.
Navigating Table Environments
It's crucial to differentiate between test and prod tables within our environment. While test tables are valuable for experimentation, they are subject to change. Conversely, prod tables offer stability and reliability for your data analysis projects. This distinction underpins our data management workflow, ensuring data integrity from testing to production deployment.
Constructing a Training Dashboard in Ortege Studio
Adhering to the wisdom of Benjamin Franklin, "Tell me and I forget, teach me and I may remember, involve me and I learn," we introduce the Stacks Training Dashboard. This step-by-step guide through Ortege Studio's functionality will enhance your journey into Stacks analytics, starting from raw data exploration to advanced analysis techniques.
Leveraging Spark SQL for Data Analysis
Throughout this guide, we emphasize the use of Spark SQL dialect, a powerful tool for data manipulation and analysis. An exemplary query illustrates how to aggregate transaction fees by month, offering a glimpse into the practical application of Spark SQL in blockchain analytics.
Key Takeaways
By the end of this blog post, you'll gain valuable insights into leveraging Ortege Lakehouse and Ortege Studio for comprehensive Stacks blockchain analysis. Our goal is to equip you with the knowledge and tools to explore blockchain data confidently, encouraging a hands-on approach to learning and discovery.
Generating Insightful Charts Across All Layers
Leveraging Ortege Studio, we'll dive into the practical aspects of generating charts across the Bronze, Silver, and Gold layers of our Medallion Architecture. This hands-on approach will not only reinforce your understanding of Stacks blockchain data but also enhance your analytical skills.
Bronze Layer: Unveiling the Raw Data
At the Bronze layer, we're dealing with raw, unfiltered blockchain data. This layer is ideal for those who desire the most granular view of blockchain activities.
Chart 1: Current Block Number
- Objective: Display the latest block number on the Stacks blockchain.
- Chart Type: Big Number Chart
- Query:
SELECT MAX(block_height) FROM db_stacks.tbl_prod_br_blocks; - Purpose: Provides an at-a-glance view of the blockchain's current height, indicating its growth and activity level.
Chart 2: Cumulative Transaction Fees
- Objective: Show the cumulative transaction fees over time.
- Chart Type: Bar Chart
- Query: SELECT date_trunc('month', block_time) AS month, SUM(fee) AS total_feesFROM db_stacks.tbl_prod_br_transactionsGROUP BY monthORDER BY month;
- Purpose: Highlights the economic activity on the blockchain by visualizing fee contributions from transactions.
Silver Layer: Enhanced Data Accessibility
Moving to the Silver layer, we focus on datasets that have been cleaned and structured for better accessibility and readability.
Chart 3: Total Deposits for stSTX
- Objective: Aggregate total deposits for stSTX over time.
- Chart Type: Line Chart
- Purpose: Offers insights into stSTX's popularity and the community's participation in stacking activities.
Chart 4: Daily Active Users for ALEX
- Objective: Track the daily active users interacting with the ALEX dApp.
- Chart Type: Line Chart
- Purpose: Measures user engagement and the dApp's traction within the Stacks ecosystem.
Gold Layer: Delving into Complex Datasets
The Gold layer represents the apex of data analysis within Ortege Lakehouse, featuring datasets ripe for advanced analytical endeavors.
There will be a follow up post focusing on the Gold layer and some of the unique insights to be gleaned.
For now though we hope this has served as a good introductory post about some of the powerful charts and Dashboards you can create leveraging Stacks data.