Structure diagrams lie, just a little. Not on function. They present containers and arrows in clear preparations and make the whole lot look sequential and tidy. What they can’t present is what fails first, what stunned you, and which selections you’ll battle hardest to maintain if somebody needed to simplify issues.
That is about these selections.
The objective was to maneuver reference knowledge from an on-premises MongoDB occasion, the registered golden supply for enterprise reference knowledge, right into a ruled cloud pipeline, with Athena because the question floor and an enterprise Information Market because the publication layer. Easy sufficient in principle. The problems had been within the particulars, as they at all times are.
Why Three Layers and Not One
The apparent path is: extract from MongoDB, put it someplace within the cloud, let folks question it. You can also make that work, technically. What you find yourself with is a storage location that everybody regularly stops trusting, as a result of it’s by no means clear whether or not what’s in it displays the present state of the supply or a snapshot from two weeks in the past, and the schema is regardless of the final one who ran the extraction thought was smart.
Three express layers, Touchdown, Bronze, Silver had been a direct reply to that. Every has a definite accountability, a unique file format, a unique retention coverage, and a unique contract with the info.
Touchdown shops precisely what got here off the Kafka stream: uncooked JSON, timestamped, untransformed, held in Apache Iceberg tables with a 30-day archive coverage. No enterprise logic, no interpretation. When one thing goes mistaken downstream, you possibly can return to Touchdown and know with confidence it displays what was within the supply at that cut-off date. Thirty days covers any incident investigation cycle whereas maintaining storage prices affordable.
Bronze takes Touchdown’s uncooked knowledge and establishes precise desk construction, changing nested JSON to columnar Parquet format in Iceberg tables, with correct snapshots, schema evolution, and time journey functionality. The archive coverage steps as much as seven years for grasp knowledge, reflecting the regulatory context we function in. Bronze is its personal stage relatively than being collapsed into Touchdown since you need transformation failures to be seen and localised. If Bronze breaks, Touchdown is unaffected. You may repair the difficulty and reprocess with out touching the arrival checkpoint.
Silver is what customers see. Formed for analytical use, necessary audit columns utilized, quality-checked, queryable by means of Athena, saved as Parquet in Iceberg with seven-year retention. That is the product floor, and it must be held to a unique customary than the intermediate layers. Blurring Bronze and Silver into one layer is a shortcut that makes debugging a nightmare.
What the Kafka Layer Truly Does
Individuals describe Kafka as “the streaming layer” and transfer on. The choices contained in the Kafka Join configuration had been the place a whole lot of the pipeline’s trustworthiness was really constructed.
Two mechanisms ran in parallel inside Kafka Join, and each had been important.
Lifeless Letter Queue for operational visibility. When a message failed, whether or not as a result of a malformed payload, kind mismatch, or sudden nesting, it went to a DLQ with a configurable retention interval relatively than being silently dropped or blocking the stream. The DLQ was what turned “we seen one thing was mistaken three days later” into “we obtained alerted inside twenty minutes and had the unhealthy occasions proper there to examine.” The distinction between these two outcomes is critical in any atmosphere, however particularly so when downstream groups deal with the info as authoritative.
Schema validation through a Schema Registry. Each occasion goes by means of schema validation earlier than reaching the S3 sink. If a source-side change altered discipline names or sorts, the pipeline rejected the occasion at Kafka relatively than writing rubbish into Touchdown. Quiet corruption is the worst form of knowledge drawback, since you typically don’t discover out till a shopper’s job breaks in manufacturing on a Friday afternoon. Early rejection trades a visual failure in a managed place for a hidden failure found a lot later.
Collectively, these two issues meant Touchdown could possibly be handled as a reliable checkpoint relatively than a dump of no matter got here down the stream.
Two Transformation Phases, Two Completely different Jobs
Price being exact about one thing right here, as a result of it’s simple to present the mistaken impression. We’re working with reference knowledge from an authoritative golden supply. The enterprise requirement explicitly said that no business-logic transformation can be utilized. This can be a one-to-one mapping from supply to vacation spot. We’re not enriching, aggregating, or deriving something. The worth proposition is devoted preservation.
However “no transformation” doesn’t imply “no work.” MongoDB shops nested JSON paperwork. Analytical customers want flat columns in Parquet. Getting from one to the opposite is structural conversion, not semantic transformation, however it’s nonetheless a non-trivial pipeline stage that may fail.
Stage 1: Touchdown to Bronze. The job takes uncooked JSON from the touchdown path, flattens nested sub-documents right into a columnar construction, deduplicates by key, and writes the outcome as Parquet into an Iceberg desk. A checksum validation confirms the whole lot that left MongoDB arrived. No enterprise semantics touched, no values modified. Structural conversion solely.
Stage 2: Bronze to Silver. A single MongoDB assortment typically holds a number of logical entity sorts: nation codes, forex codes, organisational function sorts, multi functional assortment as a result of that’s handy for the operational system. For customers, that may be a mess. The Bronze-to-Silver stage splits every assortment by knowledge class into its personal desk. One product, one desk. Governance turns into tractable as a result of you possibly can draw a boundary round every product.
Each Silver desk will get a normal set of audit columns at this stage: CREATE_DATE_TIME, UPDATE_DATE_TIME, VALID_FROM and VALID_TO (distinguishing present from historic values), DELETE_FLAG (comfortable delete from the supply system), CREATED_BY, UPDATED_BY, SOURCE_SYSTEM, JOB_NAME, JOB_RUN_ID, JOB_START_DTTM, and JOB_END_DTTM. Extra on why these matter shortly.
Protecting these as separate pipeline phases means every one can fail, be fastened, and be rerun independently. That issues extra at 2am than any architectural class argument.
CDC: Not the Simple Half
Change knowledge seize will get described like a solved drawback. Extract the modifications, apply them downstream, performed. What it really offers you is occasions. The difficult components are what you do with them: deduplication when occasions arrive out of order, making use of deletes accurately through soft-delete flags relatively than laborious deletes, ensuring a report that modified 5 instances in an hour arrives downstream in the best last state.
The pipeline captures inserts, updates, and deletes from MongoDB and applies them precisely to the goal, validating the change order to ensure occasions are consumed within the right sequence. After the preliminary full knowledge load, all subsequent synchronization runs by means of CDC solely, no reprocessing of the complete dataset. The pipeline runs on a month-to-month batch cadence: the fifth of each month at 07:00 UTC, totally automated, no dependency on working days or vacation calendars.
The difficulty that generated probably the most help tickets, considerably embarrassingly, was the absence of occasions. If nothing modified in MongoDB, nothing flows by means of the pipeline. That’s right behaviour, totally aligned with how CDC works. However groups anticipating a day by day file drop as affirmation the pipeline was alive learn “no new file” as “one thing is damaged.” We constructed an express no-change sign: a small indicator that the pipeline ran, checked, discovered nothing new, and is wholesome. Not glamorous engineering. It closed a major variety of pointless incidents.
Minimal Transformation Is Not Minimal Accountability
As a result of we had been publishing authoritative reference knowledge with out enrichment, some stakeholders assumed the standard bar can be lighter. The logic was: we’re not altering a lot, so there’s much less to get mistaken.
The alternative is true. When the worth proposition is “we preserved the reality precisely,” validation is what proves you probably did that. The standard gates work in layers. Schema validation at Kafka is the primary gate: a schema mismatch fails the job and alerts the reference knowledge proprietor workforce. Primary knowledge high quality checks observe: non-null enforcement for necessary fields, allowed worth validation for reference codes. Reconciliation runs between layers, report counts, null charges, key distributions, so any drift between Touchdown, Bronze, and Silver surfaces shortly. Checksum logic at Touchdown confirms the whole lot that left MongoDB really arrived. When twenty-one merchandise all make the identical promise, the validation proving that promise needs to be hermetic.
What Audit Columns Truly Do
I used to consider audit columns as compliance ornament. Then I watched a workforce spend three days on what turned out to be a easy query: was this Silver report stale, soft-deleted, or simply unchanged because the final run?
With the audit columns in place, that may be a five-minute question. VALID_FROM and VALID_TO let you know whether or not you’re looking at a present or historic worth. DELETE_FLAG tells you if the supply system soft-deleted the report. JOB_RUN_ID and JOB_START_DTTM let you know precisely which pipeline run produced the report. SOURCE_SYSTEM confirms provenance.
With out them, it’s a three-day archaeology challenge involving Airflow logs, Kafka offsets, and escalating frustration. The sample repeated throughout a number of incidents. Not dramatic knowledge corruption, simply the bizarre operational questions that come up consistently when knowledge is shared throughout groups. Audit columns flip these questions from investigations into lookups.
What Made This a Platform Slightly Than Only a Pipeline
A pipeline will get knowledge from A to B. A platform is one thing folks can construct on without having to know all of the plumbing beneath.
The distinction was the Information Market and what it compelled. The endpoint for a completed product just isn’t “the Silver desk exists.” It’s “the product is listed within the Market with metadata, a Kitemark high quality rating, documentation, and subscription behaviour.” Compliance with all energetic requirements at deployment time is necessary. Consumption happens completely through the Market subscription mannequin. Not a suggestion. An enforced constraint.
That enforcement is what makes naming conventions matter in observe relatively than in precept. A shopper looking for a dataset finds it utilizing enterprise-standard terminology, not the interior shorthand that made sense to the workforce that constructed it. The metadata framework transition to FDM mapping is unglamorous work. Additionally it is what makes {the catalogue} really navigable.
The pipeline earned belief by being predictable. Schema validated. Dangerous occasions quarantined within the DLQ. JSON structurally transformed to Parquet. Information courses partitioned into particular person tables. Audit columns constantly utilized. Merchandise printed with documentation. Customers querying by means of Athena and subscribing by means of the Market. Nothing shocking.
In a big enterprise, nothing shocking is the objective.
