`diff_tables`¶

Compare two files and return what differs. Where compare_schemas diffs the column metadata, diff_tables diffs the actual rows. The mode parameter trades off how rows are matched, from coarse whole-row membership to precise cell-level change detection.

When to use¶

To answer "what records changed between these two files / versions?" without pulling both tables and comparing them yourself. Use ordered or join when you need to know which cells changed, not just which whole rows are unique to a side.

Modes¶

set (default): each row is keyed by its whole-row content (every column, in order, rendered to text). Columns are compared positionally, so the two files should share the same column order. Because matching is on rendered values, it works across formats (a CSV row matches the equivalent Parquet row). Returns the rows unique to each side.
ordered: lines up row i of A with row i of B and compares cell by cell over the shared columns. Reports matched rows that differ (with the differing column names) plus trailing rows unique to the longer side.
join: matches rows on the on key column(s) (matched by name), then reports keys added (in B only), removed (in A only), and changed (matched keys whose non-key cells differ, with the differing column names).

Input schema¶

Parameter	Type	Required?	Default	Description
`path_a`	string	yes	(no default)	Path to the first file (side A)
`path_b`	string	yes	(no default)	Path to the second file (side B)
`mode`	string	no	`set`	`set`, `ordered`, or `join`. `set` refuses the six tolerance and ignore options below
`on`	string[]	for `join` only	(no default)	Key column(s) for `join`, matched by name
`table_a`	string	no	(no default)	Specific table to read from A (multi-table sources)
`table_b`	string	no	(no default)	Specific table to read from B (multi-table sources)
`limit`	int	no	server default (1000)	Max rows returned per side. `0` = unlimited
`unlimited`	bool	no	`false`	Lift the 2,000,000-row file-loader cap for both files
`b_db`	object	no	-	Compare against a live database table instead of a second file: `{"connection": "NAME", "table": "SCHEMA.TABLE"}`. Replaces `path_b` / `open_tab_b`.
`tolerance`	number	no	(no default)	Numbers within this absolute difference count as the same; never applied to key columns
`tolerance_pct`	number	no	(no default)	Numbers within this percentage of the larger value count as the same
`time_tolerance_seconds`	number	no	(no default)	Times within this many seconds count as the same; a date counts as midnight
`ignore_case`	bool	no	`false`	Compare text ignoring upper and lower case, key columns included
`ignore_spaces`	bool	no	`false`	Compare text ignoring leading, trailing and repeated spaces, key columns included
`ignore_columns`	string[]	no	(no default)	Columns left out of the comparison; a key column cannot be ignored

Response shape¶

For mode: "set":

{
  "mode": "set",
  "only_in_a": { "schema": [...], "rows": [...], "row_count": <n>, "truncated": <bool>, ... },
  "only_in_b": { "schema": [...], "rows": [...], "row_count": <n>, "truncated": <bool>, ... },
  "only_in_a_count": <n>,
  "only_in_b_count": <n>,
  "shared_keys": <n>
}

For mode: "ordered" / "join" the response additionally carries the changed rows:

{
  "mode": "join",
  "only_in_a": { ... }, "only_in_b": { ... },
  "changed_a": { ... }, "changed_b": { ... },
  "changed": [ { "row_a": <i>, "row_b": <j>, "changed_columns": ["name", ...] }, ... ],
  "only_in_a_count": <n>, "only_in_b_count": <n>,
  "changed_count": <n>, "unchanged_count": <n>,
  "within_tolerance_count": <n>
}

within_tolerance_count counts the cells whose text differs but that passed a tolerance or an ignore option. It is always present in ordered and join responses and is 0 when no such option is set.

only_in_a / only_in_b (and changed_a / changed_b) are each a read_table-style payload (so limit and the per-cell byte cap apply to each). changed_a[k] and changed_b[k] line up with changed[k], which names the differing columns for that pair. For set, shared_keys is the number of distinct row keys present in both files. Unchanged rows are not returned.

Example call¶

{
  "name": "diff_tables",
  "arguments": {
    "path_a": "/tmp/users_v1.csv",
    "path_b": "/tmp/users_v2.csv",
    "mode": "join",
    "on": ["id"]
  }
}

Comparing against a database table¶

Pass b_db instead of path_b to check a file against a live table, for example to confirm that a load landed:

{
  "path_a": "orders.csv",
  "b_db": { "connection": "warehouse", "table": "public.orders" },
  "mode": "join",
  "on": ["id"]
}

connection is the name of a saved database connection. The list_db_connections tool enumerates them; they are configured under Database Connections.
table is SCHEMA.TABLE, or CATALOG.SCHEMA.TABLE on Snowflake, Databricks and BigQuery. An unqualified name uses the connection's own database.
Side A stays the file, so only_in_a means "in the file, not the table".
The database side is read under the normal row cap, so a large table is compared on its first rows.

The GUI equivalent is Compare with a Database Table; on the command line it is --diff-db.

Tolerance¶

For ordered and join modes these fields make the comparison forgiving. set mode refuses them.

tolerance: numbers at most this far apart count as the same.
tolerance_pct: numbers within this percentage of the larger value count as the same. A pair passes if it is within either number tolerance.
time_tolerance_seconds: dates and times at most this many seconds apart count as the same. A date counts as midnight.
ignore_case and ignore_spaces: text compares without regard to case, or trimmed with runs of spaces as one. Join keys follow these too; number and time tolerance never apply to keys.
ignore_columns: columns left out of the comparison. They still appear in the returned rows.

The response carries within_tolerance_count, the number of cells that differed in text but passed the tolerance.

diff_tables¶