Where Dedup Lives: Write-Side versus Read-Side
Pick between absorbing redelivery at the write and absorbing it at the read, price what absorbing it nowhere costs, and probe for redeliveries that disagree.
Two places to absorb a duplicate
Module 9.1 established that at-least-once delivery manufactures duplicates and that nothing you configure removes them. You have now graded both ways of absorbing them.
Dedup at write. The previous lesson's apply reduced the changelog to one event per key and upserted it. The target holds one row per key at all times, any consumer can query it directly, and a redelivery just rewrites the same value. What you give up is history: after the upsert lands, the intermediate states are gone, and if the logic that produced them was wrong you cannot recompute, because the input is not there any more.
Dedup at read. de-l8-append-dedup-at-read appended every delivery to a raw table exactly as it arrived and pushed the correctness into a view: ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingest_seq) and keep rn = 1. Raw stays immutable and auditable, you can replay it after a bug fix, and you can prove what you received. What you give up is convenience: every consumer must remember to dedup, and the one that forgets reports inflated numbers with a straight face.
Stage 1 of 4: One stream, redelivering. Neither branch can prevent that.
Choosing, out loud
- Need replay, audit, or the ability to recompute after a logic bug? Dedup at read. Regulated finance and anything with a "prove what you received" requirement lands here by default.
- Need a table other tools hit directly, with no shared view discipline to enforce? Dedup at write. A dimension a BI tool joins to is the archetype.
- The common production answer is both: append raw for the audit trail, and curate a served table from it with an upsert. That costs one more table and buys both properties, which is why so many platforms end up with a bronze and a silver layer.
What is never an option is neither. Absorbing duplicates nowhere does not fail loudly; it inflates every additive measure you have, silently, in proportion to how often the producer retried.
The trap: duplicates that disagree
Here is the part that separates people who have cleaned a real stream from people who memorized ROW_NUMBER.
A redelivery of the same event_id with a byte-identical payload is a duplicate. Collapsing it loses nothing, and rn = 1 is exactly right.
A redelivery of the same event_id with a different payload is not a duplicate. It is two different claims wearing one identity, which means either the producer has a bug, a key collided, or something upstream mutated a record it had already published. A silent rn = 1 picks one of the two claims by sort order and throws the other away, and you never learn that a decision was made at all.
So the order of operations is: audit for disagreement first, collapse second. The probe is small. Group by the key, and flag any key whose payload values are not all equal. In this lesson two of the twenty five events are corrupted that way, and the practice is to find them before anything collapses them.
Common mistake: treating "duplicate rate" as a data-quality metric on its own. A 20% duplicate rate with byte-identical payloads is a producer that retries, which is normal and healthy. A 0.1% duplicate rate where the payloads disagree is a correctness incident. The rate does not tell you which one you have; the payload comparison does.
Interview nuance: "How do you handle duplicate events" is answered in three beats. Name both absorbers, pick one with a reason tied to the requirement, then raise the disagreeing-duplicate case before you are asked. That last beat is the unprompted failure mode that reads as production experience.
On a real platform this differs. Here the audit is one
GROUP BYover 32 rows in SQLite. On a real platform the raw table is an Iceberg or Delta table holding billions of rows, the deduped read is a materialized view or a dbt model with auniquetest on the key, and the disagreement probe runs as a scheduled data test that pages someone. The disagreeing rows themselves usually go to a dead letter queue with the record, the error, and a timestamp, alerting past roughly a five percent rate, rather than halting the pipeline. The SQL shape is the one you just wrote.
CREATE TABLE raw_events (
ingest_seq INTEGER, -- the order the sink landed the row; decides which copy is "first"
event_id TEXT, -- the producer's idempotency key; a redelivery repeats it
user_id INTEGER,
amount_cents INTEGER,
event_time_ms INTEGER -- when it happened at the SOURCE; identical across redeliveries
);
-- ingest_seq 1..25 is the clean first pass. Everything from 26 on is a redelivery.
INSERT INTO raw_events (ingest_seq, event_id, user_id, amount_cents, event_time_ms) VALUES
( 1, 'E-9001', 501, 1200, 1767290000000),
( 2, 'E-9002', 502, 3450, 1767290045000),
( 3, 'E-9003', 501, 800, 1767290090000),
( 4, 'E-9004', 503, 2100, 1767290135000),
( 5, 'E-9005', 502, 1750, 1767290180000),
( 6, 'E-9006', 501, 990, 1767290225000),
( 7, 'E-9007', 504, 5600, 1767290270000),
( 8, 'E-9008', 503, 1300, 1767290315000),
( 9, 'E-9009', 502, 2400, 1767290360000),
(10, 'E-9010', 501, 1500, 1767290405000),
(11, 'E-9011', 504, 700, 1767290450000),
(12, 'E-9012', 505, 3300, 1767290495000),
(13, 'E-9013', 502, 1100, 1767290540000),
(14, 'E-9014', 501, 2750, 1767290585000),
(15, 'E-9015', 504, 1900, 1767290630000),
(16, 'E-9016', 503, 850, 1767290675000),
(17, 'E-9017', 502, 4200, 1767290720000),
(18, 'E-9018', 501, 640, 1767290765000),
(19, 'E-9019', 504, 2600, 1767290810000),
(20, 'E-9020', 503, 1450, 1767290855000),
(21, 'E-9021', 502, 980, 1767290900000),
(22, 'E-9022', 501, 3100, 1767290945000),
(23, 'E-9023', 504, 1250, 1767290990000),
(24, 'E-9024', 503, 2050, 1767291035000),
(25, 'E-9025', 505, 200, 1767291080000),
(26, 'E-9002', 502, 3450, 1767290045000),
(27, 'E-9005', 502, 1795, 1767290180000),
(28, 'E-9007', 504, 5600, 1767290270000),
(29, 'E-9012', 505, 3300, 1767290495000),
(30, 'E-9014', 501, 2700, 1767290585000),
(31, 'E-9018', 501, 640, 1767290765000),
(32, 'E-9007', 504, 5600, 1767290270000);-- The same landing table read two ways. One of these numbers is a lie.
SELECT 'naive SUM over raw' AS reading,
ROUND(SUM(amount_cents) / 100.0, 2) AS revenue_usd
FROM raw_events
UNION ALL
SELECT 'first delivery per event_id',
ROUND(SUM(amount_cents) / 100.0, 2)
FROM (
SELECT amount_cents,
ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingest_seq) AS rn
FROM raw_events
)
WHERE rn = 1;Apply
Your turn
The task this lesson builds to.
Write a query that returns, per user, what a naive sum reports next to the true revenue and the gap between them, as (user_id, naive_cents, true_cents, inflation_cents), biggest inflation first, over raw_events(ingest_seq, event_id, user_id, amount_cents, event_time_ms).
naive_cents sums every landed row. true_cents keeps only the FIRST delivery of each event_id, where first means the lowest ingest_seq. inflation_cents is the difference. This is the cost of absorbing duplicates nowhere, priced per user.
3 hints and 1 automated check are waiting in the workspace.
Practice
Make it stick
A second problem on the same idea, plus 3 bonus drills.
Write a query that returns the events whose deliveries disagree on amount_cents, as (event_id, delivery_count, min_cents, max_cents), ascending by event_id, over the same raw_events table.
Most of the redeliveries in this landing zone carry byte-identical payloads and are safe to collapse. A handful do not. Those are the ones to surface before anything runs rn = 1 over them.
1 automated check is waiting in the workspace.