ClickHouse¶

Paridity reads ClickHouse natively. The aggregate proof (hash_total) is computed inside ClickHouse, so only fingerprints leave the server; when the proof fails, Paridity extracts keys and row fingerprints, then fetches only the rows that differ. ClickHouse can be the source or the target of a mapping, paired with any other supported system.


Readiness	Active (since 2026-10-08)
Proof	Native SHA-256 pushdown with a real XOR aggregate (`xor_sum_sha256`), the same fingerprint every other engine computes
Supported servers	ClickHouse 25.8 LTS or newer
Tested servers	25.8.33.6 and 26.8.20.9, in continuous integration
Interface	HTTPS (port 8443) with certificate verification, by default
Driver	clickhouse-connect 1.10.0 (Apache-2.0), shipped in the worker image
Cross-engine pairs	Verified live against every other SQL engine Paridity supports

Supported versions¶

Paridity supports ClickHouse 25.8 LTS and newer. Continuous integration tests every change against 25.8.33.6 (the oldest supported version) and 26.8.20.9.

A server older than 25.8 is refused when Paridity connects, with an error that names the server version and the 25.8 floor. Paridity does not fall back to a slower comparison on an older server: the query settings Paridity relies on (see Why every query carries a SETTINGS clause) and several rendering functions differ or do not exist before 25.8, and a result that cannot be verified is not reported.

Provisioning the read user¶

Paridity connects as a dedicated user that can read and can change query settings, but cannot write.

Create the user with readonly = 2. readonly = 2 lets a query set its own settings and still refuses every write. readonly = 1 is not enough: Paridity sets settings on every query, so a readonly = 1 user fails at connect with a message telling you to use readonly = 2. readonly = 0 would allow writes; Paridity never needs them.
Grant read access to the databases you reconcile, and to the system and information-schema tables Paridity uses for introspection and discovery.
Use a settings profile with no row, byte or time caps for this user (for example max_rows_to_read, max_bytes_to_read, max_execution_time). Paridity pins every *_overflow_mode setting to throw, so a cap can never cut a result short silently. If a cap is reached, the run fails loudly (ClickHouse error code 158) rather than comparing a partial result. Paridity does not override a cap your administrators set; give the read user a profile without one.
Do not put a CONST constraint on any setting Paridity pins (the list is below). If a profile makes one of them CONST, ClickHouse refuses Paridity's query (error code 452, SETTING_CONSTRAINT_VIOLATION) and the connection test fails with that message. This is deliberate: the alternative is a query that runs with a setting that can change the answer.

The grant set that Paridity is tested with:

CREATE USER recon IDENTIFIED BY '<password>' SETTINGS readonly = 2;

GRANT SELECT ON <your_database>.* TO recon;      -- one line per reconciled database
GRANT SELECT ON system.* TO recon;               -- tables, columns, databases, settings
GRANT SELECT ON information_schema.* TO recon;
GRANT SELECT ON INFORMATION_SCHEMA.* TO recon;

A narrower system grant may work but is not tested.

The connection secret¶

Like every other system, the connection details live in your secrets manager; the system record holds only a pointer (connection_ref) to the secret. The secret is a JSON object:

Key	Required	Default	Meaning
`host`	Yes		Server host name
`port`	No	`8443` (or `8123` when `secure` is false)	HTTP(S) interface port
`database`	No	`default`	Default database for unqualified names
`user`	No	`default`	The read user (`username` is accepted too)
`password`	No	empty	The read user's password
`secure`	No	`true`	TLS. Set `false` only for a local test server; a plaintext connection is logged as a warning on every connect
`ca_cert_pem`	No	none (the worker's system trust store)	The CA certificate that signed the server's certificate, as PEM text (the `-----BEGIN CERTIFICATE-----` block itself, not a file path). Needed when the server uses a private or self-signed CA. Ignored when `secure` is false
`verify`	No	`true`	Certificate verification. Set `false` only for a local test server; the server's identity is then not checked, and a warning is logged on every connect. Prefer `ca_cert_pem`

Why every query carries a SETTINGS clause¶

Some ClickHouse settings change the answer a query returns without raising an error. During onboarding we reproduced each of these on a real server:

a user-profile limit = 1000 returned 1,000 of 50,025 rows with no error;
read_overflow_mode = 'break' with a row cap returned a partial count and a different fingerprint;
timeout_overflow_mode = 'break' returned a count of 0;
additional_table_filters silently filtered the table.

Setting the correct values through the driver is not enough: the driver drops a setting that a profile marks CONST without telling anyone, and reading the setting back (getSetting(), system.settings) reports the requested value even while the server is truncating. So Paridity appends an inline SETTINGS clause to every statement it sends. Inline settings are enforced by the server, and a constraint that forbids one makes the server refuse the query loudly instead of answering it differently.

The pinned settings:

Setting	Pinned to	Why
`session_timezone`	`'UTC'`	Timestamps render in UTC, and window bounds parse in UTC
`formatdatetime_f_prints_scale_number_of_digits`, `formatdatetime_f_prints_single_zero`	`0`	Fractional seconds render with a fixed width
`limit`, `offset`	`0`	No profile-level truncation
`read_overflow_mode`, `result_overflow_mode`, `timeout_overflow_mode`, `group_by_overflow_mode`, `sort_overflow_mode`, `join_overflow_mode`, `set_overflow_mode`, `distinct_overflow_mode`, `transfer_overflow_mode`	`'throw'`	A limit fails the query instead of returning part of the answer
`additional_table_filters`, `additional_result_filter`	empty	No hidden filters
`final`	`0`	`FINAL` comes only from the read mode you declare on the mapping
`do_not_merge_across_partitions_select_final`, `optimize_move_to_prewhere_if_final`	`0`	Both can make a `FINAL` read return row versions that should have been merged away
`use_query_cache`	`0`	A cached fingerprint would be a stale verdict
`apply_patch_parts`	`1`	Lightweight updates are visible to the comparison

Read modes: tables that hold unmerged row versions¶

ReplacingMergeTree and the other merge-dependent engines (Collapsing, VersionedCollapsing, Summing, Aggregating, including their Replicated and Shared forms) store an update as a new row version and remove the old version later, when parts merge. Until then a plain read sees both versions. A replica that is exactly right can therefore show more rows than the source and fail the comparison.

You tell Paridity how to read such a table with the mapping's Table Semantics setting (mapping form, Watermark & CDC step; API field cdc_table_semantics). It is set per mapping only and is never overridden by a schedule or a run.

Table Semantics	Use it when	What Paridity reads
None (default)	The ClickHouse table is a plain MergeTree, or a merge-dependent table you have fully merged (`OPTIMIZE ... FINAL`)	The table as stored
ClickHouse: read through FINAL (ReplacingMergeTree) (`replacing_merge_tree_final`)	The table is a ReplacingMergeTree (or another merge-dependent engine) loaded by your own pipeline	`FROM table FINAL` on every read
ClickHouse: ClickPipes / PeerDB (FINAL, excluding deleted rows) (`clickpipes_peerdb`)	The table is a ClickPipes (PeerDB) CDC destination: deletes arrive as new versions with `_peerdb_is_deleted` set	`FROM table FINAL` plus `_peerdb_is_deleted = 0` on every read

The two ClickHouse options appear only when one side of the mapping is a ClickHouse system; the API refuses them otherwise (HTTP 422, CDC_TABLE_SEMANTICS_SYSTEM_MISMATCH).

Why both parts matter for ClickPipes tables. On a ClickPipes-shaped table with 50,000 ids, 5,000 unmerged updates and 1,000 unmerged deletes, the true row count is 49,000. A plain read counts 56,000; FINAL alone counts 50,000 (deleted ids survive as their latest version); only FINAL with the delete filter gives 49,000, and its fingerprint equals an independent deduplication of the same table.

What Paridity checks. Before reading, Paridity confirms the table fits the mode you declared and refuses the run with the reason if it does not: a FINAL mode on a table whose engine is not merge-dependent, or clickpipes_peerdb on a table without the _peerdb_is_deleted column.

Suggestions, never automatic. When you onboard tables, the preview suggests clickpipes_peerdb for a ReplacingMergeTree that has the three _peerdb_* columns, and replacing_merge_tree_final for other merge-dependent tables. A suggestion is never applied unless you accept it.

Left at None on a merge-dependent table. The run proceeds and reads the table as stored. The mapping and the run narrative carry an advisory that unmerged versions are counted. The failure this can cause is a visible false failure (extra rows), never a false pass.

The advisory knows the table engine. Refresh Schema (and the first run of a mapping that has never been refreshed) records the ClickHouse table engine with the schema baseline. Once recorded, the advisory names the engine when it is merge-dependent ("The ClickHouse side is a ReplacingMergeTree table read as-is ..."), and is not shown at all for a plain MergeTree. Until it is recorded the advisory keeps its conditional wording and asks you to Refresh Schema. Paridity never queries ClickHouse to render the mapping page; the engine is read from the baseline that introspection wrote.

Cost. A FINAL read merges at query time. In our onboarding measurement it cost about 1.27 times a plain read on 2.2 million input rows (indicative, one test server).

Watermarks do not need FINAL. The newest value of the replication progress column (for ClickPipes, _peerdb_synced_at) over all row versions is the replication mark, merged or not, so watermark reads never use FINAL.

About ClickPipes and PeerDB. ClickPipes is ClickHouse's managed CDC service, built on the open-source PeerDB project. Paridity only reads the tables they write. PeerDB is licensed AGPL-3.0; Paridity never bundles, imports or links it.

Keys and watermarks¶

Keys. ClickHouse has no primary-key or unique constraint; ORDER BY does not guarantee uniqueness. Discovery proposes a key only for a Replacing- or Collapsing-family table whose sorting key is a list of plain columns (the deduplication key under FINAL). For a plain MergeTree it proposes none: set the key columns on the mapping yourself.

Key columns of these types are extracted natively: 8- to 64-bit integers, String, Decimal, Date, Date32, DateTime, DateTime64 up to microsecond precision, UUID and Bool. A key column of another type (for example a 128- or 256-bit integer, or DateTime64(9)) may be extracted through Paridity's reference engine instead: slower, equally exact.

Watermarks. Supported strategies: timestamp, ingest_ts, control_marker and custom_sql. Discovery suggests _peerdb_synced_at as an ingest_ts watermark (high confidence) and other DateTime columns as timestamp watermarks. It never suggests the virtual columns _part, _block_number or _part_data_version.

Known limits¶

Dates before 1900 on ClickHouse 25.8. On 25.8, Date32 and DateTime64 store only 1900-01-01 through 2299-12-31; an older value is silently stored as 1900-01-01 when it is written. (Date holds 1970-01-01 to 2149-06-06 and DateTime 1970 to 2106; values outside those ranges are also stored at the nearest end of the range.) If your source holds values outside the range of the ClickHouse column, the replica does not hold the source value, and Paridity reports that row as a real difference. That is correct: the data in ClickHouse is not the data in the source.
Types compared through the reference engine only. Array, Map, Tuple, Nested, JSON / Object, Variant, Dynamic, IPv4 / IPv6 and the aggregate-function types have no native rendering. A mapping that compares such a column takes the slower reference path for the whole comparison. No cross-engine verification has been recorded for these types; exclude such columns from the comparison, or verify them on your own data before relying on a pass. A container (Array, Map or Tuple) whose element is a Date or DateTime type is refused on the reference path as well: the read has no verified conversion for it, so the run fails with a clear error instead of comparing it. Exclude such a column.
Settings that fall back to the reference path. A rounding mode other than half-up, a numeric tolerance, a float compare mode, a canonical type override, timestamp rounding, a non-UTC timezone for naive timestamps, and transform chains other than null_to_empty, empty_to_null and decimal_separator_normalize are computed by the reference engine, not in ClickHouse.
Encrypted columns. An in-SQL decrypt_expression is not supported on ClickHouse; the run refuses it. Use a Python-side decryption algorithm (decrypt_algo) for the field instead.
Distributed tables under FINAL are not supported. For a Distributed table whose sharding key differs from its ORDER BY, FINAL merges each shard separately, so duplicate versions on different shards may survive. We have not tested this on a multi-shard cluster, so we document it as unsupported rather than claim either way.
TLS is verified with a private CA, not yet against ClickHouse Cloud. HTTPS with certificate verification on, against a server whose certificate is signed by a private CA supplied as ca_cert_pem, runs in continuous integration (step "ClickHouse gate (#2953) - TLS 8443 self-signed ENFORCED"), together with checks that a wrong CA, a plaintext port and a missing CA are each refused with a configuration error. Mutual TLS (client certificates) is not supported.
ClickHouse Cloud has not been verified live yet. The connector speaks the same HTTPS interface, but no end-to-end run against ClickHouse Cloud has been recorded.
Hierarchical narrowing (segment filters) is not available on ClickHouse in this version.
Strings must be valid UTF-8. A String column holding bytes that are not valid UTF-8 stops the run with an error naming the column. Set that field's compare mode to hash (compared as hex) to reconcile it.

Evidence¶

Every claim on this page traces to a recorded run:

Byte identity. ClickHouse's rendering of every value, its row fingerprint and its hash_total are proven identical to Paridity's reference implementation on 25.8.33.6 and 26.8.20.9, over the shared cross-engine test vectors, the hash_total vectors, and LOB values up to 1.5 MB. The same tests run in continuous integration on both servers, plus a check that a 22.8 server is refused.
End-to-end pairings. Live runs against every other SQL engine passed on matching data and failed on an injected difference, with the proof computed natively on both sides. The run ids are in the pairing matrix (docs/pairing_matrix.md, "ClickHouse").
TLS. The HTTPS transport with certificate verification connects and computes a hash_total identical to the reference over a private-CA server in continuous integration; a wrong CA, a plaintext port and a missing CA each fail with a configuration error.
Read modes. The ClickPipes-shaped pairing passes under clickpipes_peerdb, and the same table read with Table Semantics = None does not pass.

The full record, including each finding from onboarding and how it was checked, is in the verification record (cross_engine_hash_audit.md, Issues 62 to 65).