Deduplicating Incrementally Synced Grade Records

Incremental sync produces duplicates by design. The overlap window that keeps a delta pull from stranding boundary edits deliberately re-reads a sliver of already-seen rows, and every retry after a transient failure re-fetches whatever it retried. Both are correct behaviors — but they mean the same graded submission can arrive two, three, or a dozen times, and unless the pipeline collapses those copies to exactly one authoritative row, a student’s transcript shows a grade recorded twice and an aggregate double-counts it. This guide implements deduplication that is safe under exactly those conditions: it identifies a record by a stable composite key, keeps the single newest version, and holds up whether you deduplicate in-flight with an upsert or after the fact over a batch. It is the convergence half of incremental sync state management; the watermark persistence that makes the overlap re-reads happen lives in tracking last-sync timestamps for incremental LMS pulls.

Collapsing duplicate grade rows to the newest version by composite key On the left, four incoming rows share a composite key of course, assignment, and student token but carry different updated_at timestamps and scores; two are exact duplicates from an overlap re-read and one is a stale earlier version. A dedupe step sorts by updated_at descending and keeps the first row per key. On the right, one authoritative row remains, carrying the newest score. SAME COMPOSITE KEY, MANY ARRIVALS → KEEP NEWEST updated_at C7·A3·stu_9f · 10:02 · score 80 C7·A3·stu_9f · 10:45 · score 88 C7·A3·stu_9f · 10:45 · score 88 (dup) C7·A3·stu_9f · 09:50 · score 72 (stale) sort desc, keep first C7·A3·stu_9f · 10:45 · 88 one authoritative row

Prerequisites

Confirm each before running the script.

Step-by-step implementation

1. Choose a composite natural key, not a surrogate row id. The vendor’s per-row id is not stable across a re-pull — the same submission can be re-serialized with a new envelope id — so deduplicate on the business key that names the gradebook cell: source, course, assignment, and tokenized student. Two arrivals with the same composite key are the same fact regardless of transport metadata.

2. Rank arrivals within each key by recency. Sort each key’s group by version descending (when present), then updated_at descending. The top row is the authoritative one; everything below it is a superseded or duplicate copy to drop. Ranking rather than blind drop_duplicates() is what distinguishes an exact duplicate from a stale earlier edit — both share the key, but only the exact duplicate is safe to discard on content alone.

3. Keep exactly one row per key. After ranking, retain the first row per composite key. This collapses the overlap-window re-reads and retry re-fetches to a single record and guarantees the batch is convergent: running it twice yields the same result.

4. Decide upsert-in-flight versus dedupe-after. Two valid architectures. An idempotent upsert writes each record immediately with INSERT … ON CONFLICT (key) DO UPDATE … WHERE incoming.updated_at > existing.updated_at, letting the database enforce newest-wins per key with no staging batch. A dedupe-after pass collects a batch, deduplicates it in memory, and bulk-loads the survivors. Upsert suits streaming, low-latency writes and shifts the newest-wins guard into SQL; dedupe-after suits large periodic batches where an in-memory pass is cheaper than millions of single-row upserts. The keep-newest rule is identical either way; only where it runs differs.

5. Guard the newest-wins comparison explicitly. Whichever architecture, the write must refuse to overwrite a newer row with an older one. An out-of-order arrival — a retry delivering a stale copy after a fresh edit already landed — must lose. The WHERE incoming.updated_at > existing.updated_at clause (or the descending sort) encodes that; omit it and a late duplicate silently reverts a grade.

6. Never dedupe on score alone. Two legitimately different students, or the same student on two assignments, can share a score. Deduplicating on value collapses distinct facts. The key is the identity; the score is payload.

Complete runnable code block

python
"""Deduplicate incrementally synced grade records: keep the newest per key."""
from __future__ import annotations

import pandas as pd

# The composite natural key that names one gradebook cell.
KEY = ["source", "course_id", "assignment_id", "student_token"]


def dedupe_grades(df: pd.DataFrame) -> pd.DataFrame:
    """Collapse duplicate/stale rows to one authoritative row per composite key.

    Newest wins: rank by version (if present) then updated_at, both descending,
    and keep the first row in each key group. Convergent — running it twice on
    its own output is a no-op.
    """
    if df.empty:
        return df

    frame = df.copy()
    # updated_at must be UTC-aware so the sort orders edits correctly.
    frame["updated_at"] = pd.to_datetime(frame["updated_at"], utc=True)

    sort_cols = KEY + (["version"] if "version" in frame.columns else []) + ["updated_at"]
    ascending = [True] * len(KEY) + ([False] if "version" in frame.columns else []) + [False]
    frame = frame.sort_values(sort_cols, ascending=ascending, kind="mergesort")

    # First row per key group is the newest after the descending sort.
    deduped = frame.drop_duplicates(subset=KEY, keep="first").reset_index(drop=True)
    return deduped


def merge_incremental(existing: pd.DataFrame, incoming: pd.DataFrame) -> pd.DataFrame:
    """Dedupe-after merge: union a new delta batch into the canonical frame,
    then keep the newest per key. Equivalent in outcome to an idempotent upsert.
    """
    combined = pd.concat([existing, incoming], ignore_index=True)
    return dedupe_grades(combined)


UPSERT_SQL = """
INSERT INTO grade_facts (source, course_id, assignment_id, student_token,
                         score, version, updated_at)
VALUES (%(source)s, %(course_id)s, %(assignment_id)s, %(student_token)s,
        %(score)s, %(version)s, %(updated_at)s)
ON CONFLICT (source, course_id, assignment_id, student_token) DO UPDATE SET
    score      = EXCLUDED.score,
    version    = EXCLUDED.version,
    updated_at = EXCLUDED.updated_at
WHERE EXCLUDED.updated_at > grade_facts.updated_at;   -- newest wins, in-flight
"""


if __name__ == "__main__":
    # Four arrivals for the SAME cell: an early edit, the current edit,
    # an exact overlap-window duplicate, and a stale out-of-order retry.
    rows = pd.DataFrame(
        [
            {"source": "canvas", "course_id": 7, "assignment_id": 3,
             "student_token": "stu_9f", "score": 80.0, "version": 1,
             "updated_at": "2026-05-01T10:02:00Z"},
            {"source": "canvas", "course_id": 7, "assignment_id": 3,
             "student_token": "stu_9f", "score": 88.0, "version": 2,
             "updated_at": "2026-05-01T10:45:00Z"},
            {"source": "canvas", "course_id": 7, "assignment_id": 3,
             "student_token": "stu_9f", "score": 88.0, "version": 2,
             "updated_at": "2026-05-01T10:45:00Z"},   # overlap re-read duplicate
            {"source": "canvas", "course_id": 7, "assignment_id": 3,
             "student_token": "stu_9f", "score": 72.0, "version": 1,
             "updated_at": "2026-05-01T09:50:00Z"},   # stale retry, must lose
        ]
    )
    result = dedupe_grades(rows)
    print(result[["student_token", "score", "version", "updated_at"]].to_string(index=False))
    assert len(result) == 1, "one authoritative row per composite key"
    assert result.iloc[0]["score"] == 88.0, "kept the newest version"
    print("OK: collapsed 4 arrivals to 1 newest-wins row")

Verification and output validation

  • One row per key. assert result.duplicated(subset=KEY).sum() == 0 — no composite key survives more than once.
  • Newest version kept. For a known key, assert the surviving updated_at equals the group’s maximum and the score matches that version, proving stale copies were dropped, not the fresh one.
  • Idempotence. assert dedupe_grades(result).equals(result) — re-running on the output changes nothing, the defining property of a convergent dedupe.
  • Row-count sanity. assert len(result) == rows[KEY].drop_duplicates().shape[0] — the deduped count equals the number of distinct keys, not the number of arrivals.
  • Out-of-order arrival loses. Feed the stale 09:50 row after the 10:45 row; assert the 10:45 score still wins, proving order of arrival does not decide the winner — recency does.
  • No accidental value-dedup. Add a second student with the same score; assert both survive, proving the key, not the score, defines identity.

Troubleshooting

  • A grade reverted to an older value. An out-of-order stale copy overwrote a fresh one because the newest-wins guard was missing. Ensure the upsert carries WHERE EXCLUDED.updated_at > grade_facts.updated_at, or that the in-memory sort is descending on recency before drop_duplicates(keep="first").
  • Duplicates survive dedupe. The composite key is wrong — usually a missing source or assignment_id, so genuinely-one cell is split across keys, or the vendor row id crept into the key. Deduplicate on the business key only.
  • Distinct records collapsed into one. The key is too coarse (for example, omitting assignment_id), folding different gradebook cells together. Widen the key to fully identify one fact.
  • TypeError sorting updated_at. Mixed timezone-aware and naive timestamps. Normalize with pd.to_datetime(..., utc=True) before sorting, as the script does.
  • Ties resolved unpredictably. Two edits share an updated_at to the second and there is no version to break the tie. Add the monotonic version counter to the sort, or fall back to a stable ingestion sequence number so the tiebreak is deterministic.
  • Dedupe is slow on large batches. sort_values over millions of rows is memory-heavy in pandas. Deduplicate per key-partition, or push the newest-wins logic into the database upsert so the batch never fully materializes in memory.

Part of: Incremental Sync State Management