4. Data Architecture and Storage Systems

Introduction and Context

Data architecture is the disciplined design of the structures, technologies, policies, and operational practices through which an organisation collects, stores, processes, protects, and delivers data. In a scalable web system, data architecture is not merely a choice between databases. It determines how quickly requests are served, how failures are handled, how accurately business rules are enforced, how systems evolve, and how confidently an organisation can use information for analysis and decision-making.

A small application may begin with one relational database. As users, transactions, geographic locations, and analytical requirements grow, this arrangement may become insufficient. Read-heavy workloads may require replicas and caches; large event streams may require time-series or column-oriented storage; flexible product information may suit a document store; and relationship-intensive problems may benefit from a graph database. The resulting architecture is often polyglot: different stores are used for different access patterns while a coherent governance model preserves meaning, security, and integrity.

This chapter examines the principal storage models and the architectural decisions surrounding schema design, indexing, transactions, replication, partitioning, sharding, data lakes, and governance. The central principle is that a storage technology should be selected from the workload and business constraints—not from popularity or novelty.

Learning Objectives

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

1. Distinguish relational, document, key-value, column-family, graph, time-series, and search stores.

2. Select storage technologies according to access patterns, consistency needs, scale, and operational constraints.

3. Design schemas, indexes, transactions, replication, partitions, and shards for scalable systems.

4. Explain the roles of data warehouses, data lakes, lakehouses, and polyglot persistence.

5. Evaluate data integrity, security, governance, regulatory, and ethical risks.

6. Analyse a realistic architecture and justify storage decisions using explicit trade-offs.

From Business Facts to Data Models

A data model represents the entities, attributes, relationships, constraints, and events relevant to a domain. Conceptual models express business meaning; logical models translate that meaning into structures such as tables, documents, or vertices; physical models account for indexes, partitions, storage formats, and deployment topology.

Two design approaches are especially important. In normalised relational design, facts are stored once and dependencies are represented through relationships. Normalisation reduces update anomalies and strengthens integrity. In denormalised design, data is deliberately duplicated or embedded to make common reads efficient. Denormalisation can be appropriate in document and distributed systems, but it creates a consistency obligation: every copy must either be updated reliably or treated as eventually consistent.

A useful design sequence is to identify the business operations first. What must be written atomically? Which queries are frequent? What is the expected volume and growth rate? Which records require ordering, expiration, or geographical locality? What is the acceptable loss of data during a failure? These questions reveal the workload more accurately than an abstract preference for a database brand.

Important terminology includes consistency, availability, partition tolerance, latency, throughput, durability, cardinality, schema evolution, and idempotency. The CAP perspective states that when a network partition occurs, a distributed system must make a practical choice between strong consistency and availability; partition tolerance is generally unavoidable in distributed deployments. CAP is not a claim that a database is permanently only one of two categories. Real systems offer configurable consistency and different guarantees for different operations.

The PACELC extension adds that, when there is no partition, a distributed system may still trade latency against consistency. This is valuable in design reviews because synchronous replication may provide stronger guarantees while increasing response time. Durability and consistency should also be distinguished: a committed value can be durable on disk without every reader immediately observing it.

Choosing the Storage Model

Relational databases

Relational databases represent data in tables with rows and columns and use SQL for querying. Primary keys identify records, foreign keys express relationships, and constraints enforce validity. They are particularly suitable for orders, payments, inventory, accounting, and other domains where transactions and cross-entity consistency are central.

The transaction model is commonly described through ACID properties: atomicity ensures that a transaction is all-or-nothing; consistency preserves declared rules; isolation controls the visibility of concurrent operations; and durability protects committed results against failure. Isolation levels, such as read committed and serializable, provide different balances between concurrency and anomaly prevention. A relational system is not automatically strongly consistent in every deployment: replicas, asynchronous pipelines, and application caches may introduce stale reads.

Document stores

Document databases store aggregates, often represented as JSON-like documents. They are effective when an object is usually read or written as a unit and when attributes vary between records. A catalogue item with variable specifications is a typical example. Embedding child data can reduce joins, whereas references avoid excessive duplication and document growth. Document design should therefore follow access patterns rather than simply reproduce object-oriented classes.

Key-value stores

A key-value store maps a unique key to an opaque value. It offers simple, fast access and is commonly used for sessions, feature flags, counters, rate limits, shopping carts, and caches. Its simplicity is also its limitation: complex relationships and ad hoc queries are generally the responsibility of the application or another data system. Expiration policies and atomic operations are important in temporary or high-volume use cases.

Column-family stores

Column-family systems organise data by partition key and clustering or sorting columns. They are designed for distributed scale and predictable query patterns. A schema is often created for each important query, and related values are colocated in a partition. This model is powerful for event histories and large write workloads, but poor partition-key selection can create hot partitions or unbounded data growth.

Graph databases

Graph stores represent entities as nodes and relationships as edges, each potentially carrying properties. They are suitable for fraud networks, recommendation relationships, identity links, route planning, and dependency analysis. Their strength lies in traversing connected data; they are less attractive when the workload consists mainly of independent aggregates and large scans.

Time-series databases

Time-series stores optimise records containing a timestamp, measurement, and dimensions such as device or region. They commonly provide retention policies, downsampling, window queries, and efficient append operations. Monitoring, telemetry, financial observations, and industrial sensors are natural applications. Designers must manage timestamp quality, late-arriving events, cardinality of tags, and the distinction between event time and ingestion time.

Search stores

Search engines build inverted indexes that map terms to documents and support relevance ranking, filtering, faceting, and text analysis. They are excellent for discovery interfaces and log exploration but should not usually be treated as the authoritative transactional source. Indexes can be rebuilt from a system of record, and asynchronous indexing means that newly committed data may not be immediately searchable.

Schema, Index, and Query Design

Schema design and query design are inseparable. A primary key should be stable, sufficiently unique, and appropriate to distribution. Surrogate identifiers can simplify relationships, while natural identifiers may provide useful business semantics but can be difficult to change. Constraints such as uniqueness, checks, and non-null requirements should be enforced as close to the authoritative data as practical.

Indexes reduce read cost by maintaining an additional access structure, but they consume storage and slow writes. A composite index should usually place columns according to filtering, ordering, and selectivity requirements. The leftmost-prefix behaviour of many composite indexes means that column order matters. An index that supports one query may be useless for another. Query plans, cardinality estimates, and production-like measurements should guide decisions rather than intuition.

Common mistakes include indexing every column, using functions that prevent index use, retrieving unnecessary fields, generating unbounded result sets, and allowing application code to create an accidental N+1 query pattern. Pagination should use a stable ordering; keyset pagination is often more predictable at scale than repeatedly scanning with large offsets.

Transactions and Distributed Coordination

A transaction is appropriate when several changes must satisfy one business invariant. For example, confirming a purchase may require reserving stock, recording an order, and creating a payment obligation. If these actions span independent systems, a single local transaction is unavailable. Distributed two-phase commit can provide coordination but introduces blocking and operational complexity.

A common alternative is a saga: a business process is decomposed into local transactions, and compensating actions address failure. The outbox pattern strengthens event publication by recording a domain event in the same local transaction as the business change; a relay later publishes it. Consumers should be idempotent because messages may be delivered more than once. These patterns do not eliminate inconsistency windows, but they make them explicit and manageable.

Replication, Partitioning, and Sharding

Replication maintains multiple copies of data. Synchronous replication can reduce acknowledged-data loss but may increase latency and reduce availability during network problems. Asynchronous replication improves responsiveness and geographic reach but creates replication lag and possible data loss after a primary failure. Read replicas scale read traffic, although applications must know whether a read requires the primary or can tolerate stale data.

Partitioning divides a logical dataset into smaller physical units. Range partitioning is useful for time intervals and ordered scans; hash partitioning distributes records more evenly; list partitioning follows known categories such as region. Sharding distributes partitions across independent nodes. Sharding can increase capacity, but it complicates joins, transactions, backups, rebalancing, and operational diagnosis.

A good shard key distributes load, supports frequent queries, and avoids concentration on one node. Monotonically increasing keys can create write hotspots in some systems, while random keys may harm locality. Resharding requires careful migration, dual reads or writes in some designs, validation, and a controlled cutover. Partition boundaries should also account for retention and archival policies.

Analytical Storage: Warehouses, Lakes, and Lakehouses

Operational databases are optimised for current application transactions, whereas analytical platforms are designed for large scans, aggregation, and historical comparison. A data warehouse stores curated, structured data, often using dimensional models with fact and dimension tables. Extract-transform-load or extract-load-transform pipelines move data from operational sources into analytical storage.

A data lake stores raw or lightly processed data in inexpensive object storage, including structured, semi-structured, and unstructured material. Its flexibility supports exploration and machine learning, but weak cataloguing can produce a “data swamp” in which nobody knows what data means or whether it can be trusted. A lakehouse attempts to combine lake flexibility with warehouse-like table management, transactions, metadata, and governance.

Architects must distinguish batch and streaming ingestion, define data contracts, track lineage, and establish quality checks. A dashboard should not silently mix records with incompatible definitions of revenue, customer, or time. The semantic layer and ownership model are as important as the storage engine.

Polyglot Persistence and Governance

Polyglot persistence uses multiple storage technologies in one solution. For example, a transactional relational database may remain the system of record, a search index may support product discovery, a cache may reduce repeated reads, and an analytical lake may support forecasting. The benefit is workload fit; the cost is duplicated data, more failure modes, operational skills, and difficult cross-system consistency.

Governance supplies the controls that make such an architecture trustworthy. It includes ownership, classification, retention, access control, lineage, quality rules, backup policy, and deletion procedures. Sensitive data should be minimised, encrypted in transit and at rest, and protected through least-privilege access. Logs and replicas can unintentionally preserve data after a user requests deletion, so retention must cover secondary copies and derived datasets.

Regulatory obligations vary by jurisdiction and sector, but common concerns include lawful processing, consent, purpose limitation, access rights, breach response, and data residency. Ethical practice also requires attention to bias in training data, unfair profiling, excessive surveillance, and the explainability of automated decisions. Governance is not only a compliance activity: accurate metadata and clear accountability improve engineering outcomes.

Illustrative Case Study: Scaling a Multi-Region Commerce Platform

Background

A fictional retailer, Northstar Market, began with a single relational database supporting customers, products, orders, and payments. As traffic grew across three regions, users reported slow catalogue searches and occasional stale order-status pages. Analysts also needed years of sales and clickstream history.

Situation and analysis

The relational database was authoritative for financial and inventory transactions, but it was serving incompatible workloads. Full-text catalogue searches consumed CPU, reporting queries competed with checkout transactions, and asynchronous replicas produced visible lag. Clickstream data had high volume and low immediate transactional value.

Architecture and strategy

Northstar retained the relational database for orders, payments, stock reservations, and customer accounts. It introduced a search store populated through an outbox-based indexing pipeline. Product documents were denormalised for search, while the relational catalogue remained authoritative. A read-optimised replica served non-critical account views, and the application directed payment and inventory checks to the primary.

Clickstream events were written to object storage in partitioned, immutable files and catalogued for analytical use. A warehouse-style curated layer supported financial reporting. A cache stored short-lived product and session data, with explicit invalidation and expiration rules. Region-aware partitioning was applied to high-volume event data, while the transactional system used a carefully selected customer or order distribution strategy.

Outcome

The design separated workloads, reduced search contention, and allowed analytics to scale independently. It did not promise instantaneous consistency everywhere. Instead, the user interface labelled search and status views according to their freshness requirements, while checkout decisions used authoritative transactional reads.

Learning points

The case demonstrates that polyglot persistence is effective only when systems of record, data ownership, synchronization mechanisms, freshness guarantees, and recovery procedures are explicit. Adding stores without these decisions would have multiplied inconsistency rather than solved the original problem.

Risks, Limitations, and Professional Practice

Storage migrations can fail through incomplete backfills, incompatible schemas, incorrect time zones, or silent truncation. Teams should rehearse migrations, validate counts and business totals, preserve rollback options, and monitor both technical and domain-level correctness. Backups are useful only if restoration is tested. Recovery objectives should specify the maximum tolerable data loss and service interruption.

Other recurring risks include unbounded documents, hot keys, oversharding, replica lag, destructive schema changes, uncontrolled personal-data duplication, and treating a cache as durable storage. Observability should cover latency percentiles, error rates, saturation, replication lag, partition balance, cache effectiveness, query plans, and data-quality indicators.

Current developments include serverless databases, distributed SQL systems, vector indexes for semantic retrieval, open table formats for analytical lakes, change-data-capture pipelines, and policy-driven data catalogues. These technologies broaden architectural options but do not remove fundamental trade-offs. Vector search, for example, may improve discovery but requires evaluation of relevance, privacy, cost, and the risk of unsupported generated answers when combined with AI systems.

Best-Practice Decision Framework

A defensible storage decision should document: the authoritative source; read and write patterns; consistency and freshness requirements; volume and growth; latency and throughput targets; failure and recovery objectives; security classification; geographical constraints; operational expertise; and total cost. The design should then identify alternatives, test representative workloads, and record why the chosen option is preferable.

Use the simplest store that satisfies the dominant workload. Keep transactional truth separate from derived indexes and analytical copies. Design partitions from real queries, enforce invariants near the source of truth, make asynchronous consumers idempotent, measure before tuning, and treat governance and recovery as architectural requirements rather than later additions.

Chapter Summary

Data architecture connects business meaning with storage, processing, operational reliability, and governance. Relational systems are strong for constrained transactions; document, key-value, column-family, graph, time-series, and search stores serve distinct access patterns. Effective design begins with workload analysis and proceeds through schema, index, transaction, replication, partitioning, and recovery decisions. Distributed systems require explicit treatment of consistency, availability, latency, and failure. Warehouses, lakes, and lakehouses support analytical purposes different from operational databases. Polyglot persistence can improve scalability and usability, but only when ownership, synchronization, data quality, security, and lifecycle policies are clearly defined.

Key Terms

ACID: Transaction properties of atomicity, consistency, isolation, and durability.

CAP theorem: A framework describing trade-offs among consistency, availability, and partition tolerance during network partitions.

Column-family store: A distributed database organised around partition and clustering keys for predictable large-scale access.

Data lake: Storage for raw or lightly processed data in varied formats.

Data warehouse: Curated analytical storage optimised for historical queries and aggregation.

Denormalisation: Deliberate duplication or embedding of data to improve read performance.

Durability: The guarantee that committed data survives specified failures.

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

Index: An auxiliary data structure that accelerates retrieval at additional storage and write cost.

Lakehouse: An analytical architecture combining data-lake flexibility with warehouse-like management and reliability.

Partitioning: Dividing a logical dataset into manageable physical segments.

Polyglot persistence: Using multiple storage technologies according to workload requirements.

Replication: Maintaining multiple copies of data across nodes or locations.

Saga: A sequence of local transactions coordinated through events and compensating actions.

Schema evolution: Controlled change to a data structure while preserving compatibility.

Sharding: Distributing partitions across independent database nodes.

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

Time-series database: A store optimised for timestamped measurements and temporal queries.

Transaction: A unit of work whose operations are governed by defined consistency and isolation rules.

Data lineage: A record of data origins, transformations, destinations, and ownership.

Review Questions

Short Answer Questions

1. What distinguishes a system of record from a search index?

2. Define denormalisation and state one reason for using it.

3. What is replication lag?

4. Why is a shard key important?

5. State two responsibilities of data governance.

Long Answer Questions

1. Compare relational, document, and key-value stores for an online commerce application.

2. Explain ACID properties and discuss why distributed workflows may require sagas or an outbox pattern.

3. Discuss range, hash, and list partitioning, including an appropriate use for each.

4. Compare a data warehouse, data lake, and lakehouse in terms of structure, flexibility, and governance.

5. Explain the benefits and costs of polyglot persistence in a large web platform.

Analytical / Application Questions

1. A payment service requires strict consistency, while product search may be a few seconds stale. Design a storage and replication approach and justify the different guarantees.

2. A time-series platform experiences one overloaded partition despite even overall data volume. Analyse possible causes and propose remedies involving keys, retention, or partitioning.

3. Design a migration from a single relational database to a relational system plus search store and analytical lake. Identify authoritative data, synchronization, validation, failure handling, and governance controls.

22. Multiple Choice Questions with Answers

Question 1

A retailer needs atomic updates to stock, orders, and payment obligations. Which store is the most appropriate system of record for these operations?

A. A relational database with transactional constraints

B. A search index

C. A time-series database

D. A cache-only key-value store

Correct Answer: A. A relational database with transactional constraints

Question 2

Which design best addresses the need to publish an event whenever a database transaction commits successfully?

A. Write to the message broker first and update the database later

B. Store the event in the same local transaction using an outbox pattern

C. Rely on a periodically rebuilt search index

D. Use a random shard key

Correct Answer: B. Store the event in the same local transaction using an outbox pattern

Question 3

A workload consists of device measurements queried by time range and device identifier, with retention and downsampling requirements. Which storage model is the best fit?

A. Graph database

B. Time-series database

C. Search store בלבד

D. Document store used without timestamps

Correct Answer: B. Time-series database

Question 4

What is the principal risk of selecting a poor shard key?

A. All transactions automatically become serializable

B. Data becomes impossible to encrypt

C. Load and storage become concentrated on one or more hot nodes

D. Indexes no longer require maintenance

Correct Answer: C. Load and storage become concentrated on one or more hot nodes

Question 5

Why should a search store generally not be treated as the authoritative transactional source?

A. It cannot store text fields

B. Its indexes may be asynchronously updated and are designed for retrieval rather than core transactional integrity

C. It always has stronger consistency than a relational database

D. It cannot support filtering

Correct Answer: B. Its indexes may be asynchronously updated and are designed for retrieval rather than core transactional integrity

Question 6

Which statement best describes a data lake?

A. A system limited to normalised financial tables

B. A storage environment for raw or lightly processed data in varied formats

C. A cache that stores only active sessions

D. A graph containing only social relationships

Correct Answer: B. A storage environment for raw or lightly processed data in varied formats

Question 7

An application reads a newly created order from an asynchronous replica and cannot find it. Which issue most directly explains this behaviour?

A. Replica lag

B. Over-normalisation of the schema

C. Inverted indexing

D. Keyset pagination

Correct Answer: A. Replica lag

Question 8

Which property means that repeating a message-processing operation does not create an unintended duplicate effect?

A. Cardinality

B. Idempotency

C. Partition tolerance

D. Denormalisation

Correct Answer: B. Idempotency

Question 9

A team adds a separate cache, search index, and analytical copy. Which governance decision is most important before deployment?

A. Whether all systems should use identical query syntax

B. Which system is authoritative and how copies are refreshed, protected, retained, and deleted

C. Whether indexes should be removed from the source database

D. Whether all data should be stored permanently

Correct Answer: B. Which system is authoritative and how copies are refreshed, protected, retained, and deleted

Question 10

Which approach is most suitable when a business process spans independent services and cannot use one local transaction?

A. A saga with local transactions and compensating actions

B. A larger database index

C. An unbounded document

D. A search query with relevance ranking

Correct Answer: A. A saga with local transactions and compensating actions