Architecture Views

When a sink's contents and the change log disagree, which one is wrong?

Context

The obvious way to serve five consumers from one database is five replication jobs, each reading the source and writing its own destination. It is the cheapest thing to build for the first consumer, and it is what most teams write first. It also means there is no single place where the history of change exists: each destination is its own truth, repaired by hand when it drifts, and the fifth consumer costs what the first did all over again. Once a destination is authoritative for its own contents, every correctness problem becomes a bespoke recovery procedure written under pressure.

Decision

An immutable, replayable change log sits between capture and every sink and is the only system of record for change. Each sink is a derived projection with its own offset, rebuildable from the log within retention and from the archive beyond it. No sink is ever authoritative for its own contents, and no sink is repaired in place.

How it is realised on AWS

Capture publishes normalised change events to Pub/Sub topics partitioned by primary-key hash, with seven-day retention and a continuous export to a dual-region Cloud Storage archive held for thirteen months. Each sink is a separate Dataflow job holding its own subscription and offset. Spanner holds the offsets, schema versions and snapshot chunk progress; BigQuery, the search index and the caches hold nothing the platform could not reconstruct.

Options weighed

• ChosenImmutable change log as the record, sinks as versioned projections: One recovery procedure — rebuild — for every class of sink corruption, and a new consumer costs an offset.
• RejectedDirect source-to-sink replication job per consumer: Cheapest for one consumer; linear cost per consumer after that, and repair becomes correction SQL against production.
• RejectedSink is authoritative, log kept only as an audit trail: Makes the audit trail unverifiable and the sink unrebuildable — the worst of both.
• Right elsewhereDual writes from the application to database and bus: Right where the application owns both sides and can accept a transactional outbox; wrong here, where 9 services would each have to get it correct.

Consequences

What it buys

• A bad transform, a corrupt partition, a wrong type coercion and an operator error all have the same remedy, so recovery is routine rather than improvised.
• Adding the fourth and fifth sink costs an offset and a job, not a change to the capture path.
• A slow or dead sink is isolated by construction: back-pressure terminates in log retention.

What it costs

• An extra durable hop in the latency budget, which is why the end-to-end target is seconds rather than milliseconds.
• Retention becomes a hard operational deadline: a consumer that falls behind it needs a re-snapshot, not a retry.
• Every sink writer must be idempotent and monotonic per key, which is a discipline the direct design does not demand.

Choose differently when

If there were exactly one consumer, permanently, and that consumer's contents were cheap to rebuild from the source directly, the log is overhead and a direct replication job is the better design. The decision should also be revisited if retention economics ever make a replayable log of this volume disproportionately expensive relative to a source-side re-read.

Why it holds up over time

This is a statement about where authority lives, not about a product. It survives replacing the broker, the stream processor and the capture engine, because none of those changes which store is allowed to be wrong.

When a sink is wrong, what does the fix look like?

Context

Sinks go wrong in ways that are not the source's fault: a bad transform deployed, an offset reset to the wrong position, a schema evolution applied in the wrong order, a key collision after a primary-key change. The instinctive fix is a corrective UPDATE against the serving table, written during an incident by whoever is awake. That path is fast, unreviewable, usually undocumented, and it makes the sink's state path-dependent on the history of its own repairs.

Decision

A sink is never repaired in place. It is rebuilt from the change log, or from the archive beyond retention, into a shadow table, which is then swapped in atomically. Rebuild is exercised on a schedule so that its duration is a measured number rather than an estimate.

How it is realised on AWS

The rebuild runner replays from a chosen position into a shadow table with a parallelism of its own, diffs the result against the live table, and swaps the pointer. Offsets realign after the swap. Every tier-1 table's largest sink is rebuilt from the archive quarterly and diffed, which is what substantiates the eight-hour figure for the largest table.

Options weighed

• ChosenRebuild into a shadow table, then swap atomically: Repair is reviewable, repeatable and rehearsed, and the serving table never holds partial state.
• RejectedCorrective SQL against the serving table: Fast, unreviewable, and it makes current state depend on the sequence of past repairs.
• RejectedRebuild in place from the log: Correct eventually, and serves partial state throughout, which is worse than being stale.
• Right elsewhereRestore the sink from a backup taken before the defect: Right where the sink is the record; here the log is the record, so a backup is a slower, lossier rebuild.

Consequences

What it buys

• Every class of sink corruption has one remedy, so there is no bespoke procedure to write under pressure.
• A rebuild is rehearsed, which turns the recovery target into a measurement.
• Readers never see a partially repaired table.

What it costs

• A rebuild needs sink capacity for a full second copy of the table during the build.
• Rebuild competes with live apply for sink write throughput; the MVP throttles rather than schedules, which is the cruder option.
• Beyond log retention the rebuild depends on the archive, which makes the archive's correctness load-bearing.

Choose differently when

If a sink were too large to hold a shadow copy, an in-place rebuild with a readers-redirected flag becomes the pragmatic choice — but that is a capacity concession, and it should be recorded as one rather than adopted as a pattern.

Why it holds up over time

"Rebuild rather than repair" follows directly from the log being the record, so it holds for as long as ADR-01 does.

Should offsets, schema versions and chunk progress live in the same store as the data they describe?

Context

The platform has two kinds of state with almost nothing in common. One is a few million rows that must be exactly right and must survive — offsets, schema versions with their effective positions, snapshot chunk progress, pause state, audit. The other is billions of rows that are explicitly disposable. A store that is excellent at both at a thousand-to-one ratio does not exist, and co-locating them means a rebuild of the disposable side risks the side that makes rebuilds possible at all.

Decision

Control state lives in a strongly consistent relational store, separate from every sink and from the log. No projection holds state the platform needs in order to recover that projection.

How it is realised on AWS

Spanner holds streams, sinks, subscriptions and offsets, schema versions, snapshot chunk state, pause state and the append-only audit log, regionally durable and independent of BigQuery, the search index and Pub/Sub. The capture standby in the second region resumes purely from the position recorded here, which is why this store's availability target is separate from the sinks'.

Options weighed

• ChosenDedicated strongly consistent control store: Recovery metadata survives the loss of anything it describes, and is small enough to be cheap.
• RejectedOffsets in the sink alongside the data: Attractive because it makes an apply transactional, and it means a sink rebuild destroys its own resume point.
• RejectedOffsets only in the broker's subscription state: Adequate for steady state, insufficient for replay-from-position, cross-region resume and audit.
• Right elsewhereControl state in the warehouse: Workable for a small single-sink pipeline; here it couples recovery to the store most likely to be being rebuilt.

Consequences

What it buys

• A sink can be dropped entirely without losing the means to rebuild it.
• Cross-region capture resume needs one small store to be available, not the whole data plane.
• Audit and schema history get the durability guarantees they need without paying warehouse-scale costs.

What it costs

• An apply and its offset commit are not one transaction, which is exactly why idempotence is mandatory (ADR-04).
• One more store to operate, secure and back up.
• The control plane becomes a dependency of every operator action, so its availability target matters.

Choose differently when

For a single-sink pipeline where the sink is a transactional database, writing the offset inside the same transaction as the data is simpler and strictly stronger. That design does not survive the second sink.

Why it holds up over time

The ratio between recovery metadata and the data it describes is a structural property of derived systems. Separating the two will keep paying off regardless of which stores are involved.

Where do changes come from — the log, a query, or the application?

Context

Three mechanisms are available. Polling a modified-at column is trivial to build and is what most first attempts use. Having each service publish its own events alongside its database write gives the cleanest semantics on paper. Reading the database's own replication log is the most invasive to set up and the only one that sees what actually committed. The choice determines what the pipeline can be correct about, and it cannot be changed later without re-snapshotting everything.

Decision

Capture reads the source's write-ahead log via logical decoding, under a replication identity with no write privilege. Polling is a documented fallback for sources without log access, and every stream records its capture method so consumers know whether deletes are observable. Application-level dual writes are not used.

How it is realised on AWS

Datastream holds one logical replication slot per Cloud SQL for PostgreSQL database, shared across all allow-listed tables in that database. Each change arrives with operation, post-image, primary key, commit timestamp, log position and transaction id; pre-images are available where the table is configured to provide them, and that availability is recorded per table rather than assumed.

Options weighed

• ChosenLog-based capture by logical decoding: Sees deletes, intermediate versions and commit order, and adds no query load to the source.
• RejectedPolling a modified-at column: Cannot see deletes, misses intermediate versions, and its ordering is a query artefact rather than a fact. Also adds read load to the primary.
• RejectedApplication dual writes to database and bus: Fails silently in the one case that matters — a crash between the commit and the publish — and asks nine service teams to get the same hard thing right.
• Right elsewhereTransactional outbox in each service: Genuinely good where one team owns the service and the pipeline, and where events are domain events rather than row changes. Here it is nine implementations of the same table.

Consequences

What it buys

• Deletes, intermediate versions and true commit order are all observable, which the other two mechanisms cannot offer at any price.
• No application change is required to replicate a table, so onboarding does not queue behind nine product backlogs.
• The source carries decoding cost rather than query cost, which is the cheaper of the two at this rate.

What it costs

• A replication slot is operational shared fate with the source database (ADR-03).
• The pipeline is coupled to the source engine's decoding behaviour, including its pre-image configuration and its failover semantics.
• Row changes are not domain events: the pipeline delivers what the schema says, not what the business meant.

Choose differently when

If a source platform offered no log access at all, polling becomes the only option and the delete-visibility gap has to be closed in the source schema with soft deletes. If the organisation moved to event-sourced services that already publish reliable domain events, this pipeline is redundant for those services.

Why it holds up over time

The reasons polling loses — invisible deletes, lost intermediate versions, fictional ordering — are properties of polling, not of any era's tooling. They will be as true in a decade as now.

What does the pipeline owe the database it is reading?

Context

A logical replication slot holds the source's log until the consumer confirms it. A capture job that stops and is forgotten therefore does not fail quietly on its own side — it fills the source database's disk, which is an application outage caused by the analytics platform. The same mechanism means that a pause, which looks like the safe operator action, is the dangerous one: a table paused for long enough to exhaust retention cannot resume, and its resumption silently skips a range of changes if the pipeline is careless.

Decision

Slot retention headroom is a first-class alarm with its own operator action. The platform refuses a pause whose expected duration would exhaust source log retention, and a detected gap in positions stops the affected table loudly and marks it for re-snapshot rather than resuming across the gap.

How it is realised on AWS

Capture reports slot lag and remaining retention headroom per source to Cloud Monitoring, alarmed before the source's configured retention is reached. The operator API checks headroom and in-flight snapshots in the control plane before accepting a pause or re-snapshot and returns a refusal with the reason, which is recorded in the audit log along with the attempt.

Options weighed

• ChosenHeadroom as an alarm, plus refusal of unsafe operator actions: Puts the constraint where the damage would happen, and makes the refusal auditable.
• RejectedTrust the operator and log a warning: The failure lands on a team that did not take the action, during an incident they did not cause.
• RejectedDrop the slot automatically when headroom is low: Protects the source at the cost of a silent gap — trading an outage for a correctness incident.
• Right elsewhereCapture from an archived-log copy rather than a live slot: Right where the source platform exposes shipped logs and lag of minutes is acceptable; it removes shared fate and the sub-five-second target together.

Consequences

What it buys

• The failure mode most likely to make this platform unwelcome — filling someone else's disk — has an owner, an alarm and a refusal path.
• A pause is safe to use, which means operators will use it instead of improvising.
• A gap is always an incident with a name rather than a quiet correctness loss.

What it costs

• Some legitimate operator actions are refused, and the operator has to raise the source budget or shorten the window first.
• The platform must model source retention per database, which is configuration it does not own and must track.

Choose differently when

If the source engine gained failover-safe slots with their own retention guarantees independent of disk, or if the sources moved to a platform that ships logs to object storage, the shared-fate problem largely disappears and the refusal logic becomes unnecessary ceremony.

Why it holds up over time

Shared fate between a consumer's progress and a producer's disk is inherent to log-based replication. The specific alarm thresholds will change; the obligation will not.

When the source is busy and a backfill is running, which one slows down?

Context

The platform's single most important non-functional property is that it cannot degrade the database the product writes to. A backfill is the one part of the pipeline that issues real queries against the source, and it is the part most likely to run during a busy period because it runs for hours. Without an explicit answer, the implicit answer is whichever one the database's scheduler happens to favour, which is not a design.

Decision

Snapshot reads come from a read replica where the engine permits it, throttled against a source budget declared per database. When the source approaches that budget, snapshot concurrency reduces: the backfill is always the thing that slows down, never the application's write path.

How it is realised on AWS

Each source database carries a budget — assumed at ≤ 5% added CPU and ≤ 10% added p99 write latency — enforced by adaptive chunk concurrency with reported progress and an ETA. Streams captured from a replica are annotated so consumers know commit timestamps carry replica lag. Snapshot progress is a reported metric, which is what makes a slower backfill an acceptable outcome rather than an invisible stall.

Options weighed

• ChosenReplica-sourced, budget-throttled snapshots that yield to live traffic: Makes the trade explicit and puts the cost on the operation that can afford to be slow.
• RejectedSnapshot the primary at full speed: Fastest backfill, and the reason platform teams are told they cannot have replication.
• RejectedSnapshot only in a maintenance window: Caps backfill throughput at the window and makes onboarding wait on a calendar.
• Right elsewhereSnapshot from a storage-level backup or export: Right where exports are routine and consistent; here it adds a second code path for reading the source, which ADR-05 deliberately avoids.

Consequences

What it buys

• The service owner's question — can this make my database slow? — has a numeric answer and a mechanism behind it.
• Backfills can run during business hours, which is what makes onboarding a day rather than a window.
• A declared budget is negotiable per source, so a tolerant database can be backfilled faster.

What it costs

• Backfill wall-clock becomes a function of the source's load, so the ETA moves and has to be published.
• Replica-sourced snapshots inherit replica lag, which must be annotated rather than hidden.
• Adaptive concurrency is more machinery than a fixed parallelism setting.

Choose differently when

If a source were provisioned with headroom specifically for replication, or if the replica were dedicated to this platform, the throttle can be relaxed to a fixed high concurrency and the complexity removed.

Why it holds up over time

The operational database will always matter more than the analytics copy of it. A design where the derived work yields stays correct as both sides grow.

Where is exactly-once implemented — in the transport, or in the sink?

Context

Every few years a transport claims exactly-once delivery, always with conditions that stop holding at a boundary: a crash between a sink write and an offset commit, a replay after an operator error, a late event arriving after a newer one. A pipeline that depends on the claim is correct until it is not, and the failure is a silently overwritten value rather than an error. The alternative is to make duplication harmless, which costs a key and a comparison per row.

Decision

The platform offers at-least-once delivery and per-row commit ordering. Every sink writer is idempotent, keyed on (primary key, log position), and carries a monotonic per-key sequence so a replayed or late event with a lower position is discarded rather than applied. The offset is committed after the sink write, never before.

How it is realised on AWS

Change events carry the log position as a monotonic sequence within a key. The warehouse applier groups a micro-batch by primary key, keeps the highest position, and issues a MERGE conditioned on the stored position being lower; the search indexer uses the position as its external version. Offsets are committed to the subscription only once the sink acknowledges the write, so a crash in between replays the batch.

Options weighed

• ChosenAt-least-once plus idempotent, monotonic apply: Correct under replay, late arrival, operator reset and crash, with no dependence on a transport guarantee.
• RejectedRely on the transport's exactly-once mode: Narrows to a single broker, and still breaks across an offset reset or a deliberate replay — which this design treats as routine.
• RejectedAt-most-once with a fast path: Trades a duplicate for a lost change, which is the one failure this platform must not have.
• Right elsewhereTwo-phase commit between log and sink: Right where a sink is a transactional database and volumes are low; here it couples availability of the sink to availability of the log.

Consequences

What it buys

• Replay becomes a safe, routine operation, which is the precondition for rebuild as a recovery path (ADR-11).
• Correctness does not depend on any broker's strongest claim, so the broker is replaceable.
• A late or out-of-order event cannot overwrite a newer value, which removes the most confusing class of wrong number.

What it costs

• Every sink must store or derive the per-key position, which rules out sinks that cannot carry one.
• Duplicates are visible to anything reading the append-only changelog tables, so consumers of those must deduplicate themselves.
• The apply path is marginally more expensive per row than a blind upsert.

Choose differently when

If a sink appears that genuinely cannot hold a version per key — some external APIs cannot — then that sink needs an idempotency shim in front of it, or it is accepted as best-effort and labelled so. That is a per-sink exception, not a change to the platform's semantics.

Why it holds up over time

Keys and sequences in the data model are cheap, transport-independent and have outlived several generations of delivery-guarantee marketing. They will outlive the next one.

How much work happens between the log and the sink?

Context

A stream processor between log and sink can do almost anything: enrich from a lookup, join two tables, maintain windowed aggregates, denormalise into the shape a dashboard wants. Each of those is cheaper to run once in the pipeline than many times in the sink, and each of them turns the pipeline into a stateful system with its own correctness, its own recovery and its own backfill semantics. The question is where the boundary sits, and it has to be decided before the first transform is written, because moving it later means rebuilding every sink.

Decision

Only stateless, per-event transformation happens in the pipeline: projection, column renaming, type coercion, masking, tenant stamping and soft-delete interpretation. Joins, aggregation and business derivations happen in the sink. The pipeline holds no business state.

How it is realised on AWS

Dataflow appliers run a per-event transform with no side state beyond the micro-batch, then issue an idempotent write. Everything a consumer wants joined or aggregated is expressed as BigQuery SQL over the mirror and changelog tables. The only stateful components in the platform are the control plane and the log itself.

Options weighed

• ChosenStateless per-event transform, aggregate in the sink: Keeps the pipeline restartable from any position with no state to recover, and makes a logic change a backfill of one sink.
• RejectedStateful stream processing — joins and windowed aggregates in the pipeline: Cheaper per query and far more expensive per incident: the pipeline acquires checkpoints, watermarks and its own recovery semantics.
• RejectedNo transformation at all — raw payloads to the sink: Pushes masking downstream, which ADR-07 forbids, and makes every sink re-implement the same coercions.
• Right elsewhereTransform entirely in the sink as ELT: Right where every sink is a warehouse that can do the work; here the search index and the fan-out cannot, so some coercion must happen upstream.

Consequences

What it buys

• The pipeline is restartable from any retained position with nothing to recover but its offset.
• A transformation defect is repaired by rebuilding one sink rather than replaying into all of them.
• The same event shape serves a warehouse, an index and an HTTP fan-out, because none of them receives pre-joined data.

What it costs

• Consumers pay query-time cost for joins the pipeline could have done once.
• Denormalised shapes some consumers want must be built as sink-side views, which is their work rather than the platform's.
• Cross-table consistency questions move to the sink, where they surface as a consumer's correctness problem (see Core Question 2 in the requirement).

Choose differently when

If a single denormalised projection were needed by many consumers at a freshness the sink's own views cannot deliver, a stateful join in the pipeline becomes the right answer for that one projection — introduced deliberately, with its own recovery story, rather than by drift.

Why it holds up over time

The trade between stateless simplicity and stateful efficiency is permanent. What changes is the sink's ability to do the work, and sinks have become steadily more capable, which pushes the boundary further in this decision's favour over time.

How is "everything that exists" stitched to "everything that changes" without a lock and without a gap?

Context

A stream alone knows only what happened since it was switched on, so every new table needs a snapshot of what is already there. The textbook method takes a consistent position with a lock, which is unacceptable against a live primary holding 1.2 billion rows. The naive alternative — snapshot first, then start the stream — leaves a gap exactly as wide as the snapshot took. This is the hardest correctness problem in CDC and the one most often got wrong quietly.

Decision

Record the log position at snapshot start and begin streaming into the log from that position before reading any rows. Snapshot in primary-key ranges, interleaved with live stream consumption and resumable by chunk, marking snapshot rows with op = READ. The overlap is resolved by the apply path, which keeps the higher position per key.

How it is realised on AWS

The control plane in Spanner holds the recorded start position and the state of every chunk. Snapshot workers read ranges from a read replica while the applier consumes the live stream; because apply is monotonic per key (ADR-04), a snapshot row arriving after a newer stream row for the same key is discarded. An interrupted snapshot resumes at the last completed chunk.

Options weighed

• ChosenChunked snapshot, stream-first, monotonic apply resolves the overlap: No lock, no long transaction, resumable, and one apply path for backfill and live traffic.
• RejectedLock the table, take one consistent position: Correct and simple, and unacceptable on a live primary for any table worth replicating.
• RejectedSnapshot first, start the stream afterwards: Leaves a gap the width of the snapshot, which is the silent correctness failure this design exists to avoid.
• Right elsewhereSnapshot a point-in-time clone and stream from the clone's position: Clean seam and genuinely attractive where the source platform clones cheaply; it costs a clone per table and ties the design to that platform.

Consequences

What it buys

• A 400 GB table can be backfilled during business hours without a lock, a long-running transaction or a maintenance window.
• Backfill and live capture share one apply path, so they cannot diverge in behaviour.
• An interrupted backfill resumes rather than restarts, which is what makes re-snapshot a usable operator action.

What it costs

• Correctness now depends on the apply path being strictly monotonic per key — a sink that cannot do this cannot be backfilled this way.
• The overlap window produces redundant writes, so a backfill costs more sink writes than a clean snapshot would.
• Chunk state is extra control-plane state that has to be durable and correct.

Choose differently when

If every replicated table were small enough to snapshot inside a short lock window, the simpler locked snapshot is the better engineering choice. If the source platform offers transactionally consistent cheap clones, the clone approach removes the overlap entirely and is worth revisiting.

Why it holds up over time

Reconciling existing state with a change stream is inherent to replication rather than to any engine. The method will still apply when both the source and the sink have been replaced.

How does a pipeline survive a migration nobody told it about?

Context

Schema drift is the most common cause of CDC outages, and it arrives as a routine Tuesday migration from a team that has no reason to think about replication. Any design that depends on being notified will fail, because the notification depends on a human remembering a system they do not own. The pipeline must therefore find out from the only channel that cannot be forgotten: the change stream itself.

Decision

Schema change is detected from decoded relation metadata and DDL events in the stream. Each change is classified as compatible — added nullable column, widened type, new table — or breaking — dropped or renamed column, narrowed type, primary-key change. Compatible changes propagate automatically, including additive DDL on the sink; breaking changes pause exactly one table and raise an operator decision. An unregistered column is either propagated or excluded by allow-list, never silently dropped.

How it is realised on AWS

The schema registry in Spanner holds every version with the log position at which it became effective, and each event carries its schema version so a replayed week-old event is interpreted with the schema in force when it was written. A primary-key change is treated as a re-snapshot rather than an in-place migration. A pause holds the table's position and the slot keeps running for every other table in the database.

Options weighed

• ChosenDetect from the stream; auto-propagate compatible, pause on breaking: Depends on no human remembering anything, and keeps the blast radius at one table.
• RejectedRequire migration announcements or a registry pre-registration step: Correct in principle and guaranteed to be bypassed; the first bypass is a silent column drop.
• RejectedPause the whole source on any schema change: Simple and safe, and it makes one team's routine migration an outage for 180 tables.
• Right elsewhereSchema-on-read: replicate raw payloads and interpret at the sink: Right for a lake whose consumers are tolerant and few; it pushes the classification problem onto every consumer instead of solving it once.

Consequences

What it buys

• The common case — an added nullable column — needs no human and completes inside a minute.
• A breaking change costs one table, so the pipeline is not a reason to slow down migrations elsewhere.
• Replay remains interpretable because events carry their schema version.

What it costs

• Classification is the single point of judgement in the pipeline, and a misclassification is either a needless pause or a corrupted sink.
• Per-table pause means per-table position tracking on a shared slot, which is more state to reason about during an incident.
• Detection quality depends on what the source engine emits, which the platform does not control.

Choose differently when

In an organisation with enforced schema review and a migration pipeline the platform can hook into, pre-registration becomes reliable and allows stricter validation before the change reaches production — a better outcome where it is genuinely achievable.

Why it holds up over time

That the team shipping a migration is not the team running the pipeline is an organisational fact, not a technical one, and it is remarkably stable.

What does a deleted row look like in the sink, and for how long?

Context

A delete in the source has no obviously correct representation in a derived store. Removing the row matches the source exactly and destroys the ability to answer what the row looked like last week, while breaking any downstream join that still holds the key. Keeping a tombstone preserves history and auditability, and makes every sink query responsible for filtering it while the mirror grows without bound. The choice has to be made per table and defaulted in the direction that is reversible.

Decision

The current-state mirror represents deletes as soft-delete tombstones by default, with a deleted-at and the originating log position. Hard delete is available as a per-table opt-in. The append-only changelog table always retains the delete as an event regardless of the mirror's mode.

How it is realised on AWS

The mirror carries is_deleted and deleted_at columns; the platform publishes views that filter tombstones so the default consumer query is the correct one. A table opted into hard delete has rows removed from the mirror, while its changelog table still records the delete event — so history survives even where current state does not.

Options weighed

• ChosenSoft-delete tombstones by default, hard delete per table: The reversible choice is the default, and auditability is preserved without forbidding the other mode.
• RejectedHard delete everywhere: Matches the source and silently breaks downstream joins and historical questions; recovering the lost row needs a replay.
• RejectedTombstones everywhere, no opt-out: Unbounded mirror growth for high-churn tables and a filter requirement every consumer will eventually forget.
• Right elsewhereDeletes as a separate stream, mirror unaware: Works where consumers are event-driven; here it leaves the mirror wrong, which is the one thing the mirror exists not to be.

Consequences

What it buys

• Historical questions remain answerable, which is most of why the warehouse exists.
• A delete propagated in error is recoverable from the mirror itself rather than from a replay.
• Downstream joins holding a deleted key degrade visibly rather than losing rows silently.

What it costs

• Every consumer query must filter tombstones, so the platform has to ship the filtering views and keep them the obvious path.
• High-churn tables grow a mirror larger than the source, which is a cost the opt-in exists to relieve.
• Erasure requests must handle both the tombstone and the changelog event, not just the row.

Choose differently when

A table whose deletes are legally required to be complete erasures, or whose churn makes tombstones dominate storage, should be opted into hard delete — and the opt-in exists precisely so that is a configuration decision rather than a platform redesign.

Why it holds up over time

The tension between matching the source and preserving history does not resolve with better technology, because it is a disagreement about what the derived store is for.

How does the platform find out a sink is wrong before a consumer does?

Context

Lag tells you whether a sink is behind. Nothing in the lag signal tells you whether the values that did arrive are right. Wrongness arrives through transform defects, out-of-order application, schema evolution applied incorrectly, and deletes that never propagated — none of which raise an error. Without an active check, the detector is a human noticing a number look odd, which is both slow and arbitrary.

Decision

Reconciliation runs per tier-1 table daily and compares row counts plus a checksum over sampled primary-key ranges between source and sink, publishing a verdict. Divergence is one of the platform's four alarms. Reconciliation coverage — the set of replicated tables with no verdict — is reported as a first-class number.

How it is realised on AWS

The reconciliation runner reads counts and sampled ranges from a read replica inside the same source budget as snapshots, compares them against the sink, and writes a verdict to the control plane with the sampled ranges recorded. Tier-1 is assumed to be 40 of the 180 tables; tier-2 is weekly.

Options weighed

• ChosenRow counts plus sampled checksums, with coverage reported: Catches value drift as well as row loss at a bounded source cost, and makes the gap in coverage visible.
• RejectedRow counts only: Cheap, and blind to the most common defect — right number of rows, wrong values in them.
• RejectedFull checksum of every table: Definitive, and it spends the source read budget that ADR-06 reserves for the application.
• Right elsewhereNo active check; rely on consumer reports: Defensible for a sink nobody makes decisions from; indefensible for the one the board's revenue number comes from.

Consequences

What it buys

• Value drift is detected within a day on the tables that matter, rather than whenever someone notices.
• Coverage as a reported number turns "we should check more tables" into a visible backlog.
• A verdict gives an incident a starting point instead of a conversation about whether anything is wrong at all.

What it costs

• Sampling is probabilistic: a defect confined to unsampled ranges survives until the sample moves.
• Reconciliation consumes part of the same source budget as backfills, so the two compete.
• Detection latency is hours, which makes this the slowest signal in the platform and the one most worth improving.

Choose differently when

If a sink's correctness became high-stakes enough to justify the source read cost — a regulatory ledger, say — full checksums on a schedule become the right answer for that table. Equally, a sink derived only from append-only tables can be verified by position continuity alone, far more cheaply.

Why it holds up over time

Derived data drifts from its source for reasons that have nothing to do with the technology carrying it, so an active check will always be needed. Only its cost changes.

Where is the boundary a regulated value must not cross?

Context

The platform fans one source into several sinks and keeps a thirteen-month archive. A value that enters the change log therefore exists in the log, in the archive, and in every sink built from it, and removing it afterwards means deleting from all of them and rewriting history in the archive. Masking at the sink is easier to implement and arrives one step too late in every case that matters.

Decision

Column-level masking and exclusion are applied in the capture plane, before the event is published. A declared sensitive column either never enters the log, or enters it masked. No downstream component is relied upon to redact.

How it is realised on AWS

The allow-list per table names columns to exclude and columns to mask, applied by the capture plane as part of normalisation. The change log and the archive are encrypted with customer-managed keys. Adding a masking rule to a table that is already live does not clean the existing log or archive: it requires a re-snapshot, and the platform says so rather than implying the rule is retroactive.

Options weighed

• ChosenMask and exclude at capture, upstream of the log: The only placement where the regulated value never enters the long-lived copies.
• RejectedMask at the sink: Leaves the raw value in the log and the archive, which is where it is hardest to remove.
• RejectedMask in the stream processor between log and sink: Same flaw, one step earlier: the log already has it.
• Right elsewhereReplicate everything and rely on sink access control: Defensible inside a single trust domain with uniform controls; here the archive's thirteen-month horizon makes it a standing liability.

Consequences

What it buys

• The log and the archive, the two longest-lived copies, are clean by construction rather than by policy.
• A sink's access control is a second line of defence instead of the only one.
• Masking becomes a capture-time declaration that is visible in the registry and auditable.

What it costs

• Masking is irreversible for data already captured: a late rule needs a re-snapshot and an archive rewrite.
• A masked column cannot later be used for a join or a lookup without changing the rule and re-snapshotting.
• Capture carries per-column logic, which couples it slightly to the schema it is trying to be generic about.

Choose differently when

If the archive were dropped and log retention fell to hours, masking at the sink becomes defensible because the window of exposure is short and the copies are few. It is the archive that makes capture-side masking non-negotiable here.

Why it holds up over time

The asymmetry between not collecting data and deleting it later is a permanent feature of data protection, and regulation has moved consistently in the direction that favours not collecting.

What single number says whether this platform is healthy?

Context

A pipeline has lag at every stage, and each stage's own metric can look fine while the thing a consumer experiences is broken. Worse, the obvious measurement — time since the last event was applied — reports zero lag for a table with no traffic and for a table whose capture has stopped, which are opposite conditions. Without a measurement that works on an idle table, a stalled stream is invisible until someone queries it.

Decision

End-to-end lag is defined as source commit timestamp to sink commit timestamp, measured on a synthetic heartbeat row written to every source database every ten seconds, reported per sink, and alarmed against each sink's declared freshness target. It is the platform's primary SLO.

How it is realised on AWS

A heartbeat writer updates one row per source database every ten seconds; the row flows through the identical path as production changes, so its arrival time at each sink measures the real path rather than a probe. Capture, transport and apply lag are retained as diagnostic breakdowns, not as the SLO. Alarms fire on lag above the declared target for five minutes on a tier-1 table.

Options weighed

• ChosenHeartbeat-based end-to-end lag per sink as the SLO: Works on idle tables, measures the path a consumer actually experiences, and decomposes for diagnosis.
• RejectedTime since last applied event: Indistinguishable between "nothing happened" and "capture stopped", which is the distinction that matters at 3 a.m.
• RejectedPer-stage lag with no end-to-end number: Every stage green while the consumer is stale is the classic failure of stage-local metrics.
• Right elsewhereConsumer-reported freshness: Useful as a second signal where consumers are instrumented; too late and too uneven to be the SLO.

Consequences

What it buys

• A stalled stream on a quiet table is detected in seconds instead of at the next query.
• The SLO is expressed in the units a consumer cares about, which makes the freshness contract meaningful.
• Per-stage breakdowns still exist, so an alarm has somewhere to point.

What it costs

• The platform writes to every source database, which is a small but real exception to the read-only posture and must be a dedicated, tiny table.
• Heartbeat traffic is a floor on log volume even when nothing else is happening.
• A heartbeat measures the path it takes, so a sink that handles the heartbeat table differently from a wide table will under-report.

Choose differently when

If the source platform exposed a reliable position-to-wall-clock mapping, lag could be derived from positions without writing to the source, which would restore a strictly read-only capture posture and is worth taking when available.

Why it holds up over time

That an idle stream and a stopped stream look identical to a passive metric is a property of measurement, not of tooling. Heartbeats have been the answer for decades and will continue to be.

Can capture run in two regions at once, and what does a log position mean after a failover?

Context

Running capture in two regions simultaneously means two readers of one source's replication state, which is a correctness problem rather than a capacity one: the source's log has one position sequence, and two consumers advancing it independently cannot both be right. Separately, when a source fails over to a replica, log positions may not be comparable across the new timeline — the same number can mean a different place in history, which is the subtlest correctness trap in the whole design.

Decision

Capture runs in one region. A standby in a second region holds no state of its own and resumes from the last committed position held in the control plane. Position discontinuity after a source failover is detected explicitly, and the affected tables are re-snapshotted rather than resumed from a timestamp-derived position.

How it is realised on AWS

Capture, transport, projection and control run in europe-west1; europe-west4 holds a Datastream standby and applier templates scaled to zero. BigQuery is EU multi-region and the archive dual-region, because residency obligations are read against the derived stores. RTO is 5 minutes in-region and 30 minutes cross-region; RPO is zero relative to the source's log position.

Options weighed

• ChosenSingle write region, stateless standby, explicit timeline reconciliation: Avoids two readers of one position sequence, and treats a timeline change as the correctness event it is.
• RejectedActive-active capture in two regions: Two independent readers advancing one source's replication state; duplicate and gap handling becomes a merge-semantics problem with no clean answer.
• RejectedDerive position from commit timestamp after failover: Convenient, and lossy exactly at the boundary — a bounded gap or duplicate at the worst possible moment.
• Right elsewhereRequire failover-safe logical slots that survive promotion: The cleanest answer and the right one where the source platform offers it; it constrains the source platform, which this design does not get to choose.

Consequences

What it buys

• There is exactly one advancing reader per source, so positions mean one thing.
• Failover is a small, cheap operation because the standby carries no state to reconcile.
• A timeline change produces a loud, bounded re-snapshot instead of a silent gap.

What it costs

• A regional outage is the one failure class with a whole-pipeline blast radius, and 30 minutes of lag with it.
• Re-snapshotting after a failover is expensive on large tables, and it will happen at a bad time.
• The standby's correctness depends on the control plane's durability, which concentrates risk in one small store (ADR-14).

Choose differently when

If sources moved to a platform offering failover-safe logical slots with position continuity across promotion, the re-snapshot path can be replaced by a straightforward resume — the single biggest available improvement to this design's failure behaviour.

Why it holds up over time

That one log has one position sequence is arithmetic, not engineering fashion. Active-active capture of a single source will stay a merge problem however good the tooling becomes.