Data Engineering and Persistence

Introduction

Software systems become valuable when they preserve, organize, and retrieve information reliably. Persistence is the capability of retaining data beyond the lifetime of a running process. Data engineering extends this concern to the design of data models, pipelines, storage platforms, quality controls, operational policies, and analytical access patterns.

Modern applications rarely depend on one universal database. An online banking system may use a relational database for accounts and transfers, a key-value store for sessions, a cache for frequently accessed balances, a document store for flexible customer preferences, and a time-series platform for infrastructure measurements. Good engineering therefore begins with workload and consistency requirements rather than with enthusiasm for a particular product.

This chapter develops a practical framework for selecting and using relational, document, key-value, graph, and time-series stores. It also examines schemas, indexes, transactions, caching, replication, migration, data quality, governance, and the data lifecycle.

Learning Objectives

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

1. Distinguish major database models and select an appropriate store for a workload.
2. Design schemas and indexes from access patterns and integrity requirements.
3. Explain transactions, isolation, concurrency anomalies, replication, and consistency trade-offs.
4. Design caching, migration, quality, retention, and archival strategies.
5. Evaluate persistence architectures using reliability, performance, cost, security, and governance criteria.
6. Apply these concepts to a realistic software system.

1. The Data-Engineering Perspective

A persistence design must answer five questions:

• What data exists? Entities, events, relationships, measurements, and metadata must be identified.
• How is data accessed? Reads, writes, scans, joins, aggregations, and searches have different costs.
• What must be guaranteed? Requirements may include uniqueness, atomicity, durability, freshness, ordering, or auditability.
• How will the system change? Schemas, software versions, traffic, and business rules evolve.
• How long and under what authority may data be retained? Legal, ethical, and operational constraints govern the lifecycle.

A useful engineering model separates three layers:

1. Operational data stores support application transactions.
2. Integration and processing moves, validates, transforms, and enriches data.
3. Analytical and archival stores support reporting, machine learning, audit, and historical analysis.

Data should not be copied casually between layers. Every copy creates obligations concerning freshness, ownership, security, failure recovery, and deletion.

2. Database Models and Their Appropriate Workloads

Relational databases

A relational database represents data in tables of rows and columns. A schema defines attributes and types; primary keys identify rows; foreign keys represent references; constraints express business rules. SQL provides declarative querying, joins, grouping, and transaction control.

Relational systems are a strong default when data has stable structure, relationships are important, and correctness requires multi-record transactions. Examples include orders, invoices, inventory, enrolments, and payroll.

Normalization reduces unnecessary duplication. In a normalized design, a customer is stored once and referenced by orders. Denormalization may deliberately duplicate values to reduce joins or optimize reads, but then update consistency must be managed explicitly.

A relational schema might contain:

Customer(customer_id PRIMARY KEY, name, email UNIQUE)
Order(order_id PRIMARY KEY, customer_id REFERENCES Customer, order_date, status)
OrderLine(order_id REFERENCES Order, product_id REFERENCES Product, quantity, unit_price,
          PRIMARY KEY(order_id, product_id))

The composite key in OrderLine prevents the same product from appearing twice in one order, unless the business rules require separate lines.

Document databases

A document store represents records as documents, commonly JSON-like objects. Documents are useful when aggregates are naturally accessed together, structure varies between records, or nested data is central to the application. A product catalogue, user profile, or content-management record may be a suitable document.

Embedding related data can make an aggregate available in one read, but large or frequently changing embedded data can create update and size problems. Referencing documents resembles foreign-key relationships but usually places more responsibility on application code.

Document flexibility is not the same as absence of structure. Production systems should define validation rules, version fields, required properties, and migration conventions. Otherwise, historical documents become difficult to interpret.

Key-value stores

A key-value store maps a key to an opaque value. It is particularly effective for sessions, authentication tokens, feature flags, rate-limit counters, shopping carts, and idempotency records. Its central design question is key construction. A key should support the required lookup directly, such as session:user:4812 or cart:user:4812.

Key-value systems often provide excellent latency and horizontal scalability, but they generally offer fewer query relationships than relational systems. A design that requires arbitrary filtering over values may be unsuitable unless secondary indexes or a separate search system are available.

Graph databases

A graph database represents vertices and edges, each potentially carrying properties. It is appropriate when traversing relationships is the principal operation: social connections, recommendation paths, fraud rings, network topology, and knowledge graphs.

A graph model makes relationships explicit, but it is not automatically superior for every connected dataset. Simple parent-child data may be easier to maintain relationally. Graph workloads also require careful limits on traversal depth and result size to avoid unpredictable queries.

Time-series stores

Time-series databases optimize records consisting of a timestamp, a measurement, dimensions or tags, and a value. Examples include temperature readings, application latency, power consumption, and financial observations. Design concerns include timestamp precision, late-arriving data, retention, downsampling, and high-ingest indexing.

A time-series schema should distinguish dimensions used for filtering from measurements that vary over time. Excessive high-cardinality dimensions—such as a unique identifier for every request—can cause memory and index costs to grow rapidly.

Choosing among models

Polyglot persistence means using several models deliberately. It does not mean duplicating every dataset across many technologies.

3. Schema and Index Design

Schema design begins with access patterns. List the important queries, their frequency, expected result size, freshness requirement, and acceptable latency. Then design storage structures that make those queries efficient while preserving correctness.

Keys and constraints

A primary key should be stable, unique, and suitable for the workload. Sequential numeric keys are compact and index-friendly, while globally generated identifiers simplify distributed creation but may be larger and less locality-friendly. The choice depends on scale, privacy, ordering needs, and integration boundaries.

Constraints are executable documentation. NOT NULL, UNIQUE, CHECK, and foreign-key constraints prevent invalid states at the database boundary. Application validation remains useful for user feedback, but it should not be the only protection when the database can enforce the rule.

Indexes

An index is an additional access structure that helps locate records without scanning the entire table or collection. B-tree indexes are effective for equality and ordered-range predicates. Hash-oriented indexes are useful for equality in systems that support them, but generally do not support ordered ranges. Inverted indexes support text search; spatial indexes support geometric queries.

Indexes consume storage and impose write and maintenance cost. A practical index review asks:

• Does it support a real, important query?
• Is its leading column selective enough?
• Does its column order match equality and range predicates?
• Can it provide a covering result without accessing the base table?
• Is it redundant with another index?
• What is its write, memory, and storage cost?

For a frequent query filtering by customer_id and ordering by created_at, an index beginning with (customer_id, created_at) is often more useful than two unrelated single-column indexes. The exact result depends on the optimizer and workload, so query plans and production measurements are essential.

Indexes do not eliminate the need for pagination. Offset pagination can become increasingly expensive as the offset grows. Keyset pagination uses a stable ordering key—for example, “records after this timestamp and identifier”—and is often more predictable, provided ties and changing records are handled correctly.

4. Transactions and Consistency

A transaction groups operations into a unit with defined correctness properties. The classic ACID properties are:

• Atomicity: either all required operations take effect or none does.
• Consistency: committed transactions preserve declared constraints and application invariants.
• Isolation: concurrent transactions appear to obey a specified visibility rule.
• Durability: committed data survives the failures covered by the system’s durability design.

A transfer illustrates atomicity. Debiting one account without crediting the other is invalid. A transaction can enforce that both updates commit together, subject to the database’s failure and recovery guarantees.

Isolation levels represent a trade-off between concurrency and anomaly prevention. Common anomalies include:

• Dirty read: observing uncommitted data from another transaction.
• Non-repeatable read: reading a row twice and seeing different committed values.
• Phantom read: repeating a predicate query and finding a changed set of matching rows.
• Lost update: one concurrent write overwrites another without proper coordination.

The names and exact guarantees of isolation levels vary by implementation. Engineers must consult the database documentation rather than assuming that a label has identical behavior everywhere. Optimistic concurrency can use a version column: an update succeeds only when the version still equals the value read earlier. Pessimistic locking can prevent competing changes, but may reduce concurrency and introduce deadlocks.

Distributed transactions across independent stores are difficult and often undesirable. Alternatives include an outbox pattern, sagas, idempotent consumers, and compensating actions. In the outbox pattern, an application writes its business change and an event record in one local transaction; a relay later publishes the event. Consumers must tolerate retries and duplicate delivery.

Idempotency means that repeating an operation has the same intended effect as performing it once. An idempotency key stored with the result can protect payment or order APIs from client retries.

5. Caching and Performance

A cache stores data closer to the consumer to reduce latency or database load. Common patterns include:

• Cache-aside: the application reads the cache, loads from the database on a miss, and then populates the cache.
• Read-through: the cache layer loads missing values automatically.
• Write-through: writes update the cache and backing store together through the cache layer.
• Write-behind: the cache acknowledges first and persists later; this improves write latency but increases durability risk.

Cache correctness depends on invalidation, expiration, and concurrency. A stale cache can display an old price or authorization decision. Time-to-live is not a complete consistency strategy because expiration does not immediately remove incorrect data. Versioned keys, explicit invalidation, and bounded staleness policies are often combined.

A cache should be treated as expendable unless it is explicitly designed as durable storage. The application must function correctly after cache loss, warm-up, partial outage, or a stampede of simultaneous misses. Request coalescing, jittered expiration, and rate limits can reduce stampedes.

Performance analysis should measure latency percentiles, not only averages. The 99th percentile describes a boundary below which approximately 99% of observations fall; it can reveal user-visible tail latency hidden by an average. Measurements should be collected under representative load and with realistic data distributions.

6. Replication, Partitioning, and Availability

Replication maintains multiple copies of data. It can improve availability, read capacity, and disaster recovery, but creates synchronization and operational complexity. Synchronous replication waits for specified replicas before acknowledging a write; asynchronous replication acknowledges earlier and may lose recent acknowledged writes if the primary fails before propagation.

Replication lag affects read-after-write behavior. A user may create an order on a primary and immediately query a lagging replica, apparently losing the order. Routing the user to the primary temporarily, tracking a consistency token, or using a session-consistency mechanism can address this issue.

Partitioning, or sharding, divides data across nodes. A partition key should distribute load evenly and support important queries. Hot keys—such as one celebrity account or a global counter—can overload a single partition. Changing a partition key later may require expensive data movement, so expected growth and access patterns matter.

Availability planning should define recovery objectives:

• RPO (Recovery Point Objective): the maximum acceptable amount of data loss measured in time.
• RTO (Recovery Time Objective): the maximum acceptable time to restore service.

Backups are not a complete disaster-recovery plan. Restoration must be tested, credentials and configuration must be recoverable, and dependencies must be identified. A replica can reproduce corruption or accidental deletion; an immutable, isolated backup provides a different protection.

7. Data Pipelines, Quality, and Observability

A data pipeline extracts or receives data, validates it, transforms it, and delivers it to a destination. Batch pipelines process bounded groups periodically; streaming pipelines process events continuously or in small windows. Both require explicit handling of ordering, duplication, late arrival, schema change, and failure.

Useful data-quality dimensions include:

• Completeness: required records or fields are present.
• Validity: values conform to type, range, and domain rules.
• Uniqueness: duplicate records are controlled.
• Consistency: related representations agree.
• Timeliness: data arrives within the required freshness window.
• Accuracy: values correspond adequately to the real-world subject.

Quality checks should be observable. A pipeline should expose record counts, rejection counts, processing lag, duplicate rates, schema versions, and lineage. Invalid data should be quarantined with enough context for diagnosis rather than silently discarded.

Event processing commonly uses at-least-once delivery, meaning a consumer may receive an event more than once. Idempotent processing, deduplication keys, and transactional offsets can make repeated delivery safe. “Exactly once” is not a universal end-to-end property; it must be defined for a particular boundary and failure model.

8. Migration and Schema Evolution

Data migration changes data representation, storage technology, or both. A safe migration is a controlled release process rather than a one-time script.

A common expand-and-contract sequence is:

1. Expand: add the new schema or destination without removing the old one.
2. Dual read or dual write: introduce compatibility logic and compare results where necessary.
3. Backfill: copy historical data in controlled batches, with checkpoints and validation.
4. Cut over: route reads and writes to the new representation.
5. Observe: monitor errors, latency, completeness, and consistency.
6. Contract: remove obsolete fields, code, and infrastructure only after rollback risk has passed.

Dual writing can create divergence if one write succeeds and the other fails. An outbox, replayable change stream, or reconciliation process can reduce this risk. Backfills should be rate-limited so they do not starve production traffic, and they should be restartable rather than relying on a single all-or-nothing operation.

Schema evolution should preserve compatibility between producers and consumers. Additive changes are usually safer than renaming or changing the meaning of an existing field. Every stored record should have an interpretable version or migration convention.

9. Data Lifecycle, Security, and Governance

The data lifecycle normally includes collection, validation, active use, sharing, retention, archival, and deletion. Retention should be justified by business, legal, or operational purpose. “Keep everything forever” increases breach impact, storage expense, discovery burden, and the risk of using obsolete information.

Governance responsibilities include ownership, classification, access control, lineage, quality accountability, and deletion procedures. Sensitive data should be minimized and protected in transit and at rest. Access should follow least privilege, and administrative actions should be auditable. Encryption does not replace authorization: an authenticated but over-privileged service can still misuse decrypted data.

Privacy engineering may require purpose limitation, consent or another lawful basis, masking, tokenization, access logging, and support for correction or deletion rights where applicable. Exact legal obligations depend on jurisdiction and sector, so technical teams must work with qualified legal and compliance specialists.

Data used for artificial intelligence requires additional care. Training data may contain personal information, hidden bias, licensing restrictions, or leakage from evaluation data. Dataset versions, provenance, feature definitions, and deletion workflows should be recorded. A model pipeline is itself a data system and inherits the need for validation, monitoring, access control, and reproducibility.

10. Worked Design Example: An Online Learning Platform

Consider an illustrative platform that manages courses, enrolments, video progress, quizzes, and recommendations.

Requirements

The platform needs strong correctness for enrolment and payment records, flexible course metadata, rapid session access, relationship queries for prerequisites, and high-volume progress events. Users expect a newly completed quiz to appear immediately in their dashboard, while analytical reports may tolerate several minutes of delay.

Architecture and reasoning

A relational database stores users, courses, enrolments, payments, quiz attempts, and grades. Foreign keys and unique constraints prevent duplicate enrolments and invalid course references. The enrolment transaction records payment authorization and the enrolment state according to the payment integration’s reliability model.

A document store holds course pages and optional content blocks because different courses have varying structures. A key-value store holds sessions, short-lived recommendation results, and idempotency keys. A graph store represents prerequisite relationships and supports queries such as “which courses are reachable after this course?” A time-series store records service latency, video buffering, and infrastructure measurements.

Progress events are written to an event stream. A consumer updates a relational summary for immediate dashboards and another consumer produces analytical data. Consumers use an event identifier to deduplicate retries. A dead-letter or quarantine mechanism retains malformed events for inspection.

Indexes are derived from queries: enrolments use a unique index on (user_id, course_id); dashboard retrieval uses an index beginning with user_id and a suitable status or timestamp; progress events use partitioning by learner or course according to access distribution. Indexes are tested with realistic cardinality rather than assumed to be beneficial.

Migration scenario

The platform initially stores progress in the relational database but encounters write contention during examinations. The team introduces an append-oriented event store while retaining relational summaries. It expands the schema, deploys event writing, backfills historical progress, compares totals and timestamps, then switches selected reads. Only after several release cycles does it remove obsolete write paths.

Outcome and lessons

The architecture matches storage models to workload while preserving a clear source of truth for each business fact. It also exposes trade-offs: more stores increase operational skill requirements, monitoring needs, and failure modes. The key lesson is not that polyglot persistence is always desirable, but that each additional system must have a defensible purpose, owner, recovery plan, and data contract.

11. Best Practices and Common Failure Modes

Effective persistence engineering follows several principles:

• Start with invariants and access patterns, not product names.
• Keep one clearly defined owner for each authoritative business fact.
• Enforce important integrity rules at the strongest practical boundary.
• Make retries safe through idempotency and deduplication.
• Test backups, restoration, migrations, and failover—not merely their configuration.
• Monitor latency percentiles, replication lag, cache hit rate, error rate, and data freshness.
• Document retention, classification, lineage, and deletion behavior.
• Load-test with realistic skew, hot keys, and failure conditions.

Common failures include storing everything in one fashionable database, adding indexes without measuring write costs, treating replicas as backups, assuming caches are always fresh, performing irreversible migrations without rollback, and silently dropping invalid data. Another failure is allowing database access to spread through many services without ownership boundaries, making it impossible to reason about schema changes.

Contemporary Developments

Cloud-managed databases reduce infrastructure administration but do not remove responsibility for data modelling, cost control, security, and recovery testing. Serverless and distributed databases offer elasticity, yet may impose limits on transactions, consistency, query shape, or predictable cost. Lakehouse architectures combine object storage with table formats and analytical engines, but still require schema governance and quality controls.

AI-assisted coding can generate queries and migration scripts, but generated code must be reviewed for injection risk, locking behavior, transaction boundaries, privacy exposure, and performance. Natural-language access to data should enforce authorization at the query layer and provide auditable explanations of which data was accessed.

Chapter Summary

Data engineering and persistence involve designing reliable information systems rather than merely selecting a database product. Relational stores provide strong integrity and expressive joins; document stores support aggregate-oriented and variable structures; key-value stores provide direct low-latency access; graph stores support relationship traversal; and time-series stores handle timestamped measurements at scale.

Good designs derive schemas and indexes from access patterns, enforce invariants, and measure real query plans. Transactions provide atomicity and controlled isolation, while idempotency, outbox patterns, and compensating actions help coordinate distributed workflows. Caches improve performance but introduce staleness and invalidation concerns. Replication and partitioning improve scale and availability while creating lag, failover, and data-distribution challenges.

Professional systems also require observable pipelines, explicit quality dimensions, compatible schema evolution, tested migrations, recovery objectives, security controls, retention policies, and accountable data ownership. The most defensible architecture is the simplest one that meets correctness, performance, availability, cost, and governance requirements.

Key Terms

Persistence: Retaining data beyond the lifetime of a running process.

Relational database: A database organized into tables connected through keys and queried commonly with SQL.

Document store: A database that stores aggregate records as structured documents.

Key-value store: A database that retrieves values primarily through unique keys.

Graph database: A database that represents entities as vertices and relationships as edges.

Time-series database: A store optimized for timestamped measurements and their dimensions.

Schema: The structural and semantic definition of stored data.

Index: An auxiliary access structure that accelerates selected queries.

Normalization: Organizing relational data to reduce inappropriate redundancy and update anomalies.

Transaction: A group of operations governed by atomicity and other correctness guarantees.

Isolation: The visibility rule governing concurrent transactions.

Idempotency: The property that repeating an operation produces the same intended effect as performing it once.

Cache-aside: A caching pattern in which application code loads missing values and populates the cache.

Replication lag: The delay before a change becomes visible on a replica.

Partitioning: Dividing data across storage nodes or partitions.

RPO: The maximum acceptable amount of data loss expressed in time.

RTO: The maximum acceptable time to restore service.

Data lineage: A record of a dataset’s origins, transformations, and destinations.

Schema evolution: Controlled change to a data representation while maintaining compatibility.

Data retention: The policy defining how long data is kept before archival or deletion.

Review Questions

Short Answer Questions

1. Why is a relational database often suitable for payment and enrolment records?
2. What is the principal lookup characteristic of a key-value store?
3. Define replication lag and explain one user-visible consequence.
4. What is the purpose of an idempotency key?
5. Distinguish RPO from RTO.

Long Answer Questions

1. Compare relational, document, key-value, graph, and time-series databases using workload, schema, and consistency criteria.
2. Explain how access patterns influence schema and index design, including the costs of excessive indexes.
3. Discuss ACID properties and common concurrency anomalies.
4. Describe an expand-and-contract migration and explain why dual writing is risky.
5. Explain the relationship among data quality, lineage, observability, retention, and governance.

Analytical / Application Questions

1. Design a persistence architecture for a food-delivery application. Justify each store, identify authoritative data, and describe transaction and caching choices.
2. A replicated database shows a newly created order only after several seconds when users read from replicas. Analyze possible solutions and their trade-offs.
3. A company plans to retain all customer events indefinitely for AI training. Evaluate this plan using privacy, quality, cost, retention, provenance, and deletion considerations.

Multiple Choice Questions with Answers

Question 1

Which workload is most naturally suited to a relational database?

A. A direct lookup of a short-lived session by token

B. A payment transfer requiring constraints across several related records

C. A deep traversal of social relationships

D. High-volume storage of timestamped sensor readings

Correct Answer: B. A payment transfer requiring constraints across several related records

Question 2

What is the primary purpose of a database index?

A. To guarantee that every query returns current data

B. To replace transaction management

C. To accelerate selected access patterns using an additional structure

D. To eliminate the need for schema design

Correct Answer: C. To accelerate selected access patterns using an additional structure

Question 3

Which property means that all operations in a transaction succeed or none take effect?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: A. Atomicity

Question 4

Which pattern writes a business change and an event record in one local transaction before publishing the event later?

A. Cache-aside pattern

B. Outbox pattern

C. Read-through pattern

D. Round-robin pattern

Correct Answer: B. Outbox pattern

Question 5

Why can an asynchronous replica show an older version of a record?

A. It has replication lag

B. It automatically normalizes the record

C. It has a stronger primary key

D. It disables all indexes

Correct Answer: A. It has replication lag

Question 6

Which migration step adds the new schema while retaining compatibility with the old schema?

A. Contract

B. Expand

C. Archive

D. Rollback-only

Correct Answer: B. Expand

Question 7

Which database model is most directly optimized for traversing explicit relationships such as prerequisite paths?

A. Key-value

B. Time-series

C. Graph

D. Flat-file

Correct Answer: C. Graph

Question 8

What is a major risk of write-behind caching?

A. It always prevents horizontal scaling

B. It can acknowledge a write before durable persistence

C. It cannot store values with expiration

D. It requires every query to be a graph traversal

Correct Answer: B. It can acknowledge a write before durable persistence

Question 9

Which data-quality dimension concerns whether required fields or records are present?

A. Completeness

B. Uniqueness

C. Timeliness

D. Accuracy

Correct Answer: A. Completeness

Question 10

What does RTO measure?

A. The acceptable amount of data loss

B. The time required to create an index

C. The maximum acceptable time to restore service

D. The number of replicas required

Correct Answer: C. The maximum acceptable time to restore service