> ## Documentation Index
> Fetch the complete documentation index at: https://www.integrate.io/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# ETL: Dedupe Transformation

> Keep one row per key, such as the latest record for each customer, with the Dedupe transformation in your Integrate.io ETL pipeline.

## Overview

The Dedupe transformation keeps **one row per key**. You choose the key fields (for example `student_id`), the field that decides which row wins (for example `updated_at`), and whether to keep the first or the last row. By default, every field in an output row comes from the same input row, so values are never mixed across duplicates. With **Output: Merged row per key**, empty fields are filled from the other duplicates instead (see [Merge duplicate records](#merge-duplicate-records)).

Use Dedupe when your input has several versions of the same record and you want only the latest (or earliest) one. To remove rows that are identical in every field, use [Distinct](https://www.integrate.io/docs/etl/using-components-distinct-transformation.md) instead.

> [!NOTE]
>
> Dedupe is a [Limit](https://www.integrate.io/docs/etl/using-components-limit-transformation.md) set to 1 row per key. A package that uses it shows a Limit component in the API and in the package JSON, with `"preset": "dedupe"`.

## Keep the latest row per key

**Step 1**

In the package designer, add a component and search for **Dedupe** (searching for "duplicate" or "unique" also finds it). Connect it after the component that produces the duplicates.

**Step 2**

Under **Key fields**, add the fields that identify a record, for example `student_id`. Add more than one field when the key is a combination, for example `student_id` and `school`.

**Step 3**

Under **Order by**, keep **Order by fields** selected and add the field that tells the versions apart, for example `updated_at`. Add more fields after it to break ties when two rows have the same value, for example `id`.

**Step 4**

Open **Advanced settings**. Under **Keep**, select **Last** to keep the row with the latest or highest Order by value, or **First** to keep the earliest or lowest. Last is the default.

**Step 5**

Under **Rows with empty key**, also in Advanced settings, choose what happens to rows with no key (see below), then save.

## Configuration

**Key fields** and **Order by** are always shown. The other settings are under **Advanced settings**, which starts collapsed; while it is closed, its header names each setting changed from its default, and marks one that needs attention. Each choice is a group of buttons; hover over a button for a short description.

| Field | Description |
| :- | :- |
| Key fields | The output has one row for each distinct combination of these fields. Required. |
| Order by | **Order by fields** (default): these fields decide which row is kept for each key, and later fields break ties between rows with the same value in earlier fields. **No order**: any one row per key is kept, which is arbitrary and can change between runs. |
| Keep | **Last** sorts the Order by fields in descending order and keeps the top row. **First** sorts them in ascending order. Applies to every Order by field. Shown only with **Order by fields**: with **No order** there is nothing to keep first or last. A Dedupe whose Order by fields sort in different directions (possible only through the API) shows each field's direction instead. |
| Prefer the most complete row | Off by default. When on, the row with the most **Fields that count** filled is kept, and Order by only decides between equally complete rows. |
| Output | **One row per key** (default) keeps the chosen row unchanged. **Merged row per key** fills that row's empty fields from the other duplicates; see [Merge duplicate records](#merge-duplicate-records). |
| Rows with empty key | What to do with rows whose key is empty. Defaults to **Keep all of them**. |

### Rows with empty key

A key is **empty** when any key field is null, or is blank after trimming spaces.

| Option | What happens to rows with an empty key |
| :- | :- |
| Keep all of them (default) | Every such row passes through unchanged. Nothing is deduplicated among them. |
| Drop them | They are removed from the output. |
| Handle them like any other key | They are deduplicated like any other key value. Different empty values (null, an empty string, a string of spaces) count as different keys, so one row is kept for each. |

### Keep the most complete row

Turn on **Prefer the most complete row** to keep the copy of a record that has the most information, rather than the latest one. **Fields that count** starts with every field except the key fields; remove the ones that should not matter.

A field counts as **empty** when it is null, when it is text that is blank after trimming spaces, or when it is an empty JSON object or array (`{}` or `[]`). Any other value counts as filled, including `0` and `false`. The rule is the same for every field, whatever its type and wherever it comes from, including fields computed in a Select or Window.

Each row's score is the number of filled fields. The highest score wins, whatever **Keep** is set to. Rows with the same score are then ordered by **Order by** and **Keep** as usual. Every field of the kept row still comes from that one row.

For example, with **Fields that count** set to `email`, `phone` and `tags`, and **Keep: Last** by `updated_at`:

| student\_id | updated\_at | email | phone | tags | Filled |
| :- | :- | :- | :- | :- | :- |
| S1 | 2026-03-01 | [new@example.com](mailto:new@example.com) | (null) | (empty) | 1 |
| S1 | 2026-01-01 | [old@example.com](mailto:old@example.com) | 555 | vip | **3** (kept) |
| S1 | 2026-02-01 | (blank) | (null) | (null) | 0 |

### Merge duplicate records

Duplicates often hold different parts of the same record: the latest row has the new address but no phone number, and an older row has the phone. Set **Output** to **Merged row per key** to combine them:

**Step 1**

Dedupe picks the kept row exactly as before, with **Order by**, **Keep** and **Prefer the most complete row**.

**Step 2**

Every empty field of that row is filled from the next duplicate, in the same order, that has a value. Empty means the same as above: null, blank text, or an empty JSON object or array, for every field type.

**Step 3**

Key fields, and fields that no duplicate has a value for, keep the kept row's value.

> [!WARNING]
>
> In a merged row, fields can come from different input rows. Use **One row per key** when every field must come from the same record.

For example, with **Keep: Last** by `updated_at`:

| student\_id | updated\_at | address | phone | Notes |
| :- | :- | :- | :- | :- |
| S1 | 2026-03-01 | New St | (null) | kept row |
| S1 | 2026-02-01 | (blank) | 222 | next in order |
| S1 | 2026-01-01 | Old St | 111 | |

The output is one row: `S1, 2026-03-01, New St, 222`. The address comes from the kept row, and the phone from the next row that has one. **Rows with empty key** still decides what happens to rows without a key: with **Keep all of them** they pass through unmerged, with **Drop them** they are removed, and with **Handle them like any other key** they are merged by their key value like any other key (null, empty text and spaces are separate keys).

The merged row has the fields the Dedupe recorded when it was last saved, which is every field of the input, in the same order. If you add, rename or remove a field upstream, apply the field changes when the designer offers to, or open the Dedupe and save it, so its output follows. Until then, components after the Dedupe do not see the new field.

Each field is filled with its own pass over the duplicates of a key, so merging costs more than keeping one row on wide inputs (many fields) with keys that have many duplicates.

### Null values in Order by fields

Rows are sorted with null values **lowest**. This matters only for **Keep first**:

| Keep | Row kept for a key whose Order by field is null in some rows |
| :- | :- |
| Last | The row with the latest or highest value. Rows with a null value are never chosen over a row that has a value. |
| First | **A row with a null value**, not the row with the earliest value. |

To keep the earliest row that has a value, add a [Select](https://www.integrate.io/docs/etl/using-components-select-transformation.md) before Dedupe with a field that marks missing values, and order by it first:

**Step 1**

In the Select, add a field with the expression `(updated_at is null ? 1 : 0)` and the alias `updated_at_missing`.

**Step 2**

In Dedupe, set **Order by** to `updated_at_missing`, then `updated_at`, and **Keep** to **First**.

Rows with a value sort first (`0`), and among them the earliest `updated_at` wins. A key whose rows all have a null value still keeps one of them. The `updated_at_missing` field passes through to the output; remove it with a Select after Dedupe if you don't need it.

## Example

Input, with three versions of the same student and two rows that have no student ID:

| student\_id | updated\_at | grade |
| :- | :- | :- |
| S1 | 2026-01-01 | B |
| S1 | 2026-03-01 | A |
| S1 | 2026-02-01 | C |
| S2 | 2026-01-01 | B |
| (null) | 2026-01-01 | C |
| (blank) | 2026-01-05 | A |

Key fields `student_id`, Order by `updated_at`, Keep **Last**, Rows with empty key **Keep all of them**:

| student\_id | updated\_at | grade |
| :- | :- | :- |
| S1 | 2026-03-01 | A |
| S2 | 2026-01-01 | B |
| (null) | 2026-01-01 | C |
| (blank) | 2026-01-05 | A |

S1's latest row is kept unchanged, and both students without an ID are kept. Output row order is not guaranteed; add a [Sort](https://www.integrate.io/docs/etl/using-components-sort-transformation.md) after Dedupe if you need one.

## FAQ

**Q: How is Dedupe different from Distinct?**

Distinct removes rows that are identical in every field. Dedupe compares only the key fields, so it can collapse rows that differ in other fields, such as several updates to the same customer.

**Q: How is Dedupe different from an Aggregate with max() on every field?**

An Aggregate computes each field separately, so the output can combine values from different input rows. With **Output: One row per key** (the default), Dedupe keeps one whole input row per key. **Merged row per key** does combine rows, but on purpose: it starts from the kept row and only fills its empty fields, from the next duplicate in **Order by** order that has a value.

**Q: Why did Keep First pick a row with no date?**

Null values sort lowest, so with **Keep: First** a row whose Order by field is null comes before every dated row. See [Null values in Order by fields](#null-values-in-order-by-fields) for how to skip those rows.

**Q: Can I keep the row with the most data instead of the latest one?**

Yes. Turn on [Prefer the most complete row](#keep-the-most-complete-row) and choose the fields that count.

**Q: Can I pick the row by some other rule, such as a status?**

Yes. Add a [Select](https://www.integrate.io/docs/etl/using-components-select-transformation.md) before Dedupe with a field that ranks the rows, for example `(status == 'active' ? 1 : 0)`, and put it first in **Order by** with **Keep: Last**.

**Q: Can I combine the duplicates into one row instead of keeping one of them?**

Yes. Set **Output** to [Merged row per key](#merge-duplicate-records): the kept row's empty fields are filled from the other duplicates.

**Q: Can I keep the rows that were removed?**

Not yet. Dedupe has one output, which holds the kept rows.

**Q: My existing Limit with partition fields: does it change?**

No. A Limit saved before **Rows with empty key** was added keeps its behavior, **Handle them like any other key**.

## Related

> [card] [Limit Transformation](https://www.integrate.io/docs/etl/using-components-limit-transformation.md)

> [card] [Distinct Transformation](https://www.integrate.io/docs/etl/using-components-distinct-transformation.md)

> [card] [Window Transformation](https://www.integrate.io/docs/etl/using-components-window-transformation.md)

> [card] [Sort Transformation](https://www.integrate.io/docs/etl/using-components-sort-transformation.md)


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.