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.

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 Replication

This section provides a high-level overview of the objects and settings available for replication.

Replicated Database Objects

When a primary database is replicated, a snapshot of its database objects and data is transferred to the secondary database. However, some database objects are not replicated. The following table indicates which database objects are replicated to a secondary database.

For specific usage information about these objects, see Database Replication Considerations.

Object

Type or Feature

Replicated

Notes

Tables

Permanent tables

Transient tables

Temporary tables

Automatic Clustering of clustered tables

External tables

Creating or refreshing a secondary database is blocked if an external table exists in the primary database. . Planned for a future version of database replication.

Table constraints

Except if a foreign key in the database references a primary/unique key in another database. .

Sequences

Views

Views

If a view references any object in another database (e.g. table columns, other views, UDFs, or stages), . both databases must be replicated.

Materialized views

File formats

Stages

Stages

Planned for a future version of database replication.

Temporary stages

Pipes

Planned for a future version of database replication.

Stored procedures

Streams

Planned for a future version of database replication.

Tasks

Planned for a future version of database replication.

User-defined functions (UDF)

Masking policies

The replication operation is blocked if either of the following use cases is true: . The primary database is in an Enterprise (or higher) account and contains a masking policy but one or more of the accounts approved for replication are on lower editions. . A masking policy contained in the primary database is applied to columns in a table or view in another database, or vice-versa.

Other Objects in an Account

Currently, replication is supported for databases only. Other types of objects in an account cannot be replicated. This list includes:

  • Users

  • Roles

  • Warehouses

  • Resource monitors

  • Shares

Access Control

Privileges granted on database objects are not replicated to a secondary database.

Parameters

Account parameters are not replicated.

Object parameters are not replicated, with the following exception:

Parameter

Objects

DATA_RETENTION_TIME_IN_DAYS

schema, table

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.

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