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
- 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_TYPESof 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.
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
| Property | Primary | Secondary |
|---|---|---|
| Account | Source account | Target account, same organization |
| Access | Read and write | Read only |
| Source of changes | Applications and users | Refresh from the primary |
| Replaced by | Promotion 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.
| Replicated | Not replicated or restricted |
|---|---|
| Permanent tables with their data | Temporary tables |
| Schemas, views, sequences, file formats | Event tables |
| Stored procedures and user defined functions | Databases created from a share (inbound shares) |
| Database roles and their grants | Files on user stages and table stages |
| Streams, tasks, tags and policies, with conditions | Objects 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.
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 type | Examples |
|---|---|
ACCOUNT PARAMETERS | Account level parameter settings |
INTEGRATIONS | API, notification, security and storage integrations |
NETWORK POLICIES | Account and user network policies |
RESOURCE MONITORS | Credit quotas |
ROLES | Account roles and their grants |
USERS | User definitions |
WAREHOUSES | Warehouses, which arrive suspended in the target |
SHARES | Outbound shares |
DATABASES | Whole 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.
- 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.
- 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.
- Replicated roles bring their grants. When
ROLESis 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. - Create users and roles in the source. If
USERSorROLESis 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:
| Action | Who or what privilege |
|---|---|
| Enable database replication | ACCOUNTADMIN |
CREATE REPLICATION GROUP, CREATE FAILOVER GROUP | Must be granted by ACCOUNTADMIN |
| Refresh or manage a group | OWNERSHIP, REPLICATE, MODIFY or MONITOR on the group |
| Fail over a failover group | FAILOVER on the failover group |
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.
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_TYPESof a replication or failover group. - Roles and grants replicate only when
ROLESis 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.