Data Movement · Topic 4 of 17
Compression, Parsing Strategies and Staged File Metadata
Handle compression and CSV or JSON parsing options, and read staged file metadata with METADATA columns, LIST, directory tables and COPY transformations.
- 5 min
COMPRESSION = AUTOis the default for loading;PUTgzips uncompressed files by default.STRIP_OUTER_ARRAYsplits a JSON array into rows;FIELD_OPTIONALLY_ENCLOSED_BYhandles quoted CSV fields.ERROR_ON_COLUMN_COUNT_MISMATCHdefaults toTRUE.
What it is
Compression shrinks files for faster upload and cheaper storage, and Snowflake must know how a file was compressed to read it. Parsing strategy is the set of file format options that turn raw text into rows and columns, such as delimiters, quotes, headers and null markers.
Staged file metadata is information about the files themselves, such as name, row number inside the file and last modified time. You can read it before loading, or save it into the table during loading for lineage.
How it works
Compression
For loading, the COMPRESSION file format option tells Snowflake how the staged file is compressed.
| Setting | Meaning |
|---|---|
AUTO (default) | Snowflake detects the method from the file header, except for Brotli, which cannot be detected automatically |
GZIP, BZ2, BROTLI, ZSTD, DEFLATE, RAW_DEFLATE | Name the method explicitly; required for Brotli files |
NONE | The file is not compressed |
Two details are tested often.
PUTcompresses for you. WithAUTO_COMPRESS = TRUE(default) an uncompressed file is gzipped on upload, and the staged name gets a.gzsuffix. SetAUTO_COMPRESS = FALSEto keep it as is, and setSOURCE_COMPRESSIONif the local file is already compressed.- Parquet, Avro and ORC compress inside the file. Their internal compression (for example Snappy in Parquet) is read automatically and is separate from file level compression.
Each file is one load operation, so one large .gz file cannot be spread across parallel threads. For speed, split the data into many files of roughly 100 to 250 MB compressed before compressing, rather than producing one giant archive. When unloading, COMPRESSION works the same way, and the default for CSV and JSON is gzip.
A compressed CSV loading slowly is rarely fixed by a bigger warehouse. Many moderate files in parallel is the fix.
Parsing strategies for common formats
CSV parsing options and what they solve:
| Problem | Option | |
|---|---|---|
| Header line should not load | SKIP_HEADER = 1 | |
| Use header as column names | PARSE_HEADER = TRUE | |
| Fields wrapped in quotes that contain commas | FIELD_OPTIONALLY_ENCLOSED_BY = '"' | |
| Different separator | `FIELD_DELIMITER = '\ | ' or '\t'` |
| Embedded newlines in values | Enclose with quotes; keep RECORD_DELIMITER correct | |
| Text values that mean null | NULL_IF = ('NULL', '\\N', '') | |
| Empty field should be null | EMPTY_FIELD_AS_NULL (default TRUE) | |
| Row has more or fewer fields than the table | ERROR_ON_COLUMN_COUNT_MISMATCH (default TRUE) | |
| Non UTF-8 text | ENCODING = 'ISO-8859-1' or similar (default UTF8) | |
| Bad characters | REPLACE_INVALID_CHARACTERS = TRUE | |
| Different date text | DATE_FORMAT, TIMESTAMP_FORMAT |
JSON parsing options:
| Problem | Option |
|---|---|
| File is one big array of objects | STRIP_OUTER_ARRAY = TRUE |
| Remove null values from objects | STRIP_NULL_VALUES = TRUE |
| Same key appears twice in an object | ALLOW_DUPLICATE = TRUE keeps the last value; otherwise error |
| Nested array should become rows | Load into VARIANT, then LATERAL FLATTEN in a query |
A parsing strategy for semi-structured data is a choice between three designs: keep raw VARIANT and parse at query time, map to typed columns at load time with a COPY transformation or MATCH_BY_COLUMN_NAME, or load raw first and then transform with SQL. Raw first is flexible and preserves the source; typed at load is faster to query.
Extracting metadata from staged files
Snowflake exposes metadata pseudo columns for staged files.
| Column | Value |
|---|---|
METADATA$FILENAME | Staged path and file name |
METADATA$FILE_ROW_NUMBER | Row number within that file |
METADATA$FILE_LAST_MODIFIED | Last modified timestamp of the file |
METADATA$FILE_CONTENT_KEY | Checksum of the file contents |
METADATA$START_SCAN_TIME | When the row was scanned in the load |
You can query them straight from a stage, with the file format and an optional pattern.
SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, $1, $2
FROM @raw_stage/orders/ (FILE_FORMAT => 'csv_ff', PATTERN => '.*2026-11.*[.]csv');
They can also be saved into the table during COPY, which records which file and row every record came from.
COPY INTO orders (id, amount, src_file, src_row)
FROM (SELECT $1, $2, METADATA$FILENAME, METADATA$FILE_ROW_NUMBER
FROM @raw_stage/orders/)
FILE_FORMAT = (FORMAT_NAME = 'csv_ff');
Other ways to see what is in a stage:
LIST @stagereturns name, size, MD5 and last modified for each file.- Directory table, enabled with
DIRECTORY = (ENABLE = TRUE), is queried withSELECT * FROM DIRECTORY(@stage)and returnsRELATIVE_PATH,SIZE,LAST_MODIFIED,MD5,ETAGandFILE_URL. RunALTER STAGE ... REFRESHto sync it, or setAUTO_REFRESH = TRUEwith cloud event notifications (default isFALSE). - INFER_SCHEMA returns the column structure of the files.
- Streams on directory tables capture new, changed and removed files, which suits pipelines that process each file once.
A directory table is metadata only and is not refreshed in real time unless auto refresh is configured. If new files are missing from DIRECTORY(@stage), run ALTER STAGE ... REFRESH first.
Metadata columns are never returned by a plain SELECT *; you must name them explicitly, in a stage query or a COPY transformation.
Common mistakes
Setting COMPRESSION to the wrong method. An explicit setting that does not match the file fails the load. AUTO avoids that, except for Brotli files, which need BROTLI set explicitly.
Compressing everything into one huge gzip file. One file is one load operation, so the load cannot run in parallel.
Expecting METADATA$ columns in SELECT *. They appear only when you name them.
Forgetting to refresh a directory table. Without AUTO_REFRESH or a manual refresh, new files are not listed.
Mixing SKIP_HEADER and PARSE_HEADER. Pick one.
What to remember for the exam
COMPRESSION = AUTOis the default for loading;PUTgzips uncompressed files by default.STRIP_OUTER_ARRAYsplits a JSON array into rows;FIELD_OPTIONALLY_ENCLOSED_BYhandles quoted CSV fields.ERROR_ON_COLUMN_COUNT_MISMATCHdefaults toTRUE.METADATA$FILENAMEandMETADATA$FILE_ROW_NUMBERrecord the source file and row; use them inSELECTor COPY transformations.- Directory tables expose file level metadata and need a refresh;
LISTshows files and MD5.
Check yourself
How do you store the source file name with each loaded row?
Use a COPY transformation that selects METADATA$FILENAME into a target column.
A JSON file contains one array with 50,000 objects. How do you load one row per object?
Set STRIP_OUTER_ARRAY = TRUE in the JSON file format.
New files do not show up in a directory table query. What do you run?
ALTER STAGE stage_name REFRESH, or enable AUTO_REFRESH with event notifications.
Topic 4 of 17 in Data Movement
Test yourself on Data Movement
The full Snowflake DEA-C02 question bank has 709 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.