What is ACID property in a database? For data analytics tasks, do you need to care about ACID properties ?

ACID properties are important in an RDBMS setting where operations are transnational and there are database updates involved as a part of the task. For instance a banking or an e-commerce application where real-time user data is updated typically needs an RDBMS.

A data analyst typically handles structured data using query languages such as SQL. However, typical analytics tasks do not involve database updates, and even if they do, their requirements on consistency are much weaker. This is because Analytics might involve wrangling data and giving insights without really updating sensitive data. One can even use no-sql data sources for batch analysis of big data – such no-sql databases often do not follow all of ACID properties.

In face, it is often recommended to keep the analytics database different from the primary database that supports transactions coming from the application. This ensures (1) The latency requirements are met for the primary database without having to allocate bandwidth to the analytics queries (2) The analytics queries can use a different data base engine that allows for more efficient handling of big data, even if it does not support ACID properties.

ACID properties in the context of an RDBMS are  (https://en.wikipedia.org/wiki/ACID) :

ATOMICITY: The “All or None” property : Transactions in a database often comprise of multiple sentences. When a transaction is committed to the database, atomicity refers to the property where either all statements will be executed or none will.

CONSISTENCY: Database Consistency states that only valid data will be written to the database. If a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to its original state.

ISOLATION: Ensures that concurrent execution of transactions has the same result as executing them serially.

DURABILITY: Once a transaction is committed, it has been written to permanent storage and remains committed.

Leave a Reply

Your email address will not be published. Required fields are marked *