DEV Community

Sagara
Sagara

Posted on

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

#snowflake #dataengineering

📝 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> );
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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)
);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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

2026-06-06_23h13_55

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));
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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.

2026-06-06_23h18_00

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));
Enter fullscreen mode Exit fullscreen mode 
SELECT order_id, total_price, tax_amount
FROM orders_raw
ORDER BY order_id
LIMIT 5;
Enter fullscreen mode Exit fullscreen mode

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.

2026-06-06_23h19_03

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));
Enter fullscreen mode Exit fullscreen mode 
SELECT customer_name, email, email_domain
FROM orders_raw
ORDER BY order_id
LIMIT 5;
Enter fullscreen mode Exit fullscreen mode

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.

2026-06-06_23h19_50

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
  );
Enter fullscreen mode Exit fullscreen mode 
SELECT order_id, total_price, price_tier
FROM orders_raw
ORDER BY total_price DESC;
Enter fullscreen mode Exit fullscreen mode

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

2026-06-06_23h20_34

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;
Enter fullscreen mode Exit fullscreen mode

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.

2026-06-06_23h21_54

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;
Enter fullscreen mode Exit fullscreen mode

2026-06-06_23h24_12

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');
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode 
SELECT * FROM orders_with_category ORDER BY order_id LIMIT 5;
Enter fullscreen mode Exit fullscreen mode

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.

2026-06-06_23h26_06

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());
Enter fullscreen mode Exit fullscreen mode

2026-06-06_23h27_16

NOT NULL Constraint 

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

2026-06-06_23h27_35

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;
Enter fullscreen mode Exit fullscreen mode

2026-06-06_23h28_04

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!

Top comments (0)