Data Loading, Unloading, and Connectivity · Topic 1 of 7
Data Loading, Unloading and File Formats
Learn the SnowPro Core load and unload path: PUT, COPY INTO, GET, supported file formats, named file format objects, compression and file sizing.
- 5 min
- Load: files go to a stage, then
COPY INTO <table>reads them on a warehouse. Unload:COPY INTO @stage FROM table or query, then GET for internal stages. - PUT uploads to internal stages only and gzip compresses by default.
- Load formats: CSV, JSON, Avro, ORC, Parquet, XML. Unload formats: CSV, JSON, Parquet.
What it is
Loading moves data from files into Snowflake tables. Unloading writes table or query results out to files. Both directions go through a stage, a named location that holds the files, and both use the same command family: COPY INTO.
A file format tells Snowflake how to read or write those files: the type (CSV, JSON, Parquet and so on), the delimiter, the header, the compression and many other details. You can define it inline each time or save it as a reusable named file format object.
How it works
The load path
A bulk load has two steps. First the files land in a stage, then COPY INTO <table> reads them on a running virtual warehouse.
- From your own machine: run
PUT file://... @stagefrom a client such as SnowSQL, Snowflake CLI or a driver. PUT compresses files with gzip by default (AUTO_COMPRESS = TRUE) and encrypts them in the internal stage. - From cloud storage: files already in Amazon S3, Google Cloud Storage or Azure storage are read through an external stage, so no PUT is needed.
- From Snowsight: the load data wizard uploads small files and runs the load for you, which is handy for one-off files.
The unload path
COPY INTO @stage FROM <table or (query)> writes files into a stage. From an internal stage you download them with GET; from an external stage you use the cloud provider's own tools.
| Unload option | Default | What it does |
|---|---|---|
SINGLE | FALSE | FALSE splits output into several files in parallel; TRUE writes one file |
MAX_FILE_SIZE | 16 MB | Target size of each output file, up to 5 GB for a cloud stage |
HEADER | FALSE | TRUE writes column names in CSV files |
OVERWRITE | FALSE | TRUE replaces files with the same name |
PARTITION BY | none | Splits output into folders by an expression, such as a date |
Unloaded CSV and JSON files are gzip compressed by default, and Parquet files use Snappy.
Supported file formats
| Type | Load | Unload | Notes |
|---|---|---|---|
| CSV (delimited text) | Yes | Yes | Default type; any delimiter, not only commas |
| JSON | Yes | Yes | Unloads only VARIANT data, as NDJSON |
| Parquet | Yes | Yes | Columnar; Snappy compressed on unload |
| Avro | Yes | No | Semi-structured |
| ORC | Yes | No | Semi-structured |
| XML | Yes | No | Semi-structured |
The exam often tests the unload column: only CSV, JSON and Parquet can be unloaded. Semi-structured types land in a VARIANT column unless you transform them into separate columns during the load.
Named file format objects
CREATE FILE FORMAT my_csv
TYPE = CSV
FIELD_DELIMITER = '|'
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '"'
NULL_IF = ('NULL', '');
A named file format is a schema level object, so it can be granted, reused by many stages and loads, and changed in one place. You reference it with FILE_FORMAT = (FORMAT_NAME = 'my_csv').
You can set a file format on the stage or directly in the COPY statement. A file format given in the COPY statement overrides the one set on the stage, so one stage can serve loads that need different options. FORMAT_NAME and TYPE are mutually exclusive in one definition.
| Option | Type | Purpose |
|---|---|---|
FIELD_DELIMITER | CSV | Column separator; default is a comma |
SKIP_HEADER | CSV | Number of header lines to skip |
PARSE_HEADER | CSV | Reads column names from the header, used with schema detection |
FIELD_OPTIONALLY_ENCLOSED_BY | CSV | Quote character around values that contain the delimiter |
NULL_IF | CSV and others | Strings to load as SQL NULL |
STRIP_OUTER_ARRAY | JSON | Removes the outer brackets so each array element loads as its own row |
COMPRESSION | All | AUTO detects gzip, bzip2, Zstandard and more on load; Brotli must be named |
A JSON file that holds one big array loads as a single row unless you set STRIP_OUTER_ARRAY = TRUE. That option is a favorite exam answer for "the whole file landed in one row".
File sizing
Snowflake loads files in parallel, one file per thread, so many medium files beat one huge file. The official guidance is roughly 100 to 250 MB compressed per file. Very large files (100 GB or more) should be split, and thousands of tiny files should be combined.
Common mistakes
Expecting to unload Avro, ORC or XML. Those types load but do not unload. Pick CSV, JSON or Parquet for output.
Forgetting PUT runs from a client. PUT and GET work with internal stages from a client such as SnowSQL, Snowflake CLI or a driver. GET does not download from external stages; use the cloud provider's tools for those files.
Expecting one output file. COPY INTO @stage writes several files by default because SINGLE = FALSE. Set SINGLE = TRUE and raise MAX_FILE_SIZE if a consumer needs exactly one file.
Sizing the warehouse before the files. A bigger warehouse cannot speed up a load of one giant file. Split the file first.
Unloaded files do not include a header row by default. If a downstream tool expects column names in a CSV file, set HEADER = TRUE in the unload.
What to remember for the exam
- Load: files go to a stage, then
COPY INTO <table>reads them on a warehouse. Unload:COPY INTO @stage FROM table or query, then GET for internal stages. - PUT uploads to internal stages only and gzip compresses by default.
- Load formats: CSV, JSON, Avro, ORC, Parquet, XML. Unload formats: CSV, JSON, Parquet.
- Unload defaults:
SINGLE = FALSE,MAX_FILE_SIZE16 MB (up to 5 GB),HEADER = FALSE, gzip for CSV and JSON. - Named file formats are reusable schema objects; a file format in the COPY statement overrides the stage's file format.
- Target 100 to 250 MB compressed files for parallel loading.
Check yourself
Which file types can you unload from Snowflake?
Only CSV, JSON and Parquet. Avro, ORC and XML can be loaded but not unloaded.
A JSON file containing one array loads as a single row. What fixes it?
Set the file format option STRIP_OUTER_ARRAY = TRUE so each array element loads as its own row.
Why does splitting a 50 GB file speed up a load?
Snowflake loads files in parallel, so many 100 to 250 MB files let every warehouse thread work at once.
Topic 1 of 7 in Data Loading, Unloading, and Connectivity
Test yourself on Data Loading, Unloading, and Connectivity
The full Snowflake SnowPro Core COF-C03 question bank has 845 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.