Effective satellite for link of json source

Data Vault 2.0
1

Hello everyone,

Need some suggestion on below scenario,
We have built a LNK__SUPPLIER__COMPANY from a source table in which we have json columns. The company details are present inside one of a json column. So we have flatten the column and pulled out the company id from json column.
Here for one supplier id, we have multiple company_id’s and load date is same for all these records as it is flattened from single row from source.
example:

image
image1273×138 9.37 KB

LNK don’t have any issue as SUPPLIER, COMPANY combination for hash key.

We try to build Effectivity satellite for LNK__SUPPLIER__COMPANY, where we had driving key as supplier_id and we ended up in primary key error as we have two company_id, same load_date for one supplier_id.

Do we have any option like multi active effective satellite to handle this? any suggestion would be appreciated.

Note: Building effectivity satellite to capture latest updated company_id for supplier_id.

Thanks

2

Hello,

I’m not sure how AutomateDV have done this, but an Effectivity Sat should handle 1:M relationships, scenario 3 in this blog

A link-sat with a finer grain than the link-hash key should then be a link-sat with a dependent-child key

As for the payload, if you can schematise before ingestion this will be cheaper and faster for processing semi-structured data, (from the screenshot this looks like Snowflake) Schema detection and evolution for Kafka connector with Snowpipe Streaming | Snowflake Documentation