Snowpipe (Continuous Data Loading)

Snowflake · Views, Pipelines & Automation

Theory

Snowpipe loads files continuously as they arrive in a stage. A pipe is an object that wraps one COPY INTO statement. With AUTO_INGEST = TRUE, your cloud storage sends an event notification when a new file lands (S3 events through SQS, Azure Event Grid, or Google Pub/Sub), Snowpipe queues the file and loads it, usually within minutes. Pipes can also be triggered by calling the Snowpipe REST API.

Snowpipe runs on Snowflake-managed serverless compute, so no warehouse has to stay running. Like COPY, it keeps load history so the same file is not loaded twice, and its default ON_ERROR is SKIP_FILE.

Why we use it

Running COPY on a schedule means data waits until the next run and a warehouse must wake up each time. Snowpipe loads each file shortly after it arrives, with no warehouse to manage, and is billed a fixed number of credits per GB loaded.

When to use it

Use Snowpipe for files arriving continuously in small batches throughout the day. Use scheduled COPY for large daily or hourly batches. For row-level streaming from apps or Kafka, look at Snowpipe Streaming instead of files.

Event-Driven Loading

Instead of running COPY INTO manually or on a schedule, Snowpipe listens for a cloud storage event notification the moment a new file lands in a stage.

Serverless Compute

Loads run on Snowflake-managed serverless compute within seconds to minutes, so no warehouse has to stay running. Snowpipe is billed as a fixed number of credits per GB loaded, not by warehouse time or the number of files.

Where It Fits in a Pipeline

The standard building block for near-real-time ingestion — pair it with Streams and Tasks downstream to transform data as soon as it lands.

How it works

  1. New file lands in the bucket
  2. Cloud storage sends an event notification
  3. Snowpipe queues the file for its pipe
  4. Serverless compute runs the pipe's COPY INTO
  5. Did the file contain errors? Yes: Skip the file (default) and record the error. No: Load the rows into the table.
  6. Check SYSTEM$PIPE_STATUS and COPY_HISTORY

Example

CREATE OR REPLACE TABLE streaming_orders (
  order_data VARIANT, file_name VARCHAR(500) DEFAULT METADATA$FILENAME);

CREATE OR REPLACE PIPE orders_pipe AUTO_INGEST = TRUE AS
COPY INTO streaming_orders (order_data)
FROM @my_s3_stage/orders/ FILE_FORMAT = (TYPE = 'JSON');

SHOW PIPES LIKE 'orders_pipe';  -- use notification_channel for S3 events
SELECT SYSTEM$PIPE_STATUS('orders_pipe');
ALTER PIPE orders_pipe REFRESH;

Key points

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic