Clarification on ACID Compliance of "Truncate Table" Action in Snowflake Sink for ADF Data

Question

Best regards, [Your Name]Dear Microsoft Support Team,

I am working with Azure Data Factory (ADF) Data Flows and utilizing the Snowflake sink for data loading. In the Settings tab, there is an option to select "Truncate table" under the Table action dropdown. I have concerns about whether this action adheres to ACID compliance principles, particularly regarding the gap between truncating the table and inserting new data.

Based on my understanding, the "Truncate table" action involves two discrete steps:

1. Truncating the table, which removes all existing rows.
2. Inserting new data into the table from the data flow.

My specific question is:

• Is there a moment between these two steps where the target table is completely empty and queries on the table would return no data?
• If so, can you confirm whether this process is ACID-compliant?

Additionally, I would like to know if there are any recommendations or best practices for ensuring data consistency and availability when using this action.

Thank you for your assistance. I look forward to your clarification on this matter.

Best regards,
Stefan Kudras

Answer 1

Hi @Kudras, Stefan
Welcome to Microsoft Q&A platform and thanks for posting your query here.

Regarding your concern about using the "Truncate table" option in the Snowflake sink with Azure Data Factory Data Flows, here’s some clarification:

The "Truncate table" action works in two steps:

1. It deletes all rows in the table (making it empty).
2. It then inserts the new data.

This means there is a brief moment when the table is empty before the new data is added. So, if another query tries to access the table during this gap, it may see an empty table.

As for ACID compliance, while ADF Data Flows strive to maintain data consistency, "Truncate table" operations do not offer the same transactional guarantees as other ACID-compliant operations (e.g., ensuring no data loss or partial updates during failures).

To ensure data consistency and minimize any potential gaps:

• You can consider using a staging table to load data first, then swap or update the main table after validation.
• Alternatively, transactional controls available in Snowflake or batch strategies could further help maintain data availability.

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