This report evaluates Google Gemini 3 Flash, OpenAI GPT 5.1/5.2, Anthropic Claude Opus 4.5, and Anthropic Claude Sonnet 4.5 using the Top 25 Type Inhabitation Dataset. The primary objective was to assess the capability of Large Language Models (LLMs) to autonomously generate valid Programmable Transaction Blocks (PTBs) for Move smart contracts on the Sui mainnet.

The most significant finding of this study is the emergence of a "Fog of War" differentiator.

The data reveals that the deciding factor between model tiers was not syntax proficiency, but epistemic management—the ability to act methodically in the face of incomplete information.

• Tier 1 (Claude Opus 4.5, GPT 5.1, GPT 5.2) models treated missing information as a search problem. They actively utilized progressive disclosure to build a complete mental map of the package dependencies before attempting construction. Consequently, these models achieved a 100% success rate.

• Tier 2 (Claude Sonnet 4.5, Gemini 3 Flash/Pro) models treated missing information as a barrier. When the full context was not immediately visible, these models reacted with hallucination (guessing) or abandonment (stopping early), resulting in success rates below 50%.

Binary Reliability Cliff Crucially, the performance data exhibits a radical step-function rather than a gradient. There were no models in the "70-90%" range. This suggests that Move Smart Contract Inhabitation is a threshold capability: once a model possesses sufficient epistemic resilience to manage the "Fog of War," it solves the problem completely. Below that threshold, reliability collapses to unusable levels.

Benchmark Context: The Inhabitation Task

Unlike benchmarks that provide complete source code context, the Type Inhabitation benchmark evaluates a model's ability to operate in a low-information environment. The model is placed in a scenario similar to a developer interacting with a closed-source or unverified package: it is given only a package address and a goal.

To succeed, the model must autonomously discover interfaces, resolve type dependencies, and construct a valid Programmable Transaction Block (PTB). This design creates a "dynamic context retrieval" challenge, testing the model's reasoning and search capabilities rather than its ability to recall memorized patterns from open-source repositories.

Technical Walkthrough: The Escrow Example

To illustrate the technical complexity, consider a specific test case from the dataset. The model is initialized with the following prompt:

Goal: Inhabit the type 0x0f8343240d42fbefdb499f2c316f939aa168bf7b29ab63c31bf6c4bc0ba97fe0::escrow::Escrow.

The model has no prior knowledge of this package. The evaluation proceeds in specific technical phases:

1. Interface Discovery: The model's first action is to query the interface for the target module. It executes get_module_interface(package="0x0f83...fe0", module="escrow"). The system returns a JSON representation of the bytecode interface, revealing the constructor function signature:

   public fun create(
       arg0: Coin<0x2::sui::SUI>,
       arg1: 0x0f8343240d42fbefdb499f2c316f939aa168bf7b29ab63c31bf6c4bc0ba97fe0::lock::Lock
   ): Escrow
   

2. Recursive Dependency Resolution: The model identifies that while Coin<SUI> is a standard primitive, the second argument requires a custom type: lock::Lock. This type resides in a separate module within the same package. The model must recognize this dependency and trigger a secondary query: get_module_interface(..., module="lock").

3. Dependency Graph Construction: Upon analyzing the lock module, the model locates the constructor for the Lock struct. It then synthesizes a Directed Acyclic Graph (DAG) of operations:

   • Node 1: SplitCoins (Move primitive) → Generates Coin<SUI>.

   • Node 2: Call lock::create() → Generates Lock.

   • Node 3: Call escrow::create(Node 1, Node 2) → Generates Escrow.

4. PTB Generation & Verification: Finally, the model serializes this logic into a JSON-based PTB schema. The benchmark harness then dry-runs this transaction against the Sui Mainnet state. Success is defined strictly: the transaction must execute without VM errors (e.g., Effects.status.status == success).

This workflow demonstrates that "inhabitation" is not merely about writing code; it is about autonomously traversing a graph of type constraints and determining the correct sequence of on-chain operations to satisfy them.

Technical Methodology

The benchmark environment is designed to ensure reproducibility and fair comparison across different models. The complete evaluation harness, including the Rust-based extractor and Docker configurations, is available in the project repository.

Experimental Constraints

To rigorously test efficiency and decision-making, each evaluation is subject to strict constraints:

• Single Attempt: The model is given only one opportunity to inhabit a specific package type. There are no "retries" if the final transaction fails.

• Iteration Limit: The model is restricted to a maximum of 10 turn-based cycles (thought/action loops). Within this budget, it must discover all necessary interfaces, resolve dependencies, and generate the final transaction. If it fails to produce a valid result within 10 turns, the run is marked as a failure.

Bytecode-Derived Interface Analysis

Source code is purposely withheld to simulate a rigorous "black box" environment. The system extracts interfaces directly from on-chain bytecode, presenting the model with raw struct definitions and function signatures. This tests the model's fundamental understanding of Move's type system and linear logic.

Isolated Execution Environment

Evaluations are conducted within ephemeral Docker containers. This approach ensures:

• State Consistency: The environment is reset after every run, preventing models from retaining information between attempts.

• Uniform Tooling: All models interact with the chain using the same set of Rust extractors and Sui CLI binaries.

• Realistic Network Conditions: Interaction occurs against the live Sui Mainnet, subjecting models to real-world latency and data structures.

Progressive Disclosure

A "Progressive Disclosure" strategy is employed where models must explicitly request module interfaces. This allows for observation of the model's search efficiency. It enables tracking whether a model navigates the dependency tree logically—requesting only what is necessary—or if it struggles to form a coherent mental map of the package structure.

Results

Performance Tiers

The results indicate a binary distribution in capabilities.

• Tier 1 (High Reliability): The GPT-5 series and Claude Opus 4.5 demonstrated robust reasoning capabilities, effectively navigating the "unknown" environment without hallucinations or logical errors.

• Tier 2 (Emerging Capability): Other models showed promise on simpler tasks but faced significant difficulties when dependency chains extended beyond a single layer.

Analysis of Behavioral Patterns

To better understand the divergence in performance, model behaviors are analyzed across two dimensions: by Model Family and by Performance Tier.

Comparative Analysis by Model Family

OpenAI (GPT-5 Series)

• Search Strategy: Methodical and exhaustive. These models systematically request every potential dependency before attempting construction, favoring complete information over speed.

• Error Mode: Over-caution. Occasionally, they refuse to proceed if a specific permission object is missing, marking the task as "uninhabitable" rather than attempting a partial solution.

• Latency Profile: High (50-60s), reflecting the exhaustive verification process.

Anthropic (Claude Series)

• Search Strategy: Precision-targeted. These models request only the immediate dependencies. Opus resolves deeper layers effectively, while Sonnet often stops early if the path is not obvious.

• Error Mode: Under-exploration (Sonnet). Sonnet tends to abandon complex dependency trees early. Opus shows no significant error mode.

• Latency Profile: Varied. Opus is highly efficient (~24s), while Sonnet is slower (~40s) due to less decisive planning.

Google (Gemini Series)

• Search Strategy: Heuristic and speculative. These models frequently attempt to "guess" interfaces or parameters to save steps, leading to high speed but high hallucination rates.

• Error Mode: Hallucination. They frequently invent function names or misinterprets parameter types (e.g., passing strings where objects are required).

• Latency Profile: Bimodal. Flash is extremely fast (~12s) due to skipping verification, while Pro is slow (~45s) due to analysis loops.

Comparative Analysis by Performance Tier

Context Management

• Tier 1 (Opus 4.5, GPT 5.1/5.2): Maintains a dynamic map of the package structure. These models can backtrack and revise plans without losing context as new modules are revealed.

• Tier 2 (Sonnet 4.5, Gemini Flash/Pro): Struggles with fragmented views. These models often fail to integrate new information with previous findings, "forgetting" dependencies discovered in earlier steps.

Dependency Resolution

• Tier 1: Employs a depth-first search approach. They successfully resolve nested dependencies (A requires B, B requires C) and treat the graph as a recursive logical puzzle.

• Tier 2: Relies on shallow pattern matching. While effective at direct instantiations (A requires integer), they fail when dependencies are recursive or require cross-module lookups.

Self-Correction

• Tier 1: Proactive. These models catch schema errors before submitting the transaction, verifying that arguments match expected types.

• Tier 2: Reactive or absent. They often submit invalid transactions and rely on error messages to guess the fix, or get stuck in a loop repeating the same mistake.

Conclusion

The data indicates that Tier 1 models (Claude Opus 4.5, GPT 5.1, GPT 5.2) have reached a maturity level suitable for production-grade automation of Move smart contracts. Among these, Claude Opus 4.5 stands out for achieving this reliability with the lowest latency.

The central finding of this research is that the performance gap is not driven by coding ability, but by search strategy. Tier 1 models successfully leveraged progressive disclosure to illuminate the "Fog of War," constructing valid mental models from partial information. Tier 2 models, unable to sustain this state of uncertainty, resorted to guessing or abandonment. This suggests that for autonomous agents, epistemic resilience—the ability to act methodically in the face of incomplete information—is the critical capability gap to bridge.

Time: ~1 hour | Cost: < $1

Setup: Claude Code + Factory CLI (Big shoutout for the mathematical rendering!)

Result: Mathematically rigorous proof of CDF monotonicity

I built move-gaussian, an on-chain Gaussian distribution library for Sui Move. The library provides CDF, PDF, PPF (inverse CDF), and sampling functions—core statistical primitives living natively on-chain.

I wanted to formally verify the library's mathematical properties. The sui-prover handled most verification tasks: overflow safety (45 specs), arithmetic primitives, and point evaluations. But one property stood out as both critical and difficult: proving the CDF is monotonically increasing.

Why Monotonicity Matters

A CDF (Cumulative Distribution Function) answers: "What's the probability that a random variable is less than or equal to z?" By definition, this probability must increase as z increases—you're accumulating more probability mass as you move right on the number line, never less. If a CDF implementation decreases anywhere, it's not a valid CDF.

For a Gaussian CDF specifically, monotonicity is load-bearing. It guarantees the inverse CDF (PPF) is well-defined and unique, which means sampling via inverse transform actually works. It ensures probability intervals make sense—P(a < X ≤ b) = CDF(b) - CDF(a) must be non-negative. Without monotonicity, the entire statistical machinery breaks down.

What Actually Happened

The implementation uses rational polynomial approximation: degree-12 polynomials P(z) and Q(z), evaluated via Horner's method in fixed-point arithmetic. My first attempt was direct SMT verification with sui-prover, built on Boogie and Z3.

It failed immediately. The prover explores all possible intermediate values—not just the ones our coefficients produce—and found theoretical overflow paths:

error: code should not abort
    ┌─ ./sources/math.move:154:8
    │
154 │     (a * x) / SCALE
    │        ^
    │
    =     at ./sources/erf.move:233: horner_eval_p
    =         a = 115792089237316195423570985008687907853269984665640564039458...

That a value is near u256 max—a path the real code never takes, but Z3 doesn't know that. Even switching to real arithmetic (sui-prover's to_real() bypasses overflow), Z3's nonlinear real arithmetic theory times out around degree 6-8. Our problem is degree 22.

My mathematical intuition said: stop verifying the computation, verify the algebra. For monotonicity, we need CDF'(z) ≥ 0. For a rational function P/Q, this means proving N(z) = P'(z)·Q(z) - P(z)·Q'(z) ≥ 0—an algebraic problem, not a numerical one.

I prompted Claude toward algebraic approaches. It searched external documentation and surfaced Sturm's Theorem (1829)—a classical result for counting polynomial roots in an interval. The theorem is elegant: build the Sturm sequence (polynomial remainder sequence), count sign changes at interval endpoints, and the difference equals the number of roots. If N(z) has zero roots in [0, 6] and N(0) > 0, then N(z) > 0 everywhere.

Claude generated a Python script using SymPy with exact rational arithmetic. No floating-point errors. No numerical instability. Just pure polynomial algebra: V(0) = 11 sign changes, V(6) = 11 sign changes, root count = 0, and N(0) > 0.

Theorem verified: N(z) > 0 for all z ∈ [0, 6]. Therefore CDF is monotonically increasing.

Reflection

What struck me most was Claude's role as a living encyclopedia of mathematical knowledge. It's not always 100% correct, but it seems to have an answer for everything if you ask the right question. The human provides domain expertise, quality control, and strategic direction. I knew to look for algebraic approaches when Z3 failed, and I could verify the proof structure made sense. The AI provides breadth of knowledge, implementation speed, and pattern matching across domains, surfacing Sturm's theorem, generating a working Python script in minutes, connecting dots for me while I point out the direction.

But this only works because we're not blindly trusting AI output. The proof stands on Sturm's theorem, which has 200 years of mathematical scrutiny behind it. The implementation uses Python and SymPy, decades-old tools with extensive testing, and exact rational arithmetic that avoids floating-point uncertainty entirely. The sui-prover verified the supporting facts: overflow bounds (45 specs, all passing), point evaluations, and arithmetic primitive correctness. Claude found the path; the path itself is ancient and trusted.

The formal verification took an hour, and it was simultaneously a learning experience, an application experience, and a research experiment. These categories blur together now. The distinction between "learning" and "doing" feels increasingly blurry when AI can collapse the gap between knowing something exists and implementing it immediately.

GaussianMove uses the AAA algorithm to generate near-optimal rational approximations offline, then evaluates them on-chain via Horner's method—achieving CDF error of 3.35×10⁻⁹ and PPF error of 3.11×10⁻¹³ with predictable gas costs. Sui's native sui::random then makes Gaussian sampling operationally simple inside a single transaction. This article describes the constraints, the approximation methods, and several applications once these functions are available as ordinary library calls.

On-chain, Gaussian machinery is usually pushed off to oracles, hidden inside off-chain engines, or avoided entirely because gas and fixed-point arithmetic make it challenging. GaussianMove asks a simple question:

What does it actually take to compute Φ and Φ⁻¹ on Sui when all you have are integers, fixed-point scaling, and strict gas limits?

We’ll start from the mathematical constraints, choose an approximation strategy that is compatible with those constraints, and then plug the resulting primitives into familiar objects like Black–Scholes, VaR, and Gaussian-shaped AMMs.

Deployed Packages (Sui Testnet)

GaussianMove and its companion Black-Scholes package are deployed on Sui testnet:

Repositories:

• move-gaussian — Core Gaussian library (399 tests)

• move-black-scholes — European options pricing (83 tests)

Part I: The Mathematical Problem

Why is Gaussian math hard on-chain? The difficulty is not just gas; it’s that you only get integers, fixed-point scaling, and inverse functions that amplify small errors, which constrains the approximation method choices.

Gaussian Math in Traditional Finance

Gaussian-based models are standard tools in traditional finance.

• The Black–Scholes–Merton model for European options pricing literally contains Φ(d₁) and Φ(d₂), where Φ is the standard normal CDF.

• Risk measures such as Value at Risk and Expected Shortfall often assume (log)normal returns and use the inverse CDF Φ⁻¹(α) to convert a confidence level α into a loss threshold.

• Many factor and term-structure models linearize around Gaussian assumptions even when reality is heavier-tailed.

In banks and brokerages, these functions are evaluated using high-quality numerical libraries (BLAS/LAPACK, Boost, SciPy, etc.), and their error properties are well understood. The question here is what it takes to bring that level of Gaussian math on-chain, where the only primitives are integer arithmetic and fixed-point representations.

From Off-Chain Models to On-Chain Systems

DeFi derivatives protocols already lean heavily on Gaussian-style thinking, but most of the heavy numerical work happens off-chain:

• Lyra (~$58M TVL, one of the largest on-chain options protocols) prices options with Black–Scholes-style formulas but relies on Chainlink price feeds and off-chain Greeks; it does not evaluate Φ/Φ⁻¹ directly on-chain.

• Hegic, Dopex, Rysk, and Valorem use a mix of RFQ mechanisms, oracles, and simplified pricing rules rather than full on-chain Gaussian math.

• Panoptic is explicitly oracle-free but achieves this by deriving option payoffs from Uniswap v3 LP mechanics instead of computing Gaussian functions.

• Primitive RMM-01 together with solstat is the clearest EVM example of explicit on-chain Gaussian CDF usage, but the protocol has since closed down.

In practice, most production DeFi options protocols avoid full on-chain Gaussian computation. They either use oracles to import off-chain pricing, derive prices from AMM mechanics, or accept simplified models. The few that attempted full on-chain Black–Scholes (like early Primitive) faced gas costs and complexity that limited adoption.

So why build this?

Three reasons:

1. The landscape is changing. Sui’s native randomness and lower compute costs make on-chain statistical computation more practical than on EVM.

2. Research infrastructure matters. Even if production protocols use hybrid approaches, having audited, well-documented Gaussian primitives enables experimentation. The Paradigm pm-AMM paper (2024) and Distribution Markets work show continued research interest in Gaussian-based mechanisms.

3. The mathematical problem is nontrivial. Computing Φ⁻¹(p) without floating-point is a substantive applied mathematics problem, independent of any particular application.

The fundamental question is deceptively simple: how do you compute Φ⁻¹(p) when your only arithmetic primitives are integer addition, subtraction, multiplication, and division? This is a timeless numerical analysis challenge from the 1960s—blockchain merely adds gas costs and determinism constraints.

Key Constraints for On-Chain Gaussian Math

• Fixed-point arithmetic constraints. On-chain environments (Solidity, Move, etc.) operate with integers and implicit scaling (typically WAD = 10¹⁸). This introduces overflow risk for (a * b) / SCALE, truncation error in division, and a hard floor on the smallest representable probabilities. Practical accuracy is limited by cumulative rounding, even though u256 offers ~77 decimal digits of intermediate precision.

• Randomness source. Gaussian sampling via inverse transform requires a high-quality uniform variate U ∈ (0,1). On EVM/Solana/Aptos this usually comes from external VRF providers such as Chainlink VRF or Switchboard VRF, adding multi-transaction flows, callbacks, and gas. On Sui, sui::random exposes a Random object that can be consumed inside a single transaction.

• Inverse functions amplify errors. Even if Φ(x) is approximated accurately, inverting it to obtain Φ⁻¹(p) amplifies small forward errors, especially in the tails where d/dp Φ⁻¹(p) grows large. Classical work such as Algorithm AS 241 (Wichura) and Acklam's normal quantile function emphasizes careful piecewise design and tail handling; GaussianMove follows the same philosophy with AAA-based piecewise approximations.

Fixed-Point Constraints: Working Without Floats

Before we talk about algorithms, we need to make peace with a simple fact: on-chain, there are no floats—only integers with a fixed scale. That single choice drives almost every design decision in GaussianMove.

DeFi universally uses WAD scaling: 1.0 is represented as 10¹⁸. This gives 18 decimal places of precision, sufficient for most financial calculations (basis points are 10⁻⁴), but it also means all transcendental functions (exp, ln, sqrt) must be realized as integer-based polynomial or rational approximations.

const SCALE: u256 = 1_000_000_000_000_000_000;  // 10^18

/// Fixed-point multiplication: (a × b) / SCALE
public fun mul_wad(a: u256, b: u256): u256 {
    (a * b) / SCALE  // u256 intermediate prevents overflow
}

/// Fixed-point division: (a × SCALE) / b
public fun div_wad(a: u256, b: u256): u256 {
    (a * SCALE) / b
}

Precision Hierarchy

Understanding where precision is lost is crucial for error analysis:

Layer                          Precision       Notes
─────────────────────────────────────────────────────
1. Off-chain AAA fitting       ~10⁻¹⁴         Dominated by algorithm tolerance
2. Coefficient quantization    ~10⁻¹⁸         Negligible (WAD has 18 digits)
3. On-chain Horner rounding    ~10⁻¹⁵         ~0.5 ULP per operation
4. WAD representation          10⁻¹⁸          Hard floor
─────────────────────────────────────────────────────
Current achieved:              3.35×10⁻⁹      CDF
Theoretical floor:             ~10⁻¹⁵         

The precision floor (~10⁻¹⁵) comes from accumulated rounding in Horner evaluation: each of the ~11 iterations loses approximately 0.5 ULP (unit in last place), totaling 5-10 ULP. In practical terms, the approximation method is not the bottleneck here—the fixed-point evaluation is.

Approximation Theory: Why AAA?

Once you accept fixed-point arithmetic and u256 as your playing field, the next question is how to approximate Φ and Φ⁻¹ themselves.

The Landscape of Approximation Methods

Several approaches exist for approximating Φ and Φ⁻¹:

GaussianMove uses AAA because it produces near-optimal rational approximations automatically, avoiding the need to hand-tune coefficients for each function.

The AAA Algorithm

AAA (Nakatsukasa et al., 2018) is a greedy algorithm that iteratively builds a barycentric rational approximation:

r(x) = Σⱼ (wⱼ × fⱼ) / (x - zⱼ)
       ─────────────────────────
       Σⱼ wⱼ / (x - zⱼ)

Where:

• zⱼ (nodes): Sample points chosen adaptively

• wⱼ (weights): Barycentric weights computed by the algorithm

• fⱼ (values): Function values at nodes (from high-precision baseline)

The algorithm's key property: it produces approximations close to the theoretical best rational function of a given degree, without requiring manual coefficient tuning. In practice, this lets us treat “pick a good rational approximation” as a design-time task handled by Python, not an on-chain concern.

From Barycentric to Polynomial Form

For on-chain evaluation, we convert the barycentric form to explicit polynomials:

r(x) = P(x) / Q(x) = (p₀ + p₁x + p₂x² + ... + pₙxⁿ) / (q₀ + q₁x + ... + qₘxᵐ)

This conversion happens offline. The Move code only evaluates P(x) and Q(x) using Horner's method, then computes their ratio.

Comparison with Morpheus (Aptos)

The Morpheus PM-AMM on Aptos uses a different approach: Abramowitz-Stegun (1964) for CDF and Acklam (2000) for inverse CDF, with Newton-Raphson refinement.

Both approaches are valid. Morpheus prioritizes maximum precision with proven classical formulas; GaussianMove prioritizes a unified, auditable pipeline with modern approximation theory.

Piecewise Strategy for Φ⁻¹

The inverse CDF presents special challenges because its derivative approaches infinity as p → 0 or p → 1. A single rational approximation cannot maintain accuracy across the full domain.

GaussianMove uses three regions:

Region 1: Central (0.02 ≥ p ≥ 0.98)

• Direct AAA approximation of Φ⁻¹(p)

• Expected error: ~3.11×10⁻¹³

Region 2: Lower tail (10⁻¹⁰ ≤ p < 0.02)

• Transform: t = √(-2 ln(p))

• Approximate Φ⁻¹ as function of t

• Expected error: ~2.03×10⁻¹³

Region 3: Upper tail (0.98 < p ≤ 1 - 10⁻¹⁰)

• Symmetry: Φ⁻¹(p) = -Φ⁻¹(1-p)

• Reuses lower tail approximation

This piecewise approach mirrors classical algorithms (Wichura AS 241, Moro/Jäckel) but fits coefficients via AAA rather than reusing floating-point polynomials.

At this point, we have a clear picture of the constraints (fixed-point, randomness, error amplification) and a modern approximation strategy (AAA + piecewise design). Next we need to quantify how much error is actually left and how it propagates into financial models.

Error Analysis and Bounds

Error bounds link approximation theory to practical finance. If the approximation error is many orders of magnitude smaller than model uncertainty and oracle noise, then from a DeFi perspective the numerical contribution of the approximation is relatively small.

Current Error Budget

From GaussianMove v0.9.0 coefficient generation:

These errors are validated against mpmath with 100-digit precision baselines.

How Error Propagates into Financial Models

For VaR calculations:

VaR(α) = μ + σ × Φ⁻¹(α)

With |Φ⁻¹ error| ≤ 3.11×10⁻¹³, the VaR error is:

|VaR error| ≤ σ × 3.11×10⁻¹³

For σ = $10,000 (typical portfolio volatility), this is $3.11×10⁻⁹—far below any practical threshold.

For Black-Scholes: The Greeks (Delta, Gamma, Vega) involve Φ(d₁) and φ(d₁). With CDF error 3.35×10⁻⁹:

• Delta error ≤ 3.35×10⁻⁹ (direct)

• Gamma involves φ, error ≤ 7.61×10⁻¹⁵

• Vega scales by √T, error remains negligible

Comparison with model uncertainty:

• Volatility estimation error: typically 5-20% (10⁻¹ to 10⁰)

• Oracle price staleness: seconds to minutes of drift

• Approximation error: 10⁻⁹ to 10⁻¹³

Conclusion: Approximation error is 6-10 orders of magnitude smaller than model/oracle uncertainty. For DeFi applications, even 10⁻⁸ precision (solgauss level) is overkill.

Domain Clamping

Probability inputs are clamped to (ε, 1-ε) with ε = 10⁻¹⁰ × WAD. This corresponds to approximately ±6.3σ—beyond which fixed-point arithmetic cannot meaningfully distinguish probabilities.

Applications requiring fatter tails (e.g., extreme risk modeling) should document this limitation and consider alternative approaches.

With constraints, approximation strategy, and error budgets in place, we can now switch from “math and theory” to “engineering”: how to turn all of this into a reproducible Python→Move pipeline.

Part II: The Implementation

Part II describes how GaussianMove turns the previous section’s numerical choices into concrete code. The design-time Python pipeline finds and validates rational approximations; the runtime Move code evaluates them cheaply and deterministically on-chain.

The Python-to-Move Pipeline

GaussianMove follows a "design-time vs runtime" separation:

┌─────────────────────────────────────────────────────────┐
│  PYTHON (Design-Time / Offline)                         │
│  ┌─────────────────────────────────────────────────┐    │
│  │ 1. Sample function with mpmath (50+ digits)     │    │
│  │ 2. Run AAA algorithm (SciPy)                    │    │
│  │ 3. Convert barycentric → polynomial coeffs      │    │
│  │ 4. Quantize to WAD-scaled integers              │    │
│  │ 5. Validate accuracy, export to Move            │    │
│  └─────────────────────────────────────────────────┘    │
│                           │                             │
│                           ▼                             │
│                  [coefficient arrays]                   │
└───────────────────────────┼─────────────────────────────┘
                            │
                            ▼
┌─────────────────────────────────────────────────────────┐
│  MOVE (Runtime / On-Chain)                              │
│  ┌─────────────────────────────────────────────────┐    │
│  │ 1. Load pre-computed coefficients (constants)   │    │
│  │ 2. Evaluate P(x), Q(x) using Horner's method    │    │
│  │ 3. Compute P(x) / Q(x)                          │    │
│  │ 4. Apply domain clamping and output bounds      │    │
│  └─────────────────────────────────────────────────┘    │
└─────────────────────────────────────────────────────────┘

Why this matters for auditability: All numerically delicate work (node selection, coefficient fitting, convergence analysis) happens in a Python toolchain that can be inspected, re-run, and validated. The on-chain Move code is a small, predictable evaluation kernel.

Horner's Method: O(n) Polynomial Evaluation

Evaluating P(x) = c₀ + c₁x + c₂x² + ... + cₙxⁿ naively requires O(n²) multiplications (computing x², x³, etc.). Horner's method restructures as:

P(x) = c₀ + x(c₁ + x(c₂ + ... + x(cₙ)))

This is O(n)—one multiplication and one addition per term.

/// Horner's method for polynomial evaluation
public fun horner_eval(x: u256, coeffs: &vector<u256>): u256 {
    let n = vector::length(coeffs);
    if (n == 0) { return 0 };
    
    // Start with highest-degree coefficient
    let mut result = *vector::borrow(coeffs, n - 1);
    
    // Work backwards: result = result × x + coeffs[i]
    let mut i = n - 1;
    while (i > 0) {
        i = i - 1;
        result = mul_wad(result, x);
        result = result + *vector::borrow(coeffs, i);
    };
    
    result
}

For degree-11 polynomials (typical for GaussianMove), this is 11 iterations—predictable gas cost, numerical stability, and minimal intermediate overflow risk.

Signed WAD for Negative Quantiles

The standard normal distribution is symmetric around zero, so Φ⁻¹(p) can be negative. Move lacks native signed integers, so GaussianMove uses a SignedWad type:

public struct SignedWad has copy, drop, store {
    magnitude: u256,
    negative: bool,
}

Operations track sign explicitly:

public fun add(a: SignedWad, b: SignedWad): SignedWad {
    if (a.negative == b.negative) {
        // Same sign: add magnitudes
        SignedWad { magnitude: a.magnitude + b.magnitude, negative: a.negative }
    } else {
        // Different signs: subtract smaller from larger
        if (a.magnitude >= b.magnitude) {
            SignedWad { magnitude: a.magnitude - b.magnitude, negative: a.negative }
        } else {
            SignedWad { magnitude: b.magnitude - a.magnitude, negative: b.negative }
        }
    }
}

v0.9.0 API Design Decisions

Strict PPF Domain Validation

The ppf(p) function enforces strict domain validation:

// Aborts with EProbOutOfDomain (302) if p is outside valid domain
let z = core::ppf(0);  // Aborts! p=0 is outside (EPS, 1-EPS)

// For sampling, use ppf_from_u64 which maps any u64 to valid domain
let z = core::ppf_from_u64(random_seed);  // Always succeeds

Rationale: Explicit failure surfaces invalid inputs immediately rather than silently clamping. For sampling use cases, ppf_from_u64(seed) provides a safe alternative that maps any u64 into the valid domain.

SignedWad Fields

The SignedWad struct uses short field names for ergonomics:

public struct SignedWad has copy, drop, store {
    mag: u256,   // magnitude (absolute value)
    neg: bool,   // true if negative
}

Accessor methods (abs(), is_negative()) provide a stable API.

Sui's Randomness Advantage

With the mathematical machinery in place, Sui's sui::random completes the picture for sampling:

use sui::random::Random;
use gaussian::core;
use gaussian::signed_wad::SignedWad;

public entry fun sample_standard_normal(r: &Random, ctx: &mut TxContext): SignedWad {
    let mut gen = random::new_generator(r, ctx);
    
    // Generate uniform in (0, 1), avoiding exact 0 or 1
    let u = random::generate_u256_in_range(&mut gen, 1, WAD - 1);
    
    // Transform via inverse CDF
    core::ppf(u)
}

Single transaction: No VRF callback or second transaction.

Validator consensus security: Randomness derived from distributed validator set, not manipulable by any single party.

Native integration: &Random is a first-class Sui object, passed directly to functions that need it.

This is the one place where Sui provides a genuine platform advantage—but it only matters because the mathematical foundation (accurate Φ⁻¹ approximation) is already in place.

Taken together, the Python pipeline, Horner evaluation, SignedWad representation, and sui::random give us a complete implementation story. The next natural question is: what do you actually do with these primitives?

Part III: Applications

Part III sketches how Φ and Φ⁻¹ plug into familiar financial formulas. These are not full protocol designs; they are case studies meant to show that once GaussianMove exists, Black–Scholes, VaR, and Gaussian-shaped AMMs can be expressed directly in terms of its API.

Black-Scholes as a Mathematical Case Study

The Black-Scholes formula for European call options is:

C = S₀Φ(d₁) - Ke^(-rT)Φ(d₂)

where:

d₁ = [ln(S₀/K) + (r + σ²/2)T] / (σ√T)
d₂ = d₁ - σ√T

The Mathematical Components

Computing d₁ requires:

1. ln(S₀/K) — natural logarithm (transcendental)

2. σ√T — square root (algebraic but irrational)

3. Division and addition — straightforward in fixed-point

Computing the option price requires: 4. Φ(d₁), Φ(d₂) — normal CDF (the hard part) 5. e^(-rT) — exponential (transcendental)

GaussianMove provides the CDF. The companion move-black-scholes package implements the transcendental helpers:

// From black_scholes::d_values
public fun compute_d1(
    spot: u256,      // S₀ in WAD
    strike: u256,    // K in WAD
    time: u256,      // T in WAD (years)
    rate: u256,      // r in WAD
    vol: u256        // σ in WAD
): SignedWad {
    // ln(S/K)
    let log_moneyness = transcendental::ln_wad(div_wad(spot, strike));
    
    // (r + σ²/2)T
    let drift = mul_wad(rate + mul_wad(vol, vol) / 2, time);
    
    // σ√T
    let vol_sqrt_t = mul_wad(vol, transcendental::sqrt_wad(time));
    
    // d₁ = (ln(S/K) + drift) / (σ√T)
    signed_wad::div(
        signed_wad::add(log_moneyness, drift),
        vol_sqrt_t
    )
}

The transcendental functions (ln_wad, sqrt_wad, exp_wad) are also implemented via polynomial approximation, following the same AAA pipeline philosophy. In practice, this means Black–Scholes-style pricing on Sui becomes a matter of wiring together a few math primitives.

Empirical Validation: Live Testnet Transactions

The following transactions demonstrate GaussianMove in production, pricing ATM European options (S=$100, K=$100, T=1yr, r=5%, σ=20%):

Transaction 1: Option Pricing

• TX: CdAxPyw1T7tF4xMPpfVqVhJMDL4Xy6zeyC24YeQxpjJt

• Results: Call=$10.45, Put=$5.57, Put-Call Parity=✓

Transaction 2: Greeks Calculation

• TX: 48TFYV87TXRJMUuCzoMZ4T5CLVsFgQoT1fptR2w7NXPv

• Results: Δ=0.637, Γ=0.019, ν=37.52, θ=-6.41, ρ=53.23

Comparison with scipy reference:

These results validate that GaussianMove's ~10⁻⁹ CDF error propagates to <0.01% pricing error in Black-Scholes applications.

Part IV: Ecosystem Context

Part IV places GaussianMove alongside existing Gaussian and options libraries in EVM and other ecosystems. The goal is not to declare a winner, but to show where a Sui-native, AAA-based library fits on the accuracy/gas/complexity frontier.

EVM Gaussian Libraries: A Brief Comparison

The EVM ecosystem has seen two waves of Gaussian implementations:

First wave (2014-2022): Production-driven

• errcw/gaussian (2014): JavaScript reference

• primitivefinance/solstat (2022): First DeFi library, ~10⁻⁷ error, ~5,000 gas

Second wave (2024): Research-driven

• GA006/gaussian-cdf: Zelen-Severo polynomial, ~10⁻⁸ error

• 0xknxwledge/DegeGauss: ABDK 128-bit float, ~10⁻¹⁶ error, ~53,000 gas

• cairoeth/solgauss: Rational Chebyshev, ~10⁻⁸ error, ~600 gas, includes PPF

Key insight: solgauss achieves the best gas/accuracy Pareto frontier via rational approximation—the same approach GaussianMove uses, but with AAA instead of hand-tuned Chebyshev coefficients.

Algorithm Comparison

GaussianMove occupies a distinct position: better accuracy than solgauss, lower complexity than Morpheus, and native randomness integration that no EVM library can match.

Conclusion

We started with a simple question: how do you compute Φ and Φ⁻¹ on a chain that only speaks integers? GaussianMove's answer is to treat this as an approximation-theory problem first, and an engineering problem second.

Concretely, GaussianMove:

1. Uses modern AAA rational approximation to generate near-optimal fits for Φ and Φ⁻¹ offline.

2. Enforces explicit error budgets (CDF 3.35×10⁻⁹, PPF 3.11×10⁻¹³) validated against high-precision baselines.

3. Wraps everything in an auditable Python→Move pipeline and a small, deterministic on-chain evaluation kernel.

4. Leverages Sui's native randomness so Gaussian sampling fits cleanly into single-transaction flows.

Whether or not DeFi fully embraces on-chain Gaussian math, it is useful to have a transparent implementation available.

References

Approximation Theory

• Nakatsukasa, Y., Sète, O., & Trefethen, L. N. (2018). The AAA algorithm for rational approximation. SIAM J. Sci. Comput., 40(3), A1494-A1522. arXiv:1612.00337

• Nakatsukasa, Y., & Trefethen, L. N. (2023). The first five years of the AAA algorithm. arXiv:2312.03565.

• Nakatsukasa, Y., & Trefethen, L. N. (2025). Applications of AAA rational approximation. Acta Numerica. arXiv:2510.16237.

• Driscoll, T. A., Nakatsukasa, Y., & Trefethen, L. N. (2024). AAA Rational Approximation on a Continuum. SIAM J. Sci. Comput., 46(2), A929-A952.

Classical Algorithms for Normal Distribution

• Abramowitz, M., & Stegun, I. A. (1964). Handbook of Mathematical Functions. Ch. 26.2.17.

• Wichura, M. J. (1988). Algorithm AS 241: The Percentage Points of the Normal Distribution. Applied Statistics, 37(3), 477-484.

• Cody, W. J. (1969). Rational Chebyshev Approximations for the Error Function. Mathematics of Computation, 23(107).

• Acklam, P. J. (2000). An algorithm for computing the inverse normal cumulative distribution function. (See also John D. Cook's literate program)

• Koopman, R. (2025). Some Simple Full-Range Inverse-Normal Approximations. Journal of Numerical Analysis and Approximation Theory, 54(1).

Horner's Method and Polynomial Evaluation

• Graillat, S., et al. (2024). Accurate Horner methods in real and complex floating-point arithmetic. BIT Numerical Mathematics, 64, article 17.

• Graillat, S., Langlois, P., & Louvet, N. (2009). Algorithms for accurate, validated and fast polynomial evaluation. Japan Journal of Industrial and Applied Mathematics, 26(2), 191-214.

• Horner's method - Wikipedia

Fixed-Point Arithmetic in DeFi

• RareSkills. (2024). Fixed Point Arithmetic in Solidity. (Comprehensive tutorial on WAD/RAY standards)

• PRBMath: Solidity library for advanced fixed-point math.

• ds-math: Original DappHub WAD/RAY implementation.

• brine-fp: Fixed-point math library with logarithmic and exponential functions for blockchain.

Solidity Gaussian Implementations

• primitivefinance/solstat: First production DeFi Gaussian library (Primitive RMM-01).

• cairoeth/solgauss: Rational Chebyshev approximation, most complete API.

• 0xknxwledge/DegeGauss: ABDK 128-bit floating-point approach.

• simontianx/OnChainRNG/GaussianRNG: CLT-based Gaussian approximation.

• araghava/cairo-black-scholes: Black-Scholes on StarkNet.

• opynfinance/BlackScholes: Opyn's Black-Scholes implementation.

Move Implementations

• GaussianMove (move-gaussian) — v0.9.0, Package: 0x66f9087a3d9ae3fe07a5f3c1475d503f1b0ea508d3b83b73b0b8637b57629f7f

• BlackScholes (move-black-scholes) — v0.2.0, Package: 0x1637ddc0495a8833ebd580224dad7154dfb33477f73d2c7fb41e2b350efa55b3

• Morpheus PM-AMM (Aptos)

AMMs and Options Protocols

• Evans, A., Angeris, G., & Chitra, T. (2021). Introducing Primitive RMM-01. Primitive Finance.

• Sterrett, E., & Jepsen, W. (2022). Replicating Portfolios: Constructing Permissionless Derivatives. arXiv:2205.09890.

• RMM Primer: Friendly guide to Primitive.

• Primitive Library Documentation

• Moallemi, C., & Robinson, D. (2024). pm-AMM: A Uniform AMM for Prediction Markets. Paradigm.

• White, D. (2024). Distribution Markets. Paradigm.

DeFi Risk Management and VaR

• Chainrisk. (2024). VaR Methodology for DeFi.

• Gauntlet. (2023). Improved VaR Methodology.

• GARP. (2024). Digital-Asset Risk Management: VaR Meets Cryptocurrencies.

• Aufiero, S., et al. (2025). Mapping Microscopic and Systemic Risks in TradFi and DeFi. arXiv:2508.12007.

• KernelDAO. (2024). Traditional vs DeFi Risk Management: A Quantitative Comparison.

Black-Scholes in DeFi

• Polygon. (2022). Black Scholes Merton Model to Price DeFi Options.

• Chainlink. (2020). Build a DeFi Call Option Exchange With Chainlink Price Feeds.

• Auctus. (2020). ACO Black-Scholes: A Pooled Liquidity Model for Options Powered by Chainlink.

• Panoptic. (2024). How to Price Perpetual Options: Five Models Compared.

Sui Documentation

• Sui Randomness Guide

• sui::random Framework Reference

Tools

• SciPy AAA

• mpmath (Arbitrary Precision)

• baryrat (AAA Implementation)

• SciPy PINV (Polynomial interpolation based INVersion of CDF)

The latest-state pattern is one of the most important techniques for anyone working with Sui object data. This technique generalizes to every Sui object type and forms the backbone for any analysis rooted in the chain’s actual state. Every asset—whether a coin, NFT, or protocol object—exists as a Move object that evolves through a sequence of versions.

Each mutation creates a new version, deleted objects remain visible with object_status = 'Deleted', and there is no separate balance table or account registry keeping track of state elsewhere. As a result, the canonical truth for any asset is simply the row with the highest version number. If you want to know “what does this object look like right now?”, the answer is always: find its latest version.

This structural choice gives Sui enormous flexibility but also shapes how analysts must work with the data. Tasks like fetching the current owner or metadata of an object become trivial once you understand the pattern. But tasks like reconstructing transfers, ownership histories, or application-level flows require walking through the full sequence of versions—something covered in the previous post.

The Pawtato Heroes example highlights why the latest-state pattern is so essential: the latest version reveals the object’s current metadata and owner, but because objects can be owned by other objects, the last modifying transaction’s sender is often the real controller.

object_id: 0xHERO...123

                      object versions
          +-----------------------------------------------+
          |                                               |
 version 1   version 2          version 3 (latest)
 +--------+  +--------+         +------------------------+
 |owner:A |  |owner:X |   →     |owner:Y (wrapper obj)   |
 |meta:v1 |  |meta:v1 |         |meta:v2                 |
 +--------+  +--------+         +------------------------+
     ^           ^                        ^
     |           |                        |
 Created      Mutated                Mutated
                                     (ownership changed,
                                     but to an object)

          transaction producing version 3
          -----------------------------------------
          tx_digest: 0xTX...789
          sender:    0xREAL_OWNER   ← true controller
          -----------------------------------------
                             ↑
                             |
                    previous_transaction  
                    on version 3 object row

Latest-state logic:
- MAX(version) = 3 → latest object row (owner:Y, meta:v2)
- But owner:Y is another object (wrapper/vault)
- Join version 3's previous_transaction → transaction.sender
- Effective owner = 0xREAL_OWNER

Querying Latest State Objects on Dune

Below is a practical pattern for retrieving the latest version and owner for a Sui NFT collection Pawtato Heroes. The query can be found on Dune here. This example demonstrates how to apply latest-state logic to an NFT collection by tracking ownership changes that occur through Mutated versions, and resolving the true owner by joining the object’s most recent version to the transaction that produced it.

WITH params AS (
  SELECT
    DATE '2025-10-30' AS start_date,
    CURRENT_DATE      AS end_date
),

-- All relevant HERO object versions in the date window
base AS (
  SELECT
    o.object_id,
    o.version,
    o.object_status,
    o.owner_address,   -- may be an intermediary object
    o.timestamp_ms,
    o.type_,
    o.object_json,
    o.previous_transaction AS tx_digest,
    from_unixtime(CAST(o.timestamp_ms AS DOUBLE) / 1000.0) AS updated_at_utc
  FROM sui.objects o
  CROSS JOIN params p
  WHERE o.type_ = '0xe0fa7b75a3dc8137b38bceb0c0c21c10e0f57c408fe9068694f58fd21e071925::pawtato_heroes::HERO'
    AND o.object_status IN ('Created', 'Mutated')   -- ownership may change on Mutated
    AND o.date >= p.start_date                     -- efficient partition pruning
    AND o.date <  p.end_date
),

-- Latest version per HERO
latest AS (
  SELECT
    object_id,
    MAX(version) AS max_version
  FROM base
  GROUP BY object_id
),

-- Date-windowed transactions (prevents full-table scan)
tx_window AS (
  SELECT
    t.transaction_digest,
    t.sender,
    t.timestamp_ms AS tx_timestamp_ms,
    from_unixtime(CAST(t.timestamp_ms AS DOUBLE) / 1000.0) AS tx_timestamp_utc
  FROM sui.transactions t
  CROSS JOIN params p
  WHERE t.date >= p.start_date
    AND t.date <  p.end_date
)

-- Final latest state + effective owner
SELECT
  b.object_id,
  b.version,
  b.object_status,
  b.owner_address,     -- raw object owner (could be another object)
  b.updated_at_utc,
  b.type_,
  b.object_json,
  b.tx_digest,
  tx.sender            AS effective_owner,     -- true controller of the NFT
  tx.tx_timestamp_utc  AS last_tx_at_utc
FROM base b
JOIN latest l
  ON b.object_id = l.object_id
 AND b.version   = l.max_version
LEFT JOIN tx_window tx
  ON b.tx_digest = tx.transaction_digest;

This query works by first collecting all relevant Pawtato HERO object versions—both Created and Mutated. The date window is recent because the collection launched October 2025 which makes the dataset small and efficient to scan. It then identifies the latest version of each object by selecting the maximum version per object_id, which represents the current on-chain state of that NFT.

To resolve the true owner, the query pulls in a date-filtered slice of sui.transactions and joins each object’s most recent previous_transaction to the corresponding transaction record. This allows the query to enrich the final result with full object_json metadata, the transaction that last updated the object, and most importantly the sender of that transaction—who is often the actual controlling address, even when the object’s recorded owner is an intermediate wrapper object.

Outline

1. Mental Model of Sui Objects

2. Querying USDC Coin Transfers on Dune

3. Transfer Heuristics

4. Query Optimizations

This post shows how to calculate USDC transfers on the Sui blockchain using Dune’s Trino SQL engine. Sui is object-centric - meaning that every object has their own lifecycle, ownership history, versioning, and special object type data. This means that coins and addresses on Sui are also Move objects. To calculate balance transfers, we must compare the state of multiple coin objects, not from a single account balance like on EVM chains.

1) Mental Model of Sui Objects

• Objects, not accounts. Every coin is an object with an object_id, a version that increases with each mutation, an owner, and a coin type (e.g., 0xdba34672e30cb065b1f93e3ab55318768fd6fef66c15942c9f7cb846e2f900e7::usdc::USDC).

• Ownership forms. Address-owned, object-owned, shared, or immutable. A wallet can hold many coin objects for the same token.

• Causality via transactions. Each new object version is produced by a specific transaction digest (previous transaction).

Practical implication: to compute “transfers,” we trace how coin objects’ ownership and balances change over time, then aggregate the positive inflows to receivers.

2) Querying USDC Coin Transfers on Dune

Below is a query example to calculate USDC transfers for the first month of the coin’s creation. The dune link can be found here.

The query reconstructs USDC transfers directly from object-state changes rather than relying on emitted events. Each time a new coin object is created, its full coin_balance represents an inflow to the receiver. When an existing object is mutated, the query computes the positive delta between its current and previous balances (max(new − old, 0)), which captures additional inflows such as received transfers or merged coins. To make these deltas accurate without scanning the entire historical table, the query introduces an anchor—the most recent pre-window version of every object—so that LAG(prev_balance) comparisons are local to the analysis range. Finally, by filtering both sui.objects and sui.transactions on their date partitions, Dune’s engine can prune away irrelevant partitions and limit I/O to only the time window being studied, significantly reducing scan cost.

WITH params AS (
  SELECT
    CAST('0xdba34672e30cb065b1f93e3ab55318768fd6fef66c15942c9f7cb846e2f900e7::usdc::USDC' AS VARCHAR) AS usdc,
    DATE '2024-09-01' AS start_date,
    DATE '2024-10-10' AS end_date
),
day_rows AS (
  SELECT
    o.object_id,
    o.version,
    o.previous_transaction AS tx_digest,
    o.timestamp_ms,
    o.owner_address AS receiver,
    o.coin_type,
    o.object_status,
    TRY_CAST(o.coin_balance AS BIGINT) AS coin_balance
  FROM sui.objects o
  CROSS JOIN params p
  WHERE o.object_status IN ('Created', 'Mutated')
    AND o.coin_type = p.usdc
    -- ✅ partition pruning on objects
    AND o.date >= p.start_date
    AND o.date <  p.end_date
),
anchors AS (
  SELECT
    p.object_id,
    MAX(p.version) AS version,
    CAST(NULL AS VARCHAR) AS tx_digest,
    max_by(p.timestamp_ms, p.version) AS timestamp_ms,
    max_by(p.owner_address, p.version) AS receiver,
    p.coin_type,
    CAST('ANCHOR' AS VARCHAR) AS object_status,
    max_by(TRY_CAST(p.coin_balance AS BIGINT), p.version) AS coin_balance
  FROM sui.objects p
  CROSS JOIN params pr
  WHERE p.coin_type = pr.usdc
    -- ✅ prune to pre-window partitions only
    AND p.date < pr.start_date
    -- only anchor for objects that appear in the main window
    AND p.object_id IN (SELECT DISTINCT object_id FROM day_rows)
  GROUP BY p.object_id, p.coin_type
),
unioned AS (
  SELECT object_id, version, tx_digest, timestamp_ms, receiver, coin_type, object_status, coin_balance FROM anchors
  UNION ALL
  SELECT object_id, version, tx_digest, timestamp_ms, receiver, coin_type, object_status, coin_balance FROM day_rows
),
calc AS (
  SELECT
    u.object_id,
    u.version,
    u.tx_digest,
    u.timestamp_ms,
    u.receiver,
    u.coin_type,
    u.object_status,
    u.coin_balance,
    LAG(u.receiver)     OVER (PARTITION BY u.object_id ORDER BY u.version) AS prev_owner,
    LAG(u.coin_balance) OVER (PARTITION BY u.object_id ORDER BY u.version) AS prev_balance
  FROM unioned u
),
-- Prefilter transactions by partition, then join (avoids scanning all tx partitions)
tx_window AS (
  SELECT tx.*
  FROM sui.transactions tx
  CROSS JOIN params p
  WHERE tx.date >= p.start_date
    AND tx.date <  p.end_date
),
calc_with_tx AS (
  SELECT c.*, tx.sender AS tx_sender
  FROM calc c
  LEFT JOIN tx_window tx
    ON c.tx_digest = tx.transaction_digest
),
outs AS (
  SELECT
    tx_digest,
    receiver,
    coin_type,
    SUM(
      CASE
        WHEN object_status = 'Created' THEN coin_balance
        WHEN object_status = 'Mutated' THEN greatest(coin_balance - COALESCE(prev_balance, 0), 0)
        ELSE 0
      END
    ) AS amount_raw
  FROM calc
  WHERE (object_status = 'Created' AND coin_balance > 0)
     OR (object_status = 'Mutated' AND (coin_balance - COALESCE(prev_balance, 0)) > 0)
  GROUP BY tx_digest, receiver, coin_type
),
tx_stats AS (
  SELECT tx_digest, COUNT() AS receiver_cnt
  FROM outs
  GROUP BY tx_digest
)
SELECT
  c.object_id,
  c.version,
  c.tx_digest,
  c.timestamp_ms,
  c.receiver,
  CASE WHEN c.object_status = 'Created' THEN c.tx_sender ELSE c.prev_owner END AS sender,
  c.coin_type,
  c.object_status,
  c.coin_balance,
  c.prev_balance,
  CASE
    WHEN c.object_status = 'Created' THEN c.coin_balance
    ELSE greatest(c.coin_balance - COALESCE(c.prev_balance, 0), 0)
  END AS effective_amount,
  CASE WHEN ts.receiver_cnt = 1 THEN 'transfer' ELSE 'other' END AS classification,
  CASE WHEN ts.receiver_cnt = 1 THEN TRUE ELSE FALSE END AS is_transfer
FROM calc_with_tx c
LEFT JOIN tx_stats ts
  ON c.tx_digest = ts.tx_digest
WHERE (c.object_status = 'Created' AND c.coin_balance > 0)
   OR (c.object_status = 'Mutated' AND (c.coin_balance - COALESCE(c.prev_balance, 0)) > 0);

3) Transfer Heuristics

Sui’s object model doesn’t emit standardized “transfer” events like ERC-20 tokens on Ethereum. Instead, value movement must be inferred from how coin objects change owners or balances over time. To reconstruct these movements, the query aggregates all positive inflows on the receiver side of each transaction. This approach is designed to capture most genuine transfers with high recall—if someone truly receives tokens, that inflow will appear as a Created or Mutated coin object with a positive delta. As a result, false negatives are rare: real peer-to-peer (P2P) transfers almost always surface correctly.

However, there is a risk for false positives. Any DeFi applications—AMMs, lending protocols, routers, and bridges—mutate coin objects internally as part of swaps, liquidity operations, or accounting adjustments. These actions also create positive deltas that can look indistinguishable from user-to-user transfers when viewed at the object level.

To control for this, the query introduces a simple but effective classifier: a transaction is labeled a transfer only when it has a single receiver (receiver_cnt = 1). Transactions with multiple receivers are more likely to represent complex DeFi interactions and are therefore classified as “other.”

In practice, this “simple transfer” heuristic serves as a pragmatic first layer of classification: it cleanly isolates high-confidence P2P flows while leaving room for later enrichment into categories like “likely P2P,” “app-mediated,” or “internal protocol movement.” This balance between simplicity and interpretability makes it a good starting point for mapping economic flows in an object-centric system like Sui.

4) Query Optimization

This query is deceptively heavy. Complexity comes from logical SQL and physical layout.

4.1 Logical SQL Complexity

• Window functions (LAG) over (PARTITION BY object_id ORDER BY version) require Trino to sort and buffer complete per-object histories that land in the window.

• Union of anchors + in-window rows increases input size and sustained memory pressure.

• Post-window aggregates mean large intermediate results must be shuffled/aggregated after the sort phases.

• Joins to transactions add cross-stage shuffles if filters aren’t aligned.

Result: multi-stage distributed execution with large network shuffles and potential spills when memory is tight.

4.2 Physical Data Layout and Partitioning

On Dune, tables are clustered/partitioned by a date column, not by coin_type or object_id.

Implications:

• Partition pruning works only along date. The WHERE clause should include a filter on the date partition to avoid scanning every partition in the chosen period.

• Filtering by coin_type or grouping by object_id does not reduce partitions scanned; it only filters within each touched partition.

• Wrapping a partition column in functions may invalidate partition pruning efficiencies

Takeaway: runtime scales roughly with both the number of active coin objects and the number of date partitions touched.

Introduction

This is a build log of my first steps building an MCP server for Snowflake. It captures what I tried, what exists today, what worked, what did not, and where I am going next. If you are exploring or building your own MCP, I hope these notes and code references save you time. For install and a quickstart, see the igloo-mcp README.

Table of contents

• Why I started building an MCP

• How igloo-mcp differs from Snowflake Labs MCP

• How igloo-mcp works

• Learnings

• What's next

Why I started building an MCP

As a data scientist, there are always more questions than answers. I built igloo-mcp to optimize how agents run sequences of queries on my Snowflake database to answer the never ending stream of questions. The major benefit of agentic querying is that agents can quickly build the context needed to derive nontrivial insights with minimal instructions.

It's magical when everything works smoothly. I can rapidly debug and improve data pipelines, develop metrics faster, and also ask low priority but potentially interesting questions about datasets that I never had time to answer before. I also wanted a way to codify behaviors I found effective. MCPs are a natural choice to standardize tools that make agents' outputs more reliable and trustworthy.

Finally, I wanted to build the MCP from scratch to experiment with managing agents to design a repo from the ground up. When I use agents on the Snowflake database, I almost always use igloo-mcp to run tens of queries at a time. In contrast, building a repository lets me test multi agent workflows on code.

How igloo-mcp differs from Snowflake Labs MCP

• Stacking on top instead of rebuilding: igloo-mcp boots the Snowflake Labs MCP runtime and layers catalog/dependency workflows on top, which keeps the upstream Cortex/object/query tools available while adding the metadata utilities I rely on.

• Zero config profiles vs YAML service maps: igloo-mcp reuses the SnowCLI profile that already exists on my machine, while Snowflake Labs MCP expects a configuration.yaml that lists Cortex services, tool groups, and SQL permissions before the server can start.

• Safety first execution path: igloo-mcp runs queries through the CLI to inherit profile RBAC, validate statements against allow/deny lists, check profile health, and optionally return verbose error diagnostics. Snowflake Labs MCP executes through the connector and surfaces failures through a generic SnowflakeException, leaving destructive statements enabled unless you turn them off in YAML.

How igloo-mcp works

I evaluated Snowflake Labs MCP to understand its strengths and how to make an MCP that better fits my agentic workflows. igloo-mcp reflects those choices.

In practice, the server prioritizes safe, observable query execution for agent loops:

• Guardrails default to read‑only and provide clear, actionable messages when a statement is blocked (including when Snowflake classifies it as a generic Command).

• Each request can attach a short reason that is propagated to the Snowflake QUERY_TAG and to the optional local history, making it easy to correlate Snowflake history with local runs.

• Schema‑first tool design: each tool declares a tight JSON schema (with additionalProperties=false) and concrete examples so clients discover and call tools predictably.

• Predictable ergonomics: compact errors by default keep LLM loops clean; opt‑in verbose mode adds structured “quick fixes” when you need depth.

Currently, functionality includes:

Tools

• execute_query — Executes SQL via SnowCLI; supports warehouse/database/schema/role overrides, allow/deny validation, timeouts, and optional verbose errors

• preview_table — Returns a limited preview of a table

• build_catalog — Builds metadata from INFORMATION_SCHEMA (account or database scope) with JSON/JSONL output; includes DDL

• get_catalog_summary — Reads and returns catalog summary from a directory

• build_dependency_graph — Produces object dependency graph (JSON or DOT)

• test_connection — Verifies Snowflake connectivity using the configured profile

• health_check — Reports server and profile health

Defaults

• SQL guardrails default to read-only. INSERT, UPDATE, CREATE, ALTER, DELETE, DROP, and TRUNCATE are blocked by default; can be explicitly opted into for controlled workflows. Statements Snowflake classifies as generic "Command" are also rejected by default.

Execution

• Error handling:

  • Compact by default keeps agent loops readable and reduces noise

  • Verbose on demand includes a SQL snippet, timeout, and context for faster debugging

  • Profile health checks fail fast when a profile is missing or misconfigured

  • Allow/deny validation blocks risky or unsupported statements before anything runs

  • Clearer safety messages when Snowflake's parser falls back to a generic Command type, so agents know why a statement was blocked and what safe alternatives exist

  • Messages are formatted so MCP clients surface actionable feedback instead of long stack traces

• Session overrides: per request overrides for warehouse, database, schema, and role

• Timeouts & cancellation: per‑request timeouts are enforced; on local timeout a best‑effort server‑side cancel is issued and the tool returns immediately. Query ID is captured when available; verbose timeouts include concrete next steps.

Observability

• Lightweight local query history (opt‑in): set IGLOO_MCP_QUERY_HISTORY=/path/to/history.jsonl to write one JSON object per executed query. Each event includes a status (success/timeout/error), a truncated statement preview, optional query_id (when available), rowcount/duration_ms for successful reads, the effective timeout, and any session overrides. The optional reason you pass is stored in both Snowflake QUERY_TAG and the local history, which makes it easy to join Snowflake account history with local events for auditing and debugging.

Why add local history?

• Tie agent prompts to database activity without standing up infra; JSONL is append‑only and portable.

• Correlate with Snowflake’s ACCOUNT_USAGE.QUERY_HISTORY by QUERY_TAG to get warehouse cost, bytes scanned, or execution profile.

• Debug timeouts and cancellations: even when query_id is missing, the preview + reason + status help reconstruct sequences.

• Lightweight telemetry: count queries per task, calculate duration distributions, and quickly spot recurring errors.

Enable and sample entries

export IGLOO_MCP_QUERY_HISTORY=./igloo_query_history.jsonl

Example lines (one JSON object per line):

{"ts": 1737412345, "status": "success", "profile": "quickstart", "statement_preview": "SELECT * FROM customers LIMIT 10", "rowcount": 10, "timeout_seconds": 30, "overrides": {"warehouse": "COMPUTE_WH"}, "query_id": "01a1b2c3d4", "duration_ms": 142, "reason": "Explore sample customers"}
{"ts": 1737412399, "status": "timeout", "profile": "quickstart", "statement_preview": "SELECT * FROM huge_table WHERE date >= '2024-01-01'", "timeout_seconds": 30, "overrides": {"warehouse": "COMPUTE_WH"}, "error": "Query execution exceeded timeout and was cancelled"}
{"ts": 1737412468, "status": "error", "profile": "quickstart", "statement_preview": "SELECT * FROM missing_table", "timeout_seconds": 30, "overrides": {}, "error": "Object 'MISSING_TABLE' does not exist."}

Learnings

I pushed the boundaries to see where agent capabilities currently end. The biggest revelation was that agents cannot be trivially parallelized on large codebases. It requires thoughtful planning, and there are not yet great solutions to monitor multiple agents working on different things. However, there are promising products like Claude Subagents and Zed CLI CRDTs (Conflict free Replicated Data Types).

Another limitation is the human ability to keep up with multiple agents' work. That said, it feels like more parallel autonomous agents are getting closer, and I am looking forward to seeing more of this soon.

A large refactor cut the codebase from more than 20k LOC to less than 5k LOC; repo wide renaming required multiple assistive passes.

What's next

I will keep iterating on igloo-mcp to continue learning how to build strong MCP tools and what does and does not work in practice for different workflows. For instance, I am doing heavy quantitative research and I am exploring a cache for the execute_query tool and richer query history features (per‑project grouping, rotation, and search) on top of the current JSONL history.

The other angle is that I find it useful to hand pick issues by defining the problem and desired result, then have the LLM write a more in depth issue and eventually fix it and open a PR. As a result, I spend more time as a product manager and power user of my own work while delegating more boilerplate aspects of development to different agents in the background.

1. The Universal Challenge: The "New Protocol" Learning Curve

2. Building an LLM-First Pipeline for Move Package Analysis

3. Case Study: Bluefin Pro Perps Analysis

   • LLM Pipeline Workflow

   • Prompt Iteration Highlights

   • Takeaways & Results

4. Final Outputs

   • SQL views and daily metrics

   • Charts and dashboards

5. Appendix: Technical References

   • Condensed Prompt Chat (all with superwhisper)

   • Final LLM SQL Output at end of convo:

   • Move Package Catalogue Tasks

     • Events

     • Objects

     • Snowflake Table Data Catalogue

     • Bluefin Daily Market View Output:

The Universal Challenge: The "New Protocol" Learning Curve

A persistent challenge in crypto data analytics is the sheer diversity of smart contracts. Every protocol ships its own set of contracts that must be fully understood before you can build data pipelines and extract actionable insights.

On Sui alone, there are 95k**+ Move packages** emitting 350,000+ events and nearly 900k objects—each with distinct schema and structure. Even protocols in the same category, such as the CLMM-based DEXs Cetus and Bluefin, can have significant implementation differences. Even when they don’t, the contracts must still be analyzed to confirm parity. This constant variability makes it challenging for analysts to keep pace with the rapid innovation happening on any chain.

Building an LLM-First Pipeline for Move Package Analysis

I prototyped an LLM-enabled data pipeline to accelerate package analysis by strictly managing a local, context-rich environment. The system centers on two curated data catalogues that provide the model with the critical context it needs. I have attached the snowflake tasks and view in the appendix for additional reference:

1. Snowflake Data Catalogue – A SQL view extracts metadata for all relevant Snowflake tables. The results are exported as CSV, and a Python script converts each row into a .sql file, with a strict 1:1 mapping between table and file. This setup gives the LLM complete context for 400+ Snowflake tables, enabling it to selectively load only the subset needed for a given task.

2. Move Package Catalogue – Snowflake tasks index and store all unique event and object data for a specific package. The output is stored in an LLM-friendly format, giving the model a complete on-chain view for that package. With this holistic view of objects and events, the LLM can detect patterns and relationships more effectively.

This initial design keeps LLM access fully isolated—no direct database reads or writes. It also works well with cost-efficient models; for example, I primarily use Gemini Flash 2.5 (non-max mode) for most tasks.

These foundational tables and views provide exceptional context for the LLM. By comparison, Cursor rules have shown little additional performance benefit. There are some very basic rules that I currently use:

• Show sample datasets for core Snowflake tables (events, objects, transactions) and some non-core DeFi tables (coin info, prices). This is somewhat redundant with the Snowflake Data Catalogue now and was a legacy cursor rule.

• Include simple operational guidelines such as: write queries efficiently, check Snowflake SQL syntax, and be concise.

With this foundation in place, I tested the pipeline on a real-world case—analyzing Bluefin’s Pro Perps package.

Case Study - Bluefin Pro Perps

LLM Pipeline Workflow

1. Find relevant package data – Manually locate Bluefin Pro package objects and events from the Move Package Catalogue and export them for LLM use.

2. Prompt the LLM with context – Load the package data into the LLM and ask it to identify relevant events and generate a query for daily trading volume by perpetual market.

3. Iterate on results – Review outputs, make targeted corrections, and request additional metrics (funding rate, daily price, daily active users, open interest).

4. Plot the results and check accuracy

Prompt Iteration Highlights

• First pass – Query worked, but BTC, ETH, and SOL market decimals were off by 1. Prompted the LLM to adjust.

• Subsequent passes – Added average daily funding rate, daily price, DAUs, and open interest.

• Challenge – Open interest calculations were consistently wrong; the LLM lacked the right DeFi context. Several prompts failed to fix it until switching from “daily average” to “end-of-day snapshot” calculations.

• Resolution – After explicitly flagging the snapshot logic as incorrect, the LLM revised it to calculate open interest correctly.

Takeaways & Results

• LLM finds relevant events autonomously – Without any manual intervention, it correctly identified key events from the package-level context.

• Complex metrics need iteration – Multiple passes are normal for nuanced DeFi calculations like open interest.

• Flagging > explaining – Pointing out “this snapshot is wrong” was more effective than prescribing exact fixes.

• Documentation is built-in – Successful conversations leave the LLM with the full context to auto-generate comments for tables, views, and columns.

The end result is an underlying table that makes it effortless to plot something like this:

Appendix:

Condensed Prompt Chat (all with superwhisper)

Take a look at all of these Bluefin Pro package events. They have different event names, they have module names, as well as event JSON information. Now the file is a little messy, but are you able, can you infer which events I should focus on and create a SQL query so that I can get the trading volume broken down by perp market every day? @bbluefin_pro_package_events.csv The prompt worked on the first try. After initial inspection I noticed that the BTC, ETH, and SOL market decimals were off by 1. I told it to "fix it". Okay, this looks almost correct. It looks like the BTC perp market and the ETH perp market are one decimal off. So like BTC says, for example, 93 million, but I think it's closer to 9.3 million. Same with ETH. Can you please fix that just for BTC and ETH? The rest of the decimals look good, though. Fix was implemented. Then I realized that Solana market decimals were off too. Also I casually mentioned if it could find the funding rate from the events and calculate the daily average funding rate. I didn't specify the event or the event type structure because LLM has the package context already. Ah, my bad. It looks like Solana, the Soul Perp market also has an extra decimal, so please move it. It says 32 million, but I think it should be closer to 3.2 million. Otherwise it looks good. Additionally, are you able to add a column to get the average funding rate, the average daily funding rate for each market?@bluefin_pro_package_events.csv I also asked for a daily average price column, a daily active user count, and a daily average open interest as well. These were all added in after a first request. Upon inspecting the query outputs, I notiecd that the open interest was denominated in the native token amounts. So I asked "add usd open interest column too".

The open interest calculation was incorrect. The LLM doesn't have the right DeFi context to properly calculate open interest so I guided it a little. The re-prompt did not fix it properly: Okay, well, so I like the columns, but I'm not sure that the output is correct. How exactly are you calculating the average? Because I think this is a little complex because you have to, you can't just take all of the orders and then take the average. That's going to over count the numbers because you're not a count, you need to properly account for like how many orders are open at every funding interval versus how many get closed and then take the average of just the open orders at every funding interval.

I changed query prompt approach again. Still incorrect. Um, actually instead of doing a daily average, could we just take a daily snapshot instead? Uh, do this, take the snapshot for the end of every day for all the positions that are open.

Third time is the charm! The problem was isolated to the snapshot calculation and pointed out and the LLM was able to revise the snapshot calculations to correctly calculate the open interest. No, this isn't correct still. The numbers for the open interest in USD amounts are still way too high. I don't know what you're doing here. I don't know how to troubleshoot this either, but the numbers are still way too high. So I don't know if the snapshot is being calculated properly. I would guess you're still accounting for positions that are...you haven't properly accounted for positions that are closed, maybe?

Final LLM SQL Output at end of convo:

CREATE OR REPLACE VIEW PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.BLUEFIN_PRO_DAILY_MARKET_METRICS_V
COMMENT = 'Provides daily aggregated metrics for Bluefin Pro perpetual markets. It includes daily trading volume (USD), volume-weighted average price (USD), daily active users (DAU), end-of-day (EOD) open interest in both native token and USD, and the average daily funding rate. Data is sourced from Bluefin Pro package events (0xe744...). NOTE: A manual decimal correction (+1) is applied to BTC-PERP, ETH-PERP, and SOL-PERP markets to correct an observed 10x scaling discrepancy in the raw on-chain data for accurate calculations.'
AS
WITH
-- ============================================================================
-- STEP 1: Get the latest on-chain metadata for each market (e.g., decimals)
-- ============================================================================
market_metadata AS (
    SELECT
        event_json:perpetual:symbol::string as market,
        -- Derive decimal count from on-chain value (e.g., 8000000000 -> 8)
        event_json:perpetual:base_asset_decimals::number / 1e9 as base_asset_decimals
    FROM PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.EVENT_PARQUET
    WHERE event_type IN (
        '0xe74481697f432ddee8dd6f9bd13b9d0297a5b63d55f3db25c4d3b5d34dad85b7::events::PerpetualUpdate',
        '0xe74481697f432ddee8dd6f9bd13b9d0297a5b63d55f3db25c4d3b5d34dad85b7::events::PerpetualSynced'
    )
    AND market IS NOT NULL
    AND base_asset_decimals IS NOT NULL
    QUALIFY ROW_NUMBER() OVER (PARTITION BY market ORDER BY timestamp_ms DESC) = 1
),

-- ============================================================================
-- STEP 2: Calculate daily trading volume, average price, and active users
-- ============================================================================
trade_details AS (
    SELECT
        t.TIMESTAMP_MS,
        t.EVENT_JSON:market::string as market,
        t.EVENT_JSON:fill_price::number as fill_price,
        t.EVENT_JSON:fill_quantity::number as fill_quantity,
        t.EVENT_JSON:maker:address::string as maker_address,
        t.EVENT_JSON:taker:address::string as taker_address,
        -- MANUAL CORRECTION: Add 1 to decimals for specific markets due to
        -- an observed 10x discrepancy in their `fill_quantity` scaling.
        CASE
            WHEN m.market IN ('BTC-PERP', 'ETH-PERP', 'SOL-PERP') THEN m.base_asset_decimals + 1
            ELSE m.base_asset_decimals
        END as base_asset_decimals
    FROM PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.EVENT_PARQUET t
    JOIN market_metadata m ON t.EVENT_JSON:market::string = m.market
    WHERE t.EVENT_TYPE = '0xe74481697f432ddee8dd6f9bd13b9d0297a5b63d55f3db25c4d3b5d34dad85b7::events::TradeExecuted'
),

trade_participants AS (
    SELECT TIMESTAMP_MS, market, maker_address as user_address FROM trade_details
    UNION
    SELECT TIMESTAMP_MS, market, taker_address as user_address FROM trade_details
),

daily_active_users AS (
    SELECT
        DATE(TO_TIMESTAMP(timestamp_ms/1000)) as trade_date,
        market,
        COUNT(DISTINCT user_address) as dau
    FROM trade_participants
    GROUP BY 1,2
),

daily_trade_metrics AS (
    SELECT
        DATE(TO_TIMESTAMP(timestamp_ms / 1000)) as trade_date,
        market,
        SUM((fill_price / 1e9) * (fill_quantity / POW(10, base_asset_decimals))) as daily_volume_usd,
        SUM(fill_price * fill_quantity) / SUM(fill_quantity) / 1e9 as daily_average_price
    FROM trade_details
    GROUP BY 1, 2
),

-- ============================================================================
-- STEP 3: Calculate average daily funding rate from 'FundingRateUpdated' events
-- ============================================================================
daily_funding_rates AS (
    SELECT
        DATE(TO_TIMESTAMP(timestamp_ms / 1000)) as rate_date,
        event_json:market::string as market,
        AVG((CASE WHEN event_json:rate:sign::boolean THEN 1 ELSE -1 END * event_json:rate:value::number) / 1e9) as avg_daily_funding_rate
    FROM PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.EVENT_PARQUET
    WHERE event_type = '0xe74481697f432ddee8dd6f9bd13b9d0297a5b63d55f3db25c4d3b5d34dad85b7::events::FundingRateUpdated'
    GROUP BY 1, 2
),

-- ============================================================================
-- STEP 4: Get End-of-Day Open Interest Snapshot
-- ============================================================================
open_interest_snapshots AS (
    SELECT
        t.timestamp_ms,
        t.event_json:position:perpetual::string as market,
        -- MANUAL CORRECTION: Apply the same decimal adjustment here for `position:size`
        SUM(t.event_json:position:size::number / POW(10,
            CASE
                WHEN m.market IN ('BTC-PERP', 'ETH-PERP', 'SOL-PERP') THEN m.base_asset_decimals + 1
                ELSE m.base_asset_decimals
            END
        )) as oi_snapshot_native
    FROM PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.EVENT_PARQUET t
    JOIN market_metadata m ON t.event_json:position:perpetual::string = m.market
    WHERE t.event_type = '0xe74481697f432ddee8dd6f9bd13b9d0297a5b63d55f3db25c4d3b5d34dad85b7::events::FundingRateApplied'
      AND t.event_json:position:is_long::boolean = true
    GROUP BY 1, 2
),

eod_open_interest AS (
    SELECT
        DATE(TO_TIMESTAMP(timestamp_ms/1000)) as oi_date,
        market,
        oi_snapshot_native as eod_open_interest_native
    FROM open_interest_snapshots
    QUALIFY ROW_NUMBER() OVER (PARTITION BY oi_date, market ORDER BY timestamp_ms DESC) = 1
)

-- ============================================================================
-- FINAL STEP: Join all the daily metrics together
-- ============================================================================
SELECT
    m.trade_date,
    m.market,
    m.daily_volume_usd,
    m.daily_average_price,
    d.dau AS daily_active_users,
    oi.eod_open_interest_native,
    oi.eod_open_interest_native * m.daily_average_price as eod_open_interest_usd,
    f.avg_daily_funding_rate
FROM daily_trade_metrics m
LEFT JOIN daily_active_users d ON m.trade_date = d.trade_date AND m.market = d.market
LEFT JOIN eod_open_interest oi ON m.trade_date = oi.oi_date AND m.market = oi.market
LEFT JOIN daily_funding_rates f ON m.trade_date = f.rate_date AND m.market = f.market;

-- Create a temporary table from the view for immediate dashboarding use
CREATE OR REPLACE TEMPORARY TABLE TMP_BLUEFIN_PRO_METRICS AS
SELECT * FROM PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.BLUEFIN_PRO_DAILY_MARKET_METRICS_V;

Move Package Catalogue Tasks

For Events

create or replace task PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.POPULATE_EVENT_TYPE_SAMPLES_TASK
	warehouse=DEFI_WH
	schedule='USING CRON 0 * * * * America/New_York'
	COMMENT='Populates and updates the EVENT_TYPE_SAMPLES_TB with new unique event types and their sample data, avoiding full scans by only processing recent events and checking for existing event types.'
	as BEGIN
  CREATE TABLE IF NOT EXISTS PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.EVENT_TYPE_SAMPLES_TB (
      EVENT_TYPE VARCHAR(16777216) PRIMARY KEY,
      PACKAGE VARCHAR(16777216) COMMENT 'The package ID part of the event type.',
      MODULE VARCHAR(16777216) COMMENT 'The module name part of the event type.',
      FIRST_APPEARANCE_TRANSACTION_DIGEST VARCHAR(16777216),
      SAMPLE_EVENT_JSON VARIANT,
      FIRST_APPEARANCE_TIMESTAMP_MS NUMBER(38,0),
      FIRST_APPEARANCE_UTC TIMESTAMP_LTZ(3),
      LAST_UPDATED_AT TIMESTAMP_NTZ(9) DEFAULT CURRENT_TIMESTAMP()
  )
  COMMENT='Auxiliary table storing a single sample transaction and event JSON for each unique event type. Populated by POPULATE_EVENT_TYPE_SAMPLES_TASK.';  INSERT INTO PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.EVENT_TYPE_SAMPLES_TB (
      EVENT_TYPE,
      PACKAGE,
      MODULE,
      FIRST_APPEARANCE_TRANSACTION_DIGEST,
      SAMPLE_EVENT_JSON,
      FIRST_APPEARANCE_TIMESTAMP_MS,
      FIRST_APPEARANCE_UTC
  )
  SELECT
      e.event_type,
      SPLIT_PART(e.event_type, '::', 1) AS package,
      SPLIT_PART(e.event_type, '::', 2) AS module,
      ANY_VALUE(e.transaction_digest) AS first_appearance_transaction_digest,
      ANY_VALUE(e.event_json) AS sample_event_json,
      MIN(e.timestamp_ms) AS first_appearance_timestamp_ms,
      TO_TIMESTAMP_LTZ(MIN(e.timestamp_ms), 3) AS first_appearance_utc
  FROM
      event_parquet e
  WHERE
      e.timestamp_ms > COALESCE(
          (SELECT MAX(first_appearance_timestamp_ms) FROM PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.EVENT_TYPE_SAMPLES_TB),
          0
      )
      AND NOT EXISTS (
          SELECT 1
          FROM PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.EVENT_TYPE_SAMPLES_TB ets
          WHERE ets.event_type = e.event_type
      )
  GROUP BY
      e.event_type;
END;

For Objects

create or replace task PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.POPULATE_OBJECT_TYPE_SAMPLES_TASK
	warehouse=DEFI_WH
	schedule='USING CRON 0 * * * * America/New_York'
	COMMENT='Populates and updates the OBJECT_TYPE_SAMPLES_TB with new unique object types and their sample data, avoiding full scans by only processing recently created objects and checking for existing object types.'
	as BEGIN
  -- Create the OBJECT_TYPE_SAMPLES_TB table if it does not already exist.
  -- This table stores a single sample object JSON and metadata for each unique object type encountered on-chain.
  CREATE TABLE IF NOT EXISTS PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.OBJECT_TYPE_SAMPLES_TB (
      OBJECT_TYPE VARCHAR(16777216) PRIMARY KEY COMMENT 'The full type string of the object.',
      PACKAGE VARCHAR(16777216) COMMENT 'The package ID part of the object type (e.g., "0x2").',
      MODULE VARCHAR(16777216) COMMENT 'The module name part of the object type (e.g., "coin").',
      TYPE VARCHAR(16777216) COMMENT 'The specific type name within the module (e.g., "CoinMetadata").',
      FIRST_APPEARANCE_OBJECT_ID VARCHAR(16777216) COMMENT 'The object ID of the first created instance of this type.',
      SAMPLE_OBJECT_JSON VARIANT COMMENT 'A sample JSON representation of the object data at its first appearance.',
      FIRST_APPEARANCE_PREVIOUS_TRANSACTION VARCHAR(16777216) COMMENT 'The transaction digest of the transaction that created the first instance of this object type.',
      FIRST_APPEARANCE_TIMESTAMP_MS NUMBER(38,0) COMMENT 'The timestamp in milliseconds of the first creation event for this object type.',
      FIRST_APPEARANCE_UTC TIMESTAMP_LTZ(9) COMMENT 'The UTC timestamp of the first creation event for this object type, converted for easier readability.',
      LAST_UPDATED_AT TIMESTAMP_NTZ(9) DEFAULT CURRENT_TIMESTAMP() COMMENT 'Timestamp when this record was last updated or inserted.'
  )
  COMMENT='Auxiliary table storing a single sample object JSON and metadata for each unique object type. Populated by POPULATE_OBJECT_TYPE_SAMPLES_TASK.';

  -- Insert new unique object types into the samples table.
  -- This query is designed to be incremental, only processing objects created since the last successful run
  -- and adding only those object types that are not yet present in the `OBJECT_TYPE_SAMPLES_TB`.
  INSERT INTO PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.OBJECT_TYPE_SAMPLES_TB (
      object_type,
      package,
      module,
      type,
      first_appearance_object_id,
      sample_object_json,
      first_appearance_previous_transaction,
      first_appearance_timestamp_ms,
      first_appearance_utc
  )
  SELECT
      o.type AS object_type,
      -- Extract the package part from the object_type (e.g., '0x2::coin::CoinMetadata' -> '0x2')
      SPLIT_PART(o.type, '::', 1) AS package,
      -- Extract the module part from the object_type (e.g., '0x2::coin::CoinMetadata' -> 'coin')
      SPLIT_PART(o.type, '::', 2) AS module,
      -- Extract the specific type name part from the object_type (e.g., '0x2::coin::CoinMetadata' -> 'CoinMetadata')
      REGEXP_SUBSTR(o.type, '^[^:]*::[^:]*::(.*)', 1, 1, 'e', 1) AS type,
      -- Select any object_id for the first appearance of this type (since we group by type)
      ANY_VALUE(o.object_id) AS first_appearance_object_id,
      -- Select a sample JSON for the first appearance of this type
      ANY_VALUE(o.object_json) AS sample_object_json,
      -- Select the transaction digest of the first transaction that created this object type
      ANY_VALUE(o.previous_transaction) AS first_appearance_previous_transaction,
      -- Find the minimum timestamp to identify the very first creation event for each object type
      MIN(o.timestamp_ms) AS first_appearance_timestamp_ms,
      -- Convert the minimum timestamp to a readable UTC format
      TO_TIMESTAMP_LTZ(MIN(o.timestamp_ms), 3) AS first_appearance_utc
  FROM
      PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.object_parquet2 o
  WHERE
      -- Filter for objects that were 'Created', as we are tracking their first appearance
      o.object_status = 'Created'
      -- Optimizes the scan by only looking at data newer than the latest entry in the samples table.
      -- COALESCE with 0 ensures it processes all data from the beginning on the very first run.
      AND o.timestamp_ms > COALESCE(
          (SELECT MAX(first_appearance_timestamp_ms) FROM PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.OBJECT_TYPE_SAMPLES_TB),
          0
      )
      -- Ensures that only truly new object types (not already in the samples table) are inserted.
      AND NOT EXISTS (
          SELECT 1
          FROM PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.OBJECT_TYPE_SAMPLES_TB existing_ots
          WHERE existing_ots.object_type = o.type
      )
  GROUP BY
      o.type; -- Group by object type to ensure one entry per unique type, capturing its first observed creation.
END;

Snowflake Table Data Catalogue

create or replace view PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.DATA_CATALOGUE_VIEW(
	TABLE_SCHEMA,
	TABLE_NAME,
	TABLE_TYPE,
	DESCRIPTION,
	COLUMN_METADATA_JSON,
	COLUMN_COMMENTS,
	LAST_ALTERED
) COMMENT='Provides a comprehensive catalogue of tables and views within the PIPELINE_V2_GROOT_SCHEMA, including table-level descriptions and detailed metadata for each column (name, type, and comment).'
 as
SELECT
    t.TABLE_SCHEMA AS TABLE_SCHEMA,       -- Schema name
    t.TABLE_NAME AS TABLE_NAME,           -- Table name
    t.TABLE_TYPE AS TABLE_TYPE,           -- Type: BASE TABLE, VIEW, etc.
    t.COMMENT AS DESCRIPTION,             -- Table-level description
    OBJECT_AGG(c.COLUMN_NAME, c.DATA_TYPE::VARIANT) AS COLUMN_METADATA_JSON,       -- Column names and types as a JSON object (cast DATA_TYPE to VARIANT)
    ARRAY_AGG(c.COMMENT) WITHIN GROUP (ORDER BY c.ORDINAL_POSITION) AS COLUMN_COMMENTS,        -- Column-level comments as an array
    t.LAST_ALTERED AS LAST_ALTERED        -- Last altered time
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.COLUMNS c
    ON t.TABLE_CATALOG = c.TABLE_CATALOG
    AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
    AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_SCHEMA = 'PIPELINE_V2_GROOT_SCHEMA'
GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME, t.TABLE_TYPE, t.COMMENT, t.LAST_ALTERED;

Bluefin Daily Market View Output:

create or replace view PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.BLUEFIN_PRO_DAILY_MARKET_METRICS_V(
	TRADE_DATE,
	MARKET,
	DAILY_VOLUME_USD,
	DAILY_AVERAGE_PRICE,
	DAILY_ACTIVE_USERS,
	EOD_OPEN_INTEREST_NATIVE,
	EOD_OPEN_INTEREST_USD,
	AVG_DAILY_FUNDING_RATE
) COMMENT='Provides daily aggregated metrics for Bluefin Pro perpetual markets. It includes daily trading volume (USD), volume-weighted average price (USD), daily active users (DAU), end-of-day (EOD) open interest in both native token and USD, and the average daily funding rate. Data is sourced from Bluefin Pro package events (0xe744...). NOTE: A manual decimal correction (+1) is applied to BTC-PERP, ETH-PERP, and SOL-PERP markets to correct an observed 10x scaling discrepancy in the raw on-chain data for accurate calculations.'
 as
WITH
-- ============================================================================
-- STEP 1: Get the latest on-chain metadata for each market (e.g., decimals)
-- ============================================================================
market_metadata AS (
    SELECT
        event_json:perpetual:symbol::string as market,
        -- Derive decimal count from on-chain value (e.g., 8000000000 -> 8)
        event_json:perpetual:base_asset_decimals::number / 1e9 as base_asset_decimals
    FROM PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.EVENT_PARQUET
    WHERE event_type IN (
        '0xe74481697f432ddee8dd6f9bd13b9d0297a5b63d55f3db25c4d3b5d34dad85b7::events::PerpetualUpdate',
        '0xe74481697f432ddee8dd6f9bd13b9d0297a5b63d55f3db25c4d3b5d34dad85b7::events::PerpetualSynced'
    )
    AND market IS NOT NULL
    AND base_asset_decimals IS NOT NULL
    QUALIFY ROW_NUMBER() OVER (PARTITION BY market ORDER BY timestamp_ms DESC) = 1
),

-- ============================================================================
-- STEP 2: Calculate daily trading volume, average price, and active users
-- ============================================================================
trade_details AS (
    SELECT
        t.TIMESTAMP_MS,
        t.EVENT_JSON:market::string as market,
        t.EVENT_JSON:fill_price::number as fill_price,
        t.EVENT_JSON:fill_quantity::number as fill_quantity,
        t.EVENT_JSON:maker:address::string as maker_address,
        t.EVENT_JSON:taker:address::string as taker_address,
        -- MANUAL CORRECTION: Add 1 to decimals for specific markets due to
        -- an observed 10x discrepancy in their `fill_quantity` scaling.
        CASE
            WHEN m.market IN ('BTC-PERP', 'ETH-PERP', 'SOL-PERP') THEN m.base_asset_decimals + 1
            ELSE m.base_asset_decimals
        END as base_asset_decimals
    FROM PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.EVENT_PARQUET t
    JOIN market_metadata m ON t.EVENT_JSON:market::string = m.market
    WHERE t.EVENT_TYPE = '0xe74481697f432ddee8dd6f9bd13b9d0297a5b63d55f3db25c4d3b5d34dad85b7::events::TradeExecuted'
),

trade_participants AS (
    SELECT TIMESTAMP_MS, market, maker_address as user_address FROM trade_details
    UNION
    SELECT TIMESTAMP_MS, market, taker_address as user_address FROM trade_details
),

daily_active_users AS (
    SELECT
        DATE(TO_TIMESTAMP(timestamp_ms/1000)) as trade_date,
        market,
        COUNT(DISTINCT user_address) as dau
    FROM trade_participants
    GROUP BY 1,2
),

daily_trade_metrics AS (
    SELECT
        DATE(TO_TIMESTAMP(timestamp_ms / 1000)) as trade_date,
        market,
        SUM((fill_price / 1e9) * (fill_quantity / POW(10, base_asset_decimals))) as daily_volume_usd,
        SUM(fill_price * fill_quantity) / SUM(fill_quantity) / 1e9 as daily_average_price
    FROM trade_details
    GROUP BY 1, 2
),

-- ============================================================================
-- STEP 3: Calculate average daily funding rate from 'FundingRateUpdated' events
-- ============================================================================
daily_funding_rates AS (
    SELECT
        DATE(TO_TIMESTAMP(timestamp_ms / 1000)) as rate_date,
        event_json:market::string as market,
        AVG((CASE WHEN event_json:rate:sign::boolean THEN 1 ELSE -1 END * event_json:rate:value::number) / 1e9) as avg_daily_funding_rate
    FROM PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.EVENT_PARQUET
    WHERE event_type = '0xe74481697f432ddee8dd6f9bd13b9d0297a5b63d55f3db25c4d3b5d34dad85b7::events::FundingRateUpdated'
    GROUP BY 1, 2
),

-- ============================================================================
-- STEP 4: Get End-of-Day Open Interest Snapshot
-- ============================================================================
open_interest_snapshots AS (
    SELECT
        t.timestamp_ms,
        t.event_json:position:perpetual::string as market,
        -- MANUAL CORRECTION: Apply the same decimal adjustment here for `position:size`
        SUM(t.event_json:position:size::number / POW(10,
            CASE
                WHEN m.market IN ('BTC-PERP', 'ETH-PERP', 'SOL-PERP') THEN m.base_asset_decimals + 1
                ELSE m.base_asset_decimals
            END
        )) as oi_snapshot_native
    FROM PIPELINE_V2_GROOT_DB.PIPELINE_V2_GROOT_SCHEMA.EVENT_PARQUET t
    JOIN market_metadata m ON t.event_json:position:perpetual::string = m.market
    WHERE t.event_type = '0xe74481697f432ddee8dd6f9bd13b9d0297a5b63d55f3db25c4d3b5d34dad85b7::events::FundingRateApplied'
      AND t.event_json:position:is_long::boolean = true
    GROUP BY 1, 2
),

eod_open_interest AS (
    SELECT
        DATE(TO_TIMESTAMP(timestamp_ms/1000)) as oi_date,
        market,
        oi_snapshot_native as eod_open_interest_native
    FROM open_interest_snapshots
    QUALIFY ROW_NUMBER() OVER (PARTITION BY oi_date, market ORDER BY timestamp_ms DESC) = 1
)

-- ============================================================================
-- FINAL STEP: Join all the daily metrics together
-- ============================================================================
SELECT
    m.trade_date,
    m.market,
    m.daily_volume_usd,
    m.daily_average_price,
    d.dau AS daily_active_users,
    oi.eod_open_interest_native,
    oi.eod_open_interest_native * m.daily_average_price as eod_open_interest_usd,
    f.avg_daily_funding_rate
FROM daily_trade_metrics m
LEFT JOIN daily_active_users d ON m.trade_date = d.trade_date AND m.market = d.market
LEFT JOIN eod_open_interest oi ON m.trade_date = oi.oi_date AND m.market = oi.market
LEFT JOIN daily_funding_rates f ON m.trade_date = f.rate_date AND m.market = f.market;

Intro

This article presents a framework for building data pipelines on the Sui blockchain using Sentio, illustrated through a complete implementation to analyze Rootlets NFT holders. Sentio is used for efficient data backfilling, custom processing with TypeScript, and real-time analysis via its SQL Studio. The article culminates in demonstrating a user-friendly Sentio dashboard that queries Rootlet ownership by wallet address. This approach provides a reusable and adaptable foundation for developing data-driven applications and insights across the Sui ecosystem.

Sentio processors, written in TypeScript, provide a flexible way to construct Sui data pipelines. They manage the essential ETL (Extract, Transform, Load) steps for interacting with Sui blockchain data. 

Processors define how data is extracted, transformed (including handling Sui-specific features like object ownership), and loaded into Sentio's database. They serve as the central logic for data processing, ensuring only relevant and correctly structured data is stored for analysis. The complete processor code for this article is available here.

Entities in Sentio are analogous to custom tables, structuring data for optimized analysis. Entities are defined in a store.graphql file, located at src/schema/store.graphql. This file uses a GraphQL-like syntax to describe the structure of our custom tables.

For the Rootlets project, there are two main entities:

• RootletStaticFields: This entity captures the static fields of a Rootlet object, such as its description, image URL, and inherent traits (theme, accessories, etc.). This is information that doesn't change over time.

• RootletOwner: This entity tracks changes in Rootlet ownership. Every time a Rootlet is transferred or modified, a new entry is created in this entity, recording the sender, transaction digest, and timestamp. This is crucial for tracking who owns which Rootlets over time.

After defining entities in src/schema/store.graphql, the command yarn sentio build generates TypeScript code. This generated code facilitates interaction with these entities within the processor, enabling the creation and updating of records during data processing.

Sentio provides a built-in SQL Studio, a web-based interface where you can write and execute SQL queries against your data. As the backfilling process populates your entities (which now appear as tables), you can start writing SQL queries to analyze the data in real-time.

A unique holder count growth over time can be constructed using the RootletOwner table. This query is crucial for taking arbitrary historical snapshots of Rootlet ownership.

Sui NFT objects are stored either in kiosks or as wrapped objects, resulting in the true owner often being a nested object value. Instead of recursive searching for the true owner, the sender address is used. Sui's consensus mechanism performs a runtime validation check, ensuring that the object mutator is already the object's owner. Therefore, if the object is mutated, it is assumed that the mutation was performed by the owner. This principle allows the storage of ownership history in a table, tracking the transaction sender each time the object is mutated.

Here is what the chart looks like in the Sentio dashboard:

The full query is available in the Appendix.

A second query categorizes Rootlet owners qualitatively (see Appendix for the query). This query joins the theme value, a static object value stored in RootletStaticFields, to the latest owner from RootletOwner.

This dashboard integration demonstrates the flexibility of the Sentio data pipeline. Not only can queries provide aggregate statistics, but they also enable detailed, address-specific analysis, such as determining the exact Rootlet holdings of an address Moreover, this level of data granularity enables targeted airdrops, for example, rewarding holders based on specific Rootlet attributes or ownership history.

This article presented a comprehensive guide to building a Sui data pipeline for the Rootlets NFT collection using Sentio. The process began with defining custom entities to structure the data, followed by utilizing Sentio's TypeScript processors to handle data extraction, transformation, and the specific challenges of Sui object ownership. The integrated SQL Studio was then used to perform real-time analysis, generating queries for key metrics like holder count growth and thematic categorization. 

The culminating dashboard, parameterized by wallet address, demonstrates the power of this pipeline for in-depth analysis. This approach not only provides valuable insights into the Rootlets collection but also establishes a reusable framework for developing more sophisticated data pipelines and analytical tools within the Sui ecosystem.

WITH OrderedEvents AS (

   SELECT

       ro1.objectID,

       ro1.sender,

       toDate(ro1.timestamp / 1000) AS start_date,

       ifNull(argMin(toDate(ro2.timestamp / 1000), ro2.timestamp), toDate('2106-02-07')) AS end_date

   FROM RootletOwner ro1

   LEFT JOIN RootletOwner ro2

   ON ro1.objectID = ro2.objectID AND ro1.timestamp < ro2.timestamp

   GROUP BY ro1.objectID, ro1.sender, ro1.timestamp

),

-- Convert dates to integers for range function

DateRanges AS (

   SELECT

       objectID,

       sender,

       toUInt32(start_date) AS start_date_int,

       toUInt32(end_date) AS end_date_int

   FROM OrderedEvents

),

-- Expand date ranges into individual dates

ExpandedDates AS (

   SELECT

       objectID,

       sender,

       toDate(start_date_int + number) AS day

   FROM DateRanges

   ARRAY JOIN range(0, end_date_int - start_date_int) AS number

),

-- Get all distinct dates

AllDates AS (

   SELECT DISTINCT toDate(timestamp / 1000) AS day

   FROM RootletOwner

)

-- Join expanded dates with all dates and count distinct senders

SELECT

   ad.day,

   count(DISTINCT ed.sender) AS unique_holder_count

FROM AllDates ad

LEFT JOIN ExpandedDates ed ON ad.day = ed.day

GROUP BY ad.day

ORDER BY ad.day;

WITH LatestOwners AS (

   SELECT

       objectID,

       argMax(sender, timestamp) AS latest_sender

   FROM RootletOwner

   GROUP BY objectID

)

SELECT

   lo.latest_sender,

   s.theme,

   COUNT() AS theme_count

FROM LatestOwners AS lo

INNER JOIN RootletStaticFields AS s ON lo.objectID = s.id

GROUP BY lo.latest_sender, s.theme

ORDER BY theme_count DESC

• Shared liquidity pool object mutations have been consistently increasing, now occurring in nearly every Sui checkpoint (≈100% of blocks).

• Over 50% of transactions to mutate shared liquidity pool objects that pay the maximum gas fee end up above the median block position.

• Users paying max gas fees have a 0.001-0.002% chance of securing the top block position, compared to 7-10% for those not paying the highest fees.

• SIP-45 proposes increasing the max gas price to 1 trillion MIST and making transaction inclusion faster at the expense of increased validator network bandwidth utilization.

The Sui blockchain faces unique challenges in processing shared object transactions that require consensus. This analysis examines shared object change data across 28 weeks, focusing specifically on shared liquidity pool objects and their gas fee patterns. The findings inform critical decisions about SIP-45, a proposed change to transaction prioritization mechanisms.

The following data and query can be found in the appendix section and run using Sentio data studio. Objects are filtered specifically for shared objects such as liquidity pools and swaps. Query performance took about 8:30 minutes so it is recommended to run on a lower time frame first. All of the charts can be found in the Suinomics Dune dashboard under shared liquidity pool objects

Shared object mutations now occur in nearly every Sui checkpoint, indicating market saturationSui's shared objects are mutable, lack specific ownership, and enable complex interactions between multiple users by leveraging their own authorization logic within smart contracts. Unlike owned objects, which are tied to a single user and can be updated without consensus, shared objects require a consensus protocol to ensure safe and consistent modifications when accessed by multiple parties.

The most common examples of shared objects are liquidity pools, where users have shared access to many pools of liquidity. Over the past six months, the number of shared liquidity pool objects has reached saturation, with at least one shared object being mutated in almost every Sui checkpoint (equivalent to a block).

This saturation indicates growing network activity and highlights the importance of efficient transaction ordering mechanisms. The data reveals that shared object mutations have become a fundamental part of Sui's daily operations, particularly in DeFi applications where multiple users frequently interact with the same liquidity pools.

Max gas fees represent the highest amount a user is willing to pay for the computational and storage resources required to process their transaction. According to Sui documentation, gas fees are calculated based on the computation units and storage units consumed by a transaction, multiplied by their respective prices. The computation price itself is a combination of a network-wide reference price and an optional user-specified tip aimed at incentivizing faster processing by validators.

However, quantitative analysis of 28 weeks of transaction data, focusing on shared liquidity pool objects, reveals an intriguing insight: paying the maximum gas fee does not consistently guarantee a superior position within a block. The data shows that over 50% of transactions that opted for the highest gas fees ended up in positions that were worse than the median block position.

Ideally, users expect that higher gas fees would translate to higher priority and quicker inclusion in blocks, but this is only the case approximately 20% of the time. This discrepancy suggests that while the Sui network's gas pricing mechanism is designed to prioritize transactions based on fee offerings, other factors—such as network congestion and validator behavior—significantly influence transaction ordering. Consequently, users do not always benefit from setting their max gas fees to the highest possible values to maximize transaction inclusion.

Additionally, while the number of shared liquidity object mutations has increased, the number of max gas paid for these mutations has decreased. At the same time the number of shared objects that get the top position has been rising steadily. The below chart shows that a sender has a higher probability of getting top of block (~ 7 -10%) over those paying the max gas fee (.001 - .002%).

This misalignment between gas fees and transaction positioning presents a significant challenge for users seeking predictable transaction inclusion times. The data suggests that the current gas fee mechanism may need refinement to better serve its intended purpose of transaction prioritization.

SIP-45 introduces greater economic incentives to utilize more validator bandwidth for more reliable inclusion. However, this adjustment could inadvertently drive spamming, as users and bots exploit the opportunity to secure consistent inclusion in blocks. An amplification factor of 5 has been proposed as the supremum gas price required to be immediately broadcasted. Thus if a reference gas price is 750 MIST and the amplification factor is 5, then a transaction will only need 3,750 MIST to be broadcast faster.

SIP-45: Prioritized Transaction Submission a SIP that is going to test the boundaries of consensus spam on Sui. The major changes are:

1. Increase the max gas price from 99,999 MIST to 1,000,000,000,000 (1 trillion MIST).

1. Allow transactions that pay a higher gas price to be immediately broadcasted.

Chains offering low transaction costs and high blockspace are inherently vulnerable to transactions spamming, as bots exploit these conditions to to maximize profits. While this increases blockspace usage, it also boosts validator compensation, often leading to higher yields.

A brief numerical analysis is provided in the SIP-45 proposal which demonstrates potential effects with an amplification factor of 5. The authors make the assumption that most transactions are not competitive in nature. They isolate competitive transactions based on the amount of gas being paid. An important observation in the initial data shows that the gas price bucket distribution is heavily skewed at the tail end at the 90,000 - 100,000 MIST maximum range, suggesting that a higher gas price is warranted.

Choosing an amplification factor will strike a balance between higher yield vs increased bandwidth usage. For example a low amplification factor of 2 will make it very low cost to spam the network, soaking up additional bandwidth. A higher amplification factor such as 100 will decrease the subset of transactions that will spam the network, making the supremum gas price a much higher hurdle.

Given that the numerical analysis indicated a heavily skewed tail at the current maximum gas price range already, indicating an active market at that price point, an argument could be made that a higher amplification factor of 120 could be set as a conservative factor, making the supremum gas price 750 MIST * 120 = 90,000 MIST a good starting point and can be gradually lowered from there as a tradeoff to introducing a new priority fee feature while still conserving validator bandwidth.

Another reason to set a more conservative amplification factor is to also to understand what the effects of increasing the max gas fee will have on network activity. With the current fee cap, it is not clear what the upper bounds for max gas fees will be for contentious shared objects and whether the higher certainty of transaction inclusion will also spark higher demand that is not reflected in historical data numbers. Intuitively as the surface area of mutated shared liquidity pool objects increases, so should too the number of contentious opportunities.

This article expands on the initial results so that a more data informed decision can be made. A more sui-native way to quantify contentious transactions based on ownership. If an object has a single owner, then these transactions get filtered out into a fast path, skipping the consensus mechanism. If an object is a shared object and can have different owners, then these objects will fall into state contention based on user is claiming ownership and getting the state lock on that object.

The implementation of SIP-45 represents a critical evolution in Sui's transaction processing mechanism. While the proposed changes offer potential improvements in transaction inclusion predictability, careful consideration must be given to the amplification factor to prevent network congestion while maintaining economic efficiency. Ongoing monitoring and analysis of shared object mutations and gas fee patterns will be essential to fine-tune these parameters and ensure optimal network performance.

WITH object_changes_filter AS (
    SELECT DISTINCT
        digest,
        checkpoint
    FROM sui.object_changes
    WHERE owner_type = 'shared'
      AND timestamp >= now() - INTERVAL 28 WEEK
      AND (object_type LIKE '%pool%' OR object_type LIKE '%swap%')
),
recent_transactions AS (
    SELECT 
        t.digest, 
        t.checkpoint, 
        t.sender,
        t.gas_price, 
        t.transaction_position,
        toStartOfHour(t.timestamp) AS hour
    FROM sui.transactions AS t
    INNER JOIN object_changes_filter AS ocf
    ON t.digest = ocf.digest AND t.checkpoint = ocf.checkpoint
),
precomputed_metrics AS (
    SELECT
        hour,
        min(rt.gas_price) AS min_gas_price,
        max(rt.gas_price) AS max_gas_price,
        min(rt.transaction_position) AS min_position,
        max(rt.transaction_position) AS max_position,
        median(rt.transaction_position) AS median_position
    FROM recent_transactions AS rt
    GROUP BY hour
),
aggregated_data AS (
    SELECT
        rt.hour,
        avg(rt.gas_price) AS avg_gas_price,
        pm.min_gas_price,
        pm.max_gas_price,
        avg(rt.transaction_position) AS avg_position,
        pm.min_position,
        pm.max_position,
        pm.median_position,
        countIf(rt.gas_price = pm.min_gas_price) AS count_min_gas_price,
        countIf(rt.gas_price = pm.max_gas_price) AS count_max_gas_price,
        countIf(rt.transaction_position = pm.min_position) AS count_min_position,
        countIf(rt.transaction_position = pm.max_position) AS count_max_position,
        countIf(rt.gas_price = pm.max_gas_price AND rt.transaction_position = pm.min_position) AS max_gas_in_min_position,
        countIf(rt.gas_price = pm.max_gas_price AND rt.transaction_position > pm.median_position) AS max_gas_higher_than_median_position,
        uniqExact(rt.checkpoint) AS unique_checkpoints_count,
        count() AS total_rows
    FROM recent_transactions AS rt
    INNER JOIN precomputed_metrics AS pm
    ON rt.hour = pm.hour
    GROUP BY rt.hour, pm.min_gas_price, pm.max_gas_price, pm.min_position, pm.max_position, pm.median_position
)

SELECT
    hour,
    avg_gas_price,
    min_gas_price,
    max_gas_price,
    avg_position,
    min_position,
    max_position,
    median_position,
    count_min_gas_price,
    count_max_gas_price,
    count_min_position,
    count_max_position,
    max_gas_in_min_position,
    max_gas_higher_than_median_position,
    unique_checkpoints_count,
    total_rows AS total_shared_objects_hourly
FROM aggregated_data
ORDER BY hour DESC;

Sentio is a game-changing development platform that enables end-to-end data observability for web3 data pipelines. The platform provides a fully integrated, hosted experience that combines modern monitoring, alerting, and debugging capabilities.

What makes Sentio particularly powerful is its real-time processing architecture: developers write and deploy typed processor code to Sentio, which automatically handles everything from historical data backfilling to real-time blockchain streaming, including automatic adjustments for chain reorganizations.

Sentio's TypeScript pipeline interface transforms complex multi-chain development into a straightforward process, eliminating the traditional headaches of handling different blockchain protocols across multiple smart contract languages. The platform excels at:

1. Supporting diverse blockchains- EVM, SVM, MOVE, Fuel, and Starknet (supported networks)

2. Strongly typed objects for events and functions

3. Data processing, observability, and debugging wrapped up in a hosted db solution with easy sql and graphql endpoint creation for analytics

4. Robust testing suites for data pipelines

To illustrate Sentio's capabilities, let's consider the Sui LST Dashboard, a comprehensive analytics tool built using Sentio pipelines and integrated with Dune's frontend. The sentio processor code can be found here. This dashboard provides real-time insights into the liquid staking ecosystem on the Sui blockchain.

• Real-Time Data Streaming: Continuously monitors transactions and events on the Sui network.

• Comprehensive Analytics: Utilizes SQL and GraphQL endpoints for detailed data queries and aggregations.

• Seamless Integration with Dune: Enables the creation of dynamic and interactive visualizations, making data accessible and actionable for stakeholders.

This infrastructure enables developers to quickly spin up observable data platforms that emit pipeline metrics, event logs, entities, webhook and http endpoint integrations. The great thing here is that it’s all done in the background and all that’s left are for developers to use a polished product.

The integrated nature of these features creates a seamless development experience, where monitoring, debugging, and analytics all work together in real-time, making it significantly easier to build and maintain robust cross chain analytics solutions.

This section is an overview that shows an end to end flow from setting up a Sentio processor with a dune dashboard as an end product. To replicate this process, simply fork the repository and add it to your own Sentio project.

The main entrypoint that Sentio uses is the processor.ts file for instructions on how to index data. Contracts can be added with the command yarn sentio add –chain sui_mainnet contract_address. The abi is generally automatically fetched. Then the command yarn sentio build to generate the typed files. 

A processor can be defined by importing a specific class generated from the typings that contain functions. For example you can CTRL + F for onEvent… to find the event function bindings and click through the code to find the decoded data fields that each event holds as well. Some well prompted AI requests can make this process delightfully easy to set up multiple events from a single contract. 

Once the processor code has been defined, run the command yarn sentio upload to upload the processor code to Sentio, which will be queued up for indexing. Right out of the box, Sentio is pretty good about automatically finding the best start block point. The indexing (backfilling) process is relatively fast as well. Along the way, additional logs can be implemented into the processor to troubleshoot a data pipeline such as if there is data missing or something is not working the way it should be. These logs will appear in real time when the data gets backfilled. 

While Sentio is backfilling data, the project tables get populated in real time, so the query construction work can get started before data has finished backfilling completely. The Data Studio is where exploratory data analytics can be done and queries created for analytics such as daily aggregations, joins, and other transformations.

After the query is ready, endpoints can be created from saved queries in 2 clicks. After the endpoint is created, the data is ready to be used outside of Sentio. In this case, Dune is used as a hosted frontend of choice. An AI connector can be used to craft Dune queries from the Sentio endpoint (see AI Prompt Miscellaneous Section).

Once the data is in dune, then it can be treated as if it was any other Dune query and the dashboard can be created as usual. Note that the query has to be private to hide the Sentio API keys.

Maintaining robust and observable data pipelines is paramount for the success of blockchain applications. Sentio stands out as a powerful tool for developers and data engineers, providing a seamless, integrated platform that streamlines multi-chain development and boosts real-time data observability.

By automating complex processes such as historical data backfilling and real-time blockchain streaming, Sentio not only alleviates the technical burden but also accelerates the development lifecycle. Its comprehensive feature set, including strongly typed objects and robust testing suites, empowers teams to build, monitor, and debug their data pipelines with ease and confidence. 

As blockchain technology continues to scale and diversify, data observability tools like Sentio become indispensable for ensuring that data remains reliable, accessible, and actionable.

Context: You are using Dune's LiveFetch functions to execute SQL queries that interact with external APIs. Below is the documentation for the LiveFetch feature, followed by an example endpoint. Use this information to generate SQL queries tailored to specific API endpoints.

Functions and Operators

LiveFetch functions

• Description: LiveFetch functions send HTTP requests to a specified URL, allowing interaction with HTTP servers directly within SQL queries. They can fetch data from external APIs or invoke remote procedure calls.

Supported Functions:

1. http_get(url: varchar) → varchar

2. http_get(url: varchar, headers: array(varchar)) → varchar

3. http_post(url: varchar, body: varchar) → varchar

4. http_post(url: varchar, body: varchar, headers: array(varchar)) → varchar

Additional Details:

• Call timeout: 5 seconds

• Throttling: 50 requests per second per proxy

• Response size limit: 2 MiB

Request size limit (http_post): 1,000,000 bytes

You have an API endpoint accessible via the following curl command:

curl -L -X POST 'https://endpoint.sentio.xyz/evandekim/sui-lst/circulating_lsts' \

     -H 'api-key: api_key_here \

     -H 'Content-Type: application/json' \

     --data-raw '{}'

Your Task:

Generate a Dune SQL query using the LiveFetch functions to interact with the provided API endpoint. Ensure that all necessary headers and the request body are correctly included. If the response is in JSON format, demonstrate how to parse and extract specific fields from the response.

• URL: https://endpoint.sentio.xyz/evandekim/sui-lst/circulating_lsts

• Method: POST

• Headers:

  • api-key: api_key_here

  • Content-Type: application/json

• Body: {}

Requirements:

1. Use http_post to send the POST request.

2. Include the necessary headers.

3. Handle the JSON response to extract relevant data (e.g., list of circulating LSTs).

4. Ensure the query adheres to Dune's LiveFetch limitations.

SELECT json_parse(

    http_post(

        'https://endpoint.sentio.xyz/evandekim/sui-lst/circulating_lsts',

        '{}',

        ARRAY[

            'api-key: api_key_here,

            'Content-Type: application/json'

        ]

    )

) AS response_data

To further process the JSON response, you can use Dune's JSON functions. For example:

SELECT

    json_extract_scalar(response_data, '$.field_name') AS extracted_field

FROM (

    SELECT json_parse(

        http_post(

            'https://endpoint.sentio.xyz/evandekim/sui-lst/circulating_lsts',

            '{}',

            ARRAY[

                'api-key: api_key_here,

                'Content-Type: application/json'

            ]

        )

    ) AS response_data

)

WITH response AS (

    SELECT http_post(

        'https://endpoint.sentio.xyz/evandekim/sui-lst/circulating_lsts',

        '{}',

        ARRAY[

            'api-key: api_key_here,

            'Content-Type: application/json'

        ]

    ) AS resp

),

parsed AS (

    SELECT json_parse(resp) AS j 

    FROM response

),

rows_array AS (

    SELECT json_extract(j, '$.syncSqlResponse.result.rows') AS rows_j

    FROM parsed

),

unnested_rows AS (

    SELECT row_data

    FROM rows_array

    CROSS JOIN UNNEST(CAST(rows_j AS array(json))) AS t(row_data)

)

SELECT

    json_extract_scalar(row_data, '$.event_date') AS event_date,

    json_extract_scalar(row_data, '$.token_name') AS token_name,

    CAST(json_extract_scalar(row_data, '$.circulating_supply') AS double) AS circulating_supply,

    CAST(json_extract_scalar(row_data, '$.cumulative_minted_conv') AS double) AS cumulative_minted,

    CAST(json_extract_scalar(row_data, '$.cumulative_redeemed_conv') AS double) AS cumulative_redeemed,

    CAST(json_extract_scalar(row_data, '$.circulating_supply_usd') AS double) AS circulating_supply_usd,

    CAST(json_extract_scalar(row_data, '$.cumulative_minted_usd') AS double) AS cumulative_minted_usd,

    CAST(json_extract_scalar(row_data, '$.cumulative_redeemed_usd') AS double) AS cumulative_redeemed_usd

FROM unnested_rows

WHERE json_extract_scalar(row_data, '$.token_name') not in ('afsui', 'vsui', 'hasui', 'springsui_lsts', 'spring_sui')

Introduction

In today's blockchain ecosystem, querying transaction data across multiple chains has become an essential task for developers seeking to analyze trends, monitor transactions, and build cross-chain applications. Hypermanager enters as a solution to the challenges inherent in cross-chain querying. Built on Hypersync—a client that operates atop Envio's Hyperindex—Hypermanager enables seamless, efficient orchestration of cross-chain log queries across EVM networks.

Hypersync provides many powerful features, including querying transactions, blocks, logs, and traces and writing results directly to Polars DataFrames, decoding logs, and saving data as parquet files. However, re-using Hypersync queries across different applications can be tedious. Developers often need to repeat the same boilerplate code to fetch blocks, logs, and transactions, leading to redundancy and inefficiency.

Hypermanager addresses these challenges by serving as a query orchestration tool that enables reusable queries across various applications and multiple chains. By centralizing configurations and reducing boilerplate code, Hypermanager streamlines the process of cross-chain data analysis, offering developers a more efficient and scalable solution. This revised version maintains the core information while providing a bit more context and a smoother transition to the next section about Hypermanager's specific benefits.

Hypermanager serves as a powerful query orchestration tool, enabling reusable queries across various applications and multiple chains. By reducing repetitive coding and centralizing configuration, it streamlines workflows and saves developers significant time and effort.

Developers new to Hypermanager will find the learning curve manageable, especially those already familiar with blockchain queries. The tool abstracts away the complexities of the Hypersync client, allowing users to focus on their data analysis tasks rather than worrying about low-level implementation details.

Hypermanager's intuitive API, comprehensive documentation, and practical examples accelerate the onboarding process for new users. The significant reduction in boilerplate code not only enhances efficiency but also lowers the entry barrier for developers venturing into blockchain data analysis.

The following example illustrates the contrast between using raw Hypersync code and Hypermanager to query blocks and transactions. Without Hypermanager, developers must continuously redefine transaction and block columns within each application, leading to verbose and redundant code.

import asyncio
import hypersync
import polars as pl
import time

from hypersync import ColumnMapping, DataType, TransactionField, BlockField, TraceField, TransactionSelection


async def historical_blocks_txs_sync():
    client = hypersync.HypersyncClient(
        hypersync.ClientConfig(url='http://eth.hypersync.xyz')
    )

    to_block: int = 20035000
    from_block: int = 20000000

    # # add +/-1 to the block range because the query is exclusive to the block number
    query = hypersync.Query(
        from_block=from_block-1,
        to_block=to_block+1,
        include_all_blocks=True,
        transactions=[TransactionSelection()],
        field_selection=hypersync.FieldSelection(
            block=[e.value for e in BlockField],
            transaction=[e.value for e in TransactionField],
        )
    )
    config = hypersync.StreamConfig(
        hex_output=hypersync.HexOutput.PREFIXED,
        column_mapping=ColumnMapping(
            transaction={
                TransactionField.GAS_USED: DataType.FLOAT64,
                TransactionField.MAX_FEE_PER_BLOB_GAS: DataType.FLOAT64,
                TransactionField.MAX_PRIORITY_FEE_PER_GAS: DataType.FLOAT64,
                TransactionField.GAS_PRICE: DataType.FLOAT64,
                TransactionField.CUMULATIVE_GAS_USED: DataType.FLOAT64,
                TransactionField.EFFECTIVE_GAS_PRICE: DataType.FLOAT64,
                TransactionField.NONCE: DataType.INT64,
                TransactionField.GAS: DataType.FLOAT64,
                TransactionField.MAX_FEE_PER_GAS: DataType.FLOAT64,
                TransactionField.MAX_FEE_PER_BLOB_GAS: DataType.FLOAT64,
                TransactionField.VALUE: DataType.FLOAT64,
            },
            block={
                BlockField.GAS_LIMIT: DataType.FLOAT64,
                BlockField.GAS_USED: DataType.FLOAT64,
                BlockField.SIZE: DataType.FLOAT64,
                BlockField.BLOB_GAS_USED: DataType.FLOAT64,
                BlockField.EXCESS_BLOB_GAS: DataType.FLOAT64,
                BlockField.BASE_FEE_PER_GAS: DataType.FLOAT64,
                BlockField.TIMESTAMP: DataType.INT64,
            }
        )
    )
	
	# save data into parquet files in "data" folder
    return await client.collect_parquet("data", query, config)

Efficiency Gains with Hypermanager:

By using Hypermanager, the same query can be completed in about 10 lines of code, reducing boilerplate by 6x and greatly improving readability:

import asyncio
import polars as pl

from hypermanager.manager import HyperManager

hypersync_client: str = "https://eth.hypersync.xyz"

async def get_events():
    manager = HyperManager(url=hypersync_client)

    df: pl.DataFrame = await manager.get_blocks(
        from_block=20000000, to_block=20035000, save_data=True
    )

if __name__ == "__main__":
    asyncio.run(get_events())

With Hypermanager, developers no longer need to manually define columns or repeatedly initialize data maps. The tool handles all standard configurations, allowing users to focus more on data analysis and customizing the data pipeline than setting all up.

When using Hypersync directly, developers must define and decode each event signature individually. Below is an example where each event signature is defined in Hypersync:

import hypersync
import asyncio


# Define events (matching the structure from the second code block)
event_1 = (
    "V3FundsDeposited(address inputToken,address outputToken,uint256 inputAmount,"
    "uint256 outputAmount,uint256 indexed destinationChainId,uint32 indexed depositId,"
    "uint32 quoteTimestamp,uint32 fillDeadline,uint32 exclusivityDeadline,"
    "address indexed depositor,address recipient,address exclusiveRelayer,bytes message)"
)
event_2 = (
    "RequestedSpeedUpV3Deposit(uint256 updatedOutputAmount,uint32 indexed depositId,"
    "address indexed depositor,address updatedRecipient,bytes updatedMessage,"
    "bytes depositorSignature)"
)
event_3 = (
    "FilledV3Relay(address inputToken,address outputToken,uint256 inputAmount,"
    "uint256 outputAmount,uint256 repaymentChainId,uint256 indexed originChainId,"
    "uint32 indexed depositId,uint32 fillDeadline,uint32 exclusivityDeadline,"
    "address exclusiveRelayer,address indexed relayer,address depositor,"
    "address recipient,bytes message,V3RelayExecutionEventInfo relayExecutionInfo)"
)
event_4 = (
    "RequestedV3SlowFill(address inputToken,address outputToken,uint256 inputAmount,"
    "uint256 outputAmount,uint256 indexed originChainId,uint32 indexed depositId,"
    "uint32 fillDeadline,uint32 exclusivityDeadline,address exclusiveRelayer,"
    "address depositor,address recipient,bytes message)"
)

# List of events
events_set = [event_1, event_2, event_3, event_4]

# Hypersync client configuration
hypersync_client_url = "https://base.hypersync.xyz"
contract = "0x09aea4b2242abC8bb4BB78D537A67a245A7bEC64"


async def main():
    # Create Hypersync client using the provided base URL
    client = hypersync.HypersyncClient(hypersync.ClientConfig(url=hypersync_client_url))

    # Lowercase the smart contract address
    smart_contract = contract.lower()

    # Iterate over the list of events
    for event in events_set:
        try:
            # Convert event signature to topic0 (hash of the event signature)
            topic = hypersync.signature_to_topic0(event)

            # Prepare the query to fetch logs for the event
            query = hypersync.preset_query_logs_of_event(
                smart_contract, topic, from_block=19_500_000
            )

            print("Running the query for event...")

            # Run the query and fetch the logs
            res = await client.get(query)

            # Check if logs were returned
            if len(res.data.logs) == 0:
                print("No logs found for event")
                continue

            # Process the logs (print the log count)
            print(f"Query returned {len(res.data.logs)} logs for event")

        except Exception as e:
            print(f"Error querying event, error: {e}")
            continue  # Continue to the next event


# Run the asynchronous main function
asyncio.run(main())

Something like the below. Plus the transactions and blocks decoding too.

       decoded_log={
                "inputAmount": DataType.FLOAT64,
                "outputAmount": DataType.FLOAT64,
                "quoteTimestamp": DataType.INT64,
                "fillDeadline": DataType.UINT64,
                "exclusivityDeadline": DataType.INT64,
                "destinationChainId": DataType.UINT64,
                "depositId": DataType.UINT64,
            },

This setup requires manual event signature definitions and individual data type configurations for each event, increasing development time and complexity.

Hypermanager’s Simplified Decoding Process:

Hypermanager centralizes event decoding, enabling developers to query events across multiple chains without needing to redefine them. This simplifies cross-chain queries, as the configuration only needs to be written once. The following Hypermanager example demonstrates querying multiple events with pre-loaded decoded values:

import asyncio
import os
import polars as pl
from hypermanager.events import EventConfig
from hypermanager.manager import HyperManager
from hypermanager.protocols.across import (
    client_config,
    across_config,
)

async def get_events():
    # Iterate through the different HyperSync clients and their associated SpokePool addresses
    for client, spoke_pool_address in client_config.items():
        print(f"Querying events for {client.name}...")
        print(f"SpokePool Address: {spoke_pool_address.value}")

        # **Modified Loop**: Iterate over the values of the across_config dictionary
        for base_event_config in across_config.values():
            try:
                manager = HyperManager(url=client.client)

                # Query events using the event configuration and return the result as a Polars DataFrame
                df: pl.DataFrame = await manager.execute_event_query(
                    base_event_config,
                    tx_data=True,
                    block_range=10_000,  # query the most recent 10,000 blocks from each chain
                )

                # Check if any events were found
                if df.is_empty():
                    print(
                        f"No events found for {base_event_config.name} on {client.name}, continuing..."
                    )
                    continue  # Move to the next event if none found


                print(f"Events found for {base_event_config.name} on {client.name}:")
                print(df.shape)  


            except Exception as e:
                print(f"Error querying {base_event_config.name} on {
                      client.name}: {e}")


if __name__ == "__main__":
    asyncio.run(get_events())

With Hypermanager, events, transactions, and blocks can all be queried with decoded values already pre-loaded. This eliminates the need for repetitive event definitions and custom data type decoding across applications.

One of Hypermanager’s core strengths is its centralized data decoding setup. Once configured, the event decoding information is reusable across queries Hypermanager not only reduces setup time but also makes cross-chain querying more efficient and scalable.

In summary, Hypermanager’s orchestration abilities streamline the complexity of cross-chain log queries by centralizing configurations, reducing boilerplate, and enabling developers to quickly reuse queries across applications. With Hypermanager, the barrier to efficient cross-chain analysis is lowered, making it a vital tool for anyone working with blockchain data.

Hypermanager's centralized log repository is a collaborative effort, and developers are encouraged to contribute new logs to expand its coverage and utility. By submitting pull requests with new log configurations, developers can help grow the repository, benefiting the entire community.

To demonstrate the practicality and ease of integrating Hypermanager into a real-world data pipeline, I developed a backend MVP focused on key log events from the mev-commit chain. This proof of concept showcases the seamless integration of Hypermanager, Polars, and DuckDB, highlighting the efficiency and ergonomics of building a data pipeline for querying and storing sparse events from the mev-commit chain.

The data pipeline architecture consists of the following components:

1. Hypermanager

2. Polars

3. DuckDB

By leveraging Hypermanager, the data pipeline benefits from simplified cross-chain querying and reduced boilerplate code. Hypermanager's centralized event decoding allows for seamless retrieval of log events from the mev-commit chain without the need for repetitive event definitions.

The retrieved log data is then processed using Polars, taking advantage of its high-performance DataFrame operations. Polars' integration with DuckDB enables efficient storage and querying of the processed data within the embedded OLAP database. To streamline the deployment and testing process, I dockerized the data pipeline using Docker Compose. This allows for easy setup and execution of the pipeline with a single command: docker-compose up --build db.

Previous research looked at transaction faliure rates on Solana and identified higher than average failure rates with the Pyth pull oracle contracts. Specifically the signer contract Zeta seems to be failing with many of its price updates, leading to upwards of 20 SOL a week in lost fees from submitting failed transactions.

This post will follow up, examining the on-chain sample data (~1000 blocks is the data cap) more closely with Polars DataFrame and Agglomerative Clustering to analyze log message patterns.

Data is abstracted using the Clickhouse python client from Cryptohouse. Once extracted, data is transformed via Polars. Polars allows for complex data transformations and powerful exploratory analysis by structuring data in DataFrames. It excels at handling arrays, unnesting columns, reusing transformations, and supports ad-hoc visualizations—all within a single notebook.

The notebook for all of the code can be found here.

Compared to the average 8% transaction failure rate on Solana, transactions that utilize Jito have much lower failure rates, around 0.8%. The majority of Zeta transactions do not go in Jito bundles. When using Jito, the two charts below show that the jito transactions experience an overall lower failure rate.

Although utilizing Jito transactions shows a correlative relationship for lower failure rates, it's not clear whether there is a causation with the current data. Noticeably there are still failure rate spikes in the Jito blocks which would be data point outliers that could be explored at a later time.

Agglomerative clustering is chosen for this analysis to group similar log messages together because it excels at identifying natural groupings in data without requiring a predefined number of clusters. This is particularly useful when exploring log messages, where the goal is to uncover patterns or anomalies that might not be immediately apparent from a large selection of verbose log messages.

Two clusters are generated, a failed log cluster and a successful log cluster to provide a more succinct overview of the log messages.

It's an interesting observation that the successful log cluster tends to have a lot more addresses. One hypothesis is that if the transactions are successful, more accounts end up being utilized. In contrast, failed transactions might fail earlier in the process, not utilizing the maximum number of accounts.

The raw data from these word clouds can be found here:

log messages in failed transactions

log messages in successful transactions

This is the start of a series of posts that examines how to analyze Solana data in SQL using different platforms. We start off the series with a couple of key queries that yield insights to both the data platforms and the data themselves.

The best free SQL based tools are Cryptohouse (powered by Goldksy), Dune and Flipside for accessing Solana data. This post evaluates the tradeoffs between using each of these platforms by using a test of useful queries. These queries look at the most fundamental table for Solana data, the transactions table, which forms the basis of most fundamental research on Solana.

We'll examine three queries:

1. Transaction Sample: A basic query to quickly inspect the dataset.

2. Filter Array for Accounts: A more complex query that filters transactions based on a subset of accounts, an essential task given that many Solana transactions have columns stored as arrays.

3. Filter + GroupBy Aggregation: A sophisticated query that filters account arrays and performs a groupby aggregation, a common task in data analysis.

The transactions table is a fundamental table for Solana blockchain data and understanding how to effectively navigate through the table is key to unlocking more on-chain insights. Every Solana transaction touches a set of accounts, which are stored in an array. There is also a pre-balance and post-balance column arrays which store the SOL amount before and after the transaction, which are important to derive SOL related volume for fees, compute costs, and validator revenue.

This table also has a status/success column that determines whether the transaction was successful or not. Continuously higher than normal transaction failures can indicate an interesting research direction.

Finally the transactions table contains all of the instructions required to execute the transaction in a set of logical steps. For those familiar with EVM data, Solana instructions are similar to EVM log event data.

There are three primary criteria for evaluating these tools: performance, features, and data availability. The importance of each criterion depends on the specific research outcomes desired.

For instance, Cryptohouse tends to have faster query results, but it currently lacks features like dashboards and full data availability compared to Flipside and Dune. Dune has custom Trino-based DuneSQL, while Flipside uses a more standardized SQL dialect, making it easier to use with AI models like ChatGPT. Dune also offers intelligent caching, speeding up queries that are run multiple times.

This is a basic query that is commonly used to take a quick look at the dataset that you are working with. It is important to see that the dataset you choose has both the columns and accurate (e.g. non null) data in those columns. Although blockchain data is standardized at the node level, each data indexer chooses the most efficient way to store and format their data to serve to users.

Some notable comments about the transactions dataset differences is namely what is available and what format it shows up in. If the columns you want do not exist in the dataset, then that automatically restricts what data platform suits your needs.

For example Cryptohouse does not have an instructions column. While dune has instructions, the output is not friendly for longer formats so it's not possible to inspect the output of larger sized columns. While Flipside has instructions as well, the layout is the opposite of dune where each column is maximally sized. This is not the most ergonomical because the width of the data returned is quite large to scroll through. Another example is that both Cryptohouse and Dune have a tx_index column whereas flipside doesn't.

SELECT
  *
FROM
  solana.transactions
WHERE block_timestamp >= now() - INTERVAL 12 HOUR
ORDER BY
  block_slot,
  index
LIMIT 1000

SELECT
  *
FROM solana.transactions
WHERE block_time >= CURRENT_TIMESTAMP - INTERVAL '12' HOUR
ORDER BY
  block_slot,
  index
LIMIT 1000

SELECT 
  *
FROM 
  solana.core.fact_transactions
WHERE 
  block_timestamp >= DATEADD(HOUR, -12, CURRENT_TIMESTAMP)
ORDER BY 
  block_id
LIMIT 1000;

This is a more complex query that filters transactions based on a subset of accounts. This is a very useful query because a lot of Solana transactions columns are stored as arrays so it's important to know how to filter through arrays. The second is that most analysis will be geared towards specific accounts (protocol accounts, mev accounts, trading accounts, etc).

Notably Crytpohouse starts to shine with these kinds of queries and offers superior performance compared to Dune and Flipside. Cryptohouse also scanned nearly 4x as much data as the Dune. Flipside also performed fairly well with the filter task as well, but not as much data is available on the query statistics.

SELECT
  *
FROM
  solana.transactions
WHERE
  arrayExists(
    x -> x.1 IN (
      'Fc8bpeCMifWYv97pQ3k5xDvd98nuVg6yAaZrwmy4RRp6',
      -- zeta contract
      'rec5EKMGg6MxZYaMdyBfgwp4d5rB9T1VQH5pJv5LtFJ' -- pyth contract
    ),
    accounts
  )
  AND block_timestamp >= now() - INTERVAL 12 HOUR
ORDER BY
  block_slot,
  index
LIMIT 10000

SELECT
  *
FROM
  solana.transactions
WHERE
  arrayExists(
    x -> x.1 IN (
      'Fc8bpeCMifWYv97pQ3k5xDvd98nuVg6yAaZrwmy4RRp6',
      -- zeta contract
      'rec5EKMGg6MxZYaMdyBfgwp4d5rB9T1VQH5pJv5LtFJ' -- pyth contract
    ),
    accounts
  )
  AND block_timestamp >= now() - INTERVAL 12 HOUR
ORDER BY
  block_slot,
  index
LIMIT 10000

WITH filtered_accounts AS (
  SELECT 
    *
  FROM 
    solana.core.fact_transactions
  WHERE 
    block_timestamp >= DATEADD(HOUR, -12, CURRENT_TIMESTAMP)
)
SELECT 
  *
FROM 
  filtered_accounts f,
  LATERAL FLATTEN(input => f.account_keys) AS a
WHERE 
  a.value:pubkey IN (
    'Fc8bpeCMifWYv97pQ3k5xDvd98nuVg6yAaZrwmy4RRp6', 
    'rec5EKMGg6MxZYaMdyBfgwp4d5rB9T1VQH5pJv5LtFJ'
  )
ORDER BY 
  block_id
LIMIT 10000;

This is a more complex query that filters the account arrays and then performs a groupby aggregation. Groupby aggregations are the bread and butter for most analytics questions and provides strong intuition on the overall shape of the data. In this particular query, we filter accounts for the pyth contract and groupby whether the transaction was a failure or success to analyze the failure rates specific to that contract. It turned out that the Pyth contract is experiencing 3x higher failure rates than the average Solana transaction (more on this soon).

The Pyth example is a query example that does not rely on a dashboard to gain insights into the data. These types of queries would be best suited for the fastest performing tool, which happens to be Cryptohouse. Here Cryptohouse really shines, reading more rows and processing more bytes than Dune while being multiples faster than both Dune and Flipside.

SELECT
    status,
    COUNT(*) AS status_count
FROM
    solana.transactions
WHERE
    arrayExists(
        x -> x.1 IN [
            'rec5EKMGg6MxZYaMdyBfgwp4d5rB9T1VQH5pJv5LtFJ'
        ],
        accounts
    )
    AND block_slot > 282400000
    AND block_slot < 282900000
GROUP BY
    status

SELECT
  success,
  COUNT(*) AS transaction_count
FROM solana.transactions
WHERE
  block_slot > 282400000
  AND block_slot < 282900000
  AND ANY_MATCH(account_keys, x -> x = 'rec5EKMGg6MxZYaMdyBfgwp4d5rB9T1VQH5pJv5LtFJ')
GROUP BY
  success

WITH filtered_transactions AS (
  SELECT 
    *
  FROM 
    solana.core.fact_transactions
  WHERE 
    block_id > 282400000
    AND block_id < 282900000
)
SELECT 
  SUCCEEDED,
  COUNT(*) AS status_count
FROM 
  filtered_transactions f,
  LATERAL FLATTEN(input => f.account_keys) AS a
WHERE 
  a.value:pubkey IN (
    'rec5EKMGg6MxZYaMdyBfgwp4d5rB9T1VQH5pJv5LtFJ'
  )
GROUP BY 
  SUCCEEDED;

In conclusion, while all three platforms—Cryptohouse, Dune, and Flipside—offer valuable tools for analyzing Solana data, each has its strengths and weaknesses. Cryptohouse excels in performance, particularly with more complex queries, but lacks some features and full data availability. Dune offers flexibility with its custom SQL dialect and intelligent caching, while Flipside provides a more standardized experience with solid performance. The best choice of platform will depend on your specific needs, whether it be speed, ease of use, or the availability of specific features.

TLDR:

• Pyth contract has a 26.5% tx failure rate (292,165 txs) across 1.1m transactions, resulting in 1.45 SOL in lost fees. The failure rate is cross referenced with Dune and solscan.

• Total tx failure rate during this period is 8% (69,356,928 txs failed) across ~781m transactions, resulting in 346 SOL in lost fees. The failure rate cross referenced with Dune.

• Pyth receiver contract has a 3.3x higher tx failure rate than the general tx failure rate.

• Tx failures are an additional revenue source for Solana validators and there is no economic incentive to lower the tx failure rates.

This research looks at some behavior related to failed Solana transactions. Specifically we compare the general tx failure rate to the Pyth receiver contract. This contract has critical functionality to the Pyth oracle model and receives price feed updates from publishers. We see that the failure rate and see that the Pyth contract generates nearly 3x more failed transactions on average, costing about ~10 SOL monthly in lost fees to the publishers.

The results are validated by replicating the queries between Dune and Cryptohouse, two strong data sources for free Solana data. A 600,000 slot range [282300000, 282900000], about 3 days, is chosen to keep resource usage in check. For the duplicate queries between Cryptohouse and Dune, Dune was generally faster (~5 seconds) vs (~15 seconds).

• Cryptohouse is a new public goods data source for Solana data powered by Clickhouse and Goldsky. Current functionality is limited - can't save and share queries through a url, a non-trivial amount of data columns are missing, can't perform joins or multi-statements. Please refer to the Appendix section for the Cryptohouse queries.

• Dune needs no introduction. Currently the crypto data standard for public goods dashboards. Query here for pyth contract tx failure rates. Query here for general tx failure rates.

The analysis of transaction failures on the Solana network reveals significant insights, particularly regarding the performance of the Pyth contract. Over a three-day period, the Pyth contract experienced a high transaction failure rate of 26.5%, resulting in 1.45 SOL in lost fees.

This failure rate is 3.3 times higher than the network-wide average of 8%, which led to 346 SOL in lost fees over the same period. Extrapolating these figures to a monthly timeframe, the Pyth contract could lose 10.45 SOL, while network-wide losses could reach 3,460 SOL. The high failure rate of the Pyth contract suggests potential inefficiencies or persistent issues within its logic.

Moreover, the economic implications of transaction failures are significant, as they contribute to validator revenue, presenting little incentive for reducing these rates, despite the potential for wasted resources. This complex interplay between transaction failure rates, validator incentives, and network performance highlights the challenges and opportunities within the Solana ecosystem.

If we extrapolate these 3 day values to monthly values, then the amount of lost fees for the Pyth contract becomes 10.45 SOL and 3460 SOL for all transactions. Manually checking the solscan contract transactions page, the main tx failure reasons are:

• Program Error: "Instruction #1 Failed - The posted VAA account has the wrong owner"

• "Instruction #1 Failed - Failed to deserialize the account"

• Program Error: "Instruction #1 Failed - UnverifiedVaa"

A hypothesis would be that there is some logic that keeps producing these errors. A deeper dive into solana instructions data would be required to prove the hypothesis though. It would be very important to know whether these failure rates are delaying Pyth prices to be published on-chain by multiple slots as this would present an additional hidden latency for using Pyth oracles.

Although the 8% tx failure rate is an extra tax on the users who submit failed transactions, conversely it represents an alternative form of Validator revenue because they are the ones who ultimately keep the fees from failed transactions.

Although failed transaction rates end up wasting blockspace and computation power, the corollary is that validators do not have an economic disincentive to lower the transaction failure rates because they are still being economically rewarded. A major reason for tx failure rates is due to mev-searcher competition on Solana. Searchers will use spam strategies to land profitable mev opportunities which end up making spamming strategies economically viable. Instead of being seen as a "waste of blockspace", this could also be interpreted as an "abundance of blockspace".

In conclusion, the analysis of transaction failures on the Solana network reveals a significant discrepancy between the Pyth contract's failure rate and the network-wide average. The Pyth contract's 26.5% failure rate indicates possible inefficiencies or recurring issues within its logic, resulting in considerable lost fees.

These transaction failures also play a crucial economic role, generating revenue for validators and offering little incentive to reduce failure rates. This interplay between high failure rates, validator incentives, and overall network performance highlights both the challenges and opportunities present in the Solana ecosystem.

Further investigation into the underlying causes of the Pyth contract's high failure rate, as well as its potential impact on price feed latency, is essential to fully address these concerns.

SELECT
    status,
    COUNT(*) AS status_count
FROM
    solana.transactions
WHERE
    arrayExists(
        x -> x.1 IN [
            'rec5EKMGg6MxZYaMdyBfgwp4d5rB9T1VQH5pJv5LtFJ'
        ],
        accounts
    )
    AND block_slot > 282300000
    AND block_slot < 282900000
GROUP BY
    status

SELECT
    status,
    COUNT(*) AS status_count
FROM
    solana.transactions
WHERE block_slot > 282300000
    AND block_slot < 282900000
GROUP BY
    status

A note on lamports:

• The average lamports per slot for the Pyth contract is 5.7m lamports (0.00575 SOL).

• The average lamports per slot is 4.6b lamports (4.6 SOL).

• I am interpreting lamports as the total "cost" for compute units used per transaction. The google bigquery definition, where cryptohouse data is sourced from, defines lamport column as The account's balance, in Lamports which doesn't seem as useful though.

SELECT 
    AVG(block_slot_avg) AS average_lamports_per_block_slot
FROM (
    SELECT 
        block_slot,
        AVG(lamports) AS block_slot_avg
    FROM 
        solana.accounts
    WHERE 
        owner = 'rec5EKMGg6MxZYaMdyBfgwp4d5rB9T1VQH5pJv5LtFJ' -- pyth receiver contract
    GROUP BY 
        block_slot
) AS subquery;

SELECT 
    AVG(block_slot_avg) AS average_lamports_per_block_slot
FROM (
    SELECT 
        block_slot,
        AVG(lamports) AS block_slot_avg
    FROM 
        solana.accounts
    GROUP BY 
        block_slot
) AS subquery;

In this article, we'll explore how to build a Solana data pipeline in Python to analyze Jito fees. We'll use Solana-py to examine block and transaction-level data for a single slot using a free Solana RPC. A full notebook example with extensive comments is available to accompany this post. I highly encourage you to read through the notebook and experiment with the code.

The Solana data ecosystem is still in its early stages, offering limited tools and resources for data analysis. Analyzing Solana data presents unique challenges due to the lack of transaction standardization and the high resource costs required to sift through data generated by Solana's 1000+ TPS speed.

Before diving into the technical details, it's crucial to understand the importance of planning your ETL (Extract, Transform, Load) pipeline. A well-thought-out pipeline not only simplifies the data extraction process but also makes the subsequent analysis more straightforward and efficient. Careful planning helps to identify the necessary data, the transformations required, and how the data will be loaded into the analysis environment. This preparation can save significant time and effort, especially when dealing with large datasets like those on Solana.

• Solana data is stored in various sections of the Solana block object. For example, fees, compute units, and account balances are found in transactions.meta, while account keys are stored in transaction.message. The block number and timestamp are located in blockHeight and blockTime, respectively. Instead of storing each part of the block object separately and then combining them, we extract and combine all this data into a single dataframe, creating a unified dataset.

• One challenge in extracting Solana data is dealing with multiple signatures, locked account states, and balance changes within each transaction. To analyze Jito fees, we need to isolate the set of Jito tip accounts within the transactions. We applied a transformation at the data extraction level to process each account key and pre/post balance as a separate row, simplifying the complexity. The tradeoff is an increase in the row count for each block by approximately five times. This technique is effective for analyzing a single block with a few thousand rows but may be less feasible for analyzing 10 million blocks with billions of rows.

The end results show some transaction-level insights for a single block (277533216), which had 2,588 transactions. Unlike Ethereum, where the top of the block is a contentious area, the same does not apply to the Solana slot. The chart below illustrates that some of the highest fees are paid in the 500-1000 transaction index range.

The next chart demonstrates that fees paid do not correlate with higher computational costs. In fact, the majority of higher fees are clustered in transactions with lower compute units. This indicates that the most highly contested states take very little of the overall compute cost in Solana blocks. The majority of transactions take less than 200,000 compute costs.

The jito tips for this block are distributed throughout the block. Looking at the next chart, it looks like there is a small cluster of jito tips towards the end of the block (highest transaction indexes), but all of the high outlier jito tips are dispersed throughout the block.

Finally we also compare the jito tips being for transactions with varying compute costs. This chart shows that there is a skew towards higher jito fees for less compute units with a large cluster of fees paid for less than 25,000 compute cost. It is also observed that Jito fees are also widely distributed within the the block, but it is not clear given this sample data why that is.

Initial findings reveal interesting transaction-level insights for a single block. For instance, higher transaction fees in Solana are not necessarily correlated with higher computational costs, and Jito fees are distributed throughout the block with a notable cluster towards the end. It remains to be seen whether these transaction-level insights hold for analysis over larger blocks, but the probability that this pseudo-random block selected is an outlier block is low.

These insights highlight an ETL workflow to further understand the dynamics of Solana transactions, fees paid (including Jito), and compute costs. With the current data landscape, it is no small feat to analyze even a single week's worth of blocks (216000*7 = ~1.5m blocks). With an average blocksize of 1000 transactions, this equates to 1.5b transactions.

Latest

• On-chain Atomic Gaussian Math (December 2025)

• Building an Agentic Native MCP for Data Science (October 2025)

Highlights

• Sui NFT Analytics (February 2025)

• Economics of Sui Shared Liquidity Pool Objects (December 2024)

• Data Observability with Sentio Pipelines (December 2024)

• Tackling Blob Fee Slippage with Preconfirmations (September 2024)

• Slot Inclusion Rates and Blob Market Combinatorics (June 2024)

• Analyzing Blob Inclusion Rates and Market Strategies (April 2024)

• Trading in the Dark: Measuring Reordering Slippage in MEV (January 2024)

• Feedlot: trading with safe, low latency price oracles (April 2023)

• Using Topological Data Analysis to Identify Distinct MEV Behavior (November 2022)

Data

• Economic Implications of a Competitive Blob Market (November 2024)

• Clustering Analysis on Zeta <> Pyth Transaction Logs (August 2024)

• Conquering Solana Data Analytics Part 1 - Tooling (August 2024)

• Economic Costs of Failed Transactions on Solana (August 2024)

• Slot Inclusion Rates and Blob Market Combinatorics (June 2024)

• Streaming Embedded OLAP Data Pipeline with LanceDB + DuckDB in Python (May 2024)

• Fetching Uniswap Historical Swap Data With Python and Subgrounds (November 2023)

• Free Historical Blockchain Extraction with Cryo + Merkle Reth Nodes (November 2023)

• Streamline Subgraph Pipeline Creation With DataStreams (January 2023)

MEV

• Tackling Blob Fee Slippage with Preconfirmations (September 2024)

• Calculate Jito Tips in Solana (July 2024)

• Analyzing Blob Inclusion Rates and Market Strategies (April 2024)

• Censorship, Latency, and Preconfirmations in the Blob Market (February 2024)

• Trading in the Dark: Measuring Reordering Slippage in MEV (January 2024)

• Cross-Chain NFT Marketplace MEV Strategy with Artemis: A Technical Commentary (May 2023)

• Using Topological Data Analysis to Identify Distinct MEV Behavior (blog, November 2022)

• Using Topological Data Analysis to Identify Distinct MEV Behavior (paper, November 2022)

• MEV Arbitrage on Olympus (October 2022)

DeFi

• Feedlot: trading with safe, low latency price oracles (April 2023)

• Applying Balancer’s Managed Pool Controllers to Manage Liquidity in Bonding Curves (December 2022)

• Pari-Mutuel Market Design (August 2022)

• The AMM Design Trilemma (August 2022)

• RMM-01 Concentrated Liquidity Analysis (July 2022)

• Replicating Market Makers and Super Hedging Portfolios (May 2022)

• Liquidity Management and Supply Issuance Optimization Model (April 2022)

• Quantitative Modeling on Rebase Tokens (Ohm) (January 2022)

• Crypto Speculation Index (CSI) (January 2022)

• Why Decentralized Algorithmic Stablecoins Win the Stablecoin Wars (UST, BEAN) (December 2021)

• Applications of Graph Theory on the Structure of a DAO (November 2021)

Math

• Quadratic Reciprocity of Lucas Polynomials (December 2021)

• Groebner Basis and Automated Geometric Theorem Solver (Mathematica, April 2021)

• Measure Theory - Lebesgue Integration (May 2020)

• Abstract Algebra - Groups, Kernels, Normal Subgroups (November 2020)

• Perceptron Neural Networks (May 2020)

Intro

This post goes over a simple solution to create an embedded streaming OLAP data pipeline that leverages historical blockchain data from Hypersync and mutable columnar storage format Lance. Using the new python package lancedb-tables, it is easy to create LanceDB tables, update without complex file management systems, and stream real time data. 

Python has been experiencing massive leaps and bounds in open source data standards via the Apache Arrow standard. Apache Arrow plays a significant role in standardizing the Python data ecosystem by providing a unified, language-agnostic columnar memory format for data. This standardization is crucial for several reasons:

• System Interoperability: Arrow's language-agnostic format enhances data exchange across diverse systems and languages, minimizing serialization costs between different data processing tools. For example, Arrow enables seamless transition between dataframe and SQL apis.

• Community and Ecosystem Growth: Wide adoption across languages fosters a robust developer community, driving continuous enhancements in Arrow and its libraries.

• Future-Proofing Data Engineering: Arrow standardizes data formats and processing methods, supporting large-scale analytics and future data engineering innovations in Python.

Apache Arrow makes it possible to have seamless integration between LanceDB, DuckDB, and Polars.

• Hypersync - Query historical blockchain data

• LanceDB - write data to a LanceDB table

• DuckDB - OLAP Queries on the LanceDB table

Envio is a real-time indexing solution specifically designed for EVM-compatible blockchains. It provides developers with Hypersync, a more efficient way to sync and aggregate blockchain data compared to JSON-RPC, offering up to 20-100x faster syncing on historical data as a result.

The following example utilizes the lancedb-tables package to sync a sample of 50,000 blocks from Hypersync to a local LanceDB table. Example code and instructions can be found in this repository. The Hypersync client is used to query blocks and transactions from blocks 19,825,000 to 19,850,000. Then running the back fill table example will then back fill the LanceDB table from blocks 19,800,000 to 19,825,000.

Polars is used as an analytical preprocessing API of choice to perform a bit of data cleaning before writing data into the LanceDB table.

LanceDB contains a powerful writer that makes it very simple to read and write new Lance datasets. The key reason is that LanceDB decouples read and write operations. This is by design as all LanceDB files are read-only. New data writing operations create new fragments without affecting existing data, allowing for independent operation of read and write nodes in a distributed environment. 

This design ensures that read operations on current data remain unaffected by concurrent writes. This makes the Lance format behave as a mutable columnar storage format. In contrast, parquet is a immutable columnar storage format and requires additional overhead and code complexity to manage streaming data.

The example notebook analytics.ipynb is an easy example that shows how easy it is to add OLAP functionality by using DuckDB on top of a LanceDB table. Notably using DuckDB to count the number of unique blocks is much faster than Polars, on a magnitude of about 10x.

This was a short example of how to leverage a modern data engineering stack to build a local embedded OLAP streaming data pipeline with ease in a local environment. Follow up writings will show how to save this pipeline in a cloud environment and add more data sources.