Programmability: UDFs, Procedures, Streams, Tasks, and Snowpark

The orientation chapter for writing code near Snowflake data without turning the warehouse into an application server.

Up to now you’ve treated Snowflake as a place to put SQL: you write a query, it runs, you get rows. But there’s a second half to the platform that most tutorials skip, where you write code that lives inside the warehouse — functions the query planner can call mid-statement, procedures that run a whole workflow, change-tracking objects that notice when a table moves, schedulers that fire on their own, and a full Python DataFrame API that compiles down to the same SQL engine. This chapter is the map of that territory. It’s an orientation, not a deep dive into any one piece — the goal is that when you later reach for one of these, you know which one, why, and what the sharp edge is.

The unifying idea is bringing code to the data. Instead of pulling rows out to a Python process on your laptop, transforming them, and pushing them back, you push the logic in and let it run next to the storage, on Snowflake’s compute, at Snowflake’s scale. Every tool below is a different shape of that same move.

One warning before we start, because it colors everything: none of this is a license to hide your core transformation logic inside the database. Analytics that matters — the models your dashboards depend on — belongs in version control, in dbt or plain SQL files someone reviews, not buried in a stored procedure nobody remembers writing. Programmability earns its place when the warehouse-native primitive is genuinely the best execution surface: a scalar helper called a billion times, a change-driven pipeline, a scheduled maintenance job. Keep asking “who tests, deploys, observes, and pays for this?” and you’ll use these tools well.

UDFs: functions the planner can call

A user-defined function is a function you can call inside any query, just like UPPER() or SUM(). Snowflake gives you four flavors, and the choice between them is mostly about what language expresses your logic most cleanly.

The simplest is a scalar SQL UDF — a named, reusable SQL expression. Not a procedure, not a loop; a single expression that takes arguments and returns one value. Say the bookshop keeps classifying orders into size tiers in five different queries. Write it once:

CREATE OR REPLACE FUNCTION order_tier(total NUMBER)
  RETURNS STRING
AS
$$
  CASE
    WHEN total >= 100000 THEN 'whale'
    WHEN total >= 10000  THEN 'large'
    WHEN total >= 1000   THEN 'medium'
    ELSE 'small'
  END
$$;

SELECT o_orderkey, order_tier(o_totalprice) AS tier
FROM sales.public.orders
LIMIT 10;

The body between the $$ markers is just an expression — it can even be a subquery that returns a single value. Because it’s SQL, the optimizer can often inline it, so there’s essentially no per-row overhead. This is the workhorse: any time you catch yourself pasting the same CASE or arithmetic into query after query, promote it to a scalar SQL UDF and the logic gets one home.

When the logic is genuinely procedural — string parsing, bit-twiddling, branching that doesn’t fit an expression — reach for a JavaScript UDF. It runs a JS function body per row:

CREATE OR REPLACE FUNCTION clean_title(raw STRING)
  RETURNS STRING
  LANGUAGE JAVASCRIPT
AS
$$
  if (RAW === null) { return null; }
  return RAW.trim().replace(/\s+/g, ' ');
$$;

Note the trap that catches everyone: the argument is declared raw but referenced as RAW inside the body. Snowflake case-folds unquoted identifiers to uppercase, and JavaScript is case-sensitive, so the value shows up under the uppercased name. Declare it lowercase, reach for it uppercase, or double-quote the argument name if you want it verbatim. This bites once and never again.

The one you’ll reach for most in a modern account is the Python UDF, because it opens the door to the Anaconda package library:

CREATE OR REPLACE FUNCTION isbn13_ok(code STRING)
  RETURNS BOOLEAN
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.11'
  HANDLER = 'check'
AS
$$
def check(code):
    if code is None or len(code) != 13 or not code.isdigit():
        return False
    total = sum((1 if i % 2 == 0 else 3) * int(d)
                for i, d in enumerate(code))
    return total % 10 == 0
$$;

SELECT isbn13_ok('9780306406157');   -- TRUE

The shape is worth memorizing: LANGUAGE PYTHON, a RUNTIME_VERSION, and a HANDLER naming the function Snowflake should call. To pull in a library, add PACKAGES = ('numpy','pandas') and Snowflake resolves them from its curated Anaconda channel — no pip, no wheel-building. (Java and Scala UDFs exist too, with the same HANDLER pattern; they matter mostly for reusing existing JVM libraries.)

The fourth flavor returns a table instead of a scalar: a UDTF, or user-defined table function. Where a scalar UDF turns one row into one value, a UDTF turns one row into many rows — the classic use is exploding a packed field into a set. Here’s a SQL UDTF that fans a comma-delimited tag string into rows:

CREATE OR REPLACE FUNCTION split_tags(tags STRING)
  RETURNS TABLE (tag STRING)
AS
$$
  SELECT TRIM(value)
  FROM TABLE(SPLIT_TO_TABLE(tags, ','))
$$;

You call a UDTF inside TABLE(...), and — the part that makes it powerful — you can lateral-join it to a source so it runs once per source row:

SELECT b.book_id, t.tag
FROM (
    SELECT 1 AS book_id, 'sci-fi, classic, hardback' AS tag_list
    UNION ALL
    SELECT 2,            'cookery, gift'
) b,
TABLE(split_tags(b.tag_list)) t;
--  1 | sci-fi
--  1 | classic
--  1 | hardback
--  2 | cookery
--  2 | gift

Every book’s tag string is expanded, and the join stitches each resulting tag back to its book. Python UDTFs go further: their handler is a class with a process() method that yields rows, and an optional end_partition() for logic that needs to see a whole partition at once (running totals, top-N per group). Reach for a UDTF whenever a function’s natural answer is “several rows,” and a Python UDTF specifically when that answer depends on state across a partition.

The quick decision guide: scalar SQL UDF for a reusable expression (cheapest, inlinable); Python UDF when you want a library or real procedural logic per value; JavaScript UDF for self-contained per-row logic without packages; UDTF when the output is a set of rows rather than a single value.

Stored procedures: running a workflow

A UDF computes a value inside a query. A stored procedure does work — it runs a sequence of statements, branches, loops, and DML, and you invoke it with CALL. The mental line: a function answers a question; a procedure performs an action.

Snowflake’s native procedural language is Snowflake Scripting, a BEGIN...END block language layered over SQL. Here’s a maintenance procedure that archives old bookshop orders and reports how many it moved — with a declared variable, a bound parameter, and the row-count built-in:

-- the archive table mirrors the source; create it once
CREATE TABLE IF NOT EXISTS sales.public.orders_archive
  LIKE sales.public.orders;

CREATE OR REPLACE PROCEDURE archive_orders(cutoff DATE)
  RETURNS STRING
  LANGUAGE SQL
AS
$$
DECLARE
  moved INTEGER DEFAULT 0;
BEGIN
  INSERT INTO sales.public.orders_archive
    SELECT * FROM sales.public.orders WHERE o_orderdate < :cutoff;
  moved := SQLROWCOUNT;                       -- rows the last DML touched

  IF (moved = 0) THEN
    RETURN 'Nothing older than ' || cutoff;
  END IF;

  DELETE FROM sales.public.orders WHERE o_orderdate < :cutoff;
  RETURN 'Archived ' || moved || ' orders';
END;
$$;

CALL archive_orders('2024-01-01');

A few Scripting details earn their keep. Local variables are declared in DECLARE (or inline with LET) and read with a bare name but bound into SQL with a leading colon (:cutoff) — mixing those up is the first error everyone hits. SQLROWCOUNT reports the rows the previous DML affected. And the full control-flow kit is here: IF/ELSEIF/ELSE, CASE, FOR, WHILE, and cursor loops with FOR rec IN (SELECT ...) DO.

When you need to build SQL whose shape isn’t known until runtime — a table name from a variable, a dynamically assembled column list — use EXECUTE IMMEDIATE:

CREATE OR REPLACE PROCEDURE refresh_summary(target STRING)
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  EXECUTE IMMEDIATE
    'CREATE OR REPLACE TABLE ' || target || ' AS
       SELECT o_orderpriority, COUNT(*) AS n, SUM(o_totalprice) AS revenue
       FROM sales.public.orders
       GROUP BY o_orderpriority';
  RETURN target || ' rebuilt';
END;
$$;

EXECUTE IMMEDIATE is powerful and dangerous in the usual way — string-built SQL is injectable if any part comes from untrusted input, so keep interpolated values to identifiers you control.

Procedures don’t have to be SQL. A Python (Snowpark) procedure runs a Python handler whose first argument is a live session connected to the very account it’s running in — so you get the full DataFrame API (next section) inside a callable:

CREATE OR REPLACE PROCEDURE refresh_by_nation()
  RETURNS STRING
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.11'
  PACKAGES = ('snowflake-snowpark-python')
  HANDLER = 'run'
AS
$$
def run(session):
    (session.table("sales.public.orders")
            .group_by("o_orderpriority")
            .count()
            .write.mode("overwrite")
            .save_as_table("analytics.public.orders_by_priority"))
    return "refreshed"
$$;

Now the ownership question that quietly decides who can call this safely. By default a procedure runs with owner’s rights: it executes with the privileges of the role that created it, not the role that calls it. That’s how you let an analyst run archive_orders without granting them delete on orders directly — they can invoke the procedure, but they never hold the underlying privilege. The trade-off is that an owner’s-rights procedure can’t read the caller’s session state and is limited in a few operations. When you genuinely need the procedure to act as the caller — respecting their roles, their context — create it EXECUTE AS CALLER. Owner’s rights is the secure default (privilege stays encapsulated); caller’s rights is the deliberate exception when the procedure must inherit the invoker’s access. Choosing wrong is a real security bug, so decide it on purpose.

Streams: change tracking without a trigger

A Stream is Snowflake’s answer to “what changed in this table since I last looked?” — change data capture, built in, no triggers, no timestamps-and-hope. You create a stream on a table, and from then on it exposes the set of rows inserted, updated, or deleted since a moving marker called the offset.

CREATE OR REPLACE STREAM orders_stream ON TABLE sales.public.orders;

Selecting from the stream looks like selecting from the base table, plus three metadata columns that describe each change:

SELECT o_orderkey, o_totalprice,
       METADATA$ACTION, METADATA$ISUPDATE, METADATA$ROW_ID
FROM orders_stream;
  • METADATA$ACTION is INSERT or DELETE — the two primitive operations a change reduces to.
  • METADATA$ISUPDATE is TRUE when that INSERT/DELETE pair is really an update. A standard stream represents an update as a DELETE of the old row plus an INSERT of the new one, both flagged ISUPDATE = TRUE, so you can tell a true delete (one DELETE row, ISUPDATE = FALSE) from an update (a matched pair).
  • METADATA$ROW_ID is a stable identifier for the row across its lifetime, useful for correlating the delete and insert halves of an update.

Now the single most important behavior, the one that surprises people: the offset advances only when you consume the stream inside a DML statement. Plain SELECT ... FROM orders_stream shows you the pending changes but does not move the offset — run it ten times, see the same rows ten times. The moment you use the stream as the source of an INSERT, MERGE, or CREATE TABLE AS, and that statement commits, the offset jumps forward to the current table version and the stream goes empty until the next change lands. This is exactly right for pipelines: you read the changes, apply them, and the acknowledgement is atomic with the apply. But it means you can’t “peek and consume” in two steps — the consume is the transaction.

There are two flavors worth knowing. A standard stream (the default) tracks inserts, updates, and deletes — the full delta, with the update-as-pair representation above. An append-only stream (CREATE STREAM ... APPEND_ONLY = TRUE) tracks only inserts, ignoring updates and deletes entirely. Append-only is cheaper and is what you want for classic ingestion — a raw landing table that only ever grows, where you never need to propagate deletes. Reach for standard when downstream must mirror deletes and updates; reach for append-only when new rows are the only thing that matters.

Finally, a helper you’ll use constantly with the next section: SYSTEM$STREAM_HAS_DATA('orders_stream') returns a boolean for whether the stream currently holds any unconsumed changes. That’s the hook for firing work only when there’s work to do.

Tasks: scheduling inside the warehouse

A Task runs a SQL statement (or a CALL) on a schedule, or after another task finishes. It’s cron and a lightweight DAG runner, living in the account. The minimal shape:

CREATE OR REPLACE TASK hourly_rollup
  WAREHOUSE = compute_wh
  SCHEDULE = '60 MINUTE'
AS
  INSERT INTO analytics.public.hourly_orders
  SELECT DATE_TRUNC('hour', o_orderdate), COUNT(*)
  FROM sales.public.orders
  WHERE o_orderdate >= DATEADD('hour', -1, CURRENT_TIMESTAMP());

SCHEDULE takes either an interval ('60 MINUTE') or a cron expression (SCHEDULE = 'USING CRON 0 6 * * * America/New_York' for 6am daily). Two facts trip up newcomers. First, a task is created suspended — nothing runs until you ALTER TASK hourly_rollup RESUME. Second, you can force a run right now, schedule or not, with EXECUTE TASK hourly_rollup, which is how you test one without waiting for the clock.

You have a compute choice. The example above is a warehouse task: it runs on a warehouse you name and bills that warehouse’s credits while it works. Omit WAREHOUSE and set USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL' instead and it becomes a serverless task, running on Snowflake-managed compute that spins up per execution and bills only for what it uses. Serverless is the better default for short, frequent tasks where a dedicated warehouse would sit mostly idle; a warehouse task is right when the work is heavy or must share a warehouse’s cache with related jobs.

Tasks chain into DAGs with AFTER: a task declared AFTER root_task runs when its predecessor succeeds, letting you build a small dependency tree (a root task on a schedule, children fanning out after it). And the gate that pairs with streams is the WHEN clause:

CREATE OR REPLACE TASK consume_orders
  WAREHOUSE = compute_wh
  SCHEDULE  = '5 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
AS
  ...;

With that WHEN, the task still evaluates every five minutes, but it only does work — and only bills real compute — when the stream actually has changes. Empty polls are nearly free. This is the mechanism that makes the next pattern cheap enough to run all day.

The classic pattern: a Stream + Task CDC pipeline

Here’s where the two pieces click together into the pattern you’ll build over and over: capture changes on a source table with a stream, and apply them to a target on a schedule with a task. Say sales.public.orders is fed by loads, and you want analytics.public.orders_replica to stay in sync — inserts, updates, and deletes all propagated.

The stream captures the delta; the task applies it with a MERGE that reads METADATA$ACTION to decide insert-or-delete, gated so it only runs when there’s something to do:

-- 1. Track every change on the source
CREATE OR REPLACE STREAM orders_stream ON TABLE sales.public.orders;

-- 2. The target we keep in sync
CREATE TABLE IF NOT EXISTS analytics.public.orders_replica LIKE sales.public.orders;

-- 3. A task that drains the stream into the target, every 5 min, only if data
CREATE OR REPLACE TASK apply_orders_cdc
  WAREHOUSE = compute_wh
  SCHEDULE  = '5 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
AS
  MERGE INTO analytics.public.orders_replica AS tgt
  USING (
    -- one row per key: the LAST state each key reached in this batch
    SELECT * EXCLUDE (METADATA$ACTION, METADATA$ISUPDATE, METADATA$ROW_ID),
           METADATA$ACTION AS action
    FROM orders_stream
    WHERE NOT (METADATA$ACTION = 'DELETE' AND METADATA$ISUPDATE = TRUE)
    QUALIFY ROW_NUMBER() OVER (
      PARTITION BY o_orderkey ORDER BY o_orderdate DESC
    ) = 1
  ) AS src
  ON tgt.o_orderkey = src.o_orderkey
  WHEN MATCHED AND src.action = 'DELETE' THEN DELETE
  WHEN MATCHED AND src.action = 'INSERT' THEN UPDATE SET
       tgt.o_custkey     = src.o_custkey,
       tgt.o_orderstatus = src.o_orderstatus,
       tgt.o_totalprice  = src.o_totalprice,
       tgt.o_orderdate   = src.o_orderdate,
       tgt.region        = src.region
  WHEN NOT MATCHED AND src.action = 'INSERT' THEN
    INSERT (o_orderkey, o_custkey, o_orderstatus,
            o_totalprice, o_orderdate, region)
    VALUES (src.o_orderkey, src.o_custkey, src.o_orderstatus,
            src.o_totalprice, src.o_orderdate, src.region);

-- 4. Turn it on
ALTER TASK apply_orders_cdc RESUME;

Two details in there are the difference between a demo and something you’d actually run, and both are the kind of thing that bites in week three rather than on day one.

The QUALIFY is not optional — but not for the reason you’d guess. The usual explanation is “a stream might hand you two rows for the same key.” It won’t: a standard stream returns the net delta between two offsets, so a row updated twice in the window still arrives as a single DELETE/INSERT pair (and a row inserted-then-deleted in the window vanishes from the delta entirely). That’s the whole point of a stream.

The real hazard is upstream of the stream, and it’s structural: Snowflake does not enforce primary keys. You can declare o_orderkey a PRIMARY KEY and Snowflake will record it in the catalog, hand it to the optimizer, and then cheerfully let you insert the same key twice. So if the source table holds two rows for order 42 — a double-load, a bad backfill, an upstream bug — the stream faithfully reports both, the MERGE tries to update the same target row twice, and Snowflake fails the entire statement with Duplicate row detected during DML action. Your CDC pipeline stops, at 3am, because of a data-quality problem three systems away.

The QUALIFY ROW_NUMBER() is therefore a defensive dedup: it collapses whatever the source hands you to one row per key, so a duplicate upstream degrades your replica’s precision rather than halting your pipeline. (Order by a column that actually reflects recency — an updated_at if you have one. o_orderdate is a business attribute that an UPDATE never changes, so it can’t order changes; as a tiebreak it’s arbitrary, which is acceptable for a dedup and useless as a “pick the latest” rule.) Any CDC merge without this works perfectly until the first duplicate arrives, then breaks loudly and stays broken.

Name your columns. The UPDATE SET and INSERT list every column, not just the key and one measure. It’s tempting to write UPDATE SET tgt.o_totalprice = src.o_totalprice and move on — but the target was created LIKE sales.public.orders, so it has all the columns, and the ones you don’t name silently stay NULL (on insert) or stale (on update). A replica that’s right about o_totalprice and wrong about everything else is worse than no replica, because it looks like it works.

With those in place, trace what happens. Every five minutes the task checks the stream; if it’s empty, it costs nothing and goes back to sleep. When rows have changed, the MERGE reads the delta and by consuming the stream inside that DML, atomically advances the offset — so the same change is never applied twice, even if the task later retries. The WHERE clause drops the delete-half of updates so each update collapses to a single upsert. Delete a source row and it becomes a lone DELETE action that removes the target row; insert or update and it’s an INSERT action that upserts. That’s a self-healing replication pipeline in about forty lines, no external orchestrator, no Kafka, no cron server. It is the reason Streams and Tasks exist, and it’s worth building once by hand even if you later let a tool generate it.

The failure mode to respect: if the task is suspended and stays suspended past the source table’s data retention window, the stream can go stale — the change history it needs ages out, and it returns an error until re-created. Streams assume their task keeps up. Monitor that the task is actually running (SHOW TASKS, and the TASK_HISTORY view) rather than assuming it is.

Snowpark: the DataFrame API

Everything above is SQL-shaped. Snowpark is the other end — a DataFrame API, most fully realized in Python, that looks like pandas or Spark but compiles to SQL and runs in Snowflake. You write Python; Snowpark translates each operation into SQL and pushes it down to the warehouse, so the data never leaves the platform and no rows stream to your client until you ask for them.

from snowflake.snowpark import Session
from snowflake.snowpark.functions import col, sum as sum_

session = Session.builder.configs(connection_params).create()

orders = session.table("sales.public.orders")
big = (orders
       .filter(col("o_totalprice") > 100000)
       .group_by("o_orderstatus")
       .agg(sum_("o_totalprice").alias("revenue")))

big.show()          # <-- only now does anything execute

The key property is laziness. table(), filter(), group_by(), agg() build up a query plan; nothing runs. Only a terminal action — .show(), .collect(), .to_pandas(), .save_as_table() — triggers Snowpark to send the compiled SQL and pull results. Under the hood, that chain becomes exactly the SELECT ... WHERE ... GROUP BY you’d have written, executed by the same engine, using the same warehouse and cache.

So where does Snowpark fit versus plain SQL? If the transformation is comfortably a SQL query, write SQL — it’s more portable and more people can read it. Snowpark earns its place when you want programmatic pipelines: constructing transformations in a loop, sharing Python logic between a UDF and a job, integrating with Python libraries, or when a team simply thinks in DataFrames. It’s the same execution surface with a different authoring ergonomics, not a faster engine. And it pairs naturally with the Python stored procedure from earlier — that session argument is a Snowpark session, so a Snowpark job scheduled by a Task is just a Python procedure the task calls.

Snowpark is also the doorway to the wider “code in Snowflake” surface you’ll meet later. Snowpark Container Services runs full OCI containers on Snowflake-managed compute for workloads that don’t fit a function — long-running services, GPU jobs, model serving. Streamlit in Snowflake hosts interactive Python data apps right against your tables, no separate web host. You don’t need either on day one; know they live in this neighborhood so you recognize them when a use case points that way.

Where Snowpipe fits

One clarification, because the names overlap. Snowpipe (which you met in the loading chapter, Loading Your Own Data) and Snowpipe Streaming are about getting data in — Snowpipe ingests files as they land in a stage, Snowpipe Streaming appends rows with low latency via an API. Streams and Tasks are about what happens after it lands: transforming, replicating, and rolling up data already in Snowflake. The idiomatic continuous pipeline stacks them — Snowpipe lands raw files into a table, a Stream captures the new rows, and a Task transforms them onward — each tool doing the one job it’s built for. Don’t try to make a Task poll a bucket or a Snowpipe transform; ingestion and in-warehouse processing are different halves, and they compose cleanly precisely because they stay in their lanes.

Examples in this chapter are docs-checked against the series’ stated versions, but they were not executed in this repository unless a companion project explicitly says so.

Final thoughts

Snowflake’s programmable surfaces all pull code toward the data, but each answers a different question. A UDF encapsulates a value — scalar SQL for reusable expressions, Python when you want a library, a UDTF when the answer is a set of rows. A stored procedure runs a workflow, and its owner’s-versus-caller’s-rights setting is a real security decision, not boilerplate. A Stream remembers what changed and hands it to you exactly once, advancing its offset only when you consume it in DML. A Task brings the clock, cheaply gated on SYSTEM$STREAM_HAS_DATA so it bills only when there’s work. Together, a Stream and a Task are the classic in-warehouse CDC pipeline — self-healing replication in a few dozen lines. And Snowpark offers all of this from a Python DataFrame API that compiles right back down to the same SQL engine.

The discipline that keeps this power from curdling is the ownership question from the top: who tests, deploys, observes, and pays for each of these objects? A scalar UDF checked into version control and a Stream-and-Task pipeline your team monitors are assets. A procedure nobody remembers writing, quietly holding a transformation your dashboards depend on, is a liability waiting to become an outage. Use these tools where the warehouse-native primitive is genuinely the best surface — and keep your real analytics logic in the light.

Next: Connecting from Outside the Browser

Comments