How to Duplicate a Table in Snowflake
Last updated July 24, 2026 · By the SaturnSQL team
CREATE TABLE ... CLONE makes a zero-copy duplicate instantly without consuming extra storage until the copies diverge. Use CREATE TABLE AS SELECT instead when you want a physically independent copy.
Zero-copy clone (preferred)
A clone copies metadata only: it points at the same micro-partitions as the original and consumes no extra storage until one side changes. Creating one takes roughly the same time regardless of table size. You can also clone at a point in time with AT (OFFSET => -3600), which makes this the standard way to capture a before-state ahead of a risky migration.
CREATE TABLE orders_backup CLONE orders;Physical copy with CTAS
CTAS reads and writes every row, so it costs warehouse time and full storage, and it produces a genuinely independent copy. Use it when the copy needs to survive changes to the original's structure, or when you want to filter or reshape data on the way through. Clustering keys and comments are not carried over.
CREATE TABLE orders_copy AS SELECT * FROM orders;Structure only, no rows
CREATE TABLE ... LIKE copies the column definitions, defaults, and clustering key but no rows. It is the right choice for a staging table you are about to load into, where a clone would hand you data you then have to delete.
CREATE TABLE orders_empty LIKE orders;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free