readiness for Production?

[klaus1978]

Hi,
I am going to start a new DV dbt project soon, and thus I am comparing the different dbt solutions dbtvault and datavault4dbt.

dbtvault is now available for several years and they reached version 0.9 latestly.
Is datavault4dbt already on the same level?

What about missing objects: first of all a macro for "effectivity Sats" which I was learned would be the standard for handling relationship aging in case there is not already valid_from and valid_until columns in the source?
Instead datavault4dbt uses the "record tracking sat" (for the moment) to handle relationship aging

1. Is a "Record Tracking Sat" now the standard object to be built (from DV methodology sight) instead of an "Effectivity SAT"?
2. Will the "Effectivity Sat" macro be added in a later version?
3. How is "record tracking sat" macro working if you use a delta load process? Or would I need a persistent staging area to use it then?

Thanks a lot for clarification
Klaus

[tkirschke]

Hi, I am going to start a new DV dbt project soon, and thus I am comparing the different dbt solutions dbtvault and datavault4dbt.

dbtvault is now available for several years and they reached version 0.9 latestly. Is datavault4dbt already on the same level?

What about missing objects: first of all a macro for "effectivity Sats" which I was learned would be the standard for handling relationship aging in case there is not already valid_from and valid_until columns in the source? Instead datavault4dbt uses the "record tracking sat" (for the moment) to handle relationship aging

1. Is a "Record Tracking Sat" now the standard object to be built (from DV methodology sight) instead of an "Effectivity SAT"?
2. Will the "Effectivity Sat" macro be added in a later version?
3. How is "record tracking sat" macro working if you use a delta load process? Or would I need a persistent staging area to use it then?

Thanks a lot for clarification Klaus

Hi Klaus!

Datavault4dbt has been tested a lot and is based on years of successfully implementing Data Vault on dbt, therefore it is ready for production!

Regarding your questions on how to handle effectivity:

Since effectivity can be determined in many different ways, there isn't "the" standard object to build. If there is nothing about effectivity in the source data, you would have to use our record-tracking satellite macro to store information about all the appearances of an object. Based on these appearances, you can apply business logic to define effectivity.

The record-tracking satellite is best materialized as a table and therefore ready for delta loads. It would capture the appearances in each delta, as long as the corresponding dbt model is executed on top of each delta. You are not limited to a persistent staging area to get the value of record-tracking satellites.

For more information about effectivity in datavault4dbt, see this wiki page.

I hope this helps, if not don't hesitate to reach out to us via [email protected]!

Kind regards,
Tim

[klaus1978]

Hi Tim,
thanks for the fast response.
I still do not see how I can use a Record tracking Satellite if I use eg CDC or any other delta.
EG:
I have a Sales Order including a Customer in my Stage data. (CDC)
--> When I run my load today I have SO4711 and CUST0815 --> I write that into my Link with L_SO4711_CUST0815 (Hashkey) (and the corresponding record tracking Sat)
The next days there is no update on our SO in the source system--> no entry in my CDC for SO4711 --> no update of the Record Tracking Sat as well as CDC has not detected a change.
Then suddenly there is a change on my SO4711 because the customer changed to CUST9999 --> CDC detects a change --> new Link entry (with a new L_SO4711_CUST9999 --> new entry in Record Tracking Sat.
But how would you determine that the first Link L_SO4711_CUST0815 is obsolete now?

As long as I perform full loads for the LINK / Record source I would be able to determine the change, but with CDC/delta load???

Thanks a lot for the help

Klaus

[molschimke]

Hi Klaus,

the reason why we did not added the effectivity satellite to the package yet was exactly because of your issue: depending on the loading mechanism, we would use a different loading pattern to load the effectivity satellite.

Therefore we only added those entities where there is no much option (e.g. a hub is always loaded by a distinct query of the business keys...). There are additional patterns which are relatively easy (e.g. reference tables) but they are just not done yet. Additional entities will come, including more complex ones but we'd like to come up with a highly reusable pattern based on customizable options.

For now, the best approach would be to either: use the record tracking satellite if possible or come up with your individual pattern for an effectivity satellite. Our consultants do this in individual projects to optimize the pattern for the specific context. However, to be added to this package, the code must be generalized to be a better fit for more (if not all) clients.

With that in mind, for CDC data, the most efficient option is to retrieve the DEL flags from your CDC package and load the data into the effectivity satellite as delta. INS and UPD become also deltas, but with deletedDTS set to open.

The pattern might exist already internally at Scalefree, it's just not ready for publication. Best is to get in touch with the internal team via [email protected] - if available they can send you the code.

Hope that helps,
Mike

[klaus1978]

Hi Michael,
great thanks a lot, und vielen Dank
Klaus

[maurice-v]

Hi,

what is the status of the effectivity satellite? I see some commits on this topic, but the macro's where removed.
Is there a other solution to mitigate the needs of a effectivity satellite on link?

[universe-designer]

There is this old branch https://github.com/ScalefreeCOM/datavault4dbt/tree/Eff_Sat_Backup that I customized for oracle, but currently I have a bug in there.

I've seen also a new branch https://github.com/ScalefreeCOM/datavault4dbt/tree/effectivity_satellite.

@tkirschke can you tell us which branch to use in which situation? Even when it is in alpha / beta state.