2025年7月にリリースされたSnowflakeの新機能・変更点のまとめ #SnowflakeDB

ALTER SECURITY INTEGRATION my_integration
  SET OAUTH_SINGLE_USE_REFRESH_TOKENS_REQUIRED = TRUE;

CREATE SEMANTIC VIEW tpch_rev_analysis

  TABLES (
    orders AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
      PRIMARY KEY (o_orderkey)
      WITH SYNONYMS ('sales orders')
      COMMENT = 'All orders table for the sales domain',
    customers AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
      PRIMARY KEY (c_custkey)
      COMMENT = 'Main table for customer data',
    line_items AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM
      PRIMARY KEY (l_orderkey, l_linenumber)
      COMMENT = 'Line items in orders'
  )

  RELATIONSHIPS (
    orders_to_customers AS
      orders (o_custkey) REFERENCES customers,
    line_item_to_orders AS
      line_items (l_orderkey) REFERENCES orders
  )

  FACTS (
    line_items.line_item_id AS CONCAT(l_orderkey, '-', l_linenumber),
    orders.count_line_items AS COUNT(line_items.line_item_id),
    line_items.discounted_price AS l_extendedprice * (1 - l_discount)
      COMMENT = 'Extended price after discount'
  )

  DIMENSIONS (
    customers.customer_name AS customers.c_name
      WITH SYNONYMS = ('customer name')
      COMMENT = 'Name of the customer',
    orders.order_date AS o_orderdate
      COMMENT = 'Date when the order was placed',
    orders.order_year AS YEAR(o_orderdate)
      COMMENT = 'Year when the order was placed'
  )

  METRICS (
    customers.customer_count AS COUNT(c_custkey)
      COMMENT = 'Count of number of customers',
    orders.order_average_value AS AVG(orders.o_totalprice)
      COMMENT = 'Average order value across all orders',
    orders.average_line_items_per_order AS AVG(orders.count_line_items)
      COMMENT = 'Average number of line items per order'
  )

  COMMENT = 'Semantic view for revenue analysis';

>SHOW SEMANTIC DIMENSIONS;
+---------------+-------------+--------------------+------------+---------------+-------------+-------------------+--------------------------------+
| database_name | schema_name | semantic_view_name | table_name | name          | data_type   | synonyms          | comment                        |
|---------------+-------------+--------------------+------------+---------------+-------------+-------------------+--------------------------------|
| RAW_DB        | PUBLIC      | TPCH_REV_ANALYSIS  | CUSTOMERS  | CUSTOMER_NAME | VARCHAR(25) | ["customer name"] | Name of the customer           |
| RAW_DB        | PUBLIC      | TPCH_REV_ANALYSIS  | ORDERS     | ORDER_DATE    | DATE        | NULL              | Date when the order was placed |
| RAW_DB        | PUBLIC      | TPCH_REV_ANALYSIS  | ORDERS     | ORDER_YEAR    | NUMBER(4,0) | NULL              | Year when the order was placed |
+---------------+-------------+--------------------+------------+---------------+-------------+-------------------+--------------------------------+

>SHOW SEMANTIC METRICS;
+---------------+-------------+--------------------+------------+------------------------------+--------------+----------+----------------------------------------+
| database_name | schema_name | semantic_view_name | table_name | name                         | data_type    | synonyms | comment                                |
|---------------+-------------+--------------------+------------+------------------------------+--------------+----------+----------------------------------------|
| RAW_DB        | PUBLIC      | TPCH_REV_ANALYSIS  | CUSTOMERS  | CUSTOMER_COUNT               | NUMBER(18,0) | NULL     | Count of number of customers           |
| RAW_DB        | PUBLIC      | TPCH_REV_ANALYSIS  | ORDERS     | AVERAGE_LINE_ITEMS_PER_ORDER | NUMBER(36,6) | NULL     | Average number of line items per order |
| RAW_DB        | PUBLIC      | TPCH_REV_ANALYSIS  | ORDERS     | ORDER_AVERAGE_VALUE          | NUMBER(30,8) | NULL     | Average order value across all orders  |
+---------------+-------------+--------------------+------------+------------------------------+--------------+----------+----------------------------------------+

>SHOW SEMANTIC DIMENSIONS IN tpch_rev_analysis FOR METRIC customer_count;
+------------+---------------+-------------+----------+-------------------+----------------------+
| table_name | name          | data_type   | required | synonyms          | comment              |
|------------+---------------+-------------+----------+-------------------+----------------------|
| CUSTOMERS  | CUSTOMER_NAME | VARCHAR(25) | false    | ["customer name"] | Name of the customer |
+------------+---------------+-------------+----------+-------------------+----------------------+

--一覧にない（返すことができない）ディメンションをクエリするとエラーとなる
--ディメンション（orders.order_date）はメトリクス（customers.customer_count）より粒度が高いためエラー
>SELECT * FROM SEMANTIC_VIEW (
                              tpch_rev_analysis
                              DIMENSIONS orders.order_date
                              METRICS customers.customer_count
                              );
010234 (42601): SQL compilation error:
Invalid dimension specified: The dimension entity 'ORDERS' must be related to and have an equal or lower level of granularity compared to the base metric or dimension entity 'CUSTOMERS'.

>SHOW SEMANTIC DIMENSIONS IN tpch_rev_analysis FOR METRIC order_average_value;
+------------+---------------+-------------+----------+-------------------+--------------------------------+
| table_name | name          | data_type   | required | synonyms          | comment                        |
|------------+---------------+-------------+----------+-------------------+--------------------------------|
| CUSTOMERS  | CUSTOMER_NAME | VARCHAR(25) | false    | ["customer name"] | Name of the customer           |
| ORDERS     | ORDER_DATE    | DATE        | false    | NULL              | Date when the order was placed |
| ORDERS     | ORDER_YEAR    | NUMBER(4,0) | false    | NULL              | Year when the order was placed |
+------------+---------------+-------------+----------+-------------------+--------------------------------+

>SHOW SEMANTIC DIMENSIONS IN tpch_rev_analysis FOR METRIC average_line_items_per_order;
+------------+---------------+-------------+----------+-------------------+--------------------------------+
| table_name | name          | data_type   | required | synonyms          | comment                        |
|------------+---------------+-------------+----------+-------------------+--------------------------------|
| CUSTOMERS  | CUSTOMER_NAME | VARCHAR(25) | false    | ["customer name"] | Name of the customer           |
| ORDERS     | ORDER_DATE    | DATE        | false    | NULL              | Date when the order was placed |
| ORDERS     | ORDER_YEAR    | NUMBER(4,0) | false    | NULL              | Year when the order was placed |
+------------+---------------+-------------+----------+-------------------+--------------------------------+

--引数には'DATABASE'や'SCHEMA'を指定できる
>SELECT SYSTEM$SHOW_SENSITIVE_DATA_MONITORED_ENTITIES('DATABASE');
[
{"name":"TESTDB","type":"DATABASE","profile_name":"TESTDB.TESTSCHEMA.MY_CLASSIFICATION_PROFILE"},
{"name":"TEST","type":"DATABASE","profile_name":"TEST.PUBLIC.TEST_PROFILE"}
]

INSERT INTO ORDERS (order_id, customer_id, order_status, order_date, total_amount, delivery_region) VALUES
(1, 101, 'Pending', '2023-01-01', 100.50, 'North'),
(2, 102, 'Delivered', '2023-01-02', 250.75, 'South'),
(3, 103, 'Dispatched', '2023-01-03', 120.00, 'East'),
(4, 104, 'Cancelled', '2023-01-04', 75.20, 'West'), -- 許容されない値
(5, 105, 'Delivered', '2023-01-05', 300.00, 'North'),
(6, 106, 'Pending', '2023-01-06', 50.00, 'South'),
(7, 107, 'PROCESSING', '2023-01-07', 180.90, 'East'), -- 許容されない値 (大文字)
(8, 108, 'Delivered', '2023-01-08', 90.10, 'West'),
(9, 109, 'pending', '2023-01-09', 210.00, 'North'); -- 許容されない値 (小文字)

ALTER TABLE ORDERS SET DATA_METRIC_SCHEDULE = '5 MINUTE';

--order_statusで許容される値を指定
ALTER TABLE ORDERS ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.ACCEPTED_VALUES
ON (order_status, order_status -> order_status IN ('Pending', 'Dispatched', 'Delivered'));

>SELECT
   scheduled_time, measurement_time, table_name, metric_name, value
FROM SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS
WHERE METRIC_NAME = 'ACCEPTED_VALUES';
+-------------------------------+-------------------------------+------------+-----------------+-------+
| SCHEDULED_TIME                | MEASUREMENT_TIME              | TABLE_NAME | METRIC_NAME     | VALUE |
|-------------------------------+-------------------------------+------------+-----------------+-------|
| 2025-07-30 23:25:00.000 -0700 | 2025-07-30 23:25:05.365 -0700 | ORDERS     | ACCEPTED_VALUES | 3     |
+-------------------------------+-------------------------------+------------+-----------------+-------+

>WITH ImageEmbeddings AS (
    -- 各画像ファイルに対してAI_EMBED関数で埋め込みベクトルを生成
    SELECT 'fubuki.png' AS image_name,
           AI_EMBED('voyage-multimodal-3', TO_FILE('@images', 'fubuki.png')) AS embedding
    UNION ALL
    SELECT 'snow_crystal3.png' AS image_name,
           AI_EMBED('voyage-multimodal-3', TO_FILE('@images', 'snow_crystal3.png')) AS embedding
    UNION ALL
    SELECT 'snow_crystal5.png' AS image_name,
           AI_EMBED('voyage-multimodal-3', TO_FILE('@images', 'snow_crystal5.png')) AS embedding
)
SELECT
    img1.image_name AS image1,
    img2.image_name AS image2,
    VECTOR_COSINE_SIMILARITY(img1.embedding, img2.embedding) AS similarity_score
FROM
    ImageEmbeddings img1
CROSS JOIN
    ImageEmbeddings img2
WHERE
    img1.image_name <= img2.image_name -- 同じ画像の比較や重複する組み合わせを避けるため
ORDER BY
    image1, similarity_score DESC;
+-------------------+-------------------+------------------+
| IMAGE1            | IMAGE2            | SIMILARITY_SCORE |
|-------------------+-------------------+------------------|
| fubuki.png        | fubuki.png        |     1            |
| fubuki.png        | snow_crystal5.png |     0.4380911871 |
| fubuki.png        | snow_crystal3.png |     0.3973730352 |
| snow_crystal3.png | snow_crystal3.png |     1            |
| snow_crystal3.png | snow_crystal5.png |     0.8290283672 |
| snow_crystal5.png | snow_crystal5.png |     1            |
+-------------------+-------------------+------------------+

>SELECT AI_SIMILARITY(TO_FILE('@images', 'snow_crystal3.png'),TO_FILE('@images', 'snow_crystal5.png'));
+-------------------------------------------------------------------------------------------------+
| AI_SIMILARITY(TO_FILE('@IMAGES', 'SNOW_CRYSTAL3.PNG'), TO_FILE('@IMAGES', 'SNOW_CRYSTAL5.PNG')) |
|-------------------------------------------------------------------------------------------------|
|                                                                                    0.8290283672 |
+-------------------------------------------------------------------------------------------------+