ACID

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

Let's decode this....

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.

Image: https://www.bmc.com/blogs/acid-atomic-consistent-isolated-durable/

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.

Cheers.