Snowpipe and Streams: Real-Time Data Loading into Snowflake

Modern enterprise analytics can no longer afford the multi-hour latency of legacy batch architectures. Whether processing payment transactions, IoT telemetry, or clickstream events, organizations rely on rapid insights to trigger automated operational workflows. Within the modern Snowflake data warehouse ecosystem, achieving sub-minute data readiness requires an event-driven architecture rather than scheduled bulk loads.

By combining Snowpipe for serverless ingestion with Snowflake Streams for continuous Change Data Capture (CDC), data engineering teams can build resilient, near real-time pipelines. At Izan Technologies, we design and deploy these low-latency streaming patterns across cloud providers, ensuring clients maintain full ownership of their data infrastructure and IP while scaling workloads efficiently.

The Ingestion Layer: Event-Driven Snowpipe

Snowpipe is Snowflake’s serverless data ingestion service. Instead of requiring an active, customer-managed virtual warehouse to run periodic COPY INTO commands, Snowpipe leverages serverless compute managed by Snowflake, scaling automatically up or down based on the volume of incoming files.

How Snowpipe Works Under the Hood

Classic Snowpipe operates on an auto-ingest model via cloud storage event notifications:

1. Storage Event Generation: Upstream applications land micro-batched data (typically in Parquet, JSON, or Avro formats) into cloud object storage, such as Amazon S3, Azure Blob Storage, or Google Cloud Storage.
2. Notification Integration: The object storage service emits an event notification (AWS SQS, Azure Event Grid, or GCP Pub/Sub) informing Snowflake that a new file payload is available.
3. The Pipe Object: A Snowflake PIPE wrapper executes a defined COPY INTO <target_table> statement against the external stage as notifications arrive.
4. Serverless Execution: Snowflake manages the underlying compute resources, charging only for the execution time required to load the files, calculated down to the second.

To optimize Snowpipe performance and spend, file sizing is critical. Best practices dictate aiming for file sizes between 100 MB and 250 MB compressed. Ingesting millions of files under 1 MB causes excessive metadata overhead and drives up costs, whereas multi-gigabyte files eliminate the parallelism that makes Snowpipe fast.

The Transformation Layer: Change Data Capture with Snowflake Streams

Once data lands in a raw ingestion table, downstream systems need to process only the records that have arrived or changed since the last transformation cycle. This is where Snowflake Streams become essential.

A stream is an internal change tracking mechanism applied to a source object (standard table, external table, or view). A stream does not store data itself; instead, it creates an offset point that tracks the changes—inserts, updates, and deletes—made to the source table through Snowflake’s Time Travel engine.

Stream Types and Metadata Columns

Snowflake provides three distinct types of streams depending on your ingestion patterns:

• Standard Streams: Tracks all DML actions (INSERT, UPDATE, DELETE) on the source table.
• Append-Only Streams: Tracks only INSERT statements. This is the optimal configuration for raw landing tables populated by Snowpipe, as append-only streams bypass the overhead of tracking row mutations.
• Insert-Only Streams: Specialized for external tables, capturing new files registered in external storage.

When querying a stream, Snowflake injects three critical metadata columns alongside the table's native schema:

• METADATA$ACTION: Identifies the DML action (INSERT or DELETE).
• METADATA$ISUPDATE: A boolean indicating whether the record was part of an UPDATE operation (represented as a delete followed by an insert).
• METADATA$ROW_ID: A unique, immutable row identifier used to track specific records across operations.

Architectural Pattern: Unifying Snowpipe and Streams

Unifying Snowpipe and streams allows you to implement a robust, automated medallion architecture (Raw $\rightarrow$ Silver $\rightarrow$ Gold) within your Snowflake data warehouse without orchestrating complex external DAGs in Airflow or Prefect.

[Cloud Storage] 
       │ (Auto-Ingest via SQS / Event Grid)
       ▼
   [Snowpipe]
       │ (Serverless Load)
       ▼
[Raw Landing Table] ◄─── [Append-Only Stream]
                               │
                               ▼
                        [Snowflake Task]
                   (WHEN SYSTEM$STREAM_HAS_DATA)
                               │ (MERGE / ELT)
                               ▼
                    [Silver/Cleaned Tables]

1. Land Raw Payloads via Snowpipe

Configure Snowpipe to drop incoming records directly into an append-only, variant-heavy landing table:

CREATE OR REPLACE PIPE raw_telemetry_pipe
AUTO_INGEST = true
AS
COPY INTO raw_telemetry_landing (payload, metadata_filename, ingestion_timestamp)
FROM (
  SELECT $1, METADATA$FILENAME, CURRENT_TIMESTAMP()
  FROM @telemetry_stage
);

2. Capture Deltas with an Append-Only Stream

Initialize an append-only stream on top of the landing table. This stream maintains an offset pointing to the exact point in the transaction history where downstream consumers last processed records:

CREATE OR REPLACE STREAM raw_telemetry_stream 
ON TABLE raw_telemetry_landing
APPEND_ONLY = true;

3. Process Incrementally Using Tasks

Pair the stream with a Snowflake Task running on a dynamic schedule. By applying the conditional check SYSTEM$STREAM_HAS_DATA('raw_telemetry_stream'), the task consumes zero warehouse credits unless unread delta records exist:

CREATE OR REPLACE TASK process_telemetry_task
WAREHOUSE = compute_wh
SCHEDULE = '1 MINUTE'
WHEN
  SYSTEM$STREAM_HAS_DATA('raw_telemetry_stream')
AS
MERGE INTO silver_telemetry target
USING (
  SELECT 
    payload:device_id::STRING AS device_id,
    payload:temperature::FLOAT AS temperature,
    payload:recorded_at::TIMESTAMP AS recorded_at
  FROM raw_telemetry_stream
  WHERE METADATA$ACTION = 'INSERT'
) source
ON target.device_id = source.device_id 
AND target.recorded_at = source.recorded_at
WHEN NOT MATCHED THEN INSERT (device_id, temperature, recorded_at)
VALUES (source.device_id, source.temperature, source.recorded_at);

When the MERGE statement succeeds within the task transaction, the stream offset advances automatically to the transaction's commit time. If the downstream pipeline fails, the stream offset does not advance, preventing data loss.

Choosing the Right Ingestion Pattern

Snowflake offers multiple ingestion mechanisms. The table below outlines how standard batch loads, classic Snowpipe, and the newer Snowpipe Streaming API compare:

While the Snowpipe Streaming API bypasses intermediate object storage by streaming rows directly to Snowflake internal buffers, classic Snowpipe paired with streams remains the industry standard for architectures that require raw audit files in cloud storage.

Operational Best Practices

When deploying continuous pipelines using Snowpipe and streams in production, implement the following safeguards:

• Guard Against Stream Stale State: A stream becomes stale when the data retention period (DATA_RETENTION_TIME_IN_DAYS) of its underlying table lapses before the stream's offset is advanced. If a pipeline breaks for longer than your Time Travel window, you must rebuild the stream. Set raw landing retention windows to at least 7–14 days.
• Avoid Multi-Stream Contention: If multiple downstream consumers process the same source table, create dedicated streams for each consumer. Advancing an offset in one stream does not impact others, decoupling independent transformation logic.
• Monitor Ingestion Queue Load: Track Snowpipe queue depths using the VALIDATE_PIPE_LOAD and PIPE_USAGE_HISTORY functions in Snowflake’s ACCOUNT_USAGE schema to spot latency anomalies before SLAs are breached.

How Izan Technologies Helps

Building high-throughput, cost-effective pipelines in Snowflake requires careful consideration of file sizes, warehouse consumption, and CDC dependencies. Izan Technologies architects and deploys custom, end-to-end cloud platforms across AWS, Azure, Databricks, and Snowflake.

Unlike vendors who lock clients into proprietary tooling, Izan Technologies guarantees that our clients retain 100% ownership of all IP, pipeline code, and infrastructure we deliver. We help teams eliminate technical debt, optimize platform spend, and build streaming architectures tailored to their operational goals.

To evaluate your architecture or accelerate your Snowflake rollout:

• Try our Smart AI DataStudio
• Or talk to an expert to discuss your continuous data integration roadmap.