Financial Services platforms routinely ingest XML and JSON embedded in opaque fields, creating tension between audit fidelity and analytical usability. This article presents a regulator-defensible approach to handling such payloads in the Bronze layer: landing raw data immutably, extracting only high-value attributes, applying attribute-level SCD2, and managing schema drift without data loss. Using hybrid flattening, temporal compaction, and disciplined lineage, banks can transform messy blobs into audit-grade Bronze assets while preserving point-in-time reconstruction and regulatory confidence.

Contents

Introduction: Turning messy semi-structured payloads into compliant, traceable, high-fidelity Bronze data for UK Financial Services

In Financial Services, upstream systems routinely embed XML or JSON payloads inside VARCHAR/CLOB fields. This is not an edge case — it is the norm. Core banking systems emit SWIFT XML in free-text columns; KYC vendors send JSON blobs wrapped inside opaque fields; CRM systems encode customer events as semi-structured objects; legacy platforms export entire records as XML fragments.

Yet a modern FS data platform must:

• preserve the raw blob intact for lineage and audit
• flatten the structure for analytics
• apply SCD2 to meaningful attributes
• detect schema drift safely
• ensure regulatory point-in-time reconstruction remains possible
• avoid Bronze-layer runaway expansion

Most banks fall into one of three failure modes:

• allow uncontrolled Bronze expansion driven by upstream drift
• flatten too early and destroy audit fidelity
• preserve raw blobs indefinitely without extracting meaning

This article provides a battle-tested, regulator-defensible approach used in mature UK banks and payment processors.

1. Why Embedded XML/JSON Is a Bronze-Layer Nightmare

Before discussing solutions, it is important to understand why embedded semi-structured payloads consistently break otherwise well-designed Bronze layers. The problem is not the data format itself, but the collision between flexible upstream payloads and the strict requirements of regulated, historical data platforms.

Embedded XML/JSON is a symptom of:

• legacy systems without schema evolution
• vendor products delivering “flexible payloads”
• payment messages where structure varies by product
• AML/KYC engines adding new fields mid-year
• CRM systems emitting verbose interaction objects

In a regulated FS context, poorly handled blobs create:

• broken lineage
• inconsistent SCD2 records
• schema explosion
• failed PIT reconstructions
• inability to satisfy PRA/FCA audit queries
• huge Bronze tables with redundant versions

Bronze must maintain raw fidelity, while Silver/Gold must remain stable and consumable. Blob ingestion sits at the fault line between these goals.

2. Common Scenarios in UK Financial Services

Embedded XML and JSON do not appear randomly; they arise from predictable operational and vendor patterns across UK Financial Services. Understanding where and why these payloads originate helps explain the constraints that Bronze architectures must be designed to accommodate.

2.1 Payments (SWIFT MT, ISO20022 XML)

• Deeply nested XML
• Optional fields vary by corridor
• Arrays of beneficiaries or charges
• Schema drift during regulatory upgrades (ISO20022 yearly releases)

2.2 AML/KYC Vendor Responses

• JSON with risk indicators, match scores, document scans
• Vendor updates add/remove fields without notice
• Near-duplicate payloads causing version spam

2.3 CRM and Customer Interactions

• SFDC/CRM JSON describing interactions, tasks, preferences
• Often includes embedded status/event arrays

2.4 Legacy Exports

• Entire record dumps in XML
• Mixed naming conventions
• Inconsistent casing or nesting

All these require Bronze handling that preserves truth and supports long-term SCD2.

3. Landing Raw Data in Bronze: Do’s and Don’ts

The landing pattern you choose at the point of ingestion determines whether your Bronze layer remains auditable for years or collapses under technical debt. This section establishes the non-negotiable rules that protect lineage, replayability, and regulatory defensibility from day one.

3.1 DO: Land the raw blob exactly as received — append-only and immutable

Please Note: This is the most important rule.

Your Bronze landing table should look like:

field	description
raw_blob	XML/JSON as delivered, unchanged
record_key	primary identifier or compound key
source_system	where it came from
ingest_timestamp	when it arrived
schema_version_detected	metadata captured at ingest
batch_id / event_id	lineage identifiers

Never mutate raw. Never pre-flatten. Never infer meaning at this stage.

3.2 DO: Add metadata, not transformations

Examples:

• SHA256 hash of the raw blob (for deterministic change detection and replay verification)
• size, encoding, version markers
• XML root element name or JSON top-level keys

3.3 DO NOT: Parse XML/JSON in the landing step

Parsing at ingest time creates:

• non-deterministic pipelines
• version drift
• irreversible schema errors
• lost lineage

Raw stays raw. Always.

4. Flattening Strategies: When and How

Once raw data is safely preserved, the question becomes how much structure to extract, and when. Flattening is not a binary decision but a spectrum of strategies, each with different implications for schema stability, SCD2 behaviour, and long-term cost.

SCD2 logic applied to blob-derived attributes behaves very differently from traditional relational sources. Without careful design, innocuous payload changes can trigger excessive versioning and undermine point-in-time accuracy.

After raw landing, a parsed_bronze layer extracts structure safely.

Partial flattening dramatically reduces false-positive SCD2 versioning caused by non-material payload changes.

There are three main patterns:

4.1 Full Flatten (Complete Normalisation)

Pros:

• SQL-friendly
• Great for reporting
  Cons:
• Breaks when schema changes
• Requires constant reprocessing
• Painful for deeply nested arrays

Use only for stable, well-defined schemas.

4.2 Partial Flatten (High-Value Attributes Only)

This is the dominant and regulator-safe choice for most Financial Services pipelines.

Extract only:

• identifiers (customer_id, account_id)
• volatile attributes (risk_score, match_status, address)
• data needed for SCD2 logic

Leave everything else as a variant/struct column.

This reduces:

• schema drift
• excessive SCD2 versioning
• reprocessing cost

4.3 Hybrid Flattening (Flatten + Retain Raw)

A combined pattern:

• Extract 5–20 high-value fields → columns
• Store full parsed struct → variant/struct
• Keep original raw XML/JSON → raw_blob

This supports:

• analytics
• lineage
• PIT reconstruction

Most mature banks adopt this hybrid.

5. Applying SCD2 to Flattened Data

SCD2 logic applied to blob-derived attributes behaves very differently from traditional relational sources. Without careful design, innocuous payload changes can trigger excessive versioning and undermine point-in-time accuracy.

Blob-derived fields can participate in SCD2.

Key considerations:

5.1 Row-Level vs Attribute-Level SCD2

Row-level SCD2 explodes when:

• blobs change frequently
• vendor schemas drift
• CRM systems issue many near-duplicate updates

Attribute-level SCD2 is preferred:

• only version specific changed attributes
• hash at attribute granularity
• do NOT version the whole blob unless required

Cross-reference: your Part 3 article on attribute-level SCD2.

5.2 Hashing Nested Payloads

When blobs contain structured objects, use:

• SHA256(raw_blob) for change detection
• hash(struct) for extracted attributes
• xxHash64 for fast comparisons

Only create new versions when the semantic fields change; when business-meaningful fields change, not when payload structure shifts.

5.3 Temporal Compaction for Volatile Payloads

CRM and KYC systems often send:

• 10 updates in 30 seconds
• duplicate risk assessments
• partial patches

Temporal compaction collapses these into a single SCD2 version.

From Part 3: “Group updates within a time window and emit one SCD2 record.”

6. Handling Inconsistent Schemas & Drift

Schema drift is inevitable in environments dominated by vendors, standards bodies, and legacy platforms. The goal of a mature Bronze layer is not to prevent drift, but to detect, control, and survive it without data loss or regulatory exposure.

Because drift is inevitable the bronze layer must be designed to survive it.

Schema metadata becomes part of your audit evidence, demonstrating controlled evolution rather than uncontrolled change.

6.1 Schema Inference and Evolution

At ingest, automatically detect:

• missing fields
• added fields
• type changes

Store this in a schema_metadata table.

6.2 Drift Detection Pipelines

When schema changes:

• alert data engineering
• trigger schema evolution
• produce a versioned schema record

6.3 Fallback Patterns

Use platform-native types:

• Snowflake VARIANT
• BigQuery STRUCT
• Delta Lake MAP/STRUCT
• Iceberg/Hudi schema evolution

When flattening fails, store parsed struct only; extract later.

6.4 Backfill & Historical Repair

If schema changes introduce new fields:

1. Reparse historical blobs
2. Populate new columns
3. Adjust SCD2 history
4. Recompute PITs where needed

Cross-reference: Part 4 (Golden-Source Precedence & PIT).

7. Platform-Specific Patterns

While the architectural principles are platform-agnostic, modern data platforms provide different primitives for handling semi-structured data. This section highlights how common platforms support the patterns described, without prescribing a single vendor-specific solution.

These examples illustrate platform capabilities, not prescriptive implementations.

7.1 Databricks / Delta Lake

• from_xml(), from_json()
• Auto Loader schema evolution
• Delta Change Data Feed for incremental extractions

7.2 Snowflake

• PARSE_XML(), PARSE_JSON()
• VARIANT for hybrid storage
• Efficient semi-structured flattening with LATERAL FLATTEN

7.3 BigQuery / Iceberg / Hudi

• Nested STRUCT support
• Auto schema merge
• Efficient MERGE for SCD2

8. Regulatory Considerations

In Financial Services, technical correctness is insufficient without regulatory defensibility. Bronze-layer design decisions must be explainable under retrospective scrutiny, often years after the fact, and by audiences who care more about reconstruction and lineage than platform elegance.

For embedded XML and JSON payloads, regulators such as the PRA and FCA implicitly expect the platform to demonstrate four capabilities.

8.1 Raw Evidence Preservation

The platform must retain the original payload exactly as received. This is not a convenience requirement; it is evidentiary.

Flattened attributes, derived fields, and analytical representations are not considered primary evidence during regulatory review. The immutable raw payload provides the authoritative source from which all interpretations must be traceable.

This is why raw XML/JSON must remain:

• append-only,
• immutable,
• and independently addressable for replay and inspection.

8.2 Attribute-to-Payload Traceability

Regulators expect the ability to trace any reported attribute back to its originating location within the source payload.

This does not require pre-flattening every field, but it does require:

• deterministic parsing logic,
• stable node-path references,
• and the ability to demonstrate how an attribute was derived at the time it was believed to be true.

Hybrid flattening supports this by preserving both the raw payload and the parsed structural representation alongside promoted attributes.

8.3 Point-in-Time (PIT) Reconstruction

Many regulatory questions are framed retrospectively:

• What did the firm know at the time?
• Which data informed the decision?
• What version of the upstream payload was available?

Attribute-level SCD2, combined with immutable raw payload retention, enables precise reconstruction of historical states without conflating later corrections or schema evolution.

Row-level SCD2 over volatile payloads, by contrast, often obscures this reconstruction by introducing excessive, non-semantic versioning.

8.4 Deterministic Reprocessing Under Challenge

Regulators will not accept “we can’t reproduce it anymore” as a valid explanation.

The platform must be able to:

• reprocess historical payloads,
• apply revised interpretation logic,
• and demonstrate how conclusions would differ under updated understanding.

By separating raw preservation from interpretation, and by centralising SCD2 in Bronze, the platform remains capable of deterministic rebuilds even as schemas, models, and business understanding evolve.

8.5 What Regulators Do Not Expect

It is equally important to be clear about what regulators do not require:

• They do not require every upstream field to be flattened into columns.
• They do not require immediate analytical usability of raw payloads.
• They do not require schema stability from vendors.

They do require honesty about time, change, and interpretation.

9. Real-World Examples (Anonymised)

Abstract patterns are best validated through real outcomes. These anonymised cases demonstrate how the principles described translate into measurable improvements in auditability, storage efficiency, and operational resilience.

9.1 Case 1 — UK Bank Handling SWIFT XML

• ISO20022 MT messages embedded in CLOB
• Used hybrid extract + struct
• Reduced Bronze versioning by 65%
• Enabled AML PIT reconstruction for sanctions cases

9.2 Case 2 — AML Vendor Schema Drift

• Vendor added new <risk_model_v3> node
• Drift detection caught change
• New attribute added without breaking existing pipelines
• Backfilled 4 years of history with version-safe merge

9.3 Case 3 — CRM JSON Expansion

• CRM pushed multiple partial updates per session
• Temporal compaction collapsed 17 updates to 1
• Bronze size reduced by 80%

10. Architectural Alignment: Choosing the Correct Flattening Pattern for a highly regulated UK Financial Services organisation

The handling of embedded XML and JSON in Bronze is not an isolated technical choice; it is a direct consequence of the platform’s precursor and foundational architectural decisions. Given ingestion maximalism, immutable append-only history, centralised SCD2, streaming-first eventual consistency, and deterministic rebuild requirements, only one flattening posture is fully compatible with the architecture.

10.1 The Mandated Pattern: Hybrid Flattening with Attribute-Level SCD2

Under these principles, the platform must adopt hybrid flattening as the default and enforce attribute-level SCD2 as the only acceptable versioning strategy for blob-derived data.

This means:

• Raw XML/JSON is always preserved verbatim in Bronze, append-only and immutable, satisfying:
  • P1 (History held once, centrally, canonically)
  • P2 (Append-only, immutable history)
  • P3 (Bronze as system of record)
  • Deterministic rebuild and regulatory replay
• A constrained set of high-value attributes is selectively extracted, driven by:
  • Domain relevance (not upstream structure)
  • SCD2 participation
  • Regulatory, risk, and analytical materiality
    This extraction is intentionally incomplete and explicitly non-authoritative.
• The full parsed structure is retained as a semi-structured column, enabling:
  • Deferred interpretation
  • Backfill and schema evolution
  • Reconstruction of historical meaning as understanding evolves

This hybrid approach is not a compromise. It is the only flattening pattern that preserves optionality while preventing uncontrolled Bronze expansion.

10.2 Why Full Flattening Violates the Architecture

Full normalisation of embedded payloads is fundamentally incompatible with the platform doctrine:

• It forces premature semantic decisions, violating ingestion maximalism.
• It binds Bronze to volatile vendor schemas, undermining deterministic rebuild.
• It amplifies schema drift into version explosions, breaking centralised SCD2.
• It couples Bronze to analytical convenience, contradicting P3 (Bronze is not a staging area).

Full flattening may appear analytically attractive in the short term, but under regulatory time horizons it creates irreversible architectural debt.

10.3 Why Partial Flattening Alone Is Insufficient

Partial flattening without retaining the full parsed structure also fails the doctrine:

• It limits future reinterpretation of historical facts.
• It constrains Platinum semantic evolution.
• It weakens forensic reconstruction during regulatory inquiry.

Under P6 (Deterministic rebuild) and P7 (Conceptual model lives in Platinum), the platform must always retain enough raw and structured context to reinterpret history as understanding improves.

10.4 SCD2 Implications: Attribute-Level or Bust

Given centralised SCD2 (P4) and the separation of temporal and current-state truth (P5):

• Row-level SCD2 on blob-derived tables is explicitly disallowed
• Only business-meaningful attributes participate in SCD2
• Structural, ordering, or schema-only changes must never trigger new versions

Hashing strategies must reflect this separation:

• Raw blob hashes support lineage and replay verification
• Attribute-level hashes control version emission
• Temporal compaction is mandatory for high-frequency event streams

This ensures that SCD2 growth reflects changes in meaning, not noise in representation.

10.5 Streaming, Drift, and Rebuildability

The hybrid pattern aligns cleanly with streaming-first eventual consistency:

• Late-arriving corrections are additive, not mutative
• Schema drift is detected, versioned, and absorbed without pipeline failure
• Historical backfills are possible without re-ingestion
• Silver and Gold remain rebuildable and disposable

Most importantly, the platform remains explainable under audit — not just correct.

10.6 Architectural Decision Summary

Given the stated principles, the platform must:

• Adopt hybrid flattening as the standard pattern for embedded XML/JSON
• Enforce immutable raw preservation in Bronze
• Apply attribute-level SCD2 only
• Treat schema drift as expected, not exceptional
• Preserve deterministic rebuild and semantic optionality

Any alternative pattern is not merely suboptimal — it directly violates the architectural doctrine under which the platform operates.

10.7 Preserving the Full Picture and SQL Reasoning in Bronze

A legitimate concern with hybrid flattening is the perceived loss of completeness and usability at the Bronze layer — specifically, the fear that if not everything is flattened, the platform no longer holds the “full picture”, and that historically powerful SQL-based SCD2 reasoning becomes harder or impossible.

This concern is not philosophical. It is rooted in hard-won experience: fully flattened SCD2 Bronze tables make temporal reasoning explicit, queryable, and operationally reliable. Any pattern that undermines this must be treated as suspect.

The resolution lies in being precise about what “full picture” actually means in a regulated, temporal system.

In this architecture, completeness is not defined by immediate relational visibility. It is defined by the platform’s ability to deterministically answer, years later, the questions: what was known at the time, why was it believed, and what representation did it originate from. Hybrid flattening preserves this more faithfully than full normalisation.

Under the hybrid pattern, Bronze retains three distinct but complementary representations of the same fact:

1. The raw payload, preserved verbatim, append-only and immutable, providing unambiguous lineage, forensic traceability, and regulatory replay.
2. The parsed structural representation, stored as a semi-structured column, capturing full schema richness without committing to premature relational shape.
3. A governed set of promoted attributes, expressed as first-class columns, explicitly chosen because they participate in temporal truth, regulatory interpretation, or cross-domain reasoning.

Nothing is removed. Nothing is hidden. What changes is when meaning becomes authoritative.

The concern about “losing SQL” arises when Bronze is expected to be simultaneously:

• a canonical historical store,
• a fully relational analytical model,
• and a stable semantic contract.

That expectation cannot survive schema drift, vendor evolution, and multi-year regulatory timelines.

Instead, the architecture draws a deliberate boundary: Bronze guarantees SQL legibility for temporal logic, not for unlimited semantic exploration.

All SCD2 mechanics, effective dating, point-in-time reconstruction, late-arriving correction handling, and deterministic rebuild, remain expressed in simple, declarative SQL over columnar attributes. These attributes are not incidental; they are explicitly curated because the platform has committed to their long-term semantic stability.

What is intentionally not guaranteed is that every upstream field, structural variation, or vendor-specific construct is immediately queryable as a column. Requiring that guarantee would force Bronze to absorb schema volatility directly into SCD2, causing uncontrolled versioning, rebuild fragility, and semantic lock-in to transient representations.

To prevent erosion of day-to-day usability, the architecture imposes a non-negotiable guardrail:

Any attribute required for frequent temporal reasoning, regulatory reporting, reconciliation, or cross-domain analysis must be promoted to a first-class Bronze column and governed under centralised SCD2.

This is not an exception path. It is the primary mechanism by which SQL ergonomics are preserved where they matter.

Attributes not yet promoted are not second-class citizens; they are semantically undecided. They remain fully present, fully replayable, and fully promotable later — including retroactively through deterministic backfill — when their importance becomes clear.

This distinction matters because Financial Services platforms are not judged on how easy they are to query today, but on whether they can still explain yesterday under new questions, new regulations, and new understanding.

Full flattening optimises for immediate convenience at the cost of long-term correctness. Hybrid flattening accepts controlled complexity in order to preserve optionality, auditability, and semantic evolution without sacrificing SQL-based temporal reasoning where it is genuinely required.

Bronze does not become less powerful under this model.
It becomes harder to misuse… and far more resilient over time.

11. Conclusion: Turn Messy Blobs into Audit-Grade Bronze

Embedded XML and JSON are not anomalies to be eliminated, but inputs to be governed. This conclusion ties together ingestion discipline, selective flattening, SCD2 design, and regulatory alignment into a single, defensible Bronze philosophy.

This leads to the realisation that:

• Embedded XML/JSON is not a problem: it is a reality.
• Bronze is not a dumping ground: it is a regulatory contract with the future.

The maturity comes from:

• landing raw blobs safely
• flattening only what matters
• applying attribute-level SCD2
• managing schema drift gracefully
• maintaining audit-grade lineage

Handled correctly, XML/JSON becomes a first-class Bronze asset, not a liability. In regulated Financial Services, this is not one safe path; it is the only safe path to historical fidelity, analytical clarity, and regulator confidence.