Case Study 34.2: Dune Analytics — How Open-Source SQL Dashboards Democratized Blockchain Data

The Problem: Blockchain Data Was Accessible but Not Usable

Public blockchains are, by definition, transparent. Every transaction on Bitcoin, Ethereum, and hundreds of other networks is permanently recorded in a data structure that anyone can read. But "can read" and "can analyze" are very different things.

Before platforms like Dune Analytics existed, on-chain analysis required significant technical infrastructure. To answer a question as simple as "What was the total trading volume on Uniswap last week?" an analyst had three options:

Option 1: Run a full node. Sync an Ethereum archive node (12+ TB of storage, weeks to sync), write custom scripts to parse transaction data, decode smart contract events using the contract ABI, and aggregate the results. Total setup cost: $2,000+ in hardware, weeks of development time, ongoing maintenance.

Option 2: Use the Etherscan API. Write Python scripts to query Etherscan's REST API, paginate through millions of results (10,000 per request, with rate limiting), parse the JSON responses, and compute the aggregates locally. Total time: hours to days for a single complex query.

Option 3: Know someone. Ask a friend at a crypto fund or analytics firm who already had the infrastructure set up. This was, depressingly often, the actual workflow for journalists, researchers, and small project teams.

None of these options were accessible to the average crypto-curious data analyst, let alone a journalist writing about DeFi for a mainstream publication. The blockchain's data was theoretically public, but practically gated behind technical barriers.

The Dune Solution

Dune Analytics, founded in 2018 by Fredrik Haga and Mats Olsen in Oslo, Norway, solved this problem with a deceptively simple idea: decode all of Ethereum's smart contract events, store them in a PostgreSQL database (later migrated to a custom engine called DuneSQL), and let anyone write SQL queries against the data.

The insight was not about blockchain technology or data engineering. It was about interface design. By exposing blockchain data through SQL --- the lingua franca of data analysis, known by millions of analysts, data scientists, and even business school graduates --- Dune transformed blockchain data from a specialist resource into a public utility.

How Dune Works

Dune's architecture has four key components:

1. Raw Data Ingestion. Dune runs its own Ethereum (and later Polygon, Optimism, Arbitrum, Solana, and other chain) nodes. It ingests every block and stores the raw data: transactions, traces, logs, and state changes.

2. ABI Decoding. This is Dune's critical value-add. When a smart contract emits an event like Swap(address indexed sender, uint256 amount0In, uint256 amount1In, uint256 amount0Out, uint256 amount1Out, address indexed to), the raw blockchain data contains only the event's encoded bytes. Dune decodes these using the contract's ABI (Application Binary Interface), producing human-readable tables.

For Uniswap V2, this means the decoded table uniswap_v2_ethereum.Swap has columns like: - sender (address) - amount0In (uint256, decoded from bytes) - amount1In (uint256, decoded from bytes) - amount0Out (uint256) - amount1Out (uint256) - to (address) - evt_tx_hash (transaction hash) - evt_block_time (timestamp) - contract_address (the specific pair contract)

3. Community-Driven Decoding. Dune maintains decoded tables for thousands of popular contracts. When a new protocol launches, community members can submit the contract address and ABI for decoding. This crowdsourced approach means that coverage of new protocols is remarkably fast --- often within days of a major protocol launch.

4. The Query Editor and Dashboard Builder. Dune provides a web-based SQL editor where anyone can write queries, visualize results, and assemble dashboards. Crucially, all queries and dashboards are public by default. This was a deliberate design choice that created a powerful network effect.

The Open-Source Knowledge Loop

Dune's public-by-default policy created a virtuous cycle:

  1. Analyst A writes a query to calculate Uniswap V3 daily volume and publishes a dashboard
  2. Analyst B sees the query, forks it, and adapts it for SushiSwap
  3. Journalist C embeds both dashboards in an article comparing DEX volumes
  4. Analyst D, inspired by the article, writes a more sophisticated query that tracks volume across all DEXes
  5. Protocol team E uses the dashboard to track their own metrics and shares it with investors

Each step builds on the previous one. The result is a shared knowledge base of thousands of blockchain analytics queries, openly available to anyone. As of 2025, Dune hosts over 750,000 public queries and 200,000 dashboards covering virtually every major protocol on every major blockchain.

This is qualitatively different from traditional financial analysis, where proprietary models and data are closely guarded competitive advantages. In the Dune ecosystem, analytical work is open by default, and reputation comes from producing the best public analyses --- a model closer to academic research than to hedge fund research.

Key Dashboards That Shaped the Industry

Several Dune dashboards have become standard references, cited by media, researchers, and even protocol governance proposals:

The DeFi TVL Dashboard

Before DeFiLlama (which also relies heavily on Dune data), Dune community dashboards were the primary source for Total Value Locked metrics. Analysts wrote SQL queries that summed token balances across protocol contracts, converted to USD using price feeds, and displayed the results as time-series charts.

Example query structure:

-- Simplified Aave V2 TVL calculation
SELECT
    DATE_TRUNC('day', evt_block_time) AS day,
    SUM(
        CAST(amount AS DOUBLE) / POWER(10, decimals)
        * price_usd
    ) AS tvl_usd
FROM aave_v2_ethereum.LendingPool_evt_Deposit AS deposits
LEFT JOIN prices.usd AS p
    ON deposits.reserve = p.contract_address
    AND DATE_TRUNC('day', deposits.evt_block_time) = p.day
GROUP BY 1
ORDER BY 1;

These dashboards became the empirical ground truth for DeFi market discussions. When a protocol claimed "$1 billion TVL," journalists could verify the claim by checking the Dune dashboard rather than trusting the protocol's self-reported numbers.

The Ethereum Gas Tracker

Community-built gas analysis dashboards tracked gas prices, gas usage by contract (revealing which protocols consumed the most block space), and gas cost trends over time. These dashboards informed the debate around Ethereum's scalability and provided data for EIP-1559 (the gas price reform) discussions.

The NFT Market Dashboards

During the NFT boom of 2021-2022, Dune dashboards tracking NFT trading volume, unique buyers and sellers, and collection-level metrics became essential resources. They also revealed the scale of wash trading --- community analysts published queries that identified circular fund flows and estimated that 40-80% of NFT trading volume on some platforms was wash trading. This analysis would have been nearly impossible without Dune's decoded event data and public query infrastructure.

Protocol-Specific Dashboards

Many DeFi protocols now maintain official Dune dashboards that serve as their public metrics pages. Lido (liquid staking), MakerDAO (stablecoin), and Aave (lending) all link to Dune dashboards from their official documentation. This represents a remarkable shift: a protocol's key performance metrics are not just transparent but independently verifiable by anyone who can read SQL.

The Business Model and Its Tensions

Dune's public-by-default model created immense community value, but it also created business model challenges. If all queries and data are free, how does Dune make money?

Dune has evolved through several monetization strategies:

Free tier (original model): All queries and dashboards are free, with limited execution capacity (queries might queue during peak times). This attracted the community and created the network effect.

Dune Pro (introduced 2022): A paid tier ($349/month) offering faster query execution, private queries (breaking the open-by-default model), CSV exports, and higher API access limits. The private query feature was controversial --- some community members felt it undermined Dune's open ethos.

Dune API (introduced 2023): Programmatic access to query results, enabling developers to build applications powered by Dune queries. Priced per query execution.

Enterprise tier: Custom solutions for institutional clients, including dedicated infrastructure, custom data pipelines, and support.

The tension between open community value and commercial viability is a recurring theme in blockchain infrastructure. Dune's journey mirrors the challenges faced by open-source software companies: the community creates the value (the queries and dashboards), but the company needs revenue to maintain the infrastructure (ingesting and decoding petabytes of blockchain data is expensive).

Competitive Landscape

Dune's success spawned competitors and alternatives:

  • Flipside Crypto: Similar SQL-based analytics, with a focus on gamified community engagement (analysts earn tokens for creating popular dashboards)
  • Google BigQuery: Google hosts raw Ethereum data as a public dataset, queryable with standard BigQuery SQL. Lacks Dune's ABI decoding and community features.
  • Footprint Analytics: No-code analytics platform targeting non-technical users
  • Nansen: Enterprise-focused, with proprietary address labels (complementary to rather than competitive with Dune)
  • Arkham Intelligence: Combines entity labeling with a community intelligence exchange

Each platform represents a different trade-off between accessibility, depth, cost, and community.

Technical Deep Dive: Anatomy of a Dune Query

To understand why Dune is powerful, let us walk through a real analytical question and the SQL query that answers it.

Question: "What is the daily revenue of Uniswap V3 on Ethereum, and how does it compare to Uniswap V2?"

On Uniswap V2, every swap incurs a 0.3% fee. On Uniswap V3, fees vary by pool (0.01%, 0.05%, 0.3%, or 1%). The fees are denominated in the swapped token, not in USD. To calculate revenue in USD, we need to:

  1. Query all swap events on both Uniswap V2 and V3
  2. Calculate the swap volume in token terms
  3. Apply the fee rate
  4. Convert to USD using historical price data
-- Uniswap V3 daily revenue
WITH v3_swaps AS (
    SELECT
        DATE_TRUNC('day', evt_block_time) AS day,
        s.contract_address AS pool,
        ABS(CAST(amount0 AS DOUBLE)) / POWER(10, t0.decimals) AS amount0_human,
        ABS(CAST(amount1 AS DOUBLE)) / POWER(10, t1.decimals) AS amount1_human,
        CASE
            WHEN fee = 100 THEN 0.0001
            WHEN fee = 500 THEN 0.0005
            WHEN fee = 3000 THEN 0.003
            WHEN fee = 10000 THEN 0.01
        END AS fee_rate
    FROM uniswap_v3_ethereum.Pair_evt_Swap AS s
    JOIN uniswap_v3_ethereum.Factory_evt_PoolCreated AS p
        ON s.contract_address = p.pool
    LEFT JOIN tokens.erc20 AS t0
        ON p.token0 = t0.contract_address AND t0.blockchain = 'ethereum'
    LEFT JOIN tokens.erc20 AS t1
        ON p.token1 = t1.contract_address AND t1.blockchain = 'ethereum'
    WHERE evt_block_time > NOW() - INTERVAL '90' DAY
),
v3_revenue AS (
    SELECT
        day,
        SUM(amount0_human * fee_rate * COALESCE(p0.price, 0)
          + amount1_human * fee_rate * COALESCE(p1.price, 0)) AS revenue_usd
    FROM v3_swaps
    LEFT JOIN prices.usd AS p0
        ON p0.day = v3_swaps.day
        -- Additional price join conditions
    LEFT JOIN prices.usd AS p1
        ON p1.day = v3_swaps.day
    GROUP BY 1
)
SELECT day, revenue_usd
FROM v3_revenue
ORDER BY day;

This query --- which would take weeks to compute from raw blockchain data using a full node --- executes in seconds on Dune because the data is pre-decoded and indexed. The ability to write such queries without any blockchain-specific infrastructure is Dune's fundamental contribution.

Impact on the Blockchain Ecosystem

Dune's democratization of blockchain data has had several concrete effects:

1. Accountability Through Transparency

When a DeFi protocol claims "10,000 daily active users," anyone can write a Dune query to verify or refute the claim. This has created a culture of data-driven accountability in the crypto space. Protocols that make unverifiable claims face community members who will immediately check the on-chain data.

2. Research Quality Improvement

Academic researchers studying DeFi, token economics, and blockchain adoption now have access to real-time, granular data that was previously available only to industry insiders. Papers published since Dune's emergence show measurably more rigorous empirical work than earlier blockchain research.

3. Governance Informed by Data

DAO governance proposals increasingly cite Dune dashboards as evidence. A proposal to change Uniswap's fee structure might include a Dune dashboard showing current fee revenue, volume distribution by fee tier, and comparison with competitor protocols. This data-informed governance is a direct consequence of making blockchain data accessible to non-engineers.

4. Journalism Accountability

Crypto journalists now routinely cite Dune queries as sources. During the FTX collapse, Dune dashboards tracking FTT token flows and exchange reserves were cited by Bloomberg, CoinDesk, and The Block. The ability for readers to independently verify journalistic claims by checking the underlying Dune query represents a new standard of transparency in financial journalism.

Lessons for Data Scientists

1. The Interface Determines the User Base

The underlying data was always public. What changed was the interface --- from JSON-RPC to SQL. This transformation expanded the potential user base from "people who can run Ethereum nodes" to "anyone who knows SQL," which is orders of magnitude larger. In data science, the most impactful work is often not about discovering new data, but about making existing data accessible through better interfaces.

2. Open Defaults Create Network Effects

Dune's decision to make queries public by default was counterintuitive from a business perspective (why give away the work?), but it created a massive network effect. Each public query makes the platform more valuable for every other user. This is the same dynamic that drives open-source software, Wikipedia, and academic preprint servers.

3. Domain-Specific Tooling Beats General-Purpose Tools

Google BigQuery has raw Ethereum data, vastly more compute power, and a much larger engineering team. But Dune dominates blockchain analytics because it provides domain-specific value: ABI decoding, blockchain-specific table schemas, and a community of blockchain-focused analysts. General-purpose tools rarely win against domain-specific tools in specialized fields.

4. Composability Enables Complexity

Just as DeFi protocols compose on-chain, Dune queries compose analytically. A dashboard can reference other dashboards. A query can build on community-maintained tables. This composability means that individual analysts can produce work of much greater complexity than they could alone, because they are building on the community's cumulative analytical infrastructure.

Discussion Questions

  1. Dune's public-by-default model means that a novel analytical insight (e.g., a new way to detect wash trading) is immediately available to everyone, including the wash traders. Does openness help or hurt the analytical arms race against manipulation?

  2. Dune's enterprise clients pay for private queries --- the ability to analyze data without revealing their questions. Does this create a two-tier system where wealthy institutions have informational advantages despite the "democratized" label?

  3. Compare Dune's community model to Stack Overflow for programming questions. What are the similarities and differences in how community contributions create value?

  4. Dune recently migrated from PostgreSQL to a custom query engine (DuneSQL). What risks does a proprietary query engine create for user lock-in? How does this relate to the broader tension between open data and proprietary infrastructure?

  5. If traditional financial markets adopted similar transparency (every stock trade, every bank transfer publicly visible and queryable), what would change? What would remain the same? Is there an argument for bringing "blockchain-level transparency" to traditional finance?

Hands-On Activity

Visit dune.com and create a free account. Complete the following:

  1. Search for "Uniswap V3 volume" and find a community dashboard tracking daily swap volume. Fork the dashboard and modify one query to filter for a specific token pair (e.g., WETH/USDC).

  2. Write a simple query that counts the number of Transfer events for any ERC-20 token in the last 24 hours:

SELECT
    COUNT(*) AS transfer_count,
    COUNT(DISTINCT "from") AS unique_senders,
    COUNT(DISTINCT "to") AS unique_receivers
FROM erc20_ethereum.evt_Transfer
WHERE evt_block_time > NOW() - INTERVAL '24' HOUR
AND contract_address = 0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48  -- USDC
;

  1. Create a visualization (bar chart or line chart) from your query results and save it to a new dashboard.

  2. Find a dashboard for a protocol discussed in this textbook (Aave, Compound, MakerDAO, or Uniswap). Identify three metrics that appear on the dashboard and explain what each one measures and why it matters for evaluating the protocol's health.

Document your queries and observations. This hands-on experience with Dune will deepen your understanding of the SQL-based analytics layer discussed in Section 34.2.