DEV Community: Sagara

Hosting Elementary Reports with Snowflake App Runtime Without Docker Image Management

Sagara — Tue, 07 Jul 2026 14:19:04 +0000

This article is the English translation version of the original Japanese article.

https://dev.classmethod.jp/articles/snowflake-try-appruntime-with-elementary-report/

I'm Sagara.

Previously, I wrote an article about building an environment where you can view Elementary reports on the web using Snowpark Container Services.

https://dev.classmethod.jp/articles/snowpark-container-services-for-elementary-report/

After that, I also wrote an article for teams using Databricks, where I tried doing the same thing with Databricks Apps.

https://dev.classmethod.jp/articles/databricks-apps-hosting-elementary/

In the SPCS version, I had to create a Dockerfile, build a Docker image, push it to an Image Repository, create a Compute Pool, and create a service using snow spcs service create. Since an Elementary report is essentially just static files named elementary_report.html and elementary_output.json, building and pushing my own container image honestly felt a bit excessive.

Meanwhile, Snowflake App Runtime, which allows you to deploy Node.js applications, mainly Next.js applications, directly on Snowflake, has been released in Public Preview.

https://docs.snowflake.com/en/developer-guide/snowflake-app-runtime/about-snowflake-app-runtime

https://zenn.dev/snowflakejp/articles/9e5b8fa393ccd8

With Snowflake App Runtime, you can deploy an application using only snow app deploy from Snowflake CLI, without using a Dockerfile, Docker image build, or Image Repository. In this article, I tried solving the same problem — hosting Elementary reports — with Snowflake App Runtime.

:::message
Snowflake App Runtime internally runs as an Application Service on Snowpark Container Services. In the title, “without Docker” means that users do not need to manage Dockerfiles or container images themselves. Please keep this point in mind.
:::

Feature overview

Snowflake App Runtime is a feature that allows you to deploy full-stack web applications written in Node.js, especially Next.js, directly within Snowflake’s security boundary. You can also query Snowflake tables directly from your application code without going through an API layer or ETL process.

The deployment flow is as follows: initialize the project with snow app setup, then run snow app deploy to upload, build, and deploy everything at once. Once deployment is complete, an authenticated URL like the following is issued, and users logged in to Snowflake can access the application directly from their browser.

https://<id>-<org>-<account>-<region>.snowflakecomputing.app/

Internally, it runs on Snowpark Container Services as a dedicated object type called Application Service. Since this is a different object type from traditional SPCS services, note that it does not appear in SHOW SERVICES; you need to check it with SHOW APPLICATION SERVICES.

Limitations

As of July 7, 2026, this is a Public Preview feature. Specifications may change before GA.
Trial accounts are not supported; a paid account is required.
Snowflake CLI 3.17 or later is required. Older versions do not have the snow app setup command.
During Public Preview, a managed Compute Pool is forcibly used.
If you do not explicitly specify the database and schema when running snow app setup, the application is deployed to a personal database by default, such as USER$<username>. Applications deployed to a personal database cannot be shared with other roles.
Each deployment creates a new version instead of overwriting the existing version.
Supported stacks are mainly Node.js/Next.js, and Python support is planned as of July 7, 2026.

Auto-suspend and cost considerations

Applications deployed with Snowflake App Runtime internally run as Application Services. Therefore, from a cost perspective, you need to be aware of the Application Service lifecycle.

Application Service has an auto-suspend setting called AUTO_SUSPEND_SECS, but the default value is 0, meaning auto-suspend is disabled. If you leave an application created for testing purposes running, compute costs may continue to be incurred unintentionally, so you should be careful.

AUTO_SUSPEND_SECS can be configured with CREATE APPLICATION SERVICE or ALTER APPLICATION SERVICE. However, as far as I checked on July 7, 2026, I could not find a way to specify it directly during deployment via snow app deploy options or snowflake.yml.

Therefore, configure it after deployment as needed, as shown below.

ALTER APPLICATION SERVICE
  SAGARA_SNOWFLAKE_APPS.PUBLIC.ELEMENTARY_REPORT_APP
SET
  AUTO_RESUME = TRUE,
  AUTO_SUSPEND_SECS = 900;

In the example above, the Application Service is automatically suspended after 15 minutes of inactivity. The minimum non-zero value that can be specified for AUTO_SUSPEND_SECS is 300 seconds.

When an Application Service is suspended, its container stops and compute billing also stops. For test environments or internal applications that are used infrequently, it is safer to set AUTO_SUSPEND_SECS after running snow app deploy.

For reference, the Consumption Table listed the App Runtime unit price as shown below as of July 7, 2026.

Prerequisites

This validation assumes that the following have already been completed.

Snowflake: A paid account, not a Trial account. The account must be able to use Snowflake App Runtime.
Feature status: Public Preview as of July 7, 2026.
Snowflake CLI: Version 3.17 or later is installed and can connect to the target account.
Node.js: Installed for local testing.
Elementary report files: elementary_report.html and elementary_output.json have already been generated.

The Snowflake object names used in this article are standardized as follows.

Object	Name
Database	`SAGARA_SNOWFLAKE_APPS`
Schema	`PUBLIC`
Warehouse	`SAGARA_SNOWFLAKE_APPS_QUERY_WH`
App name	`elementary_report_app`
Application Service	`ELEMENTARY_REPORT_APP`

Preparation

Check Snowflake CLI version and connection

First, confirm that the Snowflake CLI version is 3.17 or later.

snow --version

Also check whether the snow app setup command exists. If you are using an older version, please upgrade it.

snow app setup --help

Next, check the connection to the target account.

snow connection test

If you use multiple connections, specify the connection name. Add --connection <connection_name> to the following commands as needed.

snow connection test --connection <connection_name>

If the connection information is displayed correctly, you are good to go.

Also check the Node.js version.

node --version

Check Elementary report files

This article assumes that the Elementary report has already been generated at the root of your dbt project.

dbt_project/
├── dbt_project.yml
├── models/
├── edr_target/
│   ├── elementary_report.html
│   └── elementary_output.json

What I tried

1. Create a directory for App Runtime

Create a local directory for App Runtime and copy the Elementary report files.

mkdir -p elementary-report-app/public

Rename elementary_report.html to index.html when copying it. This allows the Elementary report to be displayed simply by accessing the root URL of the application.

cp edr_target/elementary_report.html elementary-report-app/public/index.html
cp edr_target/elementary_output.json elementary-report-app/public/elementary_output.json

cd elementary-report-app

The final directory structure is as follows. We will create each file from here.

elementary-report-app/
├── app.yml
├── package.json
├── server.mjs
├── snowflake.yml        # Generated by snow app setup
└── public/
    ├── index.html
    └── elementary_output.json

2. Create server.mjs

Create server.mjs, which starts an HTTP server on Snowflake App Runtime. In the SPCS version, I served static files using an Nginx container, but this time I will use a minimal HTTP server built with Node.js.

Save the following content as elementary-report-app/server.mjs.

// server.mjs
import http from "node:http";
import { readFile, stat, readdir } from "node:fs/promises";
import path from "node:path";
import { fileURLToPath } from "node:url";

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// Use the App Runtime execution package root as the document root
const documentRoot = __dirname;

const port = Number(process.env.PORT || process.env.SERVER_PORT || 8080);
const host = "0.0.0.0";

const mimeTypes = {
  ".html": "text/html; charset=utf-8",
  ".json": "application/json; charset=utf-8",
  ".js": "text/javascript; charset=utf-8",
  ".css": "text/css; charset=utf-8",
  ".svg": "image/svg+xml",
  ".png": "image/png",
  ".jpg": "image/jpeg",
  ".jpeg": "image/jpeg",
  ".ico": "image/x-icon",
  ".map": "application/json; charset=utf-8",
  ".txt": "text/plain; charset=utf-8",
};

async function listFilesRecursive(dir, baseDir = dir) {
  const entries = await readdir(dir, { withFileTypes: true });
  const files = [];

  for (const entry of entries) {
    const fullPath = path.join(dir, entry.name);
    const relativePath = path.relative(baseDir, fullPath);

    if (entry.isDirectory()) {
      const childFiles = await listFilesRecursive(fullPath, baseDir);
      files.push(...childFiles);
    } else {
      files.push(relativePath);
    }
  }

  return files;
}

function safeResolve(urlPath) {
  const decodedPath = decodeURIComponent(urlPath.split("?")[0]);

  // Return index.html for root access
  const normalizedPath = decodedPath === "/" ? "/index.html" : decodedPath;

  const filePath = path.normalize(path.join(documentRoot, normalizedPath));

  // Prevent directory traversal
  if (!filePath.startsWith(documentRoot)) {
    return null;
  }

  return filePath;
}

const server = http.createServer(async (req, res) => {
  try {
    if (!req.url) {
      res.writeHead(400, {
        "content-type": "text/plain; charset=utf-8",
      });
      res.end("Bad Request");
      return;
    }

    console.log(JSON.stringify({
      message: "request",
      url: req.url,
      cwd: process.cwd(),
      dirname: __dirname,
      documentRoot,
    }));

    if (req.url === "/healthz") {
      res.writeHead(200, {
        "content-type": "text/plain; charset=utf-8",
      });
      res.end("ok");
      return;
    }

    // For debugging: check the list of files available in the runtime environment
    if (req.url === "/__debug/files") {
      const files = await listFilesRecursive(documentRoot);

      res.writeHead(200, {
        "content-type": "application/json; charset=utf-8",
      });
      res.end(JSON.stringify({
        cwd: process.cwd(),
        dirname: __dirname,
        documentRoot,
        files,
      }, null, 2));
      return;
    }

    const filePath = safeResolve(req.url);

    console.log(JSON.stringify({
      message: "resolved file path",
      url: req.url,
      filePath,
    }));

    if (!filePath) {
      res.writeHead(403, {
        "content-type": "text/plain; charset=utf-8",
      });
      res.end("Forbidden");
      return;
    }

    const fileStat = await stat(filePath);

    if (!fileStat.isFile()) {
      res.writeHead(404, {
        "content-type": "text/plain; charset=utf-8",
      });
      res.end("Not Found");
      return;
    }

    const body = await readFile(filePath);
    const ext = path.extname(filePath).toLowerCase();
    const contentType = mimeTypes[ext] || "application/octet-stream";

    res.writeHead(200, {
      "content-type": contentType,
      "cache-control": "no-store",
    });
    res.end(body);
  } catch (err) {
    console.error(JSON.stringify({
      message: "request failed",
      url: req.url,
      error: err instanceof Error ? err.message : String(err),
    }));

    res.writeHead(404, {
      "content-type": "text/plain; charset=utf-8",
    });
    res.end("Not Found");
  }
});

server.listen(port, host, () => {
  console.log(JSON.stringify({
    message: "Elementary report server started",
    url: `http://${host}:${port}`,
    cwd: process.cwd(),
    dirname: __dirname,
    documentRoot,
  }));
});

The key points are as follows.

It reads the port number from the PORT or SERVER_PORT environment variable, and defaults to 8080 if neither is set.
It listens on 0.0.0.0 so that it can receive requests from the Application Service.
It provides a /healthz endpoint that can be used for health checks.
It includes directory traversal protection using path.normalize and startsWith.
It does not depend on any external npm packages and uses only standard Node.js modules.

3. Create package.json

Next, create package.json.

{
  "name": "elementary-report-app",
  "version": "1.0.0",
  "private": true,
  "type": "module",
  "engines": {
    "node": ">=22"
  },
  "scripts": {
    "start": "node server.mjs"
  }
}

Since this configuration does not use any external npm packages, npm install and node_modules are basically unnecessary.

4. Create app.yml

app.yml is the file that defines the build, execution, and packaging behavior for App Runtime.

run:
  command: [node, server.mjs]

artifacts:
  - src: "server.mjs"
    dest: "."
  - src: "package.json"
    dest: "."
  - src: "public/index.html"
    dest: "."
  - src: "public/elementary_output.json"
    dest: "."

profile:
  label: "Elementary Report"
  description: "Static hosting for Elementary report on Snowflake App Runtime"

Here, the following are defined.

run.command: The command executed when the application starts.
artifacts: Files to include in the deployment target. This includes server.mjs, package.json, and the Elementary report files under public/.
profile: The application label and description.

Be careful that if the destination path of the files under public/ and the path referenced by server.mjs do not match, the Elementary report will return 404 after deployment. Details are described later in the “Issue I ran into” section.

5. Test locally

Before deploying to Snowflake, verify locally that server.mjs works as expected.

npm start

In another terminal, run a health check.

curl http://localhost:8080/healthz

If the following is returned, it is OK.

ok

Open http://localhost:8080 in your browser and confirm that the Elementary report screen is displayed.

Once confirmed, stop the server with Ctrl + C.

6. Create Snowflake objects

Create a Database and Warehouse for testing. Use PUBLIC as the Schema.

USE ROLE ACCOUNTADMIN;

CREATE DATABASE IF NOT EXISTS SAGARA_SNOWFLAKE_APPS;

CREATE WAREHOUSE IF NOT EXISTS SAGARA_SNOWFLAKE_APPS_QUERY_WH
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

Normally, the PUBLIC schema is also created when the Database is created, but if you want to create it explicitly, run the following as well.

CREATE SCHEMA IF NOT EXISTS SAGARA_SNOWFLAKE_APPS.PUBLIC;

Grant privileges to the role that deploys the application as needed. For example, if deploying with the SYSADMIN role, run the following.

USE ROLE ACCOUNTADMIN;

GRANT USAGE ON WAREHOUSE SAGARA_SNOWFLAKE_APPS_QUERY_WH TO ROLE SYSADMIN;

GRANT USAGE ON DATABASE SAGARA_SNOWFLAKE_APPS TO ROLE SYSADMIN;
GRANT USAGE ON SCHEMA SAGARA_SNOWFLAKE_APPS.PUBLIC TO ROLE SYSADMIN;

GRANT CREATE APPLICATION SERVICE ON SCHEMA SAGARA_SNOWFLAKE_APPS.PUBLIC TO ROLE SYSADMIN;
GRANT CREATE STAGE ON SCHEMA SAGARA_SNOWFLAKE_APPS.PUBLIC TO ROLE SYSADMIN;

7. Set up Snowflake App Runtime

Create the configuration for App Runtime. By explicitly specifying the deployment destination, you can avoid deploying to a personal database.

snow app setup \
  --app-name elementary_report_app \
  --database SAGARA_SNOWFLAKE_APPS \
  --schema PUBLIC \
  --warehouse SAGARA_SNOWFLAKE_APPS_QUERY_WH

If you use multiple connections, specify the connection name.

snow app setup \
  --connection <connection_name> \
  --app-name elementary_report_app \
  --database SAGARA_SNOWFLAKE_APPS \
  --schema PUBLIC \
  --warehouse SAGARA_SNOWFLAKE_APPS_QUERY_WH

After execution, snowflake.yml is generated automatically. The following is the content of the file generated when I ran the command.

definition_version: "2"

entities:
  elementary_report_app:
    type: snowflake-app
    identifier:
      name: ELEMENTARY_REPORT_APP
      database: SAGARA_SNOWFLAKE_APPS
      schema: PUBLIC
    artifacts:
      - src: ./*
        dest: ./
        ignore:
          - node_modules
          - .env*
          - __pycache__
          - "*.pyc"
          - .next
          - .git
          - snowflake.log

    query_warehouse: SAGARA_SNOWFLAKE_APPS_QUERY_WH
    code_stage: ELEMENTARY_REPORT_APP_CODE

If database and schema are set to a personal database, such as USER$<username>, the application cannot be shared with your team. Explicitly specify the --database and --schema options and rerun snow app setup.

8. Deploy

Run snow app deploy to upload, build, and deploy to Snowflake all at once.

snow app deploy

If you want to explicitly specify the connection name, run the following.

snow app deploy --connection <connection_name>

Even if the log appears to be stuck at the same point, wait until a message like App ready at https://... is displayed.

After a few minutes, if the following is displayed, the deployment has succeeded.

9. Open the app and display the report

Once deployment is complete, open the application in your browser with snow app open.

snow app open

If you want to explicitly specify the connection name, run the following.

snow app open --connection <connection_name>

If you only want to display the URL, add --print-only. Since I tested this on Ubuntu 24.04 LTS running on WSL2, I ran the following command.

snow app open --print-only

Paste this URL into your browser, authenticate with Snowflake, and if the Elementary report is displayed in the browser, it is successful.

10. Share with other roles

If you want to share the application with team members, grant the USAGE privilege to the target role. In this example, I share it with the ANALYST role.

USE ROLE ACCOUNTADMIN;

GRANT USAGE ON DATABASE SAGARA_SNOWFLAKE_APPS TO ROLE ANALYST;

GRANT USAGE ON SCHEMA SAGARA_SNOWFLAKE_APPS.PUBLIC TO ROLE ANALYST;

GRANT USAGE ON APPLICATION SERVICE
  SAGARA_SNOWFLAKE_APPS.PUBLIC.ELEMENTARY_REPORT_APP
  TO ROLE ANALYST;

This allows users with the ANALYST role to access the App Runtime URL through Snowflake authentication.

11. Check the Application Service

The created Application Service can be checked with SHOW APPLICATION SERVICES, not SHOW SERVICES.

USE DATABASE SAGARA_SNOWFLAKE_APPS;
USE SCHEMA PUBLIC;

SHOW APPLICATION SERVICES;

If you want to filter only the target Application Service, run the following.

SHOW APPLICATION SERVICES LIKE 'ELEMENTARY_REPORT_APP';

If ELEMENTARY_REPORT_APP is displayed, it is OK.

12. Flow when updating the report

After regenerating the Elementary report, replace the files under public/ and redeploy.

If you are working from the root of the dbt project, run the following.

cp edr_target/elementary_report.html elementary-report-app/public/index.html
cp edr_target/elementary_output.json elementary-report-app/public/elementary_output.json

cd elementary-report-app

snow app deploy

If you are already in the elementary-report-app directory, run the following.

cp ../edr_target/elementary_report.html public/index.html
cp ../edr_target/elementary_output.json public/elementary_output.json

snow app deploy

Internally, a new version is created, and the Application Service reference is switched to the new version. As a result, you can access the latest Elementary report using the same URL. If these commands are incorporated into a CI/CD pipeline or executed after dbt runs, report updates could also be automated.

13. Stop the deployed app

Finally, I will also describe how to stop the App Runtime application after testing.

An application deployed with Snowflake App Runtime runs as an Application Service.

If it is no longer needed, you can delete the Application Service with snow app teardown.

snow app teardown --connection <connection_name>

If you want to skip the confirmation prompt, add --force.

snow app teardown --connection <connection_name> --force

This command deletes the target Application Service based on the current project definition, namely snowflake.yml inside the elementary-report-app directory.

Therefore, you should basically run it in the directory where snowflake.yml exists.

cd elementary-report-app

snow app teardown --connection <connection_name>

When you actually run it, it is displayed as shown below.

After deletion, you can confirm that the Application Service no longer exists with the following SQL.

USE DATABASE SAGARA_SNOWFLAKE_APPS;
USE SCHEMA PUBLIC;

SHOW APPLICATION SERVICES LIKE 'ELEMENTARY_REPORT_APP';

If ELEMENTARY_REPORT_APP is not displayed, the deletion is complete.

Note that snow app teardown deletes the Application Service and related files on the code stage, but it does not delete the Database or Warehouse created in this article.

If the Snowflake objects created for testing are also no longer needed, delete them separately as follows.

USE ROLE ACCOUNTADMIN;

DROP WAREHOUSE IF EXISTS SAGARA_SNOWFLAKE_APPS_QUERY_WH;
DROP DATABASE IF EXISTS SAGARA_SNOWFLAKE_APPS;

If you want to start the stopped application again, simply deploy it again from the same project directory.

snow app deploy --connection <connection_name>

Issue I ran into: Not Found after opening the URL post-deployment

This time, I encountered an issue where snow app deploy completed successfully and an App Runtime URL was issued, but opening the URL in the browser showed Not Found.

In the deployment log, public/index.html and public/elementary_output.json were uploaded to the stage as follows.

Uploaded public/elementary_output.json -> ...
Uploaded public/index.html -> ...

At first, I thought, “The files should have been uploaded, so why?” But the cause was that after being uploaded to the stage, the files did not exist at the path referenced by the Node.js server inside the App Runtime execution package.

Initially, I specified the following in app.yml.

artifacts:
  - src: "public/**"
    dest: "public"

On the other hand, server.mjs was implemented to reference public/index.html.

However, the files were not arranged as expected in the runtime environment, and as a result, index.html could not be found and Not Found was returned.

In the end, instead of preserving the public/ directory inside the execution package, I changed the configuration to place the required static files at the package root.

artifacts:
  - src: "server.mjs"
    dest: "."
  - src: "package.json"
    dest: "."
  - src: "public/index.html"
    dest: "."
  - src: "public/elementary_output.json"
    dest: "."

I also changed server.mjs to treat the execution package root as the document root.

const documentRoot = __dirname;

As a result, the files were arranged as follows on App Runtime, and index.html could be returned correctly from the root URL.

/
├── server.mjs
├── package.json
├── index.html
└── elementary_output.json

If snow app deploy succeeds but the screen shows Not Found, as in this case, it may not be an endpoint creation issue. Instead, the file placement in the App Runtime execution environment and the reference path in the application may be misaligned.

It was helpful to prepare /healthz and a simple debug endpoint that returns the list of files in the runtime environment, as it made it easier to identify the cause.

Comparison with the SPCS version and Databricks Apps version

Compared with the two configurations I tried previously, the differences are as follows.

Item	SPCS version	Databricks Apps version	App Runtime version, this article
Static file serving	Nginx container	Python `http.server`	Minimal HTTP server with Node.js
Dockerfile	Required	Not required	Not required
Docker build	Required	Not required	Not required
Image Repository	Required	Not required	Not required
docker push	Required	Not required	Not required
Compute Pool	Created manually	Select Compute Size for Databricks Apps	Managed Pool handled by App Runtime
Sharing method	Grant to SERVICE ROLE	Grant `CAN USE` permission on Databricks Apps side	Grant `USAGE` to Application Service

The following tasks required in the SPCS version are no longer necessary in the App Runtime version.

Creating a Dockerfile
Configuring Nginx
Building a Docker image
Creating a Snowflake Image Repository
docker login / docker push
Creating a Compute Pool
Creating a service spec YAML
snow spcs service create / snow spcs service list-endpoints

Instead, only the following files and commands are needed. Once the application files are ready, you can immediately deploy the app and share it with users in your Snowflake account. This is a major strength of Snowflake App Runtime!

server.mjs
package.json
app.yml
snowflake.yml, generated by snow app setup
snow app setup / snow app deploy / snow app open

Conclusion

I tried hosting an Elementary report without Docker by using Snowflake App Runtime.

Compared with SPCS, Snowflake App Runtime removes the entire burden of container image management. For lightweight internal web publishing use cases like Elementary reports, Snowflake App Runtime looks like a very easy-to-use option. Since the specifications may change toward GA, I will continue to keep an eye on it!

New in Snowflake: Replace Your Table + View Pattern with Virtual Columns

Sagara — Sat, 06 Jun 2026 14:43:45 +0000

📝 This is the English translation of the following article:
https://dev.classmethod.jp/articles/snowflake-try-virtual-columns/

Hi, I'm Sagara.

Snowflake's new feature, Virtual Columns, has reached GA. This feature allows you to define columns whose values are computed from an expression at query execution time, rather than storing values in the table. Derived columns that were previously implemented using Views can now be defined directly on the table itself, allowing you to replace a table-and-View pair with a single table.

https://docs.snowflake.com/en/release-notes/2026/10_19

https://docs.snowflake.com/en/sql-reference/virtual-columns

I tried out several usage patterns, so I'll walk through the steps and results below.

Feature Overview

Virtual Columns are virtual columns defined by specifying an AS (<expr>) clause in the table definition. Values are not stored in the table — Snowflake computes them from the expression at query execution time.

They are defined using CREATE TABLE or ALTER TABLE syntax.

-- Define at CREATE TABLE time
CREATE OR REPLACE TABLE <table> (
  <col_name> <col_type> AS ( <expr> )
);

-- Add later with ALTER TABLE
ALTER TABLE <table> ADD COLUMN <col_name> <col_type> AS ( <expr> );

They serve a similar purpose to derived columns in a View's SELECT clause, but since they are defined on the table itself, there is no need to separately create and manage a View.

Limitations

Here is a summary of limitations and notes as of June 6, 2026.

Expressions not allowed in virtual columns:

Non-deterministic functions: Functions that return different values on each execution, such as RANDOM(), CURRENT_TIMESTAMP, CURRENT_DATE, UUID_STRING, etc.
Aggregate functions: SUM, AVG, COUNT, etc. Virtual columns are evaluated per row, so aggregation is not possible.
Window functions: Functions using the OVER clause
Subqueries: Nested SELECT statements
User-defined functions (UDFs): SQL, JavaScript, and external UDFs are all disallowed
Bind variables: Bind parameters such as ?, :1, :value, etc.
Session variables: Variables set with SET
Positional column references: References like $1, $2 (the $1 pseudo-column is allowed in virtual columns for external tables)
References to columns with DEFAULT values: Columns with DEFAULT values cannot be referenced in virtual column expressions. Virtual columns themselves cannot have DEFAULT values either.

Other limitations:

NOT NULL and CHECK constraints cannot be set
Virtual columns cannot be used as clustering keys for a table
Columns referenced by a virtual column (whether base columns or other virtual columns) cannot be dropped with DROP COLUMN
When a virtual column references another virtual column, the referenced virtual column must be defined first (forward references are not allowed)
Data type compatibility rules apply — the declared type must be compatible with the inferred type of the expression (numeric scale, string length, timestamp precision, etc.)

Known Issues:

If a virtual column is defined with an expression that converts NULL to non-NULL (e.g., COALESCE) and is used in an outer join (LEFT JOIN), non-NULL values may be returned for rows on the preserved side where the join does not match. The workaround is to "not include COALESCE in the virtual column expression, and instead apply it in the SELECT clause."

Prerequisites

Creating the Database, Schema, and Warehouse

Create a database, schema, and warehouse for this verification. If you're using existing ones, replace them as appropriate.

USE ROLE SYSADMIN;

-- Create warehouse
CREATE WAREHOUSE IF NOT EXISTS VIRTUAL_COL_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND   = 60
  AUTO_RESUME    = TRUE;

-- Create database and schema
CREATE DATABASE IF NOT EXISTS VIRTUAL_COL_DB;
CREATE SCHEMA IF NOT EXISTS VIRTUAL_COL_DB.PUBLIC;

-- Set working context
USE WAREHOUSE VIRTUAL_COL_WH;
USE DATABASE VIRTUAL_COL_DB;
USE SCHEMA PUBLIC;

Creating Sample Tables and Inserting Data

Create a sample table orders_raw modeled after e-commerce order data, and insert test data.

CREATE OR REPLACE TABLE orders_raw (
  order_id      INT,
  customer_name STRING,
  email         STRING,
  order_date    DATE,
  product_name  STRING,
  quantity      INT,
  unit_price    NUMBER(10, 2)
);

INSERT statements for test data (click to expand)

INSERT INTO orders_raw
  (order_id, customer_name, email, order_date, product_name, quantity, unit_price)
VALUES
  (1,  'Taro Yamada',      'taro.yamada@example.com',        '2026-01-05', 'Wireless Mouse',           2,  2500.00),
  (2,  'Hanako Suzuki',    'hanako.suzuki@dev.co.jp',         '2026-01-07', 'Mechanical Keyboard',      1, 12800.00),
  (3,  'Jiro Sato',        'jiro.sato@mail.example.org',      '2026-01-10', 'USB Hub',                  3,  1980.00),
  (4,  'Misaki Tanaka',    'misaki.tanaka@example.com',       '2026-01-12', 'Laptop Stand',             1,  4500.00),
  (5,  'Kenichi Nakamura', 'kenichi.nakamura@company.jp',     '2026-01-15', 'HDMI Adapter',             5,   890.00),
  (6,  'Yuko Watanabe',    'yuko.watanabe@example.com',       '2026-01-18', 'Webcam',                   1,  8900.00),
  (7,  'Makoto Ito',       'makoto.ito@dev.co.jp',            '2026-01-20', 'Wireless Mouse',           1,  2500.00),
  (8,  'Yumi Kato',        'yumi.kato@mail.example.org',      '2026-01-22', 'Portable SSD',             2, 15800.00),
  (9,  'Takashi Yoshida',  'takashi.yoshida@example.com',     '2026-01-25', 'Keyboard Wrist Rest',      1,  2200.00),
  (10, 'Yumiko Yamamoto',  'yumiko.yamamoto@company.jp',      '2026-01-28', 'LED Desk Lamp',            1,  5500.00),
  (11, 'Daisuke Matsumoto','daisuke.matsumoto@example.com',   '2026-02-02', 'Mechanical Keyboard',      2, 12800.00),
  (12, 'Sakura Inoue',     'sakura.inoue@dev.co.jp',          '2026-02-05', 'Laptop Stand',             2,  4500.00),
  (13, 'Asako Kimura',     'asako.kimura@mail.example.org',   '2026-02-08', 'USB Hub',                  1,  1980.00),
  (14, 'Takuya Hayashi',   'takuya.hayashi@example.com',      '2026-02-10', 'HDMI Adapter',             3,   890.00),
  (15, 'Michiko Saito',    'michiko.saito@company.jp',        '2026-02-12', 'Portable SSD',             1, 15800.00),
  (16, 'Hiroshi Shimizu',  'hiroshi.shimizu@example.com',     '2026-02-15', 'Webcam',                   2,  8900.00),
  (17, 'Mina Yamaguchi',   'mina.yamaguchi@dev.co.jp',        '2026-02-18', 'LED Desk Lamp',            3,  5500.00),
  (18, 'Masaru Ikeda',     'masaru.ikeda@mail.example.org',   '2026-02-20', 'Wireless Mouse',           4,  2500.00),
  (19, 'Akari Hashimoto',  'akari.hashimoto@example.com',     '2026-02-22', 'Keyboard Wrist Rest',      2,  2200.00),
  (20, 'Yuta Ishikawa',    'yuta.ishikawa@company.jp',        '2026-02-25', 'Mechanical Keyboard',      1, 12800.00),
  (21, 'Sakura Maeda',     'sakura.maeda@example.com',        '2026-03-01', 'Laptop Stand',             3,  4500.00),
  (22, 'Sho Fujita',       'sho.fujita@dev.co.jp',            '2026-03-04', 'Portable SSD',             2, 15800.00);

You're good to go once 22 rows have been inserted.

Let's Try It Out

1. Basic: Creating a Virtual Column with Arithmetic

As the simplest pattern, let's add a virtual column total_price that calculates the total amount using quantity × unit_price via ALTER TABLE.

ALTER TABLE orders_raw
  ADD COLUMN total_price NUMBER(12, 2) AS ((quantity * unit_price)::NUMBER(12, 2));

After adding it, run a SELECT to verify.

SELECT order_id, customer_name, quantity, unit_price, total_price
FROM orders_raw
ORDER BY order_id
LIMIT 5;

You're good if total_price shows the result of quantity × unit_price. The value is not stored in the table — it's computed on every query.

2. Referencing a Virtual Column from Another Virtual Column (Chained Definition)

Next, let's add a virtual column tax_amount (10% tax) that references the previously defined virtual column total_price.

ALTER TABLE orders_raw
  ADD COLUMN tax_amount NUMBER(12, 2) AS ((total_price * 0.1)::NUMBER(12, 2));

SELECT order_id, total_price, tax_amount
FROM orders_raw
ORDER BY order_id
LIMIT 5;

You're good if tax_amount equals total_price × 0.1. As shown here, you can define a virtual column that references another previously defined virtual column.

3. Creating a Derived Column with String Functions

As an example of string manipulation, let's add a virtual column email_domain that extracts the email domain from the email column. We use POSITION to find the position of @ and SUBSTR to extract the string after it.

ALTER TABLE orders_raw
  ADD COLUMN email_domain STRING AS (SUBSTR(email, POSITION('@', email) + 1));

SELECT customer_name, email, email_domain
FROM orders_raw
ORDER BY order_id
LIMIT 5;

You're good if the email_domain column shows domain parts like example.com or dev.co.jp. Deterministic built-in string functions like SUBSTR and POSITION can be used in virtual column expressions.

4. Creating a Classification Column with a CASE Expression

Let's use a CASE expression to add a virtual column price_tier that classifies orders by total amount. It references the virtual column total_price to determine the price bracket.

ALTER TABLE orders_raw
  ADD COLUMN price_tier STRING AS (
    CASE
      WHEN total_price >= 20000 THEN 'high'
      WHEN total_price >= 5000  THEN 'mid'
      ELSE                           'low'
    END
  );

SELECT order_id, total_price, price_tier
FROM orders_raw
ORDER BY total_price DESC;

You're good if orders are classified as high, mid, or low based on the total_price value.

5. Identifying Virtual Columns with DESC TABLE / SHOW COLUMNS

Let's check the table definition. Using DESC TABLE, you can identify whether a column is a regular column or a virtual column via the KIND column.

DESC TABLE orders_raw;

Regular columns show COLUMN in the KIND column, while virtual columns show VIRTUAL. You're good if the EXPRESSION column contains the expression you defined.

You can also check the same information with SHOW COLUMNS. Here, the kind column shows VIRTUAL_COLUMN and the expression column contains the expression.

SHOW COLUMNS IN TABLE orders_raw;

6. Creating a Table with Virtual Columns Using CTAS

You can also create a table with virtual columns using CREATE TABLE AS SELECT (CTAS). Since virtual columns are not counted in the number of columns in the SELECT clause, you only need to specify the non-virtual columns in the SELECT.

Let's create an aggregated table with a category name and tax-included price (virtual column) by JOINing a product category master with orders_raw.

First, create the product category master table.

CREATE OR REPLACE TABLE product_categories (
  product_name STRING,
  category     STRING
);

INSERT INTO product_categories VALUES
  ('Wireless Mouse',        'Pointing Device'),
  ('Mechanical Keyboard',   'Input Device'),
  ('USB Hub',               'Peripheral'),
  ('Laptop Stand',          'Accessory'),
  ('HDMI Adapter',          'Converter'),
  ('Webcam',                'Video Equipment'),
  ('Portable SSD',          'Storage'),
  ('Keyboard Wrist Rest',   'Accessory'),
  ('LED Desk Lamp',         'Lighting');

Next, create the table with the virtual column tax_included_price (tax-inclusive price) using CTAS.

Here is the continuation from Section 6:

CREATE OR REPLACE TABLE orders_with_category (
  order_id           INT,
  customer_name      STRING,
  product_name       STRING,
  category           STRING,
  total_price        NUMBER(12, 2),
  tax_included_price NUMBER(12, 2) AS ((total_price * 1.1)::NUMBER(12, 2))
)
AS SELECT o.order_id, o.customer_name, o.product_name, c.category, o.total_price
   FROM orders_raw o
   LEFT JOIN product_categories c ON o.product_name = c.product_name;

SELECT * FROM orders_with_category ORDER BY order_id LIMIT 5;

You're good if tax_included_price shows the tax-inclusive amount of total_price × 1.1. Virtual columns can be defined without any issues in CTAS as well.

7. Testing Disallowed Expressions to Confirm Errors

Let's actually try using expressions that are not allowed in virtual columns and check the resulting error messages.

Non-deterministic Function (RANDOM)

ALTER TABLE orders_raw
  ADD COLUMN random_val INT AS (RANDOM());

NOT NULL Constraint

ALTER TABLE orders_raw
  ADD COLUMN total_price2 NUMBER(12, 2) AS ((quantity * unit_price)::NUMBER(12, 2)) NOT NULL;

DROP COLUMN on a Dependent Column

Attempting to DROP COLUMN on a column that a virtual column depends on will be blocked.

ALTER TABLE orders_raw DROP COLUMN quantity;

Closing Thoughts

I tried out Snowflake's Virtual Columns across several different patterns.

Being able to consolidate computed columns — which previously required a "table + View" pair — directly into the table itself, reducing the number of objects to manage, is a significant benefit. Since no additional storage costs are incurred, I think this is a feature worth adopting proactively.

On the other hand, there are quite a few limitations to be aware of: the types of expressions that cannot be used (non-deterministic functions, aggregations, UDFs, etc.), the lack of support for NOT NULL / CHECK constraints, and the known issue with COALESCE × LEFT JOIN, so some caution is needed.

Since this feature has just reached GA, I'm looking forward to further improvements down the road. Give it a try!

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