Type Casting: Advanced

Decimal width follows the same logic. Parquet stores a DECIMAL with precision up to 18 as a plain INT64, and Spark keeps such a value in a single 64-bit long during arithmetic. Declare DECIMAL(38,4) "to be safe" and every value takes 16 bytes and 128-bit arithmetic, on every row of every scan, for headroom the amount column will never use.

A mismatched key costs more than a wide one. Join payments.payment_id BIGINT to ledger_entries.payment_ref VARCHAR(32) and the engine has to convert one side on every row before it can hash anything. That conversion can also disable the runtime filters and dynamic partition pruning that let the probe side skip files, because the filter was built over one type and the scan reads another.

PostgreSQL's own trap runs the other way. WHERE payment_id = 880412.0 compares a BIGINT column with a NUMERIC literal. No index operator pairs those 2 types, so the planner casts the column to NUMERIC and the B-tree on payment_id goes unused. The explicit version, created_at::date = '2024-03-15' against an index on a timestamp, fails the same way, and the rewrite is a half-open range: created_at >= '2024-03-15' AND created_at < '2024-03-16'.

The most common production case never appears in the SQL. Microsoft's JDBC driver sends Java strings as NVARCHAR by default (sendStringParametersAsUnicode=true). NVARCHAR outranks VARCHAR, so a lookup on a VARCHAR column converts the column on every row. Under a SQL collation the seek becomes a scan; under a Windows collation the optimizer can still seek over a computed range, with a worse row estimate. The fix is a single connection-string flag, and the only way to find it is to read the plan.

The reflexive fix is to match the literal to the column: payment_ref = '880412' in place of = 880412. The seek comes back, and so does a different result set, because the numeric comparison matched every spelling of the number and the text comparison matches exactly one. Watch both comparisons evaluate over 6 ledger rows.

MySQL's DOUBLE path has a second edge. A double holds every integer exactly only up to a limit, and past it the conversion rounds, so payment_ref = 18014398509481985 matches a row holding '18014398509481984'. On 17-digit ids, comparing text to a number is a correctness bug as well as a scan.

The aggregate's type is half the story. In SUM(quantity * unit_price_minor) the multiplication runs per row, in the operands' type. The product of 2 INT columns is an INT, so a single line of 30,000 units at 90,000 minor units, a product of 2.7 billion, overflows before the sum ever sees it. Casting the SUM's result changes nothing; the widening has to reach an operand.

DECIMAL stays exact until an expression's result type outgrows 38 digits. Multiplying DECIMAL(p1,s1) by DECIMAL(p2,s2) yields precision p1 + p2 + 1 and scale s1 + s2. Past 38 digits, SQL Server cuts the scale, keeping at least 6 places, and Spark does the same when spark.sql.decimalOperations.allowPrecisionLoss is on, which it is by default. Convert DECIMAL(38,10) amounts with DECIMAL(38,10) rates and every product lands at scale 6, rounded row by row before the sum adds the rounding up. In Spark with ANSI mode off, a decimal product that overflows becomes NULL, which SUM then skips, and a SUM that overflows its own precision returns NULL. Neither raises an error.

The precise vocabulary comes from schema registries. INT to LONG is a BACKWARD-compatible Avro promotion: a reader on the new schema decodes old records. FORWARD compatibility fails: a reader still on the old schema cannot decode the new ones. That fixes the order of operations: upgrade every reader first, then let the producer write the wider type. Column-level lineage from dbt's manifest or OpenLineage supplies the list of readers, and the warehouse's query history catches the ones lineage misses.

Some changes run cleanly and corrupt data. In PostgreSQL, ALTER COLUMN created_at TYPE timestamptz interprets every existing value in the session's TimeZone setting, so a migration run from a laptop set to America/Los_Angeles shifts 2 years of UTC events by 7 or 8 hours. Converting text to integer erases leading zeros from codes that were never numbers, the same padded refs that split the reconciliation. Lowering a DECIMAL's scale rounds every stored value. For each, the question to ask is what an existing value means after the change, and who signed off on that meaning.

The backfill is batched and throttled because a single UPDATE over 3 billion rows is 1 transaction that bloats the table and holds back vacuum, and replicas stall behind it. The primary key swap is where plans most often bring back the outage. A primary key requires NOT NULL, and setting it on the nullable order_id_v2 scans all 3 billion rows under an ACCESS EXCLUSIVE lock. Instead, add CHECK (order_id_v2 IS NOT NULL) NOT VALID, then run VALIDATE CONSTRAINT, which scans under a lock that lets reads and writes continue. On PostgreSQL 12 and later, SET NOT NULL then trusts that check and skips the scan. Build the unique index with CREATE INDEX CONCURRENTLY. Then, in one short transaction, drop the old primary key and the foreign keys that reference it, and promote the index with ADD CONSTRAINT ... PRIMARY KEY USING INDEX. Recreate those foreign keys against order_id_v2 as NOT VALID, and validate them afterwards outside the lock.

A common birthplace is the connector. Debezium's decimal.handling.mode decides how a PostgreSQL NUMERIC travels: precise sends an exact decimal and string sends text. double sends a binary float, which reintroduces every rounding problem the source column was declared to avoid. A string amount in a warehouse is very often a connector setting chosen once, years ago, to make a deserializer error go away. Fixing it there corrects every node downstream at once.

The upstream fix then lands as an enforced contract. A dbt model contract declares each output column's data_type and fails the build when the model produces anything else; a schema registry rejects an incompatible producer schema at write time. Either one turns the next type drift into a failed deploy instead of a wrong dashboard.