Continuing to scale Spellbook for the next 1000's of spells 🪄

[jeff-dude]

Next steps towards this previous discussion.

What is changing?

Dune team will continue to scale out Spellbook on DBT with a multi-project approach within the repo. As the above link outlines, the first pilot was on the tokens sub-project, which has been running in production for a few months now. To summarize what happens within a sub-project:

• isolated dbt project, contains it's own project config file
• revamped CI pipelines, which only run models within sub-projects that have been modified
• breaks up the lineages, by switching some ref() usage to source() across projects
• models / tests / seeds specific to sub-project will live within the given project
  • macros / sources will remain in the root directory, which can be shared across all sub-projects
  • note: some macros that are project-specific can move into sub-project. Dune team will look to do a final pass over macros once reorg is fully in place, to keep macros organized
• more granular orchestration in prod, handled by the dune team
• sub-projects broken out by sector / project / themes -- i.e. dex schema, nft schema, token metadata, etc.

What will the new setup look like?

• root directory
  • .github
  • dbt_subprojects
    • daily_spellbook
      • dbt_project.yml
      • profiles.yml
      • packages.yml
      • package_lock.yml
      • models/
      • macros/
      • tests/
      • seeds/
    • dex
      • same as above
    • hourly_spellbook
      • same as above
    • nft
      • same as above
    • solana
      • same as above
    • tokens
      • same as above
    • ...future sub projects
  • docs/
  • git_scripts/
  • macros/
  • scripts/
  • sources/

Which spells will move to it's own sub-project?

• token metadata
• dex
• nft
• solana

What happens to all other spells?

All other spells will fall into the daily_spellbook or hourly_spellbook sub-project, which will be catch-all projects for all other lineages. We have already started piloting moving around a few spell lineages into the daily sub-project to see how it looks and runs in both CI & production. The results have looked good so far, similar to tokens.

The change from hourly --> daily for all other spells

When first building Spellbook, there were only a few hundred spells (most migrated from previous engines). It made sense to simplify orchestration in production by simply doing the below:

• if materialized as a table, run full refresh 1x/day
• if materialized as incremental, run 1x/hour
• if materialized as a view, run post-merge once and be done
• if modified, run full refresh, then continue with above

This continued to work well for almost two years, but the size of the project has grown substantially and orchestration has become less efficient. In total, there are 3,000+ spells, with ~1.9k of those being incremental. all 1.9k of these spells ran every single hour, and just barely fit into the hourly window. For full transparency, it was a bit over the hour runtime every hour, which led to queues in orchestration over time.

The other downside was a failure on a random spell could block many other spells from running as needed. For how much we've fit into this job, it's been pretty good!

The majority of spells within that ~1.9k number don't need to be refreshed as often as hourly. We simply would allow them to based on how they were materialized and kept it simple.

Moving forward, all spells outside of the main sub-projects will run on a daily cadence. No changes need to be made to materialization type, rather the orchestration logic will change to make it work.

How can I request a spell to be promoted into it's own sub-project and/or hourly spellbook?

This is an open question that we are discussing. We want to hear opinions from our engaged community contributors. We will also require these spells to fit the latest design principles to be as efficient as possible, in order to be included. The best example at this time is the full dex.trades lineage, which has been refactored recently to prepare for this new setup. nft.trades is finalizing it's refactor as we speak. Please ask via GH issue, this discussion or other means of contacting Dune team.

What happens next?

In addition to multi-project approach, we have also spent some time along with our platform team to find optimizations to be applied across various spells for improved performance. We will be looking to share this information and apply the enhancements as soon as we can get the time to do so. We look forward to sharing and seeing how our wizards will help keep Spellbook clean & efficient 🧙

fyi on discussion @aalan3 @couralex6 @0xRobin @Hosuke

[jeff-dude]

for reference, the first spell lineage we have moved due to lower level of complexity within the lineage is all chainlink spells.
#6027

[jeff-dude]

another example, account abstractions:
#6036

[jeff-dude]

moving more token spells to tokens sub-project:
#6034

[jeff-dude]

fyi @AnonJon @anon-r-7 on the chainlink spells mentioned here. this will apply universally, but this was a good spell lineage to test first

[jeff-dude]

fyi @tapstoone on the account abstraction spell lineage linked here, as git history shows you helped create

[jeff-dude]

enhancements to CI workflow, to support multi-project:
#6025