Identify if a column is Virtual in Snowflake

Question

Snowflake does not document its Virtual Column capability that uses the AS clause. I am doing a migration and needing to filter out virtual columns programatically.

Is there any way to identify that a column is virtual? The Information Schema.COLLUMNS view shows nothing different between a virtual and non-virtual column definition.

Answer 1

There is a difference between column defined as DEFAULT and VIRTUAL COLUMN(aka computed, generated column):

Virtual column

CREATE OR REPLACE TABLE T1(i INT, calc INT AS (i*i));
INSERT INTO T1(i) VALUES (2),(3),(4);
SELECT * FROM T1;

When using AS (expression) syntax the expression is not visible inCOLUMN_DEFAULT:

---

DEFAULT Expression

In case of the defintion DEFAULT (expression):

CREATE OR REPLACE TABLE T2(i INT, calc INT DEFAULT (i*i));
INSERT INTO T2(i) VALUES (2),(3),(4);
SELECT * FROM T2;

It is visible in COLUMN_DEFAULT:

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'T2';

---

Comparing side-by-side with SHOW COLUMNS:

SHOW COLUMNS LIKE 'CALC';
-- kind: VIRTUAL_COLUMN

One notable difference between them is that virtual column cannot be updated:

UPDATE T1
SET calc  = 1;
-- Virtual column 'CALC' is invalid target.

UPDATE T2
SET calc = 1;
-- success

Answer 2

How about using SHOW COLUMNS ? you should identify them when expression field is not null.

create table foo (id bigint, derived bigint as (id * 10));
insert into foo (id) values (1), (2), (3);

SHOW COLUMNS IN TABLE foo;
SELECT "table_name", "column_name", "expression" FROM table(result_scan(last_query_id()));

| table_name | column_name | expression     |
| ---------- | ----------- | -------------- |
| FOO        | ID          | null           |
| FOO        | DERIVED     | ID*10          |