Data Generation in Source Systems

In one sentence

You think data engineering starts at "ingestion"; in fact it starts in an upstream system you do not own and cannot control. The chapter's point: source systems are built for the business, not for analytics. A data engineer must understand how they produce data (databases, APIs, logs, messages and streams), extract it in a way that neither hurts the source nor loses history (log-based CDC first), and agree on change rules with the system's owners up front.

Glossary

Where it sits

The first chapter of Part II, "The Data Engineering Lifecycle in Depth", covering the first stage of the Chapter 2 lifecycle: generation. Chapter 6 (storage) and Chapter 7 (ingestion) follow. In practice it is the upstream half of "get the data out of the application database, the SaaS API, the event logs and Kafka".

What problem this chapter solves

Data engineers consume source systems but do not own them. The authors keep insisting that you should know a source system as well as your own: how it generates data, how often, at what volume, whether its structure may change without warning. Otherwise everything downstream is built on sand. Not knowing the source typically fails in three ways:

• You knock over production. OLTP databases are tuned for many concurrent single-row reads and writes. Picture an order database taking a few thousand writes per second at peak, with queries measured in milliseconds; an analytical query scanning hundreds of millions of rows competes with checkout for CPU and disk and drags latency up.
• Silent breakage. Upstream renames a field or changes its type without telling anyone. The pipeline either fails or, worse, keeps running and computes the wrong answer.
• Lost history. When a table that stores only current state is copied on a schedule, intermediate states between copies and deleted rows are invisible.

The core ideas, explained

1. What source systems look like: five main shapes

Data is first split into analog data (things happening in the real world, such as speech or handwriting) and digital data (converted from analog or native to a system, such as an online order or a sensor reading). Once digital, the source systems a data engineer meets fall into five kinds:

• Files and unstructured data: Excel, CSV, JSON, text, images; still a huge share of data exchange.
• APIs: pulling data over a network interface by agreed rules, common with SaaS products.
• Application databases (OLTP): they hold application state; the most central kind.
• Logs: records of events that happened in a system.
• Messages and streams: events flowing between systems in real time.

2. Application databases: OLTP, ACID and two write patterns

OLTP (online transaction processing) databases hold the application's current state: balances, inventory, orders. They are built for high concurrency, low latency, and a few rows per read or write. Most support ACID: atomicity (a transaction happens entirely or not at all), consistency (the book defines it as: any read returns the last written version of the item), isolation (concurrent transactions don't interfere), durability (once committed, it survives a power cut). By contrast, OLAP (online analytical processing) systems are built for large scans and aggregations. Running analytics directly on OLTP is slow and hurts the business, which is why you extract the data, or at least go through a read replica.

How a source table is written decides what you can get:

• CRUD pattern: create, read, update, delete, overwriting in place. The table only ever holds current state; keeping history takes extra machinery.
• Insert-only pattern: every change inserts a new timestamped record and old rows are never touched. History comes for free; the price is a table that keeps growing, and "what is this customer's address now" means finding the latest record first.

3. Change data capture (CDC): the mechanism to take away

CDC means extracting every change (insert, update, delete) from a database. The book describes two kinds.

Batch-oriented CDC: add an updated_at column and periodically query "rows changed since last time". Simple to build, but you only see each row's final state at query time: a row changed three times between two queries shows up once, as its third version, and physically deleted rows never appear at all.

Continuous CDC: read the database's write-ahead log directly (PostgreSQL's WAL, MySQL's binlog) and turn every committed change into an event, usually written to a streaming platform such as Kafka. Every intermediate state and every delete is captured, latency can drop below a second, and reading the log does not scan tables the way a big query does. It shares its roots with replication: replicas have always kept up with the primary by replaying the log.

CDC is not free either: reading the log still uses the source database's CPU, disk and network. In practice two more things need managing. In PostgreSQL, for example, a replication slot that nobody consumes makes the log pile up until the disk fills. And CDC only gives you changes, so a first connection needs a full snapshot that is then stitched onto the log.

4. Logs: who did what, and when

A log records events in a system: who (a user or system account), what (the event and its metadata), and when (a timestamp). By encoding there are three kinds: binary (such as database logs; compact but need special parsing), semistructured (such as JSON; machine-readable and the most common), and plain text (console output; the hardest to parse). You also care about resolution (how much is recorded: just a primary key, or the full row before and after) and latency (flushed in batches or pushed in real time). Database logs matter so much precisely because they underlie both CDC and replication.

5. Messages, streams, and three kinds of time

The book separates the two: a message is a piece of data passed between two systems and is usually removed from the queue once consumed; a stream is an append-only log of event records that are retained for a period, so several consumers can each read from different positions and even replay.

Streaming platforms such as Kafka group events into topics, and split topics into partitions processed in parallel. Events are routed by a partition key (such as user ID): the same key always lands in the same partition, so ordering is guaranteed only within a partition. Pick a poor key, say one big customer generating half of all events, and you get hotspotting: one partition is crushed while the rest sit idle.

Know your delivery guarantee too: at-least-once can produce duplicates, so downstream must be idempotent; exactly-once is achievable but depends on the platform and the consumer cooperating, at a cost in throughput and complexity.

That leads to the chapter's three kinds of time: event time (when it happened at the source), ingestion time (when it entered the ingestion system), and processing time (when the pipeline processed it). The gaps between them are latency. The authors' advice is plain: timestamp every one of them, and compute business metrics by event time from the start, or a phone that was offline for hours will land its data on the wrong day.

6. Practical details: database kinds and API styles

Beyond relational databases the book names several NoSQL families: key-value stores (very fast reads and writes by key, often caches and sessions), document stores (nested JSON-like documents, flexible structure), wide-column stores (such as Cassandra, for massive write volumes), graph databases (nodes and relationships), search engines (full-text retrieval), and time-series databases (time-ordered metrics). For a data engineer the questions never change: how do I get the data out, will extraction hurt the source, and is there a CDC-like change feed.

On APIs: REST is the most common, fetching by resource and usually requiring you to handle pagination and rate limits; GraphQL lets the caller declare exactly which fields it wants in one request; a webhook is a "reverse API" where the source pushes events to you as they happen, so your receiver must not drop them; gRPC uses efficient binary transport and suits internal service-to-service calls. There is also data sharing (cloud warehouses granting direct read access without copying) and third-party data (purchased external data whose quality and refresh cadence need extra checking).

7. Working upstream: you don't own it, so agree on terms

The chapter's most practical part is working with source-system stakeholders. Data engineers should agree with system owners on SLAs/SLOs for availability and freshness, how much notice a schema change gets, and whom to call when things go wrong. The cross-cutting undercurrents apply to sources too: security (credentials in a secrets manager, VPN or private links, read-only access), data management (who is accountable for data quality), DataOps (monitor the source's availability and changes, not only your own pipeline), and orchestration (align extraction cadence with when the source actually produces data).

Key trade-offs and choices

Table 1 · Extracting from a database: four approaches

Table 2 · How source tables are written

Table 3 · Message queue vs event-streaming platform

Rule of thumb: first ask whether downstream needs intermediate states and deletes; if so, use log-based CDC. Then ask how fresh it must be; daily freshness is fine with snapshots or batch increments. Finally ask whether it will hurt the source; whatever the approach, never full-scan the primary at peak.

Why it matters / in real systems

A common interview and design-review question: "How do you sync the order database into the warehouse and the search index?" A passing answer is a scheduled SELECT ... WHERE updated_at > last_run. A good answer names its flaws (missed deletes, missed intermediate states, load on the primary), moves to CDC from the binlog or WAL into Kafka, then explains how the initial snapshot is stitched on, how the partition key keeps each order's events in sequence, and how consumers stay idempotent. Another frequent trap is the dual write: the application writes to the database and to a message queue, and if either fails the two disagree. Log-based CDC exists precisely to remove the dual write.

Pitfalls and debates

• "An updated_at column means we have CDC." Batch increments miss deletes and intermediate states, and depend on upstream updating the column every single time.
• "Querying a read replica has zero impact on the source." Replicas still have resource limits and replication lag; big queries can still slow them down.
• "Once CDC is wired up, we're done." Initial snapshots, log buildup and source schema changes need ongoing care.
• "Kafka guarantees global ordering." Only within a partition; events for one entity must share a partition key.
• "Exactly-once is the default." The common default is at-least-once; without idempotent consumers you double count.
• "Schemaless means no schema to worry about." The structure is merely deferred from write time to read time; changes still break downstream.
• "Event time equals arrival time." Offline devices, retries and time zones can push them hours apart.
• Limits of its time. The book came out in 2022. Its "agree on rules with upstream" idea has since been formalized in industry discussion as the data contract, and managed CDC services are more common, but snapshot stitching and schema evolution remain hard.

Key takeaways