COPY INTO command in Snowflake - Mini Project: Build & Apply

Loading Data into Snowflake Using COPY INTO Command

📖 Scenario: You are working as a data engineer. You have a CSV file stored in a Snowflake stage. Your task is to load this data into a Snowflake table using the COPY INTO command.

🎯 Goal: Build a Snowflake script that creates a table, sets up a stage, and uses the COPY INTO command to load data from a CSV file into the table.

📋 What You'll Learn

Create a table named employees with columns id (integer), name (string), and salary (number).

Create a named stage called my_csv_stage pointing to an internal location.

Use the COPY INTO command to load data from a CSV file named employees.csv in the stage into the employees table.

Specify the file format as CSV with header row skipped.

💡 Why This Matters

🌍 Real World

Loading data from files into Snowflake tables is a common task in data engineering pipelines to prepare data for analysis.

💼 Career

Understanding how to use the COPY INTO command and manage stages and file formats is essential for roles like data engineer, cloud data architect, and database administrator working with Snowflake.

1

Create the employees table

Write a SQL statement to create a table called employees with columns id as INTEGER, name as VARCHAR, and salary as NUMBER.

Snowflake

-- Create the employees table
-- Your code here

Need a hint?

Use CREATE OR REPLACE TABLE employees and define the three columns with their types.

2

Create an internal stage named my_csv_stage

Write a SQL statement to create or replace an internal stage called my_csv_stage.

Snowflake

CREATE OR REPLACE TABLE employees (
    id INTEGER,
    name VARCHAR,
    salary NUMBER
);

-- Create the internal stage my_csv_stage
-- Your code here

Need a hint?

Use CREATE OR REPLACE STAGE my_csv_stage; to create the stage.

3

Write the COPY INTO command to load data

Write a COPY INTO command to load data from the file employees.csv in the stage my_csv_stage into the employees table. Specify the file format as CSV and skip the header row.

Snowflake

CREATE OR REPLACE TABLE employees (
    id INTEGER,
    name VARCHAR,
    salary NUMBER
);

CREATE OR REPLACE STAGE my_csv_stage;

-- Write COPY INTO command to load employees.csv from my_csv_stage
-- Your code here

Need a hint?

Use COPY INTO employees FROM @my_csv_stage/employees.csv and specify the file format with SKIP_HEADER = 1.

4

Add a file format object for CSV with header skip

Create or replace a file format named csv_format with type CSV and skip the header row. Then update the COPY INTO command to use this file format by name.

Snowflake

CREATE OR REPLACE TABLE employees (
    id INTEGER,
    name VARCHAR,
    salary NUMBER
);

CREATE OR REPLACE STAGE my_csv_stage;

-- Create file format csv_format and update COPY INTO to use it
-- Your code here

Need a hint?

First create the file format with CREATE OR REPLACE FILE FORMAT csv_format TYPE = 'CSV' SKIP_HEADER = 1;. Then use FILE_FORMAT = (FORMAT_NAME = 'csv_format') in the COPY INTO command.