Loading Data
Loading Data
BULK CONTINUOUS
LOADING LOADING
Most frequent method Designed to load small volumes of data
Uses warehouses Automatically once they are added to
stages
Loading from stages
Lates results for analysis
COPY command
Snowpipe (Serverless feature)
Transformations possible
Understanding Stages
Not to be confused with dataware house
sLotacgaetison of data files where data can be
loaded from
External Internal
Stage Stage
Understanding Stages
External Internal
Stage Stage
External cloud provider Local storage
maintained
S3
by Snowflake
Google Cloud Plattform
Microsoft Azure
Database object created in Schema
CREATE STAGE (URL, access
settings)
Note: Additional costs may
apply if region/platform
differs
Copy Options
COPY INTO <table_name>
FROM externalStage
FILES = ( '<file_name>' ,'<file_name2>')
FILE_FORMAT = <file_format_name>
copyOptions
Copy Options
COPY INTO <table_name>
FROM externalStage
FILES = ( '<file_name>' ,'<file_name2>')
FILE_FORMAT = <file_format_name>
ON_ERROR = CONTINUE
Copy Options
COPY INTO <table_name>
FROM externalStage
FILES = ( '<file_name>' ,'<file_name2>')
FILE_FORMAT = <file_format_name>
VALIDATION_MODE =
RETURN_n_ROWS | RETURN_ERRORS
Validate the data files instead of loading
them
Copy Options
COPY INTO <table_name>
FROM externalStage
FILES = ( '<file_name>' ,'<file_name2>')
FILE_FORMAT = <file_format_name>
VALIDATION_MODE = RETURN_n_ROWS | RETURN_ERRORS
Validate the data files instead of loading
them
Copy Options
COPY INTO <table_name>
FROM externalStage
FILES = ( '<file_name>' ,'<file_name2>')
FILE_FORMAT = <file_format_name>
SIZE_LIMIT = num
Specify maximum size (in bytes) of data loaded in that command (at least one
file)
When the threshold is exceeded, the COPY operation
stops loading
Copy Options
COPY INTO <table_name>
FROM externalStage
FILES = ( '<file_name>' ,'<file_name2>')
FILE_FORMAT = <file_format_name>
RETURN_FAILED_ONLY = TRUE | FALSE
Specifies whether to return only files that have failed to load in the statement
result
DEFAULT = FALSE
Copy Options
COPY INTO <table_name>
FROM externalStage
FILES = ( '<file_name>' ,'<file_name2>')
FILE_FORMAT = <file_format_name>
TRUNCATECOLUMNS = TRUE | FALSE
Specifies whether to truncate text strings that exceed the target column
length
Copy Options
COPY INTO <table_name>
FROM externalStage
FILES = ( '<file_name>' ,'<file_name2>')
FILE_FORMAT = <file_format_name>
FORCE = TRUE | FALSE
Specifies to load all files, regardless of whether they’ve been loaded previously
and
have not changed since they were loaded
Note that this option reloads files, potentially
duplicating data in a table
Copy Options
COPY INTO <table_name>
FROM externalStage
FILES = ( '<file_name>' ,'<file_name2>')
FILE_FORMAT = <file_format_name>
TRUNCATECOLUMNS = TRUE | FALSE
Specifies whether to truncate text strings that exceed the target column length
TRUE = strings are automatically truncated to the target column length
FALSE = COPY produces an error if a loaded string exceeds the target column
length
DEFAULT = FALSE
Copy Options
COPY INTO <table_name>
FROM externalStage
FILES = ( '<file_name>' ,'<file_name2>')
FILE_FORMAT = <file_format_name>
SIZE_LIMIT = num
Specify maximum size (in bytes) of data loaded in that command (at least one
file)
When the threshold is exceeded, the COPY operation stops
loading
Threshold for each file
DEFAULT: null (no size limit)
Copy Options
COPY INTO <table_name>
FROM externalStage
FILES = ( '<file_name>' ,'<file_name2>')
FILE_FORMAT = <file_format_name>
PURGE = TRUE | FALSE
specifies whether to remove the data files from the
stage
automatically after the data is loaded successfully
DEFAULT: FALSE
Load unstructured data
Create
Stage
Load raw Type VARIANT
data
Analyse & Parse
Flatten &
Load
