How to Set Up Snowflake Triggers for Real-Time Pipelines

Snowflake can react to data changes. Most teams don't use that capability. They set up a cron job every 15 minutes, run a full table scan, and call it "near real-time."

I get why. Snowflake's trigger mechanisms — streams, tasks, pipes, stages — each solve a different piece of the problem, and the docs don't make it obvious how they fit together. Streams track what changed. Tasks decide when to act. Pipes handle continuous file ingestion. Stages are the landing zone for external data. You need at least two of these working together to get anything useful, and getting the configuration wrong means either missed data or a credit bill that makes your finance team ask questions.

Here's how each mechanism actually works, the SQL to set them up, and the mistakes I've seen teams make with each one.

Streams: tracking what changed

A Snowflake stream is a change-tracking object on a table. It records every INSERT, UPDATE, and DELETE that happens after the stream is created, and it holds those changes until something consumes them. Think of it as a CDC log that Snowflake manages for you.

-- Create a stream on your orders table
CREATE OR REPLACE STREAM orders_stream ON TABLE raw.orders;
 
-- Check what's changed since last consumption
SELECT * FROM orders_stream;
 
-- The stream tracks change metadata automatically
-- METADATA$ACTION: INSERT, DELETE
-- METADATA$ISUPDATE: TRUE if this row is part of an UPDATE
-- METADATA$ROW_ID: unique row identifier

When you query a stream inside a DML transaction (an INSERT, MERGE, etc.), the stream advances — those changes are marked as consumed. This is the critical thing to understand: streams are consumed by reading them in a DML context, not by running a SELECT.

The staleness gotcha. Streams have a retention period tied to your table's DATA_RETENTION_TIME_IN_DAYS. Default is 1 day on Standard edition. If nothing consumes the stream within that window, it goes stale — and a stale stream is permanently broken. You can't recover it. You have to drop it and create a new one, losing any unconsumed changes. Enterprise edition gives you 90 days. Standard edition gives you a ticking clock.

-- Check stream health before it bites you
SELECT
  stream_name,
  stale,
  stale_after
FROM information_schema.streams
WHERE stream_name = 'ORDERS_STREAM';

If stale is true, you're already too late. Set up monitoring on stale_after before that happens.

Tasks: running SQL on a schedule or trigger

A Snowflake task is a scheduled SQL statement. On its own, it's just cron inside Snowflake. But combined with a stream, it becomes event-driven — the task checks whether the stream has data and only runs when there's something to process.

-- Create a task that processes the stream every 5 minutes
CREATE OR REPLACE TASK process_orders_task
  WAREHOUSE = compute_wh
  SCHEDULE = '5 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
AS
  MERGE INTO analytics.orders_summary AS target
  USING (
    SELECT
      DATE_TRUNC('hour', order_date) AS order_hour,
      COUNT(*) AS order_count,
      SUM(amount) AS total_amount
    FROM orders_stream
    WHERE METADATA$ACTION = 'INSERT'
    GROUP BY 1
  ) AS source
  ON target.order_hour = source.order_hour
  WHEN MATCHED THEN
    UPDATE SET
      order_count = target.order_count + source.order_count,
      total_amount = target.total_amount + source.total_amount
  WHEN NOT MATCHED THEN
    INSERT (order_hour, order_count, total_amount)
    VALUES (source.order_hour, source.order_count, source.total_amount);
 
-- Tasks are created in a suspended state. You have to resume them.
ALTER TASK process_orders_task RESUME;

That WHEN SYSTEM$STREAM_HAS_DATA() clause is doing the heavy lifting. The task wakes up every 5 minutes, checks if the stream has unconsumed changes, and only spins up the warehouse if there's work to do. No changes, no compute, no credits burned.

The credit consumption gotcha. Even with the WHEN clause, the task still runs a metadata check every 5 minutes. That check is free. But if your stream always has data — because your source table receives continuous inserts — the task runs every cycle, and your warehouse is up for 5+ minutes at a time (Snowflake's minimum billing increment is 60 seconds, but auto-suspend adds lag). A size XS warehouse running 24/7 is roughly $2/credit * 1 credit/hour * 24 * 30 = ~$1,440/month. Not catastrophic, but not nothing either.

Task trees. Tasks can be chained — a root task triggers child tasks in a DAG-like structure. The root runs on a schedule; children run when their predecessor finishes. This is Snowflake's answer to orchestration, but it's limited: no conditional branching, no retries on individual tasks, and debugging a failed task tree means digging through TASK_HISTORY() output that isn't exactly friendly.

-- Child task that runs after the parent completes
CREATE OR REPLACE TASK notify_downstream
  WAREHOUSE = compute_wh
  AFTER process_orders_task
AS
  CALL system$send_email(
    'order_alerts',
    'data-team@example.com',
    'Orders processed',
    'New orders have been merged into analytics.orders_summary'
  );
 
ALTER TASK notify_downstream RESUME;

Pipes: continuous loading from external stages

Snowpipe is a separate mechanism from streams and tasks. It handles one specific job: continuously loading data files from a stage into a table. Think of it as an auto-ingestion service for files landing in S3, GCS, or Azure Blob.

-- Create an external stage pointing to S3
CREATE OR REPLACE STAGE raw.orders_stage
  URL = 's3://my-bucket/orders/'
  STORAGE_INTEGRATION = my_s3_integration
  FILE_FORMAT = (TYPE = 'PARQUET');
 
-- Create a pipe for continuous ingestion
CREATE OR REPLACE PIPE raw.orders_pipe
  AUTO_INGEST = TRUE
AS
  COPY INTO raw.orders
  FROM @raw.orders_stage
  MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

With AUTO_INGEST = TRUE and an S3 event notification configured (SQS queue), Snowpipe picks up new files within seconds of landing. No scheduling, no polling. Files arrive, rows appear.

The cost model gotcha. Snowpipe uses serverless compute — you don't specify a warehouse. Snowflake bills you per file loaded, at 0.06 credits per second of serverless compute time. For large batches of small files, this adds up fast. One team I worked with was landing individual JSON records as separate files (one event = one file). Their Snowpipe bill was 10x what it would have cost to batch those records into hourly files and use a scheduled task instead. The rule of thumb: Snowpipe is economical when files are reasonably sized (10MB+) and arrive at moderate frequency. Thousands of tiny files per hour is a billing trap.

Pipe + stream combo. The real power shows up when you chain these together. Snowpipe loads raw files into a landing table. A stream on that table captures new rows. A task consuming the stream transforms and loads them into an analytics table. That's a full real-time pipeline — file lands in S3, data shows up in your dashboard — with zero external orchestration.

When to use which

Not every pipeline needs all four mechanisms. Here's how I think about the decision:

Source is another Snowflake table (internal CDC): Stream + Task. The stream tracks changes, the task processes them on a schedule with a WHEN clause. This is the most common pattern and the one most teams should start with.

Source is files landing in cloud storage: Pipe + Stage. Snowpipe handles ingestion; add a stream + task downstream if you need transformation. Skip the pipe and use a scheduled COPY INTO if files arrive in predictable batches (e.g., daily exports from a vendor) — there's no point paying serverless compute to watch for files that arrive on a known schedule.

Source is an external system pushing data via API: Neither. Snowflake doesn't have an inbound webhook. You need middleware — a Lambda, a small service, something — to receive the event and write to Snowflake. This is where most teams give up on Snowflake-native triggers and reach for an external orchestrator.

You just need a cron job on SQL: Task without a stream. A scheduled task without a WHEN clause is just cron inside Snowflake. Fine for nightly aggregations, weekly reports, or anything where "run at this time regardless" is the right behavior.

Common pitfalls (and how to avoid them)

1. Stream staleness on Standard edition. Already covered above, but worth repeating because I've seen it burn three teams in the last year. If you're on Standard edition, your stream goes stale after 1 day of not being consumed. Set a 12-hour alert on stale_after and treat it as a page.

2. Forgetting to resume tasks. Tasks are created suspended. Every time. You will forget ALTER TASK ... RESUME at least once. Add it to your deployment script or you'll spend an hour wondering why nothing is running.

3. Warehouse auto-suspend vs. task frequency. If your task runs every 1 minute and your warehouse auto-suspends after 5 minutes — congratulations, your warehouse never suspends. Set task frequency to at least 2x your auto-suspend interval, or use a dedicated XS warehouse for task execution so the cost is contained.

4. Snowpipe file tracking. Snowpipe tracks which files it's loaded using internal metadata. If you modify a file in S3 and re-upload it with the same name, Snowpipe ignores it — it's already marked as loaded. You have to either use unique filenames or run ALTER PIPE ... REFRESH manually. This trips up every team that uses mutable file paths.

5. Task ownership and privileges. The role that creates a task owns it. If your dbt service role creates a task but your monitoring role needs to see its history, you need explicit grants. Snowflake's RBAC model interacts with tasks in ways that aren't obvious until something fails silently because the wrong role is checking TASK_HISTORY().

Skipping the plumbing with Fastero

All of the above works. It's also a lot of moving parts for what amounts to "when this data changes, do something." Streams, tasks, pipes, stages, warehouse sizing, staleness monitoring, RBAC grants — each piece is individually reasonable, but the combined surface area is real.

Fastero's Snowflake triggers abstract this away. You connect your Snowflake account, point a trigger at a table or stream, and define what should happen when data changes — run a query, kick off a workflow, send a Slack notification. Fastero manages the stream lifecycle, handles consumption, and monitors for staleness so you don't have to build that infrastructure yourself.

The same trigger model works across sources. A Snowflake integration trigger and a Kafka consumer group trigger use the same interface — pick the source, define the condition, attach the action. If your pipelines span multiple systems (most do), you're not learning four different trigger models for four different sources.

For teams that want the Snowflake-native approach and have the bandwidth to manage the pieces, the SQL above will get you there. For teams that want event-driven pipelines without operating the trigger infrastructure, that's the problem Fastero was built for. Either way, stop defaulting to 15-minute cron jobs when Snowflake has real change tracking — your data is fresher and your warehouse bill is smaller when you only compute on actual changes.

If you're evaluating the orchestration layer more broadly — whether to use Snowflake-native triggers, an external orchestrator, or something event-driven — we compared the tradeoffs in Airflow vs Dagster.

Try Fastero free — connect Snowflake, set a trigger on any table, and let Fastero handle the stream lifecycle and plumbing. No credit card required.