Snowflake Export

In the Snowflake Export, we have an option to execute SQL. It provides the feature to Execute post-delivery SQL for data modification or manipulation.

Clicking on the checkbox “Execute SQL after file delivery to stage” enables the Scripting box to either execute the “Default” SQL or update the SQL scripting to write your own or add the script to modify the content.

Uncheck the “Use Default SQL” checkbox, as shown above to Edit the SQL Query. Also, you can pass the Warehouse but it is optional.

The following example Shows the Query to update the content after the file is delivered on the Table:

CODE

CREATE OR REPLACE TABLE "${DATABASE}"."${SCHEMA}"."${EXPORT_NAME}" (${EXPORT_COLUMNS});

COPY INTO "${DATABASE}"."${SCHEMA}"."${EXPORT_NAME}" from @"${DATABASE}"."${SCHEMA}"."${STAGE}" FILE_FORMAT = (type = 'CSV', FIELD_DELIMITER = ',' FIELD_OPTIONALLY_ENCLOSED_BY = '"' SKIP_HEADER = 1 NULL_IF = ('NULL', 'null', '')) PURGE = true FORCE = true TRUNCATECOLUMNS = false ON_ERROR = ABORT_STATEMENT FILES = ('${EXPORT_FILE_NAME}.csv.gz');

UPDATE "${DATABASE}"."${SCHEMA}"."${EXPORT_NAME}"

SET PRODUCT_TYPE = "TV"

WHERE DATE>=2024/07/20;

The above example updates the table and sets the columns “Product_Type” to “TV” where the condition is that the date columns in the table must be equal to or greater than “2024/07/20”. This script will be executed at the time of Data export.