Course contents

SnowPro Core COF-C03 Study GuidePart 4: Data loading and storageFile Formats

File Formats

File Formats

In shortA file format tells COPY how to read the bytes of a file. If you don't give one anywhere, Snowflake reads every file as comma-separated CSV.

The problem

A file is just a sequence of bytes. To turn it into rows, COPY needs the type (CSV, JSON, Avro, ORC, Parquet, XML), the compression, and for CSV the delimiter, the header handling, the quoting and the NULL markers. A file format is that set of parsing instructions. It can be a named, reusable schema object, or written inline in a statement.

How it works: which format wins

You can set a file format on the COPY, on the stage or on the table (its STAGE_FILE_FORMAT), and if none does, Snowflake reads CSV. Snowflake checks them in that order and uses the first level that sets a format. File format options are not cumulative: the winning level replaces the lower levels' format options completely, it doesn't merge them option by option. Copy options such as ON_ERROR work differently: they are merged, and the higher level wins option by option. Snowflake never picks the file type from the extension, but it does detect compression such as gzip on its own.

File format precedence. Snowflake checks four levels in order: the FILE_FORMAT on the COPY statement, then the stage's file format, then the table's STAGE_FILE_FORMAT, then its default, CSV with a comma. The first level that sets a format wins, and its options replace the lower levels as a whole. Example: the stage sets CSV with SKIP_HEADER = 1, the COPY sets CSV with FIELD_DELIMITER = ';'. The COPY's format is used alone, so SKIP_HEADER goes back to 0 and the header line loads as a data row.WHO DECIDES · CHECKED TOP DOWNEXAMPLE · OPTIONS DO NOT MERGE1 · COPY statementFILE_FORMAT = (…)2 · StageCREATE STAGE … FILE_FORMATnot set3 · TableSTAGE_FILE_FORMATnot set4 · Snowflake defaultCSV, comma, no header skipnot setThe first level that is set wins, whole.LEVEL 2 · IGNOREDStage formatTYPE = CSV · SKIP_HEADER = 1LEVEL 1COPY formatTYPE = CSV · FIELD_DELIMITER = ';'RESULTFormat actually usedthe COPY's alone: SKIP_HEADER = 0the header line loads as a rowSet every option you need in the winning format.level 1 wins

WHO DECIDES · CHECKED TOP DOWN

1 · COPY statementFILE_FORMAT = (…)

not set

2 · StageCREATE STAGE … FILE_FORMAT

not set

3 · TableSTAGE_FILE_FORMAT

not set

4 · Snowflake defaultCSV, comma, no header skip

The first level that is set wins, whole.

EXAMPLE · OPTIONS DO NOT MERGE

LEVEL 2 · IGNOREDStage formatTYPE = CSV · SKIP_HEADER = 1

LEVEL 1COPY formatTYPE = CSV · FIELD_DELIMITER = ';'

level 1 wins

RESULTFormat actually usedthe COPY's alone: SKIP_HEADER = 0the header line loads as a row

Set every option you need in the winning format.

Figure 4.3 Format precedence, and the consequence people miss. The stage sets SKIP_HEADER = 1, then the COPY sets its own CSV format to change the delimiter. The COPY's format wins as a whole, so SKIP_HEADER is back to its default of 0 and the header line loads as data (or fails on a numeric column).

Example: two reusable formats

CREATE FILE FORMAT sales_db.raw.csv_std
  TYPE = CSV
  SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  NULL_IF = ('', 'NULL')
  EMPTY_FIELD_AS_NULL = TRUE;   -- already the default; shown for clarity

CREATE FILE FORMAT sales_db.raw.json_events
  TYPE = JSON
  STRIP_OUTER_ARRAY = TRUE;   -- one row per element of a top-level [...] array

ALTER STAGE sales_db.raw.s3_landing SET FILE_FORMAT = (FORMAT_NAME = 'sales_db.raw.csv_std');

Expected resultAny COPY from s3_landing that doesn't name a format now skips the header line and turns empty fields into NULL. A COPY that names FILE_FORMAT = (FORMAT_NAME = 'sales_db.raw.csv_std') gets exactly the same behavior, which is the safest habit: reference one named format everywhere instead of repeating options.

Watch out
  • A stage full of .json files with no format anywhere is parsed as CSV. The load fails or produces nonsense.
  • Overriding one option on COPY silently drops the stage's other format options.
Exam takeaway
  • Precedence: COPY statement > stage > table; nothing anywhere = CSV.
  • File format options don't merge across levels; copy options do.
  • Extensions are never used to guess the format.