Every time I demo Snowflake to someone new, zero-copy cloning gets the biggest reaction. You type one line. You get an instant copy of a table — or an entire database — with no data duplication, no storage cost at the moment of creation. It feels like magic.
And it is genuinely impressive engineering. I’m not here to tell you it’s a bad feature. It’s one of my favourite things about Snowflake and I use it constantly.
But I’ve watched teams get badly surprised by it. A dev environment clone that started silently inflating the storage bill. A cloned database used for UAT that bypassed data masking policies on PII columns. A Time Travel query on a clone that returned nothing because the source table’s retention window had already expired.
None of these are edge cases. They’re predictable consequences of how zero-copy cloning actually works — consequences that the marketing language around “instant, free copies” tends to obscure. Let me get into it.
HOW ZERO-COPY CLONING ACTUALLY WORKS
When you clone a table in Snowflake, you’re not copying data. You’re creating a new metadata pointer that references the same underlying micropartitions as the source object.
SQL — Instant. No data movement. No storage cost at this moment.
-- Instant. No data movement. No storage cost at this moment.CREATE TABLE orders_clone CLONE orders; -- Works for schemas tooCREATE SCHEMA dev_schema CLONE prod_schema; -- And entire databasesCREATE DATABASE dev_db CLONE prod_db;At the moment of creation, the clone costs you nothing in storage. Both the original and the clone point to the same micropartitions on disk. The moment either side changes, Snowflake uses copy-on-write. The modified micropartition gets written fresh for whichever side made the change.
Think of it like a fork in a Git repo. At fork time, both repos share the same commit history. The moment either side commits, they diverge. The more divergence, the more independent storage you accumulate. Zero-copy cloning works exactly like this — except the “commits” are DML operations and the cost is real money.
PROBLEM 1 — STORAGE COSTS THAT CREEP UP INVISIBLY
A team clones production to create a development environment. The dev team runs experiments, updates records, backfills some columns. Six weeks later, storage is up 40%. Every modified micropartition in the dev database is now independent storage. Production kept its micropartitions too. You’re paying for both.
The cost also compounds with Time Travel. If production has 90-day retention and you clone it for dev, that clone also starts with 90-day retention. DML operations in dev accumulate 90 days of write history.
SQL — Clone with reduced Time Travel for non-production environments.
-- Clone with reduced Time Travel for non-production environmentsCREATE DATABASE dev_db CLONE prod_db; -- Immediately reduce Time Travel on the cloneALTER DATABASE dev_db SET DATA_RETENTION_TIME_IN_DAYS = 1; -- Or set it at schema level for finer controlALTER SCHEMA dev_db.analytics SET DATA_RETENTION_TIME_IN_DAYS = 0;Audit clone storage footprint regularly:
SQL — Find clones and their storage footprint.
-- Find clones and their storage footprintSELECT table_catalog, table_schema, table_name, clone_group_id, bytes / (1024 * 1024 * 1024) AS size_gb, row_count, created AS clone_created_atFROM snowflake.account_usage.tablesWHERE clone_group_id IS NOT NULL AND deleted IS NULLORDER BY bytes DESC;This same class of invisible cost creep comes up a lot with Snowflake features that look free until you read the bill — similar to what I covered in “Why I Stopped Using Snowflake Tasks for Orchestration“
PROBLEM 2 — GOVERNANCE POLICIES DON’T FOLLOW THE CLONE
Dynamic data masking policies are not automatically inherited by clones. The clone is a new object with no masking policies applied.
SQL — On prod table — email is masked for non-PII roles.
-- On prod table — email is masked for non-PII rolesSELECT email FROM prod_orders LIMIT 5;-- Result: ***@***.com (masked) -- On the clone without explicit policy assignmentSELECT email FROM orders_clone LIMIT 5;-- Result: john.doe@company.com (unmasked raw PII)Same problem with row access policies. A user restricted to one region in production can see all regions on the clone.
The fix — make policy application part of your clone process:
SQL — Step 1: Clone the table.
-- Step 1: Clone the tableCREATE TABLE dev_db.analytics.orders CLONE prod_db.analytics.orders; -- Step 2: Re-apply masking policies immediatelyALTER TABLE dev_db.analytics.orders MODIFY COLUMN email SET MASKING POLICY prod_db.security.email_mask; ALTER TABLE dev_db.analytics.orders MODIFY COLUMN phone_number SET MASKING POLICY prod_db.security.phone_mask; -- Step 3: Re-apply row access policyALTER TABLE dev_db.analytics.orders ADD ROW ACCESS POLICY prod_db.security.region_access_policy ON (region_code);Better: wrap it in a stored procedure that enforces policy application as part of the clone operation:
CLI command — executes CREATE OR REPLACE PROCEDURE create_governed_clon….
CREATE OR REPLACE PROCEDURE create_governed_clone( source_table VARCHAR, target_table VARCHAR, masking_policies ARRAY)RETURNS STRINGLANGUAGE JAVASCRIPTAS$$ var clone_stmt = snowflake.execute({ sqlText: `CREATE TABLE ${TARGET_TABLE} CLONE ${SOURCE_TABLE}` }); for (var i = 0; i < MASKING_POLICIES.length; i++) { var policy = MASKING_POLICIES[i]; snowflake.execute({ sqlText: `ALTER TABLE ${TARGET_TABLE} MODIFY COLUMN ${policy.column} SET MASKING POLICY ${policy.policy_name}` }); } return 'Clone created with governance policies applied: ' + TARGET_TABLE;$$;A clone that exists without its governance policies re-applied is a compliance gap, not a convenience feature. If you’re running dbt on top of Snowflake, the same mindset applies — see “The Problem with dbt Tests Nobody Talks About“:
PROBLEM 3 — TIME TRAVEL ON CLONES ISN’T WHAT YOU THINK
A clone’s Time Travel history starts from its creation date. You cannot go back to a point before the clone was created on the clone object.
SQL — Source table: exists since 2024-01-01, 90-day retention.
-- Source table: exists since 2024-01-01, 90-day retention-- Clone created: 2024-03-01 -- This works — within the clone's own historySELECT * FROM orders_cloneAT (TIMESTAMP => '2024-03-15 10:00:00'::TIMESTAMP_TZ); -- This FAILS — before the clone existedSELECT * FROM orders_cloneAT (TIMESTAMP => '2024-02-01 10:00:00'::TIMESTAMP_TZ);-- Error: Statement time travel is not available for this object -- For pre-clone history, query the SOURCE tableSELECT * FROM ordersAT (TIMESTAMP => '2024-02-01 10:00:00'::TIMESTAMP_TZ);Also watch: if you clone a table that’s near the end of its retention window, any history that expires on the source is gone. The clone can’t access expired source history.
PROBLEM 4 — CLONE SPRAWL AND THE INVISIBLE COST PROBLEM
Zero-copy cloning is so easy that people create clones for everything — UAT, load testing, feature branches, one-off investigations that were supposed to be deleted on Friday. Three months later, nobody knows what exists or how diverged it’s become.
Full clone audit query:
Snowflake metadata query — inspects execution history, storage, or credit telemetry.
SELECT t.table_catalog AS database_name, t.table_schema AS schema_name, t.table_name, t.clone_group_id, t.row_count, ROUND(t.bytes / POW(1024, 3), 3) AS size_gb, t.created AS created_at, t.last_altered AS last_modified_at, DATEDIFF('day', t.created, CURRENT_TIMESTAMP()) AS age_days, CASE WHEN DATEDIFF('day', t.last_altered, CURRENT_TIMESTAMP()) > 30 THEN 'STALE — review for deletion' ELSE 'Active' END AS staleness_flagFROM snowflake.account_usage.tables tWHERE t.clone_group_id IS NOT NULL AND t.deleted IS NULLORDER BY t.bytes DESC;Tag every clone at creation with expiry metadata:
Snowflake metadata query — inspects execution history, storage, or credit telemetry.
CREATE DATABASE uat_db CLONE prod_db COMMENT = '{"purpose": "UAT for v2.4 release", "owner": "data-platform@company.com", "expires": "2024-04-30", "ticket": "JIRA-1234"}'; -- Query clones past their expiry dateSELECT table_catalog, table_schema, table_name, TRY_PARSE_JSON(comment):expires::DATE AS expiry_date, TRY_PARSE_JSON(comment):owner::STRING AS ownerFROM snowflake.account_usage.tablesWHERE clone_group_id IS NOT NULL AND deleted IS NULL AND TRY_PARSE_JSON(comment):expires::DATE < CURRENT_DATE();PROBLEM 5 — CLONING STREAMS AND TASKS DOESN’T WORK HOW YOU EXPECT
Streams are not cloned when you clone a table or schema. The clone contains the data but has no streams attached.
SQL — Prod table has a stream attached.
-- Prod table has a stream attachedSHOW STREAMS ON TABLE prod_db.analytics.orders;-- Returns: orders_cdc_stream -- Clone the tableCREATE TABLE dev_db.analytics.orders CLONE prod_db.analytics.orders; -- Check streams on cloneSHOW STREAMS ON TABLE dev_db.analytics.orders;-- Returns: (empty)If you need CDC streams on cloned tables, create them explicitly after cloning:
SQL STREAM definition (dev_db.analytics.orders) — review object privileges and warehouse parameters.
CREATE OR REPLACE STREAM dev_db.analytics.orders_cdc_stream ON TABLE dev_db.analytics.orders APPEND_ONLY = FALSE SHOW_INITIAL_ROWS = FALSE; CREATE OR REPLACE TASK dev_db.analytics.process_orders_changes WAREHOUSE = dev_wh SCHEDULE = '5 minute' WHEN SYSTEM$STREAM_HAS_DATA('dev_db.analytics.orders_cdc_stream')AS CALL dev_db.analytics.process_orders_sp();Tasks are cloned but start in a SUSPENDED state — they don’t auto-resume, which is correct behaviour (you don’t want dev tasks firing against prod targets), but it surprises teams expecting a live pipeline copy. If your pipeline relies on dbt incremental models consuming from those streams, the failure compounds further — see “The Problem with Incremental Models in dbt Nobody Talks About“
WHEN ZERO-COPY CLONING IS THE RIGHT TOOL
Before risky migrations — clone first, get an instant rollback point:
SQL — Before a risky migration.
-- Before a risky migrationCREATE TABLE orders_pre_migration CLONE orders; -- Run your migrationALTER TABLE orders ADD COLUMN new_column VARCHAR;UPDATE orders SET new_column = derive_value(existing_column); -- If something went wrong:-- DROP TABLE orders;-- ALTER TABLE orders_pre_migration RENAME TO orders;Instant dev environments, UAT cycles, zero-downtime data fixes — all excellent use cases. The feature is great. Using it without understanding the lifecycle is where teams get into trouble. If you want to go further on cost reduction for dev workloads, pairing clone strategy with DuckDB is worth exploring — “How to Query Snowflake in DuckDB and Cut Your Bill While Doing It“
FREQUENTLY ASKED QUESTIONS
Q: Does zero-copy cloning in Snowflake really cost nothing?
A: At creation: yes. The cost begins the moment either side is modified via copy-on-write. In active dev environments that are modified frequently, storage costs can grow significantly over weeks. Time Travel retention on the clone compounds this further.
Q: Do data masking policies transfer when you clone a table?
A: No. Masking policies are not inherited by clones. Sensitive columns are exposed in plaintext on the clone unless you explicitly re-apply policies after creation. Treat clone creation and policy application as a single atomic operation.
Q: Can I use Time Travel on a clone to go back before it was created?
A: No. A clone’s Time Travel history starts at its creation date. For history before the clone was created, query the source table directly.
Q: Are Snowflake Streams copied when you clone a table?
A: No. Streams are not part of the clone operation. Create them explicitly on the clone if your pipeline depends on CDC. Tasks are cloned but start suspended.
Q: How do I audit all clones in my Snowflake account?
A: Query snowflake.account_usage.tables filtering on clone_group_id IS NOT NULL. Tag clones at creation with JSON metadata in the COMMENT field — owner, expiry, purpose — to make audits actionable.
Q: What’s the best practice for cloning production for dev?
A: Clone, then immediately: reduce Time Travel retention to 0 or 1 day, re-apply all masking and row access policies, set a resource monitor on dev warehouses, and tag the clone with an expiry date in the COMMENT field.
Related blogs
→ Snowflake official docs — cloning objects
→ Snowflake dynamic data masking docs
→ Snowflake Time Travel docs
→ Snowflake resource monitors docs
→ Snowflake Streams
