[BUG] solana bot trades lineage is unable to run full refresh and data is stale

[jeff-dude]

all models which feed into dex_solana.bot_trades are out of date in terms of propagating bug fixes / new DEXs added upstream to dex_solana.trades. when changes are made, or new DEX added, to dex_solana.trades, the table rebuilds fully in prod efficiently. once complete, all bot trades models kick off since they read from DEX. the bot trades models will not run in prod, regardless of compute infrastructure in place (number of worker nodes in cluster, amount of concurrency, etc).

due to this, typically we won't run these models after dex is refreshed. we also have not added a new bot trades model since.

in order to ensure data quality & add more models, we need to revamp the design for which they are built in order to run efficiently in prod.

the most impactful learning recently for spellbook performance on the trino engine has been to materialize early, and materialize often. we can use staging tables frequently to get data in the shape we need to use downstream in finals tables. trino performance is nuked when you inject a CTE or subquery into a join condition. the planner doesn't handle it well. if you materialize that CTE or subquery, then use as a table join in downstream model, it performs much better.

i think bonkbot is one of the worst performance models, it would be good to open a fresh PR with that one to rearchitect to follow these patterns.

for any of these upstream models built as staging tables, avoid displaying on data explorer with post hooks.

[jeff-dude]

ideal process:

• close out all open PRs for adding bot trades models
• focus on existing prod models to enhance first
• start with one (bonkbot) in a PR, modify to enhance performance, push live into prod
• repeat with other existing models (ideally one per PR, but can explore here)
• reopen closed PRs for adding new bot models
• enhance to new format, if proven efficient

[jeff-dude]

fyi @0xBoxer @whalehunting @clizzard7
we have had slow back and forth related to these models, but we should iterate on a plan here. i will close open PRs, as we cannot merge in current state. we can reopen as we reach that stage of enhancements.

[jeff-dude]

also fyi @Hosuke @0xSector @0xRobin

[whalehunting]

so essentially you want me to convert every CTE into a dedicated spellbook? @jeff-dude

[jeff-dude]

so essentially you want me to convert every CTE into a dedicated spellbook? @jeff-dude

it's our best start to test the difference in performance. we can create one spell for each, use some sort of stg_/stage_/base_ naming standard in the table to make it clear it's purpose. we can work closely on the first PR to make sure we're all on the same page.

[whalehunting]

implemented a refactored version in #8011 . it follows all your performance suggestions and is more modular and maintainable in general. once this gets accepted, i'll create an additional PR that puts the logic in a macro, then we can migrate all other solana bot models