SnowSQL File Operations & Data Staging
Snowflake · Staging & SnowSQL
Theory
Three operations move files. Uploading puts local files into an internal stage, compressed with gzip by default and encrypted: PUT in SnowSQL or a driver, or snow stage copy in Snowflake CLI. Downloading (GET, or snow stage copy in reverse) brings staged files back to your machine. COPY INTO <table> then loads staged files into a table, using a file format that describes the delimiter, header rows, quoting, null markers and compression.
For example, PUT file:///data/orders.csv @my_stage creates orders.csv.gz in the stage, and COPY INTO orders FROM @my_stage FILE_FORMAT = (FORMAT_NAME = 'csv_fmt') loads it. In Snowsight you can skip the client: Ingestion → Add data uploads and loads smaller files through the browser.
Why we use it
Bulk loading from files is the fastest and cheapest way to get large data into Snowflake. Staging first separates moving the file from parsing it, so a failed load can be fixed and rerun without uploading again.
When to use it
Use PUT and COPY for files on your machine or a server. For files already in cloud storage, skip PUT and use an external stage. For continuous arrival of files, use Snowpipe instead of running COPY by hand.
PUT: Uploading Files
PUT uploads local files to a stage, compressing them by default (gzip) before transfer to save bandwidth and storage.
GET: Downloading Files
GET reverses the process, pulling staged files back down to your local machine — useful for retrieving exported query results.
COPY INTO: Loading the Data
Once files are staged, COPY INTO loads them into a target table using a file format object that defines the delimiter, header row, and character encoding to expect.
How it works
- CSV file on your machine
- PUT file to @my_stage (gzip and encrypt)
- LIST @my_stage to confirm
- COPY INTO table using a file format
- Were all rows loaded? Yes: Check row counts and REMOVE files. No: Review errors, fix the file or format, rerun COPY.
- Data is queryable in the table
Example
-- Run in a terminal, not Snowsight:
-- SnowSQL: PUT file:///Users/me/data/orders.csv @my_stage AUTO_COMPRESS=TRUE;
-- GET @my_stage/orders.csv.gz file:///Users/me/downloads/;
-- Snowflake CLI: snow stage copy /Users/me/data/orders.csv @my_stage -c my_connKey points
- PUT compresses files with gzip by default before upload
- COPY INTO loads staged files into a target table in parallel
- File format objects define delimiter, header, quoting and NULL rules
- COPY skips files it has already loaded, using load metadata
- Several medium-sized files load faster than one huge file