Proving a copy matches the original

Problem

I work at a K-12 online learning provider: schools in the US and abroad partner with us so their students can take courses online. Everything lives in our online platform: which schools take part, which courses they offer, and each course’s chapters and lessons. A copy of that data is kept in a separate database (a “data warehouse”) for reports, and my team kept its own lists too. Nobody had proved that the copies held exactly the same rows as the live platform, so every report built on them carried a small, quiet doubt.

Constraints

• I could only read both sides, never change them, and only the tables the question needed. No table about people was read.
• The only things my queries returned were counts, ids (each row’s unique code) and fingerprints.
• The live platform keeps changing while you compare, so a difference might be timing rather than a mistake.

What I built

A way to compare two lists, written in SQL on BigQuery with some Python around it. Each side is boiled down to two things: how many rows it has, and one “fingerprint” of all its ids in sorted order. The fingerprint is a SHA-256 hash, a short code that changes completely if even one id differs. If the counts and fingerprints match, the lists match. I tried it first on three small courses before comparing anything bigger.

When fingerprints differ, I split the ids into groups by their first character, find the groups that disagree, and narrow down until I reach the exact rows. Each remaining difference gets one of three labels: timing (added or removed after the copy was taken), a filter that one side applies and the other does not, or unexplained. The warehouse can show a table as it was at an earlier moment, so I rebuilt each table as it stood when the live list was taken. That let me prove a difference was timing rather than assume it.

How I proved it worked

• Four lists matched exactly: schools, courses with the team that grades them, chapters in order, and lessons in order.
• Getting them to match meant finding filters nobody had written down, such as archived courses.
• Two other lists could not be rebuilt from the tables I was allowed to read, because both depend on tables about people. That is written down, not hidden.
• The schools list was off by a handful of rows. Every one was a school added or removed within an hour after the copy was taken, so none was unexplained.

I later used the same approach for a different check. Each partner school has a page of grading instructions that should link to the right form for reporting a grading problem. For a target group of schools, I read every school’s live instructions and used the warehouse copy as a second witness; the two agreed on every line. One fingerprint of the text showed that most of the group already had the same notice, and the rest had none. The text was checked where it lived, so none of it was copied out.

Outcome

• Four lists proven to match; the two that could not be checked are named, with the reason.
• Every school in the target group now shows the right notice. Most already had it; I added it to the rest myself, one at a time.
• Another group of schools, outside the target list, had older links; those went to the person who owns the forms to decide.
• Each weekly refresh of my team’s lists now starts by proving the copy matches.

What I would do next

• Run the comparison on a schedule and send an alert on the first unexplained difference.
• Publish the three labels (timing, filter, unexplained) as a short checklist anyone can use to compare two lists.

The demo it pairs with

Gap finder. Paste two lists or use the sample. See a count and a fingerprint for each side, then every difference grouped by its likely reason.