A set of properties of database transactions intended to guarantee data validity despite errors, power failures, and other mishaps.
ACID (Atomicity, Consistency, Isolation, Durability) is why you can trust that when the payment processor says "success," your bank account actually got debited. It's the contract that made relational databases the foundation of financial software for 40 years, and — this is the surprising part — it's now a feature that data-lake table formats (Apache Iceberg, Delta Lake, Apache Hudi) bring to S3/GCS/ADLS so you can finally have warehouse-grade guarantees on plain Parquet files.
Older data lakes (Hadoop + Hive + Parquet on S3) had no ACID. If two Spark jobs wrote to the same partition concurrently, you got corrupt data with no error. If a job failed halfway through a multi-file write, you got half-written partitions. Teams worked around this with elaborate coordination, staging directories, and manual cleanup — death by a thousand cuts. Iceberg/Delta/Hudi fix this with snapshot isolation, atomic commits, and write-ahead-log semantics on object storage. This is not a nice-to-have; it's what makes modern lakehouse architectures actually safe.
A retailer I worked with had a Spark job writing to S3 + Parquet. Once a month, a concurrent ingest and a batch refresh would collide — some customers saw duplicate orders in the dashboard for a few hours until the next full-refresh cleaned up. The bug was invisible in code review (both jobs were "correct" in isolation) and irreproducible locally. Migrating the same tables to Iceberg (snapshot isolation) eliminated the bug overnight. The job code barely changed; the storage format did all the work. Also, the previously scary "backfill production data" operation became safe — Iceberg snapshots let us test a backfill on a branch, validate it, and merge with a single atomic commit. ACID on the lake is not a luxury; it's table stakes for any workload with concurrent writers.
ACID stands for Atomicity, Consistency, Isolation, Durability — four properties that guarantee database transactions are processed reliably. ACID compliance is what makes relational databases trustworthy for financial systems, and it is now a key feature that table formats (Delta Lake, Iceberg) bring to data lakes.
-- Transfer $100 from Account A to Account B
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;
-- If either UPDATE fails, BOTH are rolled backIsolation Levels (from weakest to strongest):
| Level | Dirty Reads | Non-Repeatable Reads | Phantom Reads |
|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Possible |
| Serializable | Prevented | Prevented | Prevented |
| Property | ACID | BASE |
|---|---|---|
| Full Name | Atomicity, Consistency, Isolation, Durability | Basically Available, Soft state, Eventual consistency |
| Consistency | Immediate (strong) | Eventual (weak) |
| Use Case | Financial, transactional | High-throughput, distributed |
| Databases | PostgreSQL, MySQL, Snowflake | Cassandra, DynamoDB, MongoDB |
| Tradeoff | Higher latency, stronger guarantees | Lower latency, weaker guarantees |
ACID is no longer just for OLTP databases. Modern data engineering relies on ACID guarantees:
ACID pitfalls that surprise practitioners:
SELECT balance FROM accounts WHERE id = 1; UPDATE accounts SET balance = WHERE id = 1; — two concurrent clients can interleave and lose updates. Use SELECT ... FOR UPDATE, atomic UPDATE expressions (balance = balance - 100), or Serializable isolation. This is the most common data-integrity bug I see.synchronous_commit=off trades durability for throughput — acceptable for analytics, dangerous for transactional data. Cloud replication (multi-AZ, streaming) doesn't help if the unacknowledged transaction was lost before being replicated.ACID is a set of four properties — Atomicity, Consistency, Isolation, Durability — that define reliable database transactions. Atomicity: all-or-nothing, partial failures are rolled back. Consistency: transactions move the database between valid states, enforcing constraints. Isolation: concurrent transactions don't interfere with each other. Durability: committed data survives crashes. Together they make relational databases (PostgreSQL, MySQL, Snowflake) trustworthy for financial systems, order processing, and any use case where wrong data is worse than slow data. Modern data-lake table formats (Iceberg, Delta Lake) bring ACID to object-storage-based lakehouses.
ACID (Atomicity, Consistency, Isolation, Durability) prioritizes strong consistency and transactional correctness; used by relational databases and modern lakehouse table formats. BASE (Basically Available, Soft state, Eventual consistency) prioritizes availability and throughput over strong consistency; used by distributed NoSQL systems (Cassandra, DynamoDB in default config, Riak). BASE accepts temporarily inconsistent state in exchange for always-available writes. Real systems are rarely pure ACID or pure BASE — Postgres logical replication is eventually consistent; DynamoDB supports strong-consistency reads; Kafka is configurable. Treat it as a spectrum, not a binary.
Without ACID, concurrent writes to the same data lake table can corrupt data. Two Spark jobs writing to the same S3 partition produce unpredictable results — no error, just silently wrong data. Half-failed writes leave behind orphan files. Concurrent readers can see mid-commit state. Table formats — Apache Iceberg, Delta Lake, Apache Hudi — fix this by layering a metadata file (manifest/log) over Parquet files and making commits atomic via the catalog. Snapshot isolation means readers see a consistent point-in-time view even during writes. This is what makes modern lakehouse architectures safe for mixed-workload production use.
Isolation levels control how much concurrent transactions see each other's in-flight changes, from weakest to strongest: Read Uncommitted (dirty reads possible — rarely used), Read Committed (the common default in PostgreSQL and Snowflake), Repeatable Read (MySQL default; prevents non-repeatable reads), Serializable (strongest, but highest contention cost). Snowflake queries use snapshot isolation automatically. Higher isolation prevents more concurrency anomalies (dirty reads, non-repeatable reads, phantom reads) but costs more in lock contention and retries. Pick based on workload: analytical queries need little; transactional writes may need Serializable or explicit locking.
Yes — Snowflake is fully ACID compliant. DML statements (INSERT, UPDATE, DELETE, MERGE) are atomic at the statement level. Multi-statement transactions via BEGIN/COMMIT are supported but note that auto-commit is on by default — each statement commits immediately unless wrapped in an explicit transaction. Snowflake uses snapshot isolation: queries within a transaction see a consistent point-in-time view. Micro-partitions plus metadata commit ordering provide durability. Cross-account or cross-database data sharing does not weaken ACID within an account.
Yes, when used correctly. Both table formats provide atomic commits (via a metadata log), snapshot isolation for reads, and durability inherited from the underlying object store (S3, GCS, ADLS). The key caveat is the catalog — Iceberg's atomic commits require an atomic catalog operation (Nessie, REST, Glue, Hive Metastore). File-system-based catalogs on S3 historically had edge cases with eventual consistency, though S3 strong read-after-write consistency since 2020 largely closed that gap. Read the specific catalog's atomicity docs — the table format provides the framework, but the catalog enforces the critical atomic-commit operation.
Symptoms: duplicate records where uniqueness was assumed, missing updates (classic lost-update anomaly from interleaved reads/writes), balance totals that don't match ledgers, "phantom" rows appearing mid-query, dashboards showing inconsistent subtotals that don't add up to totals. Root causes: using the wrong isolation level, application-level read-modify-write without locking, concurrent writes to lake tables without ACID table format, using `synchronous_commit=off` or similar durability-weakening settings on critical data, or a bug in the application misunderstanding the transactional boundary. Fix by strengthening isolation, adopting ACID table formats (Iceberg/Delta), or refactoring logic to use atomic operations (MERGE, compare-and-swap, SELECT FOR UPDATE).