Postgres logical decoding reports every row you change and none of your DDL. Drop a column and the change stream doesn’t say so. The next event for that table just arrives one field short.
Expand and contract wasn’t designed with that in view. The pattern keeps one client running through a schema change: add the new column, write to both, backfill, switch the reads, drop the old one. Each step is safe for the application, and the application team can tell when it’s finished, because it owns all the code that reads the table.
Except it doesn’t own all the readers. A CDC connector follows the table into a warehouse, where models select the old column by name. I count those readers as one more client of the old schema. A slow one, usually. The drop waits for the last of them.
What the warehouse gets when a column is dropped
Take a common pipeline: Debezium on a replication slot, Kafka, a loader, dbt. After the ALTER TABLE, Debezium publishes events without the field. Loaders rarely remove a column on their own, so the landing table keeps it and stores null for every new row. The staging model still compiles and the models above it still run. If the column was the order date, new orders drop out of every daily revenue number, since a null date falls on no day, and every job stays green.
A dbt model contract doesn’t catch this. Contracts came in with dbt Core 1.5 and check a model’s column names and types at build time. A column full of nulls passes on both counts. A not_null test would notice, if someone wrote one for that column, and only once the nulls are already in the landing table.
So the protection has to come from the order of the steps, with the warehouse’s query log as the gate on the last one.
One rename, seen from three places
To put dates on it, take a home-goods retailer that doesn’t exist outside this page, Pallet and Pine. Its orders table holds 38 million rows, and placed_at, a timestamp stored in the head office’s Chicago time, is becoming placed_at_utc timestamptz. Debezium streams the table through Kafka into a landing table in Snowflake. A dbt staging model sits on top, and three models read placed_at from it.
| Step | SQL | Application sees | Change stream emits | Warehouse model must do |
|---|---|---|---|---|
| 1. Expand, day 0 | ADD COLUMN placed_at_utc timestamptz |
Nothing | No DDL event. The field first shows up on the next changed row | Nothing. Staging names its columns, so the new one stays out |
| 2. Dual write, day 0 | Trigger that syncs both columns | Nothing. Either column takes writes | Both values on every new or changed row | Publish placed_at_utc, filling gaps by converting placed_at from Chicago time |
| 3. Backfill, days 0 to 1 | Batched UPDATE |
Extra write load | 38 million update events | Keep up with the merges |
| 4. Cutover, day 2 | None | The new release reads and writes placed_at_utc |
Nothing new | Move the three models, then take placed_at out of staging |
| 5. Reader check, days 2 to 9 | Query-log check | Nothing | Nothing new | Nothing. The log has to go quiet |
| 6. Contract, day 9 | DROP COLUMN placed_at |
Nothing | Events lose placed_at |
Nothing. The landing column goes null, unread |
Four of the six rows say “nothing” under the application. That’s the pattern doing its job, and it’s why the application team’s sense of progress tells you so little about the two columns to its right.
The SQL, from expand to contract
Steps 1 and 2 ship together, in the release that starts the change.
-- Step 1: expand. Nullable with no default, so only the catalog changes.
SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN placed_at_utc timestamptz;
ALTER TABLE queues for an exclusive lock, and while it waits behind a long transaction, every other query on orders waits behind it. With the timeout it gives up after three seconds and the deploy retries.
-- Step 2: dual write. A write that sets the new column wins.
-- Any other write derives the new column from the old one.
CREATE FUNCTION orders_placed_at_sync() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
wrote_new boolean;
BEGIN
IF TG_OP = 'INSERT' THEN
wrote_new := NEW.placed_at_utc IS NOT NULL;
ELSE
wrote_new := NEW.placed_at_utc IS DISTINCT FROM OLD.placed_at_utc;
END IF;
IF wrote_new THEN
-- timestamptz AT TIME ZONE gives the Chicago wall-clock time.
NEW.placed_at := NEW.placed_at_utc AT TIME ZONE 'America/Chicago';
ELSE
-- timestamp AT TIME ZONE reads the value as Chicago time.
NEW.placed_at_utc := NEW.placed_at AT TIME ZONE 'America/Chicago';
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER orders_placed_at_sync
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION orders_placed_at_sync();
I’d rather do the dual write in a trigger than in application code. It covers writers a release doesn’t reach, like the old version mid-deploy or a hand-run script. It also keeps placed_at correct after the application has stopped writing it, which an app-only plan has no reason to do and the warehouse needs for another week.
One wrinkle comes with this particular change. When clocks fall back, an hour of Chicago times happens twice, and the old column can’t say which pass an order came from. Postgres picks one reading. I’d live with that for old rows, since orders placed after the cutover never go through the guess.
-- Step 3: one backfill batch. The job binds $1 to the batch's first order_id
-- and commits. It starts a batch every 9 seconds and moves $1 up by 5,000.
UPDATE orders
SET placed_at_utc = placed_at AT TIME ZONE 'America/Chicago'
WHERE order_id >= $1
AND order_id < $1 + 5000
AND placed_at_utc IS NULL;
-- After the backfill: NOT NULL without a long lock.
SET lock_timeout = '3s';
ALTER TABLE orders ADD CONSTRAINT orders_placed_at_utc_set
CHECK (placed_at_utc IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_placed_at_utc_set;
-- PostgreSQL 12 and later accept the validated check as proof and skip the scan.
ALTER TABLE orders ALTER COLUMN placed_at_utc SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_placed_at_utc_set;
Adding the check as NOT VALID and validating it separately lets Postgres scan the existing rows without blocking writes. If an index covers placed_at, build its twin on placed_at_utc with CREATE INDEX CONCURRENTLY before the cutover.
-- Step 6: contract. Only after the reader check returns no rows.
BEGIN;
SET LOCAL lock_timeout = '3s';
DROP TRIGGER orders_placed_at_sync ON orders;
DROP FUNCTION orders_placed_at_sync();
ALTER TABLE orders DROP COLUMN placed_at;
COMMIT;
38 million events the warehouse didn’t ask for
The application never notices a backfill. The stream gets one update event per row, 38 million of them, and logical decoding has no notion of an update that doesn’t matter. Every event crosses Kafka and gets merged into the landing table. That morning’s real orders wait in the same queue.
Postgres would go much faster than the job lets it. The pace is 5,000 rows a batch and a batch every 9 seconds, which comes to 2 million rows an hour and 19 hours in all. The rate was set on the warehouse side. It’s what Pallet and Pine’s loader can merge on the compute the company already pays for, with live orders still landing within minutes.
I’d watch the replication slot the whole time. If the connector stalls, Postgres keeps WAL for it until the disk fills, so the job compares confirmed_flush_lsn in pg_replication_slots with the current WAL position between batches and pauses when the gap grows. I’d also warn whoever owns an incremental model on this table. Its next run will treat every row as changed.
Seven days without a read of orders.placed_at
By day 2 the new release is out and the three models’ pull requests have merged. Staging no longer selects placed_at. That’s what the repositories say. The query log says what ran, and that includes the dashboard someone pointed straight at the landing table.
-- Snowflake Access History (Enterprise Edition). Lists who read the old
-- column in the last 7 days, directly or through a view. No rows means go.
SELECT ah.user_name,
count(DISTINCT ah.query_id) AS queries,
max(ah.query_start_time) AS last_read
FROM snowflake.account_usage.access_history AS ah,
LATERAL FLATTEN(input => ah.base_objects_accessed) AS obj,
LATERAL FLATTEN(input => obj.value:"columns") AS col
WHERE ah.query_start_time >= dateadd('day', -7, current_timestamp())
AND obj.value:"objectDomain"::string = 'Table'
AND obj.value:"objectName"::string = 'RAW.POSTGRES.ORDERS'
AND col.value:"columnName"::string = 'PLACED_AT'
AND ah.user_name <> 'KAFKA_LOADER' -- the loader's own merges
GROUP BY ah.user_name
ORDER BY last_read DESC;
Access History lists the columns each query touched, resolved through views down to the base table, so a select * counts. It can run up to three hours behind.
Any row back means the contract migration doesn’t run. That’s the loop in the diagram: back to dual write, where waiting costs one trigger and a column that was going away anyway. At Pallet and Pine the last read was on day 2. The check came back empty on day 9 and the drop ran that afternoon.
Seven days is my floor. It clears anything on a weekly schedule and nothing slower, so for a table that feeds a month-end close I’d wait out the close. The log also can’t see anything that consumes the Kafka topic directly. For those I’d list the topic’s consumer groups and go ask.
If nine days for one column sounds like a lot of ceremony, I’d agree, and the cause is that the warehouse reads a table whose shape belongs to the application. A data contract would fix that properly: a published shape for orders that the stream promises to keep, so the table could lose placed_at on day 2 while the stream goes on carrying it. Until one exists, I’d keep the trigger and read the log.