Agenda (Module 4)
• Data Loading
➢ Stages, file format
➢ Bulk load using COPY
➢ Continuous Load Options – Snowpipe (Theory)
• Data Unloading
➢ Unloading data from Snowflake to Internal Snowflake Storage
➢ Unloading data from Snowflake to local / Network Drive
➢ Unloading data from Snowflake to external cloud storage (AWS S3)
• Hands On Labs
• Quiz
• Q&A
Overview of Data Loading
Supported File Locations
• Snowflake refers to the location of data files in cloud storage as a stage
• Snowflake supports loading data from both external stages (AWS S3, GCP, Azure), as well as cloud storage contained
in your snowflake account i.e. internal stages
External Stages
• Loading data from any of the following cloud storage services is supported regardless of the cloud platform that hosts
your Snowflake account:
➢ Amazon S3
➢ Google Cloud Storage
➢ Microsoft Azure
• Upload (i.e. stage) files to your cloud storage account using the tools provided by the cloud storage service.
• A named external stage is a database object created in a schema
• This object stores the URL to files in cloud storage, the settings used to access the cloud storage account, and
convenience settings such as the options that describe the format of staged files.
Reference Link: [Link]
Overview of Data Loading
Internal Stages
• Snowflake maintains the following stage types in your account:
➢ User Stage
❖ A user stage is allocated to each user for storing files.
❖ This stage type is designed to store files that are staged and managed by a single user but can be loaded
into multiple tables.
❖ User stages cannot be altered or dropped
➢ Table Stage
❖ A table stage is available for each table created in Snowflake.
❖ This stage type is designed to store files that are staged and managed by one or more users but only loaded
into a single table
❖ Table stages cannot be altered or dropped
➢ Named Stage
❖ A named internal stage is a database object created in a schema.
❖ This stage type can store files that are staged and managed by one or more users and loaded into one or
more tables.
❖ Because named stages are database objects, the ability to create, modify, use, or drop them can be
controlled using security access control privileges
• Upload files to any of the internal stage types from your local file system using the PUT command
Overview of Data Loading
Bulk vs Continuous Loading
Bulk Loading Continuous Loading
Usage This option enables loading batches of data from files already This option is designed to load small volumes of data (i.e. micro-
available in cloud storage, or copying (i.e. staging) data files batches) and incrementally make them available for analysis.
from a local machine to an internal (i.e. Snowflake) cloud Snowpipe loads data within minutes after files are added to a
storage location before loading the data into tables using the stage and submitted for ingestion. This ensures users have the
COPY command latest results, as soon as the raw data is available.
Compute Resources Bulk loading relies on user-provided virtual warehouses, which Snowpipe uses compute resources provided by Snowflake (i.e. a
are specified in the COPY statement. Users are required to size serverless compute model). These Snowflake-provided resources
the warehouse appropriately to accommodate expected are automatically resized and scaled up or down as required,
loads. and are charged and itemized using per-second billing. Data
ingestion is charged based upon the actual workloads.
Simple Transformations • Snowflake supports transforming data while loading it into a table using the COPY command. Options include:
During a Load ➢ Column reordering
➢ Column omission
➢ Casts
➢ Truncating text strings that exceed the target column length
• There is no requirement for your data files to have the same number and ordering of columns as your target table
Rich Learning Offerings
• Learning materials
• Quizzes and Puzzles
• Case studies
• Live Projects
Summary of Data Loading Features
• Data File Details
Reference Link: [Link]
Summary of Data Loading Features
• Supported Character Sets for Delimited Files
**These are just some sample character
sets supported by Snowflake. To get the
full list of characters set supported refer
link -
[Link]
guide/[Link]
Summary of Data Loading Features
• Compression of Staged Files
• Encryption of Staged Files
Rich Learning Offerings
• Learning materials
• Quizzes and Puzzles
• Case studies
• Live Projects
Overview of Data Unloading
• Similar to data loading, Snowflake supports bulk export (i.e. unload) of data from a database table into flat,
delimited text files
• Bulk Unloading Process
• The process for unloading data into files is the same as the loading process, except in reverse:
➢ Step 1: Use the COPY INTO <location> command to copy the data from the Snowflake database
table into one or more files in a Snowflake or external stage
➢ Step 2: Download the file from the stage:
✓ From a Snowflake stage, use the GET command to download the data file(s).
✓ From S3, use the interfaces/tools provided by Amazon S3 to get the data file(s).
✓ From Azure, use the interfaces/tools provided by Microsoft Azure to get the data file(s).
• Snowflake supports specifying a SELECT statement instead of a table in the COPY INTO
<location> command. The results of the query are written to one or more files as specified in the
command and the file(s) are stored in the specified location (internal or external).
• The COPY INTO <location> command provides a copy option (SINGLE) for unloading data into a single
file or multiple files. The default is SINGLE = FALSE (i.e. unload into multiple files).
Summary of Data Unloading Features
• Output Data File Details
• Compression of Output Data Files
Rich Learning Offerings
• Learning materials
• Quizzes and Puzzles
• Case studies
• Live Projects
Summary of Data Unloading Features
• Encryption of Output Data Files
• The following table describes how Snowflake handles encryption for the output files generated by
Snowflake when unloading data
• The options are different depending on whether the files are unloaded to an internal location (i.e.
Snowflake stage) or external location (Amazon S3, Google Cloud Storage, or Microsoft Azure):
Reference Link: [Link]
Snowpipe
• Snowpipe enables loading data from files as soon as they’re available in a stage
• This means you can load data from files in micro-batches, making it available to users within minutes,
rather than manually executing COPY statements on a schedule to load larger batches
• A pipe is a named, first-class Snowflake object that contains a COPY statement used by Snowpipe.
• The data is loaded according to the COPY statement defined in a referenced pipe.
• The COPY statement identifies the source location of the data files (i.e., a stage) and a target table.
Snowpipe (Mechanism to detect stage files)
• Automating Snowpipe using cloud messaging • Calling Snowpipe REST endpoints
Automated data loads leverage event notifications Your client application calls a public REST endpoint
for cloud storage to inform Snowpipe of the arrival with the name of a pipe object and a list of data
of new data files to load. Snowpipe copies the files filenames. If new data files matching the list are
into a queue, from which they are loaded into the discovered in the stage referenced by the pipe
target table in a continuous, serverless fashion object, they are queued for loading. Snowflake-
based on parameters defined in a specified pipe provided compute resources load data from the
object. queue into a Snowflake table based on parameters
defined in the pipe.
Snowflake currently supports the following storage
account types: Snowflake currently supports the following storage
• Amazon Web Services (AWS) - Amazon S3 account types:
• Microsoft Azure
➢ Blob storage • Amazon Web Services (AWS) - Amazon S3
➢ Data Lake Storage Gen2 • Google Cloud Platform - Cloud Storage
➢ General-purpose v2 • Microsoft Azure
➢ Blob storage
➢ Data Lake Storage Gen2 — Supported as
Reference Link: [Link] a preview feature.
[Link] ➢ General-purpose v2
Quiz
1. What are the different types of stage available in Snowflake?
a. Named Internal
b. External User Stage
c. Named External
d. External Table Stage
2. Which of the following stages cannot be altered?
a. Table Stage
b. User Stage
c. Named Internal Stage
d. Named External Stage
3. Snowpipe uses the virtual warehouse provided by the user to execute the loads?
a. True
b. False
4. Get command is used to upload the file in the Snowflake Stage
a. True
b. False
Quiz
5. Which of the following command cannot be executed via Snowflake Web UI?
a. GET
b. COPY INTO TABLE
c. PUT
d. COPY INTO LOCATION
6. Which of the following parameter need to be set if we want the copy command to load the file in
table even if it was loaded previously?
a. FORCE=TRUE;
b. FORCE=FALSE;
c. OVERWRITE=TRUE;
d. OVERWRITE=FALSE;
7. For unloading the file to Snowflake internal stage or external stage which command is used?
a. COPY INTO <TABLE>
b. COPY INTO <LOCATION>
c. UNLOAD INTO <TABLE>
d. UNLOAD INTO <LOCATION>
Answers
1. What are the different types of stage available in Snowflake?
a. Named Internal
b. External User Stage
c. Named External
d. External Table Stage
2. Which of the following stages cannot be altered?
a. Table Stage
b. User Stage
c. Named Internal Stage
d. Named External Stage
3. Snowpipe uses the virtual warehouse provided by the user to execute the loads?
a. True
b. False
4. Get command is used to upload the file in the Snowflake Stage
a. True
b. False
Answers
5. Which of the following command cannot be executed via Snowflake Web UI?
a. GET
b. COPY INTO TABLE
c. PUT
d. COPY INTO LOCATION
6. Which of the following parameter need to be set if we want the copy command to load the file in table
even if it was loaded previously?
a. FORCE=TRUE;
b. FORCE=FALSE;
c. OVERWRITE=TRUE;
d. OVERWRITE=FALSE;
7. For unloading the file to Snowflake internal stage or external stage which command is used?
a. COPY INTO <TABLE>
b. COPY INTO <LOCATION>
c. UNLOAD INTO <TABLE>
d. UNLOAD INTO <LOCATION>