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_DELIMITERto the actual file delimiter causes parsing errors. - Forgetting to skip header rows leads to loading headers as data.
- Not specifying
NULL_IFcan 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
| Option | Description | Example |
|---|---|---|
| TYPE | File type: CSV, JSON, PARQUET, etc. | TYPE = CSV |
| FIELD_DELIMITER | Character separating fields in CSV | FIELD_DELIMITER = ',' |
| SKIP_HEADER | Number of header rows to skip | SKIP_HEADER = 1 |
| COMPRESSION | File compression type | COMPRESSION = GZIP |
| NULL_IF | Strings to treat as NULL | NULL_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.
