Data Engineering and Database Engineering
The seam is the write-ahead log. Almost every mechanism a source database uses to stay correct decides what a pipeline downstream of it is able to promise.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
Which parts of database internals does a data engineer genuinely have to understand, and which are somebody else's depth?
The engineer who is paged because yesterday's orders are short by four hundred rows. Every plausible cause of that page — a snapshot taken at the wrong isolation level, a replication lag the extract did not account for, a log slot that was dropped, a key that was reused — is a database mechanism showing through the pipeline, and none of it is visible from the SQL in the transformation.
The unit crossing this boundary is one committed row version. Inside the database it is a tuple with a transaction id and a position in the log; on our side it becomes a change record, then an event, then a line in a file, then a fact row. The identity that survives all of those hops is the source primary key plus the log position that produced it, and a pipeline that carries only one of the two cannot answer "is this the newest version of that row".
Treat the source as a black box with a connection string. A data engineer writes SQL, a DBA worries about pages and locks, and the boundary between them is SELECT. This is a defensible division of labour and it survives right up until the first extract that silently misses rows.
The nightly extract runs WHERE updated_at > :high_water_mark. Under MVCC: Multi-Version Concurrency Control a row's updated_at is assigned by the application before the transaction commits, so a long transaction that started at 23:58 and committed at 00:03 carries yesterday's timestamp and is never seen again by any future window (Incremental Extraction, The High-Water Mark).
- The nightly extract runs
WHERE updated_at > :high_water_mark. Under MVCC: Multi-Version Concurrency Control a row'supdated_atis assigned by the application before the transaction commits, so a long transaction that started at 23:58 and committed at 00:03 carries yesterday's timestamp and is never seen again by any future window (Incremental Extraction, The High-Water Mark). - A full-table snapshot is read in chunks at
READ COMMITTED. Each chunk sees a different point in time, so a row that moved between chunks appears twice or not at all, and the snapshot describes a database state that never existed (Isolation Levels, Snapshot and Stream: the Bootstrap Problem). - The CDC connector is paused for a maintenance window. The source reclaims log segments the connector had not read, and the changes in them are not late — they are gone, and the only repair is a fresh snapshot (Write-Ahead Logging, CDC Failure Modes and the Retention Deadline).
- To spare the primary, the extract reads a replica. The replica is behind, the extract still finishes, the row counts still look plausible, and the day is quietly truncated at whatever point the replica had reached (Replication and Read Scaling, Ingestion Failure & Recovery).
- The source table is sharded by customer. The extract job was configured with one shard's connection string, and the resulting dataset is complete, internally consistent, and covers a third of the business (Partitioning and Sharding).
- A soft delete sets
deleted_at, a hard delete removes the row. The pipeline sees the first as an update and never sees the second at all, so the warehouse accumulates rows the source no longer has (What a CDC Event Contains, Deletion Requests).
What is actually happening
- The write-ahead log is the seam. It exists so the database can recover after a crash — every change is durable in the log before it is applied to pages. Logical decoding re-reads that log and turns it into a stream of row changes, which is the entire basis of Change Data Capture. Everything the log promises (commit order per source, durability) the pipeline inherits; everything it does not (retention beyond a bound, ordering across shards) the pipeline does not get either (Write-Ahead Logging).
- MVCC is what makes a consistent snapshot possible at all. A snapshot read is a view of one transactional instant, and that instant has a log position. Stitching a snapshot to a stream correctly means starting the stream at exactly that position — earlier means duplicates, later means a permanent hole (MVCC: Multi-Version Concurrency Control, Snapshot and Stream: the Bootstrap Problem).
- Isolation level is an ingestion parameter, not a database detail. It decides whether your extract of
ordersand your extract oforder_itemsdescribe the same moment, and therefore whether the join in your transformation is meaningful (Isolation Levels, Transactions and ACID). - Indexes and analytical pruning solve the same problem with different machinery. An OLTP index gets you to a few rows without reading the rest; a partitioned, sorted, statistics-carrying analytical layout lets a scan skip files without reading them. Both are "read less"; only one of them survives a query that touches a third of the table (Why Is This Query Slow? Indexes, Partition Pruning, Parquet Internals).
- Partitioning means two different things either side of the boundary. In the source it is manageability and hot-data separation; in the lake it is the pruning key that decides how much a query scans and how many files exist. Copying the source's partitioning into the warehouse is one of the most common ways to inherit a layout that fits nobody (Partitioning and Sharding, Partitioning, Partition Cardinality).
- LSM compaction and file compaction are the same idea twice. Both accumulate small immutable pieces because writing in place is expensive, and both need a background merge or read cost grows without bound. If you understand why an LSM Trees: Why Some Engines Favour Writes store compacts, you already understand why a streaming ingest into a table format has to (File Compaction, Open Table Formats).
- Normalization is a write-side correctness technique. It removes the possibility of contradicting yourself. Analytical modelling deliberately gives that up for read simplicity and scan efficiency, and the discipline is knowing which one you are doing (Normalization: 1NF to BCNF, Denormalization on Purpose, Star Schema).
- Query plans do not disappear in the warehouse. Cost-based optimisation, join algorithm selection and statistics are the same field of study whether the engine serves point reads or scans; the constants change, the reasoning does not (Cost-Based Optimization, Join Algorithms: Nested Loop, Hash, Merge, Query Optimizers).
Where the boundary sits
Database Engineering asks how data is stored, indexed, queried, transacted and replicated inside one system. This domain asks how data moves *between* systems and what survives the trip. The two overlap on a small, dense set of mechanisms, and those mechanisms are worth knowing exactly because every one of them can silently cost you rows.
The table below is the map. Read the third column as the actual point of contact: it is not "these topics are related", it is "this specific mechanism, running inside the database for a reason that has nothing to do with analytics, decides what your pipeline can promise".
The rule of thumb for the rest of your career on this boundary: anything about how one system keeps itself correct is theirs; anything about what a second system can conclude from the first is ours. A B-tree is theirs. What an index makes possible for an incremental extract is ours.
| We teach | Depth lives in | The mechanism that crosses |
|---|---|---|
| Reading a change log as a data source | Database Engineering: Write-Ahead Logging, Crash Recovery | The log is written for durability. CDC re-purposes it, so the pipeline inherits its ordering and its retention — including the retention running out. |
| Snapshotting a table consistently | Database Engineering: MVCC: Multi-Version Concurrency Control, Isolation Levels | A snapshot is one transactional instant with a log position. That position is what lets a snapshot and a stream be stitched into one history. |
| Extracting only what changed | Database Engineering: Why Is This Query Slow? Indexes, Index Types: B-tree, Hash, Partial, Expression, Covering, Full-Text | An index on the watermark column is what makes an incremental extract cheap. Without it, "only what changed" is a full scan with extra steps. |
| Choosing an analytical partition key | Database Engineering: Partitioning and Sharding | The word is shared and the objective is not. Source partitioning manages a live table; analytical Partitioning decides how much a query has to read. |
| Keeping file counts under control | Database Engineering: LSM Trees: Why Some Engines Favour Writes, Compaction: The Merge That Pays for Cheap Writes | Small immutable pieces accumulate and reads degrade. The lake solved it the same way the storage engine did, one abstraction level up (File Compaction). |
| Modelling facts and dimensions | Database Engineering: Normalization: 1NF to BCNF, Denormalization on Purpose | The source normalizes to prevent contradiction on write. We denormalize to make reads simple and scans efficient, and that is a deliberate exchange (Star Schema). |
| Making an analytical query fast | Database Engineering: Cost-Based Optimization, Join Algorithms: Nested Loop, Hash, Merge | The optimiser exists in both worlds and reads the same way. What changes is that the analytical plan's dominant cost is bytes moved, not rows located (Reading EXPLAIN ANALYZE). |
| Reading a source under load | Database Engineering: Replication and Read Scaling, Replication Internals: WAL Shipping, LSNs, Lag and Failover | A replica read protects the primary and adds the replica's lag to your completeness, silently, at exactly the busiest time. |
| Following one write all the way out | Database Engineering: Follow a Write Through the Engine, Follow the Query | Their walk ends when the row is durable. Ours starts there — the same write, seen from the other side of the log (The Fundamental Data Journey). |
The write-ahead log is the seam
If you take one image away from this lesson, take this one. The database writes every change to a log before applying it, so that a crash loses nothing. That log is an ordered, durable, complete record of what happened — which is precisely what a data pipeline wants and cannot get by asking the table questions.
CDC is the decision to read that log instead. It is a beautiful re-use of an existing mechanism and it comes with the mechanism's constraints attached. The log is bounded. It is ordered per source, not per table and not per business entity. It contains physical or logical change records, not application intent — an UPDATE that set status = 'shipped' arrives as a before-image and an after-image, and the fact that a human called it "shipping the order" is not in there (Commands vs Events).
The second half of the seam is the snapshot. A change stream tells you what happened from the moment you started reading; it says nothing about the rows that were already there. So every log-based pipeline begins with a consistent snapshot taken at a known position, and the correctness of the whole system reduces to whether the stream resumes at exactly that position (Snapshot and Stream: the Bootstrap Problem).
The checks that require database knowledge to write
A data quality check is only as good as the assumption behind it, and on this boundary the assumptions are all database ones. "Compare row counts with the source" is not one check — it is a family of checks that differ by *when* each side was measured and *from which copy*.
The rows below are ordered from cheapest to most specific. Notice how much of each misses column is a database concept: transactions that were never observed, soft deletes, keys that are only unique per tenant, a replica that answered on the source's behalf.
The blind spot they share is that all of them compare a pipeline to a source. None of them can tell you the source itself is wrong, which is the failure that reconciles perfectly and still ends up in a board pack (Two Dashboards, Two Numbers).
| Check | Expresses | Catches | Still misses |
|---|---|---|---|
| Row count for a closed day, source versus landed, both taken at a recorded log position | Everything committed in that period arrived exactly once. | Change-stream gaps, an extract window that closed early, a shard that was never configured, duplicate delivery that was not deduplicated. | Rows inserted and deleted within the same period, which neither side ever observed; any period still open; a count taken from a lagging replica, which agrees with the pipeline because both are behind. |
| Sum of a monetary column for a closed month, source versus warehouse | The values, not just the rows, survived the trip. | Fan-out joins that multiplied rows, sign or currency-unit errors in a cast, rows dropped by a filter. | Compensating errors that net to zero; any column not summed; a change of meaning — a column that switched from gross to net reconciles beautifully and reports the wrong thing. |
| Connector position versus the source's current log position | Change capture is caught up, so absence of data means absence of changes. | A stalled or crashed connector, a slot growing towards the source's disk limit, a consumer that cannot keep up with write volume. | A connector that is caught up because its publication or table filter silently excludes a table — it is perfectly current on data it is not reading (CDC Failure Modes and the Retention Deadline). |
| Uniqueness of source primary key in the landed table | One row per source row, whatever the delivery semantics did on the way. | At-least-once redelivery, a re-run without idempotency, an overlapping snapshot-and-stream stitch. | Duplicates whose keys differ — a re-emitted change with a new event id looks like a different row; and composite keys that are only unique per tenant, which collapse when tenants are merged into one table (Deduplication). |
| Row-level comparison of a sampled key set, source versus warehouse | The version of each row we hold is the version the source holds. | A mis-stitched snapshot, a stale merge, an update that was captured as an insert, a soft delete that was not applied. | Anything outside the sample, and any row that has changed since the sample was drawn — which makes this check useful for slow-changing tables and misleading for hot ones. |
Every row's misses column is a database mechanism showing through. That is the argument for learning them: not so you can build a storage engine, but so you can write a check whose blind spot you already know.
How to build it
Most important first.
- Read the source's change log rather than asking the source questions, wherever the source supports it. Polling can only see current state at the moment it looked; the log sees every version, including the ones that existed for four seconds (CDC vs Polling).
- Take the initial snapshot at a recorded log position and start the stream from that exact position. Write the position down in the pipeline's own state — it is the only thing that makes the snapshot and the stream describe one continuous history (Snapshot and Stream: the Bootstrap Problem).
- Make the extract's isolation level and read source explicit configuration, reviewed like code. "Which replica, how far behind, at what isolation" belongs in the dataset's documentation, not in a connector's defaults (Dataset Documentation).
- Monitor log retention and connector position together. Replication slot growth on the source is your problem before it is the DBA's, because the DBA's remedy is to drop the slot (CDC Failure Modes and the Retention Deadline).
- Learn to read a query plan in whichever engine you actually run. The single highest-leverage database skill in analytics is looking at a plan and seeing which stage moved the most bytes (Reading EXPLAIN ANALYZE, Query Optimizers).
- Model for the read. Take the source's normalized shape as input, not as a template — the fact and dimension model exists because analytical readers ask different questions than the application did (Operational vs Analytical Models).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- The source database guarantees atomicity, durability and constraint consistency at commit. That is the strongest guarantee anywhere in the chain and it applies only inside that system (Transactions and ACID).
- A log-based change stream inherits per-source commit ordering and at-least-once delivery from the connector onwards. It does not inherit the source's atomicity: a transaction that changed four tables arrives as four independent streams of changes and nothing downstream reassembles it unless you build that (CDC Ordering and Transaction Boundaries).
- A snapshot read guarantees that all rows describe one instant, and only if the isolation level actually provides that. A chunked read at a weaker level guarantees nothing of the kind.
- The database guarantees nothing at all about what a column *means*. Types are enforced, semantics are not, and the change that breaks a metric usually changes neither the type nor the name (Semantic Changes).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The boundary check that matters is a reconciliation against the source at a defined position: count and sum a closed period in the source, compare the same aggregate in the landed table, and record the log position both were taken at.
- It misses rows that were created and deleted inside the period without either side observing them, any open period, and any error that is present identically in both places because it came from shared logic.
- It also cannot see a mis-stitched snapshot that happens to reconcile on totals while individual rows carry the wrong version. Row-level comparison on a sample of keys is the cheap complement.
- Log-based capture gives you the freshest possible view of committed changes that does not query the source at all — its lower bound is the log flush and the connector's own batching, not a schedule.
- Polling can never be fresher than its interval, and it structurally cannot see intermediate states, which is a completeness property rather than a latency one (CDC vs Polling).
- Reading from a replica adds that replica's lag to end-to-end freshness, and that lag is variable, invisible to the extract, and largest exactly when the source is busiest (Replication and Read Scaling).
- A source migration is a schema change you were not consulted about. Adding a nullable column is usually harmless; renaming or retyping one breaks a decoder that mapped by name, and dropping one breaks a transformation that projected it (CDC and Schema Drift, Schema Migrations from the Application Side).
- The dangerous case is a change that is invisible to every schema check: a status enum gains a value, a monetary column moves from gross to net, a foreign key starts pointing at a different table's ids (Semantic Changes).
- Changes to the source's *physical* organisation — a new index, a re-partitioned table, a switch of storage engine — normally mean nothing to a log-based pipeline, and it is worth saying that explicitly so the two categories do not get treated alike.
- Recovery from a change-stream gap is a re-snapshot, not a replay: once the log segments are reclaimed, the changes do not exist anywhere to replay from (CDC Failure Modes and the Retention Deadline).
- Recovery from a bad transformation is a re-run from raw, and needs nothing from the database — which is exactly why an untouched raw landing is worth its storage (The Raw Landing Zone, Keeping Raw History: The Recovery Position and the Liability).
- Recovery from a mis-stitched snapshot means re-snapshotting the affected tables and replaying the stream forward from the new position, with the merge into the target keyed and idempotent so overlapping ranges collapse instead of doubling (Upserts and Merges, Idempotent Data Pipelines).
What can go wrong
- Log retention expires while a connector is behind: changes are lost rather than late, and this is the failure that has no cheap repair.
- A snapshot taken at an isolation level that does not provide a consistent view, producing a dataset that is internally contradictory in ways no test looks for.
- A replica used as an extract source silently truncating the period at its own lag.
- The mitigation failing: a reconciliation query that itself reads the source through the same lagging replica, so both sides agree and both are wrong.
- A hard delete in the source that the pipeline never sees, leaving rows in the warehouse that the source would say do not exist.
- "Data engineers do not need to understand databases." Almost every silent completeness failure in a pipeline originates in a database mechanism — commit ordering, snapshot isolation, log retention, replication lag. You do not need to implement a B-tree; you do need to know why your extract missed a row.
- "The warehouse is a database, so my database knowledge transfers directly." Much of it does. What does not transfer is the assumption that indexes exist, that a point update is cheap, and that a transaction spans your whole job.
- "CDC gives me the source's transactions." It gives you the source's *changes*. Transaction boundaries are usually discarded by the time the events are partitioned by key, and reassembling them downstream is a design decision you have to make deliberately.
- "A replica read is free." It is free to the primary's CPU and expensive to your completeness, because the replica's lag becomes an invisible truncation of every period you extract.
Operating it
- Connector position versus the source's current log position — the single number that says whether change capture is caught up (Pipeline Metrics).
- Replication slot or log-retention headroom on the source, alerted before the source starts reclaiming.
- Row counts and key-level samples reconciled per closed period against the source, stored so drift is visible over weeks rather than as a pass/fail (Reconciliation).
- A lineage edge from every landed table back to the source table and the capture method used, because during an incident the first question is which mechanism produced this (Data Lineage).
- At 10x source write volume the connector becomes a throughput problem and the ordering guarantee becomes the constraint, because per-source commit order means you cannot simply parallelise it.
- At 100x, or once the source is sharded, there is no single log any more. Ordering becomes per-shard, snapshots become per-shard, and the pipeline needs an explicit story for how it merges them (Partitioning and Sharding, CDC Ordering and Transaction Boundaries).
- Table count scales worse than row count. A hundred captured tables is a hundred schema-drift surfaces, and it is where contract enforcement stops being optional (Contract Enforcement).
- Log-based capture costs the source almost nothing to produce and costs it a growing amount to *retain* when a consumer is behind — the cost is on the source and it is invisible until it is urgent.
- Polling costs the source query capacity every interval, whether or not anything changed, which is why a polling extract on a large table is a scheduled load test.
- On our side the driver is the same as everywhere else in this domain: bytes landed, bytes retained, and how much of history a transformation re-reads because it was not incremental (What Actually Drives Data Platform Cost).
- Log-based capture buys completeness and freshness and costs you an operational dependency on the source's internals — its log configuration, its retention, its upgrade schedule. You are now coupled to a component the source team considers private.
- Learning enough database internals to do this well is real time that is not spent building models. The alternative is being unable to diagnose an entire class of incident, which is worse, but it is not free.
- Snapshot-plus-stream is strictly more correct and strictly more machinery than a nightly full extract. For a small, slow-changing table the full extract is the right answer and reaching for CDC is over-engineering (Batch Ingestion).
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALThat a pipeline inherits the source database's ordering, retention and snapshot semantics is true of every source engine. What differs is the name of the log and how much of it is exposed.
- SOURCE-SPECIFICPostgres exposes changes through logical decoding on a replication slot that holds WAL until it is consumed; MySQL's binlog is retained by a server-side policy that does not care whether a consumer is behind; MongoDB's oplog is a capped collection that overwrites regardless. The failure mode differs accordingly: Postgres fills the source's disk, MySQL and Mongo silently lose the changes.
- SIMPLIFIEDTreating "the WAL" as one thing hides physical versus logical replication, and hides that some engines require a separate configuration before row-level changes are decodable at all. Database Engineering owns that detail.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns what "commit order" means once there is more than one source and no shared clock. When a database is sharded, per-source ordering stops being a total order and the pipeline needs an explicit merge story.