Forem: Sagara

Trying Out Snowflake's Adaptive Warehouse — Auto-Scaling Compute Without Manual Sizing

Sagara — Thu, 16 Apr 2026 00:59:22 +0000

This is an English translation of the original Japanese article:
https://dev.classmethod.jp/articles/snowflake-try-adaptive-warehouse/

Until now, Snowflake warehouses required you to manually select and manage a size (XS, S, M, L, etc.). With Adaptive Warehouse, compute resources automatically scale based on query characteristics. This eliminates the need for manual warehouse sizing and scale-up/scale-out configuration.

I learned about this feature from @tshowis's post:

https://x.com/tshowis/status/2044446136556282293

While release notes haven't been published yet, the official documentation has full details. The Public Preview supported regions include AWS Tokyo Region (AP Northeast 1), so those using the Tokyo region can try it right away. I tested how it works using TPC-H sample data and have summarized the steps and results below.

https://docs.snowflake.com/en/user-guide/warehouses-adaptive

Feature Overview

Adaptive Warehouse is a compute service that recognizes workloads and automatically allocates resources. It has a dedicated shared compute pool per account and automatically determines the optimal resources based on query characteristics.

Key features include:

No warehouse sizing required: No need to manually select or change sizes
Automatic per-query scaling: Small queries use fewer resources, large queries automatically use more
Online conversion: Converting existing standard warehouses to Adaptive Warehouses requires no downtime
Behavior controlled by 2 parameters

The two controllable parameters are:

Parameter	Default	Description
`MAX_QUERY_PERFORMANCE_LEVEL`	`XLARGE`	Upper performance limit per query (XSMALL–X4LARGE)
`QUERY_THROUGHPUT_MULTIPLIER`	`2`	Scale factor for concurrent execution (0 for unlimited, positive integer for cap)

Setting QUERY_THROUGHPUT_MULTIPLIER to N ensures capacity for N queries to run concurrently at the MAX_QUERY_PERFORMANCE_LEVEL resource level.

Limitations

As of April 16, 2026, this is a Public Preview feature. Specifications may change before GA
Available regions (Public Preview): US West 2 (Oregon), EU West 1 (Ireland), AP Northeast 1 (Tokyo)
Required edition: Enterprise Edition or higher
Conversion from X5Large/X6Large warehouses to Adaptive Warehouse is not supported
Mutual conversion with Snowpark-optimized warehouses and Interactive warehouses is also not supported

Cost

Adaptive Warehouse uses a query-based billing model. Each query's cost is calculated based on the compute resources and software resources consumed. The total warehouse cost is the sum of all executed query costs.

Creating a warehouse itself incurs no cost — billing begins when queries are executed. For cost management, you can use Budgets, Resource Monitors, and views in SNOWFLAKE.ACCOUNT_USAGE for monitoring.

Prerequisites

Snowflake: AWS US Oregon region, Enterprise Edition
- I happened to have a trial account, so I tested on the US Oregon region

Preparation

Verify TPC-H Sample Data

For this test, I used the TPC-H sample data provided by default in Snowflake. TPC-H is a decision support benchmark dataset well-suited for evaluating large-scale data processing with complex joins and aggregations.

https://docs.snowflake.com/en/user-guide/sample-data-tpch

Multiple schemas with different scale factors (SF) are available in the SNOWFLAKE_SAMPLE_DATA database:

Schema	Approximate Data Scale
`TPCH_SF1`	Millions of records
`TPCH_SF10`	Tens of millions of records
`TPCH_SF100`	Hundreds of millions of records
`TPCH_SF1000`	Billions of records

To clearly observe the automatic scaling behavior, I used TPCH_SF1000 (billions of records).

First, verify that you can access the sample data:

-- Verify sample data access
USE SCHEMA SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000;

-- Check table list
SHOW TABLES;

You should see 8 tables (LINEITEM, ORDERS, CUSTOMER, SUPPLIER, PART, PARTSUPP, NATION, REGION).

Disable Account-Level Cache

To accurately compare execution times before and after parameter tuning, disable the query result cache. With caching enabled, subsequent identical queries would be served from cache, making execution time comparison impossible.

https://docs.snowflake.com/en/sql-reference/parameters#use-cached-result

Disable caching at the account level:

USE ROLE ACCOUNTADMIN;

-- Disable query result cache at account level
ALTER ACCOUNT SET USE_CACHED_RESULT = false;

Verify the parameter is set to false:

-- Confirm the setting
SHOW PARAMETERS LIKE '%USE_CACHED_RESULT%';

⚠️ If you're using a production account, don't forget to re-enable caching after testing:
USE ROLE ACCOUNTADMIN;
ALTER ACCOUNT SET USE_CACHED_RESULT = true;

Hands-On Testing

1. Create an Adaptive Warehouse (XLARGE Setting)

First, create a new Adaptive Warehouse with MAX_QUERY_PERFORMANCE_LEVEL = XLARGE:

USE ROLE SYSADMIN;

-- Create Adaptive Warehouse with XLARGE setting
CREATE ADAPTIVE WAREHOUSE adaptive_wh_xlarge
  WITH MAX_QUERY_PERFORMANCE_LEVEL = XLARGE
       QUERY_THROUGHPUT_MULTIPLIER = 2;

Verify the configuration:

-- Check warehouse settings
SHOW WAREHOUSES LIKE 'adaptive_wh_xlarge';

Confirm that the type column shows ADAPTIVE.

2. Run TPC-H Queries with XLARGE Setting

Run representative TPC-H queries using adaptive_wh_xlarge.

Set QUERY_TAG so we can compare execution times later:

-- Set QUERY_TAG for later comparison
ALTER SESSION SET QUERY_TAG = 'adaptive_xlarge_m2';

USE WAREHOUSE adaptive_wh_xlarge;
USE SCHEMA SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000;

TPC-H Q1: Aggregation Query (Full LINEITEM Table Scan)

First, run TPC-H Q1 (Pricing Summary Report) — one of the simplest TPC-H queries. It performs a full table scan and aggregation on the LINEITEM table (approximately 6 billion records at SF1000).

-- TPC-H Q1: Pricing Summary Report
-- Aggregates the LINEITEM table (~6 billion records at SF1000)
SELECT
    L_RETURNFLAG,
    L_LINESTATUS,
    SUM(L_QUANTITY)                                       AS SUM_QTY,
    SUM(L_EXTENDEDPRICE)                                  AS SUM_BASE_PRICE,
    SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT))               AS SUM_DISC_PRICE,
    SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT) * (1 + L_TAX)) AS SUM_CHARGE,
    AVG(L_QUANTITY)                                       AS AVG_QTY,
    AVG(L_EXTENDEDPRICE)                                  AS AVG_PRICE,
    AVG(L_DISCOUNT)                                       AS AVG_DISC,
    COUNT(*)                                              AS COUNT_ORDER
FROM
    LINEITEM
WHERE
    L_SHIPDATE <= DATEADD(DAY, -90, TO_DATE('1998-12-01'))
GROUP BY
    L_RETURNFLAG,
    L_LINESTATUS
ORDER BY
    L_RETURNFLAG,
    L_LINESTATUS;

Results after 5 runs:

Run	Execution Time (sec)
1	4.47
2	4.55
3	3.68
4	3.85
5	3.77

TPC-H Q5: Multi-Table Join Query

Next, run Q5 (Local Supplier Volume), which joins multiple tables:

-- TPC-H Q5: Local Supplier Volume
-- Complex query joining 6 tables
SELECT
    N.N_NAME,
    SUM(L.L_EXTENDEDPRICE * (1 - L.L_DISCOUNT)) AS REVENUE
FROM
    CUSTOMER   C,
    ORDERS     O,
    LINEITEM   L,
    SUPPLIER   S,
    NATION     N,
    REGION     R
WHERE
    C.C_CUSTKEY    = O.O_CUSTKEY
    AND L.L_ORDERKEY  = O.O_ORDERKEY
    AND L.L_SUPPKEY   = S.S_SUPPKEY
    AND C.C_NATIONKEY = S.S_NATIONKEY
    AND S.S_NATIONKEY = N.N_NATIONKEY
    AND N.N_REGIONKEY = R.R_REGIONKEY
    AND R.R_NAME      = 'ASIA'
    AND O.O_ORDERDATE >= TO_DATE('1994-01-01')
    AND O.O_ORDERDATE <  DATEADD(YEAR, 1, TO_DATE('1994-01-01'))
GROUP BY
    N.N_NAME
ORDER BY
    REVENUE DESC;

Results after 5 runs:

Run	Execution Time (sec)
1	5.74
2	3.17
3	3.03
4	3.03
5	2.83

3. Create a Comparison Adaptive Warehouse (SMALL Setting)

Create a separate Adaptive Warehouse with MAX_QUERY_PERFORMANCE_LEVEL = SMALL:

USE ROLE SYSADMIN;

-- Create Adaptive Warehouse with SMALL setting
CREATE ADAPTIVE WAREHOUSE adaptive_wh_small
  WITH MAX_QUERY_PERFORMANCE_LEVEL = SMALL
       QUERY_THROUGHPUT_MULTIPLIER = 2;

-- Check warehouse settings
SHOW WAREHOUSES LIKE 'adaptive_wh_small';

Confirm that the type column shows ADAPTIVE.

4. Run TPC-H Queries with SMALL Setting

Run the same Q1 and Q5 queries using adaptive_wh_small:

-- Set QUERY_TAG for SMALL setting runs
ALTER SESSION SET QUERY_TAG = 'adaptive_small_m2';

USE WAREHOUSE adaptive_wh_small;
USE SCHEMA SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000;

TPC-H Q1: Aggregation Query

-- Q1 execution (MAX_QUERY_PERFORMANCE_LEVEL = SMALL)
SELECT
    L_RETURNFLAG,
    L_LINESTATUS,
    SUM(L_QUANTITY)                                       AS SUM_QTY,
    SUM(L_EXTENDEDPRICE)                                  AS SUM_BASE_PRICE,
    SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT))               AS SUM_DISC_PRICE,
    SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT) * (1 + L_TAX)) AS SUM_CHARGE,
    AVG(L_QUANTITY)                                       AS AVG_QTY,
    AVG(L_EXTENDEDPRICE)                                  AS AVG_PRICE,
    AVG(L_DISCOUNT)                                       AS AVG_DISC,
    COUNT(*)                                              AS COUNT_ORDER
FROM
    LINEITEM
WHERE
    L_SHIPDATE <= DATEADD(DAY, -90, TO_DATE('1998-12-01'))
GROUP BY
    L_RETURNFLAG,
    L_LINESTATUS
ORDER BY
    L_RETURNFLAG,
    L_LINESTATUS;

Results after 5 runs:

Run	Execution Time (sec)
1	21.57
2	31.73
3	27.73
4	21.74
5	34.35

TPC-H Q5: Multi-Table Join Query

-- Q5 execution (MAX_QUERY_PERFORMANCE_LEVEL = SMALL)
SELECT
    N.N_NAME,
    SUM(L.L_EXTENDEDPRICE * (1 - L.L_DISCOUNT)) AS REVENUE
FROM
    CUSTOMER   C,
    ORDERS     O,
    LINEITEM   L,
    SUPPLIER   S,
    NATION     N,
    REGION     R
WHERE
    C.C_CUSTKEY    = O.O_CUSTKEY
    AND L.L_ORDERKEY  = O.O_ORDERKEY
    AND L.L_SUPPKEY   = S.S_SUPPKEY
    AND C.C_NATIONKEY = S.S_NATIONKEY
    AND S.S_NATIONKEY = N.N_NATIONKEY
    AND N.N_REGIONKEY = R.R_REGIONKEY
    AND R.R_NAME      = 'ASIA'
    AND O.O_ORDERDATE >= TO_DATE('1994-01-01')
    AND O.O_ORDERDATE <  DATEADD(YEAR, 1, TO_DATE('1994-01-01'))
GROUP BY
    N.N_NAME
ORDER BY
    REVENUE DESC;

Results after 5 runs:

Run	Execution Time (sec)
1	42.18
2	38.49
3	38.41
4	38.69
5	36.97

5. Compare Execution Times Using INFORMATION_SCHEMA.QUERY_HISTORY

After completing runs on both warehouses, compare the two QUERY_TAG values using INFORMATION_SCHEMA.QUERY_HISTORY(). Unlike ACCOUNT_USAGE views, this can be queried without delay.

View All 20 Individual Run Times

First, check all 20 individual execution records (Q1 and Q5 × 5 runs each × 2 warehouses). Queries containing L_RETURNFLAG in QUERY_TEXT are identified as Q1, and those containing N_NAME as Q5.

-- All 20 execution records (check execution times)
SELECT
    QUERY_TAG,
    WAREHOUSE_NAME,
    CASE
        WHEN QUERY_TEXT ILIKE '%L_RETURNFLAG%' THEN 'Q1'
        WHEN QUERY_TEXT ILIKE '%N_NAME%'       THEN 'Q5'
        ELSE 'OTHER'
    END                                       AS query_name,
    START_TIME,
    ROUND(EXECUTION_TIME / 1000, 2)           AS exec_sec
FROM TABLE(
    INFORMATION_SCHEMA.QUERY_HISTORY(
        END_TIME_RANGE_START => DATEADD(HOUR, -2, CURRENT_TIMESTAMP()),
        END_TIME_RANGE_END   => CURRENT_TIMESTAMP(),
        RESULT_LIMIT         => 1000
    )
)
WHERE WAREHOUSE_NAME   IN ('ADAPTIVE_WH_XLARGE', 'ADAPTIVE_WH_SMALL')
  AND QUERY_TAG        IN ('adaptive_xlarge_m2', 'adaptive_small_m2')
  AND EXECUTION_STATUS = 'SUCCESS'
  AND QUERY_TYPE       = 'SELECT'
ORDER BY QUERY_TAG, query_name, START_TIME;

QUERY_TAG	WAREHOUSE_NAME	Query	Run	Execution Time (sec)
adaptive_xlarge_m2	ADAPTIVE_WH_XLARGE	Q1	1	4.47
adaptive_xlarge_m2	ADAPTIVE_WH_XLARGE	Q1	2	4.55
adaptive_xlarge_m2	ADAPTIVE_WH_XLARGE	Q1	3	3.68
adaptive_xlarge_m2	ADAPTIVE_WH_XLARGE	Q1	4	3.85
adaptive_xlarge_m2	ADAPTIVE_WH_XLARGE	Q1	5	3.77
adaptive_xlarge_m2	ADAPTIVE_WH_XLARGE	Q5	1	5.74
adaptive_xlarge_m2	ADAPTIVE_WH_XLARGE	Q5	2	3.17
adaptive_xlarge_m2	ADAPTIVE_WH_XLARGE	Q5	3	3.03
adaptive_xlarge_m2	ADAPTIVE_WH_XLARGE	Q5	4	3.03
adaptive_xlarge_m2	ADAPTIVE_WH_XLARGE	Q5	5	2.83
adaptive_small_m2	ADAPTIVE_WH_SMALL	Q1	1	21.57
adaptive_small_m2	ADAPTIVE_WH_SMALL	Q1	2	31.73
adaptive_small_m2	ADAPTIVE_WH_SMALL	Q1	3	27.73
adaptive_small_m2	ADAPTIVE_WH_SMALL	Q1	4	21.74
adaptive_small_m2	ADAPTIVE_WH_SMALL	Q1	5	34.35
adaptive_small_m2	ADAPTIVE_WH_SMALL	Q5	1	42.18
adaptive_small_m2	ADAPTIVE_WH_SMALL	Q5	2	38.49
adaptive_small_m2	ADAPTIVE_WH_SMALL	Q5	3	38.41
adaptive_small_m2	ADAPTIVE_WH_SMALL	Q5	4	38.69
adaptive_small_m2	ADAPTIVE_WH_SMALL	Q5	5	36.97

Aggregated Comparison

Check whether lowering MAX_QUERY_PERFORMANCE_LEVEL is reflected in execution_time. I also included queued_overload_time to observe any queuing trends.

-- Aggregate execution times by QUERY_TAG × query type for comparison
-- Only target the most recent 5 SELECT statements per QUERY_TAG × query_name
WITH ranked AS (
    SELECT
        QUERY_TAG,
        WAREHOUSE_NAME,
        CASE
            WHEN QUERY_TEXT ILIKE '%L_RETURNFLAG%' THEN 'Q1'
            WHEN QUERY_TEXT ILIKE '%N_NAME%'       THEN 'Q5'
            ELSE 'OTHER'
        END                  AS query_name,
        EXECUTION_TIME,
        TOTAL_ELAPSED_TIME,
        QUEUED_OVERLOAD_TIME,
        ROW_NUMBER() OVER (
            PARTITION BY QUERY_TAG,
                         CASE
                             WHEN QUERY_TEXT ILIKE '%L_RETURNFLAG%' THEN 'Q1'
                             WHEN QUERY_TEXT ILIKE '%N_NAME%'       THEN 'Q5'
                             ELSE 'OTHER'
                         END
            ORDER BY START_TIME DESC
        ) AS rn
    FROM TABLE(
        INFORMATION_SCHEMA.QUERY_HISTORY(
            END_TIME_RANGE_START => DATEADD(HOUR, -2, CURRENT_TIMESTAMP()),
            END_TIME_RANGE_END   => CURRENT_TIMESTAMP(),
            RESULT_LIMIT         => 1000
        )
    )
    WHERE WAREHOUSE_NAME   IN ('ADAPTIVE_WH_XLARGE', 'ADAPTIVE_WH_SMALL')
      AND QUERY_TAG        IN ('adaptive_xlarge_m2', 'adaptive_small_m2')
      AND EXECUTION_STATUS = 'SUCCESS'
      AND QUERY_TYPE       = 'SELECT'
)
SELECT
    QUERY_TAG,
    WAREHOUSE_NAME,
    query_name,
    COUNT(*)                                           AS run_count,
    ROUND(AVG(EXECUTION_TIME)       / 1000, 2)        AS avg_exec_sec,
    ROUND(MIN(EXECUTION_TIME)       / 1000, 2)        AS min_exec_sec,
    ROUND(MAX(EXECUTION_TIME)       / 1000, 2)        AS max_exec_sec,
    ROUND(AVG(TOTAL_ELAPSED_TIME)   / 1000, 2)        AS avg_elapsed_sec,
    ROUND(AVG(QUEUED_OVERLOAD_TIME) / 1000, 2)        AS avg_queued_overload_sec
FROM ranked
WHERE rn <= 5
GROUP BY QUERY_TAG, WAREHOUSE_NAME, query_name
ORDER BY QUERY_TAG, query_name;

Here are the summarized results in table format.

You can see that adaptive_small_m2 has longer avg_exec_sec than adaptive_xlarge_m2 (execution time increases as the performance cap is lowered).

QUERY_TAG	WAREHOUSE_NAME	Query	Runs	Avg Exec (sec)	Min (sec)	Max (sec)	Avg Elapsed (sec)
adaptive_xlarge_m2	ADAPTIVE_WH_XLARGE	Q1	5	4.06	3.68	4.55	7.17
adaptive_xlarge_m2	ADAPTIVE_WH_XLARGE	Q5	5	3.56	2.83	5.74	6.86
adaptive_small_m2	ADAPTIVE_WH_SMALL	Q1	5	27.42	21.57	34.35	25.82
adaptive_small_m2	ADAPTIVE_WH_SMALL	Q5	5	38.95	36.97	42.18	42.72

Analysis

Both Q1 and Q5 showed significantly shorter execution times on the XLARGE warehouse compared to the SMALL warehouse.

Query	XLARGE Avg Exec Time	SMALL Avg Exec Time	Ratio
Q1	4.06 sec	27.42 sec	~6.8x
Q5	3.56 sec	38.95 sec	~10.9x

Q5 showed a larger ratio than Q1. Since Q5 involves JOINs across 6 tables, it's a more complex query where the difference in available resources becomes more pronounced.

Testing Note: Warehouse Data Cache

There's an important point to keep in mind about this test. Snowflake warehouses have a data cache (local disk cache) separate from the query result cache. This mechanism retains micro-partitions read from remote storage on the warehouse's local SSD.

In this test, I disabled the query result cache at the account level with USE_CACHED_RESULT = false, but I did not disable the data cache. Therefore, when running the same query consecutively on the same warehouse, the 2nd run onward reads data from local SSD, making it faster than the first run.

Looking at the actual data, the XLARGE warehouse's Q5 first run took 5.74 seconds, while runs 2–5 dropped to 2.83–3.17 seconds, confirming the data cache effect.

Checking the query profile, the 2nd run shows a certain percentage for Percentage scanned from cache, confirming that the warehouse data cache was in effect.

SMALL Q5: 1st Run Query Profile

SMALL Q5: 2nd Run Query Profile

Since the purpose of this test was to confirm the "relative difference between SMALL and XLARGE," both warehouses were compared under the same conditions (same cache state), making the relative comparison valid. However, if you want to measure pure cold-start performance, note that you need to suspend and restart the warehouse to clear the data cache before measuring.

Conclusion

I tested Snowflake's new Adaptive Warehouse feature using TPC-H sample data.

Comparing MAX_QUERY_PERFORMANCE_LEVEL at XLARGE vs. SMALL, I observed approximately a 6.8x difference for Q1 (aggregation query) and approximately a 10.9x difference for Q5 (6-table JOIN). More complex JOIN queries tend to show a more pronounced difference based on resource allocation, confirming that the MAX_QUERY_PERFORMANCE_LEVEL setting directly impacts performance.

The most impressive aspect was being able to control the "cost vs. performance balance" with just two parameters (MAX_QUERY_PERFORMANCE_LEVEL and QUERY_THROUGHPUT_MULTIPLIER) without thinking about warehouse sizes at all. Previously, you had to make specific size choices like "XL or XXL," but Adaptive Warehouse automates that while letting you simply specify an upper limit — a much simpler design.

This feature is available in Public Preview for Enterprise Edition and above. In addition to the US Oregon region (US West 2) used in this test, it's also available in EU West 1 (Ireland) and AP Northeast 1 (Tokyo). If you're using the Tokyo region, you can try it immediately with the same steps described here — give it a try!

PDFs with Graphs? Just Ask the Agent: Cross-Analyzing Unstructured and Structured Data on Snowflake Cortex Agent

Sagara — Mon, 30 Mar 2026 23:13:39 +0000

This is an English translation of the original Japanese article:
https://dev.classmethod.jp/articles/snowflake-multi-modal-analytics-with-cortex-agent/

Note: Since this is a translated article, some images contain Japanese text.

Previously, analyzing unstructured data on Snowflake required Cortex Search, which meant parsing text and loading it into tables — making it difficult to work with PDFs containing graphs and charts. However, now that the AI_COMPLETE function can directly query PDF files on stages, you can pass entire PDFs to an LLM without text extraction or chunk splitting.

https://docs.snowflake.com/en/user-guide/snowflake-cortex/ai-complete-document-intelligence

This means we can wrap AI_COMPLETE in a stored procedure as a PDF custom tool and combine it with Cortex Analyst (Semantic View) to enable natural language analysis across both "unstructured data like PDFs on stages" and "table data" — all within a single Cortex Agent. I decided to put this to the test.

The idea for this article was inspired by the following blog post. The approach of using a stored procedure that reads PDFs via AI_COMPLETE as a custom tool for Cortex Agent was extremely helpful.

https://zenn.dev/truestar/articles/d2431ccd4aa127

⚠️ Important note: The table data (monthly sales details) used in this article is entirely dummy data. While the PDF financial reports are actual published financial data from Classmethod, the monthly breakdown by service line and region is fictional. The dummy data was generated by proportionally distributing values so that the PDF's annual totals match the table's monthly totals.

Background & Challenges

When analyzing unstructured data on Snowflake, the main approaches available until now were:

Cortex Search: Extract and chunk text into tables, then search and answer via RAG (Retrieval-Augmented Generation)
Document AI: Structured data extraction (table conversion) from PDFs

These work well for text-centric documents, but had the following limitations:

PDFs with graphs and charts lose information when only text is extracted
A preprocessing pipeline for text parsing and table conversion is required, adding setup overhead

Technical Approach

Using AI_COMPLETE's document intelligence feature (TO_FILE + PROMPT), you can pass PDFs on stages directly to an LLM without text extraction. Since graphs and tables can be referenced as visual elements, information that was previously lost through text extraction can now be analyzed.

By wrapping this in a Python stored procedure and registering it as a custom tool for Cortex Agent, then combining it with Cortex Analyst (via Semantic View's cortex_analyst_text_to_sql tool), we achieve the following architecture:

AGENT_CM_FINANCIAL_ANALYST (Integrated Agent)
├── SP_ASK_CM_FINANCIALS (generic tool)
│     └── AI_COMPLETE + TO_FILE → Directly query PDFs on stage
└── AnalystMonthlySales (cortex_analyst_text_to_sql tool)
      └── Semantic View → SQL aggregation on monthly sales table

Tool selection is automatic by the Agent. Based on the question content, it calls the PDF tool, the Analyst tool, or both, and integrates the results into a unified answer.

Limitations

AI_COMPLETE with documents is in Public Preview as of March 30, 2026
- Stages must use server-side encryption (SNOWFLAKE_SSE)
- File size limits: Up to 10MB (900 pages) for Gemini 3.1 Pro, up to 4.5MB for Claude models
- Document count limits per request: Up to 20 for Gemini, up to 5 for Claude

Cost

AI_COMPLETE costs are determined by processed token count, not file size. Text portions and visual elements are tokenized, and the combined input/output is billable
Cortex Analyst (Semantic View) has a lower unit price when accessed through Cortex Agents (see Consumption Table Table 6(f) and 6(h))

Prerequisites

Snowflake: Enterprise edition
Feature status: Public Preview as of March 30, 2026 (AI_COMPLETE with documents, Cortex Agents, Semantic View)
Required privileges: SYSADMIN role, SNOWFLAKE.CORTEX_USER database role grant
Cross-region inference: Setting CORTEX_ENABLED_CROSS_REGION = 'ANY_REGION' may be required to use gemini-3.1-pro (set with ACCOUNTADMIN)
Model used: gemini-3.1-pro (chosen for PDF reading as it supports up to 10MB)

Setup

Overall Architecture

Here's the complete picture of Snowflake objects we'll create:

POC_CM.SAGARA_TEST
├── STG_CM_FINANCIAL_REPORTS    -- PDF stage
├── V_CM_FINANCIAL_METADATA     -- PDF metadata view
├── T_MONTHLY_SALES             -- Dummy sales table
├── SV_MONTHLY_SALES            -- Semantic View (for Cortex Analyst)
├── SP_ASK_CM_FINANCIALS        -- PDF custom tool (stored procedure)
└── AGENT_CM_FINANCIAL_ANALYST  -- Integrated Agent

Target Data

PDFs (Unstructured Data)

We use the following 4 PDFs. In addition to 3 fiscal years of financial reports, we include a company introduction presentation (slide format) with numerous graphs and charts to also verify AI_COMPLETE's visual reading capabilities.

File Name	Type	Period	Coverage
financial-results_202306.pdf	Financial Report	19th Period	2022/7/1 - 2023/6/30
financial-results_202406.pdf	Financial Report	20th Period	2023/7/1 - 2024/6/30
financial-results_202506.pdf	Financial Report	21st Period	2024/7/1 - 2025/6/30
会社紹介資料_20251031.pdf	Company Introduction	—	As of October 2025

The company introduction presentation contains visual information that would be lost through text extraction: bar graphs of revenue trends, bar graphs of employee count trends, pie charts of team composition, office location maps, etc. The key question is whether AI_COMPLETE can directly read this type of material, which was difficult to handle with the conventional Cortex Search (text parsing approach).

Financial report example (obtained from Classmethod's official website)

Company introduction example (obtained from Classmethod's official SpeakerDeck)

Table (Structured Data) — Dummy Data

We generate dummy data that aligns with the PDF financial figures. The key demo point is verifying that "the annual revenue from the PDF side matches the monthly sales aggregation from the table side."

Column	Type	Description
SALE_ID	NUMBER	Surrogate key (sequential)
FISCAL_YEAR	NUMBER	Fiscal year (19, 20, 21)
YEAR_MONTH	DATE	First day of month (36 months)
SERVICE_LINE	VARCHAR	Service line (6 categories)
CUSTOMER_SEGMENT	VARCHAR	Customer segment (5 categories)
REGION	VARCHAR	Region (6 categories)
REVENUE	NUMBER	Revenue (thousands of yen)
COGS	NUMBER	Cost of goods sold (thousands of yen)
GROSS_PROFIT	NUMBER	Gross profit (thousands of yen)
DEAL_COUNT	NUMBER	Deal count

Alignment rules with PDFs:

Total REVENUE per period = PDF's revenue (19th: ¥59,005,311K, 20th: ¥77,190,340K, 21st: ¥95,056,018K)
Total COGS per period = PDF's cost of sales (19th: ¥52,634,720K, 20th: ¥69,320,565K, 21st: ¥85,782,527K)

Implementation

1. Stage Creation & PDF Upload

First, create an internal stage for PDF storage. Note that the encryption type must be SNOWFLAKE_SSE or AI_COMPLETE won't be able to read the files.

CREATE OR REPLACE STAGE STG_CM_FINANCIAL_REPORTS
  DIRECTORY = (ENABLE = TRUE)
  ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')
  COMMENT = 'Stage for Classmethod financial report PDFs';

After creating the stage, upload the 4 PDFs via Snowsight: Data > Databases > POC_CM > SAGARA_TEST > Stages > STG_CM_FINANCIAL_REPORTS > + Files button.

After uploading, it should look like the following:

After uploading, refresh the directory table and verify the files:

ALTER STAGE STG_CM_FINANCIAL_REPORTS REFRESH;

SELECT * FROM DIRECTORY(@STG_CM_FINANCIAL_REPORTS);

You should see 4 files listed.

Next, create a metadata view that extracts document type and fiscal year from file names. Since financial reports and company introduction materials have different naming conventions, we use CASE statements to handle both patterns.

CREATE OR REPLACE VIEW V_CM_FINANCIAL_METADATA AS
SELECT
    RELATIVE_PATH,
    FILE_URL,
    SIZE,
    LAST_MODIFIED,
    -- Determine document type
    CASE
        WHEN RELATIVE_PATH LIKE 'financial-results%' THEN '決算報告書'
        WHEN RELATIVE_PATH LIKE '会社紹介資料%' THEN '会社紹介資料'
        ELSE 'その他'
    END AS DOC_TYPE,
    -- Extract fiscal year only for financial reports
    CASE
        WHEN RELATIVE_PATH LIKE 'financial-results%' THEN
            CASE SPLIT_PART(REPLACE(RELATIVE_PATH, '.pdf', ''), '_', 2)
                WHEN '202306' THEN 19
                WHEN '202406' THEN 20
                WHEN '202506' THEN 21
            END
        ELSE NULL
    END AS FISCAL_YEAR,
    CASE
        WHEN RELATIVE_PATH LIKE 'financial-results%' THEN
            CASE SPLIT_PART(REPLACE(RELATIVE_PATH, '.pdf', ''), '_', 2)
                WHEN '202306' THEN '2022/7/1 - 2023/6/30'
                WHEN '202406' THEN '2023/7/1 - 2024/6/30'
                WHEN '202506' THEN '2024/7/1 - 2025/6/30'
            END
        WHEN RELATIVE_PATH LIKE '会社紹介資料%' THEN '2025年10月時点'
        ELSE NULL
    END AS FISCAL_PERIOD
FROM DIRECTORY(@STG_CM_FINANCIAL_REPORTS)
WHERE RELATIVE_PATH LIKE '%.pdf';

Querying the created view shows the following:

2. Standalone AI_COMPLETE Verification

Before building the PDF custom tool, let's verify that AI_COMPLETE can directly read PDFs.

SELECT AI_COMPLETE(
  MODEL => 'gemini-3.1-pro',
  PROMPT => PROMPT(
    'この決算報告書の売上高と売上原価を教えてください: {0}',
    TO_FILE('@POC_CM.SAGARA_TEST.STG_CM_FINANCIAL_REPORTS', 'financial-results_202506.pdf')
  )
);

The PDF specified with TO_FILE is passed to the {0} placeholder in the PROMPT function. If the correct revenue and cost of sales figures are returned, we're good to go.

3. Creating the Stored Procedure for the PDF Custom Tool

Since AI_COMPLETE cannot be directly registered as a Cortex Agent tool, we wrap it in a Python stored procedure. It dynamically retrieves the PDF file list from the directory table, filters by document type (DOC_TYPE) and fiscal year, then executes AI_COMPLETE against each PDF.

CREATE OR REPLACE PROCEDURE POC_CM.SAGARA_TEST.SP_ASK_CM_FINANCIALS(
    QUESTION VARCHAR,
    FILTER_DOC_TYPE VARCHAR DEFAULT NULL,
    FILTER_FISCAL_YEAR VARCHAR DEFAULT NULL
)
RETURNS VARCHAR
LANGUAGE PYTHON
RUNTIME_VERSION = '3.12'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'main'
EXECUTE AS OWNER
AS
$$
import json

def _normalize(val):
    """Treat string 'NULL'/'null'/empty string as None (ignore)"""
    if val is None:
        return None
    if val.strip().upper() in ('NULL', ''):
        return None
    return val

def main(session, question, filter_doc_type=None, filter_fiscal_year=None):
    filter_doc_type = _normalize(filter_doc_type)
    filter_fiscal_year = _normalize(filter_fiscal_year)

    query = """
        SELECT RELATIVE_PATH, DOC_TYPE, FISCAL_YEAR, FISCAL_PERIOD
        FROM POC_CM.SAGARA_TEST.V_CM_FINANCIAL_METADATA
        WHERE 1=1
    """
    if filter_doc_type:
        safe_doc_type = filter_doc_type.replace("'", "''")
        query += f" AND DOC_TYPE = '{safe_doc_type}'"
    if filter_fiscal_year:
        query += f" AND FISCAL_YEAR = {filter_fiscal_year}"

    files_df = session.sql(query).collect()

    if not files_df:
        return json.dumps(
            {"error": "No PDF files matched the specified conditions"},
            ensure_ascii=False
        )

    stage_path = '@POC_CM.SAGARA_TEST.STG_CM_FINANCIAL_REPORTS'
    results = []

    for row in files_df:
        pdf_file = row['RELATIVE_PATH']
        safe_question = question.replace("'", "''")
        safe_pdf_file = pdf_file.replace("'", "''")
        ai_query = f"""
            SELECT AI_COMPLETE(
                MODEL => 'gemini-3.1-pro',
                PROMPT => PROMPT(
                    '{safe_question}: {{0}}',
                    TO_FILE('{stage_path}', '{safe_pdf_file}')
                )
            ) AS answer
        """
        try:
            result = session.sql(ai_query).collect()
            answer = result[0]['ANSWER'] if result else 'Error: No result'
        except Exception as e:
            answer = f'Error: {str(e)}'

        results.append({
            'file': pdf_file,
            'doc_type': row['DOC_TYPE'],
            'fiscal_year': str(row['FISCAL_YEAR']) if row['FISCAL_YEAR'] else None,
            'fiscal_period': row['FISCAL_PERIOD'],
            'answer': answer
        })

    return json.dumps(results, ensure_ascii=False)
$$;

The procedure includes a _normalize() function. When Cortex Agent passes parameters to the stored procedure, it sometimes sends the string "NULL" instead of SQL NULL. This function converts those to Python None.

For verification, let's call it with several patterns:

-- Across all PDFs
CALL POC_CM.SAGARA_TEST.SP_ASK_CM_FINANCIALS('Please summarize the key points of this document');

-- Financial reports only, 21st period
CALL POC_CM.SAGARA_TEST.SP_ASK_CM_FINANCIALS(
    'What are the revenue and operating profit?', '決算報告書', '21');

-- Company introduction only (graph reading)
CALL POC_CM.SAGARA_TEST.SP_ASK_CM_FINANCIALS(
    'Read the revenue trend from the performance graph', '会社紹介資料');

The third query in particular tests AI_COMPLETE's visual reading capability — reading numerical values from bar graphs.

If JSON-formatted answers from each PDF are returned as shown below, everything is working correctly.

4. Dummy Data Generation & Table Creation

Generate table data for Cortex Analyst.

We create a T_MONTHLY_SALES table and INSERT approximately 6,000 rows of dummy data that aligns with the PDF financial figures.

Column	Description
FISCAL_YEAR	Fiscal year (19, 20, 21)
YEAR_MONTH	First day of month (36 months)
SERVICE_LINE	Service line (AWS Resale, Cloud Migration Support, etc. — 6 categories)
CUSTOMER_SEGMENT	Customer segment (Enterprise, Mid-Market, etc. — 5 categories)
REGION	Region (Kanto, Kansai, etc. — 6 categories)
REVENUE / COGS / GROSS_PROFIT	Revenue / Cost of sales / Gross profit (thousands of yen)
DEAL_COUNT	Deal count

5. Semantic View Creation

Create a Semantic View for Cortex Analyst. Setting Japanese SYNONYMS improves the accuracy of natural language queries.

CREATE OR REPLACE SEMANTIC VIEW POC_CM.SAGARA_TEST.SV_MONTHLY_SALES

  TABLES (
    T_MONTHLY_SALES AS POC_CM.SAGARA_TEST.T_MONTHLY_SALES
      PRIMARY KEY (SALE_ID)
      WITH SYNONYMS = ('月次売上', '売上データ', '売上明細', '売上実績')
      COMMENT = 'Monthly sales detail data for Classmethod Inc.'
  )

  DIMENSIONS (
    T_MONTHLY_SALES.FISCAL_YEAR AS T_MONTHLY_SALES.FISCAL_YEAR
      WITH SYNONYMS = ('期', '年度', '会計期間')
      COMMENT = 'Fiscal year (19=19th period, 20=20th period, 21=21st period)',

    T_MONTHLY_SALES.YEAR_MONTH AS T_MONTHLY_SALES.YEAR_MONTH
      WITH SYNONYMS = ('月', '年月', '対象月')
      COMMENT = 'Target month (first day of month)',

    T_MONTHLY_SALES.SERVICE_LINE AS T_MONTHLY_SALES.SERVICE_LINE
      WITH SYNONYMS = ('サービス', '事業', '事業区分', 'サービス区分')
      COMMENT = 'Service line',

    T_MONTHLY_SALES.CUSTOMER_SEGMENT AS T_MONTHLY_SALES.CUSTOMER_SEGMENT
      WITH SYNONYMS = ('顧客区分', 'セグメント', '顧客タイプ')
      COMMENT = 'Customer segment',

    T_MONTHLY_SALES.REGION AS T_MONTHLY_SALES.REGION
      WITH SYNONYMS = ('地域', 'エリア', '拠点')
      COMMENT = 'Region'
  )

  METRICS (
    T_MONTHLY_SALES.TOTAL_REVENUE AS SUM(T_MONTHLY_SALES.REVENUE)
      WITH SYNONYMS = ('売上', '売上額', '売上金額', '収益', '売上高')
      COMMENT = 'Revenue (thousands of yen)',

    T_MONTHLY_SALES.TOTAL_COGS AS SUM(T_MONTHLY_SALES.COGS)
      WITH SYNONYMS = ('原価', '売上原価', 'コスト')
      COMMENT = 'Cost of goods sold (thousands of yen)',

    T_MONTHLY_SALES.TOTAL_GROSS_PROFIT AS SUM(T_MONTHLY_SALES.GROSS_PROFIT)
      WITH SYNONYMS = ('粗利', '粗利益', '売上総利益', 'GP')
      COMMENT = 'Gross profit (thousands of yen)',

    T_MONTHLY_SALES.TOTAL_DEAL_COUNT AS SUM(T_MONTHLY_SALES.DEAL_COUNT)
      WITH SYNONYMS = ('案件数', '取引件数', 'ディール数')
      COMMENT = 'Deal count'
  )

  COMMENT = 'Classmethod monthly sales dummy data (for Cortex Analyst)';

6. Integrated Agent Creation

Next, create the Agent that integrates the PDF custom tool and the Cortex Analyst tool. The key point is to clearly specify how each tool should be used in the instructions.

CREATE OR REPLACE AGENT POC_CM.SAGARA_TEST.AGENT_CM_FINANCIAL_ANALYST
  COMMENT = 'Classmethod financial report integrated analysis agent (PDF × Table)'
  FROM SPECIFICATION
$$
models:
  orchestration: auto

orchestration:
  budget:
    seconds: 120
    tokens: 32000

instructions:
  system: >
    あなたはクラスメソッド株式会社の企業情報・財務分析アシスタントです。
    以下の2つのデータソースを使って質問に回答できます。

    【データソース1: PDF資料】
    SP_ASK_CM_FINANCIALSツールで参照できます。
    (A) 決算報告書（3期分）: FILTER_DOC_TYPE=決算報告書
    (B) 会社紹介資料: FILTER_DOC_TYPE=会社紹介資料

    【データソース2: 月次売上テーブル】
    AnalystMonthlySalesツールで参照できます。

    【ツール選択ルール】
    1. 決算書の定性的な内容 → PDFツール（FILTER_DOC_TYPE=決算報告書）
    2. 会社概要、拠点、従業員数、経営理念、業績推移グラフ等
       → PDFツール（FILTER_DOC_TYPE=会社紹介資料）
    3. 数値の集計・比較・推移分析 → Analystツール
    4. PDF×テーブルの横断分析 → 両方呼び出して比較

  response: >
    日本語で回答してください。
    数値を含む回答ではテーブル形式で見やすく整理してください。

  sample_questions:
    - question: "第21期の売上高は？"
    - question: "サービスライン別の売上推移を教えて"
    - question: "第20期の監査報告書の内容は？"
    - question: "PDFの年間売上とテーブルの月次合計を突合して"
    - question: "会社の拠点一覧を教えて"
    - question: "業績推移のグラフから売上高の成長率を教えて"
    - question: "従業員数の推移と売上高の推移を比較して"

tools:
  - tool_spec:
      type: "generic"
      name: "SP_ASK_CM_FINANCIALS"
      description: >
        クラスメソッドのPDF資料（決算報告書・会社紹介資料）に対して質問を行い、回答を取得するツール。
        FILTER_DOC_TYPE（決算報告書, 会社紹介資料）とFILTER_FISCAL_YEAR（19, 20, 21）でフィルタ可能。
      input_schema:
        type: "object"
        properties:
          QUESTION:
            type: "string"
            description: "PDF資料に対して問い合わせる質問文"
          FILTER_DOC_TYPE:
            type: "string"
            description: "資料種別フィルタ（決算報告書, 会社紹介資料）。指定しない場合はNULL。"
          FILTER_FISCAL_YEAR:
            type: "string"
            description: "会計年度フィルタ（19, 20, 21）。指定しない場合はNULL。"
        required:
          - "QUESTION"

  - tool_spec:
      type: "cortex_analyst_text_to_sql"
      name: "AnalystMonthlySales"
      description: >
        月次売上明細テーブルに対してSQL集計を行い、数値データを分析するツール。

tool_resources:
  SP_ASK_CM_FINANCIALS:
    type: "procedure"
    identifier: "POC_CM.SAGARA_TEST.SP_ASK_CM_FINANCIALS"
    execution_environment:
      type: "warehouse"
      warehouse: "COPUTE_WH"
      query_timeout: 120
  AnalystMonthlySales:
    semantic_view: "POC_CM.SAGARA_TEST.SV_MONTHLY_SALES"
    execution_environment:
      type: "warehouse"
      warehouse: "COPUTE_WH"
      query_timeout: 60
$$;

7. Testing via Snowflake Intelligence

Let's interactively test the created Agent from the Snowflake Intelligence UI.

⚠️ Once again, the table data (monthly sales details) used in this verification is entirely dummy data. While the PDF financial reports are actual published financial data from Classmethod, the monthly breakdown by service line and region is fictional. The dummy data was generated by proportionally distributing values so that the PDF's annual totals match the table's monthly totals. Please keep this in mind.

We'll run the following test questions to verify that tool selection works correctly.

Test 1: "What is the revenue for the 21st period?"

→ Answered using the Analyst tool or PDF tool. Either way, the same value (¥95,056,018K) should be returned.

Test 2: "Show me the revenue trend by service line"

→ The Analyst tool is selected, and SQL aggregation is executed against the table data.

Test 3: "What does the audit report for the 20th period say?"

→ The PDF tool is selected, and the content of the audit report within the financial report PDF is returned.

Test 4: "Reconcile the annual revenue from the PDF with the monthly totals from the table"

→ Both tools are called, and the response confirms that the annual revenue from the PDF side matches the monthly sales total from the table side. This is the highlight of this verification.

Test 5: "List the company's office locations"

→ The PDF tool (FILTER_DOC_TYPE=会社紹介資料) is selected, and a list of 8 domestic and 5 overseas offices is returned from the company introduction's office location page (slide with map).

Test 6: "What is the revenue growth rate based on the performance trend graph?"

→ The PDF tool (FILTER_DOC_TYPE=会社紹介資料) is selected, and the response reads numerical values from the bar graph and calculates growth rates. This demonstrates that graph reading — which was impossible with text extraction — works properly.

Test 7: "Tell me about Classmethod's evaluation system, career paths, and salary ranges"

→ The PDF tool (FILTER_DOC_TYPE=会社紹介資料) is selected, and it properly reads the grade list and salary correlation expressed in complex graphs.

Test 8: "Compare the employee count trend with the revenue trend"

→ Both tools are called — the PDF tool (employee count trend graph from the company introduction) and the Analyst tool (revenue aggregation from the table) — and the response provides a cross-cutting comparative analysis of employee growth and revenue growth.

Conclusion

By using AI_COMPLETE's document intelligence feature, PDFs can be passed directly to an LLM without building a RAG pipeline, making it extremely simple to incorporate as a Cortex Agent custom tool.

Here's what this verification confirmed:

Cross-analysis of PDFs and tables is now possible within a single Agent. Based on the question content, the Agent automatically selects the appropriate PDF tool, Analyst tool, or both
Reconciliation between PDF annual totals and table monthly aggregations is achievable by calling both tools and comparing results
Even PDFs containing graphs and charts can be analyzed directly without text extraction or chunk splitting, making the setup dramatically simpler compared to Cortex Search

On the other hand, it's important to note that AI_COMPLETE with documents is in Public Preview (as of March 2026), there are file size limitations, and costs are incurred based on processed token count.

This approach feels like an excellent fit for use cases where you want to perform cross-cutting analysis of unstructured and structured data on Snowflake. Give it a try!

Snowflake DCM Projects : Building DEV/PROD Environments with Declarative IaC Using Template Variables

Sagara — Fri, 20 Mar 2026 22:29:36 +0000

This is an English version of the original Japanese article:
https://dev.classmethod.jp/articles/snowflake-dcm-projects-preview/

Snowflake has released a new feature called DCM Projects. It allows you to declaratively define Snowflake objects using DEFINE statements and deploy them through a Plan → Deploy workflow — essentially an Infrastructure-as-Code (IaC) feature native to Snowflake.

https://docs.snowflake.com/en/release-notes/2026/other/2026-03-20-dcm-projects

https://docs.snowflake.com/en/user-guide/dcm-projects/dcm-projects-overview

Previously, IaC management of Snowflake objects required external tools such as Terraform or Schemachange. With DCM Projects, you can now manage objects declaratively within Snowflake natively.

In this article, I verified a workflow where a single DCM project folder (set of definition files) has two targets defined, and deploys to separate DCM project objects for DEV and PROD by switching template variables. I'll walk through the steps and share the results.

Note: DCM Projects is in Preview as of March 20, 2026. Specifications may change before GA. Also, the official docs recommend separating DEV/PROD environments by using different accounts. The single-account setup in this article is purely for verification purposes — if you operate within a single account, careful access management is required.

Feature Overview

DCM Projects (Database Change Management Projects) is a feature that lets you define the desired state of Snowflake objects in code and manage them declaratively.

Key characteristics include:

Declarative definitions: Define the desired state of objects using DEFINE statements. Dependencies are automatically resolved, so you don't need to worry about the order of declarations
Plan → Deploy workflow: Review diffs with PLAN before deploying, then apply changes with DEPLOY. It follows the same concept as Terraform's plan → apply
Jinja2 templates: Supports variable substitution, conditional branching, loops, and macros. You can expand different parameters per environment from a single set of definition files
Supported objects: Database, Schema, Table, Dynamic Table, (Secure) View, Internal Stage, Warehouse, Role / Database Role, Grant, Data Metric Function, Task, SQL Function, Tag, Authentication Policy, and more. However, only a subset of Snowflake objects is supported — for example, File Format is not included
Pipeline management: REFRESH ALL for bulk-refreshing Dynamic Tables, and TEST ALL for bulk-running data quality tests
Management interfaces: Operable from Snowsight, Snowflake CLI (v3.16+), SQL, and Cortex Code CLI

https://docs.snowflake.com/en/user-guide/dcm-projects/dcm-projects-supported-entities

Limitations

The following are the main limitations confirmed as of March 20, 2026, based on the official docs.

General

As a Preview feature, specifications may change
Only a subset of Snowflake objects is supported
Changes during deploy are subject to CREATE OR ALTER constraints. For some objects (e.g., Tables), partial changes may be applied on failure
Maximum of 1,000 source files / 10,000 rendered object definitions. Exceeding these limits may cause performance degradation or execution failures
During Preview, changesets may not fully capture all granular changes
_snow is a reserved identifier
Sensitive information should not be placed in template variables

Object-specific limitations

Database / Schema / Table / View / Dynamic Table: Rename is not supported
Table: Column rename, incompatible type changes, adding Search Optimization, and adding tags / masking policies / row access policies to column definitions are not supported. Column order changes are also not supported
Dynamic Table: INITIALIZE is immutable. Body changes or refresh mode changes may require re-initialization / full refresh. Column order changes and rename are not supported
View: Rename and column order changes are not supported
Internal Stage: Only internal stages are supported, and encryption type is immutable
Warehouse: INITIALLY_SUSPENDED is immutable
Role / Database Role: Application Roles are not supported
Grant: APPLICATION ROLE grants / CALLER grants are not supported
Tag: PROPAGATE is not supported

Jinja2 templates

import, extends, and include syntax are not supported

Prerequisites

Snowflake: AWS US West (Oregon) region, Enterprise Edition (The feature was not enabled in an AWS Tokyo region environment, so a trial account was created in AWS US West.)
Feature status: Preview as of March 20, 2026
Required privileges: CREATE DCM PROJECT ON SCHEMA privilege. Additionally, the project owner needs sufficient privileges to deploy all objects defined in the project
Project owner role: Since this article includes role creation via DEFINE ROLE, ACCOUNTADMIN (which has CREATE ROLE privilege) is used
DCM project object location: DCM project objects are schema-level objects, so a database / schema to host them is required (this is a separate concept from the deployment target database)
Operating environment: Snowsight Workspace

Preparation

Creating a Workspace

Create a Workspace in Snowsight.

Navigate to "Projects" → "Workspaces" from the left menu, click the Workspace name at the top left, select "Private Workspace", enter a name, and create it.

If the Workspace is created as shown below, you're good to go.

Creating a Database / Schema for DCM Project Objects

DCM project objects are schema-level objects and require a database and schema to host them. In this article, we create a dedicated DCM_ADMIN.PROJECTS database / schema and place both the DEV and PROD DCM project objects there.

USE ROLE ACCOUNTADMIN;
USE SECONDARY ROLES NONE;

-- For hosting DCM project objects
CREATE DATABASE IF NOT EXISTS DCM_ADMIN;
CREATE SCHEMA IF NOT EXISTS DCM_ADMIN.PROJECTS;

Note: The official DCM Projects docs recommend running USE SECONDARY ROLES NONE; because secondary roles are considered during Plan / Deploy execution, and you want to avoid depending on privileges beyond the project owner role. This article also uses USE SECONDARY ROLES NONE; during preparation and deploy-related SQL execution.

After running the queries, confirm that the DCM_ADMIN database and PROJECTS schema have been created.

Hands-On Walkthrough

Let's build DEV/PROD environments using DCM Projects!

1. Creating a DCM Project Folder

Create a single DCM project folder in the Snowsight Workspace. Within this project folder, define two targets (DEV / PROD) in manifest.yml and deploy the same set of definition files to each environment.

In the created Workspace, click the "+" button and select "DCM Project".

Check "Define default target environment" and configure as shown below. Select the database and schema created earlier, and set the Target name to "DEV".

The folders and files are automatically generated as shown below. The manifest.yml also contains the role, database, and schema information configured earlier.

Note: In DCM Projects, the DCM project folder (the set of definition files consisting of manifest.yml + sources/definitions/ in the Workspace) and the DCM project object (a Snowflake schema-level object that is the target of deployment) are separate concepts. A single project folder can have multiple targets, each deploying to a different DCM project object. If you deploy different configurations to a single DCM project object, objects no longer included in the rendered result may become drop targets. Therefore, when managing multiple environments like DEV/PROD, specify a separate DCM project object for each environment.

2. Deleting the Default .sql Files

Since we have a specific configuration for this verification, delete the default .sql files that were generated.

For reference, let's review what each file contains before deleting them.

sources/definitions/examples.sql

This file contains the most common syntax for defining objects using DCM Projects. The key point is defining each object's settings with define.

sources/definitions/jinja_demo.sql

Since DCM Projects supports Jinja2 templates, you can programmatically define objects using for and if. This file demonstrates creating multiple team tables by looping through the teams values defined in manifest.yml.

sources/macros/grants_macro.sql

Since Jinja2 templates are available, you can also create macros like in dbt. This macro creates DEVELOPER and USAGE roles for a given team and grants privileges to each.

Folder Structure After Deleting Default .sql Files

It should look like the image below. The macros folder was also deleted since it's not needed for this exercise.

Note: tmp.sql is a file where I wrote the queries to create the database / schema earlier.

3. Configuring manifest.yml (DEV/PROD Targets)

To separate DEV/PROD environments at the database level within the same account, configure manifest.yml as follows:

manifest_version: 2
type: DCM_PROJECT

default_target: DEV

targets:
  DEV:
    account_identifier: MY_ACCOUNT
    project_name: DCM_ADMIN.PROJECTS.MY_DCM_PROJECT_DEV
    project_owner: ACCOUNTADMIN
    templating_config: DEV
  PROD:
    account_identifier: MY_ACCOUNT
    project_name: DCM_ADMIN.PROJECTS.MY_DCM_PROJECT_PROD
    project_owner: ACCOUNTADMIN
    templating_config: PROD

templating:
  defaults:
    wh_auto_suspend: 60
  configurations:
    DEV:
      db_name: DEV_DB
      wh_name: DEV_WH
      wh_size: X-SMALL
      role_name: DEV_EXPLORER_ROLE
    PROD:
      db_name: PROD_DB
      wh_name: PROD_WH
      wh_size: SMALL
      role_name: PROD_EXPLORER_ROLE

Key points:

Under targets, specify account_identifier (same value since it's the same account), project_name (fully qualified name of the DCM project object), project_owner (owner role during deployment), and templating_config (configuration name to use)
Define default values in templating.defaults and environment-specific values in templating.configurations
Variable resolution order is three stages: "defaults → configuration → runtime override"

Note: Since this article includes role creation via DEFINE ROLE, both DEV and PROD use project_owner: ACCOUNTADMIN to satisfy the CREATE ROLE privilege requirement. This is to simplify the verification and is not a recommendation to routinely use ACCOUNTADMIN.

The official docs' CI/CD best practices advise against using general administrator roles (like ACCOUNTADMIN) as the project owner in production. In practice, consider creating dedicated deployment roles like DCM_DEV_DEPLOYER / DCM_PROD_DEPLOYER or service users for each environment.

Also, by default, the role that executes the deploy (project owner) holds OWNERSHIP of the deployed objects. In this article's setup, all objects after deploy are owned by ACCOUNTADMIN. In practice, consider a setup where a dedicated deployment role is the owner, and plan your GRANT OWNERSHIP design accordingly. Note that if the project owner transfers OWNERSHIP to another role it doesn't hold, it may become unable to continue managing that object in subsequent deploys.

4. Defining Databases, Schemas, and Warehouses

Now let's start defining the objects.

Create definition files for databases, schemas, and warehouses under the sources/definitions/ directory.

Since the Jinja2 template variable {{ db_name }} is used, it automatically expands to DEV_DB for the DEV configuration and PROD_DB for the PROD configuration.

sources/definitions/databases.sql

DEFINE DATABASE {{ db_name }};

sources/definitions/schemas.sql

DEFINE SCHEMA {{ db_name }}.RAW;

DEFINE SCHEMA {{ db_name }}.STG;

DEFINE SCHEMA {{ db_name }}.MART;

The warehouse size is also parameterized with {{ wh_size }} from manifest.yml, so DEV gets X-SMALL and PROD gets SMALL. {{ wh_auto_suspend }} uses the shared value defined in templating.defaults.

sources/definitions/warehouses.sql

DEFINE WAREHOUSE {{ wh_name }}
  WITH
    WAREHOUSE_SIZE = '{{ wh_size }}'
    AUTO_SUSPEND = {{ wh_auto_suspend }}
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE;

5. Defining Roles and GRANTs

Create a data exploration role for each environment and grant the necessary privileges. With the template variable {{ role_name }}, DEV_EXPLORER_ROLE is created for DEV and PROD_EXPLORER_ROLE for PROD.

In DCM Projects, you can write both DEFINE statements and GRANT statements in the same file.

sources/definitions/roles.sql

DEFINE ROLE {{ role_name }};

-- USAGE privilege on database
GRANT USAGE ON DATABASE {{ db_name }} TO ROLE {{ role_name }};

-- USAGE privilege on each schema
GRANT USAGE ON SCHEMA {{ db_name }}.RAW TO ROLE {{ role_name }};
GRANT USAGE ON SCHEMA {{ db_name }}.STG TO ROLE {{ role_name }};
GRANT USAGE ON SCHEMA {{ db_name }}.MART TO ROLE {{ role_name }};

-- RAW schema: SELECT privilege on tables (read-only)
GRANT SELECT ON ALL TABLES IN SCHEMA {{ db_name }}.RAW TO ROLE {{ role_name }};

-- STG/MART schemas: SELECT privilege on Dynamic Tables (read-only)
GRANT SELECT ON ALL DYNAMIC TABLES IN SCHEMA {{ db_name }}.STG TO ROLE {{ role_name }};
GRANT SELECT ON ALL DYNAMIC TABLES IN SCHEMA {{ db_name }}.MART TO ROLE {{ role_name }};

-- USAGE privilege on warehouse
GRANT USAGE ON WAREHOUSE {{ wh_name }} TO ROLE {{ role_name }};

-- Role hierarchy to SYSADMIN
GRANT ROLE {{ role_name }} TO ROLE SYSADMIN;

Note: DCM Projects tracks only GRANTs deployed through DCM Projects. If you remove a GRANT statement from the definition files, that GRANT will become a revoke target on the next Deploy.

6. Defining Tables

Define tables in the RAW schema to store e-commerce order data.

sources/definitions/raw_tables.sql

DEFINE TABLE {{ db_name }}.RAW.CUSTOMERS (
  CUSTOMER_ID INT,
  CUSTOMER_NAME VARCHAR(100),
  EMAIL VARCHAR(200),
  REGION VARCHAR(50),
  CREATED_AT TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

DEFINE TABLE {{ db_name }}.RAW.ORDERS (
  ORDER_ID INT,
  CUSTOMER_ID INT,
  ORDER_DATE DATE,
  ORDER_STATUS VARCHAR(20),
  TOTAL_AMOUNT DECIMAL(10,2),
  CREATED_AT TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

DEFINE TABLE {{ db_name }}.RAW.ORDER_ITEMS (
  ORDER_ITEM_ID INT,
  ORDER_ID INT,
  PRODUCT_NAME VARCHAR(200),
  QUANTITY INT,
  UNIT_PRICE DECIMAL(10,2),
  CREATED_AT TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

7. Defining an Internal Stage

Define an Internal Stage for data ingestion. Directory table settings can also be configured.

sources/definitions/stages.sql

DEFINE STAGE {{ db_name }}.RAW.DATA_STAGE
  DIRECTORY = (ENABLE = TRUE);

8. Defining Dynamic Tables

Define Dynamic Tables in the STG and MART schemas to build a RAW → STG → MART pipeline.

In this three-layer architecture, the STG layer performs data cleansing (TRIM, LOWER, UPPER, etc.) and the MART layer performs aggregation and joins. STG layer Dynamic Tables are set to TARGET_LAG = DOWNSTREAM, and MART layer tables to TARGET_LAG = '1 hour'.

sources/definitions/stg_dynamic_tables.sql

DEFINE DYNAMIC TABLE {{ db_name }}.STG.STG_CUSTOMERS
  WAREHOUSE = {{ wh_name }}
  TARGET_LAG = DOWNSTREAM
  AS
    SELECT
      CUSTOMER_ID,
      TRIM(CUSTOMER_NAME) AS CUSTOMER_NAME,
      LOWER(EMAIL) AS EMAIL,
      UPPER(REGION) AS REGION,
      CREATED_AT
    FROM {{ db_name }}.RAW.CUSTOMERS;

DEFINE DYNAMIC TABLE {{ db_name }}.STG.STG_ORDERS
  WAREHOUSE = {{ wh_name }}
  TARGET_LAG = DOWNSTREAM
  AS
    SELECT
      O.ORDER_ID,
      O.CUSTOMER_ID,
      O.ORDER_DATE,
      UPPER(O.ORDER_STATUS) AS ORDER_STATUS,
      O.TOTAL_AMOUNT,
      O.CREATED_AT
    FROM {{ db_name }}.RAW.ORDERS O;

DEFINE DYNAMIC TABLE {{ db_name }}.STG.STG_ORDER_ITEMS
  WAREHOUSE = {{ wh_name }}
  TARGET_LAG = DOWNSTREAM
  AS
    SELECT
      OI.ORDER_ITEM_ID,
      OI.ORDER_ID,
      TRIM(OI.PRODUCT_NAME) AS PRODUCT_NAME,
      OI.QUANTITY,
      OI.UNIT_PRICE,
      OI.QUANTITY * OI.UNIT_PRICE AS LINE_TOTAL,
      OI.CREATED_AT
    FROM {{ db_name }}.RAW.ORDER_ITEMS OI;

sources/definitions/mart_dynamic_tables.sql

DEFINE DYNAMIC TABLE {{ db_name }}.MART.MART_CUSTOMER_ORDERS
  WAREHOUSE = {{ wh_name }}
  TARGET_LAG = '1 hour'
  AS
    SELECT
      C.CUSTOMER_ID,
      C.CUSTOMER_NAME,
      C.EMAIL,
      C.REGION,
      COUNT(O.ORDER_ID) AS TOTAL_ORDERS,
      SUM(O.TOTAL_AMOUNT) AS TOTAL_SPENT,
      MIN(O.ORDER_DATE) AS FIRST_ORDER_DATE,
      MAX(O.ORDER_DATE) AS LAST_ORDER_DATE
    FROM {{ db_name }}.STG.STG_CUSTOMERS C
    LEFT JOIN {{ db_name }}.STG.STG_ORDERS O
      ON C.CUSTOMER_ID = O.CUSTOMER_ID
    GROUP BY C.CUSTOMER_ID, C.CUSTOMER_NAME, C.EMAIL, C.REGION;

DEFINE DYNAMIC TABLE {{ db_name }}.MART.MART_PRODUCT_SALES
  WAREHOUSE = {{ wh_name }}
  TARGET_LAG = '1 hour'
  AS
    SELECT
      OI.PRODUCT_NAME,
      COUNT(DISTINCT OI.ORDER_ID) AS ORDER_COUNT,
      SUM(OI.QUANTITY) AS TOTAL_QUANTITY,
      SUM(OI.LINE_TOTAL) AS TOTAL_REVENUE,
      AVG(OI.UNIT_PRICE) AS AVG_UNIT_PRICE
    FROM {{ db_name }}.STG.STG_ORDER_ITEMS OI
    GROUP BY OI.PRODUCT_NAME;

9. Defining Tasks (Data Ingestion via COPY INTO)

Define tasks using DEFINE TASK to load CSV files uploaded to the Internal Stage into each table.

Each task is configured with a CRON schedule to run daily at 2:00 AM (JST). With PURGE = TRUE specified, files on the stage are automatically deleted after a successful COPY INTO.

Note that tasks are created in a suspended state when deployed.

Note: File Format is not included in DCM Projects' supported objects (as of March 20, 2026), so it cannot be defined with DEFINE FILE FORMAT. In this article, format options are specified inline in the FILE_FORMAT of the COPY INTO statement to avoid a separate File Format object creation step. For objects not supported by DEFINE, you can supplement them with separate SQL scripts (EXECUTE IMMEDIATE FROM or snow sql) after DCM PLAN/DEPLOY.

sources/definitions/tasks.sql

-- COPY INTO task for CUSTOMERS table
DEFINE TASK {{ db_name }}.RAW.LOAD_CUSTOMERS
  WAREHOUSE = {{ wh_name }}
  SCHEDULE = 'USING CRON 0 2 * * * Asia/Tokyo'
  AS
    COPY INTO {{ db_name }}.RAW.CUSTOMERS
    FROM @{{ db_name }}.RAW.DATA_STAGE/customers
    FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"' SKIP_HEADER = 1 NULL_IF = ('NULL', 'null', ''))
    ON_ERROR = 'CONTINUE'
    PURGE = TRUE;

-- COPY INTO task for ORDERS table
DEFINE TASK {{ db_name }}.RAW.LOAD_ORDERS
  WAREHOUSE = {{ wh_name }}
  SCHEDULE = 'USING CRON 0 2 * * * Asia/Tokyo'
  AS
    COPY INTO {{ db_name }}.RAW.ORDERS
    FROM @{{ db_name }}.RAW.DATA_STAGE/orders
    FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"' SKIP_HEADER = 1 NULL_IF = ('NULL', 'null', ''))
    ON_ERROR = 'CONTINUE'
    PURGE = TRUE;

-- COPY INTO task for ORDER_ITEMS table
DEFINE TASK {{ db_name }}.RAW.LOAD_ORDER_ITEMS
  WAREHOUSE = {{ wh_name }}
  SCHEDULE = 'USING CRON 0 2 * * * Asia/Tokyo'
  AS
    COPY INTO {{ db_name }}.RAW.ORDER_ITEMS
    FROM @{{ db_name }}.RAW.DATA_STAGE/order_items
    FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"' SKIP_HEADER = 1 NULL_IF = ('NULL', 'null', ''))
    ON_ERROR = 'CONTINUE'
    PURGE = TRUE;

10. Plan & Deploy to the DEV Target

Now that the definition files are ready, let's run Plan against the DEV target first.

In the Snowsight Workspace DCM Project screen, switch the target to "DEV", ensure "Plan" is selected, and click the play button.

A list of objects to be created is displayed. Clicking on each object shows the detailed configuration values.

By the way, clicking Summarize in the upper right provides a summary of what changes will be made.

If everything looks good, click the "Deploy" button. According to the official docs, the "Alias" is described as Think of the deployment alias like a commit message for your code change., so I entered "first deploy".

When the deployment completes successfully, it displays as shown below.

After deployment, verify that the objects have been created. (Below is an example using queries.)

USE DATABASE DEV_DB;
SHOW SCHEMAS;
SHOW TABLES IN SCHEMA RAW;
SHOW TASKS IN SCHEMA RAW;
SHOW DYNAMIC TABLES IN SCHEMA STG;
SHOW DYNAMIC TABLES IN SCHEMA MART;

-- Verify roles and grants
SHOW ROLES LIKE 'DEV_EXPLORER_ROLE';
SHOW GRANTS TO ROLE DEV_EXPLORER_ROLE;

11. Loading Sample Data and Verifying Operation

Preparing Sample CSVs

Load sample data into the DEV environment to verify that the COPY INTO tasks and Dynamic Table pipeline work correctly.

First, prepare the following three CSV files locally.

customers.csv

CUSTOMER_ID,CUSTOMER_NAME,EMAIL,REGION,CREATED_AT
1,Taro Tanaka,tanaka@example.com,tokyo,2025-12-01 10:00:00
2,Hanako Sato,sato@example.com,osaka,2025-12-05 11:30:00
3,Ichiro Suzuki,suzuki@example.com,tokyo,2025-12-10 09:15:00
4,Misaki Takahashi,takahashi@example.com,fukuoka,2025-12-15 14:00:00
5,Kenta Ito,ito@example.com,osaka,2025-12-20 16:45:00
6,Yumi Watanabe,watanabe@example.com,nagoya,2026-01-03 08:30:00
7,Takuya Yamamoto,yamamoto@example.com,tokyo,2026-01-08 13:00:00
8,Sakura Nakamura,nakamura@example.com,sapporo,2026-01-12 10:20:00
9,Daisuke Kobayashi,kobayashi@example.com,fukuoka,2026-01-18 15:10:00
10,Ai Kato,kato@example.com,tokyo,2026-01-25 11:00:00

orders.csv

ORDER_ID,CUSTOMER_ID,ORDER_DATE,ORDER_STATUS,TOTAL_AMOUNT,CREATED_AT
1001,1,2026-01-15,completed,15000.00,2026-01-15 10:30:00
1002,2,2026-01-18,completed,8500.00,2026-01-18 14:00:00
1003,1,2026-01-22,completed,23000.00,2026-01-22 09:45:00
1004,3,2026-02-01,completed,4200.00,2026-02-01 11:20:00
1005,4,2026-02-05,shipped,31500.00,2026-02-05 16:00:00
1006,5,2026-02-10,completed,9800.00,2026-02-10 13:30:00
1007,2,2026-02-14,completed,12000.00,2026-02-14 10:15:00
1008,6,2026-02-20,shipped,7600.00,2026-02-20 15:45:00
1009,7,2026-03-01,completed,18500.00,2026-03-01 08:00:00
1010,8,2026-03-05,pending,5400.00,2026-03-05 12:30:00
1011,3,2026-03-08,completed,11200.00,2026-03-08 14:20:00
1012,9,2026-03-10,shipped,26800.00,2026-03-10 09:00:00
1013,10,2026-03-12,completed,3900.00,2026-03-12 11:45:00
1014,1,2026-03-15,pending,14700.00,2026-03-15 16:30:00
1015,5,2026-03-18,completed,8900.00,2026-03-18 10:00:00

order_items.csv

ORDER_ITEM_ID,ORDER_ID,PRODUCT_NAME,QUANTITY,UNIT_PRICE,CREATED_AT
1,1001,Wireless Earbuds,1,8000.00,2026-01-15 10:30:00
2,1001,Smartphone Case,2,3500.00,2026-01-15 10:30:00
3,1002,USB Cable,5,1700.00,2026-01-18 14:00:00
4,1003,Monitor Arm,1,15000.00,2026-01-22 09:45:00
5,1003,Mouse Pad,2,4000.00,2026-01-22 09:45:00
6,1004,Keyboard Cover,3,1400.00,2026-02-01 11:20:00
7,1005,4K Monitor,1,29000.00,2026-02-05 16:00:00
8,1005,HDMI Cable,1,2500.00,2026-02-05 16:00:00
9,1006,Wireless Mouse,2,4900.00,2026-02-10 13:30:00
10,1007,Webcam,1,12000.00,2026-02-14 10:15:00
11,1008,USB Hub,2,3800.00,2026-02-20 15:45:00
12,1009,Mechanical Keyboard,1,18500.00,2026-03-01 08:00:00
13,1010,Mouse Pad,1,4000.00,2026-03-05 12:30:00
14,1010,Keyboard Cover,1,1400.00,2026-03-05 12:30:00
15,1011,Wireless Earbuds,1,8000.00,2026-03-08 14:20:00
16,1011,USB Cable,2,1600.00,2026-03-08 14:20:00
17,1012,4K Monitor,1,29000.00,2026-03-10 09:00:00
18,1013,Smartphone Case,1,3900.00,2026-03-12 11:45:00
19,1014,Webcam,1,12000.00,2026-03-15 16:30:00
20,1014,USB Cable,3,900.00,2026-03-15 16:30:00
21,1015,Wireless Mouse,1,4900.00,2026-03-18 10:00:00
22,1015,Mouse Pad,1,4000.00,2026-03-18 10:00:00

Uploading CSV Files

Upload the prepared CSV files to the internal stage from Snowsight.

Since the task definitions specify sub-paths like FROM @{{ db_name }}.RAW.DATA_STAGE/customers, separate the upload path for each file:

customers.csv → Path: /customers/
orders.csv → Path: /orders/
order_items.csv → Path: /order_items/

After uploading, verify that the files are placed on the stage. You should see three CSV files in their respective sub-paths.

LIST @DEV_DB.RAW.DATA_STAGE;

Resuming and Manually Executing Tasks

Tasks are in a suspended state (state is suspended) when deployed. First, check the status with SHOW TASKS.

SHOW TASKS IN SCHEMA DEV_DB.RAW;

You can confirm that the state column shows suspended.

To enable the tasks, run ALTER TASK ... RESUME. Since these are three independent tasks (no parent-child relationship), resume each one individually.

ALTER TASK DEV_DB.RAW.LOAD_CUSTOMERS RESUME;
ALTER TASK DEV_DB.RAW.LOAD_ORDERS RESUME;
ALTER TASK DEV_DB.RAW.LOAD_ORDER_ITEMS RESUME;

Run SHOW TASKS again and confirm the state column shows started.

SHOW TASKS IN SCHEMA DEV_DB.RAW;

The task schedule is daily at 2:00 AM (JST), but for verification purposes, manually execute them with EXECUTE TASK.

EXECUTE TASK DEV_DB.RAW.LOAD_CUSTOMERS;
EXECUTE TASK DEV_DB.RAW.LOAD_ORDERS;
EXECUTE TASK DEV_DB.RAW.LOAD_ORDER_ITEMS;

Verify that data has been loaded into each table.

SELECT COUNT(*) FROM DEV_DB.RAW.CUSTOMERS;  -- 10 rows
SELECT COUNT(*) FROM DEV_DB.RAW.ORDERS;     -- 15 rows
SELECT COUNT(*) FROM DEV_DB.RAW.ORDER_ITEMS; -- 22 rows

Note: As verified in the bonus section below, the official docs state: When definition changes are deployed for a task that is already started, Snowflake automatically suspends that task (or its root task) temporarily, applies the change, and then resumes it again. This means you don't need to manually suspend tasks even when modifying task definitions through DCM. This is fantastic!

Bulk-Refreshing Dynamic Tables with REFRESH ALL

After executing COPY INTO, you could wait for Dynamic Tables to auto-refresh based on TARGET_LAG, but DCM Projects' REFRESH ALL command lets you immediately bulk-refresh all Dynamic Tables under the project.

https://docs.snowflake.com/en/user-guide/dcm-projects/dcm-projects-pipelines

When executed via SQL, it looks like this. REFRESH ALL triggers a refresh for all Dynamic Tables managed by the project and their upstream Dynamic Tables. The result is returned in JSON format, showing inserted_rows, deleted_rows, and data_timestamp (a timestamp indicating data freshness) for each Dynamic Table.

EXECUTE DCM PROJECT DCM_ADMIN.PROJECTS.MY_DCM_PROJECT_DEV
  REFRESH ALL;

Verify that data has been reflected in the MART layer Dynamic Tables. Customer order summaries and product sales summaries should display correctly.

SELECT * FROM DEV_DB.MART.MART_CUSTOMER_ORDERS ORDER BY TOTAL_SPENT DESC;

SELECT * FROM DEV_DB.MART.MART_PRODUCT_SALES ORDER BY TOTAL_REVENUE DESC;

12. Plan & Deploy to the PROD Target

Next, deploy to the PROD target in the same manner. Since the PROD target specifies MY_DCM_PROJECT_PROD as a separate DCM project object in manifest.yml, the DEV environment is not affected.

First, run the following query to create the PROD DCM project object.

USE ROLE ACCOUNTADMIN;

CREATE DCM PROJECT IF NOT EXISTS DCM_ADMIN.PROJECTS.MY_DCM_PROJECT_PROD
    COMMENT = 'DCM project for PROD';

Next, in the Workspace DCM Project screen, switch the target to "PROD", ensure "Plan" is selected, and click the play button. (If the PROD DCM project object is not recognized and an error occurs, refreshing the browser page should resolve it.)

Similar to DEV, the objects are displayed as new creation diffs against PROD_DB. Confirm that the warehouse size is SMALL.

If everything looks good, click the "Deploy" button to execute the deployment.

After the deployment completes successfully, verify the PROD environment objects. Confirm that PROD_DB has the same schema structure and objects as DEV_DB, the warehouse PROD_WH size is SMALL, and PROD_EXPLORER_ROLE has been granted privileges on each object.

USE DATABASE PROD_DB;
SHOW SCHEMAS;
SHOW TABLES IN SCHEMA RAW;
SHOW TASKS IN SCHEMA RAW;
SHOW DYNAMIC TABLES IN SCHEMA STG;
SHOW DYNAMIC TABLES IN SCHEMA MART;
SHOW WAREHOUSES LIKE 'PROD_WH';

-- Verify roles and grants
SHOW ROLES LIKE 'PROD_EXPLORER_ROLE';
SHOW GRANTS TO ROLE PROD_EXPLORER_ROLE;

This confirms that using a single DCM project folder (set of definition files), we can deploy the same configuration to separate DCM project objects for DEV and PROD by switching template variables!

Note: Task enablement and Dynamic Table refresh for the PROD environment need to be done separately.

Bonus: What Happens When You Modify or Remove Objects from Definition Files

In DCM Projects, removing a DEFINE statement from definition files is expected to make that object a drop target on the next Deploy. Let's verify this behavior.

Change #1: Commenting Out the Warehouse Definition

Comment out the contents of sources/definitions/warehouses.sql.

Change #2: Modifying a Task Definition

Change just one task to run at 3:00AM daily instead of 2:00 AM.

Running Plan

Run Plan against the DEV target.

The Plan result is shown below. DEV_WH is displayed as a DROP target, and the task shows as an ALTER target with clear visibility into which configuration values will change.

Running Deploy

Proceed with the Deploy. (As a bonus note, the Alias also works fine with Japanese characters.)

After Deploy, verify whether the warehouse was actually deleted. As shown below, nothing is returned, confirming it has been dropped.

SHOW WAREHOUSES LIKE 'DEV_WH';

For the task, we can confirm that the configuration value has been updated and the state remains started. This is fantastic!

Conclusion

I tried building DEV/PROD environments within a single Snowflake account using Snowflake DCM Projects.

What stood out most from hands-on experience was how incredibly simple it is to separate DEV/PROD environments using template variables. Simply defining db_name and wh_size in manifest.yml's templating.configurations lets you deploy environments with different parameters from the same set of definition files. (If you're familiar with dbt, you'll find the onboarding very smooth.)

Another great aspect is that the Plan → Deploy safety workflow is natively built in. If you're accustomed to Terraform's plan → apply, you can use it with the same mindset. The integration of Dynamic Table REFRESH ALL and data quality test TEST ALL as DCM Project features is also very convenient for data pipeline management.

On the other hand, it's important to note that some objects like File Format are not yet supported by DCM management, and as a Preview feature, there are various limitations including rename restrictions (as of March 20, 2026).

That said, this is a feature I'm very excited to see evolve with future updates!!

Automatically Generating Omni Semantic Layer YAML from Tableau .twb Files Using Claude Code

Sagara — Mon, 09 Mar 2026 05:06:05 +0000

This is an English translation of the original Japanese article published at: https://dev.classmethod.jp/articles/try-twb-to-omni-yaml-with-claude-code/
Please note that some images contain Japanese text, as they are from the original article.

When considering adopting a new BI tool like Omni, the biggest hurdle is usually "how to migrate from your existing BI tool."

However, with the recent advancement of AI technologies like Claude Code, migrating between code-based and text-based content is becoming surprisingly feasible.

With that in mind, I tried using Claude Code to automatically generate Omni's Semantic Layer YAML code from Tableau's .twb files, assuming a Tableau-to-Omni migration scenario. This article summarizes what I did and what I learned.

Note: This does not cover dashboard migration itself. The scope is limited to "generating Omni YAML code and creating Markdown instructions for how to build charts in Omni." If Omni dashboards become definable via code in the future, that could change things significantly.

Prerequisites

DWH: Snowflake (AWS Tokyo region, Enterprise edition)
- Starting from a .twb file connected to Snowflake via Tableau, I created a Connection and base Model in Omni, then attempted the migration.
Claude Code: 2.1.70 (Pro plan, using Opus 4.6)
Python environment: uv 0.10.9
- Python is required because the Skill I created uses Python scripts.
Tableau Desktop: 2025.3.3
Local environment: Ubuntu 24.04 LTS (via WSL2)

Preparation

First, I set things up on the Omni, Tableau, and Claude Code sides respectively.

Omni

For Omni preparation, I created a Connection to the same Snowflake instance used in Tableau, created a base Model, and set up Git integration so that Claude Code could easily edit the Model files.

Tableau

I prepared a .twb file with the following characteristics:

Relationships defined across 4 tables, with a data source filter restricting to the last 180 days

Custom dimensions, measures, and parameters added (the Japanese-labeled dimensions are the custom ones)
- Including LoD (Level of Detail) fields such as 地域別_平均フルフィル日数 (Average Fulfillment Days by Region)

A dashboard created within the workbook

Installing model-local-editor

Since Omni is typically developed within its in-browser IDE, you need to install model-local-editor to validate locally developed content on the Omni instance.

This tool runs as a persistent process, and any files edited during that time are automatically synced to the Omni instance.

I've written a separate blog post on how to use it, so please refer to it for details:

https://dev.to/sagara/omnis-model-local-editor-sync-your-locally-developed-semantic-layer-code-to-your-omni-instance-5034

Claude Code (Skill Implementation)

I created a Skill to generate Omni YAML from Tableau .twb files. The implementation approach was to have Claude Code's skill-creator Skill generate a base, then iterate through trial and error to refine it.

The Skill details are published in the following repository:

Note: This Skill does not guarantee perfect conversion of all .twb files to Omni's Semantic Layer. Please use it with that caveat in mind. Also, the Markdown descriptions within the linked Skill are written in Japanese.

https://github.com/SSchneider22/tableau-twb-to-omni-semantic-layer

Auto-Generating Omni YAML Files from .twb Files Using Claude Code

I cloned the Git-integrated Omni repository to my local environment and created a branch. Then I created a tableau folder.

Inside it, I placed the target Tableau workbook file (.twb file) for migration.

Next, I started model-local-editor so that local edits would be reflected on the Omni instance.

Then I launched Claude Code and ran /skills to verify that the Skill created during preparation was properly loaded. If it displays as shown below, everything is fine.

With that confirmed, I submitted the following prompt in Plan mode (adjust folder paths to match your setup):

Using the Skill "tableau-twb-to-omni-semantic-layer", generate all Omni migration files based on the .twb file in the develop/manufacturing_dataops_test/tableau folder.

Claude Code created a plan as shown below, and I then asked it to generate the code.

After code generation was complete, I reviewed the generated code. Here are some examples of what was produced:

Dimensions/measures/filters definitions added to existing View files

Topics generated per data source

Markdown files with step-by-step instructions for reproducing each Tableau worksheet/dashboard in Omni

After this, I performed Git operations to update the main branch with the latest content.

Verifying on Omni

Since I was using model-local-editor, the code generated by Claude Code was already reflected in Omni on the specified branch.

From this state, I clicked Explore and followed the Markdown file created by Claude Code to see if I could reproduce the charts originally built in Tableau.

When reproducing each Query as a chart, I confirmed that Tableau's data source filters were applied as Topic default filters, and that the parameter for switching measures and LoD fields all worked as expected.

Finally, I also created a dashboard. While the positions of filters and legends differ, the functionality covers the same content as the Tableau dashboard.

Bonus: Examples of Issues Encountered During Testing

This article highlights the successful results, but during testing I encountered various issues and had to repeatedly feed error logs back to Claude Code to refine the Skill. (I went through this code generation → Skill refinement loop at least 10+ times...)

Here are some examples of actual issues I encountered:

The generated Omni Semantic Layer code used non-existent parameters, causing error logs during model-local-editor sync
- Example 1: Specifying non-existent values for aggregation_type
- Example 2: Not creating LoD fields according to Omni's syntax
Even when sync succeeded, errors/warnings appeared on the Omni UI
- Example 1: Fields used in LoD calculations were not included in the Topic's field list
- Example 2: Filter fields already defined under filters: were also duplicated under dimensions:
- Example 3: A measure with aggregate_type: count_distinct also had COUNT DISTINCT in its sql: definition

Conclusion

I tried using Claude Code to automatically generate Omni Semantic Layer YAML code from Tableau .twb files.

While I haven't achieved automatic dashboard migration, being able to automate the generation of Semantic Layer definitions, Topics, and even Markdown instructions for reproducing charts in Omni gave me a strong sense that a significant portion of migration work can be streamlined.

In particular, the fact that complex Tableau definitions like data source filters, LoD fields, and parameters could be reproduced on the Omni side through iterative Skill improvements was a major win.

That said, perfect code isn't generated on the first try — the Skill required many rounds of refinement. While it worked well for the .twb file I tested, other .twb files may require additional adjustments.

Nevertheless, for BI tool migrations — which tend to be daunting undertakings — the advancement of AI technology is making "migration between code-based artifacts" an increasingly realistic option.

I hope this article helps anyone considering a BI tool migration.

Omni's model-local-editor: Sync Your Locally Developed Semantic Layer Code to Your Omni Instance

Sagara — Fri, 06 Mar 2026 09:34:24 +0000

This is an English translation of the original Japanese article below:
https://dev.classmethod.jp/articles/try-model-local-editor/

This is Sagara.

In Omni, you can create Semantic Layer code through GUI operations on the instance itself. However, with the recent rise of AI coding agents like Claude Code, there are increasing cases where you'd want to develop Omni's Semantic Layer locally.

I actually faced this exact situation. I developed Omni's Semantic Layer code locally, issued a pull request, and merged it into the main branch — only to find that the latest code wasn't reflected on the Omni instance. (I also tried using the API, but it didn't work as expected.)

When I consulted with an Omni engineer about this, they told me about a package called "model-local-editor". By using this package, you can sync locally developed Omni Semantic Layer code to your Omni instance.

https://www.npmjs.com/package/@omni-co/model-local-editor

In this article, I'll walk through my experience trying out this package.

Prerequisites

OS: Ubuntu 24.04 LTS (running on WSL2)
Package manager: npm 11.11.0
model-local-editor: 0.2.0

I'm assuming that the Model you develop in Omni is connected to Git, and you've cloned that repository locally for development. (For Omni setup details, please refer to this blog post.)

Also, I'm assuming this repository manages not only Omni code but also dbt code and other resources.

Preparation

Installing model-local-editor

First, install model-local-editor:

npm install -g @omni-co/model-local-editor

Setting Environment Variables

Set the following two environment variables. (If you want them to persist, consider adding them to ~/.bashrc or a .env file.)

export OMNI_API_KEY="your-api-key-here"
export OMNI_BASE_URL="https://my-org.omniapp.co"  # or your Omni instance URL

Trying It Out

Now that preparation is complete, let's sync to the Omni instance using model-local-editor.

Recommended: Create a Local Git Branch with the Same Name as the Omni Branch

Before using model-local-editor, I recommend creating a local branch with the same name as the branch you'll create on the Omni side.

The reason is that model-local-editor only creates a branch on the Omni instance side, so the sync process will push to the specified branch regardless of which local branch you're on.

For this walkthrough, I'll create and switch to a branch called 20260306-local-sync-test locally:

git switch -c 20260306-local-sync-test

Initial Setup and Branch Creation

Run the following command to specify the Omni Model, use the --dir option to point to the folder path managing the target Model, and create a new branch on Omni. (You can find the model-id from the URL bar when you open the target Model.)

For other options, please refer to the package documentation.

omni-sync init <model-id> --dir <folder-path-managing-the-target-Omni-Model> --branch <branch-name-to-create-on-Omni> --create-branch

Running this command displays the following:

Additionally, a .omni-sync.json file is created in the directory specified by the --dir option. This file tracks the change history of each file. (This file is used internally by model-local-editor, so users generally should not edit it directly.)

When you open the target Model in Omni and view the branch list, you can see that the 20260306-local-sync-test branch (specified with --branch 20260306-local-sync-test) has been created.

Syncing Local Edits to Omni

Next, let's sync locally edited content to Omni.

First, run the following command locally to start and keep the model-local-editor sync process running. Be careful to include the --dir option — without it, Omni's folders and files will be automatically moved to the root of your repository.

omni-sync start <model-id> --dir <folder-path-managing-the-target-Omni-Model> --branch <branch-name-specified-in-omni-sync-init>

You'll see output like the following. Once File watcher ready appears at the end, you're good to go. The key point is to keep this process running.

With the process running, edit and save any Omni file, and you'll see log output like this:

Now, when you switch to the branch on the Omni instance and check the target file, you'll see it has been synced! (Note: there were a few cases where syncing took some time, possibly due to browser caching.)

This means you can develop locally and immediately verify your changes on the Omni instance.

Bonus: I Recommend Creating Pull Requests from the Omni UI

By now, you should have a good picture of how to use model-local-editor to develop Omni code locally and verify it on the Omni instance.

However, when it comes to creating pull requests after you've finished development, I recommend issuing pull requests from the Omni instance UI. The reason is that even if you create a pull request and merge it from your local environment, the main branch code on the Omni instance won't be updated...

Below is the flow of creating a pull request from the Omni UI. You can see that the dimension updated_at_20260306 that was added earlier gets merged into the main branch and is reflected in the Shared model on the Omni instance (the shared model that syncs with the main branch code).

Bonus: If There Are Syntax Errors

If the file you want to sync to Omni has syntax errors, it will be displayed as shown below.

Conclusion

I tried out "model-local-editor," which lets you sync locally developed Omni code with your Omni instance.

While Omni's built-in IDE is excellent, as someone who has recently fallen in love with Claude Code, I found this capability to develop locally and immediately verify on the Omni instance incredibly convenient!

If you're using coding agents locally to develop Omni's Semantic Layer, I highly recommend giving this package a try.

Snowflake's New Feature: AI-Powered Data Quality Checks You Can Set Up Directly in Snowsight

Sagara — Thu, 26 Feb 2026 01:40:17 +0000

This is an English translation of the original Japanese article: https://dev.classmethod.jp/articles/snowflake-setup-data-quality-checks-in-snowsight/

Snowflake has released a new preview feature that allows you to set up data quality checks directly from the Snowsight UI. This feature leverages Cortex AI to automatically suggest quality checks, and also supports manual quality check definitions — all through the Snowsight GUI.

I tried it out, so let me walk you through how it works.

What Is Data Quality Check Setup in Snowsight?

Previously, to perform data quality checks in Snowflake, you had to define and configure Data Metric Functions (DMFs) using SQL. With this new feature, the following capabilities are now available directly from the Snowsight GUI:

Cortex Data Quality (AI-suggested): Cortex AI analyzes metadata characteristics and data usage patterns to automatically suggest quality check definitions (DMFs). Once you accept the suggestions, it periodically detects data quality issues.
Manual definition: You can manually define quality check types and criteria based on your own knowledge of the data.
Execution schedule adjustment: You can configure the execution frequency of quality checks using time-based, schedule-based, or DML trigger-based settings.

Cortex Data Quality leverages Snowflake Cortex's AI_COMPLETE function, and all data and metadata remain securely within Snowflake. It also fully respects Snowflake's access control — suggestions are made based only on data the user has access to.

Prerequisites

Quoting from the official documentation, you need to be aware of the following regarding editions, roles, and permissions:

Snowflake Edition: Enterprise or higher
- For this walkthrough, I used a Snowflake trial account with Enterprise edition on the AWS Tokyo region.
Required privileges for the operating role:
- OWNERSHIP privilege on the target table
- EXECUTE DATA METRIC FUNCTION privilege on the account
- SNOWFLAKE.DATA_METRIC_USER database role
- SNOWFLAKE.CORTEX_USER database role
LLM Models:
- The mistral-7b and llama3.1-8b models must be allowed in the CORTEX_MODELS_ALLOWLIST account parameter (they are allowed by default).

Preparing Test Data and Setting Permissions

Run the following SQL to create the objects and sample data for testing.

This data intentionally includes the following quality issues:

CUSTOMER_NAME is NULL (ORDER_ID: 5, 13)
EMAIL is NULL (ORDER_ID: 8, 13)
EMAIL has an invalid format (ORDER_ID: 22 → invalid-email)
QUANTITY is negative (ORDER_ID: 17 → -1)
QUANTITY is zero (ORDER_ID: 19 → 0)
TOTAL_AMOUNT is negative (ORDER_ID: 17 → -25.00)
STATUS contains an unexpected value (ORDER_ID: 25 → INVALID_STATUS)

-- Create database and schema for testing
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS DATA_QUALITY_DEMO;
CREATE SCHEMA IF NOT EXISTS DATA_QUALITY_DEMO.PUBLIC;

-- Create a warehouse for testing (you can use an existing one if available)
CREATE WAREHOUSE IF NOT EXISTS DATA_QUALITY_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

USE DATABASE DATA_QUALITY_DEMO;
USE SCHEMA PUBLIC;
USE WAREHOUSE DATA_QUALITY_WH;

-- Create a test table (simulating e-commerce order data)
CREATE OR REPLACE TABLE ORDERS (
    ORDER_ID INT,
    CUSTOMER_NAME VARCHAR(100),
    EMAIL VARCHAR(200),
    ORDER_DATE DATE,
    PRODUCT_NAME VARCHAR(200),
    QUANTITY INT,
    UNIT_PRICE DECIMAL(10, 2),
    TOTAL_AMOUNT DECIMAL(10, 2),
    STATUS VARCHAR(50),
    SHIPPING_COUNTRY VARCHAR(100),
    CREATED_AT TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

-- Insert sample data (20+ records, intentionally including data quality issues)
INSERT INTO ORDERS (ORDER_ID, CUSTOMER_NAME, EMAIL, ORDER_DATE, PRODUCT_NAME, QUANTITY, UNIT_PRICE, TOTAL_AMOUNT, STATUS, SHIPPING_COUNTRY)
VALUES
    (1, 'Taro Yamada', 'taro.yamada@example.com', '2025-01-05', 'Wireless Mouse', 2, 25.00, 50.00, 'Shipped', 'Japan'),
    (2, 'Hanako Suzuki', 'hanako.suzuki@example.com', '2025-01-06', 'Mechanical Keyboard', 1, 89.99, 89.99, 'Delivered', 'Japan'),
    (3, 'John Smith', 'john.smith@example.com', '2025-01-07', 'USB-C Hub', 3, 35.00, 105.00, 'Shipped', 'USA'),
    (4, 'Emily Johnson', 'emily.j@example.com', '2025-01-08', 'Monitor Stand', 1, 45.00, 45.00, 'Processing', 'USA'),
    (5, NULL, 'unknown@example.com', '2025-01-09', 'Webcam HD', 1, 59.99, 59.99, 'Shipped', 'Canada'),
    (6, 'Kenji Tanaka', 'kenji.tanaka@example.com', '2025-01-10', 'Laptop Sleeve', 2, 29.99, 59.98, 'Delivered', 'Japan'),
    (7, 'Maria Garcia', 'maria.garcia@example.com', '2025-01-11', 'Wireless Earbuds', 1, 79.99, 79.99, 'Shipped', 'Spain'),
    (8, 'Li Wei', NULL, '2025-01-12', 'Phone Case', 5, 12.00, 60.00, 'Delivered', 'China'),
    (9, 'Sakura Ito', 'sakura.ito@example.com', '2025-01-13', 'Desk Lamp', 1, 42.00, 42.00, 'Cancelled', 'Japan'),
    (10, 'David Brown', 'david.brown@example.com', '2025-01-14', 'Portable Charger', 2, 30.00, 60.00, 'Shipped', 'UK'),
    (11, 'Yuki Watanabe', 'yuki.w@example.com', '2025-01-15', 'Bluetooth Speaker', 1, 55.00, 55.00, 'Processing', 'Japan'),
    (12, 'Anna Mueller', 'anna.mueller@example.com', '2025-01-16', 'Tablet Stand', 1, 25.00, 25.00, 'Delivered', 'Germany'),
    (13, NULL, NULL, '2025-01-17', 'HDMI Cable', 10, 8.99, 89.90, 'Shipped', 'France'),
    (14, 'Ryo Nakamura', 'ryo.nakamura@example.com', '2025-01-18', 'Mouse Pad XL', 1, 19.99, 19.99, 'Delivered', 'Japan'),
    (15, 'Sophie Martin', 'sophie.martin@example.com', '2025-01-19', 'USB Flash Drive', 3, 15.00, 45.00, 'Shipped', 'France'),
    (16, 'Takeshi Kobayashi', 'takeshi.k@example.com', '2025-01-20', 'Webcam HD', 1, 59.99, 59.99, 'Delivered', 'Japan'),
    (17, 'Chen Mei', 'chen.mei@example.com', '2025-01-21', 'Wireless Mouse', -1, 25.00, -25.00, 'Returned', 'China'),
    (18, 'James Wilson', 'james.wilson@example.com', '2025-01-22', 'Mechanical Keyboard', 1, 89.99, 89.99, 'Processing', 'Australia'),
    (19, 'Aoi Sato', 'aoi.sato@example.com', '2025-01-23', 'Monitor Stand', 0, 45.00, 0.00, 'Cancelled', 'Japan'),
    (20, 'Lucas Dubois', 'lucas.dubois@example.com', '2025-01-24', 'Laptop Sleeve', 2, 29.99, 59.98, 'Shipped', 'France'),
    (21, 'Mika Yoshida', 'mika.yoshida@example.com', '2025-01-25', 'Desk Lamp', 1, 42.00, 42.00, 'Delivered', 'Japan'),
    (22, 'Robert Taylor', 'invalid-email', '2025-01-26', 'Portable Charger', 1, 30.00, 30.00, 'Shipped', 'USA'),
    (23, 'Haruto Kimura', 'haruto.k@example.com', '2025-01-27', 'Bluetooth Speaker', 2, 55.00, 110.00, 'Delivered', 'Japan'),
    (24, 'Isabella Rossi', 'isabella.rossi@example.com', '2025-01-28', 'Phone Case', 3, 12.00, 36.00, 'Shipped', 'Italy'),
    (25, 'Yuto Hayashi', 'yuto.hayashi@example.com', '2025-01-29', 'USB-C Hub', 1, 35.00, 35.00, 'INVALID_STATUS', 'Japan');

Next, grant the necessary privileges to the role you'll be using. In this case, I'm granting permissions to SYSADMIN.

USE ROLE ACCOUNTADMIN;

-- Grant EXECUTE DATA METRIC FUNCTION privilege
GRANT EXECUTE DATA METRIC FUNCTION ON ACCOUNT TO ROLE SYSADMIN;

-- Grant database roles
GRANT DATABASE ROLE SNOWFLAKE.DATA_METRIC_USER TO ROLE SYSADMIN;
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE SYSADMIN;

Setting Up Data Quality Checks Manually

Let's set up a data quality check manually against the data we just created.

Open the target table from Snowsight's Database Explorer and click the Data Quality tab.

The following screen appears. Click Setup manually.

A list of available data quality check candidates is displayed. Click the one you want to configure. (In this case, I'll click Nulls.)

A screen appears with an English sentence containing dropdown lists for configuring the Nulls check.

I configured it as shown below. Clicking Edit SQL lets you see the DMF definition query that will be generated. If everything looks good, click Save in the bottom right.

Looking at the Data Quality Monitoring view, you can see that the quality check has been added under the Accuracy section.

Also, clicking Settings in the Monitoring section allows you to change the frequency of data quality checks. (The default was set to run every hour.)

Setting Up AI-Powered Data Quality Checks (Cortex Data Quality)

Next, let's set up AI-powered data quality checks. (This feature is officially called Cortex Data Quality.)

On the Data Quality tab of the target data, click + Add quality check, then select Generate with Cortex Data Quality.

The following screen appears, where AI scans the data content and generates the necessary data quality checks.

For my data, after about 10 seconds, 10 data quality checks were generated as shown below.

It's great that the WHY IS THIS RECOMMENDED? column on the far right explains why each data quality check was suggested.

Check the boxes for the quality checks you need, then click Apply in the bottom right.

By the way, after clicking Apply, the following message appeared for my test data. I believe the permissions were sufficient, but I wasn't able to determine the exact cause...

After this, looking at the Data Quality Monitoring screen, you can see that the data quality checks have been added as shown below. (The one check showing an error is because the manually configured check from earlier had already been executed after one hour had passed.)

Running the Configured Data Quality Checks

Finally, let's run the data quality checks we've configured.

From Settings, change the frequency to Every 5 minutes and wait about 5 minutes.

After about 5 minutes, the Data Quality Monitoring screen displayed the results as shown below. Since you can also review the history of past quality check results, this is more than adequate as a quality monitoring feature. (If I had one wish, it would be nice to have notifications when data quality checks fail — but at least as of now, that doesn't seem to be configurable from Snowsight alone.)

Conclusion

I tried out the new feature that lets you set up data quality checks using AI directly from Snowsight, and summarized my findings in this article.

I had previously tested the Data Quality tab when it was first introduced, and at that time I felt that defining DMFs via SQL queries was time-consuming and a bottleneck. However, with this new feature, the fact that DMF definitions can be easily generated by relying on AI is truly impressive!

This is a feature you can try right away, so I highly encourage you to give it a shot.

Building a dbt Incremental Model for Parsing and Chunking PDFs for Snowflake Cortex Search Service

Sagara — Tue, 10 Feb 2026 23:11:12 +0000

This is an English translation of the following article:
https://dev.classmethod.jp/articles/snowflake-dbt-incremental-model-for-cortex-search-service/

To build RAG using Cortex Search Service, you need to prepare a table where documents such as PDFs have been parsed using functions like AI_PARSE_DOCUMENT.

I explored how to build this table using dbt, and in this article I'll share what I came up with.

The dbt Model Implementation

Let's jump right in — here is the dbt model I implemented. It performs document parsing and chunking against a directory table from an S3 bucket containing Snowflake customer case study PDFs.

The key points of this dbt model are:

Configured as an Incremental model with unique_key='relative_path' + incremental_strategy='delete+insert'. Data with the same file path is deleted and re-inserted.
- If a record with the same path and the same or newer modification timestamp already exists in the target table, it is skipped. This means only unprocessed or updated files are processed, reducing the cost of AI_PARSE_DOCUMENT parsing.
Chunking uses the SNOWFLAKE.CORTEX.SPLIT_TEXT_MARKDOWN_HEADER function.
- I adopted this approach after reading a blog post by Takada-san from Snowflake.

{{
    config(
        materialized='incremental',
        unique_key='relative_path',
        incremental_strategy='delete+insert'
    )
}}

with directory_files as (

    select
        relative_path,
        last_modified,
        TO_FILE('@RAW_DB.SAMPLE_SOURCE_SNOWPIPE.STAGE_CORPUS_SAMPLE', relative_path) as doc_file
    from
        DIRECTORY(@RAW_DB.SAMPLE_SOURCE_SNOWPIPE.STAGE_CORPUS_SAMPLE) as d
    where
        relative_path like 'snowflake-case-studies/%.pdf'
        {% if is_incremental() %}
        and not exists (
            select 1
            from {{ this }} as t
            where t.relative_path = d.relative_path
              and t.last_modified >= d.last_modified
        )
        {% endif %}

),

parsed as (

    select
        relative_path,
        last_modified,
        SNOWFLAKE.CORTEX.AI_PARSE_DOCUMENT(
            doc_file,
            {'mode': 'LAYOUT', 'page_split': TRUE}
        ) as parsed_result
    from
        directory_files

),

concatenated as (

    select
        p.relative_path,
        p.last_modified,
        LISTAGG(f.value:content::varchar, '\n\n') within group (order by f.index) as full_content
    from
        parsed as p,
        lateral flatten(input => p.parsed_result:pages) as f
    group by
        p.relative_path, p.last_modified

),

chunked as (

    select
        c.relative_path,
        c.last_modified,
        ch.value:headers:header_1::varchar as header_1,
        ch.value:headers:header_2::varchar as header_2,
        ch.value:headers:header_3::varchar as header_3,
        ch.value:chunk::varchar as chunk,
        ch.index as chunk_index
    from
        concatenated as c,
        lateral flatten(input =>
            SNOWFLAKE.CORTEX.SPLIT_TEXT_MARKDOWN_HEADER(
                c.full_content,
                OBJECT_CONSTRUCT('#', 'header_1', '##', 'header_2', '###', 'header_3'),
                10000
            )
        ) as ch

)

select
    SHA2(relative_path || ':' || chunk_index, 256) as doc_chunk_id,
    relative_path,
    SPLIT_PART(relative_path, '/', -1) as file_name,
    REPLACE(REPLACE(SPLIT_PART(relative_path, '/', -1), 'Case_Study_', ''), '.pdf', '') as case_study_name,
    header_1,
    header_2,
    header_3,
    chunk_index,
    chunk,
    last_modified,
    CURRENT_TIMESTAMP() as _parsed_at
from
    chunked

Additionally, in dbt_project.yml, I configured pre_hook and post_hook as shown below. The assumption is that a corpus folder is created within the models folder, and models for parsing and chunking for Cortex Search Service are defined inside it.

pre_hook: Refreshes the external stage pointing to the S3 bucket containing the PDFs, so the directory table queried by the dbt model is updated before the model is built.
post_hook: Since tables for Cortex Search Service require CHANGE_TRACKING to be enabled, a query to enable it is executed after the model is built.

name: 'sample_dbt_project'
version: '1.9.0'
config-version: 2

profile: 'sample_project'

models:
  sample_dbt_project:
    corpus:
      sample:
        +schema: corpus_sample
        +pre-hook:
          - "ALTER STAGE RAW_DB.SAMPLE_SOURCE_SNOWPIPE.STAGE_CORPUS_SAMPLE REFRESH"
        +post-hook:
          - "ALTER TABLE {{ this }} SET CHANGE_TRACKING = TRUE"

First dbt Model Run & Cortex Search Service Setup

In S3, I initially placed only two case study PDFs — one for Bourbon and one for Aisan Takaoka. (I'll omit the stage creation queries here.)

Running the dbt model above with dbt build creates a table in Snowflake with the parsed and chunked case study PDFs, as shown below.

I then created a Cortex Search Service and Cortex Agent for this dbt model using the following queries.

-- Create Cortex Search Service
CREATE OR REPLACE CORTEX SEARCH SERVICE PROD_DB.AIML_SAMPLE.CSS_SNOWFLAKE_CASE_STUDIES
    ON chunk
    ATTRIBUTES file_name, case_study_name, header_1, header_2, header_3, chunk_index, last_modified
    WAREHOUSE = CORTEX_SEARCH_WH
    TARGET_LAG = '365 day'
    EMBEDDING_MODEL = 'voyage-multilingual-2'
    INITIALIZE = ON_CREATE
    COMMENT = 'Cortex Search Service for chunked Snowflake customer case study PDFs'
AS
SELECT
    file_name,
    case_study_name,
    header_1,
    header_2,
    header_3,
    chunk_index,
    chunk,
    last_modified
FROM PROD_DB.CORPUS_SAMPLE.COR_SNOWFLAKE_CASE_STUDIES_PARSED;

-- Create Agent
CREATE OR REPLACE AGENT PROD_DB.AIML_SAMPLE.AGENT_SNOWFLAKE_CASE_STUDIES
    COMMENT = 'Cortex Agent for searching and answering questions about Snowflake customer case study PDFs'
    PROFILE = '{"display_name": "Snowflake Case Study Search", "avatar": "search", "color": "#29B5E8"}'
    FROM SPECIFICATION
    $$
    instructions:
      system: |
        You are an assistant that answers questions about the content of Snowflake customer case study PDFs.
        Use the Cortex Search Service to search for relevant case study information
        and provide answers based on accurate information.
        If the information is not found in the search results, do not speculate — inform the user accordingly.
      response: |
        Please respond concisely.
        Include the referenced case study name (case_study_name) in your answer.

    tools:
      - tool_spec:
          type: "cortex_search"
          name: "SearchCaseStudies"
          description: "Search Snowflake customer case study PDFs"

    tool_resources:
      SearchCaseStudies:
        name: "PROD_DB.AIML_SAMPLE.CSS_SNOWFLAKE_CASE_STUDIES"
        max_results: "5"
    $$;

-- Add to Snowflake Intelligence
ALTER SNOWFLAKE INTELLIGENCE SNOWFLAKE_INTELLIGENCE_OBJECT_DEFAULT
    ADD AGENT PROD_DB.AIML_SAMPLE.AGENT_SNOWFLAKE_CASE_STUDIES;

After querying through Snowflake Intelligence, I was able to get results as shown below.

Updating S3 Data and Running the dbt Model a Second Time

To verify that incremental updates work correctly, I made the following two changes to the S3 bucket:

Deleted the 2nd page of the Aisan Takaoka case study PDF and re-uploaded it with the same file name to S3
Added a new Chiba Bank case study PDF to the S3 bucket

After running dbt build again, you can see that the chunk count for Aisan Takaoka decreased (due to the removed page), and new records for Chiba Bank were added.

Looking at the _PARSED_AT column, which records the parsing timestamp, you can confirm that only the records for Aisan Takaoka and Chiba Bank — the files that had changes — had their timestamps updated.

With the table updated, I refreshed the Cortex Search Service by running the following query:

ALTER CORTEX SEARCH SERVICE PROD_DB.AIML_SAMPLE.CSS_SNOWFLAKE_CASE_STUDIES REFRESH;

Querying through Snowflake Intelligence again confirmed that the updated data was being used for responses.

Conclusion

I explored building a dbt Incremental model that parses PDFs and chunks them for Cortex Search Service, and shared the implementation in this article.

By using dbt's Incremental model, only files with changes are detected and processed by the AI_PARSE_DOCUMENT function, which helps reduce costs.

I hope you find this useful!

Git-Driven dbt + BI Development in Omni: Dynamic Schemas for Safe Dev/Prod Switching

Sagara — Sat, 01 Nov 2025 02:22:55 +0000

Note: This is the English translation of the original Japanese article:https://dev.classmethod.jp/articles/omni-dynamic-schema-with-dbt/

Omni offers several features that work great with dbt, and one standout is Dynamic Schemas, which lets you dynamically switch references to data in your dbt development environments.

https://docs.omni.co/docs/integrations/dbt#getting-started-with-dynamic-schemas-and-dbt-environments

I tried this feature, so I’ll summarize what I did in this post.

Preparation

Prerequisites

First, assume the following are already completed.

A Connection to Snowflake has been created
- Within the Connection, the dbt integration setup is complete

You have created one Model for the Connection, integrated it with GitHub, and required pull requests for changes to Shared Models (reference: official docs)

Create a new Environment for the dbt development environment

Next, create a new Environment that corresponds to your dbt development environment.

From the dbt tab of the target Connection, go to the Environments section at the bottom and click Add Environment.

Configure the Environment as shown below. The key points are to set Default Schema to the schema you use for development, and set Target Name to the dbt target name you use for your development environment.

Click Save. You’ll see a list of schemas used for the dbt development environment as shown below. If everything looks good, simply close the dialog with the X in the upper right.

Enable Virtual Schemas

Next, from the dbt tab of the target Connection, enable Enable Virtual Schemas and click Save. Enabling this creates a virtual schema named omni_dbt. When referencing each view, you can use the form omni_dbt__<view name>. When you switch the dbt environment using the Dynamic Schema feature, this virtual schema abstracts away the environment so that references dynamically switch between production and development schemas.

Once Virtual Schemas are enabled, a Migration panel should appear on the right. Before enabling Virtual Schemas, the existing Omni Semantic Layer code referenced a fixed schema for a particular environment. Running this migration automatically adds code that references the omni_dbt schema.

Click Run migration to perform the migration.

You’ll see the screen below. Check Overwrite in the lower left and click Run migration.

After this, if you look at the Shared Model code, you’ll see a VIRTUAL SCHEMAS section has been added, and each view file has been updated to reference the omni_dbt schema.

(From the created VIRTUAL SCHEMAS names, it looks like they’re created as omni_dbt_<custom schema name of dbt production environment>.)

Update existing content to reference the Virtual Schema views

If you’ve already built content in Omni, you’ll need to update existing references to point to the Virtual Schema views.

Create a new branch and perform the following steps. Once done, open a pull request and merge it.

Update the view references in topics

FYI, you can use Ctrl + F to search/replace text within each file.

Before

After

Update the view references in each Workbook query

This is necessary if your Workbook queries reference raw views rather than topics.

In the Content Validator, click Show all documents.

Click Replace, confirm you’re on the View tab, then configure a replacement from the raw table view names to the Virtual Schema view names as shown below, and click Replace.

After this, while still on the branch, review each affected Workbook query. Verify that charts render correctly and that clicking Go to definition on any field links to the view definition within the Virtual Schema.

Trying development with Dynamic Schemas pointing to the dbt development environment

With the setup done, let’s try how development works using Dynamic Schemas to reference dbt’s development environment.

Task

There’s a column named ordered_at in one of the dbt Models used by Omni, and we’ll change it to purchased_at.

Create a branch in dbt and develop

Create a branch in dbt and make the following changes.

Then, run dbt build in the dbt Cloud IDE; the updated column name will be created in the dbt development schema in Snowflake.

Create a branch in Omni and make fixes while referencing the dbt dev environment via Dynamic Schemas

Next, in Omni, create a branch on the affected Shared Model. (For reference, dbt and Omni are connected to separate GitHub repositories.)

First, run Refresh Schema to reflect the latest dbt schema structure into Omni’s Schema Model.

Then, click the dbt icon next to the branch name at the top and switch to the dbt development Environment.

With the Environment switched to dbt’s dev environment, open the target view file and you’ll see ordered_at is gone and purchased_at is present.

Also, when viewing the dashboard in the dbt dev Environment, you’ll get errors if those dashboards referenced the old ordered_at column. Since we’re working on a branch, published dashboards still reference the dbt production Environment and do not error.

On a branch referencing the dbt dev environment: errors occur

When you exit the branch (published dashboards): no errors

Switch back to the branch, open the Shared Model IDE, and launch the Content Validator. As shown below, errors are detected due to mismatched field references.

Click Replace and perform replacements for FIELD.

When you display the dashboard again, the field references have been updated and charts now render correctly—even while working on the branch. That completes the development and fixes.

Open a PR to main in dbt, merge, and run dbt build against production

Commit on the dbt side, open a pull request, merge into main, and run dbt build against the dbt production environment.

Now the purchased_at column has been added to the dbt production schema in Snowflake.

Open a PR to main in Omni and merge

Finally, open a pull request to main in Omni and merge it.

First, switch the Environment in Omni back to the dbt production environment. If you don’t do this before opening the pull request, all dashboards using this Shared Model will end up referencing the development environment, so be careful. (If you try to open a pull request while the Environment is set to development, Omni will show a warning.)

Next, run Sync dbt metadata. (Without this, the view file definitions for the production Environment were still outdated.)

Now, when you look at the target view file, ordered_at is gone and purchased_at is present.

Also, when viewing the target dashboard with the Environment set to production, charts display correctly. (If the chart looks different from the first screenshot, that’s due to the dbt logic used—please ignore it.)

With everything confirmed, open a pull request while the Environment is set to production. (If no code changes exist, you won’t be able to open a pull request, so add something minimal like a space to a view file’s description.)

After merging the pull request, switch out of the branch and check the target dashboard. It renders without errors. This completes the end-to-end flow for changing a field definition in a dbt Model already referenced by Omni.

Thoughts

I tried Omni’s “Dynamic Schemas,” which let you update Semantic Layer definitions and fix dashboards while referencing data from dbt’s development environment. When I first read the docs I thought “this could be big,” and indeed it’s a very exciting feature. Being able to do Git-driven development that accounts for not just dbt but also Omni dashboards is excellent.

On the other hand, a few things stood out at this stage. If these improve, I think we’ll get a truly top-tier dbt + BI development experience!

You can’t inject user attributes into the dbt Environment defined in Omni. In other words, if you want to use personal dev schemas like dbt_ssagara, you need to add one Environment per person. (If you want to avoid that, you’d need to insert an intermediate branch like develop or staging between main and define the Omni Environment for that branch.)
- Omni already has Dynamic Database Environments based on user attributes, so I’m hopeful for an update here!
The dashboard reference definitions surfaced by Content Validator are not code-ified, so as in this post, I had to add a half-width space to a description to get the final Omni pull request/merge through.
After running dbt build against production, you still need to issue and merge a pull request on the Omni side to update the dbt production schema and reflect Content Validator changes. If dbt-side field changes are ongoing, published Omni dashboards might not reference the correct fields, causing temporary downtime.
- There may be a smoother approach for this…

Personal Picks: Data Product News (October 1, 2025)

Sagara — Wed, 01 Oct 2025 02:35:06 +0000

Modern Data Stack Information Summary - October 2025

This article is an English translation of the original Japanese version: https://dev.classmethod.jp/articles/modern-data-stack-info-summary-20251001/

Hello, this is Sagara.

As a consultant specializing in Modern Data Stack, I observe that the Modern Data Stack ecosystem generates a tremendous amount of information daily.

Among this wealth of information, this article summarizes the Modern Data Stack-related updates that caught my attention over the past two weeks.

Disclaimer: This is not an exhaustive list of all the latest updates for the mentioned products. The information included here is based on my personal judgment and interests.

Modern Data Stack General

Launch of "Open Semantic Interchange (OSI)"

Snowflake, Salesforce, dbt Labs, and other companies have announced the launch of an open-source initiative called "Open Semantic Interchange (OSI)" to promote data utilization for AI.

This initiative aims to build a common semantic data framework by standardizing fragmented Semantic Layer definitions that vary across different products through a vendor-neutral open specification.

The following vendors are listed as Launch Partners:

Below are the press releases from Snowflake and Salesforce regarding this announcement:

https://www.snowflake.com/en/news/press-releases/snowflake-salesforce-dbt-labs-and-more-revolutionize-data-readiness-for-ai-with-open-semantic-interchange-initiative/

https://www.salesforce.com/blog/agentic-future-demands-open-semantic-layer/

While other participating products have also published blogs about this announcement, I found Select Star's post particularly interesting. As shown in the figure quoted from their blog, if this can be realized, Select Star could act as a hub to coordinate Semantic Layer definitions with BI tools not participating in the Open Semantic Interchange initiative, which I find exciting.

https://www.selectstar.com/resources/snowflake-ai-ready-semantic-model

"Everyone's Strongest Data Platform Architecture Vol. 5 - All-Star Special!!" Event Held

On September 25th, "Everyone's Strongest Data Platform Architecture Vol. 5 - All-Star Special!!" was held.

https://datatech-jp.connpass.com/event/360596/

The event had over 100 in-person attendees and more than 500 online participants. You can get a sense of the event's excitement by checking the hashtag "みん強" (Min-Kyō) at the following link:

https://x.com/hashtag/%E3%81%BF%E3%82%93%E5%BC%B7?src=hashtag_click

Below are links to the presentation materials from each speaker that I was able to find:

https://speakerdeck.com/kaz3284/minqiang-di-5hui-kubellnodetaji-pan-kai-fa-nozui-xin-zhuang-kuang-toainohuo-yong-noshi-jian-nituite

https://speakerdeck.com/tenajima/data-vaultwoyong-itamarutipurodakutonotamenodetaji-pan-kai-fa

https://speakerdeck.com/pei0804/revops-practice-learned

https://speakerdeck.com/foursue/20250924-lt2ben-yaru

https://speakerdeck.com/genshun9/minqiang-nokoremadetokorekara

Data Extract/Load

Airbyte

Airbyte 2.0 Released

Airbyte has released version 2.0, marking a major version upgrade. (The OSS version has not yet released 2.0.)

https://airbyte.com/v2

https://airbyte.com/blog/airbyte-2-0

As quoted from the links above, the following features have been released:

Enterprise Flex: An architecture that separates the control plane and data plane, providing a hybrid model where management is done in the cloud while actual data remains within the customer's infrastructure
Data Activation: A feature that directly syncs insights from data warehouses to business applications like Salesforce and HubSpot. This allows the Reverse ETL process to be completed within the platform
Speed: Connector architecture has been redesigned to improve data sync speed by 4-10x. For example, MySQL to S3 sync is 4.7x faster, and Postgres to S3 is 12x faster
New Pricing Plans: A new plan structure tailored to team growth stages. The "Capacity Based Pricing" introduced for Pro plans and above is particularly notable, as it's based on required parallel processing capacity (Data Workers) rather than data transfer volume
- Core (formerly OSS): Free open-source version
- Standard (formerly Cloud): Pay-as-you-go managed service
- Pro (formerly Teams): Capacity-based pricing with governance features like RBAC and SSO
- Enterprise Flex: All Pro features plus the ability to deploy data planes anywhere—cloud, multi-cloud, or on-premises
- Self-Managed Enterprise: Fully self-managed enterprise version for organizations with strict security requirements

Data Warehouse/Data Lakehouse

Snowflake

FILE Data Type Generally Available

The FILE data type for handling unstructured data in Snowflake is now generally available.

This enables confident use of generative AI with Cortex AI SQL for images and document files!

https://docs.snowflake.com/en/release-notes/2025/other/2025-09-25-file-data-type-ga

Cortex Analyst Feature Enhancements

Cortex Analyst has received functional updates with two new features added. Derived metrics is a capability that other Semantic Layers already had, and since actual business often requires calculations using multiple metrics, this is a welcome addition!

Private facts and metrics: A feature that defines metrics in the Semantic Model but prevents end users from directly querying these metrics (primarily intended for metrics used only in Derived metrics)
Derived metrics: A new type of metric that allows defining metrics based on calculations between multiple metrics

https://docs.snowflake.com/en/release-notes/2025/other/2025-09-30-semantic-model-improvements

dbt Projects on Snowflake Now Supports docs generate

dbt Projects on Snowflake received a silent update that now enables docs generate functionality.

While I haven't tested it yet, this should allow the execute dbt project command to perform docs generate when hosting docs with GitHub Actions, eliminating the need to rewrite profiles.yml for dbt Core!

https://x.com/SS_chneider/status/1973154146976145839

Claude Sonnet 4.5 Now Available in Snowflake

Claude Sonnet 4.5 is now available within Snowflake. The official documentation doesn't mention it yet.

Additionally, it's accessible in unsupported regions by enabling cross-region inference.

https://www.snowflake.com/en/blog/cortex-ai-claude-sonnet-4-5/

SELECT's Summary Article on Snowflake Features Released in Summer 2025

SELECT has published a summary article on Snowflake features released in summer 2025.

https://select.dev/posts/snowflake-summer-2025-product-updates

Best Practices Article for Combining Snowflake × Power BI

phData has published a best practices article for combining Snowflake × Power BI.

The article mainly covers the following topics:

Use Power BI's native Snowflake Connector
Carefully select connection mode (Import, DirectQuery, or Composite) based on use case
Properly model data, including adopting star schema
Configure Microsoft Entra SSO for Snowflake
Use appropriate Azure VMs for gateways
Minimize distance between Snowflake and Power BI data centers
Increase concurrent query limits for data models
Leverage AI features like Copilot

https://www.phdata.io/blog/how-to-optimize-power-bi-and-snowflake-for-advanced-analyitcs/

BigQuery

Column-Level Lineage Now Available in Dataplex

As a new feature in Dataplex, column-level lineage viewing is now available (generally available).

https://cloud.google.com/dataplex/docs/release-notes#September_29_2025

https://cloud.google.com/dataplex/docs/lineage-views#column-level-lineage

Array Unnesting Feature Using Gemini Released

A feature using Gemini that can expand each element of an array into independent rows has been released.

https://cloud.google.com/bigquery/docs/release-notes#September_29_2025

https://cloud.google.com/bigquery/docs/data-prep-get-suggestions#unnest-arrays

Summary Article on New BigQuery SQL Features

yu yamada from Google Cloud has published an article summarizing five new features related to BigQuery SQL, including UNION based on column names and simplified array operations.

https://zenn.dev/google_cloud_jp/articles/3b20a94df7624e

Databricks

Databricks One in Public Preview

"Databricks One," a simple user interface designed for business users, has entered public preview.

https://docs.databricks.com/aws/ja/workspace/databricks-one

As shown in the figure below, it features a UI where you can ask questions about data in natural language and directly link to related dashboards.

Lakeflow Pipelines Editor in Public Preview

Databricks has released "Lakeflow Pipelines Editor," a new IDE for developing and debugging ETL pipelines, as a public preview.

https://docs.databricks.com/aws/en/dlt/dlt-multi-file-editor

As shown in the figure quoted from the link above, it's not just for editing pipeline code but also allows viewing dependencies between tables.

OpenAI GPT-5 and Claude Sonnet 4.5 Now Available in Databricks

While these are separate announcements, both GPT-5 and Sonnet 4.5 are now available within Databricks.

https://www.databricks.com/blog/run-openai-models-directly-databricks

https://www.databricks.com/blog/claude-sonnet-45-here

MotherDuck/DuckDB

DuckDB ducklake Extension and DuckLake 0.3 Released

The DuckDB ducklake extension and DuckLake 0.3 have been released. Using the ducklake extension requires DuckDB v1.4.0.

The main updates appear to be data copying between DuckLake and Iceberg using DuckDB's iceberg extension, and using the MERGE statement released in DuckDB v1.4.0 through the ducklake extension.

https://duckdb.org/2025/09/17/ducklake-03.html

MotherDuck Announces First European Cloud Region in Private Preview

MotherDuck has announced its first European cloud region as a private preview.

This new region runs on AWS eu-central-1, with official release planned for this fall.

https://motherduck.com/blog/motherduck-in-europe/

Business Intelligence

Looker

Looker Accessible from Gemini CLI

A feature to access Looker has been released as an extension for Gemini CLI.

It appears you can check available Explores, confirm dimensions and measures available in specified Explores, and even create Looks and dashboards in Looker.

https://cloud.google.com/looker/docs/release-notes#September_23_2025

https://github.com/gemini-cli-extensions/looker

Data Activation (Reverse ETL)

Hightouch

Dashboards Now Available in Hightouch

As a new feature in Hightouch, functionality to consolidate multiple charts into dashboards has been released.

This should be useful for cases where you want to check everything in Hightouch, such as dashboards for confirming campaign performance.

https://changelog.hightouch.io/

https://hightouch.com/docs/campaign-intelligence/dashboards

Data Orchestration

Airflow

Airflow 3.1 Released

Airflow's latest version 3.1 has been released.

https://github.com/apache/airflow/releases/tag/3.1.0

Astronomer has published a blog post summarizing the added features.

It appears that improvements to AI workflow support, updates to a React-based UI interface, and DAG favorites functionality have been added.

https://www.astronomer.io/blog/introducing-apache-airflow-3-1/

Personal Picks: Data Product News (August 20, 2025)

Sagara — Wed, 20 Aug 2025 01:46:10 +0000

Note: This is an English translation of the Japanese article at https://dev.classmethod.jp/articles/modern-data-stack-info-summary-20250820/

Hi, this is Sagara.

As a consultant specializing in Modern Data Stack, I'm constantly exposed to the vast amount of information being shared in this space daily.

Among the numerous updates, I've compiled the Modern Data Stack-related information that caught my attention over the past two weeks.

Note: This article doesn't cover all the latest information about the mentioned products. It only includes information that I found interesting based on **my personal judgment and preferences.*

Data Warehouse/Data Lakehouse

Snowflake

Database Backup "Snapshots" That Cannot Be Deleted or Edited Even by ACCOUNTADMIN Now in Public Preview

Snowflake's new Snapshot feature is now in public preview.

The key feature is that, similar to clones, it can replicate data with zero-copy, but with the Retention lock feature, it can be maintained as an undeletable and uneditable backup.

https://docs.snowflake.com/en/release-notes/2025/other/2025-08-18-worm-snapshots

https://docs.snowflake.com/en/user-guide/snapshots

I've actually tried it myself and written a blog post about it - please check it out!

https://dev.classmethod.jp/articles/snowflake-try-snapshot/

Stored Procedure "AI_GENERATE_TABLE_DESC" for Generating Descriptions Using Generative AI Now in Public Preview

Snowflake's new stored procedure "AI_GENERATE_TABLE_DESC" for generating descriptions using generative AI is now in public preview. Previously, this could only be done by clicking buttons in Snowsight, but now SQL command-based description generation using generative AI is possible.

https://docs.snowflake.com/release-notes/2025/other/2025-08-14-sql-object-descriptions

https://docs.snowflake.com/en/user-guide/sql-cortex-descriptions

Here's my blog post about trying this feature. While AI_GENERATE_TABLE_DESC returns descriptions in English, I've also written about a custom stored procedure that translates and stores them in Japanese using the Translate function - please take a look!

https://dev.classmethod.jp/articles/snowflake-try-generate-table-desc/

Cortex Knowledge Extensions Now Generally Available

Snowflake's Cortex Knowledge Extensions is now generally available. This feature allows content that can be referenced by agent functions like Snowflake Intelligence to be obtained from the Marketplace. In essence, databases with embedded Cortex Search Service can now be obtained through the Marketplace.

https://www.snowflake.com/en/blog/easy-button-context-rich-ai-agents/

I tried the official Snowflake documentation's Cortex Knowledge Extensions with Snowflake Intelligence, and the answer accuracy clearly improved! This is a great example of how good data leads to good AI results.

https://dev.classmethod.jp/articles/snowflake-try-cortex-knowledge-extensions-with-snowflake-intelligence/

Workload Identity Federation Now Generally Available

Snowflake has released Workload identity federation as a new authentication mechanism.

With Workload identity federation, you can build service-to-service authentication mechanisms that authenticate to Snowflake using cloud provider ID systems such as AWS IAM, Microsoft Entra ID, and Google Cloud.

https://docs.snowflake.com/en/release-notes/2025/other/2025-08-14-wif

https://docs.snowflake.com/en/user-guide/workload-identity-federation

For practical usage, the following blog is very helpful:

https://zenn.dev/jimatomo/articles/c514c6e322bf1a

Looking ahead, if various SaaS/OSS tools that require authentication when integrating with Snowflake support Workload identity federation, we can connect these tools to Snowflake more securely and easily! (For example, looking at the latest roadmap for terraform-provider-snowflake linked below, it seems to be planned for implementation by the end of 2025.)

https://github.com/snowflakedb/terraform-provider-snowflake/blob/main/ROADMAP.md

Snowpipe Billing Model Changed to Simple Volume-Based for Business Critical and Above Plans

With the 9.21 release around August 1st, the Snowpipe billing model has been changed to a simple volume-based system for Business Critical and above plans.

This makes estimation much easier than before, and the new billing model seems better for cases where you want to load many small files!

For details, please check the official information below:

https://docs.snowflake.com/en/user-guide/data-load-snowpipe-billing

https://www.snowflake.com/legal-files/CreditConsumptionTable.pdf

New Community-Built Snowflake MCP Server

A new Snowflake MCP Server has been released by the international community. While the official MCP Server works through Cortex Agents, this one uses Python Connector for connection, allowing for a broader range of requests to Snowflake.

https://medium.com/snowflake/the-general-purpose-snowflake-mcp-server-sql-operation-through-natural-language-ddd33bba4fa7

https://github.com/uniquejtx/snowflake-generic-mcp

Databricks

Unity Catalog Adds User Access Request Feature for Data Objects (Public Preview)

Unity Catalog in Databricks has added a new feature for user access requests to data objects.

The functionality allows you to pre-configure notification destinations such as email addresses or Slack channels, and when users make access requests, notifications are sent to the specified destinations.

https://docs.databricks.com/aws/en/data-governance/unity-catalog/manage-privileges/access-request-destinations

Unity Catalog REST API Usage Guide

The Unity Catalog blog published an article summarizing baseline usage patterns for the Unity Catalog REST API.

Specifically, it explains common operations like listing (GET), creating (POST), updating (PATCH), and deleting (DELETE) for Catalogs and Tables using Python's requests library, with concrete code examples.

https://www.unitycatalog.io/blogs/how-to-use-the-unity-catalog-rest-api

Data Transform

dbt

dbt Fusion and Official VS Code Extension Move from Beta to Preview

dbt Fusion and the official VS Code Extension, previously available as Beta, have moved to Preview status.

According to the article below, they defined a metric called "Fusion conformance" to prove that Fusion performs exactly the same as dbt Core in specific dbt projects. This metric has passed for a sufficient percentage of users' dbt projects, giving them confidence for the preview release.

https://www.getdbt.com/blog/fusion-and-dbt-vs-code-extension-preview-launch

Business Intelligence

Looker

Looker 25.14 Release Notes Published

The release notes for Looker's latest version 25.14 have been published.

https://cloud.google.com/looker/docs/release-notes#August_13_2025

I'm particularly excited about the ability to define synonyms in views! Since internal conversations often use abbreviations for metrics, defining these abbreviations as synonyms should enable more natural interactions in Conversational Analytics to get the desired data.

https://cloud.google.com/looker/docs/reference/param-field-synonyms

Omni

"Omni Spreadsheets" Released - Perform Data Processing and Aggregation with Spreadsheet-Like Operations

Omni has released "Omni spreadsheets," a new feature that allows data processing and aggregation with almost the same operations as spreadsheets.

https://omni.co/blog/building-our-financial-models-with-omni-spreadsheets

https://docs.omni.co/docs/querying-and-sql/workbook/spreadsheet-tabs

Looking at the demo below, it's almost like a spreadsheet, making it a great feature for creating rich tabular reports that can only be made in spreadsheets. However, a concern is the risk of accumulating data with various calculated metrics in spreadsheets separate from Omni's defined Semantic Layer. It would be nice if this could be controlled well with permissions!

https://www.youtube.com/watch?v=aBjnn8FUHxE

Omni Can Now Push dbt Exposures

I've only seen this in the ChangeLog, but Omni can now push dbt exposures as a new feature.

This allows you to output how Omni content is linked to dbt Models as exposures and view them in lineage.

https://omni.co/changelog

Data Catalog

Select Star

Select Star's August Release Summary

Select Star's ChangeLog published a summary of August releases.

Notable updates include ER diagram refresh and MCP Server release.

https://docs.selectstar.com/changelog/aug-7-2025-clearer-erd-ai-ready-metadata-mcp-smarter-metrics-and-more

Data Quality・Data Observability

Elementary

Elementary's July Update Summary

Elementary's official blog published an article summarizing July updates.

I was particularly interested in the MCP Server and the feature to exclude anomalous data during training for anomaly detection.

https://www.elementary-data.com/post/july-product-update

Data Orchestration

Dagster

MCP Server Released

Dagster has released an MCP Server.

According to the blog below, use cases include creating project templates and building workflows by integrating with dbt and Snowflake MCP Servers.

https://dagster.io/blog/dagsters-mcp-server

Running the "Quickstart for dbt and Snowflake" Tutorial with dbt Projects on Snowflake

Sagara — Wed, 13 Aug 2025 05:35:24 +0000

This article is an English translation of: https://dev.classmethod.jp/articles/dbt-quickstart-for-dbt-and-snowflake-with-dbt-projects-on-snowflake/

Hi, this is Sagara.

An official tutorial called "Quickstart for dbt and Snowflake" is available for dbt Cloud. This tutorial is recommended as a first step to learn about dbt itself.

https://docs.getdbt.com/guides/snowflake?step=1

Additionally, as of the end of June 2025, dbt Projects on Snowflake, which allows you to develop and run dbt within Snowflake, is in public preview.

https://docs.snowflake.com/en/user-guide/data-engineering/dbt-projects-on-snowflake

https://dev.classmethod.jp/articles/snowflake-pupr-dbt-projects/

I tried running this "Quickstart for dbt and Snowflake" tutorial with dbt Projects on Snowflake, and I'll summarize the experience in this article.

Important Notes

The following dbt Cloud-specific features are out of scope for this article. Please be aware:

Using Managed Repository
- In this tutorial, we'll develop without Git integration
- For information about Git integration in dbt Projects on Snowflake, please refer to this blog post
Viewing documentation
Environment and Job configuration (dbt Cloud-only features)
- In dbt Projects on Snowflake, these are replaced by profiles.yml definitions and dbt Projects deployment to task execution

0. Prerequisites

Creating a Snowflake Trial Account

We'll use a Snowflake trial account for this tutorial, so please create one.

https://signup.snowflake.com/?owner=classmethodlead&plan=enterprise&cloud=aws&region=ap-northeast-1&utm_source=dev.classmethod.jp&utm_medium=banner&utm_content=snowflake&utm_campaign=single_article_foot_ads

Enabling Secondary Roles (if not already enabled)

Next, check if secondary roles are enabled in your worksheet.

Run the following query and confirm that default_secondary_roles is set to ["ALL"]:

show users;

If it shows a different value, run the following query to enable secondary roles:

alter user <username> set default_secondary_roles = ('all');

1. Introduction

https://docs.getdbt.com/guides/snowflake?step=1

This chapter explains what we'll do in this tutorial.

Here's what we'll cover in order:

Create a new Snowflake worksheet
Load sample data into your Snowflake account
Convert sample queries into models in your dbt project (dbt models are SELECT statements)
Add sources to your dbt project. Sources allow you to name and describe raw data already loaded into Snowflake
Add tests to your models
Document your models
Schedule job runs
- *Note: For jobs, we'll substitute with dbt Projects deployment to task execution

2. Create a new Snowflake worksheet

https://docs.getdbt.com/guides/snowflake?step=2

In this chapter, we'll create a worksheet to run queries in Snowflake.

From the left menu in Snowflake, go to Projects, then click Worksheets.

Click the + button in the upper right to create a new worksheet.

3. Load data

https://docs.getdbt.com/guides/snowflake?step=3

In this chapter, we'll load the data needed for the tutorial.

First, run the following query to create the necessary database, schemas, tables, and warehouse for the tutorial. The tables will also load data from public S3.

Important: For line 8 create schema analytics.dbt_ssagara;, please use your own name or something that identifies it as your schema. (Since my name is Sagara Satoshi, I'm using dbt_ssagara.)

use role accountadmin;

create warehouse transforming;
create database raw;
create database analytics;
create schema raw.jaffle_shop;
create schema raw.stripe;
create schema analytics.dbt_ssagara; 
create schema analytics.prod;

create table raw.jaffle_shop.customers 
( id integer,
  first_name varchar,
  last_name varchar
);

copy into raw.jaffle_shop.customers (id, first_name, last_name)
from 's3://dbt-tutorial-public/jaffle_shop_customers.csv'
file_format = (
    type = 'CSV'
    field_delimiter = ','
    skip_header = 1
    );

create table raw.jaffle_shop.orders
( id integer,
  user_id integer,
  order_date date,
  status varchar,
  _etl_loaded_at timestamp default current_timestamp
);

copy into raw.jaffle_shop.orders (id, user_id, order_date, status)
from 's3://dbt-tutorial-public/jaffle_shop_orders.csv'
file_format = (
    type = 'CSV'
    field_delimiter = ','
    skip_header = 1
    );

create table raw.stripe.payment 
( id integer,
  orderid integer,
  paymentmethod varchar,
  status varchar,
  amount integer,
  created date,
  _batched_at timestamp default current_timestamp
);

copy into raw.stripe.payment (id, orderid, paymentmethod, status, amount, created)
from 's3://dbt-tutorial-public/stripe_payments.csv'
file_format = (
    type = 'CSV'
    field_delimiter = ','
    skip_header = 1
    );

Next, run the following queries in order to confirm that data has been loaded successfully into each table:

select * from raw.jaffle_shop.customers;
select * from raw.jaffle_shop.orders;
select * from raw.stripe.payment;

4. Connect dbt to Snowflake

https://docs.getdbt.com/guides/snowflake?step=4

This chapter covers setting up the connection from dbt Cloud to Snowflake, but since this is not needed for dbt Projects on Snowflake, we'll skip it.

5. Set up a dbt managed repository

https://docs.getdbt.com/guides/snowflake?step=5

This chapter covers setting up the Managed Repository, which is a dbt Cloud-specific feature. Since this is not relevant to dbt Projects on Snowflake, we'll skip it.

As mentioned in the important notes at the beginning of this article, we'll develop without Git integration in this tutorial.

For reference, you can learn about Git integration in dbt Projects on Snowflake in the following blog post:

https://dev.classmethod.jp/articles/try-dbt-projects-on-snowflake-with-github/

6. Initialize your dbt project and start developing

https://docs.getdbt.com/guides/snowflake?step=6

In this chapter, we'll set up the dbt Project. Let's move on to dbt Projects on Snowflake operations!

From the left menu in Snowflake, go to Projects, then click Workspaces.

Click + Add new in the upper left, then click dbt Project.

In the popup that appears, enter the following information and click Create in the bottom right:

Project Name: dbt_quickstart
- Any name is fine
Role: accountadmin
- The role used when queries are executed through dbt Projects in the Workspace. We're using ACCOUNTADMIN for this tutorial, but any role with read permissions for the databases, schemas, and tables to be used, and create permissions for the target database and schema where dbt will create objects, will work.
Warehouse: transforming
- The warehouse used when queries are executed through dbt Projects in the Workspace
Database: analytics
- The target database for dbt output
Schema: dbt_ssagara
- The development output schema for dbt

This operation creates a new dbt Project!

The information you entered is written to profiles.yml. The official documentation for profiles.yml is here:

https://docs.getdbt.com/docs/core/connect-data-platform/profiles.yml

Looking at the contents of profiles.yml, you'll see dev: listed. This is the name of the target that can be used to switch between environments (development, production, etc.) where dbt commands are executed. (You can set a name other than dev, but since the currently configured profile is for development, dev is appropriate.)

Also, target: dev means "use dev if no target is specified when running dbt commands." We'll add a production profile to profiles.yml later, but to avoid accidentally modifying the production environment with careless commands, it's recommended to keep target: dev unchanged.

Let's also try running dbt run at this point. dbt run is a command that executes all SELECT statements written in .sql files in the models folder as CTAS/CVAS statements against the specified database and schema.

Select Run from the command selection button in the upper right and press the play button.

Logs will appear at the bottom of the Workspace.

Once processing is complete, check the dbt_ssagara schema from the Object Explorer in the lower left of the Workspace. You can see that MY_FIRST_DBT_MODEL and MY_SECOND_DBT_MODEL, which were in the default models folder, have been created!

7. Build your first model

https://docs.getdbt.com/guides/snowflake?step=7

In this chapter, we'll create a new .sql file using the data loaded into Snowflake and run dbt run.

The word "model" appears here - in dbt, .sql files stored in the models folder are called "models."

Click the + next to the models folder, select SQL File, and create a file called customers.sql.

You can now edit customers.sql in the editor pane on the right. Copy and paste the following SQL:

with customers as (

    select
        id as customer_id,
        first_name,
        last_name

    from raw.jaffle_shop.customers

),

orders as (

    select
        id as order_id,
        user_id as customer_id,
        order_date,
        status

    from raw.jaffle_shop.orders

),

customer_orders as (

    select
        customer_id,

        min(order_date) as first_order_date,
        max(order_date) as most_recent_order_date,
        count(order_id) as number_of_orders

    from orders

    group by 1

),

final as (

    select
        customers.customer_id,
        customers.first_name,
        customers.last_name,
        customer_orders.first_order_date,
        customer_orders.most_recent_order_date,
        coalesce(customer_orders.number_of_orders, 0) as number_of_orders

    from customers

    left join customer_orders using (customer_id)

)

select * from final

After pasting, it should look like this:

To run dbt run, select Run from the command selection button in the upper right and press the play button.

Once processing is complete, check the dbt_ssagara schema from the Object Explorer in the lower left of the Workspace. You can see that a view called CUSTOMERS has been created.

8. Change the way your model is materialized

https://docs.getdbt.com/guides/snowflake?step=8

In this chapter, we'll experiment with Materialization, which determines how objects are created in Snowflake (as tables, views, etc.).

First, open dbt_project.yml. This is an essential file for dbt that handles overall project settings and folder path specifications.

Modify the models: code at the bottom as follows:

Before

models:
  dbt_quickstart:
    +materialized: view

After

models:
  dbt_quickstart:
    +materialized: table

Let's run dbt run in this state to see how the behavior changes. Select Run from the command selection button in the upper right and press the play button.

Once processing is complete, check the dbt_ssagara schema from the Object Explorer in the lower left of the Workspace. You should see that everything has been created as tables.

For reference, Materialization settings can be configured at the project or folder level in dbt_project.yml, and also specified within each .sql file. For example, looking at my_first_dbt_model.sql, you'll see a config specification as shown below. When the same config setting exists in both dbt_project.yml and a .sql file, the .sql file takes precedence.

As additional reference, Materialization includes not only tables and views but also incremental for incremental updates and ephemeral which doesn't create objects but is only used as a WITH clause. For details on these, please refer to the official documentation below:

https://docs.getdbt.com/docs/build/materializations

9. Delete the example models

https://docs.getdbt.com/guides/snowflake?step=9

In this chapter, we'll delete the sample .sql files that were added by default.

From the models folder, click the "..." next to my_first_dbt_model.sql and my_second_dbt_model.sql, and click Delete for each.

This completes what we need to do in this chapter, but it's important to note that dbt doesn't automatically delete the corresponding tables/views when .sql files are deleted. After deleting .sql files, you need to manually delete the tables/views through SQL commands or other methods.

10. Build models on top of other models

https://docs.getdbt.com/guides/snowflake?step=10

In this chapter, following dbt best practices, we'll break down the data cleanup logic and create separate .sql files (corresponding to the Staging layer).

First, create the following two new .sql files directly under the models folder for the Staging layer:

stg_customers.sql

select
    id as customer_id,
    first_name,
    last_name

from raw.jaffle_shop.customers

stg_orders.sql

select
    id as order_id,
    user_id as customer_id,
    order_date,
    status

from raw.jaffle_shop.orders

Next, update the previously created customers.sql with the following content. The key point is using {{ ref() }} notation to specify existing .sql file names, which builds processing dependencies ensuring that this .sql file executes after the specified .sql files have completed.

with customers as (

    select * from {{ ref('stg_customers') }}

),

orders as (

    select * from {{ ref('stg_orders') }}

),

customer_orders as (

    select
        customer_id,

        min(order_date) as first_order_date,
        max(order_date) as most_recent_order_date,
        count(order_id) as number_of_orders

    from orders

    group by 1

),

final as (

    select
        customers.customer_id,
        customers.first_name,
        customers.last_name,
        customer_orders.first_order_date,
        customer_orders.most_recent_order_date,
        coalesce(customer_orders.number_of_orders, 0) as number_of_orders

    from customers

    left join customer_orders using (customer_id)

)

select * from final

To view these model dependencies in the Workspace, run the dbt compile command which compiles each SQL file.

After this, click DAG in the bottom right of the Workspace to see the lineage showing the dependencies.

Finally for this chapter, let's run dbt run to output to the development schema. Select Run from the command selection button in the upper right and press the play button.

Once processing is complete, check the dbt_ssagara schema from the Object Explorer in the lower left of the Workspace. You can see that the two newly created .sql files have been output as tables.

11. Build models on top of sources

https://docs.getdbt.com/guides/snowflake?step=11

In this chapter, we'll configure "sources" which define the source data for dbt transformations. By configuring sources, you can display source data in the lineage and run tests on source data before performing dbt transformations (covered in Chapter 12).

First, create sources.yml under the models folder with the following content:

version: 2

sources:
    - name: jaffle_shop
      description: This is a replica of the Postgres database used by our app
      database: raw
      schema: jaffle_shop
      tables:
          - name: customers
            description: One record per customer.
          - name: orders
            description: One record per order. Includes cancelled and deleted orders.

Next, modify the two .sql files we created earlier for the Staging layer to reference sources. When referencing sources, use notation like {{ source('jaffle_shop', 'customers') }}.

stg_customers.sql

select
    id as customer_id,
    first_name,
    last_name

from {{ source('jaffle_shop', 'customers') }}

stg_orders.sql

select
    id as order_id,
    user_id as customer_id,
    order_date,
    status

from {{ source('jaffle_shop', 'orders') }}

To view the dependencies including sources in the Workspace, run the dbt compile command to compile each SQL file.

After this, click DAG in the bottom right of the Workspace to see the lineage including sources.

Also, by running dbt compile, you can check the compiled .sql files in the compiled folder within the target folder. As shown below, you can see that the source references have been replaced with the appropriate table names.

12. Add tests to your models

https://docs.getdbt.com/guides/snowflake?step=12

In this chapter, we'll try the data testing feature, which is unique to dbt.

Data tests are defined in YAML files, so create schema.yml in the models folder with the following content:

version: 2

models:
  - name: customers
    description: One record per customer
    columns:
      - name: customer_id
        description: Primary key
        data_tests:
          - unique
          - not_null
      - name: first_order_date
        description: NULL when a customer has not yet placed an order.

  - name: stg_customers
    description: This model cleans up customer data
    columns:
      - name: customer_id
        description: Primary key
        data_tests:
          - unique
          - not_null

  - name: stg_orders
    description: This model cleans up order data
    columns:
      - name: order_id
        description: Primary key
        data_tests:
          - unique
          - not_null
      - name: status
        data_tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id

In the above schema.yml, the key point for data test definitions is the data_tests section, where you define necessary tests for each column from the following four test types:

unique
- Tests whether the column values are unique across all records
not_null
- Tests whether the column values are not null across all records
accepted_values
- Tests whether the column values are one of the values specified in the list
relationships
- Technically, this checks referential integrity
- In the above code, it confirms that "the customer_id column in stg_orders consists of values from the customer_id column in stg_customers"

After creating schema.yml, run dbt test to actually perform the data tests.

After execution completes, check the logs from the bottom right of the Workspace. When each test is executed and shows PASS, it means the test has passed.

13. Document your models

https://docs.getdbt.com/guides/snowflake?step=13

This chapter covers trying dbt's documentation generation feature. However, as of August 13, 2025, dbt Projects on Snowflake cannot execute dbt docs generate as a standard feature, so we'll skip this.

14. Commit your changes

https://docs.getdbt.com/guides/snowflake?step=14

This chapter covers Git commit and push, assuming the use of dbt Cloud's Managed Repository. However, since we're excluding Git integration for this tutorial, we'll skip this chapter.

For reference, once again, you can learn about Git integration in dbt Projects on Snowflake in the following blog post:

https://dev.classmethod.jp/articles/try-dbt-projects-on-snowflake-with-github/

15. Deploy dbt

https://docs.getdbt.com/guides/snowflake?step=15

In this chapter, we'll deploy the developed content to the production environment. Note that dbt Projects on Snowflake requires completely different operations from dbt Cloud.

Adding production environment information to `profiles.yml`

First, add information about the schema where you want to output to production to profiles.yml.

Open profiles.yml and modify it as follows. The key point is adding a new target called prod with schema: PROD:

dbt_quickstart:
  target: dev
  outputs:
    dev:
      type: snowflake
      role: ACCOUNTADMIN
      warehouse: TRANSFORMING
      database: ANALYTICS
      schema: DBT_SSAGARA
      account: ''
      user: ''
    prod:
      type: snowflake
      role: ACCOUNTADMIN
      warehouse: TRANSFORMING
      database: ANALYTICS
      schema: PROD
      account: ''
      user: ''

Deploying the dbt Project to the specified schema

dbt Projects on Snowflake can be scheduled to run as tasks, but this requires deploying the dbt Project to a specified schema.

Click Connect in the upper right of the Workspace, then click Deploy dbt project.

Enter the following information and click Deploy:

Select location
- Database: ANALYTICS
- Schema: PROD *Note: We're using the same schema as the data output destination, but a different schema is also fine.
Select or create dbt project
- Click + Create dbt project
- Enter Name: dbt_quickstart

If deployment is successful, it will display as shown below. Looking at the prod schema, you can see that the dbt Project has been deployed.

Next, go to the worksheet and run the following query to define a task using the dbt Project. The key point is args='build --target prod', where build executes the dbt build command which alternately runs dbt run and dbt test for each model, and --target prod executes the dbt command against the production target prod we created earlier.

use role accountadmin;

create or alter task analytics.prod.dbt_execute
  warehouse = transforming
  schedule = 'using cron 0 9 1 1 * Asia/Tokyo' -- Runs at 9 AM on January 1st every year
as
execute dbt project analytics.prod.dbt_quickstart args='build --target prod';

After this, run the following command to manually execute the task:

execute task analytics.prod.dbt_execute;

For reference, you can check the execution status of the dbt project itself by clicking dbt projects from Monitoring on the left side of the screen.

After confirming the task succeeded, looking inside the prod schema, we can see that the tables have been successfully created!

Conclusion

I tried running the "Quickstart for dbt and Snowflake" tutorial with dbt Projects on Snowflake.

I think this is perfect content for learning about dbt itself while trying out dbt Projects on Snowflake! Please give it a try.

Personal Picks: Data Product News (July 23, 2025)

Sagara — Wed, 23 Jul 2025 05:04:05 +0000

※This is an English translation of the following article:
https://dev.classmethod.jp/articles/modern-data-stack-info-summary-20250723/

Hi, this is Sagara.

As a consultant in the Modern Data Stack field, I see a vast amount of information being released every day.

With so much happening, I'd like to use this article to summarize the Modern Data Stack-related news that has caught my eye over the past couple of weeks.

*Disclaimer: This is not an exhaustive list of all product updates. It only includes information that I found interesting based on my personal judgment and bias.

General Modern Data Stack

"Data Engineering Study #30 - Celebrating 30 Sessions! A Look Back and Forward at Data Engineering Tech and Careers with Past Speakers" was held.

Data Engineering Study #30 has taken place. To mark this 30th milestone, we welcomed back 10 past speakers for a 5-minute lightning talk session where they discussed the "aftermath" of the technical initiatives and their own careers they had previously talked about.

https://forkwell.connpass.com/event/357942/

For more details on Data Engineering Study #30, the following event report is very helpful. Please check it out as well.

https://zenn.dev/shinyaa31/articles/aaadbefa457197

Also, Yuzutaso, who has been the advisor for Data Engineering Study, has stepped down after this #30 event, and a new advisor team of the following three members has been formed. I, Sagara, have also become a member of this new advisor team!

As someone who has learned a lot about the technology and careers in the data engineering world by watching Data Engineering Study, I will do my best to make it even more exciting than before!

The next event, #31, will feature lightning talks and a public planning meeting by the three members of the advisor team. Please join us!

https://forkwell.connpass.com/event/363198/

Summer Data Engineering Roadmap

MotherDuck's blog has published an article outlining a roadmap for how to learn data engineering.

It's broken down into three levels—Foundation, Core, and Advanced—making it an easy-to-understand guide on where to start.

https://motherduck.com/blog/summer-data-engineering-roadmap/

Additionally, MotherDuck's blog has previously published articles summarizing which tools are useful for data engineering. These are also systematically organized and serve as a great reference.

https://motherduck.com/blog/data-engineering-toolkit-essential-tools/

https://motherduck.com/blog/data-engineering-toolkit-infrastructure-devops/

Databricks vs. Snowflake: The Final Chapter

A blog post from Orchestra explains their perspective that the rivalry between Databricks and Snowflake is over, and both companies are moving into new markets with different strategies (developer-focused vs. business-focused).

The following is just a summary of the article's content, but it outlines these predictions:

Databricks → To a Developer Platform
- Leveraging its strong developer community, it might aim to provide the best development environment on top of hyperscalers like Azure.
Snowflake → To the Business App Market
- Utilizing its customer base of business users, it might aim to become a "composable" application platform that could replace giant SaaS products like Salesforce.

https://www.getorchestra.io/blog/databricks-vs-snowflake-the-final-chapter

Data Extract/Load

Fivetran

Fivetran's Comparison Article with Airbyte

Fivetran's official blog has published an article comparing their service with Airbyte.

It's important to consider that this article is "written by Fivetran," and I felt some of the descriptions of Airbyte were a bit dated. (For example, there was no mention of Airbyte's Schema Change Management or Connector Builder).

However, it's still a useful reference for a rough understanding of the differences between the two companies.

https://www.fivetran.com/blog/fivetran-vs-airbyte-features-pricing-services-and-more

Data Warehouse/Data Lakehouse

Snowflake

"QUERY_INSIGHTS" View Released, Providing Analysis and Improvement Suggestions for Queries Executed in Snowflake

A new ACCOUNT_USAGE view, QUERY_INSIGHTS, has been released. It automatically analyzes the execution of queries within Snowflake and stores the results, identifying areas that may be impacting performance.

https://docs.snowflake.com/en/release-notes/2025/other/2025-07-03-query-insights

I tried it out myself, and I found it excellent that Snowflake handles the detection automatically. All the user has to do is check the QUERY_INSIGHTS view, review the findings, and take action. It simplifies the process greatly!

https://dev.classmethod.jp/articles/snowflake-query-insights-view/

Official Snowflake MCP Server with Cortex AI Functionality Released

Snowflake has officially released an MCP Server that supports Cortex AI features. It currently supports Cortex Search and Cortex Analyst.

https://github.com/Snowflake-Labs/mcp

An official blog post about this release also mentioned that Snowflake-managed MCP servers, where Snowflake manages the infrastructure, are planned for a future release.

https://www.snowflake.com/en/blog/mcp-servers-unify-extend-data-agents/

BigQuery

"source_column_match" and "null_markers NULL" Options Added to CREATE EXTERNAL TABLE and LOAD DATA

BigQuery has added source_column_match and null_markers NULL options to CREATE EXTERNAL TABLE and LOAD DATA, which are used for querying and loading data from external storage.

https://cloud.google.com/bigquery/docs/release-notes#July_22_2025

Databricks

RSS Feed for Databricks Release Notes

Databricks has started providing an RSS feed for its release notes, which includes the latest product information and other feature release notes.

https://docs.databricks.com/aws/en/release-notes/#feed

Recursive CTEs are in Public Preview in Databricks

As a new feature in Databricks, Recursive CTEs are now in public preview.

https://www.databricks.com/blog/introducing-recursive-common-table-expressions-databricks

MotherDuck/DuckDB

MotherDuck Announces New "Mega" and "Giga" Instances for Large, Complex Data Processing

To meet the demand for more intensive data processing that exceeds the capabilities of the existing "Jumbo" ducklings, MotherDuck has announced two new, larger instance sizes: "Mega" and "Giga."

According to the article, Mega is designed for large-scale workloads, while Giga is intended for extremely complex and massive transformation jobs for which there are no other alternatives.

https://motherduck.com/blog/announcing-mega-giga-instance-sizes-huge-scale/

For a list of instance types offered by MotherDuck, please see the official documentation below.

https://motherduck.com/docs/about-motherduck/billing/instances/

Data Transform

dbt

Build Iceberg Tables via BigLake Metastore in dbt-bigquery

Starting with the dbt-bigquery 1.10 release, it is now possible to build Iceberg tables via BigLake Metastore.

https://www.getdbt.com/blog/dbt-supports-apache-iceberg-tables-bigquery

https://docs.getdbt.com/docs/mesh/iceberg/bigquery-iceberg-support

Although it's still a preview feature, BigLake Metastore also provides Apache Iceberg REST Catalog functionality. This means it's now theoretically possible to "build an Iceberg table using dbt with BigLake Metastore as the catalog, and then query that Iceberg table from an external engine." (I'd love to try this out sometime...)

https://cloud.google.com/bigquery/docs/blms-rest-catalog?hl=en

Business Intelligence

Tableau

Analyzing Pro Wrestling Match Videos with Generative AI on Vertex AI and Tableau

rtama published an article on converting pro wrestling match videos into structured data and analyzing it with Tableau.

This article is an excellent reference for understanding what kind of prompts can be used to convert video into structured data suitable for analysis. (The choice of pro wrestling as a subject is also brilliant!)

https://zenn.dev/cavernaria/articles/ec04775eec5c4b

Lightdash

Lightdash Now Supports dbt Fusion and dbt 1.10

Lightdash has announced support for dbt Fusion and dbt 1.10.

https://changelog.lightdash.com/we-now-support-dbt-fusion-and-dbt-1-10-projects-319440

One key point to note is that from dbt 1.10 onwards, meta: must be defined under config:. For Lightdash users, migrating this part will be a significant hurdle.

Recognizing this challenge, Lightdash has released a Migration Guide and a migration tool called MetaMove.

https://docs.lightdash.com/dbt-guides/dbt-1.10-migration

https://docs.lightdash.com/dbt-guides/dbt-fusion-migration

Omni

Official Omni MCP Server Released

Omni has released its official MCP Server. This enhancement expands the potential of Omni, as it allows for connecting the semantic models built in Omni with other tools to obtain more accurate answers.

https://omni.co/blog/introducing-omnis-mcp-server

https://docs.omni.co/docs/ai/mcp

Data Catalog

Select Star

Select Star Releases MCP Server

Select Star has released a new managed MCP Server. It uses a Select Star API token for authentication.

https://www.selectstar.com/resources/data-mcp-for-ai-agents

https://docs.selectstar.com/features/mcp-server

OpenMetadata

Collate Raises $10M in Series A Funding

Collate, the company that supports the OpenMetadata project and offers a SaaS version, has raised $10 million in Series A funding.

https://blog.open-metadata.org/collate-raises-series-a-funding-to-accelerate-openmetadata-growth-4c859f0b9813

Data Activation (Reverse ETL)

Census (A Fivetran Company)

Census Releases "Mesh Datasets" for Cross-Warehouse Queries

Census has released a new feature called "Mesh Datasets," which allows users to execute queries across multiple data warehouses.

It seems you just need to write queries using PostgreSQL syntax. (The image below is from the linked blog post.)

https://www.getcensus.com/blog/break-down-data-silos-with-cross-warehouse-sql-introducing-mesh-datasets

https://docs.getcensus.com/datasets/overview/mesh-datasets