Streams, Replication and Data Recovery in Snowflake

1.Streams depend on the source table's history

A stream records the DML changes made to a source table, a process called change data capture. It doesn't hold a copy of those changes. A stream stores only an offset, which is a point in the table's version history, and when you query it, it builds the change records from the table's versioning history. That makes a stream a recovery concern: it only works while the history it points to still exists. The offset advances only when you consume the stream in a DML transaction. Simply querying it leaves the offset where it is.

A stream becomes stale when its offset falls outside the source table's retention period. A stale stream can no longer read the unconsumed change records, and the only way to keep tracking changes is to recreate it with CREATE STREAM. To prevent this, Snowflake temporarily extends the retention period of a table that has an unconsumed stream, as long as the table's period is under 14 days. Once the stream is consumed, retention goes back to the table's own setting.

The MAX_DATA_EXTENSION_TIME_IN_DAYS parameter caps this extension. It defaults to 14, can be set from 0 to 90, and applies at account, database, schema and table level. Setting it to 0 turns the extension off, which can help control storage costs or meet compliance requirements. The window in which you must consume the stream is the greater of DATA_RETENTION_TIME_IN_DAYS and MAX_DATA_EXTENSION_TIME_IN_DAYS. In the 14/0 row below, the table's own 14-day retention is the greater value, so no extension is needed and the window is still 14 days. In the other rows, the extension cap is the greater value.

To check a stream, run SHOW STREAMS or DESCRIBE STREAM. The STALE_AFTER timestamp is the last time the stream was consumed plus the greater of the two parameters, and consuming the stream moves it forward. Once that timestamp has passed, the stream can become stale at any moment, even if it has no unconsumed records. Calling SYSTEM$STREAM_HAS_DATA on a stream also keeps it from going stale, but only when the stream is empty and the function returns FALSE. Two less obvious cases also matter. Streams on shared tables or views don't extend retention at all. And recreating the source with CREATE OR REPLACE TABLE throws away its history, so any stream on it becomes stale immediately.

Sources12

2.Cross-region and cross-cloud replication

Time Travel and Fail-safe protect data inside one account. Replication keeps copies in other accounts in the same organization, and works across regions and across cloud platforms (AWS, Google Cloud and Azure). Within a region group you can replicate between any regions. Replicating to a government or Virtual Private Snowflake region needs Snowflake Support.

There are two mechanisms. Database replication is the older, limited feature. It makes a database the primary and creates read-only secondary databases in target accounts, and each refresh copies a snapshot of the primary. It doesn't replicate privilege grants, account parameters, stages or pipes. Snowflake strongly recommends account replication instead. Account replication uses replication groups, which are sets of objects replicated as a unit with point-in-time consistency and read-only access. A failover group is a replication group that can also fail over: you can promote any target account in its list of allowed accounts, and that secondary becomes the read-write primary.

A group has a replication schedule, for example REPLICATION_SCHEDULE = '10 MINUTE', that controls how often it refreshes. You can change the schedule, and you can pause or resume it in a target account with SUSPEND and RESUME on ALTER REPLICATION GROUP or ALTER FAILOVER GROUP. A replication group can't be turned into a failover group or back; to change that, delete the group and recreate it. Replication has its own cost, which you can follow in the DATABASE_REPLICATION_USAGE_HISTORY view.

Edition also matters when the primary and target accounts differ. If the primary database is in a Business Critical (or higher) account and an approved target account is on a lower edition, promoting the database to primary shows an error. An account administrator can override this by adding the IGNORE EDITION CHECK clause to ALTER DATABASE … ENABLE REPLICATION TO ACCOUNTS.

Not every object is replicated. Permanent and transient tables are. The table of replicated database objects shows no replication for temporary tables, external tables or hybrid tables. Objects that aren't supported are skipped during replication and won't be available in the target account after failover. Streams themselves are replicated, but with conditions. Append-only streams aren't supported on replicated source objects. A refresh fails if the primary database contains a stream with an unsupported source object, or if a stream's source object has been dropped. So a stream left behind after its source was dropped can block your disaster-recovery copy from refreshing.

Sources345

3.Time Travel and Fail-safe on secondary databases

A secondary database is not a copy of the primary's history. Time Travel and Fail-safe data for a secondary is kept separately and is not replicated from the primary. A refresh copies only the latest version of each table. So if Snowpipe loads a table every 10 minutes and the secondary refreshes hourly, Time Travel on the secondary can show each hourly version within its retention period, but none of the individual loads in between. The same Time Travel query can therefore give different results on the primary and the secondary. On the secondary, the retention clock for changed data starts when the refresh brings those changes over.

Retention settings are partly replicated. DATA_RETENTION_TIME_IN_DAYS is replicated for schemas and tables when it was set explicitly on them, and it overwrites the value on the matching secondary object. Database-level parameters are not replicated, and a value set explicitly at database level on the secondary stays as it is.

On the primary, Time Travel also helps you verify a replica. Read the snapshot timestamp of the latest refresh, then compare HASH_AGG on the secondary table with HASH_AGG on the primary table at that same timestamp.

SELECT HASH_AGG( * ) FROM mydb.myschema.mytable AT(TIMESTAMP => '<primarySnapshotTimestamp>'::TIMESTAMP);

Sources64

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.An unconsumed stream keeps its source table's history for as long as it needs.Why is that wrong?

   The extension goes back only to the stream's offset and is capped by MAX_DATA_EXTENSION_TIME_IN_DAYS (14 days by default). Streams on shared tables don't get any extension.

   Covered in Streams depend on the source table's history

2. 2.After failover, the secondary has the same Time Travel and Fail-safe history as the old primary.Why is that wrong?

   Each secondary keeps its own history, built from the versions its refreshes delivered, so changes made between refreshes can't be reached with Time Travel there.

   Covered in Time Travel and Fail-safe on secondary databases

3. 3.Failover groups are available on every edition, just like database replication.Why is that wrong?

   Database replication and replication groups are available on every edition, but failover groups and replication of account objects other than databases and shares need Business Critical Edition or higher.

   Covered in Cross-region and cross-cloud replication