Most Redshift teams have their transformation layer under version control — dbt models, SQL files, a CI job. The part that stays manual for years is DDL: the column somebody added by hand in production at 11pm, the sort key change nobody dares run, the type widening that turned into a four-hour table lock. This tutorial covers how to make schema changes on Amazon Redshift repeatable, reviewable and short enough that readers never notice.
Everything here applies to both provisioned RA3 and Redshift Serverless.
What ALTER TABLE can and cannot do
Redshift's ALTER TABLE is narrower than PostgreSQL's, and knowing the boundary tells you which changes are cheap and which need a rebuild.
Cheap, metadata-only (milliseconds, no rewrite):
ALTER TABLE sales.orders ADD COLUMN channel VARCHAR(40); -- added at the end, NULL-filled
ALTER TABLE sales.orders RENAME COLUMN chanel TO channel;
ALTER TABLE sales.orders DROP COLUMN legacy_flag;
ALTER TABLE sales.orders RENAME TO orders_v2;
ALTER TABLE sales.orders ALTER COLUMN order_note TYPE VARCHAR(2000); -- widening VARCHAR
ALTER TABLE sales.orders ALTER DISTSTYLE KEY DISTKEY customer_id; -- background, but see below
ALTER TABLE sales.orders ALTER SORTKEY (order_date, customer_id);
ALTER TABLE sales.orders ALTER SORTKEY AUTO;
Not supported, and therefore a rebuild:
- Changing a column's data type across families (
VARCHAR→INT,INT→BIGINT,TIMESTAMP→TIMESTAMPTZ). - Narrowing a
VARCHAR, or shrinking any type. - Inserting a column in a specific ordinal position.
- Adding a column with a
DEFAULTthat must be backfilled atomically (ADD COLUMN ... DEFAULTis allowed, but existing rows are not rewritten with the default — they get NULL). - Adding
NOT NULLto an existing column.
Two more facts worth pinning to the wall:
- Redshift DDL is transactional.
BEGIN; ALTER TABLE …; ALTER TABLE …; COMMIT;either all lands or none of it does. Use that. ALTER TABLEtakes anACCESS EXCLUSIVElock on the table. Even a metadata-only change will queue behind a long-runningSELECTand then block every query behind it. This is the real cause of most "the ALTER hung the warehouse" incidents — the ALTER was instant, the lock queue was not.
Set a lock timeout on every migration session
Never let a migration sit in the lock queue indefinitely:
SET lock_timeout TO 5000; -- milliseconds
SET statement_timeout TO 600000; -- 10 minutes
If the migration fails on a timeout, that is a success: it failed fast, nothing queued behind it, and the runner can retry after the long query finishes. Before a risky change, check what is holding locks:
SELECT l.table_id, t."table", l.transaction_id, l.pid, s.user_name,
DATEDIFF(second, s.starttime, GETDATE()) AS age_s,
LEFT(s.query_text, 120) AS query
FROM sys_transaction_history l
JOIN svv_table_info t ON t.table_id = l.table_id
LEFT JOIN sys_query_history s ON s.transaction_id = l.transaction_id
WHERE t."table" = 'orders'
ORDER BY age_s DESC;
Pattern 1: additive change (the 95% case)
Most changes can be made additive, and additive changes are safe to deploy any time:
BEGIN;
SET lock_timeout TO 5000;
ALTER TABLE sales.orders ADD COLUMN channel VARCHAR(40);
COMMIT;
-- backfill in batches, outside the DDL transaction
UPDATE sales.orders
SET channel = 'web'
WHERE channel IS NULL
AND order_date >= '2026-01-01';
Backfill in date or ID ranges rather than one statement over a billion rows; each UPDATE in Redshift is a delete-plus-insert, so a whole-table update doubles the table's blocks until vacuum reclaims them. After a large backfill:
VACUUM DELETE ONLY sales.orders;
ANALYZE sales.orders;
Rule: expand, migrate, contract. Add the new column, dual-write to old and new, move readers, then drop the old column in a later release. Never rename-in-place a column that a live BI dashboard selects.
Pattern 2: rebuild with a deep copy and a rename swap
For type changes, narrowing, reordering columns or a wholesale encoding change, build a new table and swap it in. The swap is two renames inside one transaction, so readers see either the old table or the new one and never an empty one.
-- 1. New definition, built offline
CREATE TABLE sales.orders__new (
order_id BIGINT NOT NULL ENCODE az64,
customer_id BIGINT NOT NULL ENCODE az64,
order_date DATE NOT NULL ENCODE az64,
order_ts TIMESTAMPTZ ENCODE az64, -- was TIMESTAMP
amount DECIMAL(18,4) ENCODE az64,
channel VARCHAR(40) ENCODE lzo
)
DISTSTYLE KEY DISTKEY (customer_id)
COMPOUND SORTKEY (order_date, customer_id);
-- 2. Load it
INSERT INTO sales.orders__new
SELECT order_id, customer_id, order_date,
CONVERT_TIMEZONE('UTC', order_ts), amount, channel
FROM sales.orders;
ANALYZE sales.orders__new;
-- 3. Swap, atomically
BEGIN;
SET lock_timeout TO 5000;
ALTER TABLE sales.orders RENAME TO orders__old;
ALTER TABLE sales.orders__new RENAME TO orders;
COMMIT;
Then re-grant and clean up:
GRANT SELECT ON sales.orders TO ROLE analyst;
-- keep orders__old for a day, then:
DROP TABLE sales.orders__old;
Three things that bite here:
- Grants do not follow the rename. The new table has the grants you gave
orders__new, not the ones the oldordershad. Capture them first fromSVV_RELATION_PRIVILEGESand replay them, or create the new table with the same owner and rely on default privileges (ALTER DEFAULT PRIVILEGES). - Views break or go stale. A normal view binds to the table's OID and will error after the swap; a
LATE BINDING VIEW(or one createdWITH NO SCHEMA BINDING) resolves by name at query time and survives. Late-binding views are the right default in a warehouse that does swaps. - Materialized views over the old table must be dropped and recreated; they do not follow renames.
If you only need to move data between two tables that already have identical column definitions, ALTER TABLE … APPEND moves the blocks instead of copying them — dramatically faster than INSERT … SELECT, and it empties the source table:
ALTER TABLE sales.orders APPEND FROM sales.orders_staging;
Pattern 3: blue/green at the schema level
For a release that changes many tables at once — a dimensional remodel, a dbt refactor — swap schemas, not tables:
CREATE SCHEMA sales_next;
-- build everything into sales_next (dbt: --target with schema=sales_next)
BEGIN;
ALTER SCHEMA sales RENAME TO sales_prev;
ALTER SCHEMA sales_next RENAME TO sales;
COMMIT;
Rollback is the same two statements reversed, which is what makes this worth the storage. Keep sales_prev until the next release. The caveats are the same: late-binding views, re-grants, and BI tools that cached an OID.
For a whole-warehouse change (a major resize, a Serverless move), the equivalent is a snapshot restore into a parallel cluster plus workload replay with Redshift Test Drive before you cut over.
Putting migrations in version control
Two approaches work in practice.
Numbered SQL files plus a runner. A migrations/ directory of V0042__add_orders_channel.sql files and a small tracking table:
CREATE TABLE IF NOT EXISTS ops.schema_migrations (
version VARCHAR(20) NOT NULL,
name VARCHAR(200),
checksum VARCHAR(64),
applied_at TIMESTAMPTZ DEFAULT GETDATE(),
applied_by VARCHAR(100) DEFAULT CURRENT_USER
);
The runner reads the directory, skips versions already present, and applies each file inside a transaction that also inserts the tracking row — so a failed migration leaves no trace and no half-state. Flyway and Liquibase both have Redshift support and will do this for you; for a small team, sixty lines of Python over the Redshift Data API is enough and needs no VPC attachment from CI.
dbt for everything it can own. If a table is a dbt model, let dbt own its DDL — dist, sort and full_refresh handle rebuilds. Reserve the migration runner for the things dbt does not own: source/staging tables, COPY targets, roles and grants, external schemas, stored procedures, and UDFs. Splitting on that line avoids two tools fighting over the same object.
A CI pipeline that catches the expensive mistakes
In a pull request, run against a throwaway namespace restored from a recent snapshot (Serverless makes this cheap — create the namespace from a recovery point, run, delete):
- Lint. Reject
DROP TABLE,TRUNCATE, un-batchedUPDATE/DELETEwithout aWHERE, andALTER TABLEwithout a precedingSET lock_timeout— a regex gate catches almost all of the dangerous ones. - Apply forward. Run every pending migration against the restored copy. A migration that fails here never reaches production.
- Apply the rollback. Require a paired
.down.sqlfor anything non-additive and prove it runs. - Diff the resulting schema. Compare
SVV_COLUMNSfor the target schemas between the restored copy and production, and post the diff on the PR. Reviewers approve a readable column diff far more reliably than they approve SQL. - Smoke-test the contract. Run the handful of queries your BI layer depends on, and a
SELECT COUNT(*)reconciliation between old and new tables for any rebuild.
Drift detection
Even with all of this, someone will run DDL by hand. Catch it: snapshot SVV_COLUMNS, SVV_TABLE_INFO and SVV_RELATION_PRIVILEGES nightly into an ops schema and alert on any difference from the previous day that is not explained by a schema_migrations row from the same window.
-- new/changed columns since yesterday's snapshot
SELECT c.table_schema, c.table_name, c.column_name, c.data_type
FROM svv_columns c
LEFT JOIN ops.columns_snapshot s
ON s.table_schema = c.table_schema
AND s.table_name = c.table_name
AND s.column_name = c.column_name
AND s.data_type = c.data_type
WHERE c.table_schema IN ('sales','finance')
AND s.column_name IS NULL;
Pair that with the alerting described in Monitoring and Alerting for Amazon Redshift and hand-edits become visible within a day instead of at the next incident.
Checklist
- Additive first; expand, migrate, contract.
SET lock_timeoutin every migration session; fail fast rather than queue.- Rebuild-and-rename for type changes, narrowing and key changes — inside one transaction.
- Replay grants after every rename; prefer late-binding views.
- Batch backfills, then
VACUUM DELETE ONLYandANALYZE. - Version every DDL statement; test it against a restored snapshot in CI.
- Detect drift nightly.
If you would like a review of how schema changes currently reach your production warehouse — or help building the migration runner and CI gate — that is part of our Redshift Administration and Data Modeling & Architecture work. Get in touch.