Event-Based Loading Patterns
Overview
Event-based loading is a reactive ingestion pattern where data is loaded into Snowflake automatically in response to external events — such as a new file landing in cloud storage — rather than on a fixed schedule. This is the foundation of real-time and near-real-time data pipelines.
Event-Driven vs Schedule-Driven Loading
| Aspect | Schedule-Driven | Event-Driven |
|---|---|---|
| Trigger | Cron / fixed interval (e.g., every 15 min) | Cloud event notification (file arrival) |
| Latency | Minutes to hours (depends on schedule) | Seconds to minutes |
| Idle cost | Runs even when no new data exists | Only runs when data arrives |
| Complexity | Simple — just a TASK with a schedule | Moderate — requires cloud event configuration |
| Best for | Batch ETL, daily aggregations | Streaming ingestion, CDC, real-time dashboards |
When to Use Event-Based Loading
- New files arrive unpredictably (not on a fixed schedule)
- Business requires low-latency data availability (< 5 minutes)
- You want to avoid polling and wasted compute on empty runs
- Data sources are external systems pushing files to cloud storage
When to Use Schedule-Based Loading
- Files arrive on a known cadence (daily dumps, hourly exports)
- Latency requirements are relaxed (hours are acceptable)
- Source system doesn't support event notifications
Cloud Event Notification Patterns
AWS S3 → Snowpipe
S3 Bucket → S3 Event Notification → SQS Queue → Snowpipe AUTO_INGEST
- Configure an S3 Event Notification on
s3:ObjectCreated:* - Route events to an SQS queue (Snowflake provides the queue ARN)
- Snowpipe polls the SQS queue and loads new files automatically
Azure Blob Storage → Snowpipe
Blob Container → Event Grid → Storage Queue → Snowpipe AUTO_INGEST
- Create an Azure Event Grid subscription on blob creation events
- Route to an Azure Storage Queue
- Snowpipe monitors the queue and ingests on arrival
Google Cloud Storage → Snowpipe
GCS Bucket → Pub/Sub Notification → Snowpipe AUTO_INGEST
- Enable GCS Pub/Sub notifications on object creation
- Snowpipe subscribes to the Pub/Sub topic
- Files are loaded as they land
Snowpipe AUTO_INGEST Configuration
CREATE OR REPLACE PIPE my_pipe
AUTO_INGEST = TRUE
AS
COPY INTO my_table
FROM @my_external_stage
FILE_FORMAT = (TYPE = 'PARQUET');
Key parameters:
AUTO_INGEST = TRUE— enables event-driven loading- The pipe's notification channel (SQS ARN / Event Grid / Pub/Sub) is used to configure the cloud-side event routing
Event-Based Pipeline Design Patterns
Pattern 1: Simple Event → Load
Cloud Storage Event → Snowpipe → Raw Table
Best for: Landing raw data with minimal transformation.
Pattern 2: Event → Load → Transform (Stream + Task)
Cloud Storage Event → Snowpipe → Raw Table → Stream → Task (MERGE) → Clean Table
Best for: CDC pipelines where raw data needs deduplication or SCD processing.
Pattern 3: Event → Load → Dynamic Table Chain
Cloud Storage Event → Snowpipe → Raw Table → Dynamic Table (Silver) → Dynamic Table (Gold)
Best for: Declarative medallion architecture with automatic refresh.
Monitoring Event-Based Pipelines
-- Check Snowpipe load history
SELECT * FROM TABLE(INFORMATION_SCHEMA.PIPE_USAGE_HISTORY(
DATE_RANGE_START => DATEADD('hour', -24, CURRENT_TIMESTAMP()),
PIPE_NAME => 'MY_PIPE'
));
-- Check for failed loads
SELECT * FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
TABLE_NAME => 'MY_TABLE',
START_TIME => DATEADD('hour', -24, CURRENT_TIMESTAMP())
))
WHERE STATUS = 'LOAD_FAILED';
Best Practices
- Use Storage Integration instead of access keys for secure cloud connectivity
- Set up error notifications — configure Snowpipe error integration to alert on load failures
- Monitor SQS/Event Grid queue depth — growing queues indicate Snowpipe can't keep up
- Use MATCH_BY_COLUMN_NAME for semi-structured data to handle schema evolution
- Combine with Streams for downstream CDC processing after event-based landing