[Discussion] About staging table unique key

[kavimaluskam]

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 :)