Skip to main content

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​

AspectSchedule-DrivenEvent-Driven
TriggerCron / fixed interval (e.g., every 15 min)Cloud event notification (file arrival)
LatencyMinutes to hours (depends on schedule)Seconds to minutes
Idle costRuns even when no new data existsOnly runs when data arrives
ComplexitySimple — just a TASK with a scheduleModerate — requires cloud event configuration
Best forBatch ETL, daily aggregationsStreaming 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
  1. Configure an S3 Event Notification on s3:ObjectCreated:*
  2. Route events to an SQS queue (Snowflake provides the queue ARN)
  3. Snowpipe polls the SQS queue and loads new files automatically

Azure Blob Storage → Snowpipe​

Blob Container → Event Grid → Storage Queue → Snowpipe AUTO_INGEST
  1. Create an Azure Event Grid subscription on blob creation events
  2. Route to an Azure Storage Queue
  3. Snowpipe monitors the queue and ingests on arrival

Google Cloud Storage → Snowpipe​

GCS Bucket → Pub/Sub Notification → Snowpipe AUTO_INGEST
  1. Enable GCS Pub/Sub notifications on object creation
  2. Snowpipe subscribes to the Pub/Sub topic
  3. 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​

  1. Use Storage Integration instead of access keys for secure cloud connectivity
  2. Set up error notifications — configure Snowpipe error integration to alert on load failures
  3. Monitor SQS/Event Grid queue depth — growing queues indicate Snowpipe can't keep up
  4. Use MATCH_BY_COLUMN_NAME for semi-structured data to handle schema evolution
  5. Combine with Streams for downstream CDC processing after event-based landing