A migration comparison can report a match after its own settings have erased the difference it was meant to find. In our test, rounding amounts to two decimal places turned 10.0049 in the source and 10.0000 in the target into 10.00 on both sides, so the comparison reported no difference.
At TechFabric, we'd test the settings that reshape data before comparison, using known errors seeded next to correct variants. An accepted change in representation should pass; a change to a value the migration promises to preserve should still appear in the report. That distinction matters when a matching report is used to approve the move.
We ran eight such cases on six synthetic orders in DuckDB 2.0.0, on a Databricks SQL warehouse and through Lakebridge Reconcile. Broad settings such as that rounding missed three of the five value changes we seeded, while comparing the stored values exactly found all five. Both configurations accepted the three correct variants, including 100.0000 written as 100.00.
What the comparison is allowed to change
The business agreement comes first. Our synthetic example has six orders, each with an identifier, an amount, an event time and a note. Amounts retain four decimal places, event times retain the same instant, and a missing note remains different from empty text. Writing 100.0000 as 100.00 is acceptable because its numeric value is unchanged, and so is expressing the same instant with a different time-zone offset.
We tested two comparison configurations in DuckDB 2.0.0 and on a Databricks SQL warehouse, version 2026.44, and got the same findings in both. The first rounds amounts to two decimal places, reduces timestamps to calendar dates in UTC, our reference time zone, and replaces missing notes with empty text. The second preserves the agreed values. These configurations were chosen for the demonstration, and the last section runs them through Lakebridge.
Both configurations receive the same frozen records. Each run restores the correct target and applies one test case, so a difference in findings comes from the comparison settings. The order identifiers remain unique and complete throughout this experiment.
Three errors hidden by the settings
Rounding hid a loss of 0.0049 in one order. The source amount was 10.0049 and the changed target held 10.0000, but after rounding both to two decimal places, the comparator received 10.00 on each side and reported no difference. The error had disappeared before the equality check.
Reducing a timestamp to a date hid a one-minute shift. We changed an order from 09:00 to 09:01 on October 21. Both became the same UTC date, although the agreement required the event's original instant. A daily sales total would also remain unchanged because neither the amount nor the reporting day moved.
Replacing missing notes with empty text hid the third error. Here, NULL means the note is missing, while an empty string is a present but empty value. Converting both to an empty string removes that distinction. A business may approve that conversion, but this example's agreement preserves it.
The broad settings still caught two larger changes: an amount increased by 1, and an event moved across midnight. Those cases alone wouldn't reveal the three blind spots, so we tested the nearby errors alongside correct representation changes:
| Test case | Expected | Broad settings | Preserve agreed values |
|---|---|---|---|
| Three correct variants | PASS | PASS | PASS |
| 10.0049 becomes 10.0000 | HOLD | PASS | HOLD |
| 09:00 becomes 09:01 on the same day | HOLD | PASS | HOLD |
| Missing note becomes empty text | HOLD | PASS | HOLD |
| Event moves across midnight | HOLD | HOLD | HOLD |
| Amount increases by 1 | HOLD | HOLD | HOLD |
PASS means the comparison found no difference; HOLD means it reported a difference. A PASS where HOLD was expected exposes a missed error.
The corrected comparison
Both engines load amounts into decimal columns and event times into timestamp columns, so equivalent representations already have equal typed values. The corrected settings compare those values without rounding amounts or discarding time, and preserve the distinction between NULL and empty text. All three correct controls pass.
For amounts, the comparison that returned the changed order was:
SELECT s.order_id,
s.amount AS source_amount,
t.amount AS target_amount
FROM source_orders s
JOIN target_orders t USING (order_id)
WHERE s.amount IS DISTINCT FROM t.amount;
The order identifier matches the corresponding records. IS DISTINCT FROM also treats a missing value as different from a present one, and Databricks SQL does the same. In the precision-loss case, this query returned order O103 with source amount 10.0049 and target amount 10.0000. It ran against typed decimal columns, without rounding them first.
Across the test pack, the corrected settings found all five seeded value changes and accepted all three correct variants. We checked the reported order and field against an expected list written separately from the comparison, so finding an unrelated difference couldn't count as success.
Running the same cases in Lakebridge
Lakebridge supports column transformations before comparison. Its configuration reference says custom expressions are used as supplied and require explicit string conversion and NULL handling. So we ran the same cases through Lakebridge Reconcile 0.15.4 in the same Databricks workspace, with Databricks as both the source and the target.
Each run matched records on the order identifier and compared the amount, event time and note in an all report. The Reconcile Guide describes that report as the data report for values combined with schema checks of column names and types. The first run supplied the broad settings as custom transformations, wrapped in the string conversion and NULL handling the reference asks for. The second supplied none, so Lakebridge applied its own defaults.
| Lakebridge configuration | Value changes reported | Correct variants passed |
|---|---|---|
| Broad settings as custom transformations | 2 of 5 | 3 of 3 |
| No custom transformations | 5 of 5 | 3 of 3 |
The broad transformations hid the same three errors as in DuckDB. Wrapping our note expression in the documented NULL handling didn't help, because the inner expression had already turned the missing note into empty text. Without custom transformations, Lakebridge replaced the missing note with its own placeholder text, so the change to empty text stayed visible. In every reported difference it named the order and field we expected. Both tables lived in Databricks, so this run didn't test a connection to another source system.
For each run, we kept the input versions, the tool version and settings, and the observed findings. Matching the expected order and field passes the configuration for this test pack, while a difference sends the settings back for revision and another run.
We'd start with one table whose migration report is used for approval. For each rule that changes values before comparison, we'd record an accepted equivalent and an error that should remain visible. A missing expected finding would keep the table on hold.
Frequently asked questions
Is rounding before comparison always wrong?
No. If the agreed output retains two decimal places, a two-decimal comparison may be appropriate. Test that agreement explicitly, including values near a rounding boundary, instead of using rounding to dismiss an unexplained mismatch.
Why do correct controls matter?
A comparison that rejects legitimate representations can block a sound migration. The controls check that preserving exact meaning still accepts decimal trailing zeros and equivalent time-zone offsets.
Does passing this pack approve the migration?
No. These cases test selected value-comparison settings. A migration decision also depends on complete records, suitable column types and verification through the actual connection that reads the source system.