Compare Two Parquet Files with DuckDB: Added, Deleted, and Changed Rows

The export still has five rows. So nothing changed? That is the trap. A customer can disappear while another arrives, and a plan change can hide in plain sight.

Play spot-the-difference below, then build the query that catches every change. You’ll use a full outer join, discover why NULL needs special treatment, and finish with an audit you can actually read: who arrived, who left, and whose details changed.

Your two exports, every row included

Here are the exact files we will compare. Download the Parquet files to use them locally, or run the examples here: the browser writes and reads its own copies. Your computer’s files are never uploaded.

Define the comparison before writing the join

For this walkthrough, id is a unique, non-NULL customer key. We compare name and plan, exactly and case-sensitively. Two NULL values count as unchanged; a NULL becoming a value counts as changed. Physical row order is irrelevant.

The sample setup creates previous and current views over the two files. On your machine, point those views at your own snapshots:

CREATE VIEW previous AS
SELECT id, name, plan FROM read_parquet('previous_snapshot.parquet');
CREATE VIEW current AS
SELECT id, name, plan FROM read_parquet('current_snapshot.parquet');

DuckDB reads Parquet directly, so you do not need to import either file into a permanent database. Compare explicit columns. An added ingestion timestamp should not make every customer look changed unless that field belongs in your comparison contract.

Check both keys first

A duplicate ID on both sides multiplies rows in the join. A NULL ID also breaks the presence checks we use below. Find those problems before interpreting the diff:

SELECT 'previous' AS snapshot, id, count(*) AS occurrences
FROM previous GROUP BY id HAVING id IS NULL OR count(*) > 1
UNION ALL
SELECT 'current', id, count(*)
FROM current GROUP BY id HAVING id IS NULL OR count(*) > 1;

Expected result: zero rows. If this query returns anything, stop and resolve the key definition. A composite key may be appropriate; arbitrarily keeping one duplicate can discard a meaningful record.

Try the failure case independently:

WITH example(id, name) AS (
  VALUES (1, 'Ada'), (1, 'Ada duplicate'), (2, 'Ben')
)
SELECT id, count(*) AS occurrences
FROM example GROUP BY id
HAVING id IS NULL OR count(*) > 1;

It returns ID 1 with two occurrences. The same validation belongs in an automated comparison job, where a nonempty result should fail the job instead of being silently ignored.

Classify every changed record

A full outer join retains keys found on either side. Because we checked that stored IDs cannot be NULL, a missing joined ID tells us which snapshot lacks the record:

SELECT coalesce(p.id, c.id) AS id,
       CASE WHEN p.id IS NULL THEN 'added'
            WHEN c.id IS NULL THEN 'deleted'
            ELSE 'changed' END AS change,
       p.name AS old_name, c.name AS new_name,
       p.plan AS old_plan, c.plan AS new_plan
FROM previous p FULL OUTER JOIN current c ON p.id = c.id
WHERE p.id IS NULL OR c.id IS NULL
   OR p.name IS DISTINCT FROM c.name
   OR p.plan IS DISTINCT FROM c.plan
ORDER BY id;

The SQL returns NULL for an unknown field and for fields on the missing side of the join. Read change to distinguish them: Ben exists in both snapshots but had no plan; Cy has no current record; Eli has no previous record. IDs 4 and 6 are unchanged and omitted.

SELECT c.id, c.name
FROM current c
LEFT JOIN previous p ON c.id = p.id
WHERE p.id IS NULL
ORDER BY c.id;

For a composite key, join on every key column and validate their combined uniqueness. Make the presence marker an explicitly non-NULL column; do not guess whether a row exists from an optional field such as plan.

Use NULL-safe comparisons

This looks plausible but is wrong for our contract:

WHERE p.plan <> c.plan

Comparisons involving NULL return NULL, which does not pass a WHERE filter. That would lose Ben’s change from NULL to free. Run the expressions side by side:

SELECT p.id, p.plan AS old_plan, c.plan AS new_plan,
       p.plan <> c.plan AS ordinary_inequality,
       p.plan IS DISTINCT FROM c.plan AS changed
FROM previous p JOIN current c ON p.id = c.id
ORDER BY p.id;

IS DISTINCT FROM reports Ben’s change as true and Finn’s NULL-to-NULL pair as false. DuckDB documents the corresponding NULL comparison semantics.

The predicate must list every field you intend to audit. When a new business column arrives, update the contract and the predicate together. Do not allow a schema change to silently shrink what “unchanged” means.

Handle schema changes and larger files deliberately

Inspect both schemas with DESCRIBE SELECT * FROM read_parquet(...) before comparing unfamiliar exports. Align types explicitly when the intended meaning is the same. Decide separately whether a missing column means an error, an intentional schema change, or a default value; automatically filling it with NULL can hide the distinction.

For decimals, timestamps, and floating-point measurements, write down the equality rule. Exact decimal comparison can suit currency; measured floating-point data may require a tolerance. Keep that tolerance in the SQL and the audit output.

Start with the exact join before introducing hashes. Equal hashes are not proof of equal rows, and hashing still requires reading the input. A fingerprint can help a carefully designed workflow, but it does not remove the need to reason about collisions and schema consistency. The Hashfuncs article covers typed fingerprints in more detail.

On larger datasets, project only the needed columns, inspect the plan, and allow for join memory and temporary disk use. Partitioning both exports by the same stable key can make comparisons manageable; a partition based on a field that changes can put the two versions of a record in different places.

To save the result, wrap the diff query in COPY (...) TO 'changes.parquet' (FORMAT PARQUET) on your machine. Preserve the source snapshot identifiers alongside it. You now have an explicit audit of which records changed and how, with the assumptions needed to trust it.