import polars as pl
source = pl.DataFrame(
{
"order_id": [1001, 1002, 1003, 1004, 1005],
"customer": ["Ana", "Ben", "Cho", "Dev", "Eli"],
"amount": [25.00, 40.50, 12.75, 99.99, 5.00],
"status": ["shipped", "shipped", "pending", "shipped", "cancelled"],
}
)
target = pl.DataFrame(
{
"order_id": [1001, 1002, 1004, 1005, 1006],
"customer": ["Ana", "Ben", "Dev", "Eli", "Fay"],
"amount": [25.00, 45.50, 99.99, 5.00, 18.25],
"status": ["shipped", "shipped", "shipped", "cancelled", "pending"],
}
)Comparing Two Tables
A very common data question is how do these two tables differ? Maybe you refactored a pipeline and want to know whether its output changed, migrated a table from one database to another, or received a new extract of a data feed. In each case there’s a source table (the reference, or the data you expect) and a target table (the data you want to check), and you want to know:
- which records are missing from the target
- which records in the target are extra
- which records have values that differ, and in which columns
- whether the columns themselves (names, types, order) changed
Pointblank answers all of these with the compare() function, and the same comparison is available as a validation step through Validate.tbl_match() and on the command line through pb compare.
A First Comparison
Let’s make two small tables of orders. The target is a later extract of the same data: one order had its amount corrected, one order is gone, and a new order has appeared.
Since every order has a unique order_id, we’ll use it as the key to line up the rows of the two tables:
import pointblank as pb
comparison = pb.compare(source, target, keys="order_id")
comparison| Table Comparison | |
source (5 rows) → target (5 rows), aligned by key order_idDIFFERENT 3 of 6 rows fail (50.0%) match 3changed 1missing 1extra 1 |
|
| Column mismatches (1 of 3 columns) | |
|---|---|
| amount | 1 of 4 matched rows differ (25.0%) |
| Changed rows (1 row) | |
| 1002 | amount: 40.5 → 45.5 (+5) |
| Missing from target (1 row) | |
| 1003 | customer: Cho amount: 12.75 status: pending |
| Extra in target (1 row) | |
| 1006 | customer: Fay amount: 18.25 status: pending |
The report’s header gives the verdict and a breakdown of the rows. Below it, each section shows one kind of difference: which columns have mismatched values (and how often), then samples of the changed, missing, and extra rows. For changed rows, only the differing values are shown, as old → new with the numeric difference alongside.
Row Statuses
Every row that can be aligned between the two tables gets exactly one status:
| Status | Meaning |
|---|---|
match |
the row is in both tables and all compared values are equal |
changed |
the row is in both tables but at least one compared value differs |
missing |
the row is in the source but not in the target |
extra |
the row is in the target but not in the source |
dup_key |
the row’s key appears more than once in either table, so it can’t be aligned |
The counts are available directly on the Comparison object:
comparison.status_counts{'match': 3, 'changed': 1, 'missing': 1, 'extra': 1, 'dup_key': 0}comparison.n_changed, comparison.n_missing, comparison.n_extra(1, 1, 1)A Comparison is truthy only if the tables match, so it can be used directly in a test or an if statement:
bool(comparison)FalseGetting the Differing Rows
Use the rows() method to get the rows with a given status as a table. Changed rows come with a <column>_source and a <column>_target column for each compared column:
comparison.rows("changed")
shape: (1, 7)
| order_id | customer_source | customer_target | amount_source | amount_target | status_source | status_target |
|---|---|---|---|---|---|---|
| i64 | str | str | f64 | f64 | str | str |
| 1002 | "Ben" | "Ben" | 40.5 | 45.5 | "shipped" | "shipped" |
Missing rows come from the source and extra rows from the target:
comparison.rows("missing")
shape: (1, 4)
| order_id | customer | amount | status |
|---|---|---|---|
| i64 | str | f64 | str |
| 1003 | "Cho" | 12.75 | "pending" |
comparison.rows("extra")
shape: (1, 4)
| order_id | customer | amount | status |
|---|---|---|---|
| i64 | str | f64 | str |
| 1006 | "Fay" | 18.25 | "pending" |
Only up to limit= rows (100 by default) are collected, so this is safe even for very large tables. For a per-column view of the mismatches, use column_summary():
comparison.column_summary()
shape: (3, 6)
| column | target_column | dtype_source | dtype_target | n_mismatch | f_mismatch |
|---|---|---|---|---|---|
| str | str | str | str | i64 | f64 |
| "customer" | "customer" | "String" | "String" | 0 | 0.0 |
| "amount" | "amount" | "Float64" | "Float64" | 1 | 0.25 |
| "status" | "status" | "String" | "String" | 0 | 0.0 |
How Rows Are Aligned
The keys= argument decides how the rows of the two tables are lined up:
- By key (
keys="order_id", or a list for a composite key): rows are matched on the key values, so their order doesn’t matter. This is the most informative mode, and the one to use whenever the data has an identifier. - By position (
keys=None, the default): the first row of the source is compared with the first row of the target, and so on. This suits outputs that are supposed to be identical row for row. - As multisets (
keys="*"): whole rows are compared regardless of their order, with duplicates counted. This is for tables that have no key at all.
Here’s what positional alignment makes of the same two tables. Since a row was removed and another added, most rows no longer line up:
pb.compare(source, target).status_counts{'match': 1, 'changed': 4, 'missing': 0, 'extra': 0, 'dup_key': 0}Tolerances, Normalization, and Nulls
Not every difference matters. Numeric and datetime columns can be compared with a tolerance, given for all numeric columns or per column. An absolute tolerance of 5 absorbs the corrected amount:
pb.compare(source, target, keys="order_id", tolerance={"amount": 5}).n_changed0Tolerances can be absolute (atol), relative to the source value (rtol), or both, as in tolerance={"amount": {"atol": 0.01, "rtol": 1e-6}}. Two values are equal if |source - target| <= atol + rtol * |source|. Datetime tolerances are durations like "1s", "500ms", or "2m".
String values can be normalized before comparing with normalize=. For example, normalize={"customer": ["strip", "lower"]} ignores surrounding whitespace and letter case (the available operations are "strip", "lower", "upper", "collapse_whitespace", and {"round": n}). Reports always show the original values.
By default, two nulls count as equal, and NaN values in floating-point columns are treated as nulls. Use null_equal=False to count two nulls as a difference.
Schema Differences
Column differences are reported in the Schema differences section of the report and in the schema_diff attribute. Suppose the target renamed a column and added another:
target_v2 = target.rename({"customer": "customer_name"}).with_columns(
region=pl.lit("EU")
)
comparison_v2 = pb.compare(source, target_v2, keys="order_id")
comparison_v2.schema_diffSchemaDiff(only_in_source=['customer'], only_in_target=['customer_name', 'region'], dtype_changed=[], renamed=[], case_only=[], reordered=False)With the default schema="strict", any schema difference means the tables don’t match and every row counts as failing. Two arguments help here. column_map= pairs up renamed columns, and schema="common" compares only the columns that both tables share (still reporting the schema differences, but without failing any rows):
pb.compare(
source,
target_v2,
keys="order_id",
column_map={"customer": "customer_name"},
schema="common",
)| Table Comparison | |
source (5 rows) → target (5 rows), aligned by key order_idDIFFERENT 3 of 6 rows fail (50.0%) match 3changed 1missing 1extra 1 schema: common columns |
|
| Schema differences | |
|---|---|
| region | only in target |
| customer | renamed → customer_name (via column_map=) |
| Column mismatches (1 of 3 columns) | |
| amount | 1 of 4 matched rows differ (25.0%) |
| Changed rows (1 row) | |
| 1002 | amount: 40.5 → 45.5 (+5) |
| Missing from target (1 row) | |
| 1003 | customer: Cho amount: 12.75 status: pending |
| Extra in target (1 row) | |
| 1006 | customer_name: Fay amount: 18.25 status: pending region: EU |
To compare just some of the columns, list them in columns=. The schema check is then limited to the keys and those columns.
Duplicate Keys
A key that appears more than once in either table can’t be aligned unambiguously, so its rows get the dup_key status (they always count as failing). By default the report lists the duplicated keys with their number of occurrences in each table. With dup_keys="compare", the report also compares the duplicated rows pairwise to show which columns differ between them.
Table Comparison as a Validation Step
The tbl_match() validation method uses the same comparison, which makes it a validation step like any other: it can have thresholds and actions, and it shows up in the validation report. The table being validated is the target, and tbl_compare= is the source. Every aligned row is a test unit, so thresholds can be set as a proportion of rows:
validation = (
pb.Validate(data=target, tbl_name="orders", thresholds=(0.1, 0.25))
.tbl_match(tbl_compare=source, keys="order_id")
.interrogate()
)
validation
Note
In Pointblank v1.0.1 and earlier, tbl_match() had a single test unit for the whole table. Steps still pass or fail in the same situations, but fractional thresholds now refer to the share of differing rows. To react to any difference, as before, use an absolute threshold of 1.
Three of the six aligned rows differ, exceeding both the ‘warning’ and ‘error’ thresholds. The step report holds the full comparison:
validation.get_step_report(i=1)| Report for Validation Step 1 ✗ ASSERTION tbl_match(keys='order_id')tbl_compare (5 rows) → orders (5 rows), aligned by key order_idDIFFERENT 3 of 6 rows fail (50.0%) match 3changed 1missing 1extra 1 |
|
| Column mismatches (1 of 3 columns) | |
|---|---|
| amount | 1 of 4 matched rows differ (25.0%) |
| Changed rows (1 row) | |
| 1002 | amount: 40.5 → 45.5 (+5) |
| Missing from target (1 row) | |
| 1003 | customer: Cho amount: 12.75 status: pending |
| Extra in target (1 row) | |
| 1006 | customer: Fay amount: 18.25 status: pending |
The data extract for the step contains the failing rows from both tables, with a _status_ column and source/target value pairs:
validation.get_data_extracts(i=1, frame=True)
shape: (3, 8)
| order_id | _status_ | customer_source | customer_target | amount_source | amount_target | status_source | status_target |
|---|---|---|---|---|---|---|---|
| i64 | str | str | str | f64 | f64 | str | str |
| 1002 | "changed" | "Ben" | "Ben" | 40.5 | 45.5 | "shipped" | "shipped" |
| 1003 | "missing" | "Cho" | null | 12.75 | null | "pending" | null |
| 1006 | "extra" | null | "Fay" | null | 18.25 | null | "pending" |
All the comparison arguments (keys=, tolerance=, schema=, and so on) are available in tbl_match(), and they can be written in YAML too:
tbl: orders_target.csv
steps:
- tbl_match:
tbl_compare: orders_source.csv
keys: order_id
tolerance:
amount: 0.01Reconciling Aggregates
Sometimes two tables shouldn’t have the same rows but should add up to the same numbers: a warehouse table loaded from a source system, a summary table built from detailed transactions, or two systems that record the same activity at different levels of detail. For these, the tbl_reconcile() validation method compares aggregates instead of rows. It groups both tables by the keys= columns, aggregates the measures= columns (with agg="sum" by default, or "count", "mean", "min", or "max"), and compares the results group by group. Each group is a test unit.
Here, the orders in the target have been loaded at a finer grain than in the source, so we reconcile the total amount per customer:
order_lines = pl.DataFrame(
{
"order_id": [1001, 1002, 1002, 1004, 1005, 1006],
"customer": ["Ana", "Ben", "Ben", "Dev", "Eli", "Fay"],
"amount": [25.00, 40.50, 5.00, 99.99, 5.00, 18.25],
}
)
validation = (
pb.Validate(data=order_lines, tbl_name="order_lines")
.tbl_reconcile(tbl_compare=source, keys="customer", measures="amount")
.interrogate()
)
validation.get_step_report(i=1)| Report for Validation Step 1 ✗ ASSERTION tbl_reconcile(keys='customer', measures='amount', agg='sum')tbl_compare (5 groups) → order_lines (5 groups), aligned by key customerDIFFERENT 3 of 6 groups fail (50.0%) match 3changed 1missing 1extra 1 sum by customer |
|
| Column mismatches (1 of 1 columns) | |
|---|---|
| amount_sum | 1 of 4 matched groups differ (25.0%) |
| Changed groups (1 group) | |
| Ben | amount_sum: 40.5 → 45.5 (+5) |
| Missing from target (1 group) | |
| Cho | amount_sum: 12.75 |
| Extra in target (1 group) | |
| Fay | amount_sum: 18.25 |
Ben’s two order lines add up to more than his order in the source, Cho is missing, and Fay is new. Without keys=, tbl_reconcile() compares grand totals (a single test unit), and agg="count" without measures= compares the number of rows in each group. Tolerances, column_map=, and partitions= work as they do in tbl_match(). Rows with a null key form a group of their own, which is matched with the other table’s null-key group.
Exporting the Results
The results can be serialized with to_dict() or to_json(), which include the counts, the schema differences, and (by default) samples of the differing rows. If the data could contain sensitive values, leave the samples out:
print(comparison.to_json(samples=False)){
"source_name": "source",
"target_name": "target",
"mode": "keyed",
"keys": [
"order_id"
],
"compared_columns": [
"customer",
"amount",
"status"
],
"column_map": {},
"schema_mode": "strict",
"null_equal": true,
"partitions": 1,
"n_source_rows": 5,
"n_target_rows": 5,
"n": 6,
"n_passed": 3,
"n_failed": 3,
"identical": false,
"status_counts": {
"match": 3,
"changed": 1,
"missing": 1,
"extra": 1,
"dup_key": 0
},
"column_mismatches": {
"customer": 0,
"amount": 1,
"status": 0
},
"schema_diff": {
"matches": true,
"only_in_source": [],
"only_in_target": [],
"dtype_changed": [],
"renamed": [],
"case_only": [],
"reordered": false
}
}The tabular report can be saved as HTML, like any Great Tables table:
comparison.get_tabular_report().write_raw_html("orders_diff.html")From the Command Line
The pb compare command runs a comparison on files, database tables, or built-in datasets, prints a summary, and can save the HTML report and the JSON results:
pb compare orders_source.csv orders_target.csv --key order_id --output-html diff.htmlIts exit code makes it easy to use in scripts and CI pipelines: 0 if the tables match, 1 if they differ, and 2 if there was an error. Use --format json to print the results as JSON. All the comparison settings have command-line options (e.g., --tolerance amount=0.01, --schema common); see the CLI reference for the full list.
For tables that are too large to fit in memory, see Comparing Large Files.