Where duplicate rows come from

Description

This module's lessons 2 through 4 solved one problem: which dimension version corresponds to each sale. This lesson opens a different problem, one that can coexist perfectly with an already-correct point-in-time join: how many times the same sale shows up in the data that reaches Kiosko. You're going to build a batch of orders where three of them arrived twice — resent, with a different ingestion timestamp each time — and measure, with the same grain query from module 1, exactly how many extra rows it produces.

Connection to the module. This lesson is deliberately diagnostic, not corrective — it builds the evidence that duplicates exist and explains where they come from, without deduplicating yet. Lesson 6 takes exactly the batch this lesson builds and deduplicates it with ROW_NUMBER()/QUALIFY. Separating "detect" from "fix" into two distinct lessons is intentional: understanding why duplicates exist — before learning to remove them — keeps you from treating deduplication as a magic command applied without thinking about the cause.

An analogy: the same package, touched twice by mistake

Think of a courier who delivers a package, but the tracking app doesn't confirm the delivery in time — maybe it lost signal, maybe the server was slow to respond. The system, not knowing whether the package actually arrived, marks it "pending" and retries: the courier (or another one) knocks on the door a second time, with the same package. From the recipient's point of view, nothing strange happened — they received a package — but from the tracking system's point of view, there are now two delivery events for the same package, when in reality only one occurred.

That's exactly what happens with an order that gets resent: an ingestion system that doesn't confirm receipt reliably — the technical guarantee distributed systems call at-least-once delivery, as opposed to exactly-once — would rather over-send than lose data. That's a reasonable design decision on the source side: losing a real order is much worse than processing it twice. But it means whoever receives that data — Kiosko, in this case — has to be prepared to find the same order more than once, and know what to do about it.

Worked example: a batch with three resent orders

Build a new batch, raw_orders_batch, that simulates how Kiosko's forty orders for the week would arrive if the ingestion system had retries: each order arrives once, with an ingested_at mark (one minute after order_ts, by a fixed rule) — except three orders, which arrive twice, with a second, later ingested_at, simulating the resend.

# raw_orders_batch.py
from datetime import datetime, timedelta
import duckdb

from raw_orders import RAW_ORDERS

con = duckdb.connect()
con.execute("""
    CREATE TABLE raw_orders_batch (
        order_id VARCHAR, store_id VARCHAR, product_id VARCHAR,
        quantity INTEGER, unit_price DOUBLE, revenue DOUBLE,
        order_ts TIMESTAMP, ingested_at TIMESTAMP
    )
""")

# Each order arrives once, with ingested_at = order_ts + 1 minute (fixed rule).
rows = []
for r in RAW_ORDERS:
    order_id, store_id, product_id, quantity, unit_price, order_ts_str = r
    order_ts = datetime.fromisoformat(order_ts_str)
    ingested_at = order_ts + timedelta(minutes=1)
    revenue = quantity * unit_price
    rows.append((order_id, store_id, product_id, quantity, unit_price, revenue, order_ts, ingested_at))

# Three orders get RESENT -- same order_id + product_id, later ingested_at
# (retry / "at least once" delivery), with a fixed delay each.
RESEND_DELAY = {
    "ORD-1001": timedelta(minutes=35),
    "ORD-3001": timedelta(minutes=54),
    "ORD-6005": timedelta(minutes=49),
}
for r in RAW_ORDERS:
    order_id, store_id, product_id, quantity, unit_price, order_ts_str = r
    if order_id in RESEND_DELAY:
        order_ts = datetime.fromisoformat(order_ts_str)
        original_ingest = order_ts + timedelta(minutes=1)
        resend_ingest = original_ingest + RESEND_DELAY[order_id]
        revenue = quantity * unit_price
        rows.append((order_id, store_id, product_id, quantity, unit_price, revenue, order_ts, resend_ingest))

con.executemany("INSERT INTO raw_orders_batch VALUES (?, ?, ?, ?, ?, ?, ?, ?)", rows)

print("=== The 3 resent keys, exactly as they arrive (two rows each) ===")
print(con.sql("""
    SELECT order_id, product_id, ingested_at
    FROM raw_orders_batch
    WHERE order_id IN ('ORD-1001', 'ORD-3001', 'ORD-6005')
    ORDER BY order_id, ingested_at
"""))

What to expect. Running python3 raw_orders_batch.py, the output is exactly this:

=== The 3 resent keys, exactly as they arrive (two rows each) ===
┌──────────┬────────────┬─────────────────────┐
│ order_id │ product_id │     ingested_at     │
│ varchar  │  varchar   │      timestamp      │
├──────────┼────────────┼─────────────────────┤
│ ORD-1001 │ P001       │ 2026-08-03 08:15:00 │
│ ORD-1001 │ P001       │ 2026-08-03 08:50:00 │
│ ORD-3001 │ P004       │ 2026-08-05 08:11:00 │
│ ORD-3001 │ P004       │ 2026-08-05 09:05:00 │
│ ORD-6005 │ P003       │ 2026-08-08 09:11:00 │
│ ORD-6005 │ P003       │ 2026-08-08 10:00:00 │
└──────────┴────────────┴─────────────────────┘

ORD-1001, ORD-3001, and ORD-6005 each show up twice, with the same order_id and the same product_id, but a different ingested_at — the timestamp for when the data entered Kiosko's system, which is not the same as order_ts (when the sale happened). The rest of Kiosko's orders — thirty-seven of the forty — show up exactly once, with no resend.

Diagram: the broken grain, measured with the same query from module 1

You already know the query that declares a fact table's grain — COUNT(*) against COUNT(DISTINCT key) — from module 1, lesson 5. Apply it here, with the same composite key (order_id + product_id) you used back then:

print("\n=== raw_orders_batch grain: COUNT(*) vs COUNT(DISTINCT order_id-product_id) ===")
print(con.sql("""
    SELECT
        COUNT(*) AS total_rows,
        COUNT(DISTINCT order_id || '-' || product_id) AS distinct_order_product_lines
    FROM raw_orders_batch
"""))

=== raw_orders_batch grain: COUNT(*) vs COUNT(DISTINCT order_id-product_id) ===
┌────────────┬──────────────────────────────┐
│ total_rows │ distinct_order_product_lines │
│   int64    │            int64             │
├────────────┼──────────────────────────────┤
│         43 │                           40 │
└────────────┴──────────────────────────────┘

43 versus 40 — the two numbers don't match, exactly the signal module 1 taught you to look for. The difference, 3, is the exact number of resent orders. Identify precisely which ones, by grouping on the same key and filtering for the ones that show up more than once:

print("\n=== Identifying EXACTLY which keys are duplicated ===")
print(con.sql("""
    SELECT order_id || '-' || product_id AS order_product_key, COUNT(*) AS times_seen
    FROM raw_orders_batch
    GROUP BY order_product_key
    HAVING COUNT(*) > 1
    ORDER BY order_product_key
"""))

=== Identifying EXACTLY which keys are duplicated ===
┌───────────────────┬────────────┐
│ order_product_key │ times_seen │
│      varchar      │   int64    │
├───────────────────┼────────────┤
│ ORD-1001-P001     │          2 │
│ ORD-3001-P004     │          2 │
│ ORD-6005-P003     │          2 │
└───────────────────┴────────────┘

flowchart TD
    A["Source: Kiosko's 40 real orders"] --> B["Ingestion system with retries\n(at-least-once delivery)"]
    B -->|"37 orders: 1 attempt,\nconfirmation on time"| C["1 row each"]
    B -->|"3 orders: the confirmation\ndidn't arrive on time, retried"| D["2 rows each\n(same order_id+product_id,\ndifferent ingested_at)"]
    C --> E["raw_orders_batch: 43 total rows\n40 unique keys, 3 duplicated"]
    D --> E

Going deeper: the real causes of a duplicated batch, not just this one

This lesson's scenario — retries from an ingestion system with "at least once" delivery — is a real and common cause, but not the only one. It's worth naming the others, because lesson 6's deduplication technique works for all of them equally, regardless of the specific cause:

• Network or API retries (this lesson's case): an HTTP client doesn't get confirmation in time — from a timeout, from a transient server error — and resends the same request, not knowing whether the first one arrived. The source prefers the risk of duplicating over the risk of losing data.
• Multiple extraction runs over the same time window: an extraction job that fails halfway through, and gets re-run from the start of the window instead of resuming where it left off, reintroduces the rows it had already extracted before failing.
• Replaying a change log (CDC): a Change Data Capture system — mentioned in foundations and covered in depth in streaming-with-kafka-and-flink-guide — can replay the same event more than once if the consumer restarts from a checkpoint earlier than the last event actually processed.
• The same file loaded more than once by human or automation error: someone — or a misconfigured cron job — re-runs the load of orders_2026-08-03.csv without realizing it was already loaded before.

All of these causes share something important: none of them corrupts the row values — the resent order has, in every case in this lesson, exactly the same quantity, unit_price, and revenue in both copies. This sets them apart from a different and harder problem: when a resend brings corrected values, not identical ones (for example, a different quantity because someone fixed a data-entry error). That case — a "correction" disguised as a duplicate — needs an explicit business decision about which version to keep, and it's outside this lesson's scope: here, the duplicates are exact copies, and the only decision is which copy to keep, not which value is correct.

Confirm this property — the duplicates are exact copies, not corrections — with a query:

print("\n=== Confirming both copies of each resent key have the SAME revenue ===")
print(con.sql("""
    SELECT order_id, product_id, COUNT(DISTINCT revenue) AS distinct_revenue_values
    FROM raw_orders_batch
    WHERE order_id IN ('ORD-1001', 'ORD-3001', 'ORD-6005')
    GROUP BY order_id, product_id
    ORDER BY order_id
"""))

=== Confirming both copies of each resent key have the SAME revenue ===
┌──────────┬────────────┬─────────────────────────┐
│ order_id │ product_id │ distinct_revenue_values │
│ varchar  │  varchar   │          int64          │
├──────────┼────────────┼─────────────────────────┤
│ ORD-1001 │ P001       │                       1 │
│ ORD-3001 │ P004       │                       1 │
│ ORD-6005 │ P003       │                       1 │
└──────────┴────────────┴─────────────────────────┘

distinct_revenue_values = 1 for all three — each pair of duplicated rows has exactly the same revenue in both copies. This confirms you're looking at a real duplicate, not a correction: lesson 6's deduplication is safe to apply precisely because there's no ambiguity about which value is "the correct one" — both copies are, and one is redundant.

Common mistakes

Deduplicating before confirming the rows are exact copies. What happens: someone sees two rows with the same order_id, assumes they're duplicates in this lesson's sense, and deduplicates them without checking whether the values actually match. Why it happens: two rows with the same key "look like" duplicates at a glance, and checking the values feels like an unnecessary extra step. How to spot it: if your deduplication process never compared the "duplicate" rows' values — only their keys — you can't tell a real resend (same data, twice) apart from a legitimate correction (same order_id, different values), which should be handled completely differently. How to fix it: before deduplicating any batch, run a query like this lesson's — COUNT(DISTINCT <value column>) grouped by the suspected duplicate key — to confirm the copies are, in fact, identical.

Using order_id alone as the grain key, instead of the composite key. What happens: someone, building this lesson's grain query, uses COUNT(DISTINCT order_id) instead of COUNT(DISTINCT order_id || '-' || product_id), repeating the exact mistake module 1, lesson 5, already warned about for fact_orders. Why it happens: with Kiosko's current data — where each order has a single product — both queries give the same result (40 distinct), so the error doesn't show up with this dataset. How to spot it: if your declaration of raw_orders_batch's grain doesn't mention product_id, you ran a weaker check than the table's actual schema allows. How to fix it: always use the finest composite key the schema supports, exactly as in module 1 — the discipline doesn't change just because the context (deduplication, instead of grain declaration) did.

Ignoring ingested_at and using order_ts to decide which copy is "the most recent." What happens: someone, preparing the groundwork to deduplicate in lesson 6, plans to use order_ts — the sale date — to decide which copy to keep, instead of ingested_at — the data's arrival date. Why it happens: order_ts is the column you already know from module 1, and ingested_at is new in this lesson, so it's easy to reach for the familiar one. How to spot it: in this lesson, both copies of each resent order have exactly the same order_ts — the sale happened once, at a single instant — the only thing distinguishing one copy from the other is ingested_at. If your deduplication criterion uses order_ts, you have no way to tell the two rows apart, because they're identical on that column. How to fix it: when deduplicating a resend, the "which copy to keep" criterion must be based on a column that does distinguish the copies from each other — typically the ingestion timestamp, exactly ingested_at in this case. Lesson 6 builds on this distinction.

Exercises

Exercise 1 — Confirm the 37 orders with no resend have exactly one row each. Using raw_orders_batch, write a query that confirms no order outside the three resent ones shows up more than once.

print(con.sql("""
    SELECT order_id || '-' || product_id AS order_product_key, COUNT(*) AS times_seen
    FROM raw_orders_batch
    WHERE order_id NOT IN ('ORD-1001', 'ORD-3001', 'ORD-6005')
    GROUP BY order_product_key
    HAVING COUNT(*) != 1
"""))

┌────────────────────┬────────────┐
│ order_product_key  │ times_seen │
│      varchar       │   int64    │
├────────────────────┼────────────┤
└────────────────────┴────────────┘
                0 rows

Exercise 2 — Calculate how much the batch's revenue gets inflated if you sum it without deduplicating. Using the complete raw_orders_batch (43 rows), compute SUM(revenue) and compare it against the real revenue of the forty orders (106.15).

print(con.sql("SELECT ROUND(SUM(revenue), 2) AS total_revenue_with_duplicates FROM raw_orders_batch"))

┌───────────────────────────────┐
│ total_revenue_with_duplicates │
│            double             │
├───────────────────────────────┤
│                        119.05 │
└───────────────────────────────┘

Exercise 3 — Explain why this lesson didn't deduplicate yet, even though it already has all the evidence to do so. In 2-3 sentences, explain the pedagogical decision to separate "detecting duplicates" (this lesson) from "removing them" (lesson 6).

Summary and next step

This lesson built a batch of orders with three real resends — ORD-1001, ORD-3001, ORD-6005, each with two copies identical in value but with a different ingested_at — and confirmed, with the same grain query from module 1, that the batch has 43 rows for 40 unique combinations of order_id+product_id. You named the most common causes of this kind of duplication — network retries, repeated extractions, CDC replay, duplicate loads from human error — and confirmed that, in this case, the copies are exact: the problem is "which one to keep," not "which one is correct."

Before moving on you should be able to: apply module 1's grain query to any new batch to detect duplicates; name at least three real causes of duplication in a data pipeline; and explain the difference between an exact duplicate (this lesson) and a correction disguised as a duplicate (out of scope).

Lesson 6 takes this same batch, raw_orders_batch, and deduplicates it for real: ROW_NUMBER() OVER (PARTITION BY order_id, product_id ORDER BY ingested_at DESC) combined with QUALIFY, keeping the most recent copy of each resend and restoring the exact forty-row grain.

Resources

• DuckDB — window functions documentation (ROW_NUMBER, among others) — the foundation for the technique lesson 6 applies to the batch this lesson builds. duckdb.org/docs/current/sql/functions/window_functions. In English.
• Databricks — "What is the medallion lakehouse architecture?" — the bronze/silver/gold framework where deduplication of resent data typically happens, between the bronze layer (raw, with duplicates) and silver (clean). docs.databricks.com/aws/en/lakehouse/medallion. In English.
• DuckDB — official Python client documentation, used to build and query raw_orders_batch in this lesson. duckdb.org/docs/current/clients/python/overview. In English.