Data Science Snowflake

Snowflake AWS S3 Integration Tutorial: Snowpipe, Streams, Tasks & Time Travel

October 12, 2026

Snowflake with AWS S3: Complete Data Engineering Guide with Snowpipe, Streams, Tasks, Time Travel and Cloning

Snowflake is much more than a cloud database. It is a complete cloud data platform that can ingest data from AWS S3, process incremental changes, automate transformations, recover accidentally deleted data, create zero-copy development environments, and support modern analytics workloads.

In this practical Snowflake tutorial, we will build a complete understanding of the most important Snowflake commands and AWS integration patterns used by data engineers.

We will cover:

Snowflake warehouses, databases and schemas
Tables and semi-structured JSON data
File formats
Internal and external stages
AWS S3 integration
AWS IAM roles
COPY INTO
Snowpipe continuous ingestion
Streams for Change Data Capture
Tasks and CRON scheduling
Incremental loading
MERGE
Time Travel
UNDROP recovery
Zero-copy cloning
Snowflake micro-partitions and clustering

1. Understanding Snowflake Architecture

A basic Snowflake environment can be represented as:

Snowflake Account
        |
        +-- Virtual Warehouse
        |
        +-- Database
              |
              +-- Schema
                    |
                    +-- Tables
                    +-- Views
                    +-- Stages
                    +-- File Formats
                    +-- Streams
                    +-- Tasks
                    +-- Pipes

A Virtual Warehouse provides compute power.

A Database organizes data.

A Schema groups related Snowflake objects.

2. Setting the Snowflake Context

Before running commands, it is common to select a role, warehouse and schema.

USE ROLE ACCOUNTADMIN;

USE WAREHOUSE COMPUTE_WH;

USE DATABASE MYDB;

USE SCHEMA MYDB.MYSCHEMA;

You can inspect your environment with:

SHOW DATABASES;

SHOW SCHEMAS;

SHOW TABLES;

SHOW WAREHOUSES;

3. Creating Snowflake Tables

A normal relational table can be created as follows:

CREATE OR REPLACE TABLE EMPLOYEE (
    ID INTEGER,
    NAME VARCHAR(50),
    LOCATION VARCHAR(50),
    EMAIL VARCHAR(100)
);

Snowflake also supports semi-structured data using the VARIANT data type.

CREATE OR REPLACE TABLE EVENT (
    EVENT VARIANT
);

This is particularly useful when loading JSON files from Amazon S3.

4. Snowflake File Formats

A file format tells Snowflake how an incoming file should be interpreted.

CSV File Format

CREATE OR REPLACE FILE FORMAT MY_CSV_FORMAT
TYPE = 'CSV'
FIELD_DELIMITER = ','
RECORD_DELIMITER = '\n'
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '"';

JSON File Format

CREATE OR REPLACE FILE FORMAT MY_JSON_FORMAT
TYPE = 'JSON';

Parquet File Format

CREATE OR REPLACE FILE FORMAT MY_PARQUET_FORMAT
TYPE = 'PARQUET';

Other formats supported by Snowflake include:

CSV
JSON
Parquet
Avro
ORC
XML

5. Why Integrate AWS S3 with Snowflake?

Amazon S3 is commonly used as a data lake or landing zone before data is loaded into Snowflake.

A typical architecture looks like this:

Application
     |
     v
Amazon S3
     |
     v
AWS IAM Role
     |
     v
Snowflake Storage Integration
     |
     v
External Stage
     |
     v
COPY INTO
     |
     v
Snowflake Table

6. Creating an AWS S3 Storage Integration

For production systems, using an IAM role is better than storing AWS access keys directly inside Snowflake.

CREATE OR REPLACE STORAGE INTEGRATION S3_INT
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = 'S3'
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN =
'arn:aws:iam::294845879996:role/snowflakerole'
STORAGE_ALLOWED_LOCATIONS = (
    's3://de-academy-training-bucket/'
);

After creating the integration, inspect it:

DESC INTEGRATION S3_INT;

Snowflake returns important values such as:

STORAGE_AWS_IAM_USER_ARN
STORAGE_AWS_EXTERNAL_ID

These values are then added to the AWS IAM role trust relationship.

7. Creating an External S3 Stage

A Snowflake stage tells Snowflake where files are located.

CREATE OR REPLACE STAGE MY_S3_STAGE
STORAGE_INTEGRATION = S3_INT
URL = 's3://de-academy-training-bucket/'
FILE_FORMAT = MY_CSV_FORMAT;

Verify that Snowflake can see the S3 files:

LIST @MY_S3_STAGE;

The relationship is:

Amazon S3
     |
     v
Storage Integration
     |
     v
External Stage
     |
     v
Snowflake

8. Loading S3 Data with COPY INTO

Snowflake uses COPY INTO for bulk file loading.

COPY INTO EMPLOYEE
FROM @MY_S3_STAGE
FILE_FORMAT = (
    FORMAT_NAME = 'MY_CSV_FORMAT'
);

If the stage already has a file format associated with it, the command can be simplified:

COPY INTO EMPLOYEE
FROM @MY_S3_STAGE;

9. Loading Specific Files Using PATTERN

Suppose the S3 bucket contains:

employee_001.csv
employee_002.csv
customer_001.csv
orders_001.csv

You can load only employee files:

COPY INTO EMPLOYEE
FROM @MY_S3_STAGE
PATTERN = '.*employee.*[.]csv';

PATTERN is useful when multiple datasets share the same S3 location.

10. Loading JSON Data from S3

Create a JSON file format:

CREATE OR REPLACE FILE FORMAT MY_JSON_FORMAT
TYPE = 'JSON';

Create an S3 stage:

CREATE OR REPLACE STAGE MY_S3_SNOWPIPE_STAGE
STORAGE_INTEGRATION = S3_INT
URL = 's3://de-academy-training-bucket/event/'
FILE_FORMAT = MY_JSON_FORMAT;

Verify the files:

LIST @MY_S3_SNOWPIPE_STAGE;

Raw JSON can be stored using VARIANT:

CREATE OR REPLACE TABLE EVENT (
    EVENT VARIANT
);

11. Snowpipe for Continuous S3 Data Loading

COPY INTO is excellent for bulk loading, but many production systems receive new files continuously.

This is where Snowpipe becomes useful.

S3 File Upload
      |
      v
S3 Event Notification
      |
      v
SNS / SQS
      |
      v
Snowpipe
      |
      v
Snowflake Table

Create the pipe:

CREATE OR REPLACE PIPE EVENT_PIPE
AUTO_INGEST = TRUE
AS
COPY INTO EVENT
FROM @MY_S3_SNOWPIPE_STAGE;

Snowpipe can automatically process files when new S3 object notifications are received.

12. COPY INTO vs Snowpipe

COPY INTO is generally used for manual, scheduled or batch ingestion.

Snowpipe is designed for continuous file ingestion.

Batch Loading:

S3
 |
 v
COPY INTO
 |
 v
Snowflake


Continuous Loading:

S3
 |
 v
Event Notification
 |
 v
Snowpipe
 |
 v
Snowflake

13. Snowflake Streams for Change Data Capture

Streams allow Snowflake to track changes to a table.

CREATE OR REPLACE STREAM ORDERS_STREAM
ON TABLE ORDERS;

A stream can help identify:

New rows
Updated rows
Deleted rows

Instead of repeatedly processing millions of existing records, your pipeline can process only what changed.

14. Snowflake Tasks

A task automates SQL execution.

For example, run a task every five minutes:

CREATE OR REPLACE TASK MY_TASK
WAREHOUSE = COMPUTE_WH
SCHEDULE = '5 MINUTES'
AS
INSERT INTO TARGET_TABLE
SELECT *
FROM SOURCE_TABLE;

Start the task:

ALTER TASK MY_TASK RESUME;

Stop the task:

ALTER TASK MY_TASK SUSPEND;

Execute it immediately:

EXECUTE TASK MY_TASK;

15. Check Snowflake Task History

SELECT *
FROM TABLE(
    INFORMATION_SCHEMA.TASK_HISTORY(
        TASK_NAME => 'MY_TASK'
    )
)
ORDER BY SCHEDULED_TIME DESC;

This helps troubleshoot failed or skipped task executions.

16. Scheduling Tasks with CRON

Snowflake Tasks also support CRON expressions.

Run every Sunday at 10:00 UTC:

SCHEDULE = 'USING CRON 0 10 * * SUN UTC'

Run every day at 2 AM:

SCHEDULE = 'USING CRON 0 2 * * * UTC'

Run every Monday at 9 AM:

SCHEDULE = 'USING CRON 0 9 * * MON UTC'

17. Incremental Data Loading

Suppose SOURCE_TABLE contains new records and TARGET_TABLE already contains previously processed records.

We can insert only records that do not already exist:

INSERT INTO TARGET_TABLE (
    id,
    name,
    created_date,
    created_day,
    created_month,
    created_year
)
SELECT
    a.id,
    a.name,
    a.created_date,
    DAY(a.created_date),
    MONTH(a.created_date),
    YEAR(a.created_date)
FROM SOURCE_TABLE a
LEFT JOIN TARGET_TABLE b
    ON a.id = b.id
WHERE b.id IS NULL;

18. Using MERGE for Incremental Pipelines

MERGE is often more useful because it can update existing rows and insert new rows.

MERGE INTO TARGET_TABLE t
USING SOURCE_TABLE s
ON t.id = s.id

WHEN MATCHED THEN
UPDATE SET
    t.name = s.name

WHEN NOT MATCHED THEN
INSERT (
    id,
    name
)
VALUES (
    s.id,
    s.name
);

This is commonly called an upsert.

19. Building a Complete CDC Pipeline

Snowpipe, Streams and Tasks can be combined:

Amazon S3
    |
    v
Snowpipe
    |
    v
RAW_ORDERS
    |
    v
STREAM
    |
    v
TASK
    |
    v
MERGE
    |
    v
CURATED_ORDERS

This creates an automated incremental data pipeline.

20. Snowflake Time Travel

Snowflake Time Travel allows administrators to access historical versions of data.

Configure retention:

ALTER TABLE DROP_TABLE
SET DATA_RETENTION_TIME_IN_DAYS = 30;

Query the table as it existed two minutes ago:

SELECT *
FROM DROP_TABLE
AT (
    OFFSET => -60 * 2
);

21. Query Data at a Specific Historical Timestamp

SELECT *
FROM DROP_TABLE
AT (
    TIMESTAMP =>
    '2024-08-29 23:59:00.352 -0700'::TIMESTAMP_TZ
);

22. Query Data Before a Specific SQL Statement

SELECT *
FROM DROP_TABLE
BEFORE (
    STATEMENT =>
    '01b6b085-0000-b70f-0007-c56a0002eb16'
);

This is particularly useful after an accidental UPDATE or DELETE.

23. Scenario: A User Accidentally Deleted Production Data

Imagine a user accidentally executes:

DELETE FROM CUSTOMER;

The administrator should first identify the DELETE query ID.

Then inspect the table immediately before the DELETE:

SELECT *
FROM CUSTOMER
BEFORE (
    STATEMENT => 'DELETE_QUERY_ID'
);

Instead of immediately overwriting production, create a recovery table:

CREATE OR REPLACE TABLE CUSTOMER_RECOVERY AS
SELECT *
FROM CUSTOMER
BEFORE (
    STATEMENT => 'DELETE_QUERY_ID'
);

Validate the recovered records before copying them back into production.

24. Scenario: A User Accidentally Dropped a Table

Suppose someone runs:

DROP TABLE CUSTOMER;

The administrator can restore it using:

UNDROP TABLE CUSTOMER;

Snowflake also supports:

UNDROP SCHEMA MYSCHEMA;

UNDROP DATABASE MYDB;

The object must still be within its Time Travel retention period.

25. Snowflake Zero-Copy Cloning

Snowflake can create development or testing environments very quickly using zero-copy cloning.

CREATE OR REPLACE DATABASE TEST_MYDB
CLONE MYDB;

You can safely test changes inside TEST_MYDB without modifying the original database.

When testing is complete:

DROP DATABASE TEST_MYDB;

26. Historical Cloning

You can also clone a table from its historical state.

CREATE TABLE RESTORED_TABLE
CLONE MY_TABLE
BEFORE (
    STATEMENT => 'QUERY_ID'
);

This combines Time Travel with Snowflake cloning.

27. Does Snowflake Support Table Partitioning?

Snowflake works differently from systems such as Hive, Spark or traditional partitioned warehouses.

You normally do not manually partition a Snowflake table.

Snowflake automatically stores table data in internal micro-partitions.

Table
  |
  +-- Micro-partition
  +-- Micro-partition
  +-- Micro-partition
  +-- Micro-partition

Snowflake uses metadata about these micro-partitions to eliminate unnecessary data during query execution.

28. Scenario: A Very Large Table Needs Better Query Performance

Suppose your SALES table contains billions of records and most analytical queries filter by SALE_DATE.

Instead of manually partitioning the table, consider a clustering key:

CREATE TABLE SALES (
    ID INTEGER,
    SALE_DATE DATE,
    CUSTOMER_ID INTEGER,
    AMOUNT NUMBER
)
CLUSTER BY (
    SALE_DATE
);

For an existing table:

ALTER TABLE SALES
CLUSTER BY (
    SALE_DATE,
    CUSTOMER_ID
);

Clustering can improve micro-partition pruning for appropriate large analytical workloads.

29. AWS S3 Event-Driven Snowflake Architecture

A production event-driven architecture can look like this:

Source Application
        |
        v
Amazon S3
        |
        v
S3 ObjectCreated Event
        |
        v
Amazon SNS
        |
        v
Snowflake Notification Queue
        |
        v
Snowpipe
        |
        v
RAW_EVENTS
        |
        v
STREAM
        |
        v
TASK
        |
        v
MERGE / Transformation
        |
        v
CURATED_DATA
        |
        +----------------+
        |                |
        v                v
       BI               ML

30. Key Snowflake Commands to Remember

Environment

USE ROLE ACCOUNTADMIN;

USE WAREHOUSE COMPUTE_WH;

USE DATABASE MYDB;

USE SCHEMA MYDB.MYSCHEMA;

Metadata

SHOW DATABASES;

SHOW SCHEMAS;

SHOW TABLES;

SHOW STAGES;

SHOW FILE FORMATS;

SHOW PIPES;

SHOW TASKS;

Data Loading

LIST @MY_STAGE;

COPY INTO MY_TABLE
FROM @MY_STAGE;

Task Control

ALTER TASK MY_TASK RESUME;

ALTER TASK MY_TASK SUSPEND;

EXECUTE TASK MY_TASK;

Recovery

UNDROP TABLE MY_TABLE;

UNDROP SCHEMA MY_SCHEMA;

UNDROP DATABASE MY_DATABASE;

Cloning

CREATE DATABASE TEST_DB
CLONE PROD_DB;

31. Snowflake Data Engineering Architecture Summary

The complete architecture can be summarized as:

AWS S3
   |
   v
Storage Integration
   |
   v
External Stage
   |
   v
Snowpipe
   |
   v
Raw Table
   |
   v
Stream
   |
   v
Task
   |
   v
MERGE
   |
   v
Curated Table
   |
   +---------------------+
   |                     |
   v                     v
Analytics               ML

Operational protection is provided by:

Time Travel
     +
UNDROP
     +
Zero-Copy Clone
     +
Fail-safe

Conclusion

Snowflake becomes much easier to understand once its features are viewed as parts of one data engineering workflow rather than independent SQL commands.

Amazon S3 provides scalable object storage. Storage integrations provide secure AWS access. External stages expose files to Snowflake. COPY INTO handles bulk ingestion, while Snowpipe automates continuous loading.

Streams track changes, Tasks automate processing, and MERGE enables incremental transformation.

Finally, Time Travel, UNDROP and zero-copy cloning provide powerful data protection and development capabilities.

If you are preparing for a Snowflake Data Engineer, Cloud Data Engineer or AWS Data Engineer role, the most important concepts to master are:

Virtual Warehouses
Databases and Schemas
Tables and Views
File Formats
Internal and External Stages
AWS Storage Integrations
COPY INTO
Snowpipe
Streams
Tasks
MERGE
Time Travel
UNDROP
Zero-Copy Cloning
Micro-Partitions
Clustering

Mastering these concepts provides a strong foundation for designing production-grade Snowflake data pipelines on AWS.