[BUG] duplicates in `bridges_evms.deposits`

[jeff-dude]

@hildobby we had failures all weekend for bridges_evms.deposits model having duplicates based on the unique key provided in the model.

select deposit_chain,withdrawal_chain,bridge_name,bridge_version,bridge_transfer_id, count(1)
from bridges_evms.deposits
group by deposit_chain,withdrawal_chain,bridge_name,bridge_version,bridge_transfer_id
having count(1) > 1
limit 1000

since we are past the 2-day lookback period for incremental runs, the job is no longer failing, as the troubled data is outside of that timeframe. however, if we make any additions or changes to this bridges dataset, it will break on full rebuild.

please take a moment to look into this issue and push a PR to fix when you can, prior to making any other additions to the bridges lineage of models.

[hildobby]

ah alright ty @jeff-dude, this is due to CCTP which is an intent bridge. intent bridge means the user executes the deposits but also the withdrawal, most other bridges just require executing the deposit. it seems like it's possible to send deposit events with the same values for unique_id and that only the latest one matters (withdrawal recipient always seems to match the one indicated in the latest deposit event data when there are duplicates).

so my guess is we should remove the uniqueness check from bridges_evms.deposits and change it so that overwrites any old value with the new one with matching id rows. how could i do this @jeff-dude ?

this should only be necessary for intent bridges, should we split the two types bridge types into different two tables (non intent as is, add intents with overwrite instead of a uniqueness check). this shouldnt be necessary for withdrawals afaik fyi

[jeff-dude]

so my guess is we should remove the uniqueness check from bridges_evms.deposits and change it so that overwrites any old value with the new one with matching id rows. how could i do this @jeff-dude ?

not sure i understand this. the model is already set up to be a merge incremental strategy, meaning it will overwrite the existing row when it finds a match on the unique keys. if we look at below query for example of the dupes, i would expect the more recent block_time row to have updated the prior row. few things to consider:

• we filter both source and target incrementally, but that incremental macro filter is 2 day lookback, so we should find the target row when joining on unique keys
• this issue popped up recently (~august 8-11th timeframe), but the tx's are from june 30th? are they arriving in the decoded table late or something? a bit odd to see that timeframe

select *
from bridges_evms.deposits
where 
    deposit_chain = 'avalanche_c'
    and withdrawal_chain = 'solana'
    and bridge_name = 'CCTP'
    and bridge_version = '1'
    and bridge_transfer_id = '296353'

[hildobby]

well my guess is just that the two day lookback is just insufficient since users can resubmit intents (with same set of unique id columns) whenever until the withdrawal is processed.

otherwise i can adapt the script to instead have no checks on deposits and then merge each withdrawal with the latest corresponding deposit. what do you think @jeff-dude ?

[jeff-dude]

well my guess is just that the two day lookback is just insufficient since users can resubmit intents (with same set of unique id columns) whenever until the withdrawal is processed.

otherwise i can adapt the script to instead have no checks on deposits and then merge each withdrawal with the latest corresponding deposit. what do you think @jeff-dude ?

go ahead open PR to use CI pipeline to test your ideas. it'll help me picture it better with the code modifications.

[hildobby]

here #8668 @jeff-dude, lmk if that works for you

[0xBoxer]

will maybe be fixed by #8832

[hildobby]

will maybe be fixed by #8832

yeah that's one of this PR's many goals