Step 1: Create a stage

In Snowflake, a stage is a point where data is loaded from a file. There are three different kinds of stages:

User stages: These are specific to each user and can be used to load data from a local file system or from a cloud storage provider such as Amazon S3, Microsoft Azure, or Google Cloud Storage.

Account stages: These can be used to load data from a cloud storage provider and are shared by all users in an account.

Internal stages: These are run by Snowflake and can be used to load data from remote systems, like a public HTTP(S) endpoint or an Amazon S3 bucket in a different account.

Here is an illustration of how to upload a CSV file to Snowflake:

                    
                      
                      — build a stage
                    CREATE STAGE my_stage
                      URL = 's3://my-bucket/my-folder/'
                      CREDENTIALS = (AWS_KEY_ID='' 
                                      AWS_SECRET_KEY='');  
                    
                  

Step 2: Provide the right permissions

You must give the person or role who will be importing the data into Snowflake the right permissions. This includes the stage's USAGE authorization as well as the FILE FORMAT object's description of the file's format. You can grant permissions to a user or role in Snowflake using the GRANT command.

                    
                      — give the user permissions
                      GRANT USAGE ON STAGE my_stage TO my_user;	
                      GRANT USAGE ON FILE FORMAT my_file_format TO my_user; 
                    
                  

Step 3: Make a file format

A file format specifies the formatting of the data in the file that is being loaded. The CREATE FILE FORMAT command in Snowflake can be used to create a file format.

                    
                      — develop a file format 
                      CREATE FILE FORMAT my_file_format
                        TYPE = 'CSV'
                        FIELD_DELIMITER = ','
                        RECORD_DELIMITER = '\n'
                        SKIP_HEADER = 1; 
                    
                  

Step 4: Copy file to a table

The COPY INTO command can be used to load the data from the flat file into Snowflake once you have built a stage, given the necessary permissions, and created a file format. The file location, destination table, and file format are all specified by the COPY INTO command.

                    
                      — load the data  
                      COPY INTO my_table
                        FROM @my_stage/my_file.csv
                        FILE_FORMAT = my_file_format
                        ON_ERROR = 'ABORT_STATEMENT'; 
                    
                  

This command loads the data from the file my_file.csv in the my_stage stage into the table my_table, using the my_file_format file format. If any errors occur during the load operation, the command will abort the entire statement.