import tempfile
from pathlib import Path
import numpy as np
import polars as pl
n = 200_000
rng = np.random.default_rng(23)
source = pl.DataFrame(
{
"order_id": np.arange(n),
"amount": rng.normal(100, 20, n).round(2),
"region": rng.choice(["north", "south", "east", "west"], n),
}
)
target = (
source.filter(pl.col("order_id") % 1000 != 7) # 200 orders missing
.with_columns( # 400 amounts changed
amount=pl.when(pl.col("order_id") % 500 == 3)
.then(pl.col("amount") + 1)
.otherwise("amount")
)
.vstack(source.tail(50).with_columns(pl.col("order_id") + n)) # 50 extra orders
.sample(fraction=1.0, shuffle=True, seed=1)
)
data_dir = Path(tempfile.mkdtemp())
source.write_csv(data_dir / "source.csv")
target.write_csv(data_dir / "target.csv")Comparing Large Files
Here’s a situation that comes up all the time: a pipeline run produces a target CSV file from a source CSV file, and you need to check that nothing went wrong. Specifically, you want to find
- records missing from the target,
- extra records in the target,
- records whose column values differ, and
- schema or column-name mismatches,
and the files are big enough that loading both of them into memory is slow, or impossible.
This article looks at why the usual approaches struggle with this, and how to do it with Pointblank’s compare() function and the pb compare command.
Why the Usual Approaches Fall Short
A pandas merge. Reading both files and doing an outer merge on the key (with indicator=True) does identify missing and extra rows, and the merged frame can be compared column by column to find changed values. But it needs both files in memory plus the merged result, which is wider than either input. For large files that’s where memory runs out. Nulls take extra care too, since NaN != NaN.
Python sets. Turning each file’s rows into a set of tuples and taking set differences is quick to write, but it can’t tell a changed row from a missing one plus an extra one, can’t say which column changed, silently collapses duplicate rows, and still holds every row of both files in memory (as Python objects, which is worse than a DataFrame).
Reading in chunks. Processing the files chunk by chunk keeps memory in check, but a row’s counterpart in the other file is usually in a different chunk, unless both files happen to be sorted the same way. Making chunked comparison correct means partitioning both files by key, so that each key’s rows from both files land in the same chunk. That’s a lot of code to get right.
How Pointblank Approaches It
Three things make compare() suitable for large files:
- Files are scanned, not loaded. CSV and Parquet paths are read lazily (with Polars, or DuckDB), so the comparison is a query plan rather than two DataFrames in memory.
- The work happens in the engine. The join on the key, the per-column comparisons, and the counting are all done by the query engine. Python only receives the counts and small samples of the differing rows (the
limit=of each sample). - Chunking done correctly. With
partitions=N, the comparison runs in N passes, each covering the keys whose hash falls into one bucket, on both tables. A key’s rows always land in the same pass, so the results are exactly those of a single-pass comparison, while each pass only joins about1/Nof the rows.
Let’s try it on a pair of CSV files. We’ll generate a source file with 200,000 orders and a target file in which some orders are missing, some amounts changed, a few orders were added, and the rows were shuffled:
Now we compare the two files by giving compare() their paths:
import pointblank as pb
comparison = pb.compare(
data_dir / "source.csv",
data_dir / "target.csv",
keys="order_id",
)
comparison| Table Comparison | |
/tmp/tmpqckpol4v/source.csv (200,000 rows) → /tmp/tmpqckpol4v/target.csv (199,850 rows), aligned by key order_idDIFFERENT 650 of 200,050 rows fail (0.3%) match 199,400changed 400missing 200extra 50 |
|
| Column mismatches (1 of 2 columns) | |
|---|---|
| amount | 400 of 199,800 matched rows differ (0.2%) |
| Changed rows (showing 10 of 400 rows) | |
| 3 | amount: 53.62 → 54.62 (+1) |
| 503 | amount: 115.89 → 116.89 (+1) |
| 1003 | amount: 104.27 → 105.27 (+1) |
| 1503 | amount: 117.92 → 118.92 (+1) |
| 2003 | amount: 56.37 → 57.37 (+1) |
| 2503 | amount: 119.27 → 120.27 (+1) |
| 3003 | amount: 88.82 → 89.82 (+1) |
| 3503 | amount: 58.09 → 59.09 (+1) |
| 4003 | amount: 86.61 → 87.61 (+1) |
| 4503 | amount: 96.4 → 97.4 (+1) |
| Missing from target (showing 10 of 200 rows) | |
| 7 | amount: 112.12 region: north |
| 1007 | amount: 94.3 region: south |
| 2007 | amount: 88.68 region: north |
| 3007 | amount: 140 region: west |
| 4007 | amount: 108.94 region: west |
| 5007 | amount: 108.42 region: west |
| 6007 | amount: 78.28 region: north |
| 7007 | amount: 68.5 region: north |
| 8007 | amount: 58.93 region: east |
| 9007 | amount: 95.88 region: south |
| Extra in target (showing 10 of 50 rows) | |
| 399950 | amount: 104.47 region: south |
| 399951 | amount: 102.32 region: east |
| 399952 | amount: 88.29 region: north |
| 399953 | amount: 91.71 region: east |
| 399954 | amount: 103.78 region: south |
| 399955 | amount: 134.2 region: south |
| 399956 | amount: 133.98 region: north |
| 399957 | amount: 107.84 region: east |
| 399958 | amount: 76.61 region: north |
| 399959 | amount: 90.26 region: east |
All four questions are answered in one place: missing and extra rows, changed rows (with the amount column identified as the one that differs), and the schema check in the header.
The same comparison in four partitions gives identical results:
partitioned = pb.compare(
data_dir / "source.csv",
data_dir / "target.csv",
keys="order_id",
partitions=4,
)
partitioned.status_counts == comparison.status_countsTrueChoosing an Engine and a Number of Partitions
When given file paths, compare() scans them with Polars, which is the fastest option. DuckDB uses less memory, and combined with partitions it uses the least. To use DuckDB, read the files as DuckDB relations and pass those to compare():
import duckdb
con = duckdb.connect()
comparison = pb.compare(
con.read_csv("source.csv"),
con.read_csv("target.csv"),
keys="order_id",
partitions=8,
)Here’s how the options compare on two CSV files with 10 million rows and 10 columns each (about 730 MB per file), with the rows of the target shuffled. Peak memory is the memory the process actually owns (its physical footprint):
| Approach | Wall time | Peak memory |
|---|---|---|
| Polars: read both files, then a full join (for reference) | 2.1 s | 5.7 GB |
compare() on file paths (Polars) |
5.7 s | 4.2 GB |
compare() on file paths (Polars), partitions=4 |
6.8 s | 1.6 GB |
compare() on file paths (Polars), partitions=8 |
10.2 s | 1.1 GB |
compare() on DuckDB relations |
6.8 s | 1.6 GB |
compare() on DuckDB relations, partitions=8 |
19.0 s | 0.8 GB |
And with 100 million rows (about 7.4 GB per file), where comparing without partitions needs more memory than most machines have:
| Approach | Wall time | Peak memory |
|---|---|---|
compare() on file paths (Polars), partitions=8 |
139 s | 6.6 GB |
compare() on file paths (Polars), partitions=16 |
217 s | 4.4 GB |
compare() on DuckDB relations, partitions=8 |
166 s | 2.0 GB |
Every approach found exactly the expected differences. The numbers will vary with your machine and data, but the pattern is consistent: partitions trade time (each pass reads the files again) for memory, Polars is the faster engine, and DuckDB the more memory-efficient one. Some practical guidance:
- If the files fit comfortably in memory, use the defaults.
- If memory is tight, increase
partitions=until the peak memory is acceptable; for the lowest memory use, read the files as DuckDB relations. - Setting DuckDB’s own
memory_limit(so it can spill to disk) without partitions doesn’t hold memory to the limit: with a 4 GB limit, the 100-million-row comparison peaked at 6.3 GB. Partitions give more predictable memory use. - A good key matters most: with
keys=, row order doesn’t matter, and partitioning is possible. Without a key, rows are compared by position (or as multisets, withkeys="*"), and partitions can’t be used.
You can run the same benchmark on your own machine with the script in the Pointblank repository (benchmark/compare_large_files.py).
Tables in Databases
Tables don’t have to be files. If both tables are in the same database (e.g., two Ibis tables from one DuckDB, PostgreSQL, or Snowflake connection), the comparison runs entirely inside the database, and only the counts and samples are transferred. Tables from different databases are first brought into Polars, so for large tables it’s better to have both in one place.
In Scripts and CI Pipelines
The pb compare command does all of this from the command line, and its exit code tells a script or CI job whether the files match (0), differ (1), or couldn’t be compared (2):
pb compare source.csv target.csv --key order_id --engine duckdb --partitions 8 \
--output-html diff.html --output-json diff.jsonThe HTML report can be kept as a build artifact for anyone to look at. If the data is sensitive, add --no-samples to keep row values out of the JSON output. To make the comparison one check among others in a validation, use Validate.tbl_match(), which accepts the same settings, including partitions= (see Comparing Two Tables).