skip to content

How does Snowflake's architecture make CREATE TABLE ... CLONE of a 10 TB table instant?

level: seniorimportance: should knowfreq 55%

answer

  1. the size of the table is irrelevant to the timing
  2. nothing is read, so nothing needs compute
  3. files that never change can be shared safely
  4. a table is really a list of files
  5. cost appears only when the two sides diverge

basics

~20 s

Cloning copies metadata, not data. Snowflake's stored files are immutable, so the clone is a new catalog object pointing at the same files as the source. No bytes move, no compute runs, and extra storage is billed only as one side writes new files and diverges.

solid answer

~50 s

Snowflake stores table data as **immutable** compressed columnar files, and the cloud services layer keeps, per table version, the list of files that make up the table. `CREATE TABLE t_clone CLONE t` creates a new catalog entry whose file list is the source's — a pure metadata write. Nothing is read, nothing is copied, and no virtual warehouse is required, so the elapsed time is roughly independent of table size. From that moment the two tables are fully independent logically: a write to either produces **new** files that only that table references, and only those new files add storage cost. The same mechanism powers Time Travel, since a past table version is just an older file list, and you can combine them with `CREATE TABLE t_clone CLONE t AT (OFFSET => -3600)`. The usual production uses are instant dev/test environments and a pre-migration safety copy.

code

sql · 9 lines
sql
-- Instant regardless of size: metadata only, no warehouse needed
CREATE TABLE analytics.dev.sales_dev CLONE analytics.prod.sales;

-- Clone as of a point in the past (Time Travel + clone)
CREATE TABLE analytics.dev.sales_pre_incident
  CLONE analytics.prod.sales AT (OFFSET => -3600);

-- Divergence: this write touches only the clone
DELETE FROM analytics.dev.sales_dev WHERE order_date < '2024-01-01';

go deeper

for a junior

Know the syntax and the headline fact: CREATE TABLE ... CLONE returns immediately for any table size because no data is copied, and the two tables are independent afterwards.

for a middle

Explain the mechanism — immutable files plus a metadata file list — and describe what happens on the first write to either side. Connect it to why Time Travel works the same way.

for a senior

Show judgment about the cost and safety consequences: divergence-driven storage growth across dev clones, retention and Fail-safe on churned tables, verifying grants after cloning, and treating cloned production data as production data.

for a principal

Own the policy — who may clone production, into which environments, with what masking, and how clone sprawl is reclaimed. Be clear with stakeholders that clones are logical-error protection, not disaster recovery.

## The mechanism Three architectural facts combine to make cloning free: 1. **Data files are immutable.** Snowflake never edits a stored file in place. A DML statement writes new files and marks the superseded ones as no longer part of the current table version. 2. **A table is a list of files, held as metadata.** The cloud services layer records, for each version of a table, which stored files constitute it, plus the per-file column statistics. 3. **Storage is shared and content-addressed by that metadata.** Any number of catalog objects may reference the same file. So `CREATE TABLE t_clone CLONE t` writes a new catalog object referencing the source's current file list. That is a small metadata transaction whose cost does not scale with table size — cloning 10 TB and cloning 10 MB take about the same time, and neither needs a running warehouse. ## What diverges after the clone The clone is not a view and not a snapshot you must be careful with — it is an independent table. Writes to either side create new files referenced by only that side: ```sql CREATE TABLE sales_dev CLONE prod.public.sales; -- instant, no data copied DELETE FROM sales_dev WHERE order_date < '2024-01-01'; -- writes only in the clone ``` The `DELETE` produces new files (and drops references to old ones) for `sales_dev` only. `prod.public.sales` is untouched. Storage billing follows the same logic: at clone time you add essentially nothing, and thereafter you pay for the files unique to each side. A clone that is heavily rewritten eventually approaches the cost of a full copy — this surprises teams who clone a large table into every developer's schema and then run full rebuilds against each. One related consequence: dropping the source table does not free the storage that the clone still references. The bytes live as long as any object points at them (plus the retention windows below). ## What can be cloned Tables, schemas and whole databases. Cloning a database clones its schemas and their objects. Some object properties are recreated rather than shared, and privileges on the new object are not automatically identical to the source's — check grants after cloning rather than assuming they carried over. ## The Time Travel connection Because a past table version is just an older file list that metadata still points at, Snowflake can serve reads *as of* a past moment, and can clone from one: ```sql SELECT * FROM sales AT (TIMESTAMP => '2026-08-20 09:00:00'::TIMESTAMP_LTZ); CREATE TABLE sales_before_bad_load CLONE sales BEFORE (STATEMENT => '01a2...'); ``` The retention window is governed by `DATA_RETENTION_TIME_IN_DAYS`, which defaults to 1 day. On Standard Edition the maximum for permanent objects is 1 day; Enterprise Edition and above allow up to 90 days. Transient and temporary tables support at most 1 day (and can be set to 0). Beyond Time Travel, permanent tables get a 7-day **Fail-safe** period during which only Snowflake can recover the data — you cannot query it, and you cannot turn it off. Fail-safe storage is billable, which is a real and often-missed cost of churn on large permanent tables. All of this is the same design: retention costs storage because the superseded immutable files must be kept. ## Where cloning is the right tool - **Dev/test environments.** A full-size, realistic dataset per developer or per CI run, created in seconds. Add masking policies or scrub sensitive columns after cloning if the data is regulated — the clone carries the real rows. - **Pre-migration safety copy.** Clone before a destructive backfill; if it goes wrong, swap back rather than restore. - **Point-in-time forensics.** Clone at a timestamp before an incident and investigate without freezing production. ## Where it is misused - Treating clones as free forever, when heavy rewriting makes them cost like copies. - Cloning production into an environment with weaker access controls, on the assumption that a clone is somehow less real than the source. - Assuming a clone gives you a backup in a *different* failure domain. It does not — it is metadata in the same account and region, sharing the same files. Cross-region protection is a replication feature, not a clone. ## Interview framing Lead with immutability plus metadata pointers, state that no compute is involved, then show you understand divergence and the storage bill. If you also connect it to Time Travel and Fail-safe you have demonstrated that you see one storage model behind three features rather than three unrelated features.

  • If cloning is free, why does the storage bill grow after a team clones production into ten dev schemas?
    The clone itself adds almost nothing, but every write to a clone creates files unique to that clone. Ten dev environments each running full table rebuilds end up storing roughly ten extra copies. Charge divergence, not creation — and prefer cloning smaller subsets or refreshing clones on a schedule instead of letting them drift.
  • Does dropping the source table free the storage its clone shares?
    No. The files remain as long as any object references them, and the dropped source additionally holds its own Time Travel and Fail-safe retention. Reclaiming storage means removing every referencing object and waiting out the retention windows, not just dropping the original.
  • How is a clone different from Snowflake's Time Travel query on the same table?
    A Time Travel query reads a past version of the existing table and changes nothing; the window is bounded by DATA_RETENTION_TIME_IN_DAYS. A clone creates a new, independent, writable object that keeps its referenced files alive beyond the source's retention window and can itself be modified.
  • Is a clone a backup?
    Not in the disaster-recovery sense. It is metadata in the same account and region referencing the same underlying files, so it protects against logical mistakes — a bad backfill, a wrong DELETE — not against account-level or regional loss. Cross-region protection requires replication, which is a different feature.

Like giving a second person a copy of a library catalogue card that lists the same shelved books: the books are not reprinted, and only when one person starts adding their own volumes does the collection actually grow.

saying these in an interview costs you the question

  • Thinks a background job copies the data after the clone returns
  • Says the clone stays linked so writes affect both tables
  • Assumes clones never add storage cost, regardless of writes
  • Treats a clone as disaster-recovery protection
  • Believes dropping the source frees the shared storage immediately

context

resolving links…