Data Loading and Unloading¶

📖 Data Loading Overview - Complete loading guide

Stages¶

Stages are locations where data files are stored for loading into or unloading from Snowflake.

📖 Stages Overview - Stage types and usage

Internal Stages¶

User Stage (@~): - Every user has one automatically - Cannot be altered, dropped, or shared - Referenced as @~ - Good for: personal data loading - Cannot set file format options on the stage itself

Table Stage (@%table_name): - Every table has one automatically - Cannot be altered or dropped - Referenced as @%my_table - Files can only be loaded into the associated table - Cannot set file format options on the stage itself - Does not support transformations during load

Named Internal Stage (CREATE STAGE): - Most flexible internal stage type - Can set default file format and COPY options - Can be granted to roles for shared access - Supports directory tables for file listing

CREATE STAGE my_internal_stage
  FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = '|' SKIP_HEADER = 1)
  COPY_OPTIONS = (ON_ERROR = 'CONTINUE');

📖 Internal Stages - Creating internal stages

External Stages¶

Point to cloud storage locations outside Snowflake.

Amazon S3:

CREATE STAGE my_s3_stage
  URL = 's3://mybucket/path/'
  STORAGE_INTEGRATION = my_s3_integration
  FILE_FORMAT = (TYPE = 'PARQUET');

Azure Blob Storage:

CREATE STAGE my_azure_stage
  URL = 'azure://myaccount.blob.core.windows.net/mycontainer/path/'
  STORAGE_INTEGRATION = my_azure_integration;

Google Cloud Storage:

CREATE STAGE my_gcs_stage
  URL = 'gcs://mybucket/path/'
  STORAGE_INTEGRATION = my_gcs_integration;

📖 S3 External Stage - S3 stage setup 📖 Azure External Stage - Azure stage setup 📖 GCS External Stage - GCS stage setup

Storage Integrations¶

• Avoid embedding credentials directly in stage definitions
• Created at the account level by ACCOUNTADMIN
• Reference a cloud storage location with IAM role (AWS) or service principal (Azure)
• More secure than using direct credentials

📖 Storage Integrations - Integration setup

PUT Command¶

• Uploads files from local machine to an internal stage
• Only works with internal stages (not external)
• Automatically compresses files with gzip (configurable)
• Parallel upload support

PUT file:///path/to/local/file.csv @my_stage AUTO_COMPRESS=TRUE;

📖 PUT Command - Upload syntax

GET Command¶

• Downloads files from an internal stage to local machine
• Only works with internal stages

GET @my_stage/file.csv file:///path/to/local/directory/;

📖 GET Command - Download syntax

COPY INTO (Loading Data)¶

📖 COPY INTO Table - Complete syntax reference

Basic Syntax¶

COPY INTO my_table
FROM @my_stage/path/
FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER = 1)
PATTERN = '.*[.]csv'
ON_ERROR = 'CONTINUE';

Loading with Transformations¶

COPY INTO my_table (col1, col2, col3)
FROM (
  SELECT $1, $2, TO_DATE($3, 'YYYY-MM-DD')
  FROM @my_stage/data.csv
)
FILE_FORMAT = (TYPE = 'CSV');

ON_ERROR Options¶

VALIDATION_MODE¶

• Does NOT load data - only validates
• RETURN_n_ROWS - validate and return first n rows
• RETURN_ERRORS - validate all rows, return errors
• RETURN_ALL_ERRORS - same as RETURN_ERRORS but returns all errors
• Useful for dry-run testing before actual load

COPY INTO my_table
FROM @my_stage
VALIDATION_MODE = 'RETURN_ERRORS';

Load History and Idempotency¶

• COPY INTO tracks loaded files for 64 days
• Will not re-load files that have already been loaded
• Use FORCE = TRUE to override this behavior
• Load history visible in COPY_HISTORY table function and Account Usage

-- Check load history
SELECT * FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
  TABLE_NAME => 'my_table',
  START_TIME => DATEADD(hours, -24, CURRENT_TIMESTAMP())
));

📖 Data Loading Best Practices - Optimization tips

File Preparation Best Practices¶

• Split large files into 100-250 MB compressed chunks
• Use gzip or bzip2 compression
• UTF-8 encoding recommended
• Consistent delimiter and escape characters
• Remove BOM (Byte Order Mark) from files
• Consistent date/time formats

File Formats¶

📖 File Format Options - Format configuration

Supported Formats¶

Named File Formats¶

CREATE FILE FORMAT my_csv_format
  TYPE = 'CSV'
  FIELD_DELIMITER = ','
  SKIP_HEADER = 1
  NULL_IF = ('NULL', 'null', '')
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  COMPRESSION = 'GZIP';

Snowpipe¶

📖 Snowpipe - Continuous loading

Architecture¶

• Serverless, event-driven continuous data loading
• Uses its own compute resources (not your virtual warehouses)
• Near real-time ingestion (typically within minutes)
• Per-second billing based on compute used

Auto-Ingest Setup¶

1. Create an external stage
2. Create a pipe with AUTO_INGEST = TRUE
3. Configure cloud event notifications:
4. AWS: S3 event notification to SQS queue
5. Azure: Event Grid to Storage Queue
6. GCP: Pub/Sub notification

CREATE PIPE my_pipe
  AUTO_INGEST = TRUE
AS
  COPY INTO my_table
  FROM @my_external_stage
  FILE_FORMAT = (TYPE = 'JSON');

REST API¶

• Alternative to auto-ingest
• Application calls Snowpipe REST endpoint with file list
• insertFiles endpoint triggers loading
• insertReport and loadHistoryScan for monitoring

📖 Snowpipe REST API - API endpoints

Snowpipe vs COPY INTO¶

Data Unloading¶

📖 COPY INTO Location - Unloading syntax

-- Unload to internal stage
COPY INTO @my_stage/output/
FROM my_table
FILE_FORMAT = (TYPE = 'CSV' COMPRESSION = 'GZIP')
HEADER = TRUE
OVERWRITE = TRUE;

-- Unload to external stage
COPY INTO @my_s3_stage/exports/
FROM (SELECT col1, col2 FROM my_table WHERE date_col > '2024-01-01')
FILE_FORMAT = (TYPE = 'PARQUET')
MAX_FILE_SIZE = 268435456; -- 256 MB

-- Unload with single file output
COPY INTO @my_stage/single_file
FROM my_table
FILE_FORMAT = (TYPE = 'CSV')
SINGLE = TRUE;

Unloading Options¶

• HEADER - include column headers (CSV only)
• SINGLE - output as single file (not recommended for large datasets)
• MAX_FILE_SIZE - control output file size
• OVERWRITE - replace existing files
• INCLUDE_QUERY_ID - add query ID to file name
• Partitioned unloading for organized output

📖 Unloading Best Practices - Optimization tips