ACID

Normally, ACID and Insert/Update/Delete are mixed, but they have different functions and at the same time work together.

There are 4 traits of ACID i.e., atomicity, consistency, isolation, and durability.

• Atomicity: It makes sure data is completely written in a destination with no partial data left behind.
• Consistency: It makes sure that once an application performs an operation, the results of that operation are visible to every next query. For example, sometimes a user is reading a particular data and at the same time it might get updated or even may get deleted. This should not impact the user query consistency.
• Isolation: An incomplete operation by one user does not cause unexpected side effects for other users.
• Durability: Once an operation is completed, it will be preserved even in the event of the machine or system failure. For example, in databases there is a concept of Transaction Log which holds every query statement run in the database so in case data is lost, it can still be reproducible.

Now, what is the relation between ACID and Insert/Update/Delete?

We all know what Insert/Update/Delete functions are for, so we won't go into that. ACID makes sure when these Insert/Update/Delete queries are happening, nothing should impact operational behavior.

In the past, Data Lake or Hadoop didn't have the ACID capability or Insert/Update/Delete functions but now these are there. There are different ways to achieve these, like.

• Use ORC or Parquet file formats in Hadoop via Hive
• Databricks use Delta Lake for these capabilities using ONLY Object Storage and ONLY Parquet file format.
• Snowflake uses its Computer Layer for these capabilities using Object Storage or Hadoop.

In nutshell, Insert/Update/Delete functions are not reliable without ACID capability.