A file format tells Snowflake how to parse data in staged files during loading, unloading, or inspection. It identifies a format such as CSV, tab-separated values, JSON, Avro, ORC, Parquet, or XML and can supply properties such as delimiters, header handling, and data types. For a CSV file whose first row contains column names, skipping that row prevents it from being loaded as data. section 2.3 section 2.3.1
Use FILE_FORMAT in COPY INTO when the load needs parsing rules that differ from, or are not already attached to, the stage or target table. A simple inline definition keeps a one-off load local to its command: type = CSV and skip_header = 1 parse a CSV while omitting its header row. COPY can also select positional staged-file columns such as $1 through $5 and add metadata$filename or a load timestamp to the target rows. section 2.3.1 section 2.3
Load a CSV while skipping its header
The COPY statement reads five positional columns from ORDERS_STAGE, records the source filename and current timestamp, and applies CSV parsing with a one-line header skipped. ON_ERROR = ABORT_STATEMENT stops the load on an error; PURGE = TRUE removes the internal staged file after successful ingestion. section 2.3.1
Listing 3.3 Loading data using a named file format
copy into ORDERS_BISTRO_STG from ( select $1, $2, $3, $4, $5, metadata$filename, current_timestamp() from @BISTRO_STAGE ) file_format = ORDERS_CSV_FORMAT #1 on_error = abort_statement;
A named format replaces repeated inline settings when CSV files share the same delimiter and header layout. section 3.1.6
Choose where a file format is defined deliberately:
- Create a named file format when the same parsing configuration is reused across loads. section 3.1.6
- Attach the format to a stage when files in that stage share one format and COPY commands should inherit it. section 3.1.6
- Specify it in COPY for a load-specific override; COPY takes precedence over stage and table definitions. section 3.1.6
- Avoid defining the same format in every location, since only one definition is needed and precedence determines the effective setting. section 3.1.6
File-format configuration is part of a staged-file ingestion pipeline: COPY loads parsed records into a staging table, after which MERGE and SQL transformations can prepare data for downstream use. Correct parsing is especially important for interpreting file structure, while staged-file metadata can preserve the source filename and source row position when needed. section 2.6 section 2.3
