Replication considerations¶

Replication group and failover group constraints¶

The following sections explain the constraints around adding account objects, databases, and shares to replication and failover groups.

Replication privileges¶

This section describes the replication privileges that are available to be granted to roles to specify the operations users can perform on replication and failover group objects in the system. For the syntax of the GRANT command, see GRANT <privileges>.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Replication and references across replication groups¶

Objects in a replication (or failover) group that have dangling references (i.e. references to objects in another replication or failover group) might successfully replicate to a target account in some circumstances. If the replication operation results in behavior in the target account consistent with behavior that can occur in the source account, replication succeeds.

For example, if a column in a table in failover group fg_a references a sequence in failover group fg_b, replication of both groups succeeds. If fg_a is replicated before fg_b, insert operations (after failover) on the table that references the sequence fails if fg_b was not replicated. This behavior can occur in a source account. If a sequence is dropped in a source account, insert operations on a table with a column referencing the dropped sequence fails.

When the dangling reference is a security policy that protects data, the replication (or failover) group with the security policy must be replicated before any replication group that contains objects that reference the policy is replicated.

For database objects, you can view object dependencies in the Account Usage OBJECT_DEPENDENCIES view.

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

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>']

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.

Replication and read-only secondary objects¶

All secondary objects in a target account, including secondary databases and shares, are read-only. Changes to replicated objects or object types cannot be made locally in a target account. For example, if the USERS object type is replicated from a source account to a target account, new users cannot be created or modified in the target account.

New, local databases and shares can be created and modified in a target account. If ROLES are also replicated to the target account, new roles cannot be created or modified in that target account. Therefore, privileges cannot be granted to (or revoked from) a role on a secondary object in the target account. However, privileges can be granted to (or revoked from) a secondary role on local objects (for example, databases, shares, or replication or failover groups) created in the target account.

Replication and objects in target accounts¶

If you created account objects, for example, users and roles, in your target account by any means other than via replication (for example, using scripts), these users and roles have no global identifier by default. When a target account is refreshed from the source account, the refresh operation drops any account objects of the types in the OBJECT_TYPES list in the target account that have no global identifier.

To avoid dropping these objects, see Apply global IDs to objects created by scripts in target accounts.

Replication and security policies¶

The database containing a security policy and the references (i.e. assignments) can be replicated using replication and failover groups. Security policies include:

• Authentication policies

• Masking policies

• Password policies

• Row access policies

• Session policies

• Tag-based masking policies

If you are using database replication, see Database replication and security objects.

Replication and secrets¶

You can only replicate the secret using a replication or failover group. Specify the database that contains the secret, the database that contains UDFs or procedures that reference the secret, and the integrations that reference the secret in a single replication or failover group.

If you have the database that contains the secret in one replication or failover group and the integration that references the secret in a different replication or failover group, then:

• If you replicate the integration first and then the secret, the operation is successful: all objects are replicated and there are no dangling references.

• If you replicate the secret before the integration and the secret does not already exist in the target account, a “placeholder secret” is added in the target account to prevent a dangling reference. Snowflake maps the placeholder secret to the integration.

  After you replicate the group that contains the integration, on the next refresh operation for the group that contains the secret, Snowflake updates the target account to replace the placeholder secret with the secret that is referenced in the integration.

• If you replicate the secret and do not replicate the integration from account1 to account2, the integration doesn’t work in the target account (account2) because there is no integration to use the secret. Additionally, if you failover and the target account is promoted to source account, the integration will not work.

  When you decide to failover to make account1 as the source account, the secret and integration references match and the placeholder secret is not used. This allows you to use the security integration and the secret that contains the credentials because the objects can reference each other.

Replication and cloning¶

Cloned objects are replicated physically rather than logically to secondary databases. That is, cloned tables in a standard database do not contribute to the overall data storage unless or until DML operations on the clone add to or modify existing data. However, when a cloned table is replicated to a secondary database, the physical data is also replicated, increasing the data storage usage for your account.

Replication and automatic clustering¶

In a primary database, Snowflake monitors clustered tables using Automatic Clustering and reclusters them as needed. As part of a refresh operation, clustered tables are replicated to a secondary database with the current sorting of the table micro-partitions. As such, reclustering is not performed again on the clustered tables in the secondary database, which would be redundant.

If a secondary database contains clustered tables and the database is promoted to become the primary database, Snowflake begins Automatic Clustering of the tables in this database while simultaneously suspending the monitoring of clustered tables in the previous primary database.

See Replication and Materialized Views (in this topic) for information about Automatic Clustering for materialized views.

Replication and large, high-churn tables¶

When one or more rows of a table are updated or deleted, all of the impacted micro-partitions that store this data in a primary database are re-created and must be synchronized to secondary databases. For large, high-churn dimension tables, the replication costs can be significant.

For large, high-churn dimension tables that incur significant replication costs, the following mitigations are available:

• Replicate any primary databases that store such tables at a lower frequency.

• Change your data model to reduce churn.

For more information, see Managing Costs for Large, High-Churn Tables.

Replication and Time Travel¶

Time Travel and Fail-safe data is maintained independently for a secondary database and is not replicated from a primary database. Querying tables and views in a secondary database using Time Travel can produce different results than when executing the same query in the primary database.

Historical Data:

Historical data available to query in a primary database using Time Travel is not replicated to secondary databases.

For example, suppose data is loaded continuously into a table every 10 minutes using Snowpipe, and a secondary database is refreshed every hour. The refresh operation only replicates the latest version of the table. While every hourly version of the table within the retention window is available for query using Time Travel, none of the iterative versions within each hour (the individual Snowpipe loads) are available.

Data Retention Period:

The data retention period for tables in a secondary database begins when the secondary database is refreshed with the DML operations (i.e. changing or deleting data) written to tables in the primary database.

Note

The data retention period parameter, DATA_RETENTION_TIME_IN_DAYS, is only replicated to database objects in the secondary database, not to the database itself. For more details about parameter replication, see Parameters.

Replication and materialized views¶

In a primary database, Snowflake performs automatic background maintenance of materialized views. When a base table changes, all materialized views defined on the table are updated by a background service that uses compute resources provided by Snowflake. In addition, if Automatic Clustering is enabled for a materialized view, then the view is monitored and reclustered as necessary in a primary database.

A refresh operation replicates the materialized view definitions to a secondary database; the materialized view data is not replicated. Automatic background maintenance of materialized views in a secondary database is enabled by default. If Automatic Clustering is enabled for a materialized view in a primary database, automatic monitoring and reclustering of the materialized view in the secondary database is also enabled.

Replication and dynamic tables¶

Dynamic table replication behaves differently depending on if the primary database that contains the dynamic table is replicated in a replication group or a failover group.

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

Replication and Snowpipe Streaming¶

A table populated by Snowpipe Streaming in a primary database is replicated to the secondary database in a target account.

In the primary database, tables are created and rows are inserted through channels. Offset tokens track the ingestion progress. A refresh operation replicates the table object, table data, and the channel offsets associated with the table from the primary database to the secondary database.

• Snowflake Streaming read-only operations to retrieve offsets are available in the source and target accounts:

  • The channel getLatestCommittedOffsetToken API

  • SHOW CHANNELS command

• Snowflake Streaming write operations are only available in the source account:

  • The client openChannel API

  • The channel insertRow API

  • The channel insertRows API

Replication and stages¶

The following constraints apply to stage objects:

• Snowflake currently supports stage replication as part of group-based replication (replication and failover groups). Stage replication is not supported for database replication.

• You can replicate an external stage. However, the files on an external stage are not replicated.

• You can replicate an internal stage. To replicate the files on an internal stage, you must enable a directory table on the stage. Snowflake replicates only the files that are mapped by the directory table.

• When you replicate an internal stage with a directory table, you cannot disable the directory table on the primary or secondary stage. The directory table contains critical information about replicated files and files loaded using a COPY statement.

• A refresh operation will fail if the directory table on an internal stage contains a file that is larger than 5GB. To work around this limitation, move any files larger than 5GB to a different stage.

  You cannot disable the directory table on a primary or secondary stage, or any stage that has previously been replicated. Follow these steps before you add the database that contains the stage to a replication or failover group.

  1. Disable the directory table on the primary stage.

  2. Move the files that are larger than 5GB to another stage that does not have a directory table enabled.

  3. After you move the files to another stage, re-enable the directory table on the primary stage.

• Files on user stages and table stages are not replicated.

• For named external stages that use a storage integration, you must configure the trust relationship for secondary storage integrations in your target accounts prior to failover. For more information, see Configure cloud storage access for secondary storage integrations.

• If you replicate an external stage with a directory table, and you have configured automated refresh for the source directory table, you must configure automated refresh for the secondary directory table before failover. For more information, see Configure automated refresh for directory tables on secondary stages.

• A copy command might take longer than expected if the directory table on a replicated stage is not consistent with the replicated files on the stage. To make a directory table consistent, refresh it with an ALTER STAGE … REFRESH statement. To check the consistency status of a directory table, use the SYSTEM$GET_DIRECTORY_TABLE_STATUS function.

Replication and pipes¶

The following constraints apply to pipe objects:

• Snowflake currently supports pipe replication as part of group-based replication (replication and failover groups). Pipe replication is not supported for database replication.

• Snowflake replicates the copy history of a pipe only when the pipe belongs to the same replication group as its target table.

• Replication of notification integrations is not supported.

• Snowflake only replicates load history after the latest table truncate.

• To receive notifications, you must configure a secondary auto-ingest pipe in a target account prior to failover. For more information, see Configure notifications for secondary auto-ingest pipes.

• Use the SYSTEM$PIPE_STATUS function to resolve any pipes not in their expected execution state after failover.

Replication of data metric functions (DMFs)¶

The following behaviors apply to DMF replication:

Event tables

The event table that stores the results of manually calling or scheduling a DMF to run is not replicated because the event table is local to your Snowflake account, and Snowflake does not support replicating event tables.

Replication groups

When you add the database(s) that contain your DMFs to a replication group, the following occurs in the target account:

• DMFs are replicated from the source account.

• Tables or views that the DMF definition specifies, such as with a foreign key reference are replicated from the source account, unless the table or view is associated with Cross-Cloud Auto-Fulfillment.

• Scheduled DMFs in the target account are suspended. The secondary DMFs resume their schedule when you promote the target account to source account and the secondary DMFs become primary DMFs.

Failover groups

When you replicate the database(s) that contain your DMFs using a failover group, the following occurs in the case of failover:

• Resumes the schedule of suspended DMFs when you promote the target account to source account.

• Suspends scheduled DMFs in the target account after you promote a different account to source account.

If you do not replicate the database that contains the DMF to a target account, the DMF associations to a table or view are dropped when the target account is promoted to source account because they are not available in the newly promoted source account.

Tip

Prior to failing over your account, check the DMF references by calling the DATA_METRIC_FUNCTION_REFERENCES Information Schema table function to determine the table objects that are associated with a DMF before the promotion and refresh operations.

Replication of stored procedures and user-defined functions (UDFs)¶

Stored procedures and UDFs are replicated from a primary database to secondary databases.

Replication and streams¶

This section describes recommended practices and potential areas of concern when replicating streams in Replicating databases across multiple accounts or Account Replication and Failover/Failback.

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.

Replication and tasks¶

This section describes task replication in Replicating databases across multiple accounts or Account Replication and Failover/Failback.

Replication and tags¶

Tags and their assignments can be replicated from a source account to a target account.

Tag assignments cannot be modified in the target account after the initial replication from the source account. For example, setting a tag on a secondary (i.e. replicated) database is not allowed. To modify tag assignments in the target account, modify them in the source account and replicate them to the target account.

To successfully replicate tags, ensure that the replication or failover group includes:

• The database containing the tags in the ALLOWED_DATABASES property.

• Other account-level objects that have a tag in the OBJECT_TYPES property (e.g. ROLES, WAREHOUSES).

  For more information, see CREATE REPLICATION GROUP and CREATE FAILOVER GROUP.

Historical usage data¶

Historical usage data for activity in a primary database is not replicated to secondary databases. Each account has its own query history, login history, etc.

Historical usage data includes the query data returned by the following Snowflake Information Schema table functions or Account Usage views:

• COPY_HISTORY

• LOGIN_HISTORY

• QUERY_HISTORY

• etc.