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
In a nutshell
  • 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.

Source objectclustered table, MV, policyrefreshSecondaryread onlypromoteFeature runsclustering, MV maintenance

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.

RuleDetail
Same groupPut objects that reference each other in the same group
OrderA group that holds a policy must refresh before a group whose objects use it
Name based viewsRegular views replicate, but queries fail in the target if the referenced database is not replicated there
Id based referencesA 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.

Exam tip

"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.
AccountCost for materialized view
PrimaryMaintenance after base table changes
SecondaryBackground 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 ROLES in the object types, and mind that conditions such as CURRENT_ROLE() or mapping tables work only if the roles and tables exist there.
Policy typeReplication behavior
Masking and row accessReplicate with the database in a group
Authentication, password and session policiesNeed the policy database in ALLOWED_DATABASES and USERS in OBJECT_TYPES
Network policiesInclude 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 as ROLES and WAREHOUSES, in OBJECT_TYPES.
  • Tag based masking depends on the tag and the policy both replicating.
Tag databaseand policy databasein groupSame groupALLOWED_DATABASESrefresh policy group firstObjects using themtables, columns, roles

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.