Disaster Recovery, Backup and Data Replication · Topic 4 of 9
Replication Impact on Clustering, Materialized Views, External Tables and Policies
How account replication affects dependent objects: automatic clustering, materialized views, external tables, masking and row access policies, and tags.
- 5 min
- Clustered tables arrive sorted; automatic clustering starts after promotion.
- Materialized view definitions replicate; each secondary pays to maintain its own copy.
- External table files stay in external storage; the target needs its own access to them.
What it is
Replication moves more than tables. Objects that depend on other objects, such as clustered tables, materialized views, policies and tags, behave in specific ways in the target. The exam asks what is copied, what is rebuilt, who pays, and what breaks if a dependency is missing.
Most questions turn on one fact per feature. Learn that fact for each, then the general rule: keep dependent objects in the same group and refresh groups that hold policies first.
How it works
Dependent objects in general
A refresh can fail when an object refers to something that is not replicated. These are called dangling references. Typical sources are a view on a table in another database, a table using a sequence from another group, a stream on a source in another database, and a policy or tag in a database that is left out.
| Rule | Detail |
|---|---|
| Same group | Put objects that reference each other in the same group |
| Order | A group that holds a policy must refresh before a group whose objects use it |
| Name based views | Regular views replicate, but queries fail in the target if the referenced database is not replicated there |
| Id based references | A materialized view that references an object in another database makes replication fail |
For consistency across several databases, use a replication or failover group. Plain database replication gives no point in time consistency across databases.
Automatic clustering
- A clustered table replicates with its current micro partition sort order. The secondary does not recluster it again.
- Automatic Clustering starts on the database when it is promoted. Monitoring of clustering is suspended on the former primary.
- Clustering enabled on a materialized view in the primary is also enabled on its secondary.
So the target does not pay for reclustering while it is a read only secondary. The cost starts after promotion, in the new primary.
"Does the secondary pay for reclustering before failover?" No. The data arrives already sorted, and automatic clustering starts only after promotion.
Materialized views
- A refresh replicates the view definition, not the view data.
- Background maintenance on the secondary is on by default, and the secondary builds its own data.
- Each account that holds a secondary database is billed for the background synchronization of the materialized view.
- A materialized view that references an object in another database cannot be replicated with database replication.
| Account | Cost for materialized view |
|---|---|
| Primary | Maintenance after base table changes |
| Secondary | Background synchronization after refresh |
So a materialized view costs credits in both accounts.
External tables
An external table points at files in external cloud storage, and Snowflake does not copy those files. In replication:
- The files remain in your cloud storage and are not replicated by Snowflake.
- External tables are not supported as a source for streams in replicated databases.
- The target account needs access to the same external storage, through its own stage and storage integration, to read the files after failover.
Plan for the storage side: a trust relationship for the storage integration of the target account, so it can read the same files.
Masking and row access policies
- Masking, row access and tag based masking policies replicate with the database when it is in a replication or failover group.
- Policy references are preserved, so the target enforces the same rules after promotion.
- A group that holds a policy must be refreshed before any group whose objects use it. If it is not, references dangle and the refresh can fail.
- Policies that rely on roles need those roles in the target. Include
ROLESin the object types, and mind that conditions such asCURRENT_ROLE()or mapping tables work only if the roles and tables exist there.
| Policy type | Replication behavior |
|---|---|
| Masking and row access | Replicate with the database in a group |
| Authentication, password and session policies | Need the policy database in ALLOWED_DATABASES and USERS in OBJECT_TYPES |
| Network policies | Include the network rule database and NETWORK POLICIES in the group |
Tags
- Tags and their assignments replicate.
- Tag assignments cannot be changed on the target. Change them on the source and refresh.
- The group must include the tag database in
ALLOWED_DATABASES, and the account object types that carry tags, such asROLESandWAREHOUSES, inOBJECT_TYPES. - Tag based masking depends on the tag and the policy both replicating.
Common mistakes
Expecting automatic clustering to run in the secondary. It starts after promotion.
Expecting materialized view data to replicate. Only the definition does; the target builds its own data and bills for it.
Replicating a policy database in a later group than the tables that use it. Refresh fails on dangling references.
Editing a tag on the target. Assignments are read only there.
Forgetting external storage access in the target account. The files are not copied.
What to remember for the exam
- Clustered tables arrive sorted; automatic clustering starts after promotion.
- Materialized view definitions replicate; each secondary pays to maintain its own copy.
- External table files stay in external storage; the target needs its own access to them.
- Masking and row access policies replicate with the database; refresh the policy group first.
- Tags replicate but can be changed only on the source; tag database and account object types must be in the group.
Check yourself
Does a secondary database recluster clustered tables while it is a replica?
No. Tables arrive with the same sort order, and Automatic Clustering starts after promotion.
What replicates for a materialized view?
The definition; the secondary maintains its own data and is billed for it.
Why must the policy group refresh before the group that uses the policy?
Otherwise the objects reference a policy that is not there yet, and the refresh can fail on dangling references.
Topic 4 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.