Having recently attended the CDV2P training and attaining the CDV2P-Legacy certification, PerfpormanceG2 is providing free of charge the DV101-Baseline Hands-On training course. The material was created in January of 2022 and is supposed to represent the current guidelines of the methodology. It has raised a question for me when Dan talked about different types of satellite tables as shown below.
I was under the impression that updates were deprecated in 2.0. In the above table, he has a start date column as expected. However, there are also the end date and iscurrent columns that require an update to correctly represent the data. Furthermore, there was nothing in the course about keeping previous primary key values that allowed for creating a reverse link list – although I do like the idea. Getting back to my topic, is having the end data and iscurrent columns a best practice?
Thanks
Clay
The source might be a dim or an MDM model that uses start and end dates too!
Look up IBM’s MDM (public domain) and you’ll see things like location_group that maps parties to addresses and manages its own effectivity that we simply capture and load into the DV as INSERT-ONLY.
IBM MDM model is an example of an enterprise data model that is designed to serve as many industry models as possible, the thought, is that you’ll end up with a lot more tables to ingest!
@Bigguy365 - Did you watch the associated videos in the DV101-Baseline Hands-On training course? I watched them over a month ago, and I believe (if I remember correctly) that he explains why those fields are included in these satellites.
If I still have access to them, I will review them and let you know what I find (unless you find it first).
@Nicruzer - Yes I watched all 32 videos and yes he does explain why he used them. His explanation is contrary to what was taught in the class and that was INSERT ONLY. That’s why I posted the question for clarification. According to Patrick – they are deprecated.
Hi All,
In the videos created by DVA, Dan explains that the effectivity satellite is a ‘derived’ object and thus considered BVish and thus not subject to the insert only restriction. I think he also provides and example of a effectivity satellite pattern that does not require updates but doesn’t have the active flag/end date.
So, raw DV update only, not end_dates. BV is less restrictive, same principles apply but not standardized.
Effectivity satellites are insert only
They have end dates, physicalised
In my book I show how to build them and query them
They definitely don’t grey the line of DV2.0 INSERT ONLY principles
Complex and why I recommend that relationship effectivity solved at the source before implementing EFS
It’s is a Raw Vault artefact when source cannot resolve flip flopping relationships for you
It’s business Vault if you are defining a driver key different to how the source defines it. Yes it can be both!
It might be that the effsats you are showing there are actually views. If you look at what scalefree did in their dbt4datavault package (which forked from the sponsors of this eminent forum’s version dbtvault) they have a sat _v0 which is an insert only table, and then a sat which has the is_current and the end date as calculated fields in the view.
Hey Patrick.
I have read your book but, it does not answer how you can have insert only end dates in effectivity satellite. It talks about how to generate virtual end date. All the related examples (pictures) have an explicit end date defined. Maybe you can point a specific page. The bottom line is if you have column end date (not virtual but a part of the schema ) then you would need to close it somehow.
Thanks.
