Chapter 1. Trade-Offs in Data Systems Architecture

Chapter 1. Trade-Offs in Data Systems Architecture

There are no solutions; there are only trade-offs. […] But you try to get the best trade-off you can get, and that's all you can hope for.

>

— Thomas Sowell, interview with Fred Barnes (2005)

---

Data is central to much application development today. With web and mobile apps, software as a service (SaaS), and cloud services, it has become normal to store data from many different users in a shared server-based data infrastructure. Data from user activity, business transactions, devices, and sensors needs to be stored and made available for analysis. As users interact with an application, they both read the data that is stored and generate more data.

Small amounts of data, which can be stored and processed on a single machine, are often fairly easy to deal with. However, as the data volume or the rate of queries grows, it needs to be distributed across multiple machines, which introduces many challenges. As the needs of the application become more complex, it is no longer sufficient to store everything in one system, and it might be necessary to combine multiple storage or processing systems that provide different capabilities.

We call an application data-intensive if data management is one of the primary challenges in developing the application. While in compute-intensive systems the challenge is parallelizing a very large computation, in data-intensive applications we usually worry more about things like storing and processing large data volumes, managing changes to data, ensuring consistency in the face of failures and concurrency, and making sure services are highly available.

Such applications are typically built from standard building blocks that provide commonly needed functionality. For example, many applications need to do the following:

• Store data so that they, or another application, can find it again later (databases)
• Remember the result of an expensive operation, to speed up reads (caches)
• Allow users to search data by keyword or filter it in various ways (search indexes)
• Handle events and data changes as soon as they occur (stream processing)
• Periodically crunch a large amount of accumulated data (batch processing)

In building an application we typically take several software systems or services, such as databases or APIs, and glue them together with application code. If you are doing exactly what the data systems were designed for, this process can be quite easy.

However, as your application becomes more ambitious, challenges arise. There are many database systems with different characteristics, suitable for different purposes—how do you choose which one to use? There are various approaches to caching, several ways of building search indexes, and so on—how do you reason about their trade-offs? You need to figure out which tools and which approaches are the most appropriate for the task at hand, and it can be difficult to combine tools when you need to do something that a single tool cannot do alone.

This book is a guide to help you make decisions about which technologies to use and how to combine them. As you will see, no one approach is fundamentally better than others; everything has pros and cons.

This chapter compares several contrasting concepts and explores their trade-offs:

• The difference between operational and analytical systems — ["Operational Versus Analytical Systems"](#operational-versus-analytical-systems)
• The pros and cons of cloud services and self-hosted systems — ["Cloud Versus Self-Hosting"](#cloud-versus-self-hosting)
• When to move from single-node systems to distributed systems — ["Distributed Versus Single-Node Systems"](#distributed-versus-single-node-systems)
• Balancing the needs of the business and the rights of the user — ["Data Systems, Law, and Society"](#data-systems-law-and-society)

---

TERMINOLOGY: FRONTENDS AND BACKENDS

>

Much of what we will discuss in this book relates to backend development. For web applications, the client-side code (which runs in a web browser) is called the frontend, and the server-side code that handles user requests is known as the backend. Mobile apps are similar to frontends in that they provide user interfaces, which often communicate over the internet with a server-side backend.

>

A backend service is often reachable via HTTP (or sometimes WebSocket); it usually consists of application code that reads and writes data in one or more databases and sometimes interfaces with additional data systems, such as caches or message queues. The application code is often stateless (i.e., when it finishes handling one HTTP request, it forgets everything about that request), and any information that needs to persist from one request to another needs to be stored either on the client or in the server-side data infrastructure.

---

Operational Versus Analytical Systems

In addition to the teams managing backend services, two other groups of people typically require access to an organization's data: business analysts, who generate reports about the activities of the organization to help management make better decisions (business intelligence, or BI), and data scientists, who look for novel insights in data or who create user-facing product features that are enabled by data analysis and machine learning (ML)/AI.

Although business analysts and data scientists tend to use different tools and operate in different ways, they have some practices in common. Both perform analytics, which means they look at the data that the users and backend services have generated. They generally do not modify this data (except perhaps for fixing mistakes), although they might create derived datasets in which the original data has been processed in some way.

This has led to a split between two types of systems:

• Operational systems consist of the backend services and data infrastructure where data is created — for example, by serving external users. The application code both reads and modifies the data in its databases, based on the actions performed by the users.
• Analytical systems serve the needs of business analysts and data scientists. They contain a read-only copy of the data from the operational systems, and they are optimized for the types of data processing that are needed for analytics.

As these systems have matured, two new specialized roles have emerged: data engineers (who know how to integrate operational and analytical systems and take responsibility for the organization's data infrastructure) and analytics engineers (who model and transform data to make it more useful for analysts and data scientists).

---

Characterizing Transaction Processing and Analytics

In the early days of business data processing, a write to the database typically corresponded to a commercial transaction taking place: making a sale, placing an order with a supplier, paying an employee's salary, etc. As databases expanded into other areas, the term transaction nevertheless stuck, referring to a group of reads and writes that form a logical unit.

NOTE: Chapter 8 explores in detail what we mean by a transaction. This chapter uses the term loosely to refer to low-latency reads and writes.

An operational system typically looks up a small number of records by a key (a point query). This access pattern became known as online transaction processing (OLTP).

Databases also started being increasingly used for analytics, which has very different access patterns. Usually, an analytical query scans over a huge number of records and calculates aggregate statistics (such as count, sum, or average) rather than returning the individual records to the user. For example, a business analyst at a supermarket chain may want to answer queries such as:

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

This pattern of using databases has been called online analytical processing (OLAP). The difference between OLTP and OLAP is not always clear-cut, but some typical characteristics are listed in the table below.

---

Table 1-1. Comparing characteristics of operational and analytical systems

---

With operational systems, users are generally not allowed to construct custom SQL queries and run them on the database directly. OLTP systems mostly run fixed sets of queries baked into the application code. On the other hand, analytical databases usually give their users the freedom to write arbitrary SQL queries by hand, or to generate queries automatically using a data visualization or dashboard tool such as Tableau, Looker, or Microsoft Power BI.

Another type of system is designed for analytical workloads but embedded into user-facing products. Systems designed for this type of use, known as product analytics or real-time analytics, include Pinot, Druid, and ClickHouse. Such systems ingest data in real time and are optimized for low-latency query responses.

---

Data Warehousing

At first, the same databases were used for both transaction processing and analytical queries. However, a trend arose for companies to stop using their OLTP systems for analytics purposes and to run the analytics on a separate database system instead: the data warehouse.

It is usually undesirable for business analysts and data scientists to directly query OLTP systems, for several reasons:

• The data of interest may be spread across multiple operational systems, making it difficult to combine those datasets in a single query (data silos).
• The kinds of schemas and data layouts that are good for OLTP are less well suited for analytics.
• Analytical queries can be quite expensive, and running them on an OLTP database would impact performance for other users.
• The OLTP systems might reside in a separate network that users are not allowed to directly access, for security or compliance reasons.

A data warehouse contains a read-only copy of the data from all the various OLTP systems in the company. Data is extracted from OLTP databases, transformed into an analysis-friendly schema, cleaned up, and then loaded into the data warehouse. This process is known as extract–transform–load (ETL). Sometimes the transformation is done after loading, resulting in ELT.

---

Figure 1-1. A simplified outline of ETL into a data warehouse

                    OPERATIONAL SYSTEMS
  ┌──────────────────────────────────────────────────────────────┐
  │                                                              │
  │  [Customer]         [Warehouse worker]      [Truck driver]  │
  │      │                     │                      │         │
  │      ▼                     ▼                      ▼         │
  │  ┌─────────┐        ┌────────────┐        ┌─────────────┐  │
  │  │Ecommerce│        │Stock-keep. │        │  Vehicle    │  │
  │  │  site   │        │    app     │        │route planner│  │
  │  └────┬────┘        └─────┬──────┘        └──────┬──────┘  │
  │       │                   │                      │         │
  │  ┌────┴────┐        ┌─────┴──────┐        ┌──────┴──────┐  │
  │  │Sales DB │        │Inventory DB│        │   Geo DB    │  │
  │  └────┬────┘        └─────┬──────┘        └──────┬──────┘  │
  └───────┼─────────────────── ┼─────────────────────┼─────────┘
          │ Extract            │ Extract              │ Extract
          ▼                    ▼                      ▼
    ┌───────────┐        ┌───────────┐        ┌───────────┐
    │ Transform │        │ Transform │        │ Transform │
    └─────┬─────┘        └─────┬─────┘        └─────┬─────┘
          │ Load               │ Load                │ Load
          └────────────────────┼─────────────────────┘
                               ▼
                    ANALYTICAL SYSTEMS
  ┌──────────────────────────────────────────────────────────────┐
  │                                                              │
  │  [Business analyst] ────Query────► ┌──────────────────────┐ │
  │                                    │    Data Warehouse    │ │
  │                                    └──────────────────────┘ │
  └──────────────────────────────────────────────────────────────┘

---

In some cases, the data sources of the ETL processes are external SaaS products such as CRM, email marketing, or credit card processing systems. ETL for SaaS APIs is often implemented by specialist data connector services such as Fivetran, Singer, or Airbyte.

Some database systems offer hybrid transactional/analytical processing (HTAP), which aims to enable OLTP and analytics in a single system without requiring ETL. However, even where HTAP exists, it is common to maintain a separation between transactional and analytical systems because of their different goals and requirements.

---

From Data Warehouse to Data Lake

A data warehouse often uses a relational data model queried through SQL. This model works well for business analysts, but is less well suited to the needs of data scientists performing tasks such as:

• Feature engineering — transforming data into numerical vectors or matrices for training ML models, often requiring custom code that is difficult to express in SQL.
• NLP and computer vision — extracting structured information from text, photos, and other unstructured data.

The answer is a data lake: a centralized data repository that holds a copy of any data that might be useful for analysis, obtained from operational systems via ETL. The difference from a data warehouse is that a data lake simply contains files, without imposing any particular file format, data model, or schema. Besides being more flexible, a data lake is also often cheaper than relational data storage, since it can use commoditized object stores.

ETL processes have been generalized to data pipelines, and in some cases the data lake has become an intermediate stop on the path from operational systems to the data warehouse. This approach has the advantage that each consumer of the data can transform the raw data into the form that best suits their needs — sometimes called the sushi principle: "raw data is better."

---

Beyond the Data Lake

Analytics practices have matured to pay increasing attention to the management and operations of analytical systems and data pipelines, as captured in the DataOps Manifesto. This has been driven partly by issues of governance, privacy, and compliance with regulations such as the GDPR and CCPA.

Data for analytics is increasingly made available not only as files and relational tables, but as streams of events. With file-based data analysis, you can rerun the analysis periodically (e.g., daily) to respond to changes in the data, but stream processing allows analytical systems to respond to events much faster, on the order of seconds.

In some cases the outputs of analytical systems are made available to operational systems (a process sometimes known as reverse ETL). For example, an ML model trained in an analytical system may be deployed to production so that it can generate recommendations for end users. Machine learning models can be deployed to operational systems using specialized tools such as TFX, Kubeflow, or MLflow.

---

Systems of Record and Derived Data

This book also distinguishes between systems of record and derived data systems:

Systems of record (a.k.a. source of truth) hold the authoritative or canonical version of data. When new data comes in — for example, as user input — it is first written here. Each fact is represented exactly once (typically normalized). If there is any discrepancy between another system and the system of record, the value in the system of record is (by definition) the correct one.

Derived data systems contain data that is the result of taking existing data from another system and transforming or processing it in some way. If you lose derived data, you can re-create it from the original source. Classic examples include caches, denormalized values, indexes, materialized views, and ML models trained on a dataset.

Analytical systems are usually derived data systems, because they are consumers of data created elsewhere. Operational services may contain a mixture of both: the systems of record are the primary databases to which data is first written, whereas the derived data systems are the indexes and caches that speed up common read operations.

Most databases, storage engines, and query languages are not inherently systems of record or derived systems. A database is just a tool; how you use it is up to you.

---

Cloud Versus Self-Hosting

With anything that an organization needs to do, one of the first questions is whether it should be done in-house or outsourced — that is, should you build or should you buy?

A common rule of thumb is that things that are a core competency or a competitive advantage of your organization should be done in-house, whereas things that are non-core, routine, or commonplace should be left to a vendor.

---

Figure 1-2. The spectrum of decisions on outsourcing software and its operations

More control                                                Less control
Greater investment                                       Lower investment
      │                                                         │
      ▼                                                         ▼
◄─────────────────────────────────────────────────────────────────────►

 ┌─────────────┐      ┌──────────────────────────┐      ┌─────────────────┐
 │  In-house   │      │   Off-the-shelf software  │      │ Off-the-shelf   │
 │  software,  │      │   in-house operations     │      │ software,       │
 │  in-house   │      │   (e.g., self-hosted DB   │      │ outsourced ops  │
 │  operations │      │      on IaaS)             │      │ (e.g., cloud    │
 │  (e.g., app │      │                           │      │  services/SaaS) │
 │    code)    │      │                           │      │                 │
 └─────────────┘      └──────────────────────────┘      └─────────────────┘

---

Pros and Cons of Cloud Services

Using a cloud service essentially outsources the operation of that software to the cloud provider.

Arguments for cloud services:

• Saves time and money if you lack experience deploying and operating the required system
• Particularly valuable when load varies a lot over time — cloud services can scale up or down on demand
• Lets your team focus on higher-level concerns rather than basic system administration
• Analytical systems especially benefit: query resources can be provisioned on demand and released when idle

Arguments against cloud services:

• No control: If a feature is missing, you generally cannot implement it yourself; you can only ask the vendor
• Black box: If the service goes down or has a performance issue, you usually have no access to internals, logs, or metrics
• Vendor lock-in: If the service shuts down or becomes unacceptably expensive, migration is costly — especially when no standard APIs exist
• Geopolitical risk: Political conflicts between countries can lock you out of services
• Trust: The cloud provider must be trusted to keep data secure, which can complicate privacy and security compliance

Despite these risks, it has become increasingly popular to build new applications on top of cloud services, or to adopt a hybrid approach. However, cloud services will not subsume all in-house data systems. Very latency-sensitive applications such as high-frequency trading require full control of the hardware.

---

Cloud Native System Architecture

The term cloud native describes an architecture designed to take advantage of cloud services. Systems designed from the ground up to be cloud native have demonstrated several advantages: better performance on the same hardware, faster recovery from failures, ability to quickly scale computing resources to match load, and support for larger datasets.

Table 1-2. Examples of self-hosted and cloud native database systems

---

Layering of Cloud Services

In a cloud, traditional software can be run in an IaaS environment using VMs. In contrast, cloud native services build upon lower-level cloud services to create higher-level services. For example:

• Object storage services (Amazon S3, Azure Blob Storage, Cloudflare R2) store large files and automatically distribute data across many machines, so you don't have to worry about running out of disk space.
• Many other services are built upon object storage. For instance, Snowflake is a cloud-based analytical data warehouse that relies on S3 for data storage.

---

Separation of Storage and Compute

In traditional computing, the same computer is responsible for both storage (disk) and computation (CPU and RAM). In cloud native systems, these two responsibilities have become separated (disaggregated):

• Object stores (e.g., S3) only store files; analysis code runs elsewhere
• Cloud native systems avoid virtual disks and instead build on dedicated storage services optimized for particular workloads
• Cloud native systems are often multitenant: data and computation from several customers are handled on the same shared hardware

---

Operations in the Cloud Era

Traditionally, operations involved significant work at the level of individual machines: capacity planning, provisioning new machines, installing OS patches, etc. Many cloud services present an API that hides the individual machines implementing the service, shifting operations focus toward:

• Setting up automation, preferring repeatable processes over manual one-off jobs
• Using ephemeral VMs and services rather than long-running servers
• Enabling frequent application updates
• Learning from incidents
• Preserving the organization's knowledge about the system

Capacity planning becomes financial planning, and performance optimization becomes cost optimization. Integration among services becomes a particular challenge as a growing number of vendors offer an ever broader range of cloud services targeting different use cases.

---

Distributed Versus Single-Node Systems

A system that involves several machines communicating via a network is called a distributed system. Each of the processes participating is called a node. Reasons to use distributed systems include:

---

Problems with Distributed Systems

Distributed systems also have significant downsides:

• Every network request must deal with the possibility of failure — timeouts, overloads, crashes
• Making a call to another service is vastly slower than calling a function in the same process; sometimes a single-threaded program on one computer can outperform a cluster with over 100 CPU cores
• Troubleshooting is often difficult — diagnosing where a slowness originates requires observability tooling (OpenTelemetry, Zipkin, Jaeger)
• Maintaining data consistency across multiple services' databases becomes the application's problem

For all these reasons, performing a task on a single machine is often much simpler and cheaper than setting up a distributed system. When combined with single-node databases such as DuckDB, SQLite, and KùzuDB, many workloads can now run on a single node.

---

Microservices and Serverless

The most common way of distributing a system is to divide it into clients and servers communicating via HTTP. This approach is known as a service-oriented architecture (SOA); more recently refined into a microservices architecture.

In a microservices architecture:

• Each service has one well-defined purpose and exposes an API
• Each service has one team responsible for its maintenance
• Services have their own databases and do not share databases between services

Advantages of microservices:

• Each service can be updated independently
• Each service can be assigned the hardware resources it needs
• Implementation details are hidden behind an API

Disadvantages of microservices:

• Testing a service can be complicated as it requires all dependent services to be running
• Each service requires infrastructure for deploying, scaling, monitoring, and alerting
• APIs can be challenging to evolve without breaking clients

Microservices are primarily a technical solution to a people problem: allowing different teams to make progress independently. This is valuable in a large company, but in a small company, using microservices is likely unnecessary overhead.

Serverless (Function as a Service / FaaS) is another approach in which the cloud vendor automatically allocates and frees hardware resources as needed, based on incoming requests. You pay only for the time that your application code is running. Trade-offs include function execution time limits and potential slow start times.

---

Cloud Computing Versus Supercomputing

---

Data Systems, Law, and Society

As data systems engineers, serving the needs of our own business is not enough; we also have a responsibility toward society at large.

One particular concern is systems that store data about people and their behavior. Key regulations include:

• GDPR (2018) — gives residents of many European countries greater control and legal rights over their personal data
• CCPA — similar privacy regulation in California
• EU AI Act — places further restrictions on how personal data can be used in AI systems

Automated systems increasingly make decisions that have profound consequences for individuals: who should be given a loan or insurance coverage, who should be invited to a job interview, or who should be suspected of a crime.

Legal Challenges for Data System Design

Legal considerations are influencing the very foundations of data system design. For example:

• The GDPR grants individuals the right to be forgotten — but many data systems rely on immutable constructs such as append-only logs. How can we ensure deletion of data in the middle of a file that is supposed to be immutable?
• How do we handle deletion of data that has been incorporated into derived datasets, such as training data for ML models?

The principle of data minimization (Datensparsamkeit) runs counter to the "big data" philosophy of storing lots of data speculatively in case it turns out to be useful. The GDPR mandates that personal data:

• May be collected only for a specified, explicit purpose
• Cannot later be used for any other purpose
• Must not be kept for longer than necessary

Beyond Regulation

When data could reveal criminalized behaviors (e.g., seeking an abortion in several US states), storing that data creates real safety risks for users. Travel to an abortion clinic, for example, could easily be revealed by location data or a log of IP addresses.

Businesses have also taken notice. Key industry compliance standards include:

• PCI DSS — Payment Card Industry standards for payment processing
• SOC Type 2 — Service Organization Control standards verified by third-party audits

In general, it is important to balance the needs of your business against the needs of the people whose data you are collecting and processing.

---

Summary

The theme of this chapter has been to understand trade-offs — that is, to recognize that many questions do not have one right answer, but several possibilities that each have pros and cons.

We explored the following key topics:

1. Operational vs. Analytical Systems — They differ not only in managing different types of data with different access patterns, but also in serving different audiences. Along the way, we encountered the concepts of a data warehouse and data lake, which receive data feeds from operational systems via ETL.

1. Cloud vs. Self-Hosting — Which approach is more cost-effective depends a lot on your particular situation, but cloud native approaches are bringing big changes to the way data systems are architected — for example, in the way they separate storage and compute.

1. Distributed vs. Single-Node Systems — In some situations you can't avoid going distributed, but it's advisable not to rush into making a system distributed if it's possible to keep it on a single machine.

1. Data Systems, Law, and Society — A data system's architecture is determined not only by the needs of the business, but also by privacy regulations that protect the rights of the people whose data is being processed. How we translate legal requirements into technical implementations has not yet been formalized, but it's important to keep this question in mind.