Skip to content
Contact us Go to Customer Portal
  • There are no suggestions because the search field is empty.

Deduplication Method (Dataflow)

Applies to
Platform: Microsoft SQL Server · Snowflake · Snowflake dbt · Databricks · Databricks dbt · Fabric Lakehouse · Fabric Warehouse · Oracle

Deduplication Method (DeduplicationMethod) removes duplicate source rows of a dataflow before the loader applies them. Use it when a source can deliver the same key more than once in one load.

Where to set it

Set the property when you edit a dataflow. Each dataflow of a model object has its own value. It is offered on Stage, Store Entity, Store Composite, Entity, Fact, Core Composite, Mart Fact, Mart Composite, Mart Out and all Raw Vault and Business Vault types except PIT, Business PIT, Current View, History View, Business Current View, Business History View and Hierarchy Flatten. Mart Dimension and Mart Hierarchy Flatten do not offer it. On a Stage, validation shows a warning for Distinct and Partition, see No deduplication for Stage objects (Business Rule).

Values and default

  • None (default): no deduplication.
  • Distinct: the deduplication view selects DISTINCT rows over all mapped terms.
  • Partition: the deduplication view numbers the rows of each partition with ROW_NUMBER() and keeps the first row.

The Microsoft SQL Server - Stage OpenQuery generator offers None and Distinct only.

How Partition builds the partition

  • Objects with business keys partition by the business keys.
  • Satellites and Link Satellites partition by their foreign keys, which carry the business keys of the Hub or Link.
  • Objects without business keys partition by all non-default terms. Validation shows a warning for them, see Partition deduplication without business keys (Business Rule).

The rows of a partition are ordered by Sort Order within Partition. Without it, they are ordered by the partition terms, so any of the duplicates can be kept.

Example

A Store Entity with the business key ARTICLE_NUMBER, Partition and Sort Order within Partition OFFER_DATE ascending. The deduplication view on Microsoft SQL Server, shortened:

SELECT [BG_Partition].[ARTICLE_NUMBER], ...
FROM (
    SELECT [BG_Source].[ARTICLE_NUMBER], ...
        ,ROW_NUMBER() OVER (PARTITION BY [BG_Source].[ARTICLE_NUMBER]
            ORDER BY [BG_Source].[OFFER_DATE] ASC)
            AS [BG_DeduplicationPartitionOrder]
    FROM [DS_SE_deduplication_partition_se_Source] AS [BG_Source]
) AS [BG_Partition]
WHERE [BG_Partition].[BG_DeduplicationPartitionOrder] = 1

For each article, the row with the earliest offer date is loaded.

Related articles