The decisions that kept a CDC reference data pipeline honest from the Kafka layer through to Silver, and what we learned when they were tested

Architecture diagrams lie, a little. Not on purpose. They show boxes and arrows in clean arrangements and make everything look sequential and tidy. What they cannot show is what fails first, what surprised you and which decisions you would fight the hardest to keep if someone wanted to simplify things.

This is about those decisions.

The goal was to move reference data from an on-premises MongoDB instance, the registered golden source for enterprise reference data, into a governed cloud pipeline, with Athena as the query surface and an enterprise data marketplace as the publication layer. Straightforward enough in theory — however, the complications were in the details, as they always are.

Why Three Layers and Not One

The obvious path is: Extract from MongoDB, put it somewhere in the cloud and let people query it. You can make that work, technically. What you end up with is a storage location that everyone gradually stops trusting, because it is never clear whether what’s in it reflects the current state of the source or a snapshot from two weeks ago, and the schema is whatever the last person who ran the extraction thought was sensible.

Three explicit layers — Landing, Bronze and Silver — were a direct answer to that. Each has a distinct responsibility, a different file format, a different retention policy and a different contract with the data.

Landing stores exactly what came off the Kafka stream: Raw JSON, timestamped, untransformed, held in Apache Iceberg tables with a 30-day archive policy. No business logic, no interpretation. When something goes wrong downstream, you can go back to Landing and know with confidence that it reflects what was in the source then. Thirty days cover any incident investigation cycle while keeping storage costs reasonable.

Bronze takes Landing’s raw data and establishes actual table structure, converting nested JSON to columnar Parquet format in Iceberg tables, with proper snapshots, schema evolution and time travel capability. The archive policy steps up to seven years for master data, reflecting the regulatory context we operate in. Bronze is its own stage rather than being collapsed into Landing because you want transformation failures to be visible and localized. If Bronze breaks, Landing is unaffected. You can fix the issue and reprocess without touching the arrival checkpoint.

Silver is what consumers see — shaped for analytical use, mandatory audit columns applied, quality-checked, queryable through Athena, stored as Parquet in Iceberg with seven-year retention. This is the product surface, and it needs to be held to a different standard than the intermediate layers. Blurring Bronze and Silver into one layer is a shortcut that makes debugging a nightmare.

Note: Landing stores raw arrival data as-is (30-day retention), its only job being fidelity. Bronze converts nested JSON to columnar Parquet with schema evolution and time travel (7-year retention). Silver is the consumer surface — one table per product, audit columns applied, Athena-queryable (7-year retention). Each layer has a separate contract and can fail, be fixed and be reprocessed independently.

What the Kafka Layer Actually Does

People describe Kafka as ‘the streaming layer’ and move on. The decisions inside the Kafka Connect configuration were where a lot of the pipeline’s trustworthiness was actually built.

Note: Inside k4a Kafka Connect — on the left, Error Handling: Failed events route to a Dead Letter Queue with a configurable retention period rather than being dropped or blocking the pipeline. On the right, Schema Registry: Topics are defined with partitioning strategies, and every event goes through a schema validation check before reaching the S3 Event Sink and Landing in AWS S3.

Two mechanisms ran in parallel inside Kafka Connect, and both were essential.

Dead Letter Queue (DLQ) for Operational Visibility: When a message failed, whether due to a malformed payload, type mismatch or unexpected nesting, it went to a DLQ with a configurable retention period rather than being silently dropped or blocking the stream. The DLQ was what turned “we noticed something was wrong three days later” into “we got alerted within 20 minutes and had the bad events right there to inspect.” The difference between those two outcomes is significant in any environment, but especially so when downstream teams treat the data as authoritative.

Schema Validation via a Schema Registry: Every event goes through schema validation before reaching the S3 sink. If a source-side change altered field names or types, the pipeline rejected the event at Kafka rather than writing garbage into Landing. Quiet corruption is the worst kind of data problem because you often do not find out until a consumer’s job breaks in production on a Friday afternoon. Early rejection trades a visible failure in a controlled place for a hidden failure discovered much later.

Together, these two things meant Landing could be treated as a trustworthy checkpoint rather than a dump of whatever came down the stream.

Two Transformation Stages, Two Different Jobs

It’s worth being precise about something here because it is easy to give the wrong impression. We are working with reference data from an authoritative golden source. The business requirement explicitly stated that no business-logic transformation would be applied. This is a one-to-one mapping from source to destination. We are not enriching, aggregating or deriving anything. The value proposition is faithful preservation.

However, ‘no transformation’ does not mean ‘no work’. MongoDB stores nested JSON documents. Analytical consumers need flat columns in Parquet. Getting from one to the other is a structural conversion, not a semantic transformation, but it is still a non-trivial pipeline stage that can fail.

Note: The transformation path from Landing to Silver — Stage 1 (Visual ETL) converts nested JSON from Landing to columnar Iceberg tables in Bronze, purely structural. Stage 2 (Visual ETL) partitions Bronze collections by data class and flattens them into individual Silver tables, one per product, with audit columns applied.

Stage 1 — Landing to Bronze: The job takes raw JSON from the landing path, flattens nested sub-documents into a columnar structure, deduplicates by key and writes the result as Parquet into an Iceberg table. A checksum validation confirms whether everything that left MongoDB arrived. No business semantics touched, no values changed. Structural conversion only.

Stage 2 — Bronze to Silver: A single MongoDB collection often holds multiple logical entity types — country codes, currency codes, organizational role types — all in one collection, because that is convenient for the operational system. For consumers, that is a mess. The Bronze-to-Silver stage splits each collection by data class into its own table. One product, one table. Governance becomes tractable because you can draw a boundary around each product.

Every Silver table gets a standard set of audit columns at this stage: CREATE_DATE_TIME, UPDATE_DATE_TIME, VALID_FROM and VALID_TO (distinguishing current from historical values), DELETE_FLAG (soft delete from the source system), CREATED_BY, UPDATED_BY, SOURCE_SYSTEM, JOB_NAME, JOB_RUN_ID, JOB_START_DTTM and JOB_END_DTTM. More on why these matter shortly.

Keeping these as separate pipeline stages means each one can fail, be fixed and be rerun independently. This matters more at 2 a.m. than any architectural elegance argument.

CDC: Not the Easy Part

Change data capture gets described like a solved problem. Extract the changes, apply them downstream and it’s done. What it actually gives you is events. The tricky part is ‘what you do with them’: Deduplication when events arrive out of order, applying deletes correctly via soft-delete flags rather than hard deletes, making sure a record that changed five times in an hour arrives downstream in the right final state.

The pipeline captures inserts, updates and deletes from MongoDB and applies them accurately to the target, validating the change order to ensure events are consumed in the correct sequence. After the initial full data load, all subsequent synchronization runs through CDC only, no reprocessing of the full dataset. The pipeline runs on a monthly batch cadence: The fifth of every month at 07:00 UTC, fully automated, no dependency on working days or holiday calendars.

The issue that generated the most support tickets, somewhat embarrassingly, was the absence of events. If nothing changed in MongoDB, nothing flows through the pipeline. That is correct behavior, entirely aligned with how CDC works. However, teams expecting a daily file to drop as confirmation that the pipeline was alive read ‘no new file’ as ‘something is broken’. We built an explicit no-change signal: A small indicator that the pipeline ran, checked, found nothing new and is healthy. Not glamorous engineering. It closed a significant number of unnecessary incidents.

Minimal Transformation is Not Minimal Responsibility

Since we were publishing authoritative reference data without enrichment, some stakeholders assumed the quality bar would be lighter. The logic was: We’re not changing much, so there is less to get wrong.

The opposite is true. When the value proposition is ‘we preserved the truth accurately’, validation is what proves you did that. The quality gates work in layers. Schema validation at Kafka is the first gate: A schema mismatch fails the job and alerts the reference data owner team. Basic data quality checks follow: Non-null enforcement for mandatory fields, allowed value validation for reference codes. Reconciliation runs between layers, record counts, null rates and key distributions, so any drift between Landing, Bronze and Silver surfaces quickly. Checksum logic at Landing confirms that everything that left MongoDB actually arrived. When 21 products all make the same promise, the validation proving that promise has to be airtight.

What Audit Columns Actually Do

I used to think of audit columns as compliance decoration. Then I watched a team spend three days on what turned out to be a simple question: Was this Silver record stale, soft-deleted or just unchanged since the last run?

With the audit columns in place, that is a five-minute query. VALID_FROM and VALID_TO tell you whether you are looking at a current or historical value. DELETE_FLAG tells you if the source system soft-deleted the record. JOB_RUN_ID and JOB_START_DTTM tell you exactly which pipeline run produced the record. SOURCE_SYSTEM confirms provenance.

Without them, it is a three-day archaeology project involving Airflow logs, Kafka offsets and escalating frustration. The pattern repeated across multiple incidents. Not dramatic data corruption, just the ordinary operational questions that come up constantly when data is shared across teams. Audit columns turn those questions from investigations into lookups.

What Made This a Platform Rather Than Just a Pipeline

A pipeline gets data from A to B. A platform is something people can build on without needing to understand all the plumbing underneath.

The difference was the data marketplace and what it forced. The endpoint for a finished product is not ‘the Silver table exists’. It is ‘the product is listed on the marketplace with metadata, a Kitemark quality score, documentation and subscription behavior’. Compliance with all active standards at deployment time is mandatory. Consumption occurs exclusively via the marketplace subscription model. Not a suggestion. An enforced constraint.

That enforcement is what makes naming conventions matter in practice rather than in principle. A consumer searching for a dataset finds it using enterprise-standard terminology, not the internal shorthand that made sense to the team that built it. The metadata framework transition to FDM mapping is unglamorous work. It is also what makes the catalog actually navigable.

The pipeline earned trust by being predictable. Schema-validated. Bad events are quarantined in the DLQ. JSON is structurally converted to Parquet. Data classes are partitioned into individual tables. Audit columns are consistently applied. Products published with documentation. Consumers query through Athena and subscribe through the marketplace. Nothing surprising.

In a large enterprise, nothing surprising is the goal.