π¨οΈ Loading Data Using Snowpipe in Snowflake
π What is Snowpipe?β
Snowpipe is Snowflakeβs continuous, serverless data ingestion service that allows automated loading of files from cloud storage (like Amazon S3) into Snowflake tables β without manual intervention.
It uses:
- Cloud notifications (S3 β SNS β SQS) for real-time auto-ingestion
- OR manual SQL-based triggers (for testing or scheduled ingestion)
β Why Use Snowpipe?β
| Feature | Benefit |
|---|---|
| π Continuous Ingestion | Reacts automatically to new files in storage |
| π° Serverless | Snowflake handles scaling & billing per file load |
| β‘ Near Real-Time | Loads data within seconds of file arrival |
| π¦ Format-Agnostic | Supports CSV, JSON, Parquet, Avro, ORC, and XML |
π οΈ Prerequisitesβ
Before setting up Snowpipe:
- Target table in Snowflake.
- External stage pointing to your cloud files.
- File format matching your data (CSV, JSON, etc.).
- Pipe definition with
COPY INTOlogic. - Cloud notifications (SNS β SQS) or manual trigger.
π§ Step-by-Step Setup (with CSV Example)β
Below is a real-world tested implementation for loading employee data from S3 via Snowpipe:
πΉ Step 1: Create a Secure Storage Integrationβ
CREATE OR REPLACE STORAGE INTEGRATION s3_int_snowpipe
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = S3
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::248864054957:role/secure-load-bucket-snowpipe-role'
STORAGE_ALLOWED_LOCATIONS = ('s3://secure-load-bucket-snowpipe');
π Then run:
DESC STORAGE INTEGRATION s3_int_snowpipe;
Copy:
STORAGE_AWS_IAM_USER_ARNSTORAGE_AWS_EXTERNAL_ID
π These go into the AWS IAM Trust Policy for the role referenced in the ARN.
πΉ Step 2: Create the File Formatβ
CREATE OR REPLACE FILE FORMAT csv_file_format
TYPE = 'CSV'
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '"';
πΉ Step 3: Create the External Stageβ
CREATE OR REPLACE STAGE ext_s3_stage_snowpipe
URL = 's3://secure-load-bucket-snowpipe/'
STORAGE_INTEGRATION = s3_int_snowpipe
FILE_FORMAT = csv_file_format;
π§ͺ Optional test:
LIST @ext_s3_stage_snowpipe;
Or preview file:
SELECT $1, $2, $3, $4, $5
FROM @ext_s3_stage_snowpipe/emp_01.csv
(FILE_FORMAT => 'csv_file_format');
πΉ Step 4: Create the Target Tableβ
CREATE OR REPLACE TABLE employees_snowpipe_demo (
id INT,
name STRING,
dept STRING,
location STRING,
salary NUMBER(10,2)
);
π Optional cleanup:
TRUNCATE TABLE employees_snowpipe_demo;
πΉ Step 5: Create the Pipe (Snowpipe Definition)β
CREATE OR REPLACE PIPE ext_s3_stage_snowpipe_demo
AUTO_INGEST = TRUE
AS
COPY INTO employees_snowpipe_demo
FROM @ext_s3_stage_snowpipe
FILE_FORMAT = csv_file_format
PATTERN = '.*emp_[0-9]+[.]csv';
-
β
AUTO_INGEST = TRUElistens to notifications. -
β
PATTERNensures only matching files (e.g.,emp_01.csv) are loaded.
π What Happens When a File is Uploaded?β
- You upload
emp_01.csvto S3 ats3://secure-load-bucket-snowpipe/ - S3 emits a PUT event
- SNS sends this to SQS queue
- Snowflake listens to SQS and automatically triggers the pipe
- File gets loaded into
employees_snowpipe_demo
- β Result: Data auto-ingested with zero manual work.
π§ͺ Optional: Manually Load or Test the Pipeβ
Force a manual load:
CALL SYSTEM$PIPE_FORCE_RESUME('ext_s3_stage_snowpipe_demo');
π§Ύ Monitoring and Debuggingβ
π View All Pipesβ
SHOW PIPES;
π Pipe Definitionβ
DESC PIPE ext_s3_stage_snowpipe_demo;
π₯ View File-Level Copy Historyβ
SELECT * FROM TABLE(
INFORMATION_SCHEMA.COPY_HISTORY(
TABLE_NAME => 'EMPLOYEE_DEMO_DB.HR.EMPLOYEES_SNOWPIPE_DEMO',
START_TIME => DATEADD(hours, -1, CURRENT_TIMESTAMP())
)
);
π View Usage Metricsβ
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.PIPE_USAGE_HISTORY
WHERE PIPE_NAME = 'EXT_S3_STAGE_SNOWPIPE_DEMO'
AND START_TIME >= DATEADD(DAY, -1, CURRENT_TIMESTAMP())
ORDER BY START_TIME DESC;
β Best Practices for Snowpipeβ
| β Recommendation | π Why It Matters |
|---|---|
Use compressed files (.gz) | Minimizes storage space, accelerates upload, and speeds up Snowpipe ingestion. |
| Ensure unique file names | Snowpipe maintains a record of loaded filesβduplicates are ignored to avoid reloading. |
Use ON_ERROR = 'CONTINUE' | Allows ingestion to proceed even if a few records are badβcritical for production. |
Monitor using COPY_HISTORY() | Enables detailed tracking and debugging of ingestion activities. |
| Secure all AWS components | Apply the principle of least privilege to IAM roles, S3 buckets, SNS, and SQS. |
Use PATTERN in PIPE definition | Ensures that only files matching specific naming formats (e.g., emp_*.csv) are ingested. |
| Avoid thousands of tiny files | Snowpipe performs better with fewer, larger files than many small ones. |
Define explicit FILE_FORMAT | Avoids ambiguity in parsing and ensures schema consistency. |
Disable AUTO_INGEST in dev/test | Prevents unintentional ingestion during experimentation; use manual triggers instead. |
π§Ή Cleaning Up Resourcesβ
When your Snowpipe setup is no longer needed, clean up the resources to avoid unnecessary charges or clutter:
DROP PIPE ext_s3_stage_snowpipe_demo;
DROP STAGE ext_s3_stage_snowpipe;
DROP FILE FORMAT csv_file_format;
DROP TABLE employees_snowpipe_demo;
DROP STORAGE INTEGRATION s3_int_snowpipe;
π Summary Tableβ
| π§ Component | π§© Purpose |
|---|---|
| Storage Integration | Securely allows Snowflake to access your S3 bucket using IAM Role |
| Stage | Acts as a pointer to your external cloud storage location |
| File Format | Defines how to read data (e.g., CSV, JSON, Parquet, etc.) |
| Pipe | Automates data loading via COPY INTO logic |
| SNS/SQS Setup | Enables automatic triggering when new files arrive in S3 |
| Monitoring Tools | COPY_HISTORY, PIPE_USAGE_HISTORY, and SYSTEM$PIPE_STATUS() |
π§ Key Snowpipe Insightsβ
| π·οΈ Feature | π‘ Description |
|---|---|
| Ingestion Mode | Automatic via AWS SNS + SQS, or manually using SQL trigger |
| Triggering Mechanism | S3 PUT event β SNS β SQS β Snowpipe monitors queue and starts ingest |
| Manual Trigger Option | CALL SYSTEM$PIPE_FORCE_RESUME('pipe_name') for testing or manual runs |
| Supported Formats | CSV, JSON, Parquet, Avro, ORC, XML |
| Best Use Case | Continuous, near real-time ingestion for data lakes or event pipelines |