ADF Script Activity: During the last ADF post we have discussed about COPY activity to ingest data into snowflake. With COPY Activity we have limited control in terms of loading data from external stage to Snowflake. Also there are key Snowflake COPY parameters and we cannot leverage them during ADF COPY Activity. Consider the below file Format, where we have Extra Comma in CUST_STAT column.
During the data load via ADF COPY Activity it lead to the loss of data in few columns.
Though we have field_optionally_enclosed_by='”‘ parameter tied with FILE FORMAT in snowflake but did not find option to define FILE FORMAT in COPY Activity.
Script activity in ADF provides provides great capability to run multiple SQL commands against Snowflake. We can use the Script activity to execute DML (Data Manipulation Language) statements like SELECT, INSERT, UPDATE, as well as DDL (Data Definition Language) statements like CREATE, ALTER, and DROP operations. This gives users the flexibility to transform data they have loaded into Snowflake while pushing all the compute into Snowflake.
We have created the COPY activity in with below parameters.
So now we have created the SCRIPT Activity and have full control over the SQL commands. These statements runs against the Snowflake and we are leveraging STAGE,FILEFORMAT,COPY Activity of Snowflake Database.
So we have COPY and SCRIPT Activity in our ADF Pipeline.
COPY Activity Output:
Script Activity Output: