Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Use File Format in Snowflake: Syntax and Examples

In Snowflake, use CREATE FILE FORMAT to define how files are structured for loading or unloading data. Then reference this file format in commands like COPY INTO to correctly parse or write files.
📐

Syntax

The CREATE FILE FORMAT command defines the file type and parsing options for data files. Key parts include:

  • name: The file format name.
  • type: File type like CSV, JSON, PARQUET.
  • options: Settings like field delimiter, skip header, compression.
sql
CREATE FILE FORMAT <file_format_name>
  TYPE = {CSV | JSON | AVRO | ORC | PARQUET}
  [ COMPRESSION = {AUTO | GZIP | BZ2 | NONE} ]
  [ FIELD_DELIMITER = '<char>' ]
  [ SKIP_HEADER = <number> ]
  [ NULL_IF = ('<string1>', '<string2>', ...) ]
  [ ... other options depending on type ... ];
💻

Example

This example creates a CSV file format with a comma delimiter and skips the first header row. It then uses this format to load data from a stage into a table.

sql
CREATE FILE FORMAT my_csv_format
  TYPE = CSV
  FIELD_DELIMITER = ','
  SKIP_HEADER = 1
  NULL_IF = ('NULL', 'null');

COPY INTO my_table
  FROM @my_stage/data/
  FILE_FORMAT = (FORMAT_NAME = 'my_csv_format');
Output
COPY command executed successfully, data loaded into my_table.
⚠️

Common Pitfalls

Common mistakes when using file formats include:

  • Not matching the FIELD_DELIMITER to the actual file delimiter causes parsing errors.
  • Forgetting to skip header rows leads to loading headers as data.
  • Not specifying NULL_IF can cause string 'NULL' to be loaded as text instead of null.
  • Using wrong compression setting can cause file read failures.
sql
/* Wrong: Missing skip header causes header row to load as data */
CREATE FILE FORMAT bad_format
  TYPE = CSV
  FIELD_DELIMITER = ',';

/* Correct: Skip header row */
CREATE FILE FORMAT good_format
  TYPE = CSV
  FIELD_DELIMITER = ','
  SKIP_HEADER = 1;
📊

Quick Reference

OptionDescriptionExample
TYPEFile type: CSV, JSON, PARQUET, etc.TYPE = CSV
FIELD_DELIMITERCharacter separating fields in CSVFIELD_DELIMITER = ','
SKIP_HEADERNumber of header rows to skipSKIP_HEADER = 1
COMPRESSIONFile compression typeCOMPRESSION = GZIP
NULL_IFStrings to treat as NULLNULL_IF = ('NULL', 'null')
✅

Key Takeaways

Create a file format to tell Snowflake how to read or write files.
Match file format options to your actual file structure to avoid errors.
Use SKIP_HEADER to avoid loading header rows as data.
Specify NULL_IF to convert certain strings to null values.
Reference the file format in COPY INTO or UNLOAD commands for consistent parsing.