[小ネタ]SnowflakeのDynamic Tableで利用できるUDF/UDTFについて確認してみた

モダンデータスタック(MDS)

#Snowflake

さがら

2025.04.07

さがらです。

SnowflakeのDynamic Tableで利用できるUDF/UDTFについて確認してみたので、本記事でまとめてみます。

前提知識

Dynamic Tableの公式DocsのLimitations on query constructsでは、以下のように言及がされており、これらの仕様に合致する場合はDynamic Tableで利用できない、ということを意味しています。

この仕様を実際に確かめてみたのが、本記事の内容となります。

User-defined table functions (UDTF) written in SQL.
User-defined functions (UDF) written in SQL that contain a subquery (for example, a SELECT statement).

データ準備

まず、以下のクエリを実行して必要なデータを準備します。※以降の本記事のクエリはすべてsysadminで実行しています。


-- 適当なデータベースを作成
use role sysadmin;
create database dynamic_table_test_udf;
use database dynamic_table_test_udf;
use schema public;

-- 販売データテーブルの作成
create or replace table sales (
  sale_id int,
  product_id int,
  customer_id int,
  sale_date date,
  amount decimal(10,2),
  quantity int,
  region varchar(50),
  channel varchar(20)
);

-- データ投入
insert into sales values
(1001, 101, 501, '2023-01-15', 299.99, 1, 'north', 'online'),
(1002, 102, 502, '2023-01-16', 129.50, 2, 'south', 'store'),
(1003, 101, 503, '2023-01-16', 299.99, 1, 'east', 'online'),
(1004, 103, 501, '2023-01-17', 499.99, 1, 'north', 'online'),
(1005, 104, 504, '2023-01-18', 59.99, 3, 'west', 'store');

-- 変更追跡を有効化
alter table sales set change_tracking = true;

UDFの例：利用できるケース

以下のように、あるカラムの値を受け取って処理するだけUDFであれば、Dynamic Tableでも利用できます。

-- 単純なSQL UDF（サブクエリなし）
create or replace function calculate_discount(price decimal, discount_rate decimal)
  returns decimal
  as
  $$
    price * (1 - discount_rate)
  $$;

-- 単純なSQL UDFを使用したDynamic Table
create or replace dynamic table discounted_sales
  target_lag = '1 hour'
  warehouse = compute_wh
  refresh_mode = auto
  as
    select 
      sale_id,
      product_id,
      amount,
      calculate_discount(amount, 0.1) as discounted_amount
    from sales;

2025-04-07_11h36_53

UDFの例：利用できないケース

以下のように、UDF内でSELECT文を用いている(=サブクエリがある)と、Dynamic Tableでは利用できません。

-- サブクエリを含むSQL UDF
create or replace function get_avg_amount_by_region(region_name varchar)
  returns decimal
  as
  $$
    select avg(amount) from sales where region = region_name
  $$;

-- サブクエリを含むSQL UDFを使用したDynamic Table
create or replace dynamic table regional_sales_analysis
  target_lag = '1 hour'
  warehouse = compute_wh
  as
    select 
      sale_id,
      region,
      amount,
      get_avg_amount_by_region(region) as region_avg_amount
    from sales;

2025-04-07_11h38_46

UDTFの例：利用できないケース

以下のように、UDTFはどんな処理内容であっても、Dynamic Tableでは利用できません。

-- SQL UDTF
create or replace function generate_series(start_val int, end_val int)
returns table (value int)
as
$$
  select start_val + row_number() - 1 as value
  from table(generator(rowcount => (end_val - start_val + 1)))
$$;

-- SQL UDTFを使用したDynamic Table
create or replace dynamic table number_series
  target_lag = '1 hour'
  warehouse = compute_wh
  refresh_mode = auto
  as
    select 
      value as number_value,
      value * value as squared_value
    from table(generate_series(1, 10));