Virtual Column Support

[sindhuthirugnanam]

Is it possible to include support for Virtual Column Expression? For example, if I want to construct a display name using first and last name as below:

columns:
  first_name: VARCHAR(45) NOT NULL
  last_name: VARCHAR(45) NOT NULL
  display_name: VARCHAR(91) NOT NULL (first_name || ' ' | |last_name)

[littleK0i]

Interesting. I've never heard about this feature before.

I see two problems here:

1. Virtual columns are not documented for a number of years.
2. DESC TABLE command returns expression value in column expression, but, unlike default, it is heavily normalized.

For example, original expression from this table:

CREATE OR REPLACE TABLE tmp.public.Virtual_Column_Example (
    colA INT
    , colB INT
    , derived INT AS (IFF(colA='A' or colB=1,1,0))
);

is returned as: IFF(((CAST(COLA AS NUMBER(38,5))) = (TO_NUMBER('A', 38, 5))) OR (COLB = 1), 1, 0).

It is a big problem for declarative-style tools, since we cannot tell if original expression matches normalized expression. It will lead to poor usability, unless there is a way to normalize expression on the fly before comparison.

[minghungcho]

Since column expression is evaluated on the fly, even if SnowDDL needs to DROP COLUMN and ADD COLUMN when the expression doesn't match, I don't think it would create any data loss. Assuming --apply-unsafe is used.

[littleK0i]

This approach creates a few other problems:

1. ADD COLUMN can only add a column at the end of list. There is no way to ADD COLUMN in the middle without full table rewrite.
2. DROP COLUMN / ADD COLUMN commands cannot be executed in one transaction. There is chance for race condition, when user tries to run a query exactly between these two statements and get error.
3. SnowDDL will report table as "changed" on every run and produce some extra lines if --show-sql option is enabled. It's not an issue if you have one such table, but if you have many? For example, 100+.

[littleK0i]

@sindhuthirugnanam , @minghungcho , please check latest version 0.9.0.

I've added a basic support for table column expression. Expression should be fully resolved and normalized, with upper-cased identifiers and spaces as per Snowflake internal expression formatter.

I will not document it yet and talk to Snowflake reps and see if it can be improved. But in the meantime you may start using this feature. Just be very careful with formatting and make sure to test configs with expressions repeatedly before merging.

Also, changing the expression will trigger full table re-write, similar to many other changes, like default. This is a fundamental limitation of Snowflake. If you'll find an undocumented way to change column expression with ALTER, please let me know.

[littleK0i]

Example:

columns:
  id:
    type: NUMBER(38,0)
  first_name:
    type: VARCHAR(255)
  last_name:
    type: VARCHAR(255)
  full_name:
    type: VARCHAR(520)
    expression: "FIRST_NAME || ' ' || LAST_NAME"
    comment: "Virtual column"