← Blog

Snowflake streams: the one DDL change you must never make

June 18, 2026 · 6 min read

There is a category of production incident that has no recovery path. Not “restore from backup” hard — no recovery path. In Snowflake, the most common way to trigger one is a single, innocent-looking line of DDL against a stream, or against the table underneath it.

This post explains what a stream actually stores, the three DDL changes that silently destroy it, why Time Travel will not save you, and the checklist that prevents it.

What a stream actually is (and isn’t)

A Snowflake stream is not a copy of changed rows. It stores almost nothing: an offset — a logical pointer into the source table’s version timeline. When you SELECT from the stream, Snowflake computes the delta between that offset and the table’s current version on the fly, using the table’s underlying version history.

Two consequences follow from this design, and both bite:

1. The stream’s entire value is the offset. The changed rows themselves live in the source table’s version history. The stream is a bookmark, and the bookmark is the only record of where your pipeline got to.
2. The offset is not data you can back up. You can’t export it, clone it meaningfully, or reconstruct it after the fact. GET_DDL on a stream gives you the CREATE STREAM statement — not the position.

When a stream is consumed inside a DML transaction (INSERT INTO target SELECT * FROM my_stream), the offset advances atomically with the commit. That’s the contract that makes streams exactly-once: the bookmark moves only when the read is durably written.

The three DDL changes that destroy it

1. DROP STREAM (or CREATE OR REPLACE STREAM)

-- Looks like a harmless refactor. It is not.
CREATE OR REPLACE STREAM orders_stream ON TABLE orders;

CREATE OR REPLACE is drop-and-recreate. The new stream’s offset initializes to now. Every change recorded between your pipeline’s last consumption and this moment is no longer visible to the stream — not deleted from the table’s history, but no longer enumerable as a delta. Your pipeline simply never sees those rows.

The insidious part: nothing fails. The next pipeline run executes cleanly, processes whatever happens after the recreate, and reports success. You discover the gap days later when a downstream aggregate doesn’t reconcile, and by then you’re reverse-engineering “which rows did we miss” from query history and hope.

2. CREATE OR REPLACE TABLE on the source table

This is the one that surprises people. The stream survives — it still exists, SHOW STREAMS still lists it — but it is now stale forever:

CREATE OR REPLACE TABLE orders AS SELECT * FROM orders_v2_migration;
-- orders_stream is now permanently broken:
SELECT * FROM orders_stream;
-- => Base table 'ORDERS' dropped, cannot read from stream ...

A stream tracks a specific table object, not a table name. CREATE OR REPLACE creates a new object under the same name; the old object — the one the stream’s offset points into — is gone. There is no ALTER STREAM ... REPOINT. The only path forward is recreating the stream, which is failure mode #1.

Renames have the same shape: swap a table out from under a stream (ALTER TABLE ... SWAP WITH) and the stream follows the object, not the name your pipeline thinks it’s tracking.

3. Letting the offset age past the retention window

A stream’s offset is only readable while the table’s version history covers it. If a consumer stops running (a paused task, a dead Airflow DAG, a holiday freeze), the offset ages. Snowflake extends the table’s retention to protect it — up to MAX_DATA_EXTENSION_TIME_IN_DAYS, default 14 days — and then the stream goes stale. A stale stream cannot be read at all; the delta is unrecoverable.

SHOW STREAMS;
-- check the "stale" and "stale_after" columns. "stale_after" is a countdown.

If you monitor one thing about streams, monitor stale_after.

Why Time Travel won’t save you

The reflex is “we have Time Travel, we can restore.” Time Travel restores tables. It does not restore stream offsets:

• UNDROP STREAM does not exist. (UNDROP TABLE exists; streams aren’t tables.)
• Restoring the source table to a prior version doesn’t move the stream’s bookmark back.
• A zero-copy clone of a schema clones the streams, but cloned streams in pre-2023 behavior reset their offsets; even today, the clone’s offset can’t tell you what the production pipeline had and hadn’t consumed at the moment of the incident.

Compare the failure classes. Drop a column and Time Travel can usually give it back — destructive, but recoverable. Drop a stream and the platform has nothing to give back — the offset existed in exactly one place and you deleted it. These are different categories of risk, and most review processes — a Slack thread and a senior engineer eyeballing DDL — treat them identically.

The checklist

If a change touches a stream or a table that has streams on it:

1. Enumerate the streams first. SHOW STREAMS ON TABLE orders; before any DDL on orders. If you didn’t know the table had streams, that’s the incident report writing itself.
2. Never CREATE OR REPLACE a table with streams on it. Use ALTER TABLE for additive changes. If you genuinely must rebuild the table, plan a stream cutover: drain the stream (consume to empty), pause producers, rebuild, recreate the stream, resume.
3. Drain before any stream recreate. A recreated stream with a fully-drained predecessor loses nothing. The loss is exactly the unconsumed delta — so make it empty.
4. Alert on stale_after. A scheduled query over SHOW STREAMS that pages someone when any production stream is within 48 hours of staleness costs nothing and has saved real pipelines.
5. Gate it in review. Make “does this DDL touch an object with a cursor?” a mechanical check, not a memory check.

Making the gate mechanical

That last item is the actual fix. Humans don’t reliably remember that streams (and tasks mid-schedule, and pipes with load history) carry state that DDL can vaporize — especially at 5pm, especially when the diff looks like a rename.

This is the problem I built SDT around: schema-as-files for Snowflake with a compare engine where every proposed change gets a safety classification — SAFE / EXPENSIVE / DESTRUCTIVE / UNRECOVERABLE. A column drop classifies DESTRUCTIVE (Time Travel can restore it). A stream drop classifies UNRECOVERABLE — and the deploy refuses to run unless you pass an explicit gate, because the tool knows the cursor cannot be reconstructed:

$ sdt compare --project ./Warehouse --target prod

  ~ TABLE  ANALYTICS.PUBLIC.ORDERS          rebuild required     [DESTRUCTIVE]
  - STREAM ANALYTICS.PUBLIC.ORDERS_STREAM   cursor will be lost  [UNRECOVERABLE]

  1 UNRECOVERABLE change — publish refused.
  To proceed: --allowUnrecoverableDrop (drain ORDERS_STREAM first; see remediation)

The deterministic core — compare, safe deploy, extract, lint, lineage — is free, runs offline, and there’s a 60-second demo of the refusal if you want to see it without installing anything: npm i -g @sdt-tools/cli.

But tool or no tool: go run SHOW STREAMS; in production today and look at two columns — stale_after, and the list of source tables you were sure didn’t have streams on them. One of them does.

Independent tool, not affiliated with or endorsed by Snowflake Inc. “Snowflake” is a trademark of Snowflake Inc.