Trade-Offs in Data Systems Architecture

DDIA 2nd edition — Chapter 1 notes. (Complete — only book Summary left, skippable)

Sections covered: Operational vs Analytical ✅ · Characterizing Txn/Analytics ✅ · Data Warehousing ✅ · Systems of Record & Derived Data ✅ · Cloud vs Self-Hosting ✅ · Pros/Cons of Cloud ✅ · Cloud Native Architecture ✅ · Storage/Compute Separation ✅ · Operations in the Cloud Era ✅ · Distributed vs Single-Node ✅ · Problems with Distributed Systems ✅ · Microservices & Serverless ✅ · Cloud Computing vs Supercomputing ✅ · Data Systems, Law & Society ✅

Remaining: Summary ⬜ (book recap — skip)

Data-Intensive vs Compute-Intensive

• Data-intensive system - data management is one of the primary challenges in developing the application.
• Compute-intensive system - the challenge is parallelizing a very large computation.

Building blocks of a data-intensive application

• Databases - store data so that they, or another application, can find it again later.
• Caches - remember the result of an expensive operation, to speed up reads.
• Search indexes - allow users to search data by keyword or filter it in various ways.
• Stream processing - handle events and data changes as soon as they occur.
• Batch processing - periodically crunch a large amount of accumulated data.

Operational vs Analytical Systems

People who work with data

• Backend engineer - builds services that handle requests for reading and updating data; these services often serve external users, either directly or indirectly via other services.
• Business analysts - generate reports about the activities of the organization to help management make better decisions (business intelligence, or BI).
• Data scientists - look for novel insights in data or create user-facing product features enabled by data analysis and ML/AI (e.g., "people who bought X also bought Y" recommendations, predictive analytics such as risk scoring or spam filtering, and ranking of search results).
• Data engineers - know how to integrate the operational and analytical systems and take responsibility for the organization's data infrastructure more widely.
• Analytics engineers - model and transform data to make it more useful for the business analysts and data scientists in an organization.

System types

• Operational systems - the backend services and data infrastructure where data is created (e.g., 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 are optimized for the types of data processing needed for analytics.

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

OLTP (Online Transaction Processing)

An operational system typically looks up a small number of records by a key (a point query). Records are inserted, updated, or deleted based on the user's input. Because these applications are interactive, this access pattern became known as online transaction processing (OLTP).

OLAP (Online Analytical Processing)

The reports that result from analytical queries are important for BI, helping management decide what to do next. To differentiate this pattern of using databases from transaction processing, it has been called online analytical processing (OLAP).

Operational (OLTP) vs Analytical (OLAP) - comparison

Product / real-time analytics

Another type of system is designed for analytical workloads (queries that aggregate over many records) but embedded into user-facing products. Known as product analytics or real-time analytics, these include Pinot, Druid, and ClickHouse. They ingest data in real time and are optimized for low-latency query responses. In contrast, traditional OLAP systems typically ingest data in batches and are optimized for high-throughput query processing.

Data Warehousing

In the late 1980s and early 1990s, a trend arose for companies to stop using their OLTP systems for analytics and to run 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 (a problem known as 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.

Data warehouses often store data very differently from OLTP databases, to optimize for analytics queries. The 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 (using either a periodic data dump or a continuous stream of updates), 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 order of the transform and load steps is swapped (the transformation is done in the data warehouse, after loading), resulting in ELT.

flowchart TB
    subgraph Users
        C([Customer])
        W([Warehouse worker])
        T([Truck driver])
    end

    subgraph Operational["Operational systems"]
        E[Ecommerce site]
        S[Stock-keeping app]
        V[Vehicle route planner]
        SDB[(Sales DB)]
        IDB[(Inventory DB)]
        GDB[(Geo DB)]
        E --> SDB
        S --> IDB
        V --> GDB
    end

    subgraph Analytical["Analytical systems"]
        TR1[Transform]
        TR2[Transform]
        TR3[Transform]
        BA([Business analyst])
        DW[(Data warehouse)]
        TR1 -->|Load| DW
        TR2 -->|Load| DW
        TR3 -->|Load| DW
        BA -->|Query| DW
    end

    C --> E
    W --> S
    T --> V
    SDB -->|Extract| TR1
    IDB -->|Extract| TR2
    GDB -->|Extract| TR3

Often the data sources of the ETL processes are external SaaS products such as customer relationship management (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.

HTAP (Hybrid Transactional/Analytical Processing)

Some database systems offer hybrid transactional/analytical processing (HTAP), which aims to enable OLTP and analytics in a single system without requiring ETL from one system into another.

Even though HTAP exists, it is common to keep transactional and analytical systems separate because of their different goals and requirements. HTAP therefore does not replace data warehouses. Rather, it is useful when the same application needs to both perform analytical queries that scan a large number of rows and read/update individual records with low latency. Fraud detection can involve such workloads.

Data Lake

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

• Feature engineering - transforming data into a form suitable for training an ML model. This often requires turning the rows and columns of a database table into a vector or matrix of numerical values called features. Done to maximize trained-model performance, it commonly requires custom code that is difficult to express in SQL.
• NLP / computer vision - using natural language processing on textual data (e.g., product reviews) to extract structured information (e.g., the author's sentiment, or topics mentioned). Similarly, extracting structured information from photos using computer vision techniques.

Many data scientists prefer not to work in a relational database such as a data warehouse. Instead, they prefer Python data analysis libraries such as Pandas and scikit-learn, statistical analysis languages such as R, and distributed analytics frameworks such as Spark.

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. Files in a data lake might be collections of database records encoded with a format such as Avro or Parquet, but a data lake can equally hold text, images, videos, sensor readings, sparse matrices, feature vectors, genome sequences, or any other kind of data. Besides being more flexible, a data lake is also often cheaper than relational data storage, since it can use commoditized file storage such as 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 the 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. It's sometimes called the sushi principle: "raw data is better."

Beyond the data lake

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 on data in an analytical system may be deployed to production so it can generate recommendations for end users, such as "people who bought X also bought Y." ML models can be deployed to operational systems using specialized tools such as TFX, Kubeflow, or MLflow.

Systems of Record and Derived Data

• System of record (also: source of truth) - holds the authoritative or canonical version of data. When new data comes in (e.g., as user input), it is first written here. Each fact is represented exactly once (the representation is typically normalized).
• Derived data system - its data is the result of taking existing data from another system and transforming or processing it. If you lose derived data, you can re-create it from the original source. Denormalized values, indexes, materialized views, transformed data representations, and models trained on a dataset all fall into this category.

Analytical systems are usually derived data systems, because they consume data created elsewhere. Operational services may contain a mixture of systems of record and derived data systems: 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 - especially for queries the system of record cannot answer efficiently.

A database is just a tool; how you use it is up to you. The distinction between a system of record and a derived data system depends not on the tool, but on the way you use it in your application. By being clear about which data is derived from which other data, you can bring clarity to an otherwise confusing system architecture.

When the data in one system is derived from the data in another, you need a process for updating the derived data when the original in the system of record changes. Unfortunately, many databases assume your application will only ever need that one database, and they do not make it easy to integrate multiple systems to propagate such updates.

Cloud vs Self-Hosting

Two important decisions: who builds the software and who deploys it. The spectrum runs from bespoke software you write and run in-house, to widely used cloud services or SaaS products implemented and operated by an external vendor and accessed only through a web interface or API.

flowchart LR
    A["In-house software,<br/>in-house operations<br/>(e.g., application code)<br/><br/>More control,<br/>greater investment"]
    B["Off-the-shelf software,<br/>in-house operations<br/>(e.g., self-hosted database on IaaS)"]
    C["Off-the-shelf software,<br/>outsourced operations<br/>(e.g., cloud services / SaaS)<br/><br/>Less control,<br/>lower investment"]
    A <--> B <--> C

The middle ground is off-the-shelf software (open source or commercial) that you self-host - for example, downloading MySQL and installing it on a server you control. This could be on your own hardware (often called on-premises, even if the server is in a rented datacenter rack) or on a virtual machine (VM) in the cloud (infrastructure as a service, or IaaS). There are more points along this spectrum, such as taking open source software and running a modified version of it.

Pros and Cons of Cloud Services

Cloud providers claim that using their services saves time and money and allows you to move faster compared to setting up your own infrastructure.

• Predictable load + existing expertise → If you already know how to set up and operate the systems you need, and your load is predictable (machine count doesn't fluctuate wildly), it's often cheaper to buy your own machines and run the software yourself.
• Unfamiliar systems → If you need a system you don't already know how to deploy and operate, adopting a cloud service is often easier and quicker than learning to manage it.
• Operational expertise → Outsourcing operation to a company that specializes in running it can yield better service, since the provider gains expertise from many customers. On the other hand, if you run the service yourself, you can configure and tune it for your particular workload.
• Variable load → Cloud services are particularly valuable if load varies a lot over time. Provisioning for peak load wastes resources that sit idle most of the time; the cloud makes it easier to scale up or down in response to demand.
• Large datasets → If querying quickly requires significant compute, the cloud can save money - you return unused resources to the provider rather than leaving them idle. For smaller datasets, this difference is less significant.

The biggest downside of a cloud service is that you have no control over it:

• If it lacks a feature you need, all you can do is politely ask the vendor to add it; you generally cannot implement it yourself.
• If the service goes down, all you can do is wait for it to recover.
• If you trigger a bug or performance problem, diagnosing it is difficult. With software you run yourself, you can get performance metrics and debugging information from the OS and look at server logs. With a vendor-hosted service, you usually don't have access to these internals.
• If the service shuts down, becomes unacceptably expensive, or the vendor changes the product in a way you don't like, you are at their mercy. Running an old version is usually not an option, so you'll be forced to migrate. This risk is mitigated if alternative services expose a compatible API, but many cloud services have no standard APIs, raising switching cost and making vendor lock-in a problem.
• If the cloud provider is in another country and a political conflict arises, you risk being locked out of the service due to imposed sanctions.
• The cloud provider must be trusted to keep data secure, which can complicate compliance with privacy and security regulations.

Despite these risks, it has become increasingly popular to build new applications on top of cloud services, or to adopt a hybrid approach in which cloud services are used for some aspects of a system.

Many older systems predate the cloud, and for services with specialist requirements that existing cloud services cannot meet, in-house systems remain necessary. For example, very latency-sensitive applications such as high-frequency trading require full control of the hardware.

Cloud Native System Architecture

The term cloud native is used to describe an architecture that is designed to take advantage of cloud services. In principle, almost any software that you can self-host could also be provided as a cloud service, and indeed such managed services are now available for many popular data systems. However, systems that have been designed from the ground up to be cloud native have been shown to have several advantages: better performance on the same hardware, faster recovery from failures, being able to quickly scale computing resources to match the load, and supporting larger datasets

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

Layering of Cloud Services

Many self-hosted data systems have simple system requirements; they run on a conventional operating system such as Linux or Windows, they store their data as files on the filesystem, and they communicate via standard network protocols such as TCP/IP. A few systems depend on special hardware such as GPUs (for ML) or remote direct memory access (RDMA) network interfaces, but on the whole, self-hosted software tends to use generic computing resources: CPUs, RAM, a filesystem, and an IP network.

In a cloud, this type of software can be run in an IaaS environment, using one or more VMs (or instances) with a certain allocation of CPUs, memory, disk, and network bandwidth.

In contrast, the key idea of cloud-native services is not only to use the computing resources managed by your operating system, but also to build upon lower-level cloud services to create higher-level services. For example:

• Object storage services such as Amazon S3, Azure Blob Storage, and Cloudflare R2 store large files. They provide more limited APIs than a typical filesystem (basic file reads and writes), but they hide the underlying physical machines: the service automatically distributes data across many machines, so you don't have to worry about running out of disk on any one machine, and even if some machines or their disks fail entirely, no data is lost.
• Higher-level services build on object storage. For instance, Snowflake is a cloud-based analytical database (data warehouse) that relies on S3 for storage, and yet other services build on top of Snowflake.

Separation of Storage and Compute

In traditional computing, disk storage is regarded as durable (we assume that once something is written to disk, it will not be lost). To tolerate the failure of an individual hard disk, RAID (redundant array of independent disks) is often used to maintain copies of the data on several disks attached to the same machine.

As an alternative to local disks, cloud services also offer virtual disk storage that can be detached from one instance and attached to a different one (e.g., Amazon EBS, Azure managed disks, and persistent disks in Google Cloud). Such a virtual disk is not a physical disk, but rather a cloud service provided by a separate set of machines that emulates the behavior of a disk (a block device, where each block is typically 4 KiB in size). This technology makes it possible to run traditional disk-based software in the cloud, but the block device emulation introduces overheads that can be avoided in systems that are designed from the ground up for the cloud . The use of virtual disks also makes the application very sensitive to network glitches, since every I/O operation on the virtual block device is a network call .

To address this problem, cloud-native services generally avoid virtual disks and instead build on dedicated storage services optimized for particular workloads:

• Large files (hundreds of KB to several GB) → object storage such as S3, optimized for long-term storage of big blobs.
• Small individual rows/values → optimized database storage services, since records are tiny compared to whole files.

Separation of storage and compute — Traditionally one machine owns both storage (disk) and computation (CPU + RAM). In cloud-native systems these two responsibilities are decoupled: S3 only stores the data, while the analysis runs on separate compute. This lets storage and compute scale independently.

Multitenancy — Cloud-native systems are often multitenant: rather than a separate machine per customer, data and computation from several customers share the same hardware behind the same service. This enables better hardware utilization, easier scalability, and easier management by the provider — at the cost of needing strong isolation (security, noisy-neighbor performance).

Operations in the Cloud Era

The people managing an organization's server-side data infrastructure were traditionally known as database administrators (DBAs) or system administrators (sysadmins). More recently, many organizations merge software development and operations into a single team that owns both the backend and its infrastructure — DevOps. Site Reliability Engineering (SRE) is Google's implementation of this idea.

For self-hosted systems, operations traditionally involves significant per-machine work: capacity planning (monitoring disk space, adding disks before you run out), provisioning machines, moving services between machines, and installing OS patches.

Many cloud services present an API that hides the individual machines. For example, cloud storage replaces fixed-size disks with metered billing — you store data without planning capacity in advance and are charged by space used.

The DevOps/SRE philosophy emphasizes:

• Automation, preferring repeatable processes over manual one-off jobs.
• Ephemeral VMs and services rather than long-running servers.
• Frequent application updates.
• Learning from incidents.
• Preserving organizational knowledge about the system as people come and go.

Cloud customers still need operations, but the focus shifts: choosing the right service for a task, integrating services, and migrating between them. Metered billing removes traditional capacity planning, but you must still track what resources you use and why, to avoid waste — capacity planning becomes financial planning, and performance optimization becomes cost optimization. Cloud services also impose quotas (e.g., max concurrent processes) you must plan around.

Distributed vs Single-Node Systems

A distributed system is several machines communicating via a network; each participating process is a node. Reasons to go distributed:

• Inherent distribution — if an app has multiple users on their own devices, it's unavoidably distributed.
• Requests between cloud services — data stored in one service and processed in another must cross the network.
• Fault tolerance / high availability — keep working even if one machine goes down.
• Scalability — when data volume or compute exceeds what one machine can handle, spread load across many.
• Latency — place servers around the world so each request is served from the closest one, cutting user wait time.
• Elasticity — scale up/down with demand to optimize cost (easier when distributed).
• Specialized hardware — match hardware to workload: object stores use many-disk/few-CPU machines, analytics uses high-CPU/high-RAM, ML uses GPUs.
• Legal compliance — some countries require their citizens' data to be stored and processed in-country (data residency).
• Sustainability — flexibility on where/when to run jobs lets you favor times/places with abundant renewable power and avoid straining the grid.

Problems with Distributed Systems

Distributed systems have real downsides. Every request and API call across the network must handle the possibility of failure.

• Network is slow + unreliable — datacenter networks are fast, but a cross-service call is still vastly slower than an in-process function call. At scale, it's often faster to bring the computation to the data than to ship data to a separate processing machine.
• More nodes ≠ faster — sometimes a single-threaded program beats a 100+ CPU cluster (coordination/network overhead dominates).
• Hard to troubleshoot — diagnosis falls under observability: collect data about system execution and make it queryable, so both high-level metrics and individual events can be analyzed. Tracing tools (OpenTelemetry, Zipkin, Jaeger) track which client called which server, for which operation, and how long each call took.
• CPUs, memory, and disks have grown larger, faster, and more reliable. When combined with single-node databases such as DuckDB, SQLite, and KuzuDB, many workloads can now run on a single node.

Microservices and Serverless

The most common way to distribute a system is the client/server split: clients make requests to servers. The same process can be both — a server for incoming requests and a client for outbound calls to other services.

This style was traditionally called service-oriented architecture (SOA); refined more recently into microservices:

• Each service has one well-defined purpose (e.g., S3 = file storage), exposes a network API, and is owned by one team. A complex app decomposes into many interacting services, each independently managed.
• Cost: many services breed complexity. Testing needs all dependencies running; each service needs infra for releases, scaling, logs, health monitoring, and on-call alerting. Orchestration frameworks like Kubernetes provide that foundation.
• Key insight: microservices are primarily a technical solution to a people problem — letting teams progress independently without constant coordination.

Serverless / Function as a Service (FaaS) — infrastructure management is outsourced to the cloud vendor, which automatically allocates and frees resources based on incoming requests. It brings metered billing to code execution: you pay only for the time your code actually runs, not for provisioned-in-advance capacity.

"Serverless" is a misleading name — each execution still runs on a server, just possibly a different one each time. Services like BigQuery and managed Kafka also adopt "serverless" branding to signal autoscaling + usage-based billing rather than per-instance pricing.

Cloud Computing vs Supercomputing

Cloud computing is not the only way to build large-scale systems. The alternative is high-performance computing (HPC), aka supercomputing. They overlap, but differ on priorities and techniques:

Why it matters: DDIA studies the cloud model — partial failure is the norm and you keep serving through it. The HPC "stop-and-restart-from-checkpoint" fault model is the wrong mental model for the always-on services these chapters target.

Data Systems, Law and Society

Legal and ethical considerations now shape the foundations of data-system design, not just its policies.

Law collides with immutable architecture

• Right to erasure — GDPR grants individuals the right to be forgotten (data erased on request). But many data systems are built on immutable constructs like append-only logs. Tension: how do you delete one record in the middle of a file that is supposed to be immutable?
• Derived-data deletion — even harder when the data has propagated into derived datasets — indexes, materialized views, or ML training data. Deleting the source doesn't unwind what was trained on it. (This recurs throughout the book.)

Storing data is a liability, not just a cost

• You store data when its value > cost of storing it — but "cost" isn't only the S3 bill.
• The real cost includes liability and reputational damage if the data leaks or is breached, plus legal fines if storage/processing is found non-compliant.
• Compelled disclosure — governments/police can force companies to hand over data. When data reveals criminalized behavior (e.g., sexuality in some jurisdictions, seeking an abortion in some US states), storing it creates real physical-safety risk for users — a clinic visit is exposed by location data, or even inferred from a log of IP addresses over time (approximate location).

Data minimization

• Once the risks are weighed, some data is simply not worth storing → delete it. This is the principle of data minimization.
• Aligns with GDPR: personal data may be collected only for a specified, explicit purpose, not repurposed later, and not kept longer than necessary.

Compliance regimes

• PCI (Payment Card Industry) — credit-card companies require payment processors to meet strict standards.
• SOC 2 Type 2 (Service Organization Control) — buyers increasingly require vendors to comply; verified by third-party audits (as with PCI).

Bottom line: balance the needs of the business against the needs of the people whose data you collect and process.

---

Interview Trade-Offs (Ch1)

Spoken-answer ammo. Each = a decision + the cost. Defend the pick out loud.

• OLTP vs OLAP → Don't run analytics on the prod OLTP DB. Expensive aggregate scans starve point queries + lock contention. Pick: separate analytical store (warehouse/replica), sync via ETL. Say this the moment an interviewer adds "...and the business team needs dashboards."
• ETL vs ELT → ETL transforms before load (clean, schema-on-write); ELT loads raw, transforms in-warehouse (flexible, schema-on-read, cheaper compute on demand). Pick ELT when consumers' needs differ ("sushi principle — raw is better").
• Warehouse vs Data Lake → Warehouse = relational + SQL + BI, rigid schema. Lake = files (Parquet/Avro), any format, cheap object storage, ML-friendly. Lake when data scientists + unstructured data; warehouse when fixed BI reporting.
• HTAP → Tempting single-system answer, but rarely replaces the split. Use only when one app needs both big scans AND low-latency record updates (fraud detection). Otherwise separation is healthier.
• System of record vs derived data → The senior framing: name which store is authoritative, which is rebuildable (index/cache/replica). If derived data is lost, you regenerate it. This clarity is what makes a design defensible — call it out explicitly.
• Cloud vs self-host → Variable/spiky load + unfamiliar ops → cloud (elastic, outsourced expertise). Predictable load + existing ops skill + latency-critical (HFT) → self-host (cheaper at steady state, full hardware control). The axis is control + investment vs speed + elasticity, plus vendor lock-in risk.
• Separation of storage & compute → Decoupling them (S3 stores, separate nodes compute) lets each scale independently and survives node loss without data loss. Cost: every I/O is now a network call — latency-sensitive workloads suffer. Don't decouple if you need local-disk speed.
• Distributed vs single-node → Distribute only when you must (fault tolerance, scale beyond one box, geo-latency, compliance). Cost: every network hop can fail, debugging is hard, and more nodes ≠ faster — a single-threaded program can beat a 100-CPU cluster when coordination dominates. Senior signal: justify why distributed, don't default to it.
• Microservices vs monolith → Microservices solve a people problem (independent teams), not a performance one. Cost: distributed-systems complexity, harder testing, infra overhead per service. Pick a monolith until team-coordination pain is the actual bottleneck.
• Serverless / FaaS → Pay-per-execution + autoscale, zero capacity planning. Cost: cold starts, vendor lock-in, hard local debugging, runtime limits. Good for spiky/event-driven workloads; bad for steady high-throughput or latency-floor-sensitive paths.
• Cloud vs HPC fault model → Don't borrow the supercomputing mental model for online services. HPC tolerates faults by stop-cluster → repair → restart from checkpoint; cloud services must keep serving through partial failure. Senior signal: name which fault model the system needs — always-on user-facing → design for partial failure, never "halt and restart."
• Store ≠ free → data minimization → Storing data is a liability (breach/reputational risk, legal fines, compelled disclosure, user safety), not just an S3 bill. Pick: collect for a specified purpose, don't repurpose, delete when value < risk. Drop it the moment an interviewer raises PII/GDPR/"do we even need to keep this?"
• Immutable logs vs right-to-erasure → Append-only/immutable designs (great for auditability, replication, CDC) collide with GDPR deletion — including data already baked into indexes/materialized views/ML training sets. Cost of immutability: deletion becomes a hard, designed-for problem (crypto-shredding, tombstones, compaction), not a free DELETE.

See [[synthesis/DDIA Interview Map]] for the full chapter → SD-topic map.

Next: Hands-On Exercise

Design drill — "...and the business team needs dashboards"

Take any read/write app you know (e.g., an e-commerce order service). Sketch the operational (OLTP) design, then have an imaginary interviewer drop the dashboards requirement. Defend out loud:

1. Why not query the prod DB directly? (aggregate scans starve point queries, lock contention, data spread across services).
2. What's your pipeline? Name OLTP → ETL/ELT → warehouse vs lake; pick one and justify (schema-on-write vs schema-on-read, who consumes it).
3. How does derived data stay fresh? Batch dump vs CDC stream; what's your system of record vs derived store, and how do you rebuild the derived side if it's lost?

Hands-on — watch an analytical query strangle OLTP (~1-2 hrs)

Goal: feel why OLTP and OLAP get separated.

1. docker run Postgres locally. Load a chunky table (generate_series → ~5-10M rows).
2. Fire a steady stream of point queries (SELECT ... WHERE id = $1) in one terminal; measure p50/p99 latency.
3. In another terminal, run a heavy aggregate (GROUP BY over the whole table). Watch the point-query p99 spike.
4. Now add a read replica (or a second Postgres restored from a dump), point the aggregate at it, and confirm the OLTP p99 recovers.
5. Done when: you have before/after p99 numbers showing isolation helped — and can explain it in one sentence.

Log results in this folder (System Design/DDIA/) and drop the verdict + time spent in the current Weekly Tracker.