final stages of the spellbook migration to dunesql 🚀

[jeff-dude]

since we started the spellbook migration to the dunesql engine in mid-june, we've come a long way and are approaching the final stages 🪄

here is a dune dashboard that helps us tracking spell migrations:
https://dune.com/dune/spellbook-dunesql-migration

at the time of writing this discussion, here are some stats:

• 1,553 spells on dunesql engine
• 253 remaining legacy spells not migrated

of those 253 remaining, many are in progress and close to done -- such as dex.sandwiches lineage, labels, etc.

let's use this discussion thread together as a community to let each other know which remaining spells we're working on, as i know both internal dune teammates and wizards are taking on some of these 🫂

tagging everyone i know that is actively involved, plz do comment below the spells in the remaining list you are working on, so we can organize and knock this out:
@duneanalytics/team-spellbook
@0xRobin
@Hosuke
@hildobby
@henrystats
@tomfutago

[jeff-dude]

i can start:
i'm actively working on (with the help of @Hosuke) the 0x spells and then dex_aggregator.trades

[jeff-dude]

update: these are done 🚀
we are working through a bug in bnb 0x spell, but otherwise the lineage is done

[hildobby]

A few more tables to migrate and up only 🫡

I'm working on dex.sandwiches, zerion.trades

And unless taken by someone else I can work on all the contract_mapping and gas tables

[jeff-dude]

fyi @couralex6

[couralex6]

@hildobby I have a PR open for gas: #4405

I'm gonna try finishing it today, but will let you know if I have any issues

[hildobby]

alright I'll let you do the gas tables then @couralex6!

No one tackling contract mapping? I'll get on it

[henrystats]

amazinggg!

I'm working on the balances & transfers sectors along with @tomfutago.

I also started working on some of the nft_ethereum stats models (some were merged earlier but haven't reflected on the dashboard you shared, will finish the last one tonight).

I can also take on the dao sector since I've had a few contributions in that sector.

[aalan3]

@hildobby @MSilb7 (trying again here instead) the *.fees_traces tables are very heavy on the runs. And looking at our internal data the only one that's actually being queried right now is the optimism version.

How useful are these tables for you at the moment? And are there any plans to build on top of them?

Also taking a second look at these is there anything we can do to make the tables a bit less heavy? they basically copy the entire traces table, so querying these might not be that nice of an experience. Would it for example be possible to do some sort of aggregation (similar to balances)

We should also consider using incremental_predicate on these tables to make the runs a bit more manageable.

[0xRobin]

I'll start on the remaining /models/sudoswap/ models

[hildobby]

Noticed staking_ethereum.deposits was also in there, so I migrated it (+ it was very broken so I fixed it): #4440

I'll now work on migrating the airdrop_optimism_addresses_1 (Update: PR ready for review ✅), nft_ethereum_metadata (Update: PR ready for review ✅) and the timeswap sector (Update: PR ready for review ✅).

Are all the 'labels' sector spells being tackled? Else I can migrate those

[aalan3]

@Hosuke was working on Labels but it's blocked by dex.aggregator_trades.

[jeff-dude]

we should take this opportunity to remove airdrop_optimism_addresses_1 from spellbook, as dbt isn't intended to fit use cases for such large static files -- this one is ~500k lines. can we use the new data upload feature, then mark as source in spellbook instead? the file hasn't changed in 1+ year, so i don't see why not?
cc @MSilb7

for reference, the other massive static file (there are some others) is tokens_ethereum.erc20 but we're actively working on removing that from spellbook as well

[MSilb7]

Hi yeah we can make it a static upload along with airdrop 2&3 lists.

I can comment back here when those are in!

Raw lists are available here as well for reference: https://github.com/ethereum-optimism/op-analytics/tree/main/reference_data/address_lists

[MSilb7]

uploaded at:

• dune_upload.op_airdrop1_addresses_detailed_list
• dune_upload.op_airdrop2_addresses_detailed_list
• dune_upload.op_airdrop3_addresses_detailed_list
• dune_upload.op_unclaimed_airdrop_1_distribution_simple_list (Note: This is a subset of airdrop1 list)

[jeff-dude]

awesome!
we will need to coordinate internally a bit on how to handle this in spellbook, as i believe it's the first time we'd use dune_upload tables as a source in spellbook and there is added risk that the data can change any time and break spells

fyi @aalan3 for internal convo

[Hosuke]

I am trying to migrate maker_ethereum_accounting. #4342

Also migrating these spells in #3809 :

• model.spellbook.nft_ethereum_wallet_metrics
• model.spellbook.labels_nft_smart_trader_roi_eth
• model.spellbook.labels_nft
• model.spellbook.airdrop_optimism_addresses_1
• model.spellbook.labels_airdrop_1_receivers_optimism
• model.spellbook.labels_airdrop
• model.spellbook.labels_addresses

[couralex6]

I already have this PR for cow_protocol! @Hosuke

[Hosuke]

Oh sorry.

[0xRobin]

@Hosuke model.spellbook.nft_ethereum_wallet_metrics
is being worked on by @henrystats here: #4358

[Hosuke]

Sorry for the overlapping work, I migrated the spell in labels migration, since it was part of dependencies.

[0xRobin]

No worries! I'll close Henry's PR as you already have it covered in your PR.

[jeff-dude]

we have now turned off spark orchestration both in prod & CI tests 🔥

we need to finalize a few things to close up the migration, then look to announce in proper channels to users, but we're considering the migration complete.

for transparency, there was a handful of spells left that weren't straightforward to translate and had little to no usage on the dune app in terms of queries executed anyway, so we're viewing those as low risk and can fix ad-hoc if it arises.

side note: we can now stop building dummy legacy files or editing existing ones