[BUG] Satellite inserting duplicates when granularity of source table and target satellite differ

[felix-pagel]

Describe the bug

We are currently facing the issue that in satellites where we deviate from a direct 1:1 mapping of source table columns to satellite columns, the sat_v0 macro will keep inserting on incremental loads the same records with every load even if there is no change in the hashdiff.

Environment

• dbt version: core 1.11.2
• datavault4dbt version: 1.12.2
• Database/Platform: Snowflake

To Reproduce

Example source table:

product_hk	payload_col_sat1	payload_col_sat2	ldts
A	Value B	Value C	22-02-26 09:00:14:00
A	Value B	Value F	22-02-26 09:00:14:00
A	Value B	Value G	22-02-26 09:00:14:00

This is a simplified example input where the granularity of the source table doesn't match the granularity of the target satellite (in this case the problem is in sat1). The HK and HD and ldts for all three records in this example will be the same on every load.

The behavior happens because the deduplication step in the macro has the logic for incremental loads to assign a row number that will later be used in the WHERE NOT EXISTS clause to only compare the latest record per HK of the batch with the target satellite.

The problem is that this row number is not aligned with the ordering of the deduplication function since on similar ldts the ordering of the two window functions will be non-deterministic.

SELECT
{{ parent_hashkey }},
{{ ns.hdiff_alias }},
{{- "\n\n    " ~ datavault4dbt.print_list(datavault4dbt.escape_column_names(source_cols)) if source_cols else " *" }}
{% if is_incremental() -%}
, ROW_NUMBER() OVER(PARTITION BY {{ parent_hashkey }} ORDER BY {{ src_ldts }}) as rn
{%- endif %}
FROM source_data
QUALIFY
    CASE
        WHEN {{ ns.hdiff_alias }} = LAG({{ ns.hdiff_alias }}) OVER(PARTITION BY {{ parent_hashkey|lower }} ORDER BY {{ src_ldts }}) THEN FALSE
        ELSE TRUE
    END

The result is that whenever there are complete duplicates (HK, HD, LDTS all same, as is the case when reading records from a table with a higher granularity than the attributes for the satellite) row number 1 will not match with the QUALIFY=TRUE, and therefore records will be inserted again.

This could be easily fixed by breaking the CTE into two parts, the first one assigning the row number and then the second one deduplicating with the QUALIFY statement but ordering on the row number from the first CTE. This way both steps would be aligned and would not end up inserting the same records again.

Expected behavior

The macro should be able to handle cases when the source table has a higher granularity than the attributes tracked in the satellite and therefore will be presented with duplicates in each load. It shouldn't insert the same values with each when there is no change in the HD.

[molschimke]

When dealing with multi-active data, the recommended practice is not to deal with grain shifts or remove redundant data. In the simplest case, no sat split is performed, and all data are loaded into a multi-active sat (MAS) that matches the source's grain (group-wise). The redundancy is not nice, I agree, but it has a reason: what if the source data differs and there are unforeseen changes in those attributes?

This even holds true when satellites are split (e.g., for security or performance reasons). Because the source data is multi-active, the target should be multi-active. Any grain shift or selection of the record to be used for information delivery can easily be done using bridge or PIT tables (extended by the multi-active attribute for the sat reference to select only one record from the group).

So my expectation for this scenario would be a sequence (multi-active attribute) which is unique for all three rows, and a target satellite with 3 rows, no matter if you split the satellite or not.

[felix-pagel]

Thanks for the quick response, much appreciated.
The scenario was a bit simplified, in practice we often face source system tables with hundreds of column so satellite splits are not that uncommon. If we isolate some slowly changing columns out of this we will end up with satellites that replicate the same payload thousands of times, without any actual change in the payload.
Even with a multi-active attribute this doesn't seem like a good design, what's even the purpose of defining a payload in this case if the change is not properly tracked.

I understand the concern about "what if the source data differs and there are unforeseen changes in those attributes", but this should only be an issue if the source table attribute happens to be multi-active. But that's why we start off by modelling the table and determine what is multi-active and what's not. Simply declaring everything multi-active seems a bit like a brute force approach.

The fix would be very straight forward, the macro already has everything in place to handle the scenario, the only problem is that the ordering of the row number is out of sync with the ordering of the qualify (see screenshot) when the ldts is the same. If these two are aligned, f.ex. by qualifying in a separate CTE ordering on the row number then it could handle this basic scenario.

[molschimke]

Would compression on the table/page level not solve the problem? Redundant / unchanged data is easy to compress, so while you have a high number of records in the satellite, these redundant records lead to low footprint of the satellite on disk. My design choices are often driven by lowest risk and, at least for the Raw Data Vault, to "model the source data as its being used by the business". Since your source data is multi-active (on all attributes) in the source, my RDV would be closely modeled after it. Redundancy in the source often leads to redundancy in the RDV for these two reasons. Btw, our team at Scalefree is also looking into your issue, I'm joining in more from a conceptual perspective.

…

On Thu, Mar 5, 2026 at 6:50 PM felix-pagel ***@***.***> wrote: *felix-pagel* left a comment (ScalefreeCOM/datavault4dbt#427) <#427 (comment)> Thanks for the quick response, much appreciated. The scenario was a bit simplified, in practice we often face source system tables with hundreds of column so satellite splits are not that uncommon. If we isolate some slowly changing columns out of this we will end up with satellites that replicate the same payload thousands of times, without any actual change in the payload. Even with a multi-active attribute this doesn't seem like a good design, what's even the purpose of defining a payload in this case if the change is not properly tracked. I understand the concern about "what if the source data differs and there are unforeseen changes in those attributes", but this should only be an issue if the source table attribute happens to be multi-active. But that's why we start off by modelling the table and determine what is multi-active and what's not. Simply declaring everything multi-active seems a bit like a brute force approach. The fix would be very straight forward, the macro already has everything in place to handle the scenario, the only problem is that the ordering of the row number is out of sync with the ordering of the qualify (see screenshot) when the ldts is the same. If these two are aligned, f.ex. by qualifying in a separate CTE ordering on the row number then it could handle this basic scenario. — Reply to this email directly, view it on GitHub <#427 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AGEERE34GDTM6QDVUK4BD3L4PG45ZAVCNFSM6AAAAACWG3L5FWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHM2DAMBWGY3TKNBYGE> . You are receiving this because you commented.Message ID: ***@***.***>

-- Michael Olschimke Chief Executive Officer (CEO) Diplom-Informatiker (FH), M. Sc., LB Scalefree International GmbH Schützenallee 3, 30519 Hannover, Germany Phone: +49 (511) 879 89341 Fax: +49 (511) 879 89349 ***@***.*** www.scalefree.com Company Headquarters: Hannover, Germany Registration: Amtsgericht Hannover HRB 213578 Geschäftsführung: Michael Olschimke, Christof Wenzeritt CEO: Michael Olschimke CEO: Christof Wenzeritt <https://scalefr.ee/onlinetraining>

[felix-pagel]

Yes thats true, technically theres a low footprint on disk due to compression, but if the redundancy is not necessary we would still prefer to avoid it in the first place. If we know the attribute is not multi-active, it just adds additional complexity and makes the table less readable.

In my example above, the multi-activity would be only on column payload_col_sat2, or do you consider any attribute multi-active that is not fully normalised?
As you said, we try to model the source data as it is being used by the business, so if I ask the business if one product_hk can have several payload_col_sat1 at the same time and they tell me no, then I wouldn't consider the attribute multi-active.