CAP Theorem, PACELC and BASE Explained for Data Engineers

The CAP theorem is about databases that keep copies of the same data on several machines. It says that when the network between those machines breaks (a partition), the system must choose: either refuse some requests so that nobody sees out-of-date data (consistency), or keep answering every request and accept that some answers may be out of date (availability). It cannot do both while the partition lasts.

You will meet this trade-off in Kafka settings, cloud databases and data lake storage, and it is a common data engineering interview question.

The three letters

• C, consistency. Every read sees the most recent successful write, as if there were only one copy of the data. This is a stronger and different idea from the “C” in ACID, which means the database’s rules and constraints are respected.
• A, availability. Every request sent to a working machine gets a non-error answer, even if that answer is not the newest value.
• P, partition tolerance. The system keeps operating when messages between machines are lost or delayed.

Why “pick two of three” is misleading

The popular summary says you can choose any two of the three. In practice, networks do fail, so a distributed system cannot opt out of partitions. The real choice is narrower:

When a partition happens, do you give up consistency or availability?

When there is no partition, a well-built system can be both consistent and available. That is why people label systems CP (stays consistent, may refuse requests during a partition) or AP (stays available, may return stale data during a partition). A “CA” system only makes sense on a single machine, where there is no network between copies to break.

See it in code

This small simulation has two copies (replicas) of a stock count. The network between them breaks, a sale arrives at replica A, and then a customer reads from replica B:

# Two replicas of one key-value store. A network partition stops them talking to each other.
class Replica:
    def __init__(self, name):
        self.name, self.data = name, {}

class Cluster:
    def __init__(self, mode):
        self.mode = mode                      # "CP" or "AP"
        self.a, self.b = Replica("A"), Replica("B")
        self.partitioned = False

    def write(self, replica, key, value):
        other = self.b if replica is self.a else self.a
        if self.partitioned and self.mode == "CP":
            return f"{replica.name}: write refused (cannot reach {other.name})"
        replica.data[key] = value
        if not self.partitioned:
            other.data[key] = value          # normal case: copy to the other replica
        return f"{replica.name}: wrote {key}={value}"

    def read(self, replica, key):
        if self.partitioned and self.mode == "CP":
            return f"{replica.name}: read refused (might be stale)"
        return f"{replica.name}: {key}={replica.data.get(key)}"

for mode in ("CP", "AP"):
    c = Cluster(mode)
    print(f"--- {mode} ---")
    print(c.write(c.a, "stock", 5))
    c.partitioned = True
    print(c.write(c.a, "stock", 4))          # a sale arrives at replica A during the partition
    print(c.read(c.b, "stock"))              # a customer reads from replica B

--- CP ---
A: wrote stock=5
A: write refused (cannot reach B)
B: read refused (might be stale)
--- AP ---
A: wrote stock=5
A: wrote stock=4
B: stock=5

• The CP cluster refuses the write and the read, so nobody sees a wrong stock count, but customers get errors until the network is fixed.
• The AP cluster accepts the sale and answers the read, but replica B still says 5 when the true count is 4. When the network heals, the replicas must reconcile, for example by keeping the newest write.

Real systems are smarter than this demo. A CP system usually keeps serving on the side that still has a majority of replicas, and refuses only on the minority side. The choice itself is the same.

Which side is right?

It depends on the cost of each kind of mistake:

• Choose consistency when a stale or conflicting answer is expensive: account balances, stock that must not be oversold, unique usernames, leader election.
• Choose availability when a slightly old answer is fine but an error is not: product reviews, view counters, social feeds, shopping carts that can be merged later.

Many systems let you choose per request or per table, so the answer can differ inside one application.

PACELC: the trade-off when nothing is broken

CAP only talks about partitions, which are rare. Daniel Abadi’s PACELC adds the everyday case:

If there is a Partition, choose Availability or Consistency. Else, choose Latency or Consistency.

Even on a healthy network, keeping every copy in step means waiting for other machines to confirm each write. That costs time. Systems that reply after one copy has the data are faster but may serve stale reads for a short while. In practice, this latency trade-off affects users every day, while the partition case affects them rarely.

BASE vs ACID

ACID describes single-database transactions: atomic, consistent, isolated and durable. The transactions lesson covers each property.

BASE describes many large distributed stores that favour availability:

• Basically available: the system answers, even if some data is old or some features are limited.
• Soft state: the data on each copy can change without new input, as replicas catch up with each other.
• Eventually consistent: if writes stop, all copies will end up with the same value.

	ACID	BASE
Goal	Correctness of every transaction	Availability and scale
Reads	See committed data at the chosen isolation level	May be stale for a short time
Typical home	Relational databases, warehouses	Wide-column and key-value stores, caches
Cost	Coordination, which limits scale and adds latency	Application must handle stale reads and conflicts

Modern systems often mix the two, for example distributed SQL databases with ACID transactions across machines, or key-value stores that offer strongly consistent reads as an option.

Where data engineers meet this

• Kafka. With acks=all and min.insync.replicas=2, a producer gets an error rather than a weaker write when too few replicas are in sync. That chooses consistency over availability for writes. Allowing an out-of-sync replica to become leader (unclean.leader.election.enable=true) does the opposite and can lose data. See Kafka replication and the ISR.
• Key-value and wide-column databases. Amazon DynamoDB reads are eventually consistent by default, with strongly consistent reads available as an option. Apache Cassandra lets each query choose a consistency level, such as ONE or QUORUM.
• Object storage. Amazon S3 has given strong read-after-write consistency since December 2020, so a pipeline that lists files right after writing them sees the new files. Older pipelines often contain workarounds from before that change.
• Table formats. Delta Lake, Iceberg and Hudi add ACID commits on top of object storage with optimistic concurrency: two writers can work at once, and one of them retries if they conflict. See Delta Lake transactions.
• Replicas for reporting. A read replica of the production database usually lags a little behind. Reports built on it are eventually consistent, which matters for “today so far” numbers.

Common mistakes

1. Saying “we chose CA”. A distributed system cannot avoid partitions; it can only choose what to do during one.
2. Mixing up the two C’s. CAP consistency is about copies agreeing. ACID consistency is about rules and constraints.
3. Labelling products as permanently CP or AP. Most behave differently depending on configuration and on the kind of request.
4. Forgetting latency. Most of the time there is no partition, and the real trade-off is speed versus fresh reads (PACELC).

What interviewers look for

• A correct one-sentence statement: during a partition, choose consistency or availability.
• Why “two of three” is misleading, and what CP and AP mean.
• A realistic example of each choice, ideally from a tool you have used, such as Kafka acks settings.
• Bonus: PACELC, and the difference between ACID and BASE.

Practise with the data engineering interview questions.

Key takeaway

The CAP theorem says that when replicas cannot talk to each other, a system must either refuse some requests to stay consistent or answer them and risk stale data. PACELC adds that, even without a partition, stronger consistency costs latency. ACID favours correct transactions; BASE favours availability with eventual consistency. Choose per use case, based on whether a stale answer or an error costs more.