How to Load a CSV File into a Snowflake Table using a Stage

If you’re new to Snowflake, one of the first things you’ll want to learn is how to load your data. In this blog, I’ll walk you through importing a CSV file containing Olympic athlete data into Snowflake using a schema, stage, file format, and table. By the end, you’ll understand not just the commands, but the reasoning behind each step.

The complied query is at the end of this blog.

This is the Olympic athlete data used through the blog.

Step 1: Create a Schema

CREATE SCHEMA r_beginner_demo;

A schema is like a folder in your database. It helps organize your tables, stages, and other objects. Here we create a schema called 'r_beginner_demo' to keep all objects related to this tutorial together.

Step 2: Set Your Working Schema

USE database_name.r_beginner_demo;
This tells Snowflake that any objects we create (tables, stages, etc.) after this point, should be placed inside the schema 'r_beginner_demo'.

Step 3: Create a File Format

CREATE OR REPLACE FILE FORMAT file_format_csv
TYPE = 'CSV'
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '"';

A file format defines how Snowflake should interpret the CSV file when loading it.

• TYPE = 'CSV': indicates the file type
• SKIP_HEADER = 1: ignores the first line of the file, which usually contains column headers
• FIELD_OPTIONALLY_ENCLOSED_BY = '"': handles fields that are wrapped in quotes

Step 4: Create a Stage

CREATE OR REPLACE STAGE stage_olympics_data
FILE_FORMAT = file_format_csv;

A stage is a temporary storage area in Snowflake where you upload files before loading them into a table. We are creating an internal stage called 'stage_olympics_data' and associating it with the file format we just defined.

Step 5: Upload Your CSV File to the Stage (Manual)

Since the stage is empty after creation, you need to upload the CSV manually:

• Go to the Ingestion tab
• Select 'Load Files into a Stage' and select your file.
• Select your Database, Schema, and Stage.
• Click Upload.

Optional: Verify your file is in the stage: LIST @stage_olympics_data;

You should see your CSV listed. Now the file is ready to be loaded into your table.

Step 6: Create a Table

CREATE OR REPLACE TABLE table_olympics_data (
athlete_id BIGINT,
athlete_name VARCHAR(1024),
gender VARCHAR(1024),
age VARCHAR(1024),
height_cm VARCHAR(1024),
weight_kg VARCHAR(1024),
team_name VARCHAR(1024),
noc_code VARCHAR(1024),
games VARCHAR(1024),
year BIGINT,
season VARCHAR(1024),
host_city VARCHAR(1024),
sport VARCHAR(1024),
event VARCHAR(1024),
medal VARCHAR(1024),
noc_region VARCHAR(1024),
noc_notes VARCHAR(1024)
);

The table is where the data from the CSV file will ultimately reside. We define the columns in the table to match the data in the CSV. For beginners, using VARCHAR for most fields is fine. You can adjust data types later for accuracy.

Step 7: Load Data into the Table

COPY INTO table_olympics_data
FROM @stage_olympics_data
FILE_FORMAT = (FORMAT_NAME = file_format_csv);

• COPY INTO reads files from the stage and inserts the data into the table.
• FROM @stage_olympics_data: tells Snowflake where to find the CSV file
• FILE_FORMAT: specifies how the CSV should be interpreted

Step 8: Verify the Load

SELECT *
FROM table_olympics_data
LIMIT 100;

This query selects the first 100 rows from the table so you can check that the data loaded correctly and the columns match your expectations.

Summary

Let’s review what we’ve done:

1. Created a schema – a container to keep objects organized.
2. Set the active schema – told Snowflake where to work.
3. Created a file format – defined how the CSV should be interpreted.
4. Created a stage – a temporary storage location for the CSV.
5. Created a table – the permanent home for the data.
6. Loaded data – moved CSV data into the table.
7. Verified – checked that the data looks correct.

Complied Query

CREATE SCHEMA r_beginner_demo;

USE databse_name.r_beginner_demo;

CREATE OR REPLACE FILE FORMAT file_format_csv
TYPE = 'CSV'
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '"';

CREATE OR REPLACE STAGE stage_olympics_data
FILE_FORMAT = file_format_csv;

!Refer to Step 5 at this point!

CREATE OR REPLACE TABLE table_olympics_data (
athlete_id BIGINT,
athlete_name VARCHAR(1024),
gender VARCHAR(10),
age VARCHAR(10),
height_cm VARCHAR(10),
weight_kg VARCHAR(10),
team_name VARCHAR(1024),
noc_code VARCHAR(10),
games VARCHAR(1024),
year BIGINT,
season VARCHAR(20),
host_city VARCHAR(1024),
sport VARCHAR(1024),
event VARCHAR(1024),
medal VARCHAR(1024),
noc_region VARCHAR(1024),
noc_notes VARCHAR(1024)
);

COPY INTO table_olympics_data
FROM @stage_olympics_data
FILE_FORMAT = (FORMAT_NAME = file_format_csv);

SELECT *
FROM table_olympics_data
LIMIT 100;