How can I get number of columns in Staged File Snowflake

Question

I am staging a file in Snowflake and loading in table using Copy. Before loading I want to check number of columns in file to make sure it matches with table.

How can I get column count for this staged file?

Answer 1

I do not think there is a way to do that. But if it is a CSV file that you have staged and want see the data before loading to the table, you can use a SQL as below

SELECT $1,$2,$3, ...$n from @<your stage name> - n is the number of columns in the CSV file

Answer 2

I don't think there is way to count number of column from staged files, If you are looking to validate input file(s) before loading into actual table you can use "VALIDATION_MODE" in copy command, It will validate and return error if any.

[ VALIDATION_MODE = RETURN__ROWS | RETURN_ERRORS | RETURN_ALL_ERRORS ]

Answer 3

There is workaround, you can first load header in different file format split it to get array and at last find out array size to get total size.

cat test.csv
a|b|c
1|2|3

Create name stage with other than pipeline delimted to bring all the data in one single column.

create or replace stage stg_col_count file_format = ( type = 'csv');

select $1 Col from @stg_col_count; 

+-------+                                                                       
| COL   |
|-------|
| a|b|c |
+-------+

Split it to get in array and then take size. You can only take header

select array_size(split($1,'|')) no_of_col from @stg_col_count limit 1;
+-----------+                                                                   
| NO_OF_COL |
|-----------|
|         3 |
+-----------+