Changing a Column's Type in Place
Concepts covered: Basic Types, Numeric Precision
The prompt is usually a requirement with a deadline attached: "order_id is about to pass the INT limit, widen it to BIGINT, the table takes writes around the clock", or "product needs 4 decimal places on amount". The interviewer is listening for a classification before a plan. Some type changes are a catalog edit that finishes in milliseconds, some rewrite every row under a lock, and some change what the existing values mean.
3 kinds of change
| Change | PostgreSQL | Iceberg | Snowflake |
|---|---|---|---|
| Lengthen VARCHAR | Catalog only | Not applicable: strings are unbounded | Catalog only |
| Raise DECIMAL precision, same scale | Catalog only | Catalog only | Catalog only |
| INT to BIGINT | Full rewrite under ACCESS EXCLUSIVE lock | Catalog only; old files read as BIGINT | No-op: integers are all NUMBER(38,0) |
| FLOAT to DOUBLE | Full rewrite | Catalog only | No-op: FLOAT is already 64-bit |
| Change DECIMAL scale | Full rewrite | Rejected | Not supported in place |
| Text to number, or any narrowing | Rewrite that fails on the first bad value | Rejected | Not supported in place |
PostgreSQL rewrites the table and rebuilds every index for INT to BIGINT while holding an ACCESS EXCLUSIVE lock, so on a 3-billion-row orders table nothing can read or write the table for the hours the ALTER runs. Iceberg, and Delta Lake with its type widening feature enabled, record the promotion in table metadata and widen old values at read time, which is why a lake table absorbs the same change in 1 commit. Raising a DECIMAL's precision without touching its scale is catalog-only in all 3 engines.
Safe for the table, unsafe for its readers
A catalog-only change can still break consumers, and this is the half of the answer most people skip. The table accepted the new type; every system that reads it holds its own copy of the old one.
- A pandas or Arrow job with a pinned int32 schema errors or wraps once a value passes the 32-bit ceiling.
- An ORM entity mapped to a 32-bit Integer throws on the first row past the old limit.
- A downstream CREATE TABLE AS that froze DECIMAL(10,2) rounds 4-place amounts back to 2.
- An Avro consumer on the old schema cannot decode records written as long.
- A BI extract with cached column types shows the column as text or drops it.
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.
Changes that alter meaning
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 requirement is worth pressure-testing before any plan. An INT key at 1.6 billion, growing by 1 million rows a day, has around 18 months of headroom: a migration to schedule next quarter rather than run tonight. Saying so, with the arithmetic, is often the strongest moment in the answer.
Expand and contract for the breaking ones
Any change that rewrites the table or narrows the type goes through a parallel column, as does any change in meaning. That keeps every step reversible until the last one.
| Step | What runs | Rollback |
|---|---|---|
| 1. Expand | Add order_id_v2 BIGINT, nullable: a catalog-only change | Drop the column |
| 2. Dual-write | A trigger or the application writes both columns | Stop writing v2 |
| 3. Backfill | Copy in batches of about 50,000 rows by key range, throttled on replica lag | Stop the job |
| 4. Verify | Rows where order_id_v2 IS DISTINCT FROM order_id must number 0 | Fix and re-run |
| 5. Switch readers | Views, ORMs and downstream jobs move to v2, one at a time | Point back at the old column |
| 6. Contract | Stop writing the old column, drop it after the deprecation window | None: the one-way step |
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.
About this lesson
This section is part of the Type Casting: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.