Your reconciliation job reports green. Row counts match on every table. Finance closes the month, and a controller finds a variance that traces back to a column you have been loading for two years.
The failure class is silent decimal coercion. A source column declared numeric with no scale feeds a warehouse column declared NUMBER(18,2). Every value with more than two fractional digits is rounded on write. The row count is unchanged. The money is not.
This is not a pipeline bug in the usual sense. Nothing errored. Nothing was dropped. The type contract between source and warehouse was never tested, because count(*) cannot see it.
What precision and scale actually mean
In Snowflake, precision is the total number of digits allowed and scale is the number of digits allowed to the right of the decimal point. NUMBER defaults to precision 38 and scale 0, i.e. NUMBER(38,0) (Snowflake numeric data types).
In PostgreSQL, the same terms apply. The numeric type is recommended for storing monetary amounts and other quantities where exactness is required (PostgreSQL numeric types).
The asymmetry that matters: an unconstrained numeric column does not coerce input values to any particular scale, whereas a numeric column with a declared scale does coerce input values to that scale. If the scale of a value to be stored is greater than the declared scale of the column, the system rounds the value to the specified number of fractional digits. If the number of digits to the left of the decimal point exceeds the declared precision minus the declared scale, an error is raised.
So a source column with no declared scale feeding a warehouse column with a declared scale is a rounding operation, not a copy. The rounding is deterministic and silent. It does not raise. It does not warn. It does not change the row count.
Why row-count reconciliation is structurally blind
A row-count check verifies cardinality. It answers one question: did the same number of rows arrive? It says nothing about value fidelity.
Consider a payments table. The source column is numeric with no scale. The warehouse column is NUMBER(18,2). A refund of 0.005 rounds to 0.01 in the warehouse and stays 0.005 in the source. Row counts match. The sum differs by a fraction of a cent per row. This is illustrative, not a reported incident.
The same blindness applies to floating-point paths. Snowflake’s FLOAT type uses double-precision (64 bit) IEEE 754 floating-point numbers with precision of approximately 15 digits. Snowflake recommends comparing two floating-point numbers for approximate equality rather than exact equality. PostgreSQL’s real and double precision types are inexact, variable-precision numeric types, and comparing two floating-point values for equality might not always work as expected.
If your reconciliation compares count(*) and nothing else, you are testing the one property that decimal coercion does not change.
The reconciliation query pattern
Compare three things per money column per key range and time window: row count, SUM of the column, and a scale probe. The scale probe is the part most teams skip.
SUM catches aggregate drift. It does not catch offsetting errors. One row rounded up and another rounded down can net to the same total. The scale probe catches the case where rounding is uniform enough that SUM happens to match.
A scale probe asks: what is the maximum number of digits actually present to the right of the decimal point in this column, over this key range? If the source returns 4 and the warehouse returns 2, the warehouse has coerced. The exact expression depends on your engine; the point is to measure the stored scale, not the declared scale.
For per-row fidelity, add a hash or checksum of the money column to the same test. SUM plus scale probe plus per-row hash is one test, not three. Splitting them across separate jobs means a failure in one does not block the others, and the signal gets lost in the noise.
In dbt, this is a singular data test. Data tests are assertions about models and other resources, and a test passes when it returns zero failing rows. Data tests return one row for each failure, and the columns in the test’s SQL select statement are the columns visible when you debug failures, including when you store test failures (dbt data tests).
Write the test so it returns the key range, the source sum, the warehouse sum, the source scale, and the warehouse scale. When it fails, you want the numbers in the row, not a boolean.
The type contract is the artifact under test
The reconciliation query is testing a contract: the source column’s precision and scale must be compatible with the warehouse column’s precision and scale. If the source is unconstrained and the warehouse is declared, the contract is lossy by construction.
Pair the reconciliation query with a schema-diff check that fails when the two sides’ precision and scale declarations diverge. The schema diff catches the drift at deploy time. The reconciliation query catches it at data time. You need both because a schema diff cannot see values that were already rounded before the schema changed.
This is where the decision gets expensive. Snowflake’s DECFLOAT type stores numbers exactly, with up to 38 significant digits of precision, and uses a dynamic base-10 exponent to represent very large or small values. Snowflake lists ledgers, taxes, or compliance as use cases requiring exact numeric values, and notes that use of the DECFLOAT type might cause storage consumption to increase. The NUMBER and FLOAT types might provide better performance than the DECFLOAT type.
So the choice is not free. DECFLOAT buys exactness and costs storage and possibly performance. NUMBER with an explicit scale buys predictability and costs the fractional digits you did not declare. FLOAT buys range and costs exactness. There is no option that is free on all three axes.
CDC and backfill make it worse in a specific way
Debezium’s PostgreSQL connector relies on logical decoding, which does not support DDL changes. The connector is unable to report DDL change events back to consumers (Debezium PostgreSQL connector).
This means a column type change on the source is invisible to the connector. The streaming path continues. If the connector stops for any reason, upon restart it continues reading the WAL where it last left off. If it stops during a snapshot, it begins a new snapshot when it restarts.
The failure mode: a DDL change lands between the streaming path and the backfill path. The streaming path read the column under the old type. The backfill reads it under the new type. Both paths produce rows. Both paths pass row-count reconciliation. The values differ.
This is why reconciliation must run after backfills, not only after initial load. A backfill that re-reads a column after a type change can produce a different value than the streaming path for the same logical row. The reconciliation query is the only signal, because the connector will not tell you the type changed.
Iceberg’s schema evolution goals state that schema evolution supports safe column add, drop, reorder and rename, including in nested structures (Iceberg table spec). Safe in this context means the metadata is consistent. It does not mean your source and warehouse type declarations agree. The reconciliation query is still yours to write.
Pricing the fix
The query is cheap. Adding a singular dbt test or an Airflow task that compares SUM, scale probe, and per-row hash across source and warehouse is hours of work, not weeks. It runs on the same schedule as your existing reconciliation.
The expensive part is the type audit. You need to enumerate every money column on both sides, compare precision and scale declarations, and decide for each one whether to pin an explicit scale, migrate to DECFLOAT, or accept the rounding and document it. That audit is days to weeks depending on column count and how many teams own the schemas.
The migration decision is the expensive part. The query is the cheap part. Do the query first, because it tells you which columns actually need the migration decision.
Airflow’s TaskFlow API uses XComs to move inputs and outputs between tasks and requires that variables used as arguments be serializable (Airflow TaskFlow). If you pass reconciliation results between tasks, keep them to scalars and small dicts. Do not pass result sets through XCom.
The maintenance tax
Every new money column added to the warehouse is a new place the source/warehouse type contract can drift. The reconciliation query is a standing test, not a migration checklist item, because the drift is introduced by future schema changes, not by the original load.
The tax is not the query. The tax is the review step that asks, for every new money column, what the source scale is and what the warehouse scale is, and whether the difference is intentional. That review is minutes per column if it is part of the schema change process, and hours per column if it is discovered after the fact.
Row counts will keep matching. The money will keep not matching. The only thing that changes is whether you find out before or after the controller does.
FAQ
Does unique or not_null catch this? No. dbt ships with four generic data tests: unique, not_null, accepted_values, and relationships. None of them compares values across two systems. You need a singular test or a custom generic test that queries both sides.
Can I just compare SUM? No. SUM can hide offsetting errors. One row rounded up and another rounded down can net to the same total. Compare SUM, a scale probe, and a per-row hash in the same test.
Should I migrate everything to DECFLOAT? Not without pricing it. Snowflake notes that DECFLOAT may increase storage consumption and that NUMBER and FLOAT might provide better performance. Run the reconciliation query first to find which columns actually need exactness, then decide per column.
Why not just declare a scale on the source column? That is one valid fix. It makes the source coerce to the same scale as the warehouse, so the rounding happens on both sides. The tradeoff is that you are now rounding at the source, which may not be acceptable for the business logic that reads the source directly.
How often should the reconciliation run? After every backfill, after every schema change, and on the same schedule as your existing reconciliation. The backfill case is the one teams miss, because the backfill passes row-count reconciliation and looks healthy.