Data Pipeline Architecture and Design Questions

Direct answer

Publish the change stream once, through change-data-capture (CDC, a technique that captures row-level inserts, updates, and deletes from a source database as a stream of events) into a single durable, ordered log partitioned by primary key, and let the cache, the search index, and the warehouse each consume that log independently through their own idempotent apply logic. Consistency here does not mean all three agree at every instant; it means each one correctly converges to the source state once it has processed everything up to some point, at a staleness bound that fits what that system is actually used for.

Structured elaboration

One producer, many independent consumers. Capturing the change stream once and fanning it out through a shared log avoids coupling the three downstream systems to each other or to the source's write path; a slow search indexer never blocks the cache, and a cache outage never blocks the warehouse load.

Ordering is per-key, not global. Partitioning the log by primary key keeps every event for a given row in order relative to each other, which is the guarantee that actually matters. There is no practical way to guarantee a single global order across every row in the table at this scale, and none of the three downstreams need one.

What has to be true for each downstream, and why it's different:

Idempotency is not optional for any of them. Because delivery from the log is normally at-least-once (a consumer can see the same event again after a restart or a retry), every downstream apply logic has to be safe to run twice, which is why the version or offset check matters more than the transport's own guarantees.

Schema tolerance matters as much as ordering. A column added upstream should be ignorable by a consumer that doesn't know about it yet; a column removed upstream should degrade to a default rather than break parsing. A consumer that can't tolerate either will silently drop or misparse records the moment the source schema changes, which is a common and quiet way "consistency" breaks in practice.

Eventual is not the same as guaranteed. Because each consumer applies independently and can lag or briefly misbehave, every downstream needs its own periodic reconciliation, comparing row counts or checksums against the source, rather than trusting the stream to have delivered everything correctly forever.

Worked example

Suppose the change log has 12 partitions and the source sustains 6,000 change events per second, evenly spread, so each partition carries:

If the search-index consumer degrades and can only index 400 events per second per partition, each partition accumulates a backlog of:

After 5 minutes (300 seconds) of that degraded rate, each partition has queued:

At the degraded processing rate of 400 events/sec, working through that backlog takes:

So at that moment the search index is roughly 75 seconds behind the source, and the gap keeps growing as long as the consumer stays degraded, which is exactly the kind of staleness number a team would want on a dashboard for this specific downstream, since 75 seconds might be fine for search but would already be a problem for the cache.

flowchart LR
    A[Source database] --> B[CDC capture]
    B --> C[Partitioned log, keyed by primary key]
    C --> D[Cache sink]
    C --> E[Search index sink]
    C --> F[Warehouse sink]
    D --> G[Reconciliation vs source]
    E --> G
    F --> G

Trade-offs & pitfalls

• Treating "consistency" as one uniform target across all three sinks is the most common wrong turn: a warehouse can tolerate hours of lag, a cache serving live reads cannot tolerate more than seconds without visibly confusing users.
• Writing to all three sinks synchronously from the source, instead of fanning out from one shared log, couples the source's write latency and availability to the slowest downstream and removes any ability to retry one sink in isolation.
• A schema change is the most common cause of a silent consistency break; a consumer that isn't tolerant of an added or removed field can drop or misparse records with no visible error.
• Assuming ordering holds across the whole stream, rather than just per key, leads to logic that quietly breaks the first time two different keys' events are legitimately processed out of relative order.
• Skipping periodic reconciliation because "the stream should be reliable" is how silent drift goes unnoticed for weeks; every downstream needs an independent way to detect that it no longer matches the source.

Direct answer

Write-audit-publish means landing new data in a staging location consumers can't see yet, running data-quality checks (the audit) against it while it is still invisible, and only then making it visible with a single atomic operation (a partition swap or a pointer flip), rather than publishing first and validating afterward. The audit step exists to solve the problem of consumers reading bad or partial data during a load: without it, "load then check" means anyone querying during or right after a bad load already saw the wrong numbers before any check had a chance to catch it.

Structured elaboration

The problem this solves. A pipeline that writes directly into the table or partition consumers actually query, then validates afterward if at all, leaves a window between when writes start and when a check would have failed, during which consumers can read incomplete or wrong data with no visible signal anything is wrong.

The three stages:

1. Write. Land the new batch in an isolated location, a staging table, a new partition or version, a new file set, that existing queries against the published location cannot see at all.
2. Audit. Run validation against that staged data while it remains invisible to consumers: row counts against expectation, null rates, schema conformance, referential checks, business-rule checks.
3. Publish. Only if the audit passes, flip visibility with a single atomic operation, an atomic partition swap, a metadata pointer update, or a table rename, so consumers see either the previous, already-audited state or the new, now-audited state, never a partial mix of the two.

Why the publish step has to be atomic. If publishing itself were multiple steps (remove old rows, then insert new ones), a reader querying in the middle could see an inconsistent partial state even after the audit already passed. The pattern only fully solves the visibility problem if the very last step is a single, indivisible flip.

What happens when the audit fails. The staged batch is simply never published. Production keeps serving the last good, already-audited state, and the failed batch goes to remediation instead of ever becoming visible to anyone.

Where it fits and where it doesn't. This suits batch or micro-batch loads into a location with many downstream readers who can't each validate before consuming, like a shared warehouse table. Strict low-latency streaming, where staging and swapping an entire batch isn't practical, achieves the same underlying goal (don't let one bad unit block or corrupt everyone) through per-record dead-letter handling instead.

Worked example

A daily table normally holds 2,000,000 rows. A join bug in that day's load causes fanout, and the staged batch lands with 2,600,000 rows:

If the audit's row-count check allows a tolerance band of plus-or-minus 5 percent around the historical baseline, a 30 percent deviation fails it clearly, so the atomic swap never happens. Consumers keep reading yesterday's correct 2,000,000-row table while the bad batch sits quarantined and the join bug gets fixed, instead of every downstream report briefly, or permanently until someone happens to notice, reflecting a 30 percent inflated count.

flowchart LR
    A[New batch written to staging] --> B[Audit checks run on staged data]
    B -->|pass| C[Atomic publish: swap or pointer flip]
    B -->|fail| D[Staged data quarantined]
    C --> E[Consumers read new data]
    D --> F[Remediation and retry]
    F --> A

Trade-offs & pitfalls

• Auditing the already-published table instead of the staged one only catches the problem after consumers may already have read bad data; the ordering, audit before publish rather than after, is the entire point of the pattern.
• Making publish itself a multi-statement operation reintroduces the exact partial-visibility problem the pattern exists to prevent.
• The pattern trades some latency (data isn't visible until the audit finishes) and some storage (staged and published copies briefly coexist) for that safety; that's a clear win for a widely-consumed shared table, and a less obvious win for a low-stakes, single-consumer dataset where the extra latency may not be worth it.
• A common wrong turn is treating "we run some validation somewhere in the pipeline" as equivalent to write-audit-publish; the pattern specifically depends on the audited data being invisible to consumers until it passes, not just checked at some point along the way.

Direct answer

Treat this as a data-quality incident, not a join bug: scope the blast radius first (every table, feature, and join that references the identifier), stop the bad data from continuing to propagate, and only then build a compatibility fix. Patching the join logic in place while the source keeps sending the new format just moves the failure somewhere else instead of resolving it.

Structured elaboration

Contain (minutes to hours):

• Scope affected downstream tables and pipelines using lineage (whatever tracks table-to-table dependencies in your stack), not just the one join that paged someone.
• Pause or reroute the source connector so new records land in a quarantine area instead of flowing straight into production joins.
• Where correctness matters more than freshness, roll consumers back to last-known-good cached data or safe feature defaults rather than serving nulls.

Restore (hours to a day):

• Build a translation layer between the old and new identifier formats: a deterministic parse-back where one exists, or a lookup table populated by a one-time sync against the source system where it does not.
• Backfill the affected window through that mapping layer, using idempotent writes so a retried backfill cannot double-write.
• Add a validation gate at ingestion that rejects type-mismatched identifiers going forward instead of letting them silently coerce to null.

Prevent (following days/weeks):

• Put a schema or contract registry in front of the external source so a future format change requires a version bump and advance notice instead of arriving unannounced.
• Add an adapter or facade layer between the external source and your canonical internal model, so the next format change is absorbed at the boundary instead of rippling into every downstream consumer.
• Define an explicit change-notification SLA (service-level agreement) with the source's owning team for anything that would break the join key's shape.

Worked example

Say customer_id moves from an integer to a UUID-shaped string. Joins keyed on customer_id do not error, they silently produce nulls on type mismatch, which is exactly why this tends to surface as "features are null" rather than a job failure. The detection signal is a join match-rate monitor: if this join historically matched roughly 98% of rows and, starting the day of the source's change, the match rate for newly arriving records drops toward near zero, that is visible immediately in a daily row-count or match-rate check. For a source sending 500,000 records/day, a drop from a 98% match rate to a 2% match rate is the difference between about 490,000 and 10,000 successfully joined rows in a single day:

$0.98 \times 500{,}000 = 490{,}000 \quad \text{versus} \quad 0.02 \times 500{,}000 = 10{,}000$

a change large enough that a basic daily monitor catches it well before a consumer has to report it.

Trade-offs & pitfalls

Patching the join to "just accept both formats" without an explicit mapping table hides the symptom instead of fixing it, and produces inconsistent keys once both formats coexist in the same table long-term. Racing to restore joins before scoping the blast radius risks joining the wrong entities entirely if the format change also changed what the identifier represents, not just its shape. Relying on informal, person-to-person coordination with the source team as the only prevention measure does not survive that person changing roles; the registry and facade layer are what make prevention durable.

Direct answer

Medallion layering organizes a lakehouse into three progressively refined storage layers: bronze holds raw data exactly as it arrived, append-only and unvalidated; silver holds cleaned, deduplicated, and conformed data still close to the source's row-level grain; and gold holds business-level aggregates and marts shaped for direct consumption by dashboards and analysts. Each layer's job is to isolate a different kind of change, source format drift, cleaning-logic bugs, business-definition changes, so fixing one kind of problem only requires reprocessing from the layer below it, not re-ingesting from the original source.

Structured elaboration

Bronze (raw). Ingested as-is from the source, whatever format it arrives in, append-only, tagged with ingestion metadata (source, timestamp, batch identifier), with no business logic applied. Its job is to be the durable, replayable record of exactly what arrived, so that if a downstream bug is found months later, it can be reprocessed without hitting the source system again.

Silver (cleaned and conformed). Parses and validates bronze, deduplicates, standardizes types and null handling, applies a schema, joins to reference or dimension data, and resolves late-arriving or out-of-order records. Still roughly one row per source record. Its job is to give every downstream consumer one trusted version of what the data means, instead of every team re-implementing its own cleaning logic against raw bronze.

Gold (curated, business-level). Aggregates and denormalizes silver into tables shaped for a specific consumption pattern, a business intelligence dashboard, an ML feature set. Its job is to encode business definitions, what counts as an active user, how revenue is recognized, in one governed place rather than scattered across every consumer's own query.

Why layer at all. Each layer isolates a distinct failure or change domain. A source schema change only requires a bronze or silver fix, not a full re-ingest. A redefinition of a business metric only touches gold, not the whole history underneath it. Reprocessing a layer replays from the layer directly below it, which is what makes iterative fixes cheap instead of full-pipeline reruns.

flowchart LR
  S[Source systems] --> Br[Bronze: raw, append-only]
  Br --> Si[Silver: cleaned, deduped, conformed]
  Si --> Go[Gold: business aggregates and marts]
  Go --> BI[Dashboards / BI tools]
  Br -.reprocess.-> Si
  Si -.reprocess.-> Go

Worked example

A source system emits 50 million raw events per day into bronze, roughly 2 KB per event, appended without filtering:

Silver deduplicates retries (say 2% of events are duplicate deliveries) and joins to a customer dimension:

Gold aggregates this down to one row per customer per day. If 3,000,000 distinct customers were active that day, gold's daily table has 3,000,000 rows. A dashboard that queried silver directly on every refresh, at roughly 50 bytes per aggregate row it actually needs, would scan far more than necessary:

That ratio is the concrete reason gold exists as its own layer rather than letting every dashboard query silver directly.

Trade-offs & pitfalls

Skipping silver and going straight from bronze to gold means every gold table re-implements its own cleaning and deduplication logic, so three marts built by three teams can quietly disagree on something as basic as how many customers were active on a given day, because their bespoke cleaning diverged. Over-layering, adding a fourth or fifth stage without a distinct kind of change to isolate, adds storage and pipeline hops without buying anything; the right number of layers is however many genuinely different failure domains exist, not a mandatory three. Bronze must stay genuinely raw and immutable: the moment "a little cleaning" is quietly applied on the way into bronze for convenience, the replay guarantee that is the entire point of the layer is lost.

Direct answer

Schema evolution is a deliberate, managed change to a data contract over time, such as adding a field or widening a type; schema drift is the same kind of change happening unmanaged, whenever a producer changes its output without coordinating with consumers. It is risky with many downstream consumers because each one decoded the old contract into its own assumptions (column order, types, required fields), and a change that looks harmless to the producer, such as renaming a field or tightening a type, can silently break, or worse silently mis-parse, every consumer nobody told.

Structured elaboration

Evolution, drift, and enforcement

Enforcement and evolution are complementary controls, not opposites: enforcement decides what is allowed to change at all, rejecting anything not covered by a known, versioned schema; evolution defines the compatible ways a schema is allowed to change without being rejected. A pipeline that only enforces, rejecting anything not byte-identical to the original schema, turns every legitimate producer change into an outage; a pipeline that only tolerates drift, accepting anything and adapting silently, makes every accidental change invisible until a consumer breaks downstream.

Why more consumers means more risk

Every additional consumer is another independent assumption about the contract, a required field it always expects, a type it parses a specific way, a column it reads by position. A change compatible with nine consumers can still break the tenth. Consumers rarely deploy in lockstep with a producer's change, so even a compatible change has a window where old and new consumers coexist and must both keep functioning against whichever schema version they actually receive. The failure mode is often silent rather than a crash: a numeric field that starts arriving as a string might get parsed as zero or null instead of raising an error, corrupting downstream aggregates without anyone noticing until the numbers look wrong.

Making evolution safe instead of just possible

Classify every proposed change as backward-compatible (old consumers can still read new data, such as adding an optional field with a default), forward-compatible (new consumers can still read old data), or breaking (neither), and require the first category for routine changes. Version the schema explicitly and require producers to register a change before shipping, so an incompatible change is rejected before it ever reaches a consumer, enforcement and evolution working together. Give consumers a tolerant-reading discipline, ignoring unknown fields and applying explicit defaults for newly added ones, so the registration rules are not the only thing standing between a schema change and a broken consumer.

Worked example

A shared events contract has a field that has always held a small, fixed set of string values. A producer team widens it to include a new value without registering the change. Consumers that validate against the original fixed set now reject or mis-bucket every event carrying the new value; consumers that do not validate at all just silently mis-categorize those records in downstream reports. Had the change gone through a compatibility check first, adding a value while the field stays the same type is backward-compatible for consumers that do not hard-validate the value set, but a known breaking change for the ones that do, the validating consumers could have been notified and updated before the producer shipped, instead of the team finding out from a support ticket.

Trade-offs & pitfalls

• Enforcing so strictly that every legitimate, backward-compatible change requires a coordinated multi-team release pushes teams to bypass the pipeline entirely for "small" changes, which just relocates the drift somewhere else.
• Relying purely on tolerant reading everywhere and skipping registration entirely lets drift stay invisible until an aggregate looks wrong, by which point the root cause could be weeks old.
• Backward-only compatibility is cheaper to enforce than full, both-direction compatibility, but it constrains rolling a change back, since old consumers reading new data is covered while new consumers reading not-yet-upgraded old data is not.
• Treating schema evolution as purely a technical versioning problem is a common wrong turn; the real risk is coordinating independently deployed consumers, which a version number alone does not solve.