Study guide
8.2 Snowpipe Continuous Automated Ingestion
SnowPro Advanced: Architect ARA-C01
Track your progress as you read
Finished sections get a check mark.
1 Introduction: The ARA-C01 Exam
About the SnowPro Advanced: Architect (ARA-C01) Exam
2 Chapter 1: Account Strategy & Organization Architecture
1.1 Multi-Account Strategy & Snowflake Organizations 1.2 Snowflake Parameter Hierarchy & Configuration 1.3 Database and Schema Design Architecture
3 Chapter 2: Identity, Access Control & Storage Integrations
2.1 Role-Based Access Control Architecture & Hierarchy 2.2 Secondary Roles, Object Ownership & Delegated Administration 2.3 Cloud Storage Integrations & IAM Trust Policies 2.4 Identity Federation, SSO, SCIM & Authentication Policies
4 Chapter 3: Governance, Data Privacy, Network Security & Compliance
3.1 Column-Level Masking & Row Access Policies 3.2 Object Tagging, Tag-Based Masking & Audit Governance 3.3 Encryption, Key Management & Tri-Secret Secure 3.4 Aggregation Policies, Projection Policies & External Tokenization 3.5 Network Policies, Network Rules, External Access & Private Connectivity 3.6 Snowflake Editions & Compliance Architecture (PCI DSS, PII & PHI)
5 Chapter 4: Data Modeling in Snowflake
4.1 Dimensional Modeling: Star vs. Snowflake Schemas 4.2 Data Vault 2.0 Architecture in Snowflake 4.3 Relational Constraints & Query Optimizer Integration
6 Chapter 5: Data Sharing & Collaboration Solutions
5.1 Direct Data Sharing Architecture & Security 5.2 Managed Reader Accounts for External Consumers 5.3 Snowflake Marketplace, Data Exchange & Clean Rooms
7 Chapter 6: Multi-Cloud, Multi-Region & Business Continuity
6.1 Database & Account Replication Mechanics 6.2 Disaster Recovery Architecture & Client Redirect 6.3 Cross-Cloud Auto-Fulfillment & Listing Replication 6.4 Data Protection: Time Travel, Fail-safe & Storage Costs
8 Chapter 7: Environments, Lakehouse, DevOps & AI/ML Applications
7.1 Multi-Tier Environment Strategy & Zero-Copy Cloning 7.2 Data Lakehouse Architecture & Apache Iceberg 7.3 DataOps, CI/CD & Snowflake Git Integration 7.4 AI/ML Pipelines & Applications: Cortex AI, ML Functions, Snowpark Container Services, Streamlit & Native Apps
9 Chapter 8: Data Ingestion Architecture
8.1 Bulk Ingestion Architecture: COPY INTO Optimization 8.2 Snowpipe Continuous Automated Ingestion 8.3 Snowpipe Streaming & Real-Time Ingestion 8.4 Data Unloading Architecture & Cloud Export Patterns 8.5 Schema Detection, Schema Evolution & Source-Change Architecture
10 Chapter 9: Continuous Pipelines & Stream Transformation
9.1 Change Data Capture (CDC) with Snowflake Streams 9.2 Task Orchestration, DAGs & Serverless Scheduling 9.3 Declarative Data Pipelines with Dynamic Tables 9.4 Views, Materialized Views & Transformation Table Design
11 Chapter 10: Semi-Structured Data & Ecosystem Tooling
10.1 Semi-Structured Data: VARIANT, OBJECT & ARRAY Processing 10.2 Snowflake Ecosystem Connectors, Drivers & SQL API 10.3 Snowpark, User-Defined Functions & Stored Procedures
12 Chapter 11: Virtual Warehouse Architecture & Concurrency
11.1 Virtual Warehouse Architecture, Sizing & Credit Economics 11.2 Multi-Cluster Warehouses & Concurrency Scaling Policies 11.3 Warehouse Types & Query Acceleration Service
13 Chapter 12: Query Profiling & Performance Diagnostics
12.1 Query Profile Deep-Dive & Bottleneck Analysis 12.2 Memory Spilling, Warehouse Queuing & Timeout Governance 12.3 Three-Tier Caching Architecture & Result Reuse
14 Chapter 13: Storage Optimization, Cost Governance & Observability
13.1 Micro-Partitioning & Automatic Clustering 13.2 Search Optimization Service (SOS) & Point Lookup Acceleration 13.3 Resource Monitors, Financial Governance & Usage Observability 13.4 Warehouse Monitoring, Alerts, Event Tables & Performance Explorer
8.2 Snowpipe Continuous Automated Ingestion
Key Takeaways Explain This Topic AI
Snowpipe loads files continuously on Snowflake-managed compute as they land in a stage, triggered by cloud event notifications (auto-ingest) or by calls to the Snowpipe REST API, with no customer warehouse.
Auto-ingest utilizes cloud-native asynchronous messaging integrations (AWS SQS/SNS, Azure Event Grid, Google Cloud Pub/Sub) governed by NOTIFICATION INTEGRATION objects.
The Snowpipe REST API endpoint (insertFiles) enables programmatic, client-driven file submission secured via asymmetric 2048-bit RSA key-pair authentication.
Snowpipe keeps load history in pipe metadata for 14 days (versus 64 days for bulk COPY); ALTER PIPE ... REFRESH can queue only files staged within the last 7 days and is meant for short-term recovery.
Operational health and pipeline latency are monitored via SYSTEM$PIPE_STATUS, parsing failures are audited with VALIDATE_PIPE_LOAD, and missing event notifications are recovered using ALTER PIPE ... REFRESH.
Last updated: September 2026
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
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.
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>' .
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
Staging: Client applications or edge agents upload data files directly to a Snowflake internal or external stage using the PUT command or cloud SDKs.
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"}
- ]
- }
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.
- Architectural Attribute Bulk COPY INTO <table> Snowpipe Continuous Ingestion
- Deduplication History Retention 64 Days 14 Days
- Compute Sizing Model Customer-managed Virtual Warehouse Snowflake-managed Serverless Pool
- Invocation Mechanism Scheduled batch script / client SQL Event notification or REST API call
- Billing Basis Per-second active warehouse credits Fixed credits per GB loaded
Load Status Telemetry LOAD_HISTORY (Information Schema & Account Usage) PIPE_USAGE_HISTORY , VALIDATE_PIPE_LOAD
Cloning Behavior Standard object cloning Pipes on external stages clone paused ( STOPPED_CLONED if auto-ingest); pipes on internal stages are not cloned
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.