Introduction to Database Replication Across Multiple Accounts

This feature enables replicating databases between Snowflake accounts (within the same organization) and keeping the database objects and stored data synchronized. Database replication is supported across regions and across cloud platforms.

In this Topic:

What is a Primary Database?

Replication can be enabled for any existing permanent or transient database. Enabling replication designates the database as a primary database. Any number of databases in an account can be designated a primary database. Likewise, a primary database can be replicated to any number of accounts in your organization. This involves creating a secondary database as a replica of a specified primary database in each of the target accounts. These accounts are typically located in other regions, on the same or a different cloud platform, or they can be in the same region as the source account.

All DML/DDL operations are executed on the primary database. Each read-only, secondary database can be refreshed periodically with a snapshot of the primary database, replicating all data as well as DDL operations on database objects (i.e. schemas, tables, views, etc.).

Overview of Database Replication

For the full list of replicated database objects, see Replicated Database Objects.

Other Objects in an Account

Database replication is supported for databases only. Other types of objects in an account can be replicated with account replication. For the full list of supported objects for account replication, see Replicated Objects.

Access Control

Privileges granted on database objects are not replicated to a secondary database. This includes privilege grants on existing database objects as well as grants on future objects (i.e. future grants).

Privilege grants can be replicated with account replication.

Parameters

Account parameters are not replicated with database replication. Account parameters can be replicated with account replication.

Object parameters that are set at the schema or schema object level are replicated:

Parameter

Objects

DATA_RETENTION_TIME_IN_DAYS

schema, table

DEFAULT_DDL_COLLATION

schema, table

MAX_DATA_EXTENSION_TIME_IN_DAYS

schema, table

PIPE_EXECUTION_PAUSED [1]

schema, pipe

QUOTED_IDENTIFIERS_IGNORE_CASE

schema, table

Parameter replication is only applicable to objects in the database (schema, table) and only if the parameter is explicitly set using CREATE <object> <parameter> or ALTER <object> … SET <parameter>. Database level parameters are not replicated.

Parameters explicitly set on objects in the primary database overwrite parameters set on objects in the secondary database. For example, if the primary database has a schema s1 with DATA_RETENTION_TIME_IN_DAYS set to 10 and the secondary database has DATA_RETENTION_TIME_IN_DAYS set to 1 at the database level, DATA_RETENTION_TIME_IN_DAYS for schema s1 in the secondary database is set to 10 after replication.

Parameters explicitly set at the database level on secondary databases are not overwritten. For example, if the secondary database parameter DATA_RETENTION_TIME_IN_DAYS is explicitly set to 1 and the primary database parameter DATA_RETENTION_TIME_IN_DAYS is explicitly set to 10, DATA_RETENTION_TIME_IN_DAYS for the secondary database remains set to 1 after replication.

[1] Note that PIPE objects are not replicated. If the PIPE_EXECUTION_PAUSED parameter is set at the schema level in the primary database, it is replicated to the secondary database. When the secondary database is promoted to primary database in the case of a failover and a pipe is created, the parameter setting will take effect.

Database Replication and Encryption

When a database is replicated to another account (both during the initial replication and later, when a secondary database is refreshed), Snowflake encrypts the database files (i.e. the database object metadata and data sets) in-transit from the source account to the target account. Snowflake encrypts files for database replication operations using a random, unique key for each replication job.

In addition, if Tri-Secret Secure is enabled for the replication accounts (i.e. the source and target accounts), the files are encrypted using the public key for an encryption key pair that is protected by the account master key (AMK) for your target account. The extra level of security provided by Tri-Secret Secure protects the AMK, therefore this protection also applies to data files in transit.

Note that tri-secret guarantees are valid for data files in transit also. Revoking access to the customer-managed key (in the key management service for the cloud provider that hosts your Snowflake account) prevents Snowflake from decrypting any data files in transit.

When replicating between Tri-Secret Secure enabled accounts, the same key is not required for the source and target accounts.

For more information about data encryption in Snowflake, see Data Encryption.

Database Replication to Accounts on Lower Editions

If either of the following conditions is true, Snowflake displays an error message when a local database is promoted to serve as a primary database:

  • The primary database is in a Business Critical (or higher) account but one or more of the accounts approved for replication are on lower editions. Business Critical Edition is intended for Snowflake accounts with extremely sensitive data.

  • The primary database is in a Business Critical (or higher) account and a signed business associate agreement is in place to store PHI data in the account per HIPAA and HITRUST CSF regulations, but no such agreement is in place for one or more of the accounts approved for replication, regardless if they are Business Critical (or higher) accounts.

This behavior is implemented in an effort to help prevent account administrators for Business Critical (or higher) accounts from inadvertently replicating sensitive data to accounts on lower editions.

An account administrator can override this default behavior by including the IGNORE EDITION CHECK clause when executing the ALTER DATABASE … ENABLE REPLICATION TO ACCOUNTS statement. If IGNORE EDITION CHECK is set, the primary database can be replicated to the specified accounts on any Snowflake edition.

Current Limitations of Replication

  • Refreshing a secondary database is blocked if an external table exists in the primary database.

  • Databases created from shares cannot be replicated.

Back to top