How to load CSV data from the local to Snowflake?

In today's data-driven world, efficient data management and transfer are critical for business success. Snowflake, a powerful cloud-based data warehousing solution, allows organizations to store and analyze vast amounts of data seamlessly. In this article, we will cover everything from the basics to step-by-step instructions to load CSV to Snowflake, along with examples and expected outputs.

What is SnowFlake?

Snowflake has revolutionized data warehousing with its cloud-native architecture, offering scalability, concurrency, and performance that traditional solutions struggle to match.

Snowflake is a cloud-based data platform that supports data warehousing, data lakes, data engineering, and data science. It operates on a pay-as-you-go model, providing flexibility and cost-efficiency.

Stages in Snowflake

Stages in Snowflake are locations where data files are stored for loading into tables. There are three types:

1. User Stages: Each user has a personal stage.
2. Table Stages: Each table has a stage associated with it.
3. Internal and External Named Stages: Named stages can be internal (within Snowflake) or external (e.g., S3, Azure Blob Storage).

SnowSQL

SnowSQL is a command-line client for connecting to Snowflake and executing SQL queries. It's often used for data loading, unloading, and other administrative tasks. Loading data into Snowflake is a common task, especially for those who need to transfer CSV files from their local machines for analysis and reporting.

What is a CSV File?

CSV (Comma Separated Values) is a simple file format used to store tabular data, such as a spreadsheet or database. A CSV file stores tabular data (numbers and text) in plain text. Each line of the file is a data record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator is the source of the name for this file format.

Steps to Load CSV Data from Local to Snowflake

Before loading the CSV data from local to the Snowflake, make sure that you have access to a Snowflake account and SnowSQL already installed on your local machine.

Install SnowSQL

Download and install SnowSQL from the Snowflake Downloads page.

Configure SnowSQL

Configure SnowSQL by creating or updating the 'config' file with your Snowflake account details.

[connections.my_account]
accountname = <your_account_name>
username = <your_username>
password = <your_password>
region = <your_region>

Create a Snowflake Table

Log into Snowflake and create a table to hold your CSV data. Use SnowSQL or the Snowflake web interface to create a table that matches the structure of your CSV file.

CREATE TABLE table_name (
    column1 STRING,
    column2 STRING,
    column3 STRING
    -- Add more columns as needed
);

Upload the CSV File to a Stage

Use SnowSQL to upload your CSV file to a stage. For this example, we'll use a user stage.

snowsql -c my_account -q "PUT file:///path/to/your/file.csv @~/staging_area/"

Copy Data from Stage to Table

Copy the data from the stage to your Snowflake table.

COPY INTO table_name
FROM @~/staging_area/file.csv
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"' SKIP_HEADER = 1);

Complete Code Example

Let's walk through a complete code example for a better understanding.

employees.csv

Name,Position,Department
John Doe,Software Engineer,Engineering
Jane Smith,Data Scientist,Data Science
Alice Johnson,Product Manager,Product

SnowSQL Commands

Upload the CSV file to the stage:

snowsql -c my_account -q "PUT file:///path/to/employees.csv @~/staging_area/"

Create Table

Create the table in Snowflake using SnowSQL and add the column along with their datatypes that you need in the table.

snowsql -q "
CREATE TABLE employees (
    name STRING,
    position STRING,
    department STRING
);"

Copy data into the table

In this example, we have created a table named 'employee' in which we are copying the data from the CSV file.

COPY INTO employees
FROM @~/staging_area/employees.csv
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"' SKIP_HEADER = 1);

Output

Once the data is loaded successfully, you can verify it by querying the table.

SELECT * FROM employees;

Conclusion

Loading CSV data from your local machine to Snowflake is a straightforward process that involves creating a table, uploading the CSV file to a stage, and then copying the data into the table. With Snowflake's powerful and flexible architecture, managing your data becomes efficient and scalable. Whether you're just starting or looking to optimize your data workflows, this guide provides a comprehensive overview to get you started.