Find Exactly What Changed Between Two CSV Releases

Compare two versions of a public CSV in two passes: first the structure (headers, column order, types), then the records, matched by a stable key that you have checked for blanks and duplicates in both files. Every valid keyed row ends up in exactly one bucket: added, removed, changed or unchanged. Rows with a blank or duplicated key go to an unresolved report instead of being forced into the totals. Row numbers are never the identity; a reordered file with identical keys and values should produce zero record changes.

Two Passes: Schema Diff, Then Keyed Record Diff

The workflow has five steps:

1. Preserve both source files. Keep untouched copies and record where and when each version was obtained.
2. Parse both under the same settings. Hold delimiter, encoding, quoting, header and null rules constant, and apply the same types to shared fields.
3. Compare headers and schemas. List added, removed, reordered and type-changed columns.
4. Validate a stable record key. Confirm it is present, nonblank and unique within each release.
5. Classify records and publish a summary. Report added, removed, changed, unchanged and unresolved counts, with one detail row per changed field.

The order matters because a CSV carries no declaration of column types, uniqueness constraints or a primary key. Those assumptions have to come from a data dictionary or separate metadata, as the W3C CSV on the Web primer explains. Skip the schema pass and the key audit, and the record pass will report parsing artefacts and duplicate collapses as data changes.

The record-level outcomes are:

• Added: the key appears only in the new release.
• Removed: the key appears only in the old release.
• Changed: the key appears in both and at least one compared field differs.
• Unchanged: the key appears in both and all compared fields agree.
• Unresolved: the row has a blank key or belongs to a duplicate-key group.

Schema changes are reported separately. Adding a column is not adding a record, and a new column must not mark every matched record as changed. Whether values in a new column need their own analysis is a separate decision.

Paste your old and new CSV and name the key column; the counts, key audit and changed-field table update as you type.

Preserve Both Files and Parse Them Under One Set of Rules

Keep the old and new files exactly as downloaded. For each, record the filename, source URL, release date if the publisher gives one, retrieval date, a checksum or file size if you use one, and which is old and which is new. Do not clean, sort or resave these archival copies; do all transformation on working copies so another reviewer can reproduce the result from the originals.

Import both files with identical settings for delimiter, header handling, character encoding, quote and escape behaviour, null representation, date parsing and the types of shared fields. Inconsistent parsing manufactures changes before the comparison starts: one import reads an empty field as NULL while the other keeps an empty string, or independent type inference reads a code column as integers in one release and as text in the other.

DuckDB is one practical ingestion tool. It reads CSV directly, accepts explicit header, delimiter, null, encoding and column-type settings, and shows the imported schema with DESCRIBE. Supplying column definitions disables its automatic schema detection, per the DuckDB CSV import documentation. Inspect inferred types before trusting them; when the intended types are known, apply the same definitions to the shared fields in both releases and use version-specific definitions only for genuinely added or removed columns. Importing everything as text and typing only where the comparison policy demands it is an equally valid approach.

When DuckDB reads several files, it aligns columns by position by default. Setting union_by_name = true aligns them by name and fills absent columns with NULL, which helps when schemas differ, but schema alignment is not a record-aware diff and does not detect renames, as the guide to combining file schemas documents.

Report Added, Removed, Reordered and Retyped Columns Separately

Run the structural checks before looking at any row:

Structural check	Report as
Column only in new file	Added column
Column only in old file	Removed column
Same columns, new sequence	Reordered columns
Imported or declared type differs	Type change

If an old column disappears and a similarly named new column appears, label it a possible rename, not a confirmed one. Confirm through metadata, release notes or the publisher. A comparison tool cannot know that region_code became region_cd on purpose.

For recurring releases, external metadata removes most of this guesswork. CSVW metadata can describe structure, identifiers, validation constraints and provenance. A Frictionless Tabular Data Resource can declare field names, field types, a primary key, encoding and the CSV dialect. Record the documentation version or access date for such sources in your comparison notes, since the specs evolve.

Pick a Key That Is Present, Nonblank and Unique in Both Releases

A usable key identifies the same logical record across releases. It must be present in both versions, nonblank, unique within each version, stable when descriptive values change, and ideally documented in metadata or a data dictionary. A persistent record ID beats a row number every time, and the first column is not a primary key just because it comes first.

If no single field is unique, use a documented composite key such as agency_id plus reporting_period. Test the combined values for collisions in each release, and keep the component boundaries intact or encode them unambiguously rather than concatenating strings, so that “12” plus “34” and “123” plus “4” cannot collide.

Key validation is a gate, not a formality. For each file, count blank keys, distinct keys, duplicate-key groups and rows inside duplicate-key groups. Move blank-key rows and entire duplicate-key groups to an exception report before building any lookup structure. A dictionary or key-value map silently keeps only one row per duplicated key, and the dropped rows vanish from every total.

Key condition	Action
Unique and stable in both files	Proceed with keyed comparison
Blank	Mark the row unresolved
Duplicated	Resolve or report the whole group
Identifier changed	One removal plus one addition, unless evidence links them

Software cannot safely decide that person_17 and person_017 are the same record because the other fields look similar. Override the mechanical result only with a documented crosswalk, publisher confirmation or another authoritative link.

Classify Shared, Old-Only and New-Only Keys

With unresolved rows set aside, derive three sets: old-only keys (old minus new), new-only keys (new minus old) and shared keys (the intersection). New-only keys are added records; old-only keys are removed records. For each shared key, compare the selected shared fields under the declared policy; if any compared value differs the record is changed, otherwise unchanged.

For every changed record, write one detail row per changed field with the columns record_key, status, field, old_value and new_value. A record whose status moved from active to paused produces the row 101, changed, status, active, paused. This long-form layout is a practical machine-readable convention, not a standard.

Take these two synthetic releases.

Old CSV:

id	name	status
101	Alpha	active
102	Beta	active
103	Gamma	inactive

New CSV:

id	name	status
103	Gamma	inactive
101	Alpha	paused
104	Delta	active

The correct result:

Key	Result	Detail
104	Added	Only in the new release
102	Removed	Only in the old release
101	Changed	status: active → paused
103	Unchanged	All compared fields agree

Row 103 moved from last to first and produced nothing, which is the point of keying on id rather than position.

Finish with three reconciliation checks: old unique keys must equal removed plus matched keys; new unique keys must equal added plus matched keys; matched keys must equal changed plus unchanged keys. These are integrity checks on your own pipeline, not proof that the data is right. If one fails, inspect filtering, joins, duplicate handling and key construction. Unresolved rows stay outside these totals until resolved or disclosed.

Decide in Advance What Counts as a Changed Value

Write down how the comparison treats leading and trailing whitespace, letter case, NULL, empty and whitespace-only strings, missing columns, dates and time zones, leading zeros and numeric formatting. “1”, “1.0” and “01” are three different text strings; a typed numeric comparison calls them equal. Neither policy is universally right, but one of them has to be declared.

Identifiers such as ZIP codes, account codes and catalogue numbers should stay text when leading zeros carry meaning. Turning “01” into 1 erases a distinction and alters the published representation.

Blank, NULL, absent and whitespace-only are not the same value by default. One reasonable policy trims outer whitespace while keeping empty strings distinct from nulls; another dataset may demand exact raw-text comparison.

Volatile fields, such as an export timestamp regenerated on every run, may be excluded only when the exclusion is explicit and listed with a reason. Whatever normalization you apply for classification, keep the raw old and new values in the detail report, because normalization can hide a genuine source change. Above all, never infer one release as numbers and the other as text and then present the mismatch as a revision.

A Sorted Line Diff Is Not a Record Diff

Pick the method by the output you need:

Method	Best use	Main limitation
Sorted text diff	Quick checks on small, consistently serialized files	Compares lines, not identities
Stable-key script	Reproducible classification and detail output	Needs explicit key and comparison rules
DuckDB or another SQL engine	Schema inspection, database-style processing	You implement the matching
Visual browser comparison	Manual spot checks	Usually not key-aware

Line tools compare serialized lines. Sorting removes row-order noise, but an edited record still shows up as one deleted line and one added line, and a quoting or whitespace difference does the same. Sorting whole rows is not matching records by key.

The implementation-neutral procedure is: preserve both files; load them with identical parsing and type settings; compare headers and schemas; audit blank and duplicate keys; set unresolved rows aside; compute old-only, new-only and shared key sets; classify old-only as removed and new-only as added; compare the selected shared fields for shared keys; write the summary, field-level detail and exception outputs; run the reconciliation checks.

A short Python script can load a two-column CSV into a dictionary keyed by the first field and classify additions, removals and changed values; a community example on Stack Overflow shows the basic shape. Its assumptions do not cover duplicate keys or wider schemas, so run the key audit before you build the dictionary.

Browser-based comparison tools can be fine for visual review, but treat their privacy, capacity and performance statements as vendor claims until verified, and read the current terms before uploading a file you do not already publish.

Publish the Changelog Next to the Revised CSV

Ship the revised public CSV with a short changelog and a machine-readable detailed diff, and keep both source versions plus the comparison notes. A publication-ready changelog covers:

• Inputs: previous file, new file, source URLs, release or retrieval dates.
• Method: stable or composite key, parsing settings, text or typed comparison, normalization rules, excluded fields.
• Schema changes: added, removed and reordered columns, type changes, possible renames awaiting confirmation.
• Added and removed records: a count and a detail file for each.
• Changed fields: changed-record count, changed-field count, confirmed corrections if any, detail file.
• Unchanged and excluded: unchanged-record count, excluded fields with reasons.
• Unresolved key issues: blank-key rows, duplicate-key groups, how they were resolved or disclosed.
• Review: who confirmed suspected renames, changed identifiers and unusually large differences.

Reserve the word correction for a change confirmed by release notes, publisher documentation or reviewer sign-off. A mechanical comparison establishes only that a value changed.

The detail file keeps record_key, status, field, old_value and new_value. Blank and duplicate keys live in a separate exceptions file so nobody mistakes them for confirmed additions or removals.

Version numbering is optional. A Frictionless Data pattern proposes distinct increments for incompatible structural changes, appended data and corrections, and presents this explicitly as a pattern rather than a CSV standard; any documented policy works.

Before publishing, have a reviewer confirm suspected renames, changed identifiers and any surprisingly large difference. A surge in removals may be real, or it may be a parsing error, a key failure or a truncated export. Then publish the revised dataset and the changelog as linked public pages, using only public or synthetic data on anything public-facing. Two preserved releases, constant parsing assumptions, a separate schema report, a validated key and field-level detail are what make the comparison reproducible by someone who was not in the room.