# Effectivity Satellite from Intersection Table with Payload **URL:** \ **Category:** AutomateDV\ **Tags:** link-satellite, automate-dv\ **Created:** [August 29, 2025, 4:37pm UTC](https://forum.data-community.org/t/effectivity-satellite-from-intersection-table-with-payload/1942 "2025-08-29T16:37:11Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![darkchanter](https://avatars.discourse-cdn.com/v4/letter/d/53a042/32.png) [@darkchanter](https://forum.data-community.org/u/darkchanter)\ **Post date:** [August 29, 2025, 4:37pm UTC](https://forum.data-community.org/t/effectivity-satellite-from-intersection-table-with-payload/1942/1 "2025-08-29T16:37:11Z")
Hi, I recently started to learn AutomateDV. It works quite well, but I’m struggeling a little with specific link-satellites. Consider a simple m:m relation coming from an intersection table: ```auto create table "source".cse_customer_employee ( cse_rowid serial primary key, cse_created_at timestamp, usr_created_by int, cse_modified_at timestamp, usr_modified_by int, cse_deleted_at timestamp, usr_deleted_by int, -- payload cst_customer int, emp_employee int, cod_role int, cse_effective_from date, cse_effective_to date, cse_primary boolean ); ``` In DataVault, I would model this as h\_customer and h\_employee. These hubs are connected via l\_customer\_employee with its effectivity satellite s\_customer\_entitiy. - there’s a technical key (cse\_rowid) which I’m not going to use, since I think the “datavault-way” of doing it is to integrate using the business keys (see below) - business keys (PK) are hk\_customer, hk\_employee, cod\_role and cse\_effective\_from - i want to include/track the payload attributes as well First Question: Is it okay to put the payload (without the FKs) into s\_customer\_employee or should it go to a “normal” satellite instead? As far I know, a satellite hanging off of a link is considered an effectivity satellite, yet it’s still a satellite 😉 In AutomateDV I picked the eff\_sat macro. Basically, it works as expcected, but it won’t detect changes in payload (non-key) attributes. ```auto {{ config(materialized='incremental') }} {%- set source_model = "v_cse_customer_employee" -%} {%- set src_pk = "HK_CUSTOMER_EMPLOYEE" -%} {%- set src_dfk = "HK_CUSTOMER" -%} {%- set src_sfk = ["HK_EMPLOYEE", "COD_ROLE", "CSE_EFFECTIVE_FROM"] -%} {%- set src_start_date = "START_DT" -%} {%- set src_end_date = "END_DT" -%} {%- set src_extra_columns = ["cse_rowid", "cse_created_at", "usr_created_by", "cse_modified_at", "usr_modified_by", "cse_deleted_at", "usr_deleted_by", "cse_effective_to", "cse_primary"] -%} {%- set src_eff = "EFFECTIVE_FROM" -%} {%- set src_ldts = "load_ts" -%} {%- set src_source = "RECORD_SOURCE" -%} -- src_extra_columns added {{ automate_dv.eff_sat(src_pk=src_pk, src_dfk=src_dfk, src_sfk=src_sfk, src_start_date=src_start_date, src_end_date=src_end_date, src_extra_columns=src_extra_columns, src_eff=src_eff, src_ldts=src_ldts, src_source=src_source, source_model=source_model) }} ``` Second question: Should I use the “normal” satellite template instead or should I take an all different approach? (I don’t think a hub “customer\_employee\_assigment is a good idea) Since I included all the necessary attributes into src\_dfk and src\_sfk, changes in the link’s combination are detected and loaded. However, it won’t detect changes in any other fields, especially cse\_effective\_to. Usually, the source system would update cse\_effective\_from and cse\_effective\_to (eg. from 2099 to something else) and set cse\_modified\* - so the change is detected and loaded. However it can also just update cse\_effective\_to (and modified) or any other payload attribute (such as the cse\_primary flag) - these changes are not detected. As to my background: I have theoretical understanding of the DV2 modelling, since I attended that “Boot Camp” with Michael Olschimke, but that’s like 10 years since. I got back to their book (the one with the racing car) every here and then to fresh-up my knowledge. Just this week, I received Patrick Cuba’s book (data vault guru) from Amazon. I haven’t built a real Data Vault yet. ---
**Author:** ![joe.barter](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/joe.barter/32/317_2.png) [@joe.barter](https://forum.data-community.org/u/joe.barter)\ **Post date:** [December 2, 2025, 11:03am UTC](https://forum.data-community.org/t/effectivity-satellite-from-intersection-table-with-payload/1942/2 "2025-12-02T11:03:36Z")
Hi @darkchanter ! Thanks for laying out the example so clearly. Intersection tables that carry both effectivity dates and relationship attributes always expose the strange corners of DV modelling, so you’re in familiar territory. Bit of a late reply on my part, but this is a good modelling question and still very common, so here’s how I’d think about it. Hopefully it is still of use to you! From what I can see, you have (and correct me if I’m wrong): - `H_CUSTOMER` - `H_EMPLOYEE` - `L_CUSTOMER_EMPLOYEE` representing “customer–employee–role at a point in time”. - A relationship that needs both effective from/to and some payload fields You’ve used `eff_sat`, but payload-only changes are not producing new rows. So to hopefully answer both of your questions! ## 1. The modelling itself is fine If the attributes describe the relationship, they belong on a satellite attached to the link. Things like role, primary flag, and effective window all belong at that grain. So nothing wrong at the logical level. To be clear, in my opinion this is absolutely a Link Satellite and it doesn’t need to be a normal Hub hanging Satellite just because it has a payload. The issue is how AutomateDV handles effectivity. ## 2. Why `eff_sat` ignores payload changes The `eff_sat` only treats the following as change drivers: - The link keys - The effectivity start and end dates Any extra columns passed as `src_extra_columns` are not part of the hashdiff. They are carried through but do not trigger history. So if only a payload field changes, the hashdiff is unchanged and AutomateDV will not create a new row. This is intentional. The `eff_sat` is designed to manage time windows, not payload history. From this I see two obvious directions: ### Option A: keep `eff_sat` for dates only and split the payload Use: - One satellite for effectivity, loaded with `eff_sat` - A second satellite for payload, loaded with the standard `sat` macro The payload satellite includes all descriptive fields in its hashdiff, so any change triggers a new row. This keeps responsibilities clear. ### Option B: skip `eff_sat` and use a normal SAT Load a single SAT where the hashdiff includes both the dates and the payload. Any change then creates a new record. You lose the specialised effectivity behaviour from `eff_sat`, but history becomes more predictable because it behaves like a standard SAT. ## Where my head is at on this Most of the time, Option A is the simplest and most predictable: - Let `eff_sat` manage the validity window - Use a separate SAT for the relationship attributes - Keep both satellites at the same grain under the same link This avoids fighting the macro and keeps lineage and history clean. Hope this provides some direction! Joe