Clone dynamic tables¶

Cloning creates a new dynamic table with the same column definitions and contains all the existing data from the source dynamic table, without actually copying the data.

You can clone a dynamic table to a new dynamic table or regular table.

Clone a dynamic table to a new dynamic table¶

Cloned dynamic tables are suspended by default, whether you clone a dynamic table directly or clone a database or schema that contains dynamic tables. In the output of the DYNAMIC_TABLE_GRAPH_HISTORY table function, their SCHEDULING_STATE column shows CLONED_AUTO_SUSPENDED as the reason_code. Any dynamic tables created downstream to these cloned dynamic tables are also suspended, with a reason_code of UPSTREAM_CLONED_AUTO_SUSPENDED. For more information, see Automatic dynamic table suspension.

-- Clone a dynamic table to a new dynamic table
CREATE [ OR REPLACE ] [ TRANSIENT ] DYNAMIC TABLE <name>
  CLONE <source_dynamic_table>
        [ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ]
  [
    COPY GRANTS
    TARGET_LAG = { '<num> { seconds | minutes | hours | days }' | DOWNSTREAM }
    WAREHOUSE = <warehouse_name>
  ]
Copy

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

CREATE DYNAMIC TABLE my_cloned_dynamic_table CLONE my_dynamic_table AT (TIMESTAMP => TO_TIMESTAMP_TZ('04/05/2013 01:02:03', 'mm/dd/yyyy hh24:mi:ss'));
Copy

For more information, see Cloning using Time Travel (databases, schemas, tables, dynamic tables, event tables, and streams only).

Clone a dynamic table to a new table¶

Cloned tables inherit the same column definitions and data of the source dynamic table but lack dynamic table-specific properties. They retain row access and masking policies, tags, clustering keys, and comments.

-- Clone a dynamic table to a new table
CREATE [ OR REPLACE ] TABLE [ IF NOT EXISTS ] <name>
CLONE <source_dynamic_table_name>
  [ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ]
Copy

Cloning a dynamic table to a regular table follows the same considerations as cloning a regular table, with the following exceptions:

  • The source dynamic table has to be initialized in order to be cloned as a regular table.

  • You can’t clone dynamic Apache Iceberg™ tables.

Best practice for cloning pipelines of dynamic tables¶

Clone all elements of the dynamic table pipeline in the same clone command to avoid reinitializations of your pipeline. You can do this by consolidating all elements of the pipeline (e.g. base tables, view, and dynamic tables) in the same schema or database.

Language: English