Changing a Column Type Without Rewriting the Table

The classic pain: a quantity INT column approaches the 2.1 billion ceiling, or a source system starts sending BIGINT. Historically your options were a full-table rewrite with overwriteSchema, or a new table plus backfill — terabytes moved to change a label, because every int already is a valid bigint.

The whole migration sql

ALTER TABLE gold.orders
  SET TBLPROPERTIES ('delta.enableTypeWidening' = 'true');

ALTER TABLE gold.orders ALTER COLUMN quantity TYPE BIGINT;
-- metadata-only: no files rewritten, history intact

What qualifies is exactly what the name says — widening: the integer family upward (byte → short → int → long), float → double, decimal precision growth, date → timestamp_ntz. Two deliberate exceptions: integer types to decimal or double must be done manually with ALTER TABLE — the engine won’t silently promote integers to decimals for you. Narrowing is never on the table.

The quiet superpower is the automatic mode: with type widening enabled and schema evolution on, an INSERT or MERGE whose source column is wider than the target widens the column instead of failing. Ingestion survives an upstream type bump without a 3 a.m. page. (How that interacts with mergeSchema is its own piece.)

The principle underneath: distinguish changes to representation from changes to values. Widening never touches a value, so it should never cost a rewrite — and now it doesn’t. Save the heavy machinery for changes that actually transform data.