How to define and create a custom file format for snowflake destination?

Product: Streamsets Datacollector

Question:

As per Snowflake documentation to create file format

https://docs.snowflake.com/en/user-guide/data-unload-considerations.html 
https://docs.snowflake.com/en/sql-reference/sql/create-file-format.html 

We  have a setting called EMPTY_FIELD_AS_NULL = TRUE | FALSE

Today (While writing this article the version of SDC was 3.18.1) when SDC generates a CREATE FILE FORMAT statement it looks something like

create file format:

"STREAMSETS_DB"."RAJAN"."SDC_TEMP_464B46EBF411467A821C2DCC6FEACA19" type = CSV FIELD_DELIMITER=',' TRIM_SPACE = true FIELD_OPTIONALLY_ENCLOSED_BY = '\'' ESCAPE = 'NONE' ESCAPE_UNENCLOSED_FIELD = 'NONE';

We will need an option to add EMPTY_FIELD_AS_NULL=FALSE in the UI that will specifically handle 0 length character.

Answer:

First create a file format in snowflake for ex :

create file format "STREAMSETS_DB"."RAJAN"."MY_CUSTOM_FORMAT" type = CSV FIELD_DELIMITER=',' TRIM_SPACE = true FIELD_OPTIONALLY_ENCLOSED_BY = '\'' ESCAPE = 'NONE' ESCAPE_UNENCLOSED_FIELD = 'NONE' EMPTY_FIELD_AS_NULL = FALSE ;

1    File format MY_CUSTOM_FORMAT successfully created.

Step 2 

Define Data Advanced -> Snowflake File Format = MY_CUSTOM_FORMAT as shown in screenshots attached in the snowflake stage in pipeline

This will help you pick the custom file format and it will not run the create file format command.

Screenshots and sample pipeline attached.