Clone dynamic tables

Cloning creates a new dynamic table with the same column definitions and data as the source, without physically copying the data. This page explains how to clone a single dynamic table, clone a dynamic table to a regular table, and clone an entire pipeline.

For the full syntax reference, see CREATE DYNAMIC TABLE … CLONE.

Clones are suspended after creation. Resume a cloned dynamic table before it refreshes on schedule (see Resume cloned dynamic tables).

Clone a dynamic table

The simplest clone creates an identical dynamic table that shares the source’s data through Snowflake’s zero-copy cloning:

CREATE DYNAMIC TABLE dt_orders_clone CLONE dt_orders;

The clone inherits the source’s properties, including its column definitions, AS-clause logic, target lag, warehouse, refresh mode, INITIALIZE setting, IMMUTABLE WHERE constraint, clustering keys, and attached storage lifecycle policy. It does not inherit the source’s refresh history or scheduling state.

Clone with time travel

You can clone a dynamic table as it existed at a specific point in the past:

CREATE DYNAMIC TABLE dt_orders_clone
  CLONE dt_orders
    AT (TIMESTAMP => '2025-01-16 12:00:00'::TIMESTAMP_TZ);

For more information about the AT and BEFORE clauses, see Understanding & using Time Travel.

Override properties at clone time

You can change the target lag and warehouse when you clone a dynamic table:

CREATE DYNAMIC TABLE dt_orders_dev
  CLONE dt_orders
  COPY GRANTS
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh;

Use COPY GRANTS to preserve existing privilege grants on the cloned dynamic table.

Clone a dynamic table to a regular table

To create a static snapshot of a dynamic table’s data, clone it to a regular table. The resulting table has the same columns and data but no refresh schedule, target lag, or warehouse assignment:

CREATE TABLE orders_snapshot
  CLONE dt_orders;

The cloned table retains row access policies, masking policies, tags, clustering keys, and comments from the source dynamic table.

Cloning a dynamic table to a regular table has these requirements:

  • The source dynamic table must be initialized. An uninitialized dynamic table has not completed its initial refresh, so there is no materialized snapshot to clone.
  • Dynamic Iceberg tables can’t be cloned, and regular dynamic tables can’t be cloned into Iceberg format. See Limitations.

Error: cloning an uninitialized dynamic table

If you try to clone an uninitialized dynamic table to a regular table, you get the following error:

CREATE TABLE dt_uninitialized_snapshot CLONE dt_uninitialized;
Cannot clone an uninitialized dynamic table. Please run a manual refresh or wait for a scheduled refresh before cloning.

To resolve this, run a manual refresh on the source dynamic table first, or clone it to another dynamic table instead (which doesn’t require initialization).

Clone an entire pipeline

When a dynamic table depends on other dynamic tables or base tables, clone the entire schema or database rather than individual objects. This preserves the relationships between objects in the pipeline, avoiding reinitializations that would otherwise occur.

CREATE SCHEMA analytics_dev
  CLONE analytics_prod;

The schema-level CLONE copies all objects together (base tables, views, and dynamic tables), preserving their relationships. In the cloned schema, unqualified object references in definitions resolve to the cloned copies, so the pipeline is fully independent. However, fully qualified references (such as other_schema.table_name) still point to the original objects, creating cross-schema dependencies.

If you clone a single dynamic table without its upstream objects, the clone still references the original upstream tables. This creates a dependency across environments that may cause unexpected behavior (reinitialization or, if a base object was missing at clone time, refresh failures).

Clone scopePipeline preserved?Reinitialization riskWhen to use
Single dynamic table (same schema)No (references original upstream objects)LowIsolated dynamic tables with no upstream dependencies
Single dynamic table (cross-schema)No (references original upstream objects)HigherTesting with shared upstream sources
Schema cloneYes (all objects cloned together)LowerPipelines contained in one schema
Database cloneYes (all objects cloned together)LowestFull environment copies for development or testing

Resume cloned dynamic tables

All cloned dynamic tables are suspended after cloning. In DYNAMIC_TABLE_GRAPH_HISTORY, this appears as SUSPENDED in the scheduling_state column with a suspend reason of CLONED_AUTO_SUSPENDED. Any downstream dynamic tables of the cloned object are also suspended, shown as UPSTREAM_CLONED_AUTO_SUSPENDED.

To resume scheduled refreshes, run ALTER DYNAMIC TABLE <name> RESUME for each cloned dynamic table.

When you resume an upstream dynamic table, its downstream dynamic tables with the UPSTREAM_CLONED_AUTO_SUSPENDED state automatically resume as well, provided that:

  • The downstream dynamic table was not manually suspended before the clone. If it was manually suspended, you must resume it explicitly.
  • All upstream dynamic tables that the downstream depends on are resumed. If it depends on multiple upstream dynamic tables, it stays suspended until every one is resumed.
  • Dynamic tables that were already suspended before the clone retain their original suspend reason and are not affected by the clone operation.

Initial refresh may reinitialize

The initial refresh after cloning may be a full reinitialization rather than an incremental refresh. Snowflake triggers a full reinitialization if the clone’s change-tracking state has diverged from its base objects.

Reinitialization is more likely when you clone a single dynamic table without its base objects, or when you delay resuming the clone after cloning. Cloning the entire schema or database together reduces this risk.

Plan for the compute cost of a full reinitialization on the initial refresh. For more information, see Reinitialization triggers.

Subsequent refreshes return to the normal refresh mode (incremental or full) based on the dynamic table’s REFRESH_MODE setting.

Limitations

  • Dynamic Iceberg tables can’t be cloned. Cloning a dynamic Iceberg table directly or to a regular table fails with an error. When you clone a schema or database that contains a dynamic Iceberg table, the clone operation succeeds but skips the dynamic Iceberg table. Verify that the cloned schema contains all expected objects after cloning. Cloning a regular dynamic table always produces a regular dynamic table; there is no syntax to change the storage format at clone time.
  • Lag constraint violations can surface at clone time. If an upstream dynamic table’s target lag was changed after a downstream dynamic table was created, the lag constraint (downstream lag must be greater than or equal to the largest upstream lag) is only validated during the clone. The clone command fails if the constraint is not met. To avoid this, verify target lag constraints before cloning. For more information, see Set the target lag for a dynamic table.
  • Storage diverges after cloning. At clone time, the source and clone share existing micro-partitions through zero-copy cloning. As each side is modified through refreshes, the shared storage diverges and both copies accrue independent storage costs.

What’s next