Dune Launches Comprehensive SQL Style Guide to Streamline Web‑3 Analytics
June 2024 – Dune, the leading platform for on‑chain data exploration, has published a detailed style guide aimed at standardising the way analysts write SQL queries. The guide, co‑authored by Randall Potter and C. Ittevrec of Absinthe, lays out a set of conventions that promise to make blockchain queries more readable, maintainable and performant.
Why a Style Guide Matters for DeFi and NFT Analytics
Analysts on Dune routinely sift through millions of Ethereum rows to track everything from DeFi protocol metrics to NFT sales. While the platform’s powerful engine makes raw data accessible, the quality of insights often hinges on how the underlying SQL is constructed. Inconsistent naming, tangled sub‑queries and sprawling SELECT statements can impede collaboration, increase the risk of bugs, and slow down query execution.
The new guide addresses these pain points by codifying a “best‑practice” workflow that mirrors modern data‑engineering standards, such as those popularised by the dbt community. By encouraging a uniform approach, Dune hopes to lower the barrier for newcomers, speed up peer review, and ultimately produce a richer, reusable library of queries for the broader Web 3 community.
Core Recommendations
| Area | Guideline | Rationale |
|---|---|---|
| CTEs vs. sub‑queries | Prefer Common Table Expressions (CTEs) and organise them in a logical, step‑by‑step flow. | CTEs improve readability, simplify debugging and frequently offer better optimisation than deeply nested sub‑queries. |
| CTE structure | • Break queries into isolated, purpose‑driven CTEs. • Name each CTE descriptively (e.g., daily_transactions).• Filter unnecessary rows as early as possible. • End with a final CTE that produces the result set. |
Clear naming and early filtering reduce cognitive load and improve performance. |
| Formatting | • Start each new CTE with a leading comma. • Use leading commas for column lists. • Indent four spaces inside CTE bodies and align WHERE clauses with the keyword.• Keep line length under 80–120 characters. |
Consistent formatting makes version control diffs cleaner and prevents syntax errors when adding or commenting out lines. |
| Naming conventions | • Write all identifiers in lowercase. • Use AS for aliases and favour descriptive names over single‑letter shortcuts.• Adopt a consistent pluralisation strategy for table names. • Standardise ID columns (e.g., unique_row_id) and timestamps (current_timestamp AS last_updated_at). |
Uniform naming eliminates ambiguity, especially when queries involve many joins. |
| Join best practices | • Explicitly declare join types (INNER JOIN, LEFT JOIN).• Place the primary (left) table first. • Prefix column references with their table alias. • Prefer LEFT JOIN over RIGHT JOIN. |
Explicit joins clarify intent and prevent accidental omission of rows. |
| Aggregation & grouping | • Perform aggregations before joining to smaller tables. • Use positional references ( GROUP BY 1, 2) rather than column names where feasible. |
Early aggregation reduces data volume, speeding up downstream processing. |
| CASE statements | • Place each WHEN and ELSE on its own line, indent the THEN clause, and close with END on a new line. |
Improves the visual parsing of conditional logic. |
| Set operations | Use UNION ALL unless duplicate elimination is required; if it is, be explicit with UNION DISTINCT. |
UNION ALL avoids the costly duplicate‑removal step and is faster for most analytics workloads. |
| Tooling | Adopt linting/formatting tools such as sqlfluff and integrate them into editors or CI pipelines. | Automates compliance with the guide and reduces manual formatting effort. |
Analysis: Potential Impact on the Dune Ecosystem
- Accelerated Collaboration – With a shared visual and structural language, teams can review each other’s work more quickly, reducing the time from hypothesis to published dashboard.
- Higher Query Performance – Early data pruning and explicit joins align with Dune’s query optimizer, potentially lowering execution costs for large‑scale analyses (e.g., historical Ethereum transaction histories).
- Reduced Technical Debt – By enforcing DRY (Don’t Repeat Yourself) principles through reusable CTEs and modular models, analysts can avoid duplicated logic that often becomes a source of errors as protocols evolve.
- Lower Entry Barrier – New contributors, especially those transitioning from traditional finance or data‑science backgrounds, will find a familiar and predictable codebase, encouraging broader participation in Web 3 analytics.
- Community‑Driven Standards – Because the guide mirrors the dbt community’s “Fishtown” style, Dune positions itself to adopt future best‑practice evolutions with minimal friction, fostering a cohesive analytics culture across platforms.
Key Takeaways for Practitioners
- Prioritise readability over clever one‑liners; extra whitespace does not affect query speed but dramatically improves maintainability.
- Leverage CTEs to decompose complex on‑chain logic—think of each CTE as a mini‑pipeline stage.
- Adopt consistent naming (lowercase, descriptive aliases) and always use
ASfor clarity. - Explicitly declare join types and keep column references fully qualified to avoid ambiguity.
- Run aggregations early and filter aggressively to minimise the amount of data joining and scanning.
- Prefer
UNION ALLunless you truly need duplicate removal; it’s more performant. - Integrate linters like sqlfluff into your workflow to enforce the style guide automatically.
Looking Forward
The Dune SQL Style Guide is more than a checklist—it is an invitation for the DeFi and NFT analytics community to co‑create a robust, shared knowledge base. By aligning on a disciplined coding approach, analysts can focus on extracting insights rather than untangling convoluted queries. The guide’s release, coupled with tooling recommendations, signals Dune’s commitment to fostering a mature, collaborative ecosystem for on‑chain data exploration.
For a full view of the guidelines and contributions, interested parties can reach out to the authors via the contact information provided on the Dune blog.
Source: https://dune.com/blog/dune-sql-style-guide
