Trade-Offs in Data Systems Architecture

Why data systems are hard in the first place

Modern applications are assembled from standard building blocks, each solving one recurring need:

• Databases, store data so it can be found again later.
• Caches, remember the result of an expensive operation to speed up reads.
• Search indexes, let users search by keyword or filter.
• Stream processing, handle events and data changes as they occur.
• Batch processing, periodically crunch large accumulated datasets.

The difficulty is not any single block, it is gluing them together and choosing among many options that each have different characteristics. There are dozens of databases, several caching strategies, multiple ways to build search indexes. Knowing how to reason about these choices is what this book is for.

A deeper reason it's hard: different people need to do very different things with the same data. Your team and another team may share a dataset but have completely different priorities, and those priorities are often never stated explicitly, which breeds disagreement about the "right" approach.

Frontend vs Backend

Most of the book is about the backend. For a web app, the frontend is client-side code (runs in the browser); the backend is server-side code that handles requests. The hardest data challenges live in the backend, because a frontend manages only one user's data, whereas the backend manages data on behalf of all users. Backend services are usually reachable over HTTP (or WebSocket), are often stateless (they forget everything about a request once it's handled), and rely on data infrastructure (databases, caches, message queues) to persist anything that must outlive a single request.

---

The Four Trade-offs

Fullscreen

---

1. Operational vs Analytical Systems

This is the book's central early distinction. The same underlying data supports two very different worlds with different access patterns, users, and optimizations.

The two kinds of system

• Operational systems (OLTP), the backend services and data infrastructure where data is created and modified by user actions. The application both reads and writes. This is typically the system of record. Example: the database behind a checkout flow, updated every time someone buys something.
• Analytical systems (OLAP), hold a read-only copy of operational data, optimized for analysis. Used by business analysts and data scientists, who read the data (and may build derived datasets from it) but do not modify the originals.

Where "transaction" comes from: Historically a database write corresponded to a commercial transaction, a sale, an order, a payroll payment. Databases spread far beyond money, but the word stuck. In this chapter "transaction" is used loosely to mean low-latency reads and writes; the precise definition arrives in Chapter 8.

Access patterns

• OLTP, point query: look up a small number of records by key, then insert/update/delete based on user input. Because these apps are interactive, this became known as OnLine Transaction Processing.

• OLAP, aggregate scan: scan over a huge number of records and compute aggregates (count, sum, average) rather than returning individual records. Example questions a supermarket analyst asks:

  • What was the total revenue of each store in January?
  • How many more bananas than usual did we sell during the latest promotion?
  • Which baby-food brand is most often bought together with brand X diapers?

  This pattern is called OnLine Analytical Processing.

What does "online" mean in OLAP? It is unclear and somewhat historical, it probably signals that analysts use the system interactively for exploratory queries, not just for fixed predefined reports.

OLTP vs OLAP: full comparison

Why OLTP queries are locked down: operational users generally cannot write arbitrary SQL, because a custom query could read data they shouldn't access, or could be so expensive it degrades performance for everyone else. So OLTP runs a fixed set of queries baked into the app. Analytical systems, by contrast, hand users the freedom to write arbitrary SQL or generate it through tools like Tableau, Looker, or Power BI.

Product / real-time analytics is a third category: analytical workloads (aggregations over many records) embedded into user-facing products. Systems like Pinot, Druid, and ClickHouse ingest data in real time and optimize for low-latency responses, whereas traditional OLAP ingests in batches and optimizes for high-throughput.

Who Uses These Systems

Data warehousing

A data warehouse is a separate database holding a read-only copy of data from all of the company's OLTP systems, so analysts can query it freely without affecting OLTP performance.

A large enterprise might run dozens or hundreds of OLTP systems (website, point-of-sale, inventory, routing, suppliers, HR…), each complex and independently maintained. Querying them directly for analytics is undesirable because:

• The data of interest is spread across multiple systems, hard to combine in one query, a problem called data silos.
• OLTP-friendly schemas are poorly suited to analytics.
• Analytical queries are expensive and would hurt OLTP performance.
• OLTP systems may live on restricted networks for security/compliance.

ETL (Extract → Transform → Load) is the process of getting data into the warehouse:

Fullscreen

• ELT is the same idea with the order swapped: load the raw data first, transform it inside the warehouse afterward.
• When sources are external SaaS products you can only reach through a vendor API, data connector services (Fivetran, Singer, Airbyte) handle the extraction.

HTAP (Hybrid Transactional/Analytical Processing) tries to do OLTP and OLAP in a single system with no ETL, useful for workloads like fraud detection that both scan many rows and read/update individual records with low latency. But most HTAP systems internally couple an OLTP engine and a separate analytical engine behind one interface, so the distinction still matters for understanding them. HTAP does not replace data warehouses, partly because good practice gives each operational system its own database (hundreds of them), while the enterprise typically wants a single warehouse to combine everything.

As workloads grow more demanding, systems become more specialized. General-purpose systems handle small volumes fine, but "the greater the scale, the more specialized systems tend to become." (Stonebraker's "one size fits all is an idea whose time has come and gone.")

From warehouse to data lake, and beyond

A warehouse usually uses a relational model queried with SQL, great for business analysts, less great for data scientists, who often need to:

• Turn rows/columns into numerical feature vectors/matrices for ML training (feature engineering), hard to express in SQL.
• Run NLP on text (e.g., extract sentiment from reviews) or computer vision on images.

So data scientists typically prefer Python (Pandas, scikit-learn), R, and Spark over a relational warehouse, and use a data lake instead.

Fullscreen

• A data lake is a centralized repository holding a copy of any potentially useful data as raw files, with no enforced schema or format. Cheaper than relational storage because it can use commodity object stores.
• Sushi principle: "raw data is better." Store data raw and let each consumer transform it to suit their own needs, rather than transforming once up front. The lake often becomes an intermediate stop on the way to the warehouse.
• DataOps applies operational and governance discipline to data pipelines, driven partly by privacy regulation (GDPR, CCPA).
• Data is increasingly delivered as streams of events (Chapter 12), letting analytics respond in seconds (e.g., block fraud) rather than waiting for a daily batch.
• Reverse ETL pushes analytical outputs back into operational systems, for example, deploying a trained ML model so it generates live recommendations. Tools: TFX, Kubeflow, MLflow.

Systems of record vs derived data

A second cross-cutting distinction (related to, but not the same as, operational vs analytical):

Fullscreen

Key insights:

• Derived data is technically redundant (it duplicates information) but essential for performance, and you can derive many views from one source to look at data different ways.
• Analytical systems are usually derived systems (they consume data created elsewhere). Operational services mix both: the primary DB is the system of record, while indexes and caches are derived.
• A database is just a tool. Whether it is a "system of record" or "derived" depends on how you use it, not what it is.
• When data is derived from another source, you need a process to keep it up to date when the source changes. Many databases assume your app uses only that one database and make multi-system integration awkward, Chapter 11 covers data pipelines as the answer.

---

2. Cloud vs Self-Hosting

The classic build vs buy question, which is ultimately about business priorities.

Rule of thumb: Do your core competency / competitive advantage in-house; outsource the non-core, routine, commonplace work to a vendor. (To take an extreme example: almost no company fabricates its own CPUs, it's cheaper to buy them.)

The spectrum: two decisions: who builds, who operates

Fullscreen

The middle ground (self-hosted) can be on your own hardware ("on premises," even if it's a rented datacenter rack) or on a cloud VM (IaaS, infrastructure as a service).

Pros and cons of cloud services

Is cloud actually cheaper? It depends on your skills and workload. If you already know how to run the system and your load is predictable (machine count doesn't swing wildly), buying your own machines is often cheaper. Cloud wins when load is highly variable and datasets are large enough that idle provisioned capacity would be wasteful. Some cases (e.g., latency-sensitive high-frequency trading) need full hardware control, so they stay in-house.

Cloud native architecture

Cloud native means designed from the ground up to exploit cloud services, not merely self-hosted software lifted into a VM. Cloud-native systems have shown better performance on the same hardware, faster failure recovery, faster scaling, and support for larger datasets.

Layering of cloud services, the defining cloud-native idea is to build higher-level services on top of lower-level ones:

Fullscreen

Higher-level abstractions are more convenient but more use-case-specific. If your needs match what a high-level system was designed for, use it. If nothing fits, you assemble from lower-level components yourself.

Separation of storage and compute (disaggregation), traditionally one machine does both storage and computation; cloud-native systems separate them:

Fullscreen

• Traditionally, disk is treated as durable and RAID keeps copies across disks on one machine.
• In the cloud, a VM's local disk is treated as an ephemeral cache, it disappears if the instance fails or is resized onto different physical hardware.
• Virtual disks (EBS, Azure managed disks, GCP persistent disks) can detach/reattach across instances, but they are network services emulating a block device (typically 4 KiB blocks). They add overhead and make every I/O a network call, so cloud-native systems often avoid them in favor of dedicated storage services (S3 for large files; small values managed separately).
• Multitenancy, multiple customers share the same hardware, improving utilization and easing management, but requiring careful engineering so one tenant can't hurt another's performance or security.

Operations in the cloud era

• Old roles: DBA (database administrator), sysadmin.
• New philosophy: DevOps / SRE (Site Reliability Engineering, Google's implementation), shared responsibility for development and operations.
• DevOps/SRE emphasizes: automation over manual one-offs; ephemeral VMs over long-running servers; frequent application updates; learning from incidents; preserving organizational knowledge as people come and go.
• In the cloud, ops shifts focus rather than disappearing: capacity planning becomes financial planning, performance optimization becomes cost optimization. You still own security, service integration, monitoring, and root-causing outages. "The need for operations is as great as ever."

---

3. Distributed vs Single-Node Systems

A distributed system is multiple machines communicating over a network. Each machine is a node.

Reasons to go distributed

Why distributed systems are hard

• Network failure is always possible. A request may time out and you don't know whether it was received, so blindly retrying may be unsafe (Chapter 9).
• Remote calls are vastly slower than calling a function in the same process. For large data, it's often faster to bring the computation to the data than to move the data.
• More nodes are not always faster. A single-threaded program on one machine can beat a 100-core cluster for some tasks.
• Troubleshooting is hard, which is why observability matters (tracing tools: OpenTelemetry, Zipkin, Jaeger).
• Cross-service consistency becomes the application's problem. Distributed transactions exist (Chapter 8) but are rarely used in microservices because they couple services together.

Practical guidance: A single machine is often simpler and cheaper. Hardware keeps getting bigger, faster, and more reliable, and single-node databases (DuckDB, SQLite, KùzuDB) now handle many workloads. Don't rush into distribution if one machine will do.

Fullscreen

Microservices and serverless

The most common way to distribute is client/server: clients make requests to servers, usually over HTTP. A process can be both a server (handling requests) and a client (calling other services).

SOA → microservices. Split an app into small services, each with one well-defined purpose, its own API, its own owning team, and (importantly) its own database.

Fullscreen

• Why not share a database? A shared schema effectively becomes part of every service's API, making it very hard to change, and one service's queries can degrade another's performance.
• Advantages: independent updates (less cross-team coordination), independent hardware allocation, hidden implementation details behind the API.
• Costs: testing is harder (you must run dependencies too); each service needs deployment, scaling, logging, monitoring, and on-call infrastructure (often via orchestration frameworks like Kubernetes); and APIs are hard to evolve, adding/removing fields can break clients, often discovered late. Standards like OpenAPI and gRPC help (Chapter 5).
• The key reframing: microservices are primarily a technical solution to a people problem, letting teams progress independently. Valuable in a large company; usually unnecessary overhead in a small one.

Serverless / FaaS (Function as a Service). The vendor automatically allocates and frees hardware per incoming request; you pay only for execution time (metered billing for code, the way cloud storage brought metered billing to disk). Caveats: execution time limits, restricted runtimes, cold-start latency. "Serverless" is partly marketing, it still runs on servers (just possibly a different one each time), and products like BigQuery and managed Kafka use the term to mean autoscaling + usage-based billing.

Cloud computing vs supercomputing (HPC)

Large-scale analytical systems sometimes resemble HPC, so the techniques are worth knowing, but this book mainly concerns continuously available services.

---

4. Data Systems, Law, and Society

Core idea: A data system's architecture is shaped not only by technical goals but by human, legal, and ethical responsibility, especially for systems storing data about people. Engineers have a responsibility toward society, not just their employer.

• Regulations: GDPR (EU, since 2018), CCPA (California), and the EU AI Act give individuals rights over their personal data and restrict its use.
• Societal impact: social media reshapes how people consume news (affecting politics and elections); automated systems increasingly decide who gets a loan, insurance, a job interview, or becomes a criminal suspect, decisions with profound consequences.
• A concrete engineering tension, the "right to be forgotten": GDPR lets people demand erasure of their data. But many systems rely on immutable, append-only logs. How do you delete a record from something designed to be immutable? How do you remove data already baked into derived datasets (e.g., ML training data)? There are no clear standard answers yet, GDPR deliberately avoids mandating specific technologies, setting high-level principles instead.
• The true cost of storing data exceeds the storage bill: add the risk of breach liability, reputational damage, legal fines, and the possibility that governments compel disclosure. When data could reveal criminalized behavior (in some jurisdictions: homosexuality, or seeking an abortion), storing it creates real physical-safety risk for users, e.g., location or IP logs revealing a clinic visit.
• Data minimization (German: Datensparsamkeit), collect data only for a specified, explicit purpose; don't reuse it for other purposes; don't keep it longer than necessary. This runs counter to the "big data" instinct of hoarding data speculatively. Sometimes the right decision is not to store data at all.
• Business-driven compliance: PCI standards for payment card data (with frequent independent audits) and SOC 2 audits demanded by software buyers.

Ethics, bias, and discrimination get the full treatment in Chapter 14.

---

Real-world examples and analogies

• OLTP vs OLAP, the cashier vs the auditor. OLTP is the supermarket cashier: thousands of tiny, fast transactions, each touching one customer's basket. OLAP is the finance auditor at month-end: a handful of giant questions ("total revenue per store") that sweep across every receipt ever printed. You wouldn't make the cashier stop scanning so the auditor can tally the year's sales, which is exactly why you copy data into a separate warehouse.
• ETL, the translator at customs. Goods (data) arrive from many countries (OLTP systems) in different packaging and languages. ETL is customs: unpack, inspect, relabel into one common standard, then shelve in the warehouse so any analyst can find them.
• Data warehouse vs data lake, filing cabinet vs storage unit. A warehouse is a labeled filing cabinet: everything pre-sorted into a schema, easy to retrieve if your question fits the folders. A lake is a storage unit: throw anything in raw (boxes, furniture, photos), cheaper, and you sort it only when you need it. The "sushi principle" is preferring the raw storage unit because you don't yet know how each person will want their data cooked.
• System of record vs derived data, the original document vs the photocopies. The signed contract in the safe is the source of truth. The scanned PDF, the summary spreadsheet, and the index card noting where it's filed are all derived, lose any of them and you can regenerate from the original. Lose the original and you're in trouble.
• Cloud vs self-hosting, renting vs owning a car. Renting (cloud) is great when your needs vary (a moving van one weekend, nothing the next) and you don't want to learn car maintenance. Owning (self-hosting) is cheaper if you drive the same predictable route daily and already know how to fix it, but you're on the hook when it breaks.
• Separation of storage and compute, coat check. Instead of every guest (compute node) lugging their coats (data) around all night, coats live in one coat-check room (S3) and guests fetch them when needed. The room can hold far more than any one person could carry, and losing a guest doesn't lose the coats.
• Microservices, a restaurant kitchen with stations. Grill, salad, and pastry stations each own their tools and recipes and can be improved independently. It works because each station has a clear interface (the order ticket). The catch: a tiny food truck doesn't need five stations, one cook is simpler. That's microservices being "a technical solution to a people problem."
• Distributed systems caution, the group project. Two people can split work and go faster; twenty people often go slower because of coordination, miscommunication, and waiting on each other. More nodes, like more teammates, is not automatically faster.
• Data minimization, don't keep what you can't protect. A shop that doesn't record customers' home addresses can't leak them. The safest data is the data you never stored.

---

Cheat-sheet flashcards

Format below is Q → A. Cover the answer and recall it.

• OLTP stands for? → Online Transaction Processing.
• OLAP stands for? → Online Analytical Processing.
• OLTP's main read pattern? → Point queries (fetch records by key).
• OLAP's main read pattern? → Aggregate scans over many records.
• ETL expands to? → Extract, Transform, Load.
• Difference between ETL and ELT? → ELT transforms after loading, inside the warehouse.
• What is a data warehouse? → A separate read-only copy of OLTP data, optimized for analytics.
• What is a data lake? → A centralized repo of raw files with no enforced schema/format.
• The "sushi principle" says? → Raw data is better; transform per-consumer, not up front.
• What is reverse ETL? → Pushing analytical outputs back into operational systems.
• What is HTAP? → Hybrid Transactional/Analytical Processing (both in one system).
• System of record = ? → The authoritative source of truth; each fact stored once.
• Derived data = ? → Data re-creatable from a source (cache, index, view, model).
• "Cloud native" means? → Designed from the ground up to exploit cloud services.
• IaaS / SaaS / FaaS = ? → Infrastructure / Software / Function as a Service.
• Disaggregation refers to? → Separating storage from compute.
• Multitenancy = ? → Multiple customers sharing the same hardware.
• A "node" is? → One machine in a distributed system.
• SRE stands for? → Site Reliability Engineering.
• In the cloud, capacity planning becomes? → Financial planning (cost optimization).
• Microservices are primarily a solution to what kind of problem? → A people problem (independent teams).
• Why avoid sharing a database between microservices? → The schema becomes a hard-to-change API and causes performance interference.
• One reason more nodes can be slower? → Network/coordination overhead; remote calls are far slower than in-process calls.
• Tools for observability/tracing? → OpenTelemetry, Zipkin, Jaeger.
• Data minimization (German term)? → Datensparsamkeit.
• The "right to be forgotten" clashes with which design pattern? → Immutable append-only logs.

---

Common interview questions

1. Explain the difference between OLTP and OLAP, with an example of each. (Expect: access patterns, query types, users, dataset size, and a concrete example like a checkout DB vs a sales-reporting warehouse.)
2. Why do companies separate analytical workloads from operational databases? (Data silos, schema mismatch, expensive queries hurting OLTP, network/security isolation.)
3. Walk me through ETL. When would you use ELT instead? (ELT when the warehouse is powerful enough to transform after load, or when you want raw data preserved first.)
4. Data warehouse vs data lake, when would you choose each? (Warehouse: relational, SQL, BI, defined schema. Lake: raw files, ML/feature engineering, cheap, schema-on-read.)
5. What's the difference between a system of record and derived data? Give examples and explain why the distinction matters. (Source of truth vs re-creatable copies; matters for correctness, recovery, and clarity of data flow.)
6. When is self-hosting cheaper than the cloud, and when is the cloud cheaper? (Self-host: predictable load + existing expertise. Cloud: variable load, large datasets, lack of in-house ops skill.)
7. What does "separation of storage and compute" mean and why is it valuable in the cloud? (Independent scaling, durability via object storage, ephemeral compute, elasticity.)
8. List the main reasons to build a distributed system, and the main reasons not to. (For: fault tolerance, scalability, latency, compliance, hardware. Against: network failure, debugging difficulty, consistency, cost, "more nodes ≠ faster.")
9. Why are microservices described as a solution to a people problem rather than a technical one? (Team autonomy and independent deployment; technical complexity is a cost, not the goal.)
10. What is serverless / FaaS, and what are its limitations? (Per-request resource allocation, pay-per-execution; limits: cold starts, execution time caps, restricted runtimes.)
11. How can privacy regulations like GDPR influence the technical design of a data system? (Right to erasure vs immutable logs; deletion in derived datasets; data minimization; storage-location/residency.)
12. What is data minimization and why might storing less data be the safer engineering choice? (Reduce breach/liability/safety risk; data you never store can't leak.)

---

Key terms glossary

---