4
0
During this post we will discuss a simple but interesting scenario with Virtual Column in Snowflake. Recently as per ask by business we were supposed to load a file into SF. Respective base table related to the file was already created in the Snowflake Database. Source team has placed the file on AWS Cloud in S3 bucket. We as data load team needs to load this file into Snowflake Table. At first sight the requirements seems pretty straightforward and thought of writing a simple COPY command to load data. So to carry forward our understanding we have developed the below simple COPY command and ran.
copy into CUSTOMER
from @demo_db.public.ext_csv_stage/CUSTOMER_FILE.csv
on_error = CONTINUE;
To our surprise the above command has reported the below error:
SQL compilation error: Insert value list does not match column list expecting 6 but got 7
Initially we thought that File format has been changed by the source team. To cross check the format we have opened the file and verify there were 7 columns in file. (Of course in original file 20+ cols there).
We also query the Snowflake table and confirm the number of columns in table.
Very Surprising, when File and Table columns are in synch then why COPY is failing. I had a very hard time to understand the root cause of issue.
Root cause Analysis:
Recently we worked on SIEBEL implementation project and there we created few Virtual columns related to Asset and Accessories entities. As per the definition by Oracle:
A virtual column is similar to a normal table column but it is defined by an expression. The result of evaluation of this expression becomes the value of the column.
So I tried to relate that it can be the possibility that table present in snowflake may be the migrated table from Oracle. And if yes, then we should check the definition of table in snowflake. So I executed the below query and it has confirmed my understanding.
So now I could relate easily that in Snowflake table, physical columns are 6 while in File we are having 7 columns.
Now the question comes why the file has 7 columns:
Because Source team has extracted the table without any filteration on Virtual columns. Hence the full table got extracted and 7 columns exported into file.
After so much analysis we got the solution and executed the COPY command like below:
