SnowPro Advanced Data Engineer: Data Movement: Loading and Unloading

What candidates must know

Loading is stage-centric: COPY INTO <table> FROM @stage, while unloading is COPY INTO @stage FROM (SELECT ...). Internal stages are the user stage (@~), the table stage (@%tbl) and named stages, populated with PUT/GET; external stages point at S3, Azure or GCS through a storage integration. Expect questions on file formats, PATTERN, ON_ERROR, PURGE, FORCE, VALIDATION_MODE, MATCH_BY_COLUMN_NAME, INFER_SCHEMA with USING TEMPLATE, metadata columns (METADATA$FILENAME, $FILE_ROW_NUMBER) and directory tables refreshed with ALTER STAGE ... REFRESH.

Traps

• Re-running COPY INTO silently skips files already loaded in the last 64 days; only FORCE = TRUE reloads them.
• ON_ERROR defaults to ABORT_STATEMENT. CONTINUE loads good rows and reports bad ones; SKIP_FILE discards the whole file.
• VALIDATION_MODE validates and reports errors but never loads data.
• PUT/GET do not work against external stages, and staged files on external storage are never written by PUT.
• Unload requires a stage or external location, not a table name; SINGLE = TRUE collapses output into one file and destroys parallel reads.

Rule of thumb

Target roughly 100-250 MB compressed files so each warehouse thread has work; thousands of tiny files mostly add queueing overhead. A bigger warehouse helps with large files, not with a backlog of small ones (use more pipes or Snowpipe instead).

Worked example

A nightly job reports 100 files read, 99 loaded, 1 error. Inspect the COPY result columns and COPY_HISTORY: compare rows_parsed, rows_loaded and errors_seen, run VALIDATION_MODE = RETURN_ERRORS to find the bad row, fix it, then reload with FORCE = TRUE for that file only.