ACID Properties in Databases Explained

In 1983, two German researchers, Theo Harder and Andreas Reuter, published Principles of Transaction-Oriented Database Recovery in ACM Computing Surveys. That paper coined the acronym ACID. Harder and Reuter were describing what IBM System R and its contemporaries already did internally; their contribution was giving the guarantees names that engineers could argue about. Forty-three years later, the same four letters show up in every data engineering interview.

ACID Properties FAQ

What are ACID properties in a database?+

ACID stands for Atomicity, Consistency, Isolation, and Durability. These four properties guarantee that database transactions are processed reliably. Atomicity ensures all-or-nothing execution. Consistency ensures the database moves between valid states. Isolation ensures concurrent transactions do not interfere. Durability ensures committed data survives system failures. Together, they form the foundation of reliable data storage in relational databases like PostgreSQL, MySQL, and SQL Server.

Does Snowflake support ACID transactions?+

Yes. Snowflake supports ACID transactions. Each SQL statement runs within an implicit transaction, and you can use explicit BEGIN/COMMIT for multi-statement transactions. Snowflake uses multi-version concurrency control (MVCC) for isolation, meaning readers never block writers. However, Snowflake does not enforce foreign key constraints, so the Consistency property is partially the pipeline's responsibility. Atomicity, Isolation, and Durability are fully supported by the engine.

What is the difference between ACID and BASE?+

ACID prioritizes correctness and strong consistency. Every read returns the latest committed value. BASE (Basically Available, Soft state, Eventual consistency) prioritizes availability and partition tolerance. Reads may temporarily return stale data. ACID systems include PostgreSQL and Snowflake. BASE systems include DynamoDB and Cassandra. Most data engineering architectures use both: BASE systems for high-throughput ingestion and ACID systems for the analytics warehouse where correctness matters.

Why do data engineers need to understand ACID properties?+

Data engineers build pipelines that read from and write to databases. Understanding ACID properties helps you design pipelines that handle failures correctly (atomicity), validate data integrity (consistency), avoid race conditions with concurrent writes (isolation), and trust that committed data is safe (durability). Pipeline failures are inevitable. ACID understanding is what separates pipelines that recover gracefully from pipelines that produce corrupted data.

02 / Why practice

Four Letters That Outlasted the Mainframe

01
Active recall beats re-reading by 50%
Cognitive-science meta-reviews (Dunlosky et al., 2013) rank practice testing as a top-tier study technique, while re-reading and highlighting rank near the bottom
02
76% of hiring managers reject on the coding task, not the resume
From HackerRank's 2024 Developer Skills Report. Candidates who look strong on paper still fail the live screen if they haven't done timed, executable practice
03
Five problem shapes cover 80% of data engineer loops
Dedup, sessionization, top-N-per-group, slowly-changing dimensions, partition tricks. Writing the shapes by hand turns the unfamiliar into pattern recognition

Open the problems

Related Guides

Data Warehouse Design→

Architecture patterns for analytics warehouses

SQL UPSERT→

MERGE, INSERT ON CONFLICT, and idempotent write patterns

System Design Interview→

End-to-end data system design with trade-off analysis