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

Rakesh Roshan Panigrahi 1 Reputation point
2023-12-06T10:34:00.9966667+00:00

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?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,160 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Bhargava-MSFT 28,951 Reputation points Microsoft Employee
    2023-12-06T19:10:59.3+00:00

    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.