Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Create File Format in Snowflake: Syntax and Example

To create a file format in Snowflake, use the CREATE FILE FORMAT command specifying the format type and options like field delimiter or compression. This defines how Snowflake reads or writes files during data loading or unloading.
📐

Syntax

The CREATE FILE FORMAT command defines a named file format in Snowflake. You specify the format type (like CSV, JSON, or PARQUET) and options such as delimiters, compression, or encoding.

  • file_format_name: Name you choose for the file format.
  • TYPE: File type, e.g., CSV, JSON, PARQUET.
  • FORMAT_OPTIONS: Settings like FIELD_DELIMITER, SKIP_HEADER, COMPRESSION, etc.
sql
CREATE FILE FORMAT file_format_name
  TYPE = 'format_type'
  [FORMAT_OPTION = 'value' ...];
💻

Example

This example creates a CSV file format named my_csv_format with a comma as the field delimiter, skipping the first header row, and using UTF-8 encoding.

sql
CREATE FILE FORMAT my_csv_format
  TYPE = 'CSV'
  FIELD_DELIMITER = ','
  SKIP_HEADER = 1
  ENCODING = 'UTF8';
Output
File format MY_CSV_FORMAT created.
⚠️

Common Pitfalls

Common mistakes include:

  • Not matching the FIELD_DELIMITER to the actual file delimiter, causing load errors.
  • Forgetting to set SKIP_HEADER when files have headers, leading to header rows loading as data.
  • Using incorrect COMPRESSION type if files are compressed.
  • Not specifying TYPE, which is required.
sql
/* Wrong: Missing TYPE */
CREATE FILE FORMAT bad_format
  FIELD_DELIMITER = ',';

/* Correct: Include TYPE */
CREATE FILE FORMAT good_format
  TYPE = 'CSV'
  FIELD_DELIMITER = ',';
📊

Quick Reference

OptionDescriptionExample
TYPEFile type like CSV, JSON, PARQUETTYPE = 'CSV'
FIELD_DELIMITERCharacter separating fields in CSVFIELD_DELIMITER = ','
SKIP_HEADERNumber of header rows to skipSKIP_HEADER = 1
COMPRESSIONCompression type like GZIP, NONECOMPRESSION = 'GZIP'
ENCODINGFile encoding like UTF8ENCODING = 'UTF8'
✅

Key Takeaways

Always specify the TYPE when creating a file format in Snowflake.
Match file format options to your actual file structure to avoid load errors.
Use SKIP_HEADER to ignore header rows in CSV files.
Set COMPRESSION correctly if your files are compressed.
File formats help Snowflake understand how to read or write your data files.