Merge Duplicates: behavior, errors, and troubleshooting
The Merge Duplicates Flow Step collapses records that share the same values in the selected Fields into a single row. This article covers what happens when the data or the configuration does not cooperate. For what the step does and how to configure it, see Transform Flow Steps.
Start with the engine
Several scenarios depend on the Deduplication Engine chosen in the step:
- PostgreSQL groups the records in the database. Nothing is staged in Elasticsearch.
- Stream merges consecutive records while their merge values stay the same, so it depends on the Query already returning them sorted by the merge Fields.
- Elasticsearch stages every record in a temporary Elasticsearch index, reads them back sorted by the merge Fields, and merges consecutive records. The staging and sorting scenarios further down apply only to this engine.
Scenarios on every engine
Duplicates remain, and every row carries an error
What happened: the step's saved configuration failed validation, so the run skipped the step rather than fail. Nothing was merged.
What you will see:
- Every duplicate is still in the output.
- Every row carries an error naming the step:
(Merge Duplicates): step omitted: its settings are invalid (...). Open the Flow Step and save it again.
What you can do:
- Open the Merge Duplicates Flow Step, check each section, and save it. Saving writes a configuration the current version accepts.
Duplicates remain on the Stream engine
What happened: the Stream engine only merges records that arrive next to each other with the same merge values. Duplicates that arrived apart were each treated as a new group.
What you will see:
- The same merge values appear on more than one row, and the output is not sorted by the merge Fields.
What you can do:
- Add an Order By on every Field you merge on to the Query, or switch the engine to PostgreSQL or Elasticsearch, which sort the records for you.
A merged column comes back empty
What happened: the Keep values as option chosen for a Field cannot be applied to the values that Field holds. The sum and average need values that read as numbers; the minimum and maximum need values that can be ordered, which rules out true and false values and objects.
The editor only offers each option to the Field types that support it, so this usually means the Field's type changed after the option was saved, or the step was imported from another system.
What you will see:
- The column is empty for every merged row, while every other Field merges normally.
- Each of those rows carries an error naming the Field:
(Merge Duplicates): Keep values as '...' does not work for the values of '...', or, for a sum or average,(Merge Duplicates): Some values of '...' are not numbers that can be used.
What you can do:
- Open the Merge Duplicates Flow Step. An option that can never produce a value for the Field's type is reset to All values as an array when the editor opens, so the grid shows what the step will actually do: that is any of the four on a true/false Field, the sum or average on a date or time Field, and the minimum or maximum on an object Field.
- Pick an option the Field supports, or add a Flow Step before Merge Duplicates that converts the values into a type the option can use.
A sum or average is lower than expected
What happened: some of the values are not plain numbers, so the sum or average left them out. Formatted amounts such as 1,234 or $20 are common in text Fields from MultiValue and CSV sources, and a number too large to represent (such as 1e999) is left out too.
What you will see:
- The merged value covers only the values that are plain numbers.
- The row carries an error naming the Field:
(Merge Duplicates): Some values of '...' are not numbers that can be used (for example '1,234', '$20' or '1e999'), so they were left out of its merged value.
What you can do:
- Add a Flow Step before Merge Duplicates (such as a Power Script) that strips the formatting, for example removing thousands separators and currency symbols, so every value reads as a number.
A sum comes back empty with a "too large" error
What happened: the values add up to more than the largest number a result can hold.
What you will see:
- The merged value is empty, and the row carries the error
(Merge Duplicates): The merged value of '...' is too large to represent, so it is empty.
What you can do:
- Check the Field for values that are far larger than expected, which usually means a unit or formatting problem upstream.
The merged rows are in an unexpected order
What happened: merged rows come out ordered by the Fields you merge on, and only an Order By that starts with one of those Fields changes that.
What you can do:
- Put the merge Field first in the Query's Order By, or sort the results by its column header. See Sort order and merged rows.
Scenarios on the Elasticsearch engine
No sortable data found
What happened: none of the selected Fields contain values that can be sorted (all empty, null, or invalid) or merged upon.
What you will see:
- All records are merged into a single row, in the order they were received.
- An error is included in that row.
What you can do:
- Review the step. Clear the selected Fields with the X and confirm you chose the correct ones.
- If your data may contain blanks or invalid values, add a Flow Step (such as a Power Script) before Merge Duplicates to validate Fields, apply default values, or fix formatting, so the selected Fields always hold something sortable.
Some Fields cannot be sorted
What happened: some of the selected Fields contain no valid sortable values anywhere in the results.
What you will see:
- Processing continues, and records are sorted and merged by the remaining valid Fields.
- Fields without valid data are skipped silently. An error appears only when all selected Fields are unusable (the scenario above).
What you can do:
- If the empty Fields are unexpected, check their Datasources or preceding Flow Steps. Some rows may also carry error messages unrelated to Merge Duplicates.
- If needed, add a Flow Step (such as a Power Script) before Merge Duplicates to validate the data or assign default values so the Fields become sortable.
- If the empty Fields are expected, no action is required.
Staging data processing errors
What happened: an error occurred while staging the data in the temporary Elasticsearch index used for sorting.
Possible causes:
- Issues with your Elasticsearch cluster (connectivity, resource limits, or other failures).
- Invalid data in at least one row for at least one selected Field, causing Elasticsearch to reject it.
What you will see:
- Processing stops immediately.
- A single error record: the first record from your original data, with error details attached. All other upstream data is discarded.
What you can do:
- Check connectivity to your Elasticsearch cluster and verify it is healthy and has sufficient resources.
- Add a Flow Step (such as a Power Script) before Merge Duplicates to validate or sanitize the data and assign default values.
Sorting data processing errors
What happened: an error occurred while reading the sorted, staged data back from the temporary index.
Possible causes:
- Issues with your Elasticsearch cluster (connectivity, resources, or other failures).
- Data-specific exceptions when searching or sorting, for example a selected Field holding unexpected values, types, or structures. This often happens when a Field includes complex objects or binary data instead of simple values.
What you will see:
- Incomplete output: only the records processed before the error, followed by an error record with details about the issue.
- Processing stops.
What you can do:
- Check connectivity to your Elasticsearch cluster and verify it is healthy and has sufficient resources.
- Add a Flow Step (such as a Power Script) before Merge Duplicates to validate or sanitize the data and assign default values.