Snowflake - Column default values not effective with COPY command?

Question

I'm using Matillion to load data into Snowflake, both on Azure. When I create tables I specify default values for the columns, I don't like having NULLs in the warehouse.

From what I've read, the Database Query orchestration component in Matillion for Snowflake will put the retrieved data set into an Azure blob and use the Snowflake COPY command to move the data from the blob to the target table.

The result is that NULL values are still there in the target table.

Can someone confirm that the COPY command does some kind of bulk data copy and that the default values are effective only with INSERT statements?

If so I'll just use trap the NULL values at the source.

Thanks.

JFS.

Answer 1

It isn't mentioned in a straight-forward way but the COPY INTO … TABLE statement documentation does specify that it will use the default only for skipped column names (and not in other scenarios):

( col_name [ , col_name ... ] )

[ … ]

Any columns excluded from this column list are populated by their default value

Additionally, the NULL-use behaviour is mentioned for another scenario where data may be missing, making no note of the use of defaults:

ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE | FALSE

[ … ]

If the input file contains records with fewer fields than columns in the table, the non-matching columns in the table are loaded with NULL values.