Disaster Recovery, Backup and Data Replication · Topic 1 of 9

Replication Basics - Primary, Secondary, Objects and Access Control

Manage data replication: primary and secondary databases, what database and account objects replicate, and the effect on roles and access control.

  • 5 min
In a nutshell
  • Primary is read write; secondary is read only and refreshed from the primary.
  • Database and share replication: all editions. Account objects and failover: Business Critical or higher.
  • Account object types go in OBJECT_TYPES of a replication or failover group.

What it is

Replication keeps a read only copy of Snowflake objects in another account. The copy can sit in another region or on another cloud, and it is refreshed from the source on demand or on a schedule.

The source object is the primary. The copy is the secondary. Everyone who writes uses the primary; the secondary serves reads and is the object you promote when the primary is lost.

Primary databaseaccount A, read writerefreshSecondary databaseaccount B, read only

An administrator needs to know when each is the right tool, what replicates, and what happens to access control.

How it works

Primary and secondary databases

PropertyPrimarySecondary
AccountSource accountTarget account, same organization
AccessRead and writeRead only
Source of changesApplications and usersRefresh from the primary
Replaced byPromotion of a secondary (failover)Refresh, or promotion

Typical use cases:

  • Business continuity and disaster recovery. Keep a copy in another region that can be promoted if the primary region is lost.
  • Read workloads close to users. Query the secondary in a nearby region or on another cloud.
  • Data sharing across regions and clouds. A share only works inside one region, so replicate the database to the consumer region and share from the secondary side.

Database replication works on one database at a time:

-- in the source account
ALTER DATABASE sales ENABLE REPLICATION TO ACCOUNTS myorg.acct_dr;

-- in the target account
CREATE DATABASE sales AS REPLICA OF myorg.acct_src.sales;
ALTER DATABASE sales REFRESH;

The first refresh copies everything. Later refreshes apply only the changes since the previous refresh. A secondary is read only: you cannot insert, update or create objects inside it.

Replicating database objects

A database refresh replicates the objects inside the database. These include schemas, permanent tables, views, sequences, file formats, user defined functions, stored procedures, streams, tasks, database roles, tags and policies, with the restrictions on later pages.

ReplicatedNot replicated or restricted
Permanent tables with their dataTemporary tables
Schemas, views, sequences, file formatsEvent tables
Stored procedures and user defined functionsDatabases created from a share (inbound shares)
Database roles and their grantsFiles on user stages and table stages
Streams, tasks, tags and policies, with conditionsObjects the group does not include

Plain database replication cannot carry every object. Stages, pipes and some security objects need a replication group or failover group, which the next pages cover.

Exam tip

When a question says "replicate only one database to another region", database replication is enough. When it says "also users, roles, warehouses or integrations", it needs a group.

Replicating account level objects

Account level objects are outside any database. They replicate only through a replication group or failover group, and they need Business Critical Edition or higher.

Account object typeExamples
ACCOUNT PARAMETERSAccount level parameter settings
INTEGRATIONSAPI, notification, security and storage integrations
NETWORK POLICIESAccount and user network policies
RESOURCE MONITORSCredit quotas
ROLESAccount roles and their grants
USERSUser definitions
WAREHOUSESWarehouses, which arrive suspended in the target
SHARESOutbound shares
DATABASESWhole databases in the group

You list the types in OBJECT_TYPES when you create the group. Types you leave out do not replicate.

CREATE REPLICATION GROUP rg_prod
  OBJECT_TYPES = ROLES, WAREHOUSES, DATABASES
  ALLOWED_DATABASES = prod
  ALLOWED_ACCOUNTS = myorg.acct_dr
  REPLICATION_SCHEDULE = '10 MINUTE';

Impact on access control

Replication touches who can do what in four ways.

  1. Secondary objects are read only. Replicated roles in the target cannot be modified or granted new privileges there, and users cannot change replicated objects. Change them on the source and refresh.
  2. Local roles can still be used. A role that exists only in the target can receive privileges on local target objects and can read the secondary data once granted.
  3. Replicated roles bring their grants. When ROLES is in the group, role definitions and the grants between them replicate, so the DR account keeps the same structure. With plain database replication, account roles are not replicated, so you grant access in the target by yourself.
  4. Create users and roles in the source. If USERS or ROLES is in the object types, replicated users and roles are read only in the target, and a refresh brings the target back to the source state. Users and roles are created in the source and replicated to each target.

Privileges for managing replication:

ActionWho or what privilege
Enable database replicationACCOUNTADMIN
CREATE REPLICATION GROUP, CREATE FAILOVER GROUPMust be granted by ACCOUNTADMIN
Refresh or manage a groupOWNERSHIP, REPLICATE, MODIFY or MONITOR on the group
Fail over a failover groupFAILOVER on the failover group
Watch out

Replicated roles and users are not editable in the target. If an admin "fixes" a grant in the DR account by hand, the next refresh can overwrite or remove it. Always change the source.

Source accountroles, users, grantsgroup refreshTarget accountsame objects, read onlypromoteRoles and grantsusable after failover

Common mistakes

Believing the secondary can accept writes. It is read only until promoted.

Expecting account roles to follow a plain database replica. Roles are an account object type that needs a group.

Expecting temporary tables or inbound shares to replicate. They do not.

Creating a user in the DR account and expecting it to be managed by replication. If USERS is replicated, create users in the source and refresh.

What to remember for the exam

  • Primary is read write; secondary is read only and refreshed from the primary.
  • Database and share replication: all editions. Account objects and failover: Business Critical or higher.
  • Account object types go in OBJECT_TYPES of a replication or failover group.
  • Roles and grants replicate only when ROLES is included; replicated roles cannot be changed in the target.
  • ACCOUNTADMIN enables database replication and grants the create group privileges.

Check yourself

  • A secondary database in another region must serve read queries. Can analysts write to it?

    No. A secondary is read only until it is promoted.

  • Which object types does a database refresh not carry?

    Temporary tables, event tables and databases created from shares, plus account level objects such as users and warehouses.

  • Why can a hand made grant in the DR account disappear?

    Replicated roles are managed from the source, and the next refresh restores the source state.

Topic 1 of 9 in Disaster Recovery, Backup and Data Replication

Test yourself on Disaster Recovery, Backup and Data Replication

The full Snowflake ADA-C02 question bank has 814 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.