search

duneanalytics / spellbook

SQL views for Dune
Other
1.19k stars 1.13k forks source link

readme

spellbook-logo@10x spellbook-logo-negative_10x

Welcome to Spellbook. Cast a magical incantation to tame the blockchain.

TL;DR

Table of Contents

Introduction

Spellbook is Dune's interpretation layer, built for and by the community.

Spellbook is a dbt project. Each model is a simple SQL query with minor syntactic sugar (meant to capture dependencies and help build the resulting tables), and does a small part of the task of turning raw and decoded records into interpretable blockchain data.

Spellbook is built for and by the community, you are welcome to close any gaps that you find by sending a PR, creating issues to propose small changes or track bugs, or participate in discussions to help steer the future of this project.

Docs

Spellbook has a lot of moving parts & specific design principles for contributing to Dune's interpretation layer of data. In order to prepare contributors to participate most efficiently, the docs directory contains a wide ranging set of topics to answer common questions & provide info on why the repo is setup as it is. Please read & refer to this section when developing in Spellbook and questions arise. The Dune team will also link back to these docs to answer questions often, to help grow awareness and keep communications clean.

Sub-projects

In order to scale Spellbook, the repo has introduced sub-projects to break out complex DBT lineages a bit & keep focus areas clean. This will also help downstream orchestration to keep spells fresh in production. DBT sub-projects in Spellbook are simply multiple DBT projects within one repo. The current structure for projects:

For further information on sub-projects, please visit this discussion and ask any questions there.

Ways to contribute to Spellbook

Submitting a PR

Want to get right to work? Follow the guide here to get started.

Testing your spell

You don't need a complex local setup to test spells against Dune's engine. Once you send a PR, our CI pipeline will run and test it, and, if the job finishes successfully, you'll be able to query the data your PR created directly from dune.com.

Simply write a query like you would for any of our live tables, and use the test schema to fetch the tables your PR created.

test_schema.git_dunesql_{{commit_hash}}_{{table_name}}

You can find the exact names easily by looking at the logs from the dbt slim ci action, under dbt run initial model(s).

Please note: the test tables built in the CI pipeline will exist for ~24 hours. If your table doesn't exist, trigger the pipeline to run again and recreate the test table.

Connecting with other wizards

We use Discord to connect with our community. Head over to spellbook channel on Dune's Discord for questions or to ask for help with a particular PR. We encourage you to learn by doing, and leverage our vibrant community to help you get going.

Setting up your Local Dev Environment

Prerequisites

Initial Installation

You can watch the video version of this if you scroll down a bit.

Navigate to the spellbook repo within your CLI (Command line interface).

cd user\directory\github\spellbook
# Change this to wherever spellbook is stored locally on your machine.

Using the pipfile located in the spellbook repo, run the below install command to create a pipenv.

pipenv install

If the install fails, one likely reason is our script looks for a static python version and the likelihood of an error for a wrong python version is pretty high. If that error occurs, check your python version with:

python --version

Now use any text editor program to change the python version in the pipfile within the spellbook directory to your python version. You need to have at least python 3.9. If you have changed the python version in the pipfile, run pipenv install again.

You are now ready to activate this project's virtual environment. Run the following command to enter the environment:

pipenv shell

You have now created a virtual environment for this project. You can read more about virtual environments here.

Within the Spellbook repo, there are multiple dbt projects, located in the root directory. Navigate to the correct project, depending on your use case.

cd ../spellbook/dbt_subprojects/<subproject_name>/

Each subproject has it's own dbt project file with varying configs. Once your CLI has navigated to the correct project directory, follow the below steps:

Each Spellbook subproject includes a profiles.yml file, which helps tell dbt how to run commands. The profile is located in each subproject directory, such as here. This should never need modified, unless done intentionally by the Dune team.
Due to the profiles.yml file being stored in the root directory of each subproject, this is why users must be in the root directory per subproject on the command line to run dbt compile as expected.

dbt compile will compile the JINJA and SQL templated SQL into plain SQL which can be executed in the Dune UI. Your spellbook directory now has a folder named target containing plain SQL versions of all models in Dune. If you have made changes to the repo before completing all these actions, you can now be certain that at least the compile process works correctly, if there are big errors the compile process will not complete. If you haven't made changes to the directory beforehand, you can now start adding, editing, or deleting files within the repository. Afterwards, simply run dbt compile again once you are finished with your work in the directory and test the plain language sql queries on dune.com.

Coming back

If you have done this installation on your machine once, to get back into dbt, simply navigate to the spellbook repo, run pipenv shell, and you can run dbt compile again.

What did I just do?

You now have the ability to compile your dbt model statements and test statements into plain SQL. This allows you to test those queries on the usual dune.com environment and should therefore lead to a better experience while developing spells. Running the queries will immediately give you feedback on typos, logical errors, or mismatches. This in turn will help us deploy these spells faster and avoid any potential mistakes.

How to use dbt to create spells

There are a couple of new concepts to consider when making spells in dbt. The most common ones wizards will encounter are refs, sources, freshness, and tests.

In the body of each query, tables are referred to either as refs, ex {{ ref('1inch_ethereum') }} or sources, ex {{ source('ethereum', 'traces') }}. Refs refer to other dbt models and they should refer to the file name like 1inch_ethereum.sql, even if the model itself is aliased. Sources refer to "raw" data or tables/views not generated by dbt. Using refs and sources allows us to automatically build dependency trees.

Sources and models are defined in schema.yml files where tests and other attributes are defined.

The best practice is to add tests unique and non_null tests to the primary key for every new model. Similarly, a freshness check should be added to every new source (although we will try not to re-test freshness if the source is used elsewhere).

Adding descriptions to tables and columns will help people find and use your tables.

models:
  - name: 1inch_ethereum
    description: "Trades on 1inch, a DEX aggregator"
    columns:
      - name: tx_hash
        description: "Table primary key: a transaction hash (tx_hash) is a unique identifier for a transaction."
        data_tests:
          - unique
          - not_null

  sources:
  - name: ethereum
    freshness:
      warn_after: { count: 12, period: hour }
      error_after: { count: 24, period: hour }
    tables:
      - name: traces

See links to more docs on dbt below.

Generating and serving documentation:

To generate documentation and view it as a website, run the following commands:

See dbt docs documentation for more information on how to contribute to documentation.

As a preview, you can do things like:

DBT Resources: