Copy activity fails to log Incompatible Rows (Fault Tolerance) for sink Snowflake

Question

Copy activity fails to log Incompatible Rows (Fault Tolerance) for sink Snowflake -->

I am using ADF Copy Activity to copy data from Azure SQL Server to Snowflake. I have enabled the Fault Tolerance feature to log the incompatible rows in a Blob CSV file. Unfortunately, I am not able to see all the incompatible rows that were rejected during the Copy activity (COPY INTO command). In the Blob file, I can see only the first_error value for a single row per datafile, not all the errored-out rows.

Note: On the Sink (Snowflake) side, I am passing additional Snowflake Copy option: ON_ERROR = 'CONTINUE'

Question: Is there a way I can run this SQL right after the Copy Command ends so that it will show all the rejected rows?

SQL: select * from table(validate(customer4 , job_id => '_last'));

Question: Can I run the Copy command using VALIDATION_MODE=RETURN_ALL_ERRORS, so that it will display all the Validation errors prior to the Copy?

Answer 1

Hello Rakesh Roshan Panigrahi,

Welcome to the Microsoft Q&A forum.

Did you specify the container name in the folder path? if you do not provide a path, the service creates a container and the log file at this path: https://[your-blob-account].blob.core.windows.net/[path-if-configured]/[copy-activity-run-id]/[auto-generated-GUID].csv.

The log files can only be the csv files. The original data being skipped will be logged with comma as column delimiter if needed.

https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-fault-tolerance#monitor-skipped-rows-1

for the first question:
ADF doesn't provide a direct way to run a SQL query immediately after the Copy Command. However, you can create a separate activity in your pipeline to execute the SQL query after the Copy Activity is completed.

for the second question:

from the snowflake documentation, Snowflake does not directly support a VALIDATION_MODE=RETURN_ALL_ERRORS option for the COPY INTO command.

You might need to rely on the SELECT * FROM TABLE(VALIDATE(...)) approach after the copy activity to identify rejected rows

from the snowflake document:

VALIDATION_MODE does not support COPY statements that transform data during a load. If the parameter is specified, the COPY statement returns an error.

Use the VALIDATE table function to view all errors encountered during a previous load. Note that this function also does not support COPY statements that transform data during a load.

https://community.snowflake.com/s/question/0D5Do00000LlUBaKAN/how-to-capture-all-errors-from-a-failed-copy-into-statement-using-transformation-in-the-querybelow-query-trying-to-get-the-result-but-getting-sql-compilation-error-validationmode-does-not-support-copy-with-transform

https://docs.snowflake.com/en/sql-reference/sql/copy-into-table

I hope this helps. Please let me know if you have any further questions.