Data Loading and Unloading
Stages and the load path
Snowflake loads through stages. A user stage (@~) is private to one user, a table stage (@%table_name) is tied to a table and usable by anyone with INSERT rights, and a named stage (internal @my_stage, or external pointing at S3/Azure/GCS) is a database object you can grant privileges on. PUT uploads local files to an internal stage and GET downloads them; both run only through a client such as SnowSQL or the connectors - not from a worksheet.
COPY INTO <table> FROM @stage loads data under a file format (type, field delimiter, SKIP_HEADER, compression, error handling). COPY is idempotent: a file already loaded is skipped for 64 days unless FORCE = TRUE. ON_ERROR controls behavior (ABORT_STATEMENT is the default; also CONTINUE and SKIP_FILE). VALIDATION_MODE checks files without loading them. PURGE = TRUE deletes staged files after a successful load. Metadata columns such as METADATA$FILENAME let you audit source files.
Continuous and bulk
Snowpipe loads files automatically as they arrive, using file event notifications and Snowflake-managed compute. For unloading, COPY INTO @stage FROM table writes files; control output with SINGLE, MAX_FILE_SIZE, HEADER, OVERWRITE, and partition the output by column for efficient downstream reads.
Traps
- Trying
PUTinside the web worksheet. - Forgetting a file format when the stage has none, or mismatched delimiters.
- Expecting Snowpipe to re-ingest a file it already processed.
- Loading one giant file; splitting into 100–250 MB compressed files parallelizes far better.
Rule of thumb
Stage → validate → load: PUT, then COPY ... VALIDATION_MODE = 'RETURN_ERRORS', then the real load with ON_ERROR = 'SKIP_FILE'.