Hi, new to this group and using dbt. I was wondering while loading sat tables using incremental load, if the unique key exists but the values of the rest of the row is changing, can dbtvault keep the old row but modify a flag column to identify its an old row(is_deleted = Y) and then insert the new values as a new row with (is_Deleted= N). Right now for me dbtvault is either replacing the whole row or keeping the old row as is unchanged(with older date columns ofcouse).
Data vault is an insert only model. There is no need to update rows in a satellite. I understand that you are trying to solve a requirement given to you. You should solve it in another way. You can always select the latest row per bk.
Not sure how you would do this in dbtvault, but the tool we are using (its a Swiss/Austrian tool called biGenius) creates what it calls result and result history views combining all the interesting data for each entity.
The result history view is basically all the versions and then uses a rank/partition by hub key order by load date to get the order of the hub key being implemented (effectively a type 2 SCD), then the result view selects the current version (a type 1)
But anyway, you want views, and you want to have a rank/partition thing going on. You don‘t need a ‚deleted flag‘, its actually an anti-pattern
1 Like
I think what you might be looking for is an Effectivity Satellite or even a Record/Status tracking satellite.
As both the previous community members have suggested, you should not be updating any records at all; a Satellite is supposed to keep the history.
1 Like