How to Find and Remove Duplicate Rows in a Parquet File

Where the duplicates come from

Duplicates in Parquet are almost always a pipeline artifact rather than bad source data: a batch job re-ran and appended the same day twice, overlapping date ranges were exported, or several per-region files were stacked with UNION ALL and the overlap was never removed. The fix is the same in every case — first measure, then decide which copy wins.

Step 1: how many duplicates are there?

Drop the file into the SQL Workbench and ask for both counts at once:

SELECT
  (SELECT count(*) FROM 'sales.parquet') AS total_rows,
  (SELECT count(*) FROM (SELECT DISTINCT * FROM 'sales.parquet')) AS distinct_rows;

If the two numbers match, there are no exact duplicates — which does not mean there are no duplicated records. Any column that differs per load (ingested_at, source_file, a surrogate row id) makes every copy unique at the row level while the business content is repeated.

Step 2: find repeated keys

This is the check that actually matters. Group by the column that should be unique and keep only the groups with more than one row:

SELECT order_id, count(*) AS copies
FROM 'sales.parquet'
GROUP BY order_id
HAVING count(*) > 1
ORDER BY copies DESC
LIMIT 50;

Then look at one offender in full to decide what "duplicate" means here — byte-identical re-import, or two genuine versions of the same record?

SELECT * FROM 'sales.parquet'
WHERE order_id = 'A-1043'
ORDER BY updated_at;

For a composite key, group by all its parts: GROUP BY order_id, line_no.

Step 3: remove them

Exact duplicates collapse with DISTINCT:

SELECT DISTINCT * FROM 'sales.parquet';

Keeping one row per key — the useful version — is a window function. DuckDB's QUALIFY filters on it without wrapping the query in a subquery:

SELECT *
FROM 'sales.parquet'
QUALIFY row_number() OVER (
  PARTITION BY order_id
  ORDER BY updated_at DESC
) = 1;

Swap updated_at DESC for whatever defines "the good copy": the highest version number, the latest load timestamp, or source_file DESC. Add tie breakers to the ORDER BY if the sort column is not unique — otherwise which row survives is arbitrary and can change between runs.

Export the cleaned result as CSV from the workbench, then send it back through the CSV to Parquet converter if you need the output in Parquet again.

Traps worth knowing

• NULL keys. PARTITION BY order_id puts every NULL-keyed row into one partition, so all but one are dropped. Count them first (SELECT count(*) FROM 'sales.parquet' WHERE order_id IS NULL) and handle them separately.
• Near-duplicates. "ACME " and "acme" are different keys. Normalize in the partition expression: PARTITION BY lower(trim(customer)).
• Floats. Values that differ in the last bits look distinct to DISTINCT even when they print identically. Round in the comparison if that matters.

The pandas equivalent

import pandas as pd

df = pd.read_parquet("sales.parquet")
df = df.sort_values("updated_at").drop_duplicates(subset=["order_id"], keep="last")
df.to_parquet("sales_dedup.parquet", compression="zstd")

Correct, but it loads the whole file into memory and gives you no view of what was duplicated. Run the counting queries first — the row count you delete is worth understanding before you delete it. To confirm the result, drop the new file into the Parquet Viewer, or diff it against the original with the Parquet Diff tool.