[Discussion] About staging table unique key
Closed this issue · 0 comments
First of all great work here!! And @FlipsideCrypto is so great to opening the community interface for users :).
Why?
I am trying to use setup similar to yours with dbt, building some staging table as well.
Often I find records failed to be unique by 'tx_id' as one single transaction can consists of multiple similar message.
i.e. https://terrasco.pe/mainnet/tx/2845976B16715B12F9D232DC4E8A309D904628642FFBED4429AFD9AEF096FC71, anchor borrows event.
I have made some changes to ensure the schedule update clean. I wonder if you have faced similar issue?
What?
I can see unique_key='tx_id'
is still applied in table like stg_anchor_borrow_borrows
, which shall fail due to multiple record with identical tx_id
.
How?
In my staging table I am trying to use composite key tx_id
+ msg_index
, which shall be able to distinct all the tx msg, one drawback is I need to create an additional column stg_id
for that.
Afterall, I see the flipside.anchor
tables have not such issue at all, probably due to no unique_key
is applied.
Just like to check if you have come across this issue, and any opinions on that :)