Key identity contract (exact-v1)

This page says when Paridity treats a source row and a target row as the same entity. The default contract is exact-v1. A mapping can instead declare the exact-v1-trim-ws profile, which is exact-v1 with one change: leading and trailing ASCII whitespace is removed from string key values before matching (see "The trim profile" below). Every statement below names the code that implements it. The verification table at the end is generated from the code and checked in CI.

What "same row" means

A mapping declares its key columns. For every row it reads, Paridity builds the row's key from the values of those columns exactly as the database driver returns them. It then writes that key as a JSON object with the column names in sorted order. One adjustment is made: a decimal value with no fractional part is written as an integer, so the decimal 1.0 and the integer 1 are the same key (_canonical_key_json in src/workers/sql/activities.py).

Typed (non-string) key values are rendered to one cross-engine form by the connector before the key is built: timestamps as UTC instants (for example, Oracle TIMESTAMP WITH TIME ZONE keys are projected with SYS_EXTRACT_UTC in extract_keys in src/connectors/oracle.py, #1217) and whole-number decimals as integers. String keys are never rendered by the connector - under the trim profile they MATCH on their trimmed form, but each side keeps and is fetched by its own raw key (see "The trim profile" below).

Two rows are the same entity only when their keys are identical, character for character. The source and target keys are matched by exact text equality in the platform's own database (run.key_hash_staging, joined on key_hash_key in src/workers/sql/activities.py).

For key columns the platform never applies:

• case folding (SMITH is not smith);
• accent folding (Müller is not Muller);
• Unicode normalisation (a precomposed é is not e followed by a combining accent);
• whitespace trimming. The trim_strings setting applies to compared column values, not to keys (src/services/canonicalization.py shapes the row comparison; the key is built from the raw values above);
• numeric_scale or timestamp_precision. These also shape the row comparison only.

A composite key is one JSON object that holds every key column, so each column must match on its own. NULL is a value like any other: a NULL key column matches a NULL on the other side.

Each side must have at most one row per key. The staging table's primary key (run_id, side, key_hash_key) refuses a second row with the same key. The run then stops as a Config refusal (Duplicate keys; config_error since #3104) - a retry cannot change it, the key columns or the scope filter must. It never picks one of the two rows (_stage_side in src/workers/sql/activities.py, DuplicateRowsPerKeyError in src/exceptions.py).

What this means in practice

• A source row keyed SMITH and a target row keyed smith are two different keys. Paridity reports one missing row and one extra row. It does not report a match with a field difference. The same happens with accent variants.
• A key that a replication tool trims, pads or re-cases on the way to the target shows up the same way, as missing plus extra, because the key is compared before any content setting applies.
• The common case is a fixed-width CHAR key on the source ('MAT-001 ') that the replication tool writes to a VARCHAR key as 'MAT-001' (GoldenGate trims trailing spaces by default). Identical data still passes, because the proof compares trimmed values. But when the proof fails for any other reason, every such row is reported missing in target and missing in source, and the real difference is never compared field by field. The run detail then shows a Key format mismatch notice with how many of the reported rows have a partner that matches once leading and trailing ASCII whitespace is removed (the same six characters trim_strings removes). The notice changes no verdict and no evidence; it tells you those rows are probably the same rows (_detect_whitespace_key_pairs in src/workers/sql/activities.py, recorded in run.runs.key_representation_detail). To have those rows matched and compared field by field, declare the trim profile on the mapping (next section).
• A decimal key keeps its written scale unless it is a whole number: 1.50 and 1.500 are different keys (_canonical_key_json).

If your pipeline changes key values on purpose, reconcile a view that restores the original values on the target. The alternative is a case-insensitive identity profile, which V1 does not offer (see the last section).

How Paridity enforces this against the database's own rules

A database compares strings by its own collation. Many collations treat Key, key and KEY as equal. Paridity therefore does not let the database's collation decide identity for any predicate it asks the database to run.

Keyed lookup (#2555). When Paridity fetches rows by key, the query keeps the database's native key match so the index can be used. For each string key column whose native match is not already byte-exact, it adds a byte-exact form of the same match: for example COLLATE Latin1_General_BIN2 on SQL Server, ::TEXT on Snowflake, or COLLATE <charset>_bin on MySQL. Only rows that pass both are returned. The per-engine forms are _KEY_EXACT_TEMPLATE in each connector module, applied by BaseConnector._key_exact_wraps (src/connectors/base.py). MySQL and PostgreSQL override it to add the form only where the column's collation needs it (src/connectors/mysql.py, src/connectors/postgres.py). No second form is added on Oracle and SAP HANA, where the session pins below make the native match byte-exact, or on Trino, which compares code points. Db2 relies on the database's default IDENTITY collation; a case-insensitive Db2 database collation is not yet handled (#2616). Every caller then keys the returned rows by their own key text and looks up the key it asked for, so a collation-widened row is never used in place of the requested one (src/workers/diff/activities.py).

Page walk (#2565). Keys are read in pages. The query keeps the database's own sort order and an index-friendly >= boundary, which re-reads the whole group of rows the collation calls equal at each boundary. The platform then removes, by exact key, the rows it has already returned. The walk is complete under any collation and never returns a row twice (KeysetCursor in src/connectors/base.py).

Session pins. Some engines take their comparison rules from the session. Paridity sets them when it connects:

• SAP HANA: CASE_SENSITIVE = TRUE, read back after it is set. The connection fails if the read-back differs (SAP_HANA_SESSION_PINS in src/connectors/sap_hana.py, #2572).
• Oracle: NLS_COMP = BINARY and NLS_SORT = BINARY, read back the same way. The connection fails if either differs (ORACLE_SESSION_PINS in src/connectors/oracle.py, #2615).
• PostgreSQL: DateStyle = ISO, YMD and timezone = UTC (PG_SESSION_SETTINGS in src/connectors/postgres.py, #2436).
• Redshift: datestyle ISO, YMD and timezone UTC (REDSHIFT_SESSION_PINS in src/connectors/redshift.py, #2445).

The PostgreSQL and Redshift pins fix how dates render. Without them a non-default session style could make the same date read differently.

A key column is always in the hash (#2602). The proof phase compares an order-insensitive total of the row hashes. If a key column were excluded from the hash, two datasets whose values were swapped between two keys would compare equal. Paridity refuses that configuration when it is saved (check_key_excluded_from_hash in src/services/field_config_gate.py, used by the field API and the contract importer). It refuses it again before every run (ConfigResolver in src/services/config_resolver.py).

There is no case-sensitivity setting. A key column used to accept a case_sensitive flag. That flag never changed a comparison. It is now retired: a request that sets it to anything other than false is rejected with HTTP 422 and code KEY_IDENTITY_IS_STRICT. false is accepted and ignored so older clients keep working. The contract importer applies the same rule (check_key_identity_strict in src/services/field_config_gate.py).

The trim profile (exact-v1-trim-ws)

A mapping declares its profile in key_identity_profile (mapping settings, or the mappings API). The default is exact-v1. Under exact-v1-trim-ws:

• Matching. Each string key value loses leading and trailing ASCII whitespace (the six characters trim_strings removes: space, tab, line feed, vertical tab, form feed, carriage return) before the two sides are matched. A no-break space and other Unicode spaces are kept. Non-string key values are unchanged (trimmed_key in src/services/key_identity.py).
• Each side keeps its own key. The staged match value is the trimmed key. The raw key each side stored is kept alongside it, so a mismatched row records the source key (key_json) and, when the target's differs, the target key (target_key_json on ev.ev_mismatch_keys).
• Reading rows back. The field diff fetches each side by the raw key that side stored. Paridity never puts TRIM(column) in a query, so the key index is still used.
• Duplicates. Two rows on one side whose keys differ only in edge whitespace ('A' and 'A ') become the same key. The run stops with the duplicate-key failure, as for any other duplicate.
• Audit. The profile is recorded in every run's config snapshot (key_identity), including the default, so an auditor can see which identity rule a run used.
• Onboarding suggests it. Auto-configuration proposes the trim profile when a chosen source key column is fixed-width CHAR and the target's key column is known to be another type (the preview shows "ignores key spaces", with the reason in its tooltip). When the target's key type cannot be read, it changes nothing and shows "check key format" instead. Confirming a proposal applies its suggestion (_suggest_key_identity in src/services/auto_config.py; type spellings verified live on the five local engines by scripts/verify_2644_trim_lookup.py).

The accepted limit. Three lookups hold only one side's raw key: the lag recheck, the open-mismatch recheck and live reveal. They look the row up on the other side by that key and by its trimmed form. A CHAR column pads a stored value, and some engines do not treat a trimmed lookup value as equal to it (Oracle is one, measured below). Such a key stays open until the next full key extraction, which matches it correctly. It is never reported as resolved when it is not.

Measured live on 2026-09-28 (scripts/verify_2644_trim_lookup.py, run with --prove-can-fail). One row keyed 'MAT-004' in a CHAR(10) column and in a VARCHAR(10) column:

Engine	Fetch by the raw CHAR key (field diff)	Padded key finds the trimmed VARCHAR row (lag recheck)	Trimmed key finds the CHAR row
postgres	found	found	found
mysql	found (the driver returns CHAR trimmed)	found	found
sqlserver	found	found	found
oracle	found	found	not found (the accepted limit)
db2_luw	found	found	found on a byte-order database; not found on a case-insensitive one (the accepted limit; measured on ucadb 2026-09-29, #2616)

Not measured: trino, snowflake, databricks, bigquery, redshift, sap_hana (no local rig).

End-to-end gates (pairing legs in scripts/pairing_configs/, each run live on 2026-09-29 and each seen to fail when the profile is switched back to exact-v1):

Leg	What it proves	Result
oracle_to_postgres_char_key_trim_2644	Oracle CHAR(10) key to a trimmed PostgreSQL VARCHAR(10) key, one changed value	exactly 1 mismatched key and 1 field difference (exact-v1: 8 keys, none)
postgres_to_oracle_char_key_trim_reverse_2644	the same with the padded key on the target	exactly 1 mismatched key and 1 field difference
oracle_to_postgres_char_key_trim_composite_2644	a two-column key, CHAR(10) plus a number, where one material exists under two plants	exactly 1 mismatched key and 1 field difference
postgres_to_postgres_key_trim_duplicate_2644	'MAT-001' and 'MAT-001 ' on one side	the run stops on the duplicate-key failure, with no evidence written for either row
postgres_to_postgres_key_trim_duplicate_ctrl_2644	the same data under exact-v1	an ordinary missing row (fail_count), no error

Throughput. Key extraction on the 50,025-row Workers tables with a text key, alternating the two profiles over three rounds on the dev stack (scripts/benchmark/bench_2644_key_identity_extraction.py): 2.5 to 3.4 s under exact-v1 and 2.8 to 3.5 s under the trim profile. The difference is inside the run-to-run spread.

Verification by engine

Each engine that runs key predicates in its own SQL is checked on two predicates. Where the engine can be made hostile, the check runs against a case-insensitive or accent-insensitive column, session or database; Trino has no collation to make hostile and compares code points. DB2 sets collation per database, so its hostile check runs in a second dev database created case insensitive (ucadb, COLLATE USING UCA500R1_LEN_S2). Paridity reads a DB2 database's collation on the first keyed lookup; on anything but byte order (IDENTITY), or when the collation cannot be read, the lookup also compares the key's bytes (a binary cast sized to the key column). On a byte-order database the lookup is unchanged. A check is marked verified only after a live test passes (tests/integration/connectors/test_key_predicate_2603.py). The allowlist is KEY_PREDICATE_VERIFIED in src/services/partition_coverage.py.

"Not yet verified" means that live proof has not been completed. It does not mean the check is known to fail. The allowlist records proof. It does not decide whether a run may start: no run is refused today because its engine has an unverified cell.

Generated from src/services/partition_coverage.py (KEY_PREDICATE_VERIFIED) by scripts/gen_key_identity_matrix.py. Do not hand-edit; CI fails if this table and the code disagree.

• Keyed lookup (equality): fetching the row for key Key returns only the byte-identical row, never key or KEY.
• Page walk (keyset): reading the keys page by page returns every row exactly once, even inside a group of keys the database collates as equal.

Engine	Check	Status
bigquery	Keyed lookup (equality)	Verified live
bigquery	Page walk (keyset)	Verified live
clickhouse	Keyed lookup (equality)	Verified live
clickhouse	Page walk (keyset)	Verified live
databricks	Keyed lookup (equality)	Verified live
databricks	Page walk (keyset)	Verified live
db2_luw	Keyed lookup (equality)	Verified live
db2_luw	Page walk (keyset)	Verified live
mysql	Keyed lookup (equality)	Verified live
mysql	Page walk (keyset)	Verified live
oracle	Keyed lookup (equality)	Verified live
oracle	Page walk (keyset)	Verified live
postgres	Keyed lookup (equality)	Verified live
postgres	Page walk (keyset)	Verified live
redshift	Keyed lookup (equality)	Verified live
redshift	Page walk (keyset)	Verified live
sap_hana	Keyed lookup (equality)	Verified live
sap_hana	Page walk (keyset)	Verified live
snowflake	Keyed lookup (equality)	Verified live
snowflake	Page walk (keyset)	Verified live
sqlserver	Keyed lookup (equality)	Verified live
sqlserver	Page walk (keyset)	Verified live
trino	Keyed lookup (equality)	Verified live
trino	Page walk (keyset)	Verified live

24 of 24 cells verified live.

What is not offered in V1

V1 offers no case-insensitive identity profile and no accent-insensitive one (the trim profile removes edge whitespace only). Both are parked as a V2 capability (#2606), for these reasons:

• Databases do not agree on what "case-insensitive" means. MySQL's default collation treats e and é as equal as well as E and e. That was measured live on MySQL 8.0 (#2575, addendum V3).
• A binary wrapper does not rescue every engine. Under SAP HANA's case-insensitive session, even a TO_BINARY comparison is folded. That was measured live on HANA Cloud (#2575, V7 result).
• No replication tool Paridity supports re-cases or accent-folds key values by default (#2575, addendum V1).

A caseless profile would need its own versioned rules and its own proof on every engine. It will be built when a real pipeline needs it, not before.