Behavior of DEFAULT option in Snowflake Tables

Question

I've created a table and added default values for some columns.
E.g.

Create table Table1( COL1 NUMBER(38,0),
                     COL2 STRING,                    
                     MODIFIED_DT STRING DEFAULT CURRENT_DATE(),
                     IS_USER_MODIFIED BOOLEAN DEFAULT 'FALSE' )

Current Behavior:
During data load, I see that when running inserts, my column 'MODIFIED_DT' is getting inserted with default values. However, if there are any subsequent updates, the default value is not getting updated.

Expected Behavior:

1. My requirement is that the column value should be automatically taken care by ANY INSERT/UPDATE operation. E.g. In SQL Server, if I add a Default, the column value will always be inserted/updated with the default values whenever a DML operation takes place on the record Is there a way to make it work? or does default value apply only to Inserts?

2. Is there a way to add logic to the DEFAULT values. E.g. In the above table's example, for the column IS_USER_MODIFIED, can I do:

   Case when CURRENT_USER() = 'Admin_Login' then 'FALSE' Else 'TRUE' end

   If not, is there another option in snowflake to implement such functionality?

Answer 1

the following is generic to most (all?) databases and is not specific to Snowflake...

Default values on columns in table definitions only get inserted when there is no explicit reference to that column in an INSERT statement. So if I have a table with 2 columns (column_a and column_b and with a default value for column_b) and I execute this type of INSERT:

INSERT INTO [dbo].[doc_exz]
           ([column_a])
     VALUES
           (3),
           (2);

column_b will be set to the default value. However, with this INSERT statement:

INSERT INTO [dbo].[doc_exz]
           ([column_a]
           ,[column_b])
     VALUES
           (5,1),
           (6,NULL);

column_b will have values of 1 and null. Because I have explicitly referenced column_b the value I use, even if it is NULL, will be written to the record even though that column definition has a default value.

Default values only work with INSERT statements not UPDATE statements (as an existing record must have a "value" in the column, even if it is a NULL value, so when you UPDATE it the default doesn't apply) - so I don't believe your statement about defaults working with updates on SQL Server is correct; I've just tried it, just to be sure, and it doesn't.

Snowflake-specific Answer

Given that column defaults only work with INSERT statements, they are not going to be a solution to your problem. The only straightforward solution I can think of is to explicitly include these columns in your INSERT/UPDATE statements.

You could write a stored procedure to do the INSERT/UPDATES, and automatically populate these columns, but that would perform poorly for bulk changes and probably wouldn't be simple to use as you'd need to pass in the table name, the list of columns and the list of values.

Obviously, if you are inserting/updating these records using an external tool you'd put this logic in the tool rather than trying to implement it in Snowflake.

Answer 2

Snowflake has a "DERIVED COLUMN" feature. These columns are VIRTUAL/COMPUTED and are not used in ETL process. However, any DML activity will automatically influence the column values.

Nice thing is, we can even write CASE logic in the column definition. This solved my problem.

CREATE OR REPLACE TABLE DB_NAME.DBO.TEST_TABLE
    (
            FILE_ID NUMBER(38,0), 
            MANUAL_OVERRIDE_FLG  INT           as (case when current_user() = 'some_admin_login' then 0 else 1 end), 
            RECORD_MODIFIED_DT   DATE          as (CURRENT_DATE()),
            RECORD_MODIFIED_BY   STRING        as (current_user()) 
    );