How to Export Data from Snowflake to CSV

Published: May 30, 2024 - 6 min read

Hannah Recker
how to export data from snowflake to csv

Exporting data from Snowflake to CSV is a common task for data professionals who need to work with Snowflake data in spreadsheets or other tools.

In this step-by-step guide, we’ll walk you through various methods to download data from Snowflake to CSV. We’ll also cover best practices and potential challenges to keep in mind when exporting large datasets.

Let’s dive in!

Method 1. Exporting Snowflake Data to Spreadsheets

The best method for exporting data from Snowflake to CSV is to skip the manual processes altogether and use a tool like Coefficient. Coefficient offers secure, self-service access to live Snowflake data directly from your spreadsheets.

Step 1. Install Coefficeint

Before getting started, install Coefficient for your spreadsheet. It only takes a few seconds, and you only have to do it once.

Step 2: Connect to Snowflake

Open the Coefficient Sidebar. Click on the menu icon and select “Connected Sources.”

set up a snowflake connected source for google sheets or excel

Search for Snowflake and click “Connect”.

connect snowflake to google sheets or excel

Authenticate your Snowflake connection by providing your account details.

Coefficient offers three options to import your data from Snowflake: Tables and Columns, Custom SQL Query, and GPT SQL Builder.

Step 3: Import from Tables & Columns

Select ‘From Tables and Columns’ to proceed.

export data from snowflake to csv

Step 4. Preview and Select Data

The Import Preview window will display the schemas of the tables available. Select the table you wish to import and choose the necessary columns

select snowflake data to export to csv

Step 4. Export Data from Snowflake

Customize your import by adding filters, and row limits as needed. When done click “Import”.
snowflake filters limits and sorts for csv file

Step 5. Export to CSV

Once your data is in the spreadsheet, export it from your spreadsheet just as you normally would.

data export from snowflake into google sheets

Step 6. Set Up Auto-Refresh

Keep your data up to date, set up an auto-refresh schedule in Coefficient. Choose an update frequency (hourly, daily, or weekly) and specify a time for the data to refresh automatically.

auto refresh snowflake data in spreadsheet

Method 2. Exporting Data Using SQL Commands

Exporting data directly using SQL commands in Snowflake is straightforward once you get the hang of it. This method can be broken down into two main approaches.

The COPY INTO Command

The COPY INTO command is a handy tool for exporting large datasets from Snowflake to CSV files. Here’s a step-by-step guide:

  • Create a Staging Area: You need to create a staging area where the CSV file will be temporarily stored.

CREATE STAGE my_stage

  •   FILE_FORMAT = (TYPE = ‘CSV’ FIELD_DELIMITER = ‘,’ SKIP_HEADER = 1);

  • Export Data: Use the COPY INTO command to export data from a table or a query result into the staging area.

COPY INTO @my_stage/output/

FROM (SELECT * FROM mytable WHERE condition)

FILE_FORMAT = (TYPE = ‘CSV’ FIELD_DELIMITER = ‘,’ SKIP_HEADER = 1)

  • HEADER = TRUE;

  • Download the CSV File: Finally, use the GET command to download the exported CSV file(s) from the staging area to your local machine.
  • GET @my_stage/output/ file://C:pathtodestination ;

Exporting Directly to a Local File

If you prefer to skip the staging area, you can export data directly to a local file using the COPY INTO command. Note that this requires ACCOUNTADMIN privileges.

COPY INTO ‘file://C:pathtooutput.csv’

Coefficient Excel Google Sheets Connectors
Try the Free Spreadsheet Extension Over 425,000 Pros Are Raving About

Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule.

Get Started

FROM mytable

FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ‘,’ SKIP_HEADER = 1)

HEADER = TRUE;

Method 3. Exporting Data via the Snowflake Web UI

For smaller datasets or quick one-off exports, you can use the Snowflake Web UI. Here’s how:

  • Run a SELECT Query: Execute a SELECT query to retrieve the data you want to export.
  • Download the Data: In the query results pane, click the “Download” button, choose “CSV” as the file format, and click “Download” to save the file to your local machine.

Keep in mind that the Web UI is useful for small datasets and quick tasks but may not be suitable for large datasets or automated workflows.

Method 4. Exporting Data with SnowSQL

SnowSQL, Snowflake’s command-line client, is another way to export data. Here’s a clear guide:

Setting Up SnowSQL for CSV Export

  • Configure SnowSQL: Open the SnowSQL configuration file located at ~/.snowsql/config in a text editor.
  • Set Output Format: Change the output_format parameter to csv to specify the CSV format.
  • Additional Settings: Optionally, customize other settings like field delimiter and whether to include headers.

Executing Export Commands

  • Using COPY INTO Command: Use the COPY INTO command with the file:// prefix to export data to a local file.

COPY INTO ‘file://C:pathtooutput.csv’

FROM mytable

FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ‘,’ SKIP_HEADER = 1)

  • HEADER = TRUE;

  • Monitor Export Progress: Run the command and monitor the export process using SnowSQL’s built-in logging features.
  • Verify Exported File: Check the exported file’s size and content to ensure it has been exported correctly.

Best Practices for Handling Data Exports from Snowflake

Optimizing for Large Data Sets

When dealing with large volumes of data, consider the following techniques to minimize export time and resource usage:

  • Partition your data and export in smaller chunks to avoid memory constraints.
  • Use columnar storage formats like Parquet or ORC for faster query processing and reduced storage costs.
  • Leverage Snowflake’s clustering keys to improve query performance on large tables.

Chunked Data Export

For extremely large datasets, exporting data in chunks can help manage the process more efficiently:

  1. Divide your data into smaller, manageable chunks based on a suitable criteria (e.g., date range, primary key).
  2. Export each chunk separately and save them as individual CSV files.
  3. Merge the individual files into a single consolidated CSV file if required.

Common Challenges and Troubleshooting Tips

Handling Common Errors

During the data export process, you may encounter various challenges. Here are some common pitfalls and how to avoid them:

  • Data type mismatches: Ensure that the data types in your SELECT query match the expected format in the CSV file. Use appropriate data type conversion functions if necessary.
  • Encoding issues: Specify the correct character encoding (e.g., UTF-8) in your export command to avoid garbled or missing characters in the CSV file.
  • Network connectivity problems: Check your network connection and firewall settings to ensure a stable connection between your local machine and the Snowflake server.

Ensuring Data Accuracy

To maintain the accuracy of your exported data, consider the following tips:

  • Perform data validation checks on the exported CSV file to identify any discrepancies or anomalies.
  • Verify that the number of records in the exported file matches the count returned by your SELECT query.
  • Double-check the formatting of the CSV file, including field delimiters and line endings, to ensure compatibility with your target system.

From Snowflake to Spreadsheets: Klaviyo’s Success Story

Klaviyo, a leading marketing automation platform, faced challenges with a growing number of data requests from across the organization. By adopting Coefficient, they were able to:

  • Enable self-serve analytics, saving the data team months of manual work
  • Centralize reporting with automated data refreshes from Snowflake
  • Expand Coefficient usage to multiple teams for use cases like product analytics and financial planning

Read the full case study to learn how Klaviyo unlocked the power of their Snowflake data with Coefficient.

Don’t Export Snowflake Data to CSV. Use Coefficient.

Exporting data from Snowflake to CSV is common for many organizations, but it comes with the risk of errors and outdated data. Instead of dealing with these issues, you can use Coefficient to get live data directly into your spreadsheet.

Skip the CSVs. Try Coefficient today to keep your data live and error-free in your spreadsheet.

Sync Live Data into Your Spreadsheet

Connect Google Sheets or Excel to your business systems, import your data, and set it on a refresh schedule.

Try the Spreadsheet Automation Tool Over 500,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 350,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Hannah Recker Growth Marketer
Hannah Recker was a data-driven growth marketer before partying in the data became a thing. In her 12 years experience, she's become fascinated with the way data enablement amongst teams can truly make or break a business. This fascination drove her to taking a deep dive into the data industry over the past 4 years in her work at StreamSets and Coefficient.
500,000+ happy users
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 50,000 Companies