Snowflake: Time Travel, Cloning, Data Sharing & Ingestion

Time Travel & Fail-safe

-- Query/restore data as it existed at a specific past point --
-- self-service, no external backup restoration needed
SELECT * FROM orders AT (OFFSET => -3600);  -- 1 hour ago
SELECT * FROM orders BEFORE (STATEMENT => 'query-id-here');

UNDROP TABLE orders;  -- recover an accidentally dropped table

-- Fail-safe: a FURTHER 7-day period AFTER Time Travel expires --
-- support-mediated only, not user-queryable via SQL, last-resort
-- disaster recovery, distinct from self-service Time Travel.

Zero-Copy Cloning

-- Instant, metadata-only clone -- shares storage with the
-- original until either side is modified (copy-on-write).
-- Fast/cheap regardless of the source table's actual size.
CREATE DATABASE dev_db CLONE prod_db;
CREATE TABLE orders_test CLONE orders;

Semi-Structured Data

-- VARIANT column stores JSON/Avro/Parquet-derived data directly --
-- queryable via SQL dot/bracket notation, no separate ETL step
-- to force it into a rigid schema first.
CREATE TABLE events (id INT, raw_data VARIANT);

SELECT raw_data:user.email::string AS email,
       raw_data:event_type::string AS event_type
FROM events;

Ingestion: Snowpipe & Streams

-- Snowpipe: continuous ingestion -- reacts to new files landing
-- in cloud storage automatically, instead of a scheduled batch job
CREATE PIPE my_pipe AS
  COPY INTO events FROM @my_s3_stage
  FILE_FORMAT = (TYPE = 'JSON');

-- Streams: change-data-capture -- tracks row-level inserts/
-- updates/deletes since last consumed, for incremental pipelines
CREATE STREAM orders_stream ON TABLE orders;
SELECT * FROM orders_stream;  -- only what changed since last read

-- External tables: query files in S3/etc DIRECTLY, no load step
CREATE EXTERNAL TABLE raw_logs
  LOCATION = @my_s3_stage
  FILE_FORMAT = (TYPE = 'PARQUET');

Secure Data Sharing

• Grants another Snowflake account LIVE, read-only access to specific data -- never copied or duplicated.

• The consuming account always sees current data, not a stale snapshot; the sharing account can revoke access at any time.

• Avoids the staleness/duplication overhead of traditional file-export or ETL-based data exchange between organizations.