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_DELIMITERto the actual file delimiter, causing load errors. - Forgetting to set
SKIP_HEADERwhen files have headers, leading to header rows loading as data. - Using incorrect
COMPRESSIONtype 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
| Option | Description | Example |
|---|---|---|
| TYPE | File type like CSV, JSON, PARQUET | TYPE = 'CSV' |
| FIELD_DELIMITER | Character separating fields in CSV | FIELD_DELIMITER = ',' |
| SKIP_HEADER | Number of header rows to skip | SKIP_HEADER = 1 |
| COMPRESSION | Compression type like GZIP, NONE | COMPRESSION = 'GZIP' |
| ENCODING | File encoding like UTF8 | ENCODING = '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.
