loading CSV data into Snowflake schema

Question

I am fairly new to Snowflake and am trying to export data from CSV files into a snowflake schema, but I am a bit lost. There’s 5 CSV files in a folder, and I have to use an internal tool to export the data from all 5 files into a table in a Snowflake schema. I have the reading files portion done, but it’s the loading part I’m stuck at.

One, how do I know which data is from which file if I just load the data from all the files into one table? Two, the structure of the files is different than the structure of the schema.

The Snowflake table structure Has the following columns : City File_Name Year Value

The files have the following structure: 1999 Albuquerque 6.5

The first column has no header. The years are the headers starting from the second header. My plan so far is to load the data into a staging table, Alias the first column as ‘City’ and unpivot the year/value data.

I would appreciate any help on how to accomplish this or alternative ideas!

Answer 1

Have a read of the COPY INTO documentation

1. how do I know which data is from which file?

When you copy files into a table there are metadata columns available that you can include in the SELECT statement e.g. METADATA$FILENAME.

2. Data Loading

In your files how are you delimiting the fields in each record? In your example you appear to have spaces between them but that obviously wouldn't work if, for example, the city name was "New York".

Assuming that you have valid csv files, you would just run something like:

COPY INTO table_name (City, File_Name, Year, Value)
SELECT $2, METADATA$FILENAME, $1, $3
FROM stage_name
FILES = (or PATTERN = ) -- list the files (or a pattern for the files) that you want to load from the stage
FILE_FORMAT = xyz