How to Validate Aggregation Correctness Across Two Live Clusters

Validating aggregation correctness across two live clusters means separating expected lag from lasting errors, then checking whether matching results are actually right. A single diff cannot do that. The reliable approach is layered, with a convergence window, partition-level hashes, targeted metric comparisons, and a resolution path tied to the cause.

Why aggregation divergence between two live clusters is structurally inevitable, not accidental

Columnar merge-tree engines favor write throughput and asynchronous compaction over immediate convergence. They write parts and merge them in the background, so two clusters processing the same stream can expose different physical states between merge cycles. Deduplication adds another source of delay. In ReplacingMergeTrees and CollapsingMergeTrees, version replacement or row collapsing happens during merges after write time. Different merge progress can therefore produce different entity states from the same logical events.

Ingestion introduces a separate clock, because trace metadata or late-arriving fields can reach one cluster before the other. Query-time reconciliation cannot read data that has not arrived. The FINAL modifier forces deduplication and collapsing over available parts, but it does not make those parts identical across clusters. Aggregating the same underlying data in different sequences can lead to different aggregate states or results. These causes operate together at different timescales, so any single check leaves part of the problem unseen.

Why a single validation check will always produce either false positives or missed divergences

Why not start and finish with row counts? Equal counts do not establish equal values. Comparing counts can show that rows are missing but miss wrong revenue totals or entity states, while a hash can show that values differ without showing whether errors or aggregation sequencing caused the difference. Regression against a baseline can show magnitude drift, but on its own it cannot find the partition or dimension that caused it.

Timing makes those blind spots more troublesome. If one cluster has seconds-level ingestion lag and another is near-real-time, a point-in-time comparison can flag expected freshness differences as faults. Both clusters can share an upstream bug, return the same wrong aggregation, and pass a hash comparison. Agreement establishes consistency between clusters.

Every validation layer needs a defined scope, a defined blind spot, and a handoff that covers what it cannot establish. That design must also account for when detection happens. A test run after loading can discover a broken aggregate after a customer has already received it. Layers catch more errors, and checks run before release can help stop errors from reaching consumers.

Establishing the convergence window before any comparison runs

To make comparisons meaningful, first establish the interval after which both clusters can be expected to have processed and merged the same data. Run identical aggregation queries at increasing offsets from ingestion and record when the results stabilize. Once the results stabilize, enough time has passed to compare them safely for the cluster pair and workload pattern being tested.

The window must change with the workload. Partition size, ingestion rate, and merge scheduling all affect how long convergence takes, so a fixed offset can produce false positives during heavy ingestion and miss divergences when delayed merges complicate low-ingest periods. Scope the measurement by time range or partition key. Recently written partitions can then remain outside comparison while older, fully merged partitions are validated. The output is a time-bounded, partition-scoped window within which a difference warrants investigation rather than an automatic explanation of compaction lag.

Hash-aggregate comparison as the primary detection instrument, applied per partition

Within that window, compute a hash aggregate over each partition independently. A matching hash indicates consistency on the comparison surface, while a mismatch identifies a partition for further investigation. Full-table hashing gives a less useful signal. It costs more and leaves the divergent range unknown, forcing another search through the data. Partition-level hashing supplies localization as part of detection and keeps comparisons small enough to run continuously.

Make row and column ordering explicit in the comparison query. Engine defaults are not a deterministic ordering contract. If the two queries hash identical data in different orders, the resulting mismatch says nothing useful about correctness.

With AggregatingMergeTrees, the comparison surface becomes pre-aggregated states. This reduces what must be compared, but only if aggregate functions and intermediate state types are identical across both clusters. Schema differences can produce genuinely different states without indicating a computation bug. Different merge sequences can also leave intermediate states that disagree even though the final answer will converge. Treat the hash as a trip-wire, then invoke regression on that partition to distinguish transient sequencing differences from persistent discrepancies in counts, metrics, or distinct-value cardinality.

Row-count and key-metric regression as the second layer, invoked on divergent partitions

For a partition with a divergent hash, compare total row count, key business metrics, and null or sentinel rates across both clusters. Revenue totals, event counts, and distinct entity counts are among the business metrics these checks can use. These checks give the mismatch a shape. A row-count difference accompanied by metric divergence suggests missing or duplicate ingestion, such as a dropped partition or double-write. When row counts match but metric values differ, aggregation logic is the place to look, while null or sentinel rates help show differences in the values being aggregated.

Comparing each cluster with known-good historical output can reveal an upstream bug that makes both clusters wrong in the same direction. Because those partitions may pass the hash check, baseline checks cannot depend solely on a mismatch if they are to cover that blind spot. They test plausibility beyond cross-cluster agreement.

If a partition arrives with a row volume far outside its usual range, it should trigger an alert before the more costly hash comparison runs. This is an inexpensive way to catch missing source data. For partitions that reach regression, the output should be a diagnosis category rather than another unexplained difference, because timing, aggregation logic, and ingestion faults call for different repairs.

How query-time consistency flags change what needs to be validated

Do production queries use FINAL on both clusters, neither cluster, or only one? With FINAL on both, query-time deduplication removes deduplication timing as a source of mismatch for the data present, but it does not erase ingestion differences. Dynamic reconciliation also makes validation slower and more costly, which may make it too expensive to run all the time at high volumes.

Without FINAL, validation must account for expected mid-merge divergence. Mixed configurations are more difficult to interpret. Comparing FINAL results on one side with non-FINAL results on the other systematically reports differences where unmerged duplicates exist, because the queries are not measuring the same state. Nevertheless, validation should use each cluster’s production consistency flag so that it tests what consumers actually see, with that asymmetry treated as part of the diagnosis. Schema-level pre-aggregation makes validation cheaper by avoiding FINAL overhead, but the aggregate definitions must be the same so that states do not diverge at write time.

Resolving divergence without halting production — the three paths and when each applies

For deduplication-timing divergence, extend the convergence window and repeat the partition hash comparison. No data repair is needed for expected merge lag. The framework should record this outcome as “transient, expected”. Re-comparison gives an ephemeral state difference time to disappear while leaving production queries running.

Persistent metric differences on equal row counts call for cluster-level reconciliation. Compare schema definitions, merge tree settings, and aggregation function parameters on the two clusters. The exact keys deserve close attention. Different ORDER BY or PARTITION BY keys can change duplicate handling, while different aggregate function intermediate types change the states being compared. Also investigate whether a deployment applied a pipeline transformation to one cluster but not the other.

When row counts and metrics diverge together, move the investigation upstream. Begin at the ingestion checkpoint. Compare what was written to each cluster’s ingest queue and when, because correctly functioning clusters can still return different answers when they receive different data. Missing or duplicate ingestion requires pipeline remediation.

Once an incident is resolved, it should lead to a validation rule that stays in place, such as a specific hash comparison, row-count threshold, or metric regression. Recurrence suggests the cause remains unresolved. Adding a separate KV or document store for “correct” entity state is not a shortcut around this discipline. Application-level reconciliation makes dual-write more complex, adds a third consistency boundary, and duplicates operations, while leaving the original cause of divergence in place.

Running validation continuously without blocking production queries

The validation layers also define the cost structure. Cheap volume checks run first, partition hashes follow for partitions that pass, and diagnostic regression runs on partitions whose hashes differ. Most partitions never reach the expensive diagnostic work. Baseline checks serve the separate purpose of detecting shared errors. How often validation runs should depend on when new data arrives and on the measured convergence window, with one comparison per partition per window to avoid checking data again and again while it is still converging.

Issue validation queries against isolated read-only compute, because sharing production compute creates latency interference and can make validation fail under heavy load. Sandboxed branches provide another boundary for comparison. These read-only snapshots share production storage but use independent compute, so a runaway validation query cannot starve the production workload.

An AI agent probing for divergence may choose non-deterministic query paths, producing false signals. Constrain agent-issued checks to fixed validation query templates through a structured command interface, and log every query. Full auditability must cover other validation runs too, allowing the team to reconstruct the exact query, cluster state, and execution time when a consumer later reports an error in a partition that passed.

Moving validation earlier — catching divergence before it reaches production consumers

Even a well-isolated, continuous validator remains reactive if it runs only after loading. A broken aggregate can reach a consumer before the next check discovers it. Earlier detection closes that exposure window. For schema changes affecting aggregation keys, partition boundaries, or merge tree settings across the clusters, require explicit human sign-off before promotion. Automated detection paired with review can catch a dangerous change before it has produced divergent data for a hash to detect.

That upstream gate cannot remain fixed while the clusters evolve. New partitions, aggregation patterns, and ingestion sources change the coverage that validation needs. The useful feedback loop runs from incident to diagnosis, from diagnosis to resolution, and from resolution to a new validation rule that detects the same class of error earlier next time. Maintaining that loop is an ongoing engineering discipline that continues after the first comparison passes.