Skip to content

Deduplication

A sum over the replay is wrong, and the amount it is wrong by is the whole motivation for this page:

type Event {
    id       : Int
    kind     : String
    customer : String
    amount   : Decimal
    at       : String
}
rows : Seq<Event> = source("silver_replayed")
rows | filter(r => r.kind == "order_placed") | map(r => r.amount) | sum
253.73

The right answer is 188.49: orders 2 and 4 are counted twice, and order 6 is counted at both its amounts. Three kinds of duplicate are in those nine rows, and each has its own step.

Exact duplicates

A row delivered twice, identical in every field, is removed by distinct, which keeps each value once at its first position:

rows : Seq<Event> = source("silver_replayed")
rows | distinct | map(r => r.id)
[1, 2, 3, 4, 5, 6, 6]

That is what at-least-once delivery produces: the subject of chapter 17 delivers a message again when a reader stopped before acknowledging it, and the message is the same bytes both times. distinct is the whole answer where every copy is exact. It is not the answer for order 6, whose two copies differ.

Duplicates by key

Two rows with one key are the same fact, and one of them is the one to keep. Which one is a rule the pipeline states. For a correction, the latest by at:

rows : Seq<Event> = source("silver_replayed")
rows
| group(by = r => r.id)
| map(g => g.items | max_by(r => r.at))
| somes
| sort(by = r => r.id)
| map(r => {id: r.id, amount: r.amount, at: r.at})
[{amount: 120.50, at: "2026-11-01T09:15:00Z", id: 1}, {amount: 42.00, at: "2026-11-01T09:20:00Z", id: 2}, {amount: 42.00, at: "2026-11-01T09:21:00Z", id: 3}, {amount: 7.99, at: "2026-11-01T10:02:00Z", id: 4}, {amount: 120.50, at: "2026-11-02T08:00:00Z", id: 5}, {amount: 18.00, at: "2026-11-02T11:50:00Z", id: 6}]

Read step by step: group(by = r => r.id) is one group per key, each holding every row with that key in input order; max_by(r => r.at) picks the row with the latest timestamp out of each group, an Option since a group could be empty; somes unwraps; sort puts the keys in order, since a set of groups has none. Six rows, order 6 at its corrected amount.

In SQL this is the pattern every warehouse carries:

select * from (
  select *, row_number() over (partition by id order by at desc) as n
  from silver_replayed
) where n = 1

partition by id is group(by = r => r.id), order by at desc with n = 1 is max_by(r => r.at), and nothing is numbered, because the row wanted is named by what makes it the one to keep rather than by its position after a sort. Keeping the first version instead, the fact as first reported, is min_by:

rows : Seq<Event> = source("silver_replayed")
rows
| group(by = r => r.id)
| map(g => g.items | min_by(r => r.at))
| somes
| filter(r => r.id == 6)
| map(r => r.amount)
{15.25}

max_by and min_by take any Orderable key, so the rule may be a timestamp, a version number, or a comparison of two rows written out. Of two copies equal under the rule, the one earlier in the input is kept. Where they are the same row, as the exact duplicates are, that is no choice at all; where they differ, a rule that ends in a key no two rows share decides between them, and Windows writes one with then. row_number itself is there too, as the step over.

The sum, over the deduplicated rows:

rows : Seq<Event> = source("silver_replayed")
rows
| group(by = r => r.id)
| map(g => g.items | max_by(r => r.at))
| somes
| filter(r => r.kind == "order_placed")
| map(r => r.amount)
| sum
188.49

Duplicates with no key

A row with no key is identified by its content. Two deliveries of one webhook body carry the same fields, written in another key order or with 42.00 for 42, and differ in when each arrived, so distinct keeps both. The fingerprint of a Data tree is the SHA-256 of its canonical JSON, and two trees equal under HQL equality have one fingerprint. It is the key the rows do not have:

from crypto.digest import *

type Delivery {
    received : String
    body     : Data
}

deliveries = [
    Delivery(received = "2026-11-01T09:15:02Z", body = Data("{\"customer\": \"c-17\", \"sku\": \"tea\", \"amount\": 42.00}", Json)),
    Delivery(received = "2026-11-01T09:15:09Z", body = Data("{\"amount\": 42, \"sku\": \"tea\", \"customer\": \"c-17\"}", Json)),
    Delivery(received = "2026-11-01T09:16:40Z", body = Data("{\"customer\": \"c-21\", \"sku\": \"jam\", \"amount\": 6.50}", Json))
]
deliveries
| group(by = d => fingerprint(d.body))
| map(g => g.items | min_by(d => d.received))
| somes
| sort(by = d => d.received)
| map(d => d.body)
[{amount: 42.00, customer: "c-17", sku: "tea"}, {amount: 6.50, customer: "c-21", sku: "jam"}]

The step is the one of Duplicates by key, with fingerprint(d.body) as the key and the first delivery as the one to keep. A fingerprint is a String, so a load may write it as a column and the merge page match on it as on any key. crypto.digest and canonical JSON are chapter 30.

In the load

Deduplication belongs in the load that writes silver, once, at the grain of a fact. A batch load reads the replay whole, deduplicates and writes. It writes to the lakehouse, so it is shown checked: Step is what hql check prints for a pipeline that ends in target, a program whose result is what it wrote:

rows : Seq<Event> = source("silver_replayed")
rows
| group(by = r => r.id)
| map(g => g.items | max_by(r => r.at))
| somes
| sort(by = r => r.id)
| target("orders_silver")
Step

A streaming load cannot group a stream that never ends, and the checker refuses it, as chapter 14 showed for a sum. It deduplicates within each micro-batch instead, and sort makes each batch a List of the declared record, which is what sink writes as rows:

events : Stream<Event> = tap("orders_raw")
events
| collect(500)
| map(batch => batch
    | group(by = r => r.id)
    | map(g => g.items | max_by(r => r.at))
    | somes
    | sort(by = r => r.id))
| emit
| sink("orders_silver")
Step

Two copies that land in different batches are not caught by this program, and that is a limit of what a bounded window can know. The merge page is the complement: deduplication against what the table already holds.