Hybrid SQL–Synapse Data Mesh Architecture

Most enterprises don't get to choose between transactional and analytical data platforms — they need both. SQL Server (or Azure SQL) handles the transactional workloads that run the business. Azure Synapse handles the analytical workloads that inform it. The architectural challenge is not picking one; it's connecting them in a way that gives analysts and data scientists access to transactional data without compromising the performance, consistency, or governance of the systems of record.

You don't choose between transactional and analytical platforms — you need both. The architecture is not about picking one; it's about connecting them so analysts reach transactional data without ever touching the systems of record.

When to Use This Architecture

The Medallion Pattern as the Bridge

The medallion architecture (Bronze, Silver, Gold) is the bridge between the transactional and analytical planes. Bronze holds raw data ingested from the transactional systems — an immutable record of what arrived. Silver holds cleansed, conformed, de-duplicated data. Gold holds business-ready data products shaped for specific analytical consumption. Each layer is a deliberate transformation with a clear contract.

Federated Governance

A data mesh distributes data ownership to domain teams — but distributed ownership without federated governance produces chaos. The resolution is a federated computational governance model: central standards (security classifications, naming conventions, quality thresholds, lineage requirements) enforced automatically through tooling, with domain teams owning their data products within those standards. Microsoft Purview provides the catalogue, lineage, and classification layer that spans both the SQL Server and Synapse planes.

Implementation Sequence

1. 01
   Establish the medallion layers in Synapse

   Create the Bronze, Silver, Gold structure. Bronze ingests raw from SQL Server via CDC or scheduled extracts. Define the transformation logic for each layer transition.

2. 02
   Set up Change Data Capture from SQL Server

   Use SQL Server CDC or Azure Data Factory to move transactional changes into Bronze without running analytical load against the OLTP system. This is what protects transactional performance.

3. 03
   Define data contracts for each domain

   For each data product a domain serves, write a versioned contract: schema, semantics, quality guarantees, SLA. Enforce schema validation in the pipeline so contract violations fail fast.

4. 04
   Deploy Microsoft Purview for federated governance

   Catalogue both planes. Establish automated lineage tracking, data classification, and the central standards that domain teams operate within.

5. 05
   Onboard domains incrementally

   Don't convert everything at once. Onboard one domain as a data product owner, prove the model, then expand. Each domain that adopts the contract model reduces central bottleneck load.