How to compare two CSV files and find what changed

Two exports of the same report, taken a week apart, and the question is always the same: what actually changed? Eyeballing them fails past a few dozen rows, and a spreadsheet diff tells you which cells differ without telling you which records were added or dropped.

Decide what identifies a row

Every meaningful comparison starts with a key — the column, or set of columns, that identifies the same real-world record in both files. Usually an order ID, a customer ID, or a date plus a product code.

Without a key you can only compare files as unordered bags of rows, which detects that something changed but never what. Establishing the key first is the whole job.

Find rows that exist in only one file

A FULL OUTER JOIN on the key puts both versions side by side and makes additions and removals fall out of which side is null.

SELECT
  COALESCE(a.order_id, b.order_id) AS order_id,
  CASE
    WHEN a.order_id IS NULL THEN 'added'
    WHEN b.order_id IS NULL THEN 'removed'
    ELSE 'in both'
  END AS status
FROM week_1 a
FULL OUTER JOIN week_2 b
  ON a.order_id = b.order_id
WHERE a.order_id IS NULL OR b.order_id IS NULL;

Find rows that exist in both but changed

For rows present on both sides, compare the value columns. Use IS DISTINCT FROM rather than <> — plain inequality returns null when either side is null, so genuinely changed rows silently vanish from the results.

SELECT
  a.order_id,
  a.status   AS old_status,
  b.status   AS new_status,
  a.revenue  AS old_revenue,
  b.revenue  AS new_revenue
FROM week_1 a
JOIN week_2 b
  ON a.order_id = b.order_id
WHERE a.status  IS DISTINCT FROM b.status
   OR a.revenue IS DISTINCT FROM b.revenue;

Summarize before reading the detail

A count per change type tells you whether you are looking at a routine update or something went wrong upstream. Three added rows is a normal week; four thousand removed rows is a broken export.

SELECT
  CASE
    WHEN a.order_id IS NULL THEN 'added'
    WHEN b.order_id IS NULL THEN 'removed'
    WHEN a.revenue IS DISTINCT FROM b.revenue THEN 'changed'
    ELSE 'unchanged'
  END        AS change_type,
  COUNT(*)   AS rows
FROM week_1 a
FULL OUTER JOIN week_2 b
  ON a.order_id = b.order_id
GROUP BY change_type
ORDER BY rows DESC;

Watch for false differences

Most surprising diffs are formatting, not data. Trailing whitespace, a changed date format, and numbers exported as text in one file but not the other all register as changes.

Normalizing inside the comparison keeps both source files untouched while removing the noise.

SELECT a.order_id
FROM week_1 a
JOIN week_2 b
  ON a.order_id = b.order_id
WHERE TRIM(LOWER(a.customer_name)) IS DISTINCT FROM TRIM(LOWER(b.customer_name));

Doing it without writing the SQL

NoEgress has a dataset comparison view that takes two opened tables and a key column and produces the added, removed, changed, and unchanged breakdown directly. It runs the same kind of query described above, locally, so comparing two confidential exports never involves uploading either one.