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.

You can only replicate outbound shares. Replication of inbound shares (shares from providers) is not supported.

Account objects¶

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

Session policies and secondary roles¶

For details, see Session policies with secondary roles

Dangling references and 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 uses a network rule, include the database that contains the schema where the network rule was created.

• 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 NETWORK POLICIES and USERS in the OBJECT_TYPES list.

For more details, see Replicating network policies.

Dangling references and packages policies¶

If there is a packages policy set on the account, the following dangling references error occurs during the refresh operation for a replication or failover group that contains account objects:

003131 (55000): Dangling references in the snapshot. Correct the errors before refreshing again.
The following references are missing (referred entity <- [referring entities]):
POLICY '<policy_db>.<policy_schema>.<packages_policy_name>' <- [ACCOUNT '<account_locator>']

To avoid dangling references, replicate the database that contains the packages policy to the target account. The database containing the policy can be in the same or different replication or failover group.

Dangling references and secrets¶

For details, see Replication and secrets.

Dangling references and 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.

Objects recreated in target accounts¶

If an existing object in the source account is replaced using a CREATE OR REPLACE statement, the existing object is dropped, and then a new object with the same name is created in a single transaction. For example, if you execute a CREATE OR REPLACE statement for an existing table t1, table t1 is dropped, and then a new table t1 is created. For more information, see the usage notes for CREATE TABLE.

When objects are replaced on the target account, the DROP and CREATE statements do not execute atomically during a refresh operation. This means the object might disappear briefly from the target account while it is being recreated as a new object.

Authentication, password, & session policies¶

Authentication, 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.

Privacy policies¶

Consider the following when replicating privacy policies and privacy-protected tables and views associated with differential privacy:

• If a privacy policy is assigned to a table or view in the source account, the policy needs to be replicated in the target account.

• Cumulative privacy loss for a privacy budget is not replicated.

• Cumulative privacy loss in the target and source accounts are tracked separately.

• Administrators in the target account cannot adjust the replicated privacy budget. The privacy budget is synced with the one in the source account.

• If an analyst has access to the privacy-protected table or view in both the source account and the target account, they can incur twice the amount of privacy loss before reaching the privacy budget’s limit.

• Privacy domains set on the columns are also replicated.

Session policies with secondary roles¶

If you are using session policies with secondary roles, you must specify the policy database in the same replication group that contains the roles. For example:

CREATE REPLICATION GROUP myrg
  OBJECT_TYPES = DATABASES, ROLES, USERS
  ALLOWED_DATABASES = session_policy_db
  ALLOWED_ACCOUNTS = myorg.myaccount
  REPLICATION_SCHEDULE = '10 MINUTE';

If you specify the session policy database that references secondary roles in a different replication or failover group (rg2) than the replication or failover group that contains account-level objects (myrg) and you replicate or fail over rg2 first, a dangling reference occurs. An error message tells you to place the session policy database in the replication or failover group that contains the roles. This behavior occurs when the session policy is set on the account or users.

If the session policy and account level objects are in different replication groups, and the session policy is not set on the account or users, you can replicate and refresh the target account. Be sure to refresh for the replication group that contains the account level objects first.

If you refresh the target account after replicating or failing over the session policy with secondary roles and role objects, the target account reflects the session policy and secondary roles behavior in the source account.

Additionally, when you refresh the database in the target account and the database contains a session policy that references secondary roles, ALLOWED_SECONDARY_ROLES always evaluates to [ALL].

Logical replication of clones¶

If the original and cloned table are included in the same replication or failover group, the cloned table can be replicated logically to the target account.

For example, if table t2 in database db2 is a clone of table t1 in database db1, and both databases are included in replication group rg1, then table t2 is created as a logical clone in the target account.

A cloned object can be cloned to create additional clones of the original object. The original object and the cloned objects are part of the same clone group. For example, if table t3 in database db3 is created as a clone of t2, it is in the same clone group as the original table t1 and the cloned table t2.

If database db3 is later added to the replication group rg1, table t3 is created in the target account as a logical clone of table t1.

CREATE REPLICATION GROUP myrg
    OBJECT_TYPES = DATABASES
    ALLOWED_DATABASES = db1, db2
    ALLOWED_ACCOUNTS = myorg.myaccount2
    REPLICATION_SCHEDULE = '10 MINUTE';

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.

Secondary dynamic tables are read-only and do not get refreshed. After a failover occurs and a secondary dynamic table is promoted to primary dynamic table, the first refresh is a reinitialization followed by incremental refreshes if the dynamic table is configured for incremental refresh of data.

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 configured replication schedule. 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, see 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

The data retention time for successfully inserted rows in your upstream data source must be greater than the configured replication schedule. If you are using the Kafka connector, ensure your log.retention configuration is set with a sufficient buffer.

Stored Procedures and UDFs and Stages¶

If a stored procedure or UDF depends on files in a stage (for example, if the stored procedure is defined in Python code that is uploaded from a stage), you must replicate the stage and its files to the secondary database. For more information about replicating stages, see Stage, pipe, and load history replication.

For example, if a primary database has an in-line Python UDF that imports any code that is stored on a stage, the UDF does not work unless the stage and its imported code are replicated in the secondary database.

Stored Procedures and UDFs and External Network Access¶

If a stored procedure or UDF depends on access to an external network location, you must replicate the following objects:

• EXTERNAL ACCESS INTEGRATIONS must be included in the allowed_integration_types list for the replication or failover group.

• The database that contains the network rule.

• The database that contains the secret that stores the credentials to authenticate with the external network location.

• If the secret object references a security integration, you must include SECURITY INTEGRATIONS in the allowed_integration_types list for the replication or failover group.

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, unless 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)

Replicating streams on directory tables is supported when you enable Stage, pipe, and load history replication.

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.

Append-only streams are not supported on replicated source objects.

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 task graph:

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 (for example, a child task is dropped but a new version of the task graph 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 task graphs depends on the number of resumed tasks in a database.