Allowing multiple deltas

[marcinsiewiera]

I have doubt whether current template would cover following scenario.
Let's assume that we need to load a Source that undergoes satellite split, only 2 attributes are added to 1 of the satellites other goes somewhere else.
Now we have received multiple deltas to process, however the part of the 1st record (D1, Attr1, Attr2) to be processed are exactly the same as the latest entry in the satellite and only subsequent entries change the scope of the satellite. Since only the earliest record is being compared from batch, there is a risk that entire batch will be ignored.
Is it indeed a problem or I don't get the code right?

[tkirschke]

Hi @marcinsiewiera and thanks for reaching out!

You have described a very neat example of arriving data! Lets go through the CTEs of a Satellite v0:

• source_data: wont kick out any of the source data because the loadDate is larger than the existing one
• deduplicate_qualify: wont kick out any of the source data, because no consecutive load has the same descriptive data
• records_to_insert: The WHERE NOT EXIST will filter out the highlighted row of the source data, because the descriptive attributes are the same as in the satellite. All other rows from the source_data are inserted.

So from my understanding, your proposed scenario does not lead to problems.
Let me know if this helps!

Best regards,
Tim

[marcinsiewiera]

Hi, I finally got it,
It's sufficient to check only the 1st row in the group, because the 2nd, 3rd, etc are definitely different thanks to previous step of deduplication.
Really clever! In this way you can process multiple changes in parallel and avoid iterations!
I suggest adding a line of comment in the template, so that others can understand it faster as well.

[cmettler]

Hi all,
As I understand it, the v0 template only loads the first delta from the Loaddate D2 and stops there. To load all the deltas received in a single batch, Dbt must be run multiple times. With the Highwater mark check the next delta check continues at D3?
Christoph

[tkirschke]

Hi @cmettler ,

The v0 Template does not stop after D2, it inserts D3 and D4 as well, all in one single dbt execution.

Best regards,
Tim

[cmettler]

Thanks! I need to take another look at it. :-) Right now, I've been assigned to a completely different project, but hopefully, I'll be able to come back to working on datavault4dbt on SQL Server soon..