Database Replication (Primary → Secondary)
Before Snowflake introduced Account Replication (which syncs users, roles, and compute), the standard method for disaster recovery or cross-region data sharing was Database Replication.
Even today, Database Replication is heavily used when you only need to copy data (not users/roles) to a secondary region, perhaps for localized analytics or to share a specific dataset with a partner company operating in a different cloud provider.
Topics Covered
- What is Database Replication?
- Enabling and Configuring Replication
- Refresh Mechanisms (Manual vs Scheduled)
- What Gets Replicated (and What Doesn't)
- Monitoring Replication History
What is Database Replication?
Database Replication securely copies the contents of a primary database to a secondary database in another Snowflake account.
- The target account can be in a different region (e.g., AWS Mumbai to AWS Singapore).
- The target account can be in a completely different cloud provider (e.g., AWS Mumbai to Azure Pune).
Crucial Rules:
- The Secondary Database is strictly Read-Only. You cannot
INSERT,UPDATE, orDELETEdata in it. - Only the Primary database can push updates to the Secondary database through a "Refresh" operation.
Hands-On: Configuring Database Replication
Let's assume our core operations database (BEEMS_CORE_DB) is located in AWS Mumbai. Our data science team in AWS Singapore needs read-only access to this data for machine learning.
1. Enable Replication on the Primary Database
Log into the AWS Mumbai account. First, the Organization Administrator must link the two accounts. Then, the Database Administrator enables replication for that specific database.
(To verify your role, run SELECT CURRENT_ROLE();)
-- Step 1: ORGADMIN links the accounts (Done once per org)
USE ROLE ORGADMIN;
SELECT SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER(
'orgname.aws_mumbai', 'ENABLE_ACCOUNT_DATABASE_REPLICATION', 'true'
);
-- Step 2: ACCOUNTADMIN enables the specific database to be replicated to Singapore
USE ROLE ACCOUNTADMIN;
ALTER DATABASE BEEMS_CORE_DB ENABLE REPLICATION TO ACCOUNTS orgname.aws_singapore;
2. Create the Secondary Replica
Now, log into the target account (AWS Singapore). We must create the database as a Replica.
-- Run this in the Singapore Account
USE ROLE ACCOUNTADMIN;
-- Create the database as a replica
CREATE DATABASE BEEMS_CORE_DB
AS REPLICA OF orgname.aws_mumbai.BEEMS_CORE_DB;
At this exact moment, the Singapore database is created, but it is empty. We must execute a refresh.
3. Refreshing the Data
Data does not sync automatically in real-time. You must explicitly trigger a refresh.
Manual Refresh:
-- Run this in the Singapore Account
ALTER DATABASE BEEMS_CORE_DB REFRESH;
Scheduled Refresh (Automated): Usually, you want this to happen automatically. We can assign a schedule using a Replication Group.
-- Back in the Mumbai Account
CREATE REPLICATION GROUP RG_CORE_DATA
OBJECT_TYPES = DATABASES
ALLOWED_DATABASES = BEEMS_CORE_DB
ALLOWED_ACCOUNTS = orgname.aws_singapore
REPLICATION_SCHEDULE = '120 MINUTE'; -- Sync every 2 hours
What Gets Replicated (And What Doesn't)
It is critical to understand what metadata travels with the tables.
Replicated:
- Tables (including data, clustering keys, and search optimization settings)
- Views and Materialized Views
- Sequences
- File Formats and Internal Stages
NOT Replicated:
- External Stages: Because the credentials (IAM roles/tokens) are specific to the primary cloud region, external stages pointing to AWS S3 Mumbai will not work seamlessly in Azure Pune without reconfiguration.
- Pipes (Snowpipe): Pipes are not replicated because the SQS queues attached to them are region-specific.
- Users and Roles: You must use Account Replication to sync these.
Monitoring Replication Lag
Replication consumes credits and time. If you have a 10 Terabyte database, the initial sync might take hours. Subsequent incremental syncs will be faster, but you must monitor them.
-- Query the replication history to monitor cost and duration
SELECT
DATABASE_NAME,
START_TIME,
END_TIME,
TOTAL_BYTES_COPIED / (1024*1024*1024) AS GB_COPIED,
CREDITS_USED
FROM SNOWFLAKE.ACCOUNT_USAGE.REPLICATION_USAGE_HISTORY
WHERE START_TIME >= DATEADD(day, -7, CURRENT_DATE())
ORDER BY START_TIME DESC;
Summary
- Database Replication creates a Read-Only clone of a database in another account/region.
- You must
ENABLE REPLICATIONon the Primary, and useAS REPLICA OFon the Secondary. - Data does not sync instantly; it requires a
REFRESH(manual or scheduled). - External Stages and Pipes are not replicated.