7. Data Architecture and Distributed Transactions

Introduction and Context

In a monolithic application, a single database often provides a convenient source of truth. Business operations can be executed within one local transaction, foreign-key relationships can be enforced directly, and reporting can use a unified schema. Microservices change this arrangement. Each service is expected to own a bounded business capability, evolve independently, and remain operationally autonomous. Once data is divided among services, however, consistency, querying, transaction management, and governance become architectural problems rather than merely database configuration tasks.

Data architecture in a microservices environment concerns more than selecting a database engine. It defines ownership, authority, replication, consistency, integration, recovery, and the way business facts move through the organization. Distributed transactions arise when a business process changes data held by more than one service. Examples include placing an order, reserving inventory, charging a payment method, and arranging shipment. These actions cannot always be committed or rolled back through one local database transaction.

This chapter examines database-per-service, polyglot persistence, CQRS, event sourcing, transactional outbox, sagas, and change-data capture. It also develops the reasoning needed to select among strong and eventual consistency, understand CAP and PACELC, choose isolation and replication strategies, and establish clear data ownership.

Learning Objectives

By the end of this chapter, the learner should be able to:

1. Explain why data ownership and database-per-service are central to microservices architecture.

2. Compare relational, document, key-value, graph, search, and time-series persistence according to workload and consistency requirements.

3. Distinguish CRUD, CQRS, event sourcing, transactional outbox, and change-data capture.

4. Analyse CAP, PACELC, isolation levels, replication, and eventual consistency in practical designs.

5. Design saga-based workflows and select orchestration or choreography appropriately.

6. Identify failure modes, operational risks, governance obligations, and security concerns in distributed data systems.

7. Apply these ideas to a realistic business scenario and justify architectural decisions.

From Shared Databases to Explicit Data Ownership

The database-per-service principle states that a service should own the data required for its business capability and should expose that data through an API or event contract rather than permitting other services to access its tables directly. Physical deployment may involve separate database servers, separate schemas, or logically isolated databases. The essential property is ownership: one service controls the meaning, write rules, schema evolution, and lifecycle of its authoritative data.

A shared database initially appears efficient. It avoids duplication and makes joins and transactions simple. Over time, it creates hidden coupling. A change to a table can break several services; one team may bypass another service's business rules; deployment schedules become coordinated; and database performance becomes a shared bottleneck. A service that owns no data is often not genuinely autonomous.

Database-per-service does not mean that every service must use a different database product. Several services may use PostgreSQL while maintaining independent schemas and access credentials. Nor does ownership prohibit data duplication. A Shipping service may retain a copy of a delivery address received from Order Management, but that copy is a read model, not authority over the customer's canonical profile.

Data ownership should be established at the domain level. The Customer service may own customer identity and contact preferences; the Catalog service may own product descriptions and prices; Inventory may own stock positions; and Payment may own payment-state information. Ownership must include responsibility for data quality, retention, availability, access control, and contractual change management.

Persistence Choices and Polyglot Design

Polyglot persistence means selecting storage technologies according to the access patterns and consistency needs of each capability rather than imposing one database technology everywhere. Relational databases remain valuable where transactions, constraints, joins, and mature query languages matter. Document databases suit aggregates that are read and updated as a unit and whose structure changes relatively frequently. Key-value stores are useful for low-latency lookup, sessions, counters, and cache-like workloads. Graph databases can represent highly connected relationships, while search engines support text analysis and relevance ranking. Time-series systems are optimized for ordered measurements such as telemetry.

The choice should begin with workload questions: What are the primary reads and writes? What is the aggregate boundary? How much duplication is acceptable? What consistency is required? How large is the dataset? What are latency and availability targets? How will backups, upgrades, monitoring, and compliance be managed?

Polyglot persistence is not an invitation to collect technologies. Every additional engine introduces operational expertise, backup procedures, failure modes, drivers, security controls, and observability requirements. A relational database with carefully designed indexes may be a better choice than a specialized product whose operational cost cannot be justified.

A useful distinction is between the system of record and derived stores. The system of record is authoritative for a particular business fact. A derived store—such as a search index, cache, reporting warehouse, or materialized view—can be rebuilt from authoritative data or events. Treating a derived index as authoritative creates difficult repair and reconciliation problems.

Consistency, Availability, and the Distributed Systems Trade-off

Consistency has several meanings. In a distributed data system, linearizability means that operations appear to occur atomically in a single real-time order. Sequential consistency preserves a valid order but not necessarily real-time ordering. Eventual consistency means that, if updates stop and communication continues, replicas will converge. A system can be eventually consistent while still offering stronger guarantees for selected operations, such as conditional writes or single-aggregate transactions.

The CAP theorem concerns a distributed system experiencing a network partition. It states that a system cannot simultaneously guarantee linearizable consistency and availability for all requests during that partition. A system designed for consistency may reject or delay requests until it can establish a safe ordering. A system designed for availability may accept operations on divergent replicas and reconcile them later. CAP is not a choice made once for an entire company; different data and operations may adopt different policies.

PACELC extends the discussion: if there is a partition, a system chooses between availability and consistency; else, during normal operation, it chooses between latency and consistency. Synchronous replication may provide stronger consistency but increases response time and makes remote failures visible to users. Asynchronous replication reduces latency and improves geographic availability but introduces replication lag and stale reads.

The correct question is therefore not “Is the system consistent?” but “Which consistency guarantee is required for this fact, operation, and time interval?” A product catalogue may tolerate a brief stale price display, whereas payment authorization and inventory reservation may require stronger coordination. Even in the latter case, global serializability may be unnecessary if a single service can own the relevant invariant.

Isolation and Replication

Isolation describes how concurrent transactions appear to interact. Read uncommitted permits dirty reads and is rarely appropriate for business-critical data. Read committed prevents reading uncommitted changes but can produce non-repeatable reads. Repeatable read generally protects rows read by a transaction, although exact behavior differs among database systems. Serializable provides the strongest conventional isolation by making concurrent execution equivalent to some serial order, but it can reduce throughput and increase contention. Snapshot or multi-version isolation gives transactions a consistent view without always blocking readers, but write conflicts still require handling.

Isolation should be selected by invariant rather than habit. If two users must not claim the same scarce resource, a design might use a conditional update, a unique constraint, pessimistic locking, serializable isolation, or an explicit reservation service. Isolation at one database cannot automatically protect data in another database.

Replication may be synchronous or asynchronous, leader-based or multi-leader, and local or geographically distributed. Read replicas improve read capacity but may return stale data. A common operational error is to write to a primary and immediately read from a lagging replica, causing a user to believe that an update disappeared. Read-your-writes guarantees, session affinity, bounded-staleness policies, or routing critical reads to the leader can mitigate this problem.

Replication also creates conflict and recovery questions. Multi-primary systems must resolve concurrent writes, while failover can create split-brain conditions if leadership is not controlled. Backups are not a substitute for replication: replication can copy corruption or accidental deletion, whereas point-in-time recovery can restore an earlier valid state.

CQRS: Separating the Write and Read Models

Command Query Responsibility Segregation (CQRS) separates operations that change state from operations that retrieve state. The command model enforces business rules and records accepted changes. The query model is optimized for consumption and may contain denormalized data, indexes, or representations tailored to particular clients.

CQRS does not require separate services, databases, or event sourcing. A simple implementation may use two application models over one database. A more distributed implementation publishes domain events from the command side and builds one or more read models asynchronously. This permits efficient queries without forcing the write schema to serve every reporting need.

The principal cost is temporal inconsistency. After a command succeeds, the corresponding query model may not yet contain the change. User interfaces should communicate such states honestly, for example by showing “processing” or reading from the command-side source when immediate confirmation is essential. Rebuilding a read model, handling duplicate events, ordering updates, and monitoring lag are necessary engineering responsibilities.

Event Sourcing and the Meaning of Events

Event sourcing stores a sequence of business events as the primary record of an aggregate's state. Instead of storing only the current balance, an Account aggregate might store AccountOpened, MoneyDeposited, and MoneyWithdrawn events. Current state is reconstructed by replaying events, optionally from a snapshot.

Event sourcing can provide a strong audit history, temporal reconstruction, and the ability to create new projections. It is particularly useful where the sequence of decisions is itself important, such as financial ledgers or workflow histories. Events should represent facts that the business recognizes, not merely low-level database updates. They are normally immutable; correcting a mistake involves recording a compensating event rather than editing history.

The pattern has significant costs. Event schemas must evolve compatibly; replay can be expensive; privacy deletion is difficult when personal data is embedded in immutable events; and developers must understand aggregate versioning, idempotency, and projection repair. Event sourcing is not automatically appropriate for every CRUD entity. A conventional current-state model may be simpler when historical reconstruction has little value.

Transactional Outbox and Reliable Publication

A service often needs to update its database and publish an event. If it commits the database first and crashes before publication, other services never learn of the change. If it publishes first and the database transaction fails, consumers receive a fact that is not true. The transactional outbox pattern addresses this by writing the business change and an outbox record in the same local database transaction.

A separate publisher reads unsent outbox records and delivers them to a broker. After successful delivery it marks the record as published. Delivery is commonly at least once, so consumers must be idempotent. A stable event identifier, unique constraint, inbox table, or processed-event record can prevent duplicate effects. The outbox publisher must also support retries, backoff, poison-message handling, monitoring, and eventual cleanup.

The pattern provides atomicity between local state and the intention to publish, not a global exactly-once guarantee. Consumers may receive duplicates, events may be delayed, and a broker acknowledgement may be lost after delivery. Business operations should therefore tolerate retry and be designed around explicit idempotency keys.

Sagas and Long-Running Business Transactions

A saga coordinates a business process through a sequence of local transactions. Each step commits independently and publishes a message or event that triggers the next step. If a later step fails, compensating actions are performed. A reservation may be released, a payment may be refunded, or a shipment request may be cancelled.

In choreography, services react to events without a central coordinator. This can be simple for short workflows, but the process may become difficult to understand as event dependencies multiply. In orchestration, a saga coordinator directs participants and tracks state, timeouts, retries, and compensation. Orchestration improves visibility but introduces a coordinator that must be highly available and carefully designed.

Compensation is not a technical rollback. A payment refund may take time or may fail; an email cannot truly be unsent; an item already shipped may require a return. Sagas therefore require explicit business states such as Pending, Confirmed, Cancelled, and CompensationRequired. They also need correlation identifiers, deadlines, duplicate handling, observability, and a policy for manual intervention.

A saga is appropriate when a process spans service boundaries and temporary inconsistency is acceptable. It is not a justification for splitting one tightly coupled invariant across many services. If two values must change atomically under all circumstances, they may belong within one service and one local transaction.

Change-Data Capture and Integration

Change-data capture (CDC) observes database changes and publishes them to downstream consumers. Log-based CDC commonly reads a database transaction log, allowing changes to be captured with less impact than application-level polling. Consumers can use CDC to populate warehouses, search indexes, caches, or migration pipelines.

CDC can expose row-level changes, but a row update is not necessarily a meaningful domain event. A change such as status = 3 may lack the business context needed by consumers. CDC should therefore be interpreted carefully, often through a translation layer or an explicit domain-event mechanism. Schema changes, ordering, deletes, snapshots, offsets, and replay must be managed.

CDC is especially useful where an existing system cannot easily be modified to publish events. It can support gradual modernization, but it does not eliminate data ownership questions. Consumers must not assume that a captured table is a stable public contract unless the schema and semantics are governed accordingly.

An Applied Case: An Online Marketplace Order

Background

This illustrative case concerns “Northstar Market,” an online marketplace whose original application uses a shared relational database. The marketplace has grown internationally. Customers report slow checkout, catalogue searches compete with order transactions, and reporting queries affect operational performance.

Situation and Problem

Checkout updates orders, inventory, payments, delivery information, and customer notifications. A single distributed database transaction is difficult to scale across regions. A payment timeout sometimes causes customers to retry, creating duplicate payment attempts. Search results also lag behind product updates, while the business wants an analytical warehouse without burdening the operational database.

Analysis

The organization identifies five ownership boundaries. Catalog owns product descriptions and current prices. Inventory owns stock and reservations. Order owns the order lifecycle. Payment owns authorization and refund state. Shipping owns fulfilment and delivery status. Search and analytics are derived consumers rather than authorities.

The team decides that each core service will have an independently managed relational database initially. This avoids premature polyglot complexity while preserving autonomy. Catalog later adds a search index because text search is a distinct workload. Each service publishes events through a transactional outbox. The Order service acts as the saga orchestrator because it must present a coherent checkout status to the customer.

Approach

The checkout saga proceeds as follows:

1. Order creates an order in Pending status and writes an OrderCreated event to its outbox.

2. Inventory attempts a reservation using an idempotency key. It emits InventoryReserved or InventoryUnavailable.

3. Payment authorizes the requested amount. Repeated requests with the same payment key return the original result rather than charging twice.

4. When both reservations and authorization succeed, Order changes to Confirmed and Shipping is requested.

5. If payment fails after inventory reservation, Order requests release of the reservation. If shipping fails, the order enters a fulfilment-exception state and may trigger refund or manual review.

Read models provide customer order history and operational dashboards. The customer interface displays Pending rather than pretending that all steps are instantaneous. A reconciliation process compares payment records, order states, and inventory reservations. CDC streams operational changes to the warehouse, while personally identifiable information is restricted and masked in analytical environments.

Outcome

Checkout no longer depends on a global transaction. Individual services can scale and deploy independently. The design accepts eventual consistency but makes intermediate states visible and recoverable. Duplicate messages are harmless because consumers use idempotency records. The marketplace gains operational complexity—saga monitoring, event replay, reconciliation, and incident procedures—but this complexity is explicit rather than hidden in a shared database.

Learning Points

The case demonstrates that data duplication can be safer than uncontrolled sharing when ownership is clear. It also shows that a saga coordinates business progress but cannot guarantee instantaneous rollback. Strong local transactions, reliable event publication, carefully defined compensation, and operational reconciliation work together.

Risks, Governance, and Professional Practice

Distributed data systems fail in ways that local applications often conceal. Messages can be duplicated, delayed, reordered, or lost from an incorrectly configured pipeline. A consumer may process an event successfully and crash before recording that fact. A database migration may be deployed before all consumers understand the new schema. Clock differences can make event ordering ambiguous. These are normal design conditions, not exceptional anomalies.

Common mistakes include sharing tables while claiming database-per-service, using events as a substitute for domain modelling, selecting event sourcing merely for fashionable architectural reasons, assuming exactly-once delivery, ignoring replica lag, and allowing every service to query every other service's database. Another error is distributing a transaction that could have remained inside one well-designed aggregate.

Governance should define data classification, ownership, retention, lineage, access rights, backup objectives, recovery-point objectives, and recovery-time objectives. Personal data in replicated databases, logs, events, and analytics stores must be protected under applicable privacy and sector regulations. Immutable event histories can conflict with erasure obligations; designs may need tokenization, encryption-key destruction, redaction strategies, or separation of identifying information from event facts.

Security requires least-privilege database credentials, encrypted transport, encryption at rest, secret rotation, authenticated event producers and consumers, and audit trails for administrative access. A message broker is not automatically a trusted boundary. Events may contain sensitive information and should be minimized, classified, and access-controlled.

Operationally, teams should monitor outbox backlog, consumer lag, saga duration, compensation rate, dead-letter queues, replication delay, query latency, failed projections, and reconciliation discrepancies. Distributed tracing and correlation identifiers connect a user request to database transactions, messages, and compensating actions.

Emerging Directions and Best Practices

Cloud-native platforms increasingly provide managed relational databases, globally replicated stores, streaming systems, serverless data processing, and lakehouse platforms. These services reduce infrastructure work but do not remove architectural responsibility. Vendor-specific consistency models, failover behavior, pricing, data residency, and portability must be evaluated.

Data contracts are becoming more important as event streams and analytical pipelines expand. A useful contract specifies event meaning, ownership, schema evolution rules, compatibility expectations, classification, and retention. Schema registries can enforce structural compatibility, but semantic compatibility still requires domain governance.

AI and analytics increase the demand for reliable data lineage. Machine-learning features built from stale, duplicated, or unauthorised data can produce misleading or discriminatory outcomes. Training and inference pipelines should record provenance, quality checks, access decisions, and model-relevant time boundaries.

The following practices provide a sound basis for implementation:

• Assign one authoritative owner to each business fact.
• Keep transactions local where the invariant permits it.
• Use explicit APIs and versioned events rather than table sharing.
• Treat delivery as at least once and design consumers for idempotency.
• Make eventual consistency and intermediate states visible to users.
• Prefer outbox or equivalent reliable-publication mechanisms.
• Use sagas only for genuinely cross-service processes.
• Separate operational, search, and analytical workloads when their needs differ.
• Test partitions, retries, duplicate delivery, lag, failover, and replay.
• Automate reconciliation and retain procedures for human intervention.
• Document data classification, retention, lineage, and recovery requirements.

Chapter Summary

Microservices data architecture is fundamentally an exercise in allocating ownership and choosing appropriate consistency boundaries. Database-per-service protects autonomy when services alone control their data, while polyglot persistence matches storage technology to workload without excusing unnecessary operational complexity. CAP and PACELC clarify the trade-offs among consistency, availability, latency, and partition behaviour. Isolation and replication must be selected according to business invariants and user-visible guarantees.

CQRS separates write models from query models; event sourcing records immutable business facts and supports reconstruction; transactional outbox makes local state changes and publication reliable; CDC integrates database changes with downstream systems; and sagas coordinate long-running workflows through local transactions and compensation. None provides a universal substitute for sound domain modelling. Idempotency, observability, governance, security, reconciliation, and recovery planning are essential to professional implementation.

Key Terms

Database-per-service: An arrangement in which a service exclusively owns and controls its persistent data.

Data ownership: Responsibility for the meaning, integrity, access, lifecycle, and authoritative updates of data.

Polyglot persistence: Use of different storage technologies selected for different service workloads.

System of record: The authoritative source for a defined business fact.

Eventual consistency: A guarantee that replicas converge if updates cease and communication continues.

Linearizability: A consistency model in which operations appear to occur atomically in real-time order.

CAP theorem: The principle that partitioned distributed systems cannot guarantee both availability and linearizable consistency simultaneously.

PACELC: A model describing consistency-versus-availability during partitions and latency-versus-consistency otherwise.

Isolation: The degree to which concurrent transactions are prevented from interfering with one another.

CQRS: Separation of commands that change state from queries that read state.

Event sourcing: Persistence of an aggregate as an ordered sequence of immutable business events.

Domain event: A recorded business fact that is meaningful within a domain.

Transactional outbox: A pattern that stores a state change and a pending event in one local transaction.

Idempotency: The property that repeating an operation produces the same effective result.

Saga: A sequence of local transactions coordinated with messages and compensating actions.

Choreography: Saga coordination achieved through independent reactions to events.

Orchestration: Saga coordination directed by a central workflow component.

Change-data capture: A technique for detecting database changes and delivering them to downstream consumers.

Read model: A representation optimized for queries and commonly derived from authoritative data.

Replication lag: The delay between a change on a source replica and its availability on another replica.

Review Questions

Short Answer Questions

1. What does data ownership mean in a database-per-service architecture?

2. Distinguish a system of record from a derived read model.

3. Why does a transactional outbox not provide global exactly-once delivery?

4. What is the difference between saga choreography and orchestration?

5. Define eventual consistency and give one suitable use case.

Long Answer Questions

1. Explain the motivations, benefits, and costs of database-per-service and polyglot persistence.

2. Discuss CAP and PACELC and relate them to replication and geographic deployment decisions.

3. Compare CQRS and event sourcing, including situations in which each may be inappropriate.

4. Explain how transactional outbox, idempotent consumers, and CDC support reliable integration.

5. Describe the design of a saga for a multi-service business transaction, including failure and compensation handling.

Analytical / Application Questions

1. An inventory service accepts reservations on a read replica and occasionally oversells products. Analyse the consistency and replication problem and propose a safer design.

2. A team wants to split a single financial invariant across three services and coordinate it with events. Evaluate whether a saga is suitable and justify an alternative if necessary.

3. Design the data architecture for a booking platform whose search results may be stale for seconds but whose confirmed bookings must not be duplicated. Identify ownership boundaries, storage choices, consistency guarantees, and recovery mechanisms.

22. Multiple Choice Questions with Answers

Question 1

Which property is essential to database-per-service architecture?

A. Every service must use a different database product

B. Only one service controls the authoritative data and its business rules

C. All services must share a read replica

D. All services must participate in one global transaction

Correct Answer: B. Only one service controls the authoritative data and its business rules

Question 2

A service commits an order update and an outbox record in one local transaction. What problem does this primarily address?

A. Global serializability across all services

B. Elimination of all duplicate messages

C. Atomicity between local state change and publication intent

D. Removal of network partitions

Correct Answer: C. Atomicity between local state change and publication intent

Question 3

Why must consumers of an outbox event usually be idempotent?

A. Events are always delivered out of order

B. At-least-once delivery can cause the same event to be processed repeatedly

C. The database cannot enforce constraints

D. Idempotency increases replica lag

Correct Answer: B. At-least-once delivery can cause the same event to be processed repeatedly

Question 4

Which statement best describes a saga?

A. A distributed database lock held by all services

B. A sequence of local transactions linked by messages and compensation

C. A method for converting all databases to event stores

D. A read-only reporting process

Correct Answer: B. A sequence of local transactions linked by messages and compensation

Question 5

Under CAP, what trade-off arises during a network partition?

A. Latency versus storage capacity

B. Availability versus linearizable consistency

C. Schema flexibility versus indexing

D. Encryption versus compression

Correct Answer: B. Availability versus linearizable consistency

Question 6

A search index is rebuilt from catalogue events and is not authoritative for product data. It is best classified as what?

A. A system of record

B. A compensating transaction

C. A derived read model

D. A global coordinator

Correct Answer: C. A derived read model

Question 7

Which situation most strongly supports event sourcing?

A. A temporary cache with no audit requirements

B. A domain where historical decisions and state reconstruction are important

C. A static configuration file

D. A simple lookup with no state changes

Correct Answer: B. A domain where historical decisions and state reconstruction are important

Question 8

What is a major consequence of asynchronous read replication?

A. All reads are automatically serializable

B. Reads may return stale data because of replication lag

C. Writes cannot be retried

D. Backups become unnecessary

Correct Answer: B. Reads may return stale data because of replication lag

Question 9

A payment refund cannot fully undo an already sent customer notification. What does this illustrate about saga compensation?

A. Compensation is always identical to database rollback

B. Compensation is a business action that may be partial or irreversible

C. Sagas guarantee atomicity across all services

D. Notifications must always be synchronous

Correct Answer: B. Compensation is a business action that may be partial or irreversible

Question 10

What is a principal risk of using raw database CDC as a public domain-event contract?

A. It prevents all downstream integration

B. Row-level changes may lack stable business meaning and context

C. It guarantees excessive consistency

D. It cannot capture deletes

Correct Answer: B. Row-level changes may lack stable business meaning and context