How to Backup Snowflake in PostgreSQL

How do I back up a Snowflake database without downtime?

Backup Snowflake with zero-copy clones, Time Travel, and external exports to guarantee fast, granular recovery.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why back up Snowflake when Time Travel exists?

Time Travel only keeps historical data for 1–90 days. Backups let you restore data after that window, move data between accounts, or meet legal retention rules.

What are the main Snowflake backup methods?

Three options: (1) zero-copy CLONE for instant, space-efficient snapshots; (2) COPY INTO to export Parquet or CSV files to S3 for off-platform storage; (3) scheduled TASK chains that automate either approach.

How do I create a full database clone?

Run CREATE DATABASE myshop_backup CLONE myshop AT (TIMESTAMP => TO_TIMESTAMP_TZ('2024-05-01 00:00:00'));. The clone appears immediately and costs nothing until data changes.

How do I back up only critical ecommerce tables?

Clone individual tables: CREATE OR REPLACE TABLE customers_bak CLONE myshop.public.customers;. Repeat for orders, products, and orderitems.

How do I export data to S3?

1) CREATE STAGE s3_bak URL='s3://my-bucket/backup' CREDENTIALS=(AWS_KEY_ID='...' AWS_SECRET_KEY='...'); 2) COPY INTO @s3_bak FROM orders FILE_FORMAT=(TYPE=PARQUET) OVERWRITE=TRUE;

How can I automate backups?

Create a TASK that runs daily and calls either CREATE ... CLONE or COPY INTO. Example: CREATE TASK daily_db_clone SCHEDULE='USING CRON 0 5 * * * UTC' AS CREATE OR REPLACE DATABASE myshop_backup CLONE myshop;

Best practices for Snowflake backups

Use descriptive names with dates (myshop_backup_20240501), store exports in versioned buckets, encrypt objects, and test restores monthly.

Why How to Backup Snowflake in PostgreSQL is important

How to Backup Snowflake in PostgreSQL Example Usage


-- Back up the Orders and OrderItems tables nightly
CREATE TASK nightly_order_backup 
  SCHEDULE='USING CRON 0 2 * * * UTC'
AS
  CREATE OR REPLACE TABLE orders_bak CLONE myshop.public.orders;
  CREATE OR REPLACE TABLE orderitems_bak CLONE myshop.public.orderitems;

How to Backup Snowflake in PostgreSQL Syntax


-- Full database clone
CREATE DATABASE <backup_db> CLONE <source_db>
  [ AT (TIMESTAMP => '<timestamp>') | BEFORE (STATEMENT => '<query_id>') ];

-- Schema clone
CREATE SCHEMA <backup_schema> CLONE <source_db>.<schema>
  [ AT | BEFORE ... ];

-- Table clone
CREATE OR REPLACE TABLE <backup_table> CLONE <source_db>.<schema>.<table>
  [ AT | BEFORE ... ];

-- External unload to S3
CREATE STAGE <stage_name>
  URL='s3://<bucket>/backup'
  CREDENTIALS=(AWS_KEY_ID='<id>' AWS_SECRET_KEY='<key>');

COPY INTO @<stage_name>/customers/
  FROM customers
  FILE_FORMAT=(TYPE=PARQUET COMPRESSION=SNAPPY)
  SINGLE=TRUE
  OVERWRITE=TRUE;

-- Automate with TASK
CREATE TASK <task_name>
  SCHEDULE='USING CRON 0 5 * * * UTC'
AS
  CREATE OR REPLACE DATABASE <backup_db> CLONE <source_db>;

Common Mistakes

Relying solely on Time Travel. Once the retention window expires, deleted data is gone. Fix: create periodic clones or exports to extend retention indefinitely.
Exporting with COPY INTO but forgetting OVERWRITE=TRUE, which leaves multiple versions and inflates S3 costs. Fix: add OVERWRITE=TRUE or clean the bucket regularly.