Summary

The web content provides a detailed guide on using the 'COPY INTO' feature in Snowflake to efficiently load and manage data from local systems or cloud storage like S3 into Snowflake tables, ensuring data is not duplicated during the loading process.

Abstract

The article titled '‘COPY INTO’ Snowflake' explains the 'COPY INTO' feature, which is designed to load and unload data to and from Snowflake stages. It assumes the reader has prior knowledge of Snowflake stages, S3 cloud storage, and Snowflake storage integration. The guide demonstrates how to create a storage integration, upload CSV files to an S3 bucket, and define an external stage in Snowflake to access these files. It also covers creating a file format to handle CSV files with specific delimiters and headers, and how to create a table to receive the data. The 'COPY INTO' command is used to load data into the table, with Snowflake intelligently avoiding the reloading of already processed files, thus maintaining idempotency. The article further explains how to force reloading of files if necessary and encourages readers to explore other 'COPY INTO' parameters, such as loading JSON files or unloading data to S3.

Opinions

The author believes that Snowflake's 'COPY INTO' feature is smart, as it prevents duplicate loading of files, thereby maintaining data integrity without additional effort.
Snowflake's design is praised for helping users keep their data loading processes idempotent.
The author suggests that readers can intentionally reload files using the FORCE=TRUE option in the 'COPY INTO' command if needed.
To reset the loading process and start fresh, the author recommends truncating the table and setting FORCE=FALSE.
The article promotes the 'COPY INTO' feature as both useful and efficient, indicating the author's positive opinion of its capabilities.
The author encourages readers to engage with the content by trying out different parameters and reaching out with questions, showing a commitment to reader engagement and learning.
A call to action is made for readers to support the author by clapping, following on Medium, and considering a membership to access more content.

‘COPY INTO’ Snowflake

A feature that enables us to load and unload the data from snowflake’s existing tables into the snowflake stages.

You can use the snowflake copy command to load the data from the local system by internal stage or from the cloud storage like s3 by external stage.

If you are reading this article I assume that you are already aware of the Snowflake stage concept, Cloud storage S3, how to integrate cloud storage with snowflake, and Snowflake storage integration.

Check out my other articles to get some understanding of these topics. Export Data From Snowflake To S3 Load CSV from Local to Snowflake Based on the above articles create a storage integration in snowflake( In my case it is AYUSH_SI)

I have two file load1.csv and load2.csv that contains two columns NAME and MARKS.

Now upload the load1.csv file to the S3 bucket (In my case it is s3://dev-ayush-test/test/) and create an external stage to view the data from the loaded CSV file directly into the snowflake.

CREATE OR REPLACE DATABASE AYUSH_DEV;
CREATE OR REPLACE SCHEMA AYUSH_DEV.RAW;
CREATE STAGE impact_stage
  STORAGE_INTEGRATION = AYUSH_SI
  URL = 's3://dev-ayush-test/test/';
SHOW STAGES;
--Access S3 data
SELECT $1 FROM @test_stage;

The output of ‘SELECT $1 FROM @test_stage;’

We can see that the data has a header and the columns are separated by semicolons so we need to tell the snowflake about our file structure. We can do it by creating a file format in the snowflake and afterward passing the file format in the stage definition.

CREATE OR REPLACE FILE FORMAT csv_semicolon
  TYPE = CSV
  FIELD_DELIMITER = ';'
  SKIP_HEADER = 1
  NULL_IF = ('NULL', 'null')
  EMPTY_FIELD_AS_NULL = true;

CREATE STAGE impact_stage
  STORAGE_INTEGRATION = AYUSH_SI
  URL = 's3://dev-ayush-test/test/'
  FILE_FORMAT = csv_semicolon;

Now create a table to load the data from the s3 location and run the copy command to load the data into the newly created table.

CREATE OR REPLACE TABLE RAW.STUDENT
(
NAME VARCHAR,
MARKS INTEGER
);

COPY INTO RAW.STUDENT
FROM @test_stage;

Let’s run the same copy command again and see what happens.

We received a status saying 0 files have been processed. So, did you notice that the COPY INTO is smart enough to know which files it already loaded and doesn’t load the same file, twice?

Snowflake is designed like this to help you. Without any special effort on your part, you have a process that doesn’t double-load records. In other words, it automatically enables you to keep your processes IDEMPOTENT.

But, what if, for some crazy reason, you wanted to double-load your files?

You could add a FORCE=TRUE; as the last line of your COPY INTO statement and then you would double the number of rows in your table.

Then, what if you wanted to start over and load just one copy of each file?

You could TRUNCATE TABLE; set FORCE=FALSE, and run your COPY INTO again.

The COPY INTO is brilliant, which makes it useful and efficient!!

What next? Use your learning to load the data from S3 to snowflake and try other parameters e.g TYPE=JSON etc in the COPY INTO. One hint same copy command is used to unload the data from snowflake tables to S3 buckets try it out by yourself. If you think that this article is informative and helped you with what you are looking then give a clap and follow my medium account( datageeks.medium.com ). Feel free to write in the comments or email me at [email protected] in case of any questions about this topic. By signing up as a member (https://datageeks.medium.com/membership), you can read every story and help the authors on Medium.