Working with file formats

 File Formats :

we can create it using web UI (Options will be changing based of the file type you choose)

It is a named database object that can be used to simplify process of accessing the staged data and streamlines loading data into and unload data out of database tables.

A Snowflake file format encapsulates information of data files, such as file type(CSV, Json, etc..) and formatting options specific to each type used for bulk loading / unloading.

We can create it using SQL or using snowflake web-UI under database objects.

File formats supported by Snowflake : 

1. CSV 
2. JSON 
3. AVRO 
4. ORC
5. PARQUET
6. XML

File formats supported for both loading data and unloading data out of Databases :

1. CSV     
2. JSON    
3. PARQUET

File Formats supported for only loading :

1. AVRO, 
2. ORE 
3. XML

Note : File format options keep changing based on the type of file you work with so reffer documents ion always

Sample file Format with CSV :

create file format MY_CSV_FORMAT
    TYPE = 'CSV' 
    COMPRESSION = 'AUTO' 
    FIELD_DELIMITER = ',' 
    RECORD_DELIMITER = '\n' 
    SKIP_HEADER = 1 
    FIELD_OPTIONALLY_ENCLOSED_BY = '"' 
    TRIM_SPACE = FALSE 
    ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE 
    ESCAPE = 'NONE' 
    ESCAPE_UNENCLOSED_FIELD = '\134';

Sample file Format with json :

CREATE OR REPLACE FILE FORMAT books_json_format 

TYPE = 'json'

STRIP_OUTER_ARRAY = TRUE;