Export Snowflake Table Data to Local CSV format

Post author:Vithal S
Post last modified:September 30, 2022
Post category:Snowflake
Reading time:5 mins read

The exporting tables to local system is one of the common requirements. You may need to export Snowflake table to analyze the data or transport it to a different team. You can export the Snowflake schema in different ways, you can use COPY command, or Snowsql command options. In this articles, we will check how to export Snowflake table data to a local CSV format.

Using the COPY command may be the fastest method. But, it will unload tables to S3 location. You need to have an AWS subscription to use this method.

Export Snowflake Table Data to Local CSV format

This method is one of the easiest methods that you can use. It just requires you to install and set up snowsql command line option.

Snowsql supports many command line options that you can use to format your table output. You can redirect the output to the local CSV file.

Following is the Snowsql command that you can use.

snowsql -c my_example_connection -d sales_db -s public -q "select * from mytable limit 10" -o output_format=csv -o header=false -o timing=false -o friendly=false  > output_file.csv

For a security reason, it is always better to use connection file. Using a connection file, you can communicate to Snowflake without needing to enter password. However, you should protect your Snowsql config file.

You can also set SQL environment variables to avoid entering a password every time you try to export tables:

Snowsql example to Export Snowflake Table to Local CSV

You just have to execute snowsql command with your SQL query and connection file.

For example, consider below snowsql example to export tables to local CSV format. For a simplicity we have used table which has very little data.

D:\Snowflake\export>snowsql -c myconnection -q "select * from E_DEPT" -o output_format=csv -o header=false -o timing=false -o friendly=false  -o output_file=D:\Snowflake\export\dept_file.csv

D:\Snowflake\export>snowsql -c myconnection -q "select * from E_DEPT" -o output_format=csv -o header=false -o timing=false -o friendly=false  > dept_file.csv

Note that, connection file contains all the required details such as username, password, database, and schema.

Based on the query complexity and table size, Snowsql may take some time to export query results.

Now, check the content of the dept_file.csv file.

D:\Snowflake\export>more dept_file.csv
"1","Engineering"
"2","Support"

As you can see, by default, Snowsql export query result in the double quoted format.