Skip to main content

🌨️ 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?​

FeatureBenefit
πŸ” Continuous IngestionReacts automatically to new files in storage
πŸ’° ServerlessSnowflake handles scaling & billing per file load
⚑ Near Real-TimeLoads data within seconds of file arrival
πŸ“¦ Format-AgnosticSupports CSV, JSON, Parquet, Avro, ORC, and XML

πŸ› οΈ Prerequisites​

Before setting up Snowpipe:

  1. Target table in Snowflake.
  2. External stage pointing to your cloud files.
  3. File format matching your data (CSV, JSON, etc.).
  4. Pipe definition with COPY INTO logic.
  5. 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_ARN
  • STORAGE_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 = TRUE listens to notifications.

  • βœ… PATTERN ensures only matching files (e.g., emp_01.csv) are loaded.


πŸ” What Happens When a File is Uploaded?​

  1. You upload emp_01.csv to S3 at s3://secure-load-bucket-snowpipe/
  2. S3 emits a PUT event
  3. SNS sends this to SQS queue
  4. Snowflake listens to SQS and automatically triggers the pipe
  5. 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 namesSnowpipe 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 componentsApply the principle of least privilege to IAM roles, S3 buckets, SNS, and SQS.
Use PATTERN in PIPE definitionEnsures that only files matching specific naming formats (e.g., emp_*.csv) are ingested.
Avoid thousands of tiny filesSnowpipe performs better with fewer, larger files than many small ones.
Define explicit FILE_FORMATAvoids ambiguity in parsing and ensures schema consistency.
Disable AUTO_INGEST in dev/testPrevents 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 IntegrationSecurely allows Snowflake to access your S3 bucket using IAM Role
StageActs as a pointer to your external cloud storage location
File FormatDefines how to read data (e.g., CSV, JSON, Parquet, etc.)
PipeAutomates data loading via COPY INTO logic
SNS/SQS SetupEnables automatic triggering when new files arrive in S3
Monitoring ToolsCOPY_HISTORY, PIPE_USAGE_HISTORY, and SYSTEM$PIPE_STATUS()

🧠 Key Snowpipe Insights​

🏷️ FeatureπŸ’‘ Description
Ingestion ModeAutomatic via AWS SNS + SQS, or manually using SQL trigger
Triggering MechanismS3 PUT event β†’ SNS β†’ SQS β†’ Snowpipe monitors queue and starts ingest
Manual Trigger OptionCALL SYSTEM$PIPE_FORCE_RESUME('pipe_name') for testing or manual runs
Supported FormatsCSV, JSON, Parquet, Avro, ORC, XML
Best Use CaseContinuous, near real-time ingestion for data lakes or event pipelines