I have a task on Snowflake that runs a copy command with "on_error = continue" so that if any file in my S3 bucket fails to copy the job will not abort immediately. However, I have also created a "error integration" for this task with AWS / SNS so SNS will send emails whenever an error occurs with this task. The issue here is that by setting "on_error = continue" to the copy command the task will eventually succeed and no emails will be sent, even if one of the files in my S3 fails to copy into the destination table. Is there any workaround for this so that a task doesn't abort immediately when one of the files I am trying to copy fails and am still notified of any errors?
I would recommend you look into either a subsequent task that leverages the VALIDATE() function against the table and the _last query. This function will return all of the error records that were not loaded as part of the original COPY INTO function. You could push this resultset to an email as an external function call...or have the task fire off an EXECUTE ALERT call where the alert condition queries the VALIDATE() and then the action is an email notification integration like the one you're already using.
Some light reading if that doesn't make sense:
https://docs.snowflake.com/en/sql-reference/sql/execute-alert
https://docs.snowflake.com/en/sql-reference/functions/validate
My other advice would be to actually capture the output of the VALIDATE() function into a table. This allows you to fix the records inside of Snowflake and then manually push to your production table, if you wish to handle things that way. Far easier than dealing with the files themselves.