Data Collaboration · Topic 2 of 5

Zero-Copy Cloning, Replication and Failover

Zero-copy cloning in Snowflake, what a clone inherits, plus database replication, replication and failover groups, editions and how failover works.

  • 5 min
In a nutshell
  • Cloning is zero-copy: metadata only, new storage only for changes after the clone.
  • Combine CLONE with AT or BEFORE to copy an object as it was in the past.
  • Child objects in a cloned database or schema keep their grants; the cloned container and single cloned tables do not, unless COPY GRANTS is used on a table.

What it is

Cloning creates a copy of a database, schema or table in the same account almost instantly, without copying data. Replication copies databases and other objects to another account in your organization, usually in a different region or cloud. Failover promotes that copy to primary when the original region has an outage.

Both answer "I need another copy of this data", but at different scopes. Cloning works inside one account. Replication works across accounts and regions.

Source tableaccount ACLONEClonesame account, shared micro partitionsPrimary databaseaccount A, region 1replicateSecondary databaseaccount B, region 2, read only

How it works

Zero-copy cloning

CREATE <object> <name> CLONE <source> copies only metadata. The clone points to the same micro-partitions as the source, so it takes no extra storage at first. When either side changes data, new micro-partitions are written for that side only, and only those changes add storage.

CREATE DATABASE dev_db CLONE prod_db;
CREATE TABLE orders_backup CLONE orders AT(OFFSET => -3600);

Cloning works with Time Travel: add AT or BEFORE to clone an object as it was in the past. The source must have existed at that time and still be within retention.

CREATE TABLECLONEcopies metadataCloneno new storageafter updatesChanged micro partitionsbilled separately

What a clone inherits

ItemBehavior
Data, structure, comments, clustering keysInherited from the source at clone time
Child objects in a cloned database or schemaCloned, and they keep the grants that were on the source child objects
The cloned database or schema itselfDoes not inherit grants on the source container
A single cloned tableNo grants copied, unless you add COPY GRANTS (all privileges except OWNERSHIP)
Tasks and alerts in a cloned database or schemaSuspended; resume them with ALTER TASK ... RESUME or ALTER ALERT ... RESUME
PipesOnly pipes that reference an external stage are cloned
Named internal stagesNot cloned with a database or schema unless you use INCLUDE INTERNAL STAGES; external stages can be cloned
Automatic ClusteringSuspended on the cloned table until resumed

The clone is an independent object. Changes to the clone never affect the source, and the reverse is also true.

Exam tip

Cloning is the standard answer for "create a full dev or test copy of production quickly with no extra storage up front". It is also a fast pre-change safety copy before a risky update.

Replication

Snowflake replicates objects between accounts in the same organization, across regions and across cloud platforms. The primary object is read-write, and the secondary copy is read-only until it is promoted.

  • Database and share replication is available in all editions.
  • Replication groups bundle databases and shares and replicate them together as one point-in-time consistent unit.
  • Replication of account objects such as users, roles and warehouses, and failover, require Business Critical Edition or higher.
  • A database created from a share (an imported database) cannot be replicated.

Refreshes copy only changes since the last refresh. Snowflake recommends scheduling them with the REPLICATION_SCHEDULE parameter on the group, as an interval or a cron expression. Each refresh uses compute and moves data between regions, so it adds cost.

Failover groups and failover

A failover group is a replication group that can also be promoted. In an outage, an administrator in the secondary account promotes the secondary failover group to primary, and it becomes read-write. When the original region recovers, you can fail back the same way.

Primary failover groupregion 1scheduled refreshSecondary failover groupregion 2, read onlyoutage, promoteNew primaryregion 2, read write

Client Redirect (also Business Critical or higher) uses a connection object with a connection URL. After failover, you point the connection to the new primary account, and clients using that URL reconnect without changing their configuration.

NeedFeatureMinimum edition
Copy a database to another region for reading or sharingDatabase replication or replication groupStandard
Copy users, roles, warehouses and other account objectsReplication or failover group with account objectsBusiness Critical
Promote the copy during a regional outageFailover groupBusiness Critical
Keep one connection URL for clients across failoverClient RedirectBusiness Critical

Replication and sharing across regions

A direct share only reaches accounts in the same region. To share with a consumer in another region, the provider either replicates the database to an account in that region and shares from there, or uses a listing with Cross-Cloud Auto-Fulfillment, which handles the replication automatically.

Common mistakes

Thinking a clone doubles storage. A new clone costs no storage until data changes. Only the changed micro-partitions are billed.

Expecting grants on the cloned container. A cloned database keeps grants on its child objects but not grants on the database itself. A cloned table gets grants only with COPY GRANTS.

Forgetting that tasks start suspended. After cloning a schema with tasks, they do not run until resumed.

Picking Standard Edition for failover. Database replication works on Standard, but failover, account object replication and Client Redirect need Business Critical or higher.

Writing to a secondary database. Secondary databases are read-only until promoted.

What to remember for the exam

  • Cloning is zero-copy: metadata only, new storage only for changes after the clone.
  • Combine CLONE with AT or BEFORE to copy an object as it was in the past.
  • Child objects in a cloned database or schema keep their grants; the cloned container and single cloned tables do not, unless COPY GRANTS is used on a table.
  • Replication works between accounts in the same organization, across regions and clouds.
  • Database and share replication: all editions. Account objects, failover groups and Client Redirect: Business Critical or higher.
  • Secondary objects are read-only; failover promotes them to read-write.
  • Imported (shared) databases cannot be replicated.
Watch out

Cloning and replication are different scopes. "Copy to another region" is never a clone; "instant copy in the same account for testing" is never replication.

Check yourself

  • How much storage does a 2 TB table clone use right after it is created?

    Almost none. It shares the source micro-partitions and only adds storage as data in either table changes.

  • Which edition is needed to fail over a database to another region?

    Business Critical Edition or higher, using a failover group.

  • After cloning a schema, why are its scheduled jobs not running?

    Tasks in a cloned database or schema are suspended by default and must be resumed with ALTER TASK ... RESUME.

Topic 2 of 5 in Data Collaboration

Test yourself on Data Collaboration

The full Snowflake SnowPro Core COF-C03 question bank has 845 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.