[FEATURE] Use a business value for deduplication in sattelites macro

[MajorDaxx]

What additional value does this feature bring to the project?

When source data arrives with multiple rows per ldts, the current macro deduplicates incorrectly. Introducing a configurable “deduplication column” (e.g., business_event_time) would simplify user code and produce correct Satellite records without complex custom staging logic.

Is your feature request related to a problem? Please describe.

In the following I am describing a mock of my source data I am trying to load.
Each ldts is loaded seperatly.

The source data

row_num	parent_hk	ldts	business_event_time	business_hd (Exclusiv business_event_time )
1	A	a_1	1	abc
2	A	a_1	2	hxw
3	A	a_1	3	abc
4	A	a_2	4	abc
5	A	a_2	5	abc
6	A	a_3	6	abc
7	A	a_3	7	hxw
8	A	a_3	8	dfg

Used with the given macro the result would like somthing like this. But that is not correct

row_num	parent_hk	ldts	business_event_time	business_hd (Exclusiv business_event_time )
1	A	a_1	1	abc
2	A	a_1	2	hxw
4	A	a_2	4	abc
6	A	a_3	6	abc
7	A	a_3	7	hxw
8	A	a_3	8	dfg

The correct expected Sattelite would be like this.

row_num	parent_hk	ldts	business_event_time	business_hd (Exclusiv business_event_time )
1	A	a_1	1	abc
2	A	a_1	2	hxw
3	A	a_1	3	abc
7	A	a_3	7	hxw
8	A	a_3	8	dfg

Describe the solution you'd like

Its probably a smaller case but would signifanctly reduce code complexity for this type of source data. And its only a minor change in the code base.
By simply introducing a deduplication column and use it instead of the ldts the wanted behaviour could be archieved.
Basically it needs to be determined if this is "the correct way" from a methodology point of view

Describe alternatives you've considered

Alternativly the same thing could be placed in stanging or in a custome layer inbetween staging and raw_vault.

Additional context
The line in source can be found here

[lorogno]

Have a similar situation were we have Sensor-data which is loaded Batch-wise in the Plattform. So many records will have identical HK + LDTS.
Actually we are using MAS to include measurement-timestamp to differentiate. But thats not the original purpose of MAS from my perspective. So agree with your Feature Request

[molschimke]

In a data-driven Data Vault approach, the application of any business rule is discouraged. The same is true for applying a business timeline or business value for deduplication purposes.

I would recommend to capture the data as-is and only remove records later (in the Business Vault) if desired.

To capture the data as shown by @MajorDaxx , there are two general options (in fact only one):

1. use a multi-active satellite if all rows are active at the same time (this seems not to be the case here)
2. use a standard satellite with a modified ldts (load date time stamp).

Option 1: Multi-Active Satellite

Again, my understanding for the original question is that this is not the right option, but I would like to add it here as similar cases might occur in your data set.

Solution would look like:

row_num	parent_hk	ldts	business_event_time	hd
1	A	a_1	1	abc
2	A	a_1	2	abc
3	A	a_1	3	abc
4	A	a_2	4	def
5	A	a_2	5	def
6	A	a_3	6	aaa
7	A	a_3	7	aaa
8	A	a_3	8	aaa

PK of satellite is row_num, parent_hk and ldts. row_num is what we call the "multi-active attribute".

The hash diff is calculated group-wise based on the descriptive data. The descriptive data is just the business_event_time, which btw is not used for any calculations in the RDV, we just capture any timelines as descriptive data.

Hash diff is calculated from the concatentated descriptive data: 1 || 2 || 3 (simpflied, skipping any delimiter, which should be in place as well).

Group 2 (ldts a_2) fully replaces group 1 (a_1). Group 3 (a_3) fully replaces group 2 (a_2).

Option 2: Using a Modified Satellite LDTS

This option is used when only one record is active at a time, which appears to be the case here.

Order records by a technical field, such as subsequence in stage, CDC timestamp or sequence, if possible. In this case, I assume that row_num is the CDC sequence.

PK of satellite is parent_hk and ldts:

parent_hk	ldts	business_event_time	hd
A	a_1+1ns	1	abc
A	a_1+2ns	2	def
A	a_1+3ns	3	123
A	a_2+1ns	4	a23
A	a_2+2ns	5	aa3
A	a_3+1ns	6	bb1
A	a_3+2ns	7	b43
A	a_3+3ns	8	af4

I'm using the ldts of the incoming CDC package (or intraday changes) as the base and add 1 ns per record. therefore all records are in the same technical order of arrival. Remember, the ldts is the technical historization and should not be used for diagrams or calculations. Instead, I'm using the business_event_time.

The hash diff hd is different with each row as it represents a change.

One can argue of the first row should have a ldts of x+1ns or x+0ns (both work for me).

As a side-note: do not use business time line when loading the RDV satellite. Application of multi-temporal timelines (such as this) is a concept of information delivery and should be performed as late as possible for higher flexibility and simplicity.

[molschimke]

Have a similar situation were we have Sensor-data which is loaded Batch-wise in the Plattform. So many records will have identical HK + LDTS. Actually we are using MAS to include measurement-timestamp to differentiate. But thats not the original purpose of MAS from my perspective. So agree with your Feature Request

If data arrives in batches, we assign the same ldts. Use a non-historized link for capturing such sensor data. You can add a non-historized satellite for capturing complex data if required. Non-historized sats only have the HK in the PK, not the ldts, at least in the logical model.

[tkirschke]

Hi @MajorDaxx & @lorogno and thanks for reaching out!

I agree here with @molschimke . IMHO a modified ldts would be the best approach here.

Depending on the database you are using, you would exchange your current ldts with something like this:

SELECT 
    (dateadd(nanosecond,
        row_number() over (partition by <hashkey>, <load_date_column> order by <event_time_col>),
       <load_date_column>)
    ) as ldts

If your database supports this as a single-line SQL statement, you can directly implement it with datavault4dbt, by changing the value of the ldts parameter inside your stage model and insert this SQL transformation:

stage_model.sql

{%- set yaml_metadata -%}
source_model: 'source_account'
ldts: 'dateadd(nanosecond, row_number() over (partition by parent_hk, ldts order by business_event_time), ldts)'
rsrc: 'edwRecordSource'
hashed_columns: 
   [...]
{%- endset -%}

{{ datavault4dbt.stage(yaml_metadata=yaml_metadata) }}

This will ensure leveraging the business_event_time for a proper ldts.

If you need more complex SQL not implementable in a single line, you can just create a new layer inside dbt "before" the data-vault-stage, like a "pre-stage" layer, and calculate this manipulated ldts there. Your data-vault-stage would then just point to this pre-stage model.

Let me know if this works for you!

[MajorDaxx]

Hey @molschimke @tkirschke ;

thanks a lot for your reply. I absolutly agree with the point: "In a data-driven Data Vault approach, the application of any business rule is discouraged."

I have two questions. First could a solution be to integrate the business_event_time in the hd? Then each row would have a unique hd. And I do not have to tamper ldts.

Second there are really a lot of records with equal payload with different business timestamps. This records do not add a lot if any semantic value but take up a lot of space.
Is it common practice to have some form of houskeeping and deleting records from the raw vault that clearly have no additional business value?

Best Regards and Thank you in advanced

[kafkaguru]

Just wanted to chime in here and say that due to some oddness with our ingestion tooling we also see multiple entires with the same load timestamp. In our case, we are on Databricks and using _commit_timestamp as the incrementing timestamp. We do have another timestamp column that is different and also incrementing but does not line up with databricks' CDF.

So, we ended up with this little snippet for our staging model that we create from our source tables before we load into our data vault tables (we require staging tables because we require the change data feed from our source tables):

        , coalesce(_change_type = 'delete', false) AS {{ adapter.quote('is_deleted_cdf') }}
        , _commit_version AS {{ adapter.quote('commit_version_cdf') }}
        , _commit_timestamp AS {{ adapter.quote('commit_timestamp_cdf') }}
        , ROW_NUMBER() OVER (
            PARTITION BY {{ primary_keys }}, {{ adapter.quote('_commit_version') }}
            ORDER BY {{ timestamp_column }} ASC
        ) -1 AS jitter
        , coalesce(TIMESTAMPADD(
            MILLISECOND
            , jitter
            , _commit_timestamp
        ), {{ timestamp_column }}) AS load_timestamp

• We capture hard deletes as soft deletes
• We add a 0-based jitter using ROW_NUMBER() over the PK + _commit_version
• Then we do a timestampadd to the _commit_timestamp as the load_timestamp to use downstream in our sat loading
• We keep all original columns/timestamps but create a new load_timestamp just for use downstream

You technically don't need the PK or even the _commit_version, just an order by. However, it contains the jitter to the smallest subset of data possible.

Hope this helps someone figure out their own implementation of the

[github-actions]

This issue is stale because it has been open for 90 days with no activity. If you would like the issue to remain open, please remove the stale label or comment on the issue. Otherwise it will be closed in 14 days