We need to init (NOT BACKFILL) a data vault from a incremental archive of 10 years daily deltas. The deltas are no real deltas but let’s say that the records have been extracted with some sort of CDC. The daily batches have been preserved and for many satellites the daily number of changes are in the 100.000 rows ballpark, so we don’t want to use a daily loop and consider a rehashing query approach.
In the Migration chapter of the Data Vault Guru it is recommended that we use the load timestamp of when the data is migrated.
In the history we have a technical load timestamp that indicates when the original source file was loaded. Each row also contains a applied timestamp (dv_appts). For performance reasons we think it would be best to partition the data by business years.
So we might have three options for the load timestamp:
-
Use a single load timestamp for the migration over all partitions
-
Use a load timestamp per migration partition and load the partitions sequentially
-
Use the historical source load timestamp
I’m inclined to option 2, but include the original load timestamp as an extra metadata attribute.
Any advise?
Thank you in advance.
Option 3 (original), as both 1 and 2 csn have SATs pk(hk,ldts) collisions when theres multiple deltas on same timestamp.
Column Meaning Value during migration
----------------------- ---------------------------------------------------------- ----------------------------------------
DV_LDTS Historical technical arrival/order of the source delta Original source/archive load timestamp
DV_APPTS Time represented by the daily extract or applied dataset Existing historical applied timestamp
MIGRATION_LDTS When the row was inserted into the new Data Vault Current migration-run timestamp
MIGRATION_RUN_ID Operational audit and restart identifier Unique migration execution ID
MIGRATION_PARTITION_ID Physical processing unit Business year, month, file group, etc.
Using the original archive/PSA load timestamp for a multi-version initialisation is a recognised approach because it preserves version order and gives each Satellite version a usable timestamp.
Historical Satellite loads should process the source versions in timestamp order and retain only actual hashdiff changes.. i suppose original backup data didn’t come from a DV, meaning loading as-is + calculating hasdiff column and loading only differences ?
SELECT *
FROM historical_deltas
QUALIFY hashdiff IS DISTINCT FROM
LAG(hashdiff) OVER (
PARTITION BY parent_hash_key
ORDER BY dv_appts, original_load_timestamp
);
Important adding order by when loading DV SATs, as per pk for performance pruning downstream.
Instead of auditing MIGRATION_* columns, or some other type of contextual metadata in Snowflake it’s accepted loading json on a column of VARIANT type.
Hope this helps.
1 Like
Thank you very much, yes this helps.
You are right, the data is not from a DV and we will load as-is and calculate the hashes on the fly, loading only differences. I’m a bit worried about the fact that we found meny situations that require multiactive sats, though.
Hi ! 3) is mentioned in one of Daniel Linstedt 's DV books.
1 Like
In most “aparent” situations of what seems many MAS are smoke to careful reclassify:
10 years archived deltas
|
v
Identify actual source grain
|
+--> sequential versions(like intraday) ----> Standard SAT
|
+--> independent business object --> HUB + LINK + SAT
|
+--> relationship lifecycle -------> LINK + Effectivity SAT
|
+--> genuinely concurrent values
|
v
Is there a stable CDK?
/ \
yes no
| |
MAS investigate source
semantics carefully
After diverting apparent MAS into proper grain/relationship type, normally surviving MAS are much less than initially expected/felt.
Hope this helps.
Following your schema, the MAS candidates look like genuinely concurrent values with no stable CDK. The pattern is revolving in many places of the SOR, due to poorly modelled data structures.
For example, there is a business object “BOOKING” and the travellers have no stable ID but are just listed by position (enumerated from 1 .. n) in the context of the booking. During the lifecycle of the booking, the position of the travellers may change, there is no way to track them by other attributes like name, date of birth etc because name changes happen quite often.
Another example is that for a booking the costs of the consumed services are represented as a list of cost records, again with no stable CDK but just listed with a position 1 .. n. These positions change as well as the content of the booking may change over time.
One might argue that after departure the booking becomes immutable but that isn’t the case. There might be complaints from the travellers that may eventually result in cost records added to the list in a random way (they are not sequentially appended!).
The costs have a booking to child records ratio of 1 :30 which looks insane to me.
I would discard original position (or save it for audit), but main thing is the hashdiff, to reduce SAT rows explosion, so either use multiple rows or 1 single row (1 variant holding collection of values).
For the sub_sqn:
SELECT
booking_hk,
dv_ldts,
ROW_NUMBER() OVER (
PARTITION BY booking_hk, dv_ldts
ORDER BY row_hash
) AS sub_sqn,
cost_type,
amount,
currency
FROM changed_collection;
For the hashdiff, and considering multirow insert of individual collection rows and you in Snowflake use hash_agg(only attributes without dv columns or original position) as it handles well 2 collections with same values but different order returning same hash, and duplicates also producing different hash:
SELECT
booking_id,
snapshot_ts,
HASH_AGG(
cost_type,
amount,
currency
) AS collection_hash
FROM stage_booking_cost
GROUP BY
booking_id,
snapshot_ts;
Regarding source data collections, I’m guessing you probably have both scenarios:
- always full snapshot of values (better).
- or only change row (like old position 7 now have different attributes
).
Hope this help (specially if you with Snowflake to benefit of the HASH_AGG() that does all needed for the hasdiff.
Again, thank you for your valuable advice. I’ll explore dropping the original position.
We’re using Postgres, not Snowflake.