Snowflake Data Science Pipelines: Dynamic Tables, Python UDFs, UDTFs and Stored Procedures

1.Automating data transformation with dynamic tables

A model only stays as good as the features it is trained on. The first job of a data science pipeline is to keep those features clean and up to date without someone rerunning scripts by hand. In Snowflake the declarative way to do this is a dynamic table. You write a SELECT and set a target lag. Snowflake then works out which base tables the query reads, watches them for changes, and refreshes the result for you.

CREATE OR ALTER DYNAMIC TABLE dt_orders
    TARGET_LAG = '10 minutes'
    WAREHOUSE = transform_wh
    REFRESH_MODE = INCREMENTAL
AS
    SELECT
        order_id,
        customer_id,
        order_date,
        TRIM(UPPER(product_name)) AS product_name,
        quantity,
        unit_price,
        quantity * unit_price AS line_total,
        order_status
    FROM raw_orders
    WHERE order_status != 'returned';

You build a multi-step pipeline by pointing one dynamic table at another, for example a daily aggregate on top of dt_orders. You never declare the order. Snowflake infers the dependency graph from your queries and refreshes the tables in that order against a consistent snapshot, and each refresh is applied atomically. On intermediate tables, TARGET_LAG = DOWNSTREAM means they refresh only when a table downstream needs fresh data. The refresh mode sets how much work each refresh does:

These limits mark where the declarative part of the pipeline ends. Dynamic tables are not suited to workloads that need data fresher than 60 seconds or refresh timing that is strictly guaranteed. They cannot contain stored procedures or external functions. They also cannot use UDTFs outside of lateral joins. Training itself therefore never happens inside a dynamic table: the dynamic table prepares features, and code objects take over from there. Costs come from three places: the warehouse that runs each refresh, Cloud Services for scheduling and dependency tracking (a shorter lag means more scheduling work), and storage.

Sources1

2.Python UDFs: one value per row

Some feature logic, or scoring a trained model, is easier to write in Python than in SQL. A Python UDF puts that logic in a function you can call from any SELECT. It is a scalar function: each call receives one row's arguments and returns exactly one value. Arguments are bound by position, not by name, and the HANDLER clause tells Snowflake which Python function to run. The UDF name does not have to match the handler name.

The code can be written inline in the AS $$ ... $$ body, or uploaded to a stage and referenced through IMPORTS, with the handler given as module.function. If a handler should work on batches rather than single rows, a vectorized Python UDF receives input as pandas DataFrames and returns pandas arrays or Series. UDFs are also where many training pipelines end: a model trained elsewhere in Snowflake can be uploaded to a stage and then used to create UDFs that perform inference.

CREATE OR REPLACE FUNCTION addone(i INT)
  RETURNS INT
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.12'
  HANDLER = ' ? '
AS $$
def addone_py(i):
 return i+1
$$;

Sources23

3.Python UDTFs: rows out, state per partition

A UDF has to return a single value. When a step needs to return rows, or needs to see a whole group of rows before it answers, the tool is a Python UDTF. Its handler is a class, and Snowflake calls up to three methods on it:

CREATE OR REPLACE FUNCTION stock_sale_sum(symbol VARCHAR, quantity NUMBER, price NUMBER(10,2))
  RETURNS TABLE (symbol VARCHAR, total NUMBER(10,2))
  LANGUAGE PYTHON
  RUNTIME_VERSION = 3.12
  HANDLER = 'StockSaleSum'
AS $$
class StockSaleSum:
    def __init__(self):
        self._cost_total = 0
        self._symbol = ""

    def process(self, symbol, quantity, price):
      self._symbol = symbol
      cost = quantity * price
      self._cost_total += cost
      yield (symbol, cost)

    def end_partition(self):
      yield (self._symbol, self._cost_total)
$$;

SELECT stock_sale_sum.symbol, total
  FROM stocks_table, TABLE(stock_sale_sum(symbol, quantity, price) OVER (PARTITION BY symbol));

The partitions are set by the query, not by the function. With __init__ and end_partition the handler is partition-aware. Leave both out and it runs statelessly, row by row. Prefer yield with one statement per output row, because lazy evaluation is more efficient and can help avoid timeouts. A single-value tuple needs a trailing comma, as in yield (cost,). If any handler method raises an exception, processing stops and the calling query fails. UDTFs are limited to 500 input arguments and 500 output columns.

Sources4

4.Python stored procedures: the orchestrating step

UDFs and UDTFs run inside a query. A Python stored procedure runs on its own and can issue queries itself. Its handler uses the Snowpark API to query, update and transform tables, with a Snowflake warehouse doing the compute. That makes it the natural home for steps that a single SELECT cannot express. To run it on a schedule, you call it from a task. You can also call it directly from SQL.

Libraries such as scikit-learn come in through the PACKAGES clause. Snowflake recommends using Artifact Repository to import packages from PyPI. Anaconda packages are also available, but first someone must accept the External Offerings Terms, using the ORGADMIN role, once per account. Without that acceptance, stored procedures still run, but you cannot use third-party Anaconda packages, you cannot pin a Snowpark version, and you cannot call to_pandas on a DataFrame. That last limitation stops most training code. To see which package versions are available, query information_schema.packages.

Sources5

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.A dynamic table can call the training stored procedure (or an external scoring function) so that the model retrains automatically on every refresh.Why is that wrong?

   Dynamic tables do not support stored procedures or external functions in their definition. Use them to prepare features, and run training in a stored procedure, scheduled with a task if needed.

   Covered in Automating data transformation with dynamic tables

2. 2.A Python stored procedure can always use DataFrame.to_pandas(); the Anaconda terms only matter for extra libraries.Why is that wrong?

   Until the External Offerings Terms are accepted, to_pandas is unavailable, along with third-party Anaconda packages.

   Covered in Python stored procedures: the orchestrating step