8.2 Snowpipe Continuous Automated Ingestion

8.2 Snowpipe Continuous Automated Ingestion

Modern enterprise data architectures demand near-real-time ingestion pipelines that process data continuously as it lands in cloud object storage, rather than waiting for scheduled nightly batches. Snowpipe is Snowflake's automated, serverless continuous data ingestion service. Designed to load micro-batches of files within minutes of arrival, Snowpipe eliminates the architectural overhead of sizing, provisioning, and auto-suspending customer-managed virtual warehouses for ingestion workloads.

Snowpipe Architecture & The Serverless Compute Model

Traditional bulk loading via COPY INTO <table> requires an active virtual warehouse. If files land randomly throughout the day (e.g., every 90 seconds), running a customer warehouse continuously incurs substantial idle compute costs, while aggressive auto-suspend/auto-resume settings introduce spin-up latency.

Snowpipe solves this by utilizing Snowflake-managed serverless compute pools:

• No Customer Warehouse Required: A pipe object does not reference a virtual warehouse. Snowflake dynamically provisions, scales, and manages compute resources behind the scenes based on incoming file queue depth.
• Credit-per-GB Billing: Snowpipe is now billed at a fixed credit amount per GB loaded, in every edition. Text files (CSV, JSON, XML) are measured by uncompressed size; binary formats (Parquet, Avro, ORC) by observed size. This replaced the former model of per-second compute plus a per-1,000-files charge, so tiny files no longer carry a separate file fee — but they still add latency and overhead.
• Latency Profile: Snowpipe loads micro-batches shortly after files arrive (typically within about a minute or two); it is not a sub-second mechanism.

The Pipe Object Definition

A Pipe is a first-class schema-level securable object that wraps a specific COPY INTO <table> statement:

CREATE OR REPLACE PIPE raw_lake.telemetry.pipe_iot_sensor_readings
  AUTO_INGEST = TRUE
  AWS_SNS_TOPIC = 'arn:aws:sns:us-east-1:123456789012:iot-file-landing-topic'
  COMMENT = 'Continuous micro-batch ingestion for IoT gateway sensor logs'
AS
COPY INTO raw_lake.telemetry.fct_sensor_readings (
    sensor_id,
    reading_timestamp,
    metric_value,
    status_code,
    ingested_at
)
FROM (
    SELECT 
        $1:device_id::VARCHAR(32),
        $1:timestamp::TIMESTAMP_NTZ,
        $1:reading::DOUBLE,
        $1:status::VARCHAR(16),
        CURRENT_TIMESTAMP()
    FROM @raw_lake.stages.s3_iot_landing
)
FILE_FORMAT = (TYPE = 'JSON');

Architect Exam Constraint: A pipe's COPY INTO <table> supports all copy options except FILES, ON_ERROR = ABORT_STATEMENT, SIZE_LIMIT, PURGE, FORCE, RETURN_FAILED_ONLY, and VALIDATION_MODE. Transformations are limited to a simple SELECT (reordering, casting, expressions) — filtering with WHERE is not supported. A pipe definition cannot be altered; to change the COPY statement you recreate the pipe. Snowpipe's default ON_ERROR is SKIP_FILE.

Cloud Event-Driven Auto-Ingest Architecture

Snowpipe's primary ingestion mechanism is Auto-Ingest, which leverages cloud-native event messaging services to notify Snowflake the moment a new object is created in cloud storage.

+-----------------------------------------------------------------------------------------+
|                         CLOUD-NATIVE AUTO-INGEST ARCHITECTURE                           |
+-----------------------------------------------------------------------------------------+
| 1. File Upload: Upstream producer drops 'data_2026.parquet' into Cloud Object Bucket.   |
| 2. Cloud Event: Bucket triggers event notification to cloud messaging broker.           |
|    • AWS: S3 Event -> SQS Queue (Snowflake-managed) or SNS Topic                        |
|    • Azure: Blob Created Event -> Azure Event Grid -> Storage Queue                     |
|    • GCP: GCS Object Finalize -> Google Pub/Sub Topic -> Subscription                  |
| 3. Notification Integration: Snowflake pulls event notification via secure queue.       |
| 4. Serverless Ingestion: Snowpipe queues file, parses records, writes micro-partitions.  |
+-----------------------------------------------------------------------------------------+

Cross-Cloud Messaging Integration Details

1. Amazon Web Services (AWS):

   • Direct SQS Pattern: When AUTO_INGEST = TRUE is created on AWS without an SNS topic, Snowflake generates a dedicated Amazon SQS queue ARN belonging to Snowflake's AWS account (SHOW PIPES reveals notification_channel). The customer configures their S3 bucket event notification to deliver ObjectCreated events directly to this SQS ARN.
   • SNS Fan-Out Pattern: If multiple subscribers (e.g., Snowflake and an external archival service) must receive S3 bucket notifications, the customer routes S3 events to an Amazon SNS topic and provides the AWS_SNS_TOPIC ARN in the pipe definition.

2. Microsoft Azure:

   • Azure Blob / ADLS Gen2 emits storage events to Azure Event Grid, which routes them to an Azure Storage Queue.
   • Snowflake reads the queue through a notification integration created with TYPE = QUEUE, NOTIFICATION_PROVIDER = AZURE_STORAGE_QUEUE, AZURE_STORAGE_QUEUE_PRIMARY_URI, and AZURE_TENANT_ID.
   • After admin consent, the Snowflake application's service principal is granted Storage Queue Data Contributor on the queue; the pipe references the integration with INTEGRATION = '<name>'.

3. Google Cloud Platform (GCP):

   • Google Cloud Storage (GCS) triggers a notification on object creation targeting a GCP Pub/Sub Topic.
   • The topic delivers messages to a Pub/Sub Subscription.
   • Snowflake establishes a NOTIFICATION INTEGRATION referencing the subscription. Snowflake's service account is granted roles/pubsub.subscriber on GCP.

Programmatic REST API Ingestion

While event-driven Auto-Ingest is preferred for cloud stages, architectures frequently require loading from Internal Stages, on-premises data centers, or environments where enterprise cloud policies prohibit external event notifications. In these scenarios, Snowpipe provides a REST API interface.

REST API Workflow

1. Staging: Client applications or edge agents upload data files directly to a Snowflake internal or external stage using the PUT command or cloud SDKs.
2. Notification Dispatch: The client application issues an HTTP POST request to the Snowpipe endpoint:

   POST /v1/data/pipes/my_db.raw_schema.my_pipe/insertFiles
   Host: <orgname>-<accountname>.snowflakecomputing.com
   Authorization: Bearer <JWT_TOKEN>
   Content-Type: application/json
   
   {
     "files": [
       {"path": "2026/09/sensor_batch_101.json.gz"},
       {"path": "2026/09/sensor_batch_102.json.gz"}
     ]
   }
   

3. Asynchronous Scheduling: Snowflake verifies the JWT token, confirms file existence on stage, and enqueues the files for serverless processing. The API responds with an immediate HTTP 200 containing a submission response code.

Authentication via Asymmetric Key Pairs

The Snowpipe REST API does not support password-based or interactive MFA authentication. It mandates 2048-bit RSA Key-Pair Authentication:

• The client application holds an encrypted private RSA key.
• The corresponding public RSA key is registered against the Snowflake service user (ALTER USER svc_ingest SET RSA_PUBLIC_KEY = '...').
• The client generates a short-lived JSON Web Token (JWT) signed with SHA-256 (RS256) and passes it in the Authorization: Bearer header.

Deduplication Lifecycle: 14 Days vs. 64 Days

A critical distinction on the SnowPro Architect exam is the difference in load history retention between bulk COPY INTO and Snowpipe.

The 14-Day Deduplication Trap

Snowpipe maintains a sliding 14-day deduplication catalog for each pipe. If a file named events_20260901.json.gz with identical checksum is placed in the stage within 14 days of its initial ingestion, Snowpipe skips it. However, if the file lands on Day 15, Snowpipe treats it as a new file and loads it again, resulting in duplicate records in the target table.

Architectural Recommendation: Enterprise data architects must enforce deterministic directory partitioning (e.g., /YYYY/MM/DD/HH/) and configure cloud bucket lifecycle policies to archive or delete files once they move past the 14-day window.

Pipe Management, Recovery & Troubleshooting

In enterprise operations, cloud event brokers may experience temporary outages, or network policies may block notification traffic. Architects must know how to inspect pipe health, reprocess missing files, and validate parsing errors.

1. Inspecting Operational Status: SYSTEM$PIPE_STATUS

The SYSTEM$PIPE_STATUS('<pipe_name>') scalar function returns a JSON document detailing the runtime condition of the pipe:

SELECT SYSTEM$PIPE_STATUS('raw_lake.telemetry.pipe_iot_sensor_readings');

{
  "executionState": "RUNNING",
  "pendingFileCount": 14,
  "lastReceivedMessageTimestamp": "2026-09-23T01:15:30.450Z",
  "lastForwardedMessageTimestamp": "2026-09-23T01:15:32.100Z",
  "notificationChannelName": "arn:aws:sqs:us-east-1:123456789012:sf-snowpipe-A1B2C3D4",
  "lastErrorRecordTimestamp": null
}

• executionState: Can be RUNNING, PAUSED, or STOPPED_CLONED.
• pendingFileCount: Number of files currently queued in the serverless processing backlog. A persistently high count indicates ingestion delays or malformed file storms.

2. Reprocessing Missed Files: ALTER PIPE ... REFRESH

If cloud notification messages were lost during an S3/Event Grid outage, the files remain in cloud storage but were never loaded by Snowpipe. An architect can force Snowpipe to inspect the stage directory and queue unrecorded files using ALTER PIPE ... REFRESH:

-- Refresh only files staged in the last 4 hours under a specific prefix
ALTER PIPE raw_lake.telemetry.pipe_iot_sensor_readings REFRESH 
  PREFIX = '2026/09/23/'
  MODIFIED_AFTER = '2026-09-23T00:00:00Z';

CRITICAL WARNING: ALTER PIPE ... REFRESH can only queue files staged within the last 7 days. It checks both the pipe's load history and the table's COPY load history, then queues any file not already loaded. Without PREFIX or MODIFIED_AFTER, that can include old test files still sitting in the stage. Snowflake intends REFRESH for short-term recovery, not routine use.

3. Auditing Ingestion Failures: VALIDATE_PIPE_LOAD

To identify records rejected by Snowpipe due to syntax or conversion errors, use the VALIDATE_PIPE_LOAD table function:

-- Retrieve all parsing errors encountered by the pipe over the past 6 hours
SELECT *
FROM TABLE(VALIDATE_PIPE_LOAD(
  PIPE_NAME => 'raw_lake.telemetry.pipe_iot_sensor_readings',
  START_TIME => DATEADD('hour', -6, CURRENT_TIMESTAMP())
));

4. Zero-Copy Cloning Behavior: STOPPED_CLONED

When a database or schema containing an auto-ingest pipe on an external stage is cloned (CREATE SCHEMA dev_lake CLONE raw_lake), the cloned pipe's execution state is STOPPED_CLONED: it ignores new notifications until it is explicitly resumed. (Pipes with AUTO_INGEST = FALSE are cloned paused, and pipes on internal stages are not cloned.) Before resuming, confirm the pipe's COPY does not use a fully qualified production table, or it will load duplicate data into production.