Operational Edge Projection + CDC Write-Back: A Minimal Pattern for Low-Latency Authority Without Breaking Temporal Truth

This article introduces a practical operational extension to the UK FS SCD2 Bronze/Silver architecture. Rather than forcing operational applications to interact directly with the lakehouse, governed Silver current-state views are projected into a low-latency SQL database for operational consumption. Business decisions are made and stored locally, captured through SQL Change Data Capture (CDC), landed append-only into Bronze, and subsequently reconciled into governed Silver. The pattern preserves temporal correctness, auditability, replayability, and regulatory defensibility while providing the transactional performance and simplicity required by operational systems. It separates operational authority from institutional memory, allowing each layer to optimise for its intended purpose without compromising governance.

1. Introduction

Previous articles in this series established the core Bronze/Silver doctrine for regulated financial services platforms:

Bronze is institutional memory.
Silver is governed current truth.
Temporal reconstruction must always remain possible.
Historical evidence must never be overwritten.

Those principles explain how truth is preserved.

They do not answer a different question:

Where should operational authority live?

Most real systems still require:

Low-latency user interfaces
Transactional writes
Approvals and overrides
Operational workflows
Synchronous business decisions

The temptation is to push those concerns directly into the lakehouse.

This article argues against that approach.

Instead, it introduces a minimal operational pattern:

Silver Projection → Edge Authority → CDC Capture → Bronze Memory → Silver Reconciliation

The pattern extends the Bronze/Silver model without altering its underlying governance principles.

The objective is simple:

Allow operational systems to behave like operational systems while preserving the temporal integrity of the platform.

2. What Is Actually New Here?

The Bronze/Silver architecture already explains how truth is stored and reconstructed.

The missing piece is how operational applications interact with that architecture.

Specifically:

Where should operational reads come from?
Where should operational writes be made?
How should those writes enter institutional memory?
How do we preserve auditability without turning the lakehouse into an OLTP platform?

This article proposes four specific answers:

Materialise selected Silver views into a low-latency SQL projection.
Allow operational applications to write to local authority tables.
Capture those decisions using SQL CDC.
Land CDC events in Bronze and reconcile them back into Silver.

None of these alter the Bronze/Silver model.

What they provide is a practical implementation pattern for operational workloads that sit around it.

3. The Pattern

The architecture consists of four components:

Silver → SQL Projection (Read Model)
Edge SQL → Operational Writes (Authority Model)
SQL CDC → Bronze (Memory Capture)
Bronze → Silver (Governed Reconciliation)

The result is a clean separation between:

Operational authority
Institutional memory
Governed truth

4. Part 1: Silver → SQL Projection (Read Model)

Rather than forcing operational applications to query the lakehouse directly, we materialise selected current-state Silver views into a small Azure SQL database.

Important:

We do not copy all of Silver.

We copy only what operations actually require.

Typical examples include:

Clients
Portfolios
Current holdings
Instruments
Reference data

Example:

CREATE VIEW api_portfolio_current AS
SELECT *
FROM silver.portfolio
WHERE is_current = true;

Copied using ADF:

Databricks SQL Warehouse
          ↓
Azure SQL Database

Typical refresh intervals:

1–5 minutes
Event-driven later if required

The SQL projection contains:

Current state only
No SCD2 history
No temporal complexity
A familiar relational model

This database is a projection.

It is not a source of truth.

4.1 Why This Matters

The projection provides:

Fast operational reads
Simpler application development
Isolation from lakehouse complexity
Reduced analytical workload on platform resources

The governed truth remains in Silver.

5. Part 2: Operational Writes at the Edge (Authority Model)

Operational applications write locally into SQL.

Not into Silver.

Not into Bronze.

Not directly into Databricks.

Examples:

Orders
Trade requests
Approvals
Overrides
Operational decisions

Application
      ↓
Edge SQL Database

These tables represent authority.
They record business decisions.
This distinction is important.
The operational system owns decisions.
The platform owns institutional memory.
The two responsibilities should remain separate.

5.1 Why This Matters

Many architectures blur operational authority and historical truth.

That often leads to:

Governance confusion
Audit ambiguity
Difficult recovery scenarios

Keeping authority at the edge makes ownership explicit.

6. Part 3: Turn on CDC (Memory Capture)

Once operational writes exist, they need to enter institutional memory.

SQL Change Data Capture provides a simple mechanism.

Enable CDC:

EXEC sys.sp_cdc_enable_db;

Then:

EXEC sys.sp_cdc_enable_table
  @source_schema = 'dbo',
  @source_name   = 'orders',
  @role_name     = NULL;

SQL automatically generates:

cdc.dbo_orders_CT

For many regulated operational workloads, SQL CDC is sufficient. The objective is not to build a distributed event platform but to reliably capture operational decisions and preserve them as institutional memory. Kafka, Event Hubs and real-time streaming architectures remain valid options, but they should be adopted because they are required, not because they are fashionable.

6.1 Why This Matters

Many organisations jump immediately to streaming architectures.

For most regulated operational workloads, that complexity is unnecessary.

What we actually need is:

Reliable capture
Complete capture
Auditable capture

CDC provides exactly that.

7. Part 4: CDC → Bronze (Append-Only Memory)

An incremental ADF pipeline moves CDC events into Bronze.

SQL CDC
    ↓
Bronze.edge_orders_cdc

Example Bronze structure:

bronze.edge_orders_cdc
(
  event_id,
  operation,
  order_id,
  payload,
  occurred_at,
  ingestion_time
)

Important:

No merges
No reconciliation
No corrections
No history mutation

Bronze remains append-only.

Exactly as established throughout this series.

7.1 Why This Matters

Bronze exists to preserve evidence.
Not to determine truth.
Not to determine precedence.
Not to resolve conflicts.
Only to remember.
This pattern preserves that principle.

8. Part 5: Bronze → Silver Reconciliation

Silver continues to perform the role it always has.

It converts evidence into governed truth.

Example:

MERGE INTO silver.orders_current
USING bronze.edge_orders_cdc
ON silver.orders_current.order_id = bronze.order_id

WHEN MATCHED THEN
  UPDATE ...

WHEN NOT MATCHED THEN
  INSERT ...

Silver applies:

Governance rules
Validation rules
Precedence rules
SCD2 management
Regulatory controls

Nothing changes.

The operational pattern simply introduces another source of evidence.

8.1 Why This Matters

Operational decisions become visible immediately.
Governed truth remains governed.
Historical reconstruction remains possible.
The platform retains complete control over reconciliation.

9. Why Not Write Directly to Databricks?

Because lakehouses are not OLTP systems.

Operational workloads require:

Fast synchronous transactions
Predictable response times
User-driven workflows
Transactional consistency

Lakehouses optimise for:

Analytics
Historical reconstruction
Large-scale processing
Governance

Those are different responsibilities.

Combining them creates unnecessary architectural coupling.

10. Alignment to UK Financial Services Requirements

This pattern supports:

10.1 SMCR Accountability

Operational systems own decisions.
The platform owns reconstruction.
Ownership remains clear.

10.2 Auditability

All decisions are captured in Bronze.
Nothing is lost.
Nothing is overwritten.

10.3 Temporal Reconstruction

Silver can always be rebuilt.
Regulators can replay history.
Evidence remains intact.

10.4 Cost Control

Operational reads occur in SQL.
Interactive workloads do not consume unnecessary platform resources.

10.5 Controlled Evolution

Silver remains governed centrally.
Operational projections remain consumers of that governance.

11. Advantages

Fast operational reads
Transactional integrity
Clear authority boundaries
Full auditability
Complete temporal history
Lower platform coupling
Simpler operational architecture
Incremental adoption path

12. Trade-Offs

Intentional data duplication
Eventual consistency
CDC monitoring requirements
Additional operational database
Governance discipline required

Operational projections should be treated as disposable and rebuildable from Silver at any time.

This pattern is not intended for ultra-high-volume streaming environments.

It is intended for typical regulated operational workloads where simplicity and auditability matter more than architectural fashion.

13. The Architectural Principle

The important contribution is not CDC itself.

Nor is it SQL projection technology.

Both are well-established techniques.

The contribution is recognising that operational authority and institutional memory are different concerns.

Operational systems need:

Fast reads
Transactional writes
Workflow control
Immediate user feedback

Data platforms need:

Replayability
Temporal correctness
Audit reconstruction
Governance

Attempting to optimise one layer for both responsibilities usually damages both.

By introducing a deliberately small edge authority layer, organisations gain operational responsiveness without sacrificing temporal integrity.

The result is a system where:

Decisions are made close to the business process.
Institutional memory remains centralised.
Silver remains governed.
Bronze remains complete.
Regulatory reconstruction remains possible.

This is not a new data platform architecture.

It is a practical operational extension to the Bronze/Silver model that answers a question the earlier articles deliberately left open:

If Bronze is memory and Silver is truth, where should operational authority live?

14. Summary

The Bronze/Silver model already solves temporal truth, auditability, replayability and governance.

What it does not prescribe is how operational applications should interact with that model.

This article introduces a minimal answer: