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
In a nutshell
  • 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.

Local fileslaptop or serverPUTInternal stageencryptedCOPY INTO tableTablemicro partitionsTableor queryCOPY INTO stageStageinternal or externalGET or cloud toolsFilesdownloaded

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://... @stage from 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 optionDefaultWhat it does
SINGLEFALSEFALSE splits output into several files in parallel; TRUE writes one file
MAX_FILE_SIZE16 MBTarget size of each output file, up to 5 GB for a cloud stage
HEADERFALSETRUE writes column names in CSV files
OVERWRITEFALSETRUE replaces files with the same name
PARTITION BYnoneSplits 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

TypeLoadUnloadNotes
CSV (delimited text)YesYesDefault type; any delimiter, not only commas
JSONYesYesUnloads only VARIANT data, as NDJSON
ParquetYesYesColumnar; Snappy compressed on unload
AvroYesNoSemi-structured
ORCYesNoSemi-structured
XMLYesNoSemi-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.

OptionTypePurpose
FIELD_DELIMITERCSVColumn separator; default is a comma
SKIP_HEADERCSVNumber of header lines to skip
PARSE_HEADERCSVReads column names from the header, used with schema detection
FIELD_OPTIONALLY_ENCLOSED_BYCSVQuote character around values that contain the delimiter
NULL_IFCSV and othersStrings to load as SQL NULL
STRIP_OUTER_ARRAYJSONRemoves the outer brackets so each array element loads as its own row
COMPRESSIONAllAUTO detects gzip, bzip2, Zstandard and more on load; Brotli must be named
Exam tip

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.

One huge filesingle threadsplit intoMany files100 to 250 MB compressedload in parallelWarehouseall threads busy

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.

Watch out

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_SIZE 16 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.