6.1 Database & Account Replication Mechanics

6.1 Database & Account Replication Mechanics

Enterprise data platforms cannot tolerate single-cloud or single-region vulnerabilities. Natural disasters, regional cloud networking partitions, regulatory shifts, and cloud service provider outages demand robust multi-cloud and multi-region business continuity strategies. Snowflake provides native, cross-cloud account and database replication capabilities across Amazon Web Services (AWS), Microsoft Azure, and Google Cloud Platform (GCP) without requiring third-party extraction tools, external message buses, or bespoke ETL pipelines.

For the SnowPro Advanced: Architect exam, you must master the mechanics of Snowflake's replication engine, understand the architectural distinction between Replication Groups and Failover Groups, design synchronization schedules, manage dependent security policies, and evaluate the financial cost model of cross-cloud data movement.

Replication Architecture & Multi-Cloud Topology

Snowflake replication operates by creating point-in-time metadata snapshots of primary objects and transferring delta micro-partitions asynchronously to one or more target secondary accounts across cloud providers and geographical regions.

┌─────────────────────────────────────────────────────────────────┐
│                     Snowflake Organization                      │
│                                                                 │
│  ┌───────────────────────────┐     Replication Delta Transport  │
│  │ Primary Account (AWS)     │ ──────────────────────────────┐  │
│  │ • Primary Database (R/W)  │                               │  │
│  │ • Users, Roles, Warehouses│                               ▼  │
│  │ • Resource Monitors       │                  ┌───────────────────────────┐
│  └───────────────────────────┘                  │ Secondary Account (Azure) │
│                │                                │ • Secondary Database (RO) │
│                │ Replication Delta Transport    │ • Synced RBAC & Monitors  │
│                ▼                                └───────────────────────────┘
│  ┌───────────────────────────┐                                  │
│  │ Secondary Account (GCP)   │                                  │
│  │ • Secondary Database (RO) │                                  │
│  │ • Synced RBAC & Monitors  │                                  │
│  └───────────────────────────┘                                  │
└─────────────────────────────────────────────────────────────────┘

Primary vs. Secondary Objects

• Primary Database / Object: Resides in the authoritative source account. The primary database is read-write; all ingestion pipelines, transformations, ETL/ELT tasks, and analytical writes execute against the primary.
• Secondary Database / Object: Resides in a replica target account. Secondary databases are strictly read-only. Any attempt to execute write DML (INSERT, UPDATE, DELETE, MERGE), truncate tables, or execute DDL creating new schemas or tables inside a secondary database returns an error:

  -- Executed in secondary account
  INSERT INTO target_db.public.orders VALUES (101, 'ACTIVE', CURRENT_TIMESTAMP());
  -- Result: SQL compilation error: Operation not allowed on secondary database 'TARGET_DB'.
  

Database Replication vs. Account Replication

Snowflake replication has evolved across two major architectural generations:

1. Legacy Database Replication (ALTER DATABASE ... ENABLE REPLICATION):
   • Operates strictly at the individual database boundary.
   • Replicates only the database schema and table micro-partitions.
   • Critical Architectural Flaw: Fails to replicate account-level RBAC roles, users, warehouse definitions, network policies, or resource monitors. If a replicated table has a row access policy or masking policy referencing an account role that does not exist in the target account, queries fail or policy evaluation breaks upon secondary access.
2. Account Replication (Replication & Failover Groups):
   • Modern enterprise standard introduced to synchronize entire operational environments.
   • Groups multiple databases and global account objects into a single, transactionally consistent replication unit.
   • Replicates databases and shares alongside account objects such as USERS, ROLES, WAREHOUSES, RESOURCE MONITORS, NETWORK POLICIES, INTEGRATIONS (security, API, storage, external access, and some notification integrations), and ACCOUNT PARAMETERS. Policies such as masking, row access, session, and password policies are schema-level objects, so they replicate as part of the database that contains them.
   • Edition rule: database and share replication are available to all editions; replicating other account objects and failover/failback require Business Critical Edition or higher.
   • Prerequisite: an organization administrator must enable replication for the source and target accounts before groups can be created.

Supported Replicated Object Types

When defining modern replication groups, architects specify the exact set of object types synchronized across accounts:

Exam Trap: There is no POLICIES object type. Governance policies travel inside DATABASES.

Replication Groups vs. Failover Groups

A critical distinction on the SnowPro Advanced: Architect exam is the functional and operational boundary between a Replication Group and a Failover Group.

Architectural Comparison

Defining a Failover Group on the Primary Account

To establish a Failover Group, an administrator holding the ACCOUNTADMIN role executes CREATE FAILOVER GROUP on the primary source account. The group specifies which object types to replicate, which specific databases to include, which target accounts in the organization are authorized replicas, and the automated refresh schedule.

-- Step 1: Switch to ACCOUNTADMIN on Primary Account (e.g., prod_aws_useast1)
USE ROLE ACCOUNTADMIN;

-- Step 2: Create the primary failover group
CREATE FAILOVER GROUP fg_enterprise_dr
  OBJECT_TYPES = USERS, ROLES, WAREHOUSES, RESOURCE MONITORS, DATABASES, NETWORK POLICIES
  ALLOWED_DATABASES = sales_dw, finance_dw, crm_dw, governance_db  -- include the database holding shared policies
  ALLOWED_ACCOUNTS = acme_org.prod_azure_westeurope, acme_org.prod_gcp_uscentral1
  REPLICATION_SCHEDULE = '15 MINUTE';

Creating the Secondary Failover Group on the Target Account

Once the primary failover group is defined and permissions are established within the Snowflake Organization, the target account administrator creates the secondary replica referencing the organization account identifier of the source:

-- Step 3: Switch to ACCOUNTADMIN on Secondary Account (e.g., prod_azure_westeurope)
USE ROLE ACCOUNTADMIN;

-- Step 4: Create secondary replica of the failover group
CREATE FAILOVER GROUP fg_enterprise_dr
  AS REPLICA OF acme_org.prod_aws_useast1.fg_enterprise_dr;

Note: The secondary group's name can be — but is not required to be — the same as the primary's; keeping them identical makes runbooks clearer. AS REPLICA OF must reference the primary group as <org_name>.<source_account_name>.<group_name>.

Synchronization Mechanics & Scheduling

Replication in Snowflake is driven by a point-in-time metadata snapshot and differential micro-partition extraction. The synchronization process can be scheduled automatically or triggered ad-hoc via DDL.

Synchronization Workflow

1. Scheduled / Manual Trigger
   │
   ▼
2. Snapshot Generation ──► Primary account freezes point-in-time metadata snapshot
   │
   ▼
3. Delta Calculation ──► Identifies newly committed micro-partitions & metadata modifications
   │
   ▼
4. Cloud Egress & Transport ──► Micro-partitions encrypted & streamed across cloud networks
   │
   ▼
5. Target Ingestion ──► Secondary account commits micro-partitions to local cloud object storage
   │
   ▼
6. Metadata Pointer Update ──► Secondary catalog atomically updates metadata pointers

Scheduling Synchronization

Automated replication schedules are defined using the REPLICATION_SCHEDULE parameter on the primary failover group:

-- Interval-based schedule (every 10 minutes)
ALTER FAILOVER GROUP fg_enterprise_dr
  SET REPLICATION_SCHEDULE = '10 MINUTE';

-- Cron-based schedule (every weekday at 02:00 UTC)
ALTER FAILOVER GROUP fg_enterprise_dr
  SET REPLICATION_SCHEDULE = 'USING CRON 0 2 * * MON-FRI UTC';

Manual Ad-Hoc Refresh

Administrators can trigger an immediate manual synchronization directly on the secondary account. This is a common architectural pattern prior to conducting planned maintenance or disaster recovery drills:

-- Executed on Secondary Account to pull latest delta changes immediately
USE ROLE ACCOUNTADMIN;
ALTER FAILOVER GROUP fg_enterprise_dr REFRESH;

Serverless Compute Execution Engine

A pivotal architectural principle tested on the ARA-C01 exam is that Snowflake replication does not require an active virtual warehouse. The replication engine runs entirely on Snowflake-managed serverless compute infrastructure:

• Virtual warehouses are not spun up or billed for replication tasks.
• Compute consumption is measured in serverless compute credits, appearing under the REPLICATION service type in credit billing views.
• Snowflake automatically scales the underlying serverless compute resources to handle large initial seed loads versus small incremental micro-partition deltas.

Monitoring Replication Telemetry

Architects monitor synchronization performance, transferred byte volumes, and operational status using dedicated ACCOUNT_USAGE views:

-- Inspect replication refresh history and duration across groups
SELECT 
    replication_group_name,
    phase_name,
    start_time,
    end_time,
    total_bytes,
    status,
    error_code,
    error_message
FROM snowflake.account_usage.replication_group_refresh_history
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
ORDER BY start_time DESC;

-- Monitor serverless credit consumption for replication
SELECT 
    start_time,
    end_time,
    replication_group_name,
    credits_used
FROM snowflake.account_usage.replication_group_usage_history
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
ORDER BY start_time DESC;

Financial Economics & Cost Model

Cross-cloud and cross-region replication introduces three distinct cost components that data architects must budget and optimize:

1. Cloud Provider Data Egress Charges

• When data travels across regions or across cloud providers (e.g., from AWS us-east-1 to Azure westeurope), the source cloud provider levies data transfer fees for outbound network egress.
• Snowflake passes these cloud egress charges directly through to the customer contract at standard cloud provider rates per gigabyte.
• Data transferred within the same cloud provider and same region incurs zero data egress fees.

2. Serverless Replication Compute Credits

• The Snowflake serverless replication service consumes Snowflake credits to calculate micro-partition differences, generate snapshots, and write remote catalog metadata.
• Billed per-second based on serverless compute time utilized during each refresh cycle.

3. Remote Cloud Storage Charges

• Micro-partitions replicated to the target account reside physically in the target cloud provider's object storage (e.g., Azure Blob Storage or Google Cloud Storage).
• Storage in the secondary account is billed at the target region's standard Snowflake storage rate per terabyte per month.
• Replicated databases consume storage for active data micro-partitions and Time Travel history according to the target account's retention settings.

Replication Cost Summary Matrix

Architectural Scenarios & Exam Traps

Exam Trap 1: Dangling References to Policies and Other Objects

Policies are schema objects. If tables in sales_dw are protected by masking policies stored in governance_db, and governance_db is not in the same group, the table has a dangling reference. Snowflake requires that the group containing a data-protecting security policy be replicated before any group whose objects reference it; the simplest design keeps the policy database in the same group. Network policies similarly need the database that holds their network rules, and ROLES should be replicated so ownership and grants exist in the target. ACCOUNT_USAGE.OBJECT_DEPENDENCIES helps find these references before you design groups.

Exam Trap 2: Replicating Internal vs. External Stages

• External Stages: Replicate metadata only (URL, storage integration name, encryption specs). Cloud object storage files referenced by external stages are not moved.
• Internal Stages: Named internal stages can be replicated if explicitly included and enabled in replication configurations, but staging large uncompressed files directly inflates data transfer egress costs.

Exam Trap 3: Dropping Objects in Primary vs. Secondary

• Dropping a database in the primary account propagates during the next refresh, dropping the secondary replica.
• Dropping a secondary database directly on the target account is prohibited while it is linked to an active replication or failover group; the secondary failover group must be dropped first.