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
In a nutshell
  • COMPRESSION = AUTO is the default for loading; PUT gzips uncompressed files by default.
  • STRIP_OUTER_ARRAY splits a JSON array into rows; FIELD_OPTIONALLY_ENCLOSED_BY handles quoted CSV fields.
  • ERROR_ON_COLUMN_COUNT_MISMATCH defaults to TRUE.

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.

Staged filecompresseddecompress byCompression settingAUTO or namedparse withFile format optionsdelimiter, quotes, nullsrows plusMetadata columnsfile name, row number

How it works

Compression

For loading, the COMPRESSION file format option tells Snowflake how the staged file is compressed.

SettingMeaning
AUTO (default)Snowflake detects the method from the file header, except for Brotli, which cannot be detected automatically
GZIP, BZ2, BROTLI, ZSTD, DEFLATE, RAW_DEFLATEName the method explicitly; required for Brotli files
NONEThe file is not compressed

Two details are tested often.

  • PUT compresses for you. With AUTO_COMPRESS = TRUE (default) an uncompressed file is gzipped on upload, and the staged name gets a .gz suffix. Set AUTO_COMPRESS = FALSE to keep it as is, and set SOURCE_COMPRESSION if 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.

Exam tip

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:

ProblemOption
Header line should not loadSKIP_HEADER = 1
Use header as column namesPARSE_HEADER = TRUE
Fields wrapped in quotes that contain commasFIELD_OPTIONALLY_ENCLOSED_BY = '"'
Different separator`FIELD_DELIMITER = '\' or '\t'`
Embedded newlines in valuesEnclose with quotes; keep RECORD_DELIMITER correct
Text values that mean nullNULL_IF = ('NULL', '\\N', '')
Empty field should be nullEMPTY_FIELD_AS_NULL (default TRUE)
Row has more or fewer fields than the tableERROR_ON_COLUMN_COUNT_MISMATCH (default TRUE)
Non UTF-8 textENCODING = 'ISO-8859-1' or similar (default UTF8)
Bad charactersREPLACE_INVALID_CHARACTERS = TRUE
Different date textDATE_FORMAT, TIMESTAMP_FORMAT

JSON parsing options:

ProblemOption
File is one big array of objectsSTRIP_OUTER_ARRAY = TRUE
Remove null values from objectsSTRIP_NULL_VALUES = TRUE
Same key appears twice in an objectALLOW_DUPLICATE = TRUE keeps the last value; otherwise error
Nested array should become rowsLoad into VARIANT, then LATERAL FLATTEN in a query
JSON fileouter arraySTRIP_OUTER_ARRAYOne row per elementVARIANTLATERAL FLATTENRows per nested item

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.

ColumnValue
METADATA$FILENAMEStaged path and file name
METADATA$FILE_ROW_NUMBERRow number within that file
METADATA$FILE_LAST_MODIFIEDLast modified timestamp of the file
METADATA$FILE_CONTENT_KEYChecksum of the file contents
METADATA$START_SCAN_TIMEWhen 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 @stage returns name, size, MD5 and last modified for each file.
  • Directory table, enabled with DIRECTORY = (ENABLE = TRUE), is queried with SELECT * FROM DIRECTORY(@stage) and returns RELATIVE_PATH, SIZE, LAST_MODIFIED, MD5, ETAG and FILE_URL. Run ALTER STAGE ... REFRESH to sync it, or set AUTO_REFRESH = TRUE with cloud event notifications (default is FALSE).
  • 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.
Watch out

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 = AUTO is the default for loading; PUT gzips uncompressed files by default.
  • STRIP_OUTER_ARRAY splits a JSON array into rows; FIELD_OPTIONALLY_ENCLOSED_BY handles quoted CSV fields.
  • ERROR_ON_COLUMN_COUNT_MISMATCH defaults to TRUE.
  • METADATA$FILENAME and METADATA$FILE_ROW_NUMBER record the source file and row; use them in SELECT or COPY transformations.
  • Directory tables expose file level metadata and need a refresh; LIST shows 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.