Sector spell design: how to organize the dbt lineage

[0xRobin]

I'm going to expand the current nft.trades base_trades models design from ethereum to the other chains.
While we're also applying the same macro design to the dex.trades sector spells, I think it's a good time to reflect on the current design and to make a judgement call on the preferred order of operations. There are 2 main possibilities, both with pros and cons.

The elements:

1. A base platform model, deriving all the necessary base columns from the platform specific contract tables
   (ex: models/_sector/nft/trades/ethereum/platforms/blur_ethereum_base_trades.sql)
2. An enrichment macro which purpose is to join in the necessary meta tables (transactions, prices, aggregators, ...) and add the necessary metadata columns.
   (ex: macros/models/_sector/nft/enrich_trades.sql)
3. a chain union model which brings all the different platforms together in one chain specific sector spell.
   (ex: models/_sector/nft/trades/nft_ethereum_trades_beta.sql)
4. a sector union model which brings all the different chains together in the top level sector spell
   (ex: models/_sector/nft/trades/nft_events.sql)

The 2 options

A: base -> chain-union -> enrich -> sector-union (current)

This is what we currently do in the nft.trades lineage, where the chain-union and enrich step happens within nft_ethereum_trades_beta.sql

pro's:

• The join on the meta tables happens only once for each chain. This minimizes overall compute.

con's:

• When 1 base table is modified, the full union and enrichment step has to be fully refreshed. This results in more expensive modifications.
• The combination of the union and enrichment into 1 model and macro is a bit complex and not very developer/maintainer friendly.

B: base -> enrich -> chain-union -> sector-union

Here we would design the enrichment macro to only do the enrichment, the macro should be applied within the platform model after collecting the base columns from the platform contract tables.
pro's:

• The join on the meta tables happens once for each base model. The compute cost can be parallelized.
• The overall flow is more intuitive and the macro design will be less complex.
• When 1 base table is modified, only that specific platform models need's to be fully refreshed. Downstream unions that are materialized will also need a refresh but these won't involve any joins with the enrichment tables.

con's:

• The join on meta tables happens multiple times, making this total compute greater (although it can be parallelized)

[0xRobin]

This PR is an example of using Option B: #4519

[0xRobin]

cc: @aalan3 @jeff-dude @couralex6 @Hosuke
Would be good to get your thoughts on this before we solidify the current approach.

[Hosuke]

Based on the description above, I think currently I am using the Option A approach in this PR: #4533

But I can change the implementation/approach based on the conclusion in this issue.

Also now here is the Option B approach demo: #4607

[jeff-dude]

thanks @0xRobin for putting together the options, nice to see some reflection on the redesign after taking a step back during migration and then revisiting.

my main concern when redesigning spells -- with sector spells such as dex/nft trades being best examples -- is reducing downstream lineage dependencies for prod orchestration. one of our main tasks this quarter is to reduce downstream dependencies from erc20.tokens metadata, as a single token addition/modification results in 600+ spells being fully refreshed, as they are materialized as tables and store the metadata within them.

with that said, my concern with option B, if i'm following exmple PR properly, is that we still materialize each project spell with metadata included from erc20 tokens. the goal moving forward should always be:

• does this spell join to any metadata table?
• if so, ensure it's materialized as a view (or full table, as that is rebuilt every time too)

[jeff-dude]

• The combination of the union and enrichment into 1 model and macro is a bit complex and not very developer/maintainer friendly.

i didn't find this too complicated, but of course, i'm biased as an every day user of spellbook. outside opinion would be appreciated

[jeff-dude]

after further discussion, we'll go with option A above, but slightly modified.

the tl;dr:

• base models will only contain raw data from decoded & base source tables, to keep lineages & orchestration clean
• union base models together as intermediate table, prior to sector-level spell
  • change: separate union and enrichment macros, for ease of readability / support / etc.
• sector-level spell which unions all upstream together -- ideally a view, since all upstream are materialized, but if poor performance, can materialize
  • change: calls universal macro (one per sector) which enriches the raw data with metadata, such as tokens/prices/aggregators/etc.

this is where nft & dex differ a bit in their lineage.

• nft.trades: base project spell --> all projects, one blockchain --> all projects, all blockchains
• dex.trades: base project spell --> one project, all blockchains --> all projects, all blockchains

here is more detailed dex lineage example:

• one sector, one project version, one project, one blockchain** (if applicable, not all have versions)
  • uniswap_v1_ethereum.trades
• one sector, one project, one blockchain (union all versions if step 1, OR normal logic on project)
  • uniswap_ethereum.trades
• one sector, one project, all blockchains
  • uniswap.trades
• one sector, all projects, all blockchains
  • dex.trades

i'm comfortable with either approach, as the goal is to try to avoid having all of these intermediate spells queried anyway, while pushing for usage on the sector-level spell itself instead.

[jeff-dude]

i'm comfortable with either approach, as the goal is to try to avoid having all of these intermediate spells queried anyway, while pushing for usage on the sector-level spell itself instead.

@0xRobin @aalan3 please do share opinion on this, whether we choose one route or both, then we can close this issue as resolved and feel confident moving forward on sector redesigns.

[0xRobin]

i'm comfortable with either approach, as the goal is to try to avoid having all of these intermediate spells queried anyway, while pushing for usage on the sector-level spell itself instead.

Since the goal is to not expose and query them, I tried to clean them up and remove them as much as possible. There's no need to have uniswap_ethereum.trades or uniswap.trades if you're not going to query it, it just adds an extra layer to the model DAG.
uniswap.trades is useful to expose but should become a view on top of dex.trades (so it has all the metadata columns)

[0xRobin]

#4519 shows of the new structure.
As we include tx_from and tx_to in the base models we can get rid of any intermediate steps and just have base models -> union -> enrichment.
Having the enrichment as a view did not work out though, it resulted in minutes long query times (which now take seconds when it's materialized). So I suggest keeping the top level sector spell materialized for now.

[jeff-dude]

i'm comfortable with either approach, as the goal is to try to avoid having all of these intermediate spells queried anyway, while pushing for usage on the sector-level spell itself instead.

Since the goal is to not expose and query them, I tried to clean them up and remove them as much as possible. There's no need to have uniswap_ethereum.trades or uniswap.trades if you're not going to query it, it just adds an extra layer to the model DAG. uniswap.trades is useful to expose but should become a view on top of dex.trades (so it has all the metadata columns)

yep, i like this perspective. i'm comfortable with:

• base project/versioned spell --> chain union --> sector union --> (optional) project views
• example: uniswap_v1_ethereum.base_trades --> dex_ethereum.trades --> dex.trades --> uniswap.trades
• example: blur_ethereum.base_trades --> nft_ethereum.trades --> nft.trades --> blur.trades

that keeps the changes minimal on the new nft lineage, and gives us a framework for dex. of course, nft is a bit different with events/trades/mints/etc, but for simplicity, we'll keep examples as above. each sector will have minor differences at the sector level, but in general, this flow will be consistent.

PRs to follow!
thanks for the collab here. we will link back to this issue in the PRs as well as potentially in gh discussions to inform the community.

[jeff-dude]

i've converted the original issue into a discussion. i think we can centralize the conversation here and continue to link examples as we go. not 100% sure this is the best path, but giving it a go.

[jeff-dude]

closing this already 😅 as i've started a new issue here: #4637