Data Movement and Ingestion
Stages and COPY
Ingestion centers on stages: internal (named, table, or user stage) and external (S3, GCS, Azure). PUT and GET upload and download files and work only through a client (SnowSQL, drivers, Snowpark), not the web worksheet. COPY INTO loads with file format options such as type, compression, field delimiter, and error handling. ON_ERROR controls behavior: ABORT_STATEMENT, CONTINUE, SKIP_FILE, or a count. VALIDATION_MODE validates without loading.
Continuous ingestion
Snowpipe loads files automatically via cloud event notifications using Snowflake-managed compute. Snowpipe Streaming writes rows directly through an SDK for low latency. Kafka connectors and partner tools feed Snowpipe. Metadata columns such as METADATA$FILENAME and METADATA$FILE_ROW_NUMBER help track lineage.
Traps
- Assuming PUT works in a worksheet, or that GET can download from an external stage.
- Ignoring file-size guidance: 100–250 MB compressed per file is the sweet spot; too many tiny files hurts.
- Forgetting that COPY tracks load metadata for 64 days, which affects reload behavior.
- Assuming Snowpipe uses your virtual warehouse (it does not).
- Missing INFER_SCHEMA or schema evolution when semi-structured shapes change.
Worked example: a daily load of 10,000 tiny JSON files is slow. Combine them into ~100 MB compressed files and use Snowpipe auto-ingest with ON_ERROR=CONTINUE plus a quarantine table.