Partial Inserts in ADF Copy Activity and Stored Procedure — Is it Possible?

Question

Partial Inserts in ADF Copy Activity and Stored Procedure — Is it Possible?

YERNAIDU SIRAPARAPU 85

Hi community,

I’m working on a data pipeline in Azure Data Factory (ADF) where:

I copy data from ADLS (Azure Data Lake Storage) into an Azure SQL RAW layer table using a Copy Activity.

After that, I use a Stored Procedure Activity to move data from the RAW table to the Cleansed (CLN) table.

My Question:

Is it possible for partial inserts to happen in the following two failure scenarios?

🔹 Scenario 1: Copy Activity Fails Midway

Suppose I’m copying a large file (e.g., 10,000 records) from ADLS to Azure SQL using Copy Activity.

If the Copy Activity fails after inserting 4,000 rows, will those rows remain in the RAW table?

Or is the entire operation rolled back automatically?

🔹 Scenario 2: Stored Procedure Fails Midway

Assume my stored procedure does the following:

Reads from the RAW table

Performs transformations and inserts into the CLN table

Crashes midway due to a data quality issue (e.g., NULL in a NOT NULL column)

In this case:

Will the partially inserted rows in the CLN table stay?

Or will the entire stored procedure be rolled back?

I want to ensure data consistency and atomicity, so:

What are the best practices to prevent partial inserts?
Should I use explicit transactions in stored procedures?
Is there a way to make Copy Activity transactional or rollback on failure?
Are there retry strategies or error-handling patterns for these two stages?

Venkat Reddy Navari 5,330 Reputation points Microsoft External Staff Moderator

2025-08-08T14:19:27.75+00:00

YERNAIDU SIRAPARAPU Just checking in to see if the below answer provided by @Amira Bedhiafi helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Accepted answer

1 additional answer

Your answer

Venkat Reddy Navari 5,330 Reputation points Microsoft External Staff Moderator

2025-08-08T14:19:27.75+00:00

YERNAIDU SIRAPARAPU Just checking in to see if the below answer provided by @Amira Bedhiafi helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Hello Yernaidu !

Thank you for posting on Microsoft Learn.

In your case, partial rows can remain in the RAW table if the Copy Activity fails midway.

ADF Copy Activity is not transactional and it does not rollback partial inserts automatically if a failure occurs. That means if 4000 out of 10000 records are copied before failure, those 4000 will remain in the target table (RAW).

You can copy into a staging table (RAW_Staging) and once the copy completes successfully, validate row count/hash/.... Then, use a Stored Procedure with a transaction to move to RAW.

You can avoid reprocessing incomplete loads by marking successful files with a .complete or .success flag in ADLS and add assertions or row count checks post-copy before moving to next activity.

Will partial rows remain in the CLN table?

I can say that it depends on how your stored procedure is written.

By default, if you don’t wrap your T-SQL logic in a transaction, partial inserts can remain.

For example if the procedure inserts 5000 rows and crashes at row 5001, those 5000 remain unless a transaction was explicitly used.

As a best practice, you can use explicit transactions in your stored procedure:

BEGIN TRY
    BEGIN TRANSACTION;
    INSERT INTO CLN (...)
    SELECT ... FROM RAW;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;
END CATCH

and you can validate data quality before insert by using a staging table or CTEs to filter or quarantine bad rows and doing integrity checks in a separate step before insert.

For retry and error handling in ADF keep in mind that you can :

ADF copy activity: you can configure Retry policy in activity settings (for example 3 retries with interval) and set Fault tolerance (skip incompatible rows, log to bad records table).
Stored procedure activity: you can wrap logic in TRY/CATCH with proper rollback or use Lookup + If Condition to skip reprocessing if data already exists or failed previously.

As a global pattern, you can implement a load tracking table with metadata (like file name, row count, load status) and you only mark files as processed after full success.

Answer 2

Copy activity is not transaction bound and it copies data in batches.

So in case if a copy activity fails, then the rows that were copied into the destination would continue to remain in the sink and not revert back.

Depending whether the stored procedure has transaction enabled within it like Begin Transaction and commit Transaction,

if transactions are present, in case of failure your job would be reverted back to the original state and if no transaction, partial data would be inserted/updated etc.

What are the best practices to prevent partial inserts? Have stage table for copy activity as sink and use stored procedure with transactions to load from stage to final table
Should I use explicit transactions in stored procedures? Yes
Is there a way to make Copy Activity transactional or rollback on failure? Nope
Are there retry strategies or error-handling patterns for these two stages? For copy activity, have a pre script to trunacte the staging table and then reload

Share via

Partial Inserts in ADF Copy Activity and Stored Procedure — Is it Possible?

1 additional answer

Your answer