Replication Considerations¶

Database and Share Objects¶

The following constraints apply to database and share objects:

• An object can only be in one failover group.

• An object can be in multiple replication groups as long as each group is replicated to a different target account.

• An object cannot be in both a failover group and a replication group.

• Secondary (replica) objects cannot be added to a primary replication or failover group.

Account Objects¶

An account can only have one replication or failover group that contains objects other than databases or shares.

Streams¶

Dangling references for streams cause replication to fail with the following error message:

Primary database: the source object ''<object_name>'' for this stream ''<stream_name>'' is not included in the replication group.
Stream replication does not support replication across databases in different replication groups. Please see Streams Documentation
https://docs.snowflake.com/en/user-guide/account-replication-considerations#replication-and-streams for options.

To avoid dangling reference errors:

• The primary database must include both the stream and its base object or

• The database that contains the stream and the database that contains the base object referenced by the stream must be included in the same replication or failover group.

Network Policies¶

Dangling references in network policies can cause replication to fail with the following error message:

Dangling references in the snapshot. Correct the errors before refreshing again.
The following references are missing (referred entity <- [referring entities])

To avoid dangling references, specify the following object types in the OBJECT_TYPES list when executing the CREATE or ALTER command for the replication or failover group:

• If a network policy is associated with the account, include NETWORK POLICIES and ACCOUNT PARAMETERS in the OBJECT_TYPES list.

• If a network policy is associated with a user, include USERS in the OBJECT_TYPES list.

For details, refer to Network Policies in the Replication of Security Integrations & Network Policies Across Multiple Accounts topic.

Password and Session Policies¶

Password and session policy references for users are replicated when specifying the database containing policy (ALLOWED_DATABASES = policy_db) and USERS in a replication group or failover group.

If either the policy database or users have already been replicated to a target account, update the replication or failover group in the source account to include the databases and object types required to successfully replicate the policy. Then execute a refresh operation to update the target account.

If user-level policies are not in use, USERS do not need to be included in the replication or failover group.

External Tables in a Primary Database¶

External tables in a primary database currently cause the replication or refresh operation to fail with the following error message:

003906 (55000): SQL execution error:
Primary database contains an external table '<database_name>'. Replication of a database with external table is not supported

To avoid this error, move the external tables into a separate database that is not replicated. Alternatively, if you are migrating your databases to another account, you could clone the primary database, drop the external table from the clone, and then replicate the cloned database. After you promote the secondary database in the target account, you would need to recreate the external tables in the database.

External Tables in a Secondary Database¶

External tables can exist in a secondary database if it was the primary database at a prior time and the external tables were created during that time. Once another database is promoted to be the designated primary database, it becomes a secondary database. External tables in a secondary database currently cause the refresh operation to fail with the following error:

003958 (55000): SQL execution error:
Secondary database contains an external table '<table_name>'. Replication of a database with external table is not supported.

To avoid this error, move the external table into a separate database that is not a replicated secondary database.

Dynamic Tables and Replication Groups¶

A database that contains a dynamic table can be replicated using a replication group. The base object(s) it depends on are not required to be in the same replication group.

Secondary objects are read-only in the target account. If a secondary replication group is dropped in a target account, the databases that were included in the group become read-write. However, any dynamic tables included in a replication group remain read-only even after the secondary group is dropped in the target account. No DML or Dynamic Table refreshes can happen on these read-only dynamic tables.

Dynamic Tables and Failover Groups and Database Replication¶

A database that contains a dynamic table can be replicated using a failover group. The base objects it depends on must be included in the same failover group. Otherwise, a dangling reference occurs during the refresh operation with the following error message:

002749 (55000): SQL execution error: Dynamic table <dt_name> has dependencies that are missing in the replication membership: <dependency_table_name>

The same applies if the dynamic table is replicated in a database using database replication. The base objects the dynamic table depends on must be included in the same database to avoid a dangling reference error.

Avoiding Data Loss¶

To avoid data loss in the case of failover, the data retention time for successfully inserted rows in your upstream data source must be greater than the replication frequency. If data is inserted into a table in a primary database, and failover occurs before the data can be replicated to the secondary database, the same data will need to be inserted into the table in the newly promoted primary database. The following is an example scenario:

1. Table t1 in primary database repl_db is populated with data with Snowpipe Streaming and the Kafka connector.

2. The offsetToken is 100 for channel 1 and 100 for channel 2 for t1 in the primary database.

3. A refresh operation completes successfully in the target account.

4. The offsetToken is 100 for channel 1 and 100 for channel 2 for the t1 in the secondary database.

5. More rows are inserted into t1 in the primary database.

6. The offsetToken is now 200 for channel 1 and 200 for channel 2 for the t1 in the primary database.

7. A failover occurs before the additional rows and new channel offsets can be replicated to the secondary database.

In this case, there are 100 missing offsets in each channel for table t1 in the newly promoted primary database. To insert the missing data, refer to Reopen Active Channels for Snowpipe Streaming in Newly Promoted Source Account.

Requirements¶

Snowpipe Streaming replication support requires the following minimum versions:

• Snowflake Ingest SDK version 1.1.1 and later

• If you are using the Kafka connector: Kafka connector version 1.9.3 and later

Supported Source Objects for Streams¶

Replicated streams can successfully track the change data for tables and views in the same database.

Currently, the following source object types are not supported:

• External tables

• Tables or views in databases separate from the stream databases.

  Note that this design works if both the stream database and the database that stores the source object are included in the same replication or failover group.

• Tables or views in a shared databases (i.e. databases shared from provider accounts to your account)

A database replication or refresh operation fails if the primary database includes a stream with an unsupported source object. The operation also fails if the source object for any stream has been dropped.

Avoiding Data Duplication¶

Data duplication occurs when DML operations write the same change data from a stream multiple times without a uniqueness check. This can occur if a stream and a destination table for the stream change data are stored in separate databases, and these databases are not replicated and failed over in the same group.

For example, suppose you regularly insert change data from stream s into table dt. (For this example, the source object for the stream does not matter.) Separate databases store the stream and destination table.

1. At timestamp t1, a row is inserted into the source table for stream s, creating a new table version. The stream stores the offset for this table version.

2. At timestamp t2, the secondary database that stores the stream is refreshed. Replicated stream s now stores the offset.

3. At timestamp t3, the change data for stream s is inserted into table dt.

4. At timestamp t4, the secondary database that stores stream s is failed over.

5. At timestamp t5, the change data for stream s is inserted again into table dt.

To avoid this situation, replicate and fail over together the databases that store streams and their destination tables.

Stream References in Task WHEN Clause¶

To avoid unexpected behavior when running replicated tasks that reference streams in the WHEN boolean_expr clause, we recommend that you either:

• Create the tasks and streams in the same database, or

• If streams are stored in a different database from the tasks that reference them, include both databases in the same failover group.

If a task references a stream in a separate database, and both databases are not included in the same failover group, then the database that contains the task could be failed over without the database that contains the stream. In this scenario, when the task is resumed in the failed over database, it records an error when it attempts to run and cannot find the referenced stream. This issue can be resolved by either failing over the database that contains the stream or recreating the database and stream in the same account as the failed over database that contains the task.

Stream Staleness¶

If a stream in the primary database has become stale, the replicated stream in a secondary database is also stale and cannot be queried or its change data consumed. To resolve this issue, recreate the stream in the primary database (using CREATE OR REPLACE STREAM). When the secondary database is refreshed, the replicated stream is readable again.

Note that the offset for a recreated stream is the current table version by default. You can recreate a stream that points to an earlier table version using Time Travel; however, the replicated stream would remain unreadable. For more information, see Stream Replication and Time Travel (in this topic).

Stream Replication and Time Travel¶

After a primary database is failed over, if a stream in the database uses Time Travel to read a table version for the source object from a point in time before the last refresh timestamp, the replicated stream cannot be queried or the change data consumed. Likewise, querying the change data for a source object from a point in time before the last refresh timestamp using the CHANGES clause for SELECT statements fails with an error.

This is because a refresh operation collapses the table history into a single table version. Iterative table versions created before the refresh operation timestamp are not preserved in the table history for the replicated source objects.

Consider the following example:

1. Table t1 is created in the primary database with change tracking enabled (table version tv0). Subsequent DML transactions create table versions tv1 and tv2.

2. A secondary database that contains table t1 is refreshed. The table version for this replicated table is tv2; however, the table history is not replicated.

3. A stream is created in the primary database with its offset set to table version tv1 using Time Travel.

4. The secondary database is failed over, becoming the primary database.

5. Querying stream s1 returns an error, because table version tv1 is not in the table history.

Note that when a subsequent DML transaction on table t1 iterates the table version to tv3, the offset for stream s1 is advanced. The stream is readable again.

Avoiding Data Loss¶

Data loss can occur when the most recent refresh operation for a secondary database is not completed prior to the failover operation. We recommend refreshing your secondary databases frequently to minimize the risk.

Replication Scenarios¶

The following table describes different task scenarios and specifies whether the tasks are replicated or not. Except where noted, the scenarios pertain to both standalone tasks and tasks in a DAG:

Resumed or Suspended State of Replicated Tasks¶

If all of the following conditions are met, a task is replicated to a secondary database in a resumed state:

• A standalone or root task is in a resumed state in the primary database when the replication or refresh operation begins until the operation is completed. If a task is in a resumed state during only part of this period, it might still be replicated in a resumed state.

  A child task is in a resumed state in the latest version of the task.

• The parent database was replicated to the target account along with role objects in the same, or different, replication or failover group.

  After the roles and database are replicated, you must refresh the objects in the target account by executing either ALTER REPLICATION GROUP … REFRESH or ALTER FAILOVER GROUP … REFRESH, respectively. If you refresh the database by executing ALTER DATABASE … REFRESH, the state of the tasks in the database is changed to suspended.

  A replication or refresh operation includes the privilege grants for a task that were current when the latest table version was committed. For more information, see Replicated Tasks and Privilege Grants (in this topic).

If these conditions are not met, the task is replicated to a secondary database in a suspended state.

Replicated Tasks and Privilege Grants¶

If the parent database is replicated to a target account along with role objects in the same, or different, replication or failover group, the privileges granted on the tasks in the database are replicated as well.

The following logic determines which task privileges are replicated in a replication or refresh operation:

• If the current task owner (i.e. the role that has the OWNERSHIP privilege on a task) is the same role as when the task was resumed last, then all current grants on the task are replicated to the secondary database.

• If the current task owner is not the same role as when the task was resumed last, then only the OWNERSHIP privilege granted to the owner role in the task version is replicated to the secondary database.

• If the current task owner role is not available (e.g. a child task is dropped but a new version of the task DAG is not committed yet), then only the OWNERSHIP privilege granted to the owner role in the task version is replicated to the secondary database.

Task Runs After a Failover¶

After a secondary failover group is promoted to serve as the primary group, any resumed tasks in databases within the failover group are scheduled gradually. The amount of time required to restore normal scheduling of all resumed standalone tasks and DAGs depends on the number of resumed tasks in a database.