README.md

spellbook

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

Dune contributors using the project

Use the pipfile to create a pipenv.

cd spellbook/
pipenv install

If the env is created successfully, enter the env shell. (If you run into Python version errors, try installing the matching version or editing the python version in the Pipfile but don't commit your change).

pipenv shell

Set up the dbt spellbook project. (You must run this step from the spellbook directory) Run dbt init and select Databricks, then enter . or other placeholders for the host, HTTP path, and token. This will not connect to the database but you have access to some dbt actions.

When you are prompted to choose a target, please enter wizard so we know you are an external contributor.

dbt init

Then, run the following commands:

cd spellbook/
dbt compile

dbt compile will compile the JINJA and SQL templated SQL into plain SQL which can be executed in the Dune UI. We are thinking about better solutions to make more dbt actions available directly but also have to consider security.

setup.mov

How to use dbt to create abstractions

There's a couple new concepts to consider when making abstractions 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.

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."
        tests:
          - unique
          - not_null
 
  sources:
  - name: ethereum
    freshness:
      warn_after: { count: 12, period: hour }
      error_after: { count: 24, period: hour }
    tables:
      - name: traces
        loaded_at_field: block_time

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:

• dbt docs generate
• dbt docs serve You must have set up dbt with dbt init but you don't need database credentials to run these commands.

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

As a preview, you can do things like:

• Write simple one or many line descriptions of models or columns.
• Write longer descriptions as code blocks using markdown.
• Link to other models in your descriptions.
• Add images / project logos from the repo into descriptions.
• Use HTML in your description.

Dune employees using the project developing locally

Follow instructions from Databricks on how to set up dbt-core.

Use the dbt_local_development cluster on Arrakis-Dev. Look under Advanced Options and JDBC/ODBC to find host name, port, and HTTP Path. Use a schema with your name in it, ex dbt_meghan. Ask [email protected] if you need help.

Create your own access token for the Arrakis Dev. Save this to a secure password manager.

Try running the following commands from spellbook directory:

• dbt run
• dbt test
• dbt run --select {model_name e.g. opensea_trades}

Try dbt debug if the commands above do not work.

See the dbt command reference for more options

Dune employees using the project developing in the cloud

Ask [email protected] to invite you to the dbt cloud projects.

In dbt cloud, navigate to your profile on the right hand side and then credentials in the menu on the left. You'll need to add a personal access token to the spellbook dev project from generated from the arrakis-dev databricks workspace. You won't need to add credentials for spellbook prod.

These credentials will be used for the Develop IDE in the hamburger menu. You can run dbt commands from this browser IDE and preview query results, lineages, and compiled SQL.

Dune employees deploying the project

Each pull request triggers a PR job in dbt cloud spellbook dev. This job will run and test only the models that have been modified since the last dev deploy job which runs daily. Verify this job runs correctly and request code review from a colleague. When approved, merge into main.

When we are ready to deploy to production, merge main into a new branch named following the v{major}-{minor}-{patch} pattern. In dbt cloud spellbook prod deploy environment, update the custom branch to match the new branch (note the name of the current branch). Trigger the production deploy or production deploy full refresh job depending on whether the change needs to rewrite exisiting tables.

If the deploy fail, rollback by replacing the previous branch and trigger the job again.

Troubleshooting

If you fail to run dbt compile with Could not find profile named 'spellbook' as the error message, check ~/.dbt/profiles.yml and make sure there is a profile named spellbook. When you run dbt init to initiate a project, a profile gets created. Inside spellbook you cannot initiate a project called the same name, so you need to run dbt init spellbook outside the project so it creates the profile, or create one with a different name and then manually edit the profiles.yml file.

DBT Resources:

• Learn more about dbt in the docs
• Check out Discourse for commonly asked questions and answers
• Join the chat on Slack for live discussions and support
• Find dbt events near you
• Check out the blog for the latest news on dbt's development and best practices