How to Copy a Table Between Databases in Snowflake

How do I copy a table between databases in Snowflake?

Use CREATE TABLE … CLONE or CREATE TABLE AS SELECT (CTAS) to duplicate a table—including data and structure—from one Snowflake database to another.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

How do I quickly duplicate a table in another database?

Use CREATE TABLE target_db.schema.table CLONE source_db.schema.table;. CLONE copies the full structure and data instantly without rewriting files.

When should I prefer CTAS over CLONE?

Choose CREATE TABLE AS SELECT (CTAS) when you need to filter, transform, or only copy a subset of columns/rows while duplicating.

What permissions are required?

You need USAGE on both databases and schemas, plus CREATE TABLE in the target schema. To clone, you also need SELECT on the source table.

How do I keep grants in sync?

CLONE does not copy grants by default. Run SHOW GRANTS ON TABLE source_db.schema.table then re-apply with GRANT statements on the new table.

Best practice: qualify objects fully

Always prefix with database.schema to avoid ambiguities when connected to the target database.

Step-by-step CLONE example

1. Create the analytics database and schema if needed.
2. Issue the CLONE statement.
3. Re-grant privileges.

Step-by-step CTAS example with filters

1. Decide which columns/rows to copy.
2. Write the CREATE TABLE AS SELECT query.
3. Verify row count.

Why How to Copy a Table Between Databases in Snowflake is important

How to Copy a Table Between Databases in Snowflake Example Usage


-- Copy Orders table into an analytics database and keep row-level detail
CREATE TABLE analytics_db.sales.Orders CLONE prod_db.public.Orders;

-- Copy only high-value orders (> $1,000) using CTAS
CREATE TABLE analytics_db.sales.HighValueOrders AS
SELECT *
FROM prod_db.public.Orders
WHERE total_amount > 1000;

How to Copy a Table Between Databases in Snowflake Syntax


-- Clone full table (structure + data, instantaneous)
CREATE TABLE target_db.analytics.Orders CLONE source_db.public.Orders;

-- Clone only structure (omit data)
CREATE TABLE target_db.analytics.Orders LIKE source_db.public.Orders;

-- CTAS with transformation
CREATE TABLE target_db.analytics.Orders_Q4 AS
SELECT *
FROM source_db.public.Orders
WHERE order_date BETWEEN '2023-10-01' AND '2023-12-31';

Common Mistakes

Using CTAS when CLONE is adequate: CTAS rewrites all micro-partitions, consuming time and credits. Fix by using CLONE for exact, full copies.
Forgetting to grant USAGE on the target database: Without it, the CREATE statements fail. Always grant USAGE before attempting the copy.