Reference Architecture — Data Flow (Medallion)¶

TL;DR: Bronze stores raw, silver stores cleaned-and-conformed, gold stores business-ready. Every transition is a dbt (or Spark) job with tests, version control, and lineage to Purview. Don't skip layers; don't denormalize bronze.

The problem¶

A real analytics platform ingests from dozens of source systems with different schemas, qualities, freshnesses, and retention requirements — and serves dozens of consumer surfaces (BI, APIs, ML, AI agents, regulators) with conflicting needs. Without a strict layered approach, you end up with a god-mode pipeline that nobody can reason about and a single bad deploy breaks every dashboard.

Architecture¶

flowchart LR
    subgraph Sources[Source Systems]
        S1[OLTP DBs<br/>SQL Server, Cosmos, RDS]
        S2[SaaS APIs<br/>Salesforce, ServiceNow]
        S3[Files<br/>CSV/Parquet/JSON drops]
        S4[Streams<br/>IoT, Kafka, Event Hubs]
        S5[On-prem<br/>Teradata, Hadoop, mainframe]
    end

    subgraph Ingest[Ingestion]
        ADF[Azure Data Factory<br/>+ Self-Hosted IR]
        EH[Event Hubs<br/>+ Capture]
        ASA[Stream Analytics<br/>or Fabric RTI]
    end

    subgraph Bronze[🥉 BRONZE — Raw / Immutable]
        B1[(ADLS Gen2<br/>Delta tables<br/>partitioned by ingest_date)]
        B2[Schema = source schema<br/>+ ingestion metadata]
        B3[Retention: 7-90 days hot,<br/>archive cold]
    end

    subgraph Silver[🥈 SILVER — Cleaned / Conformed]
        Sv1[(Delta tables<br/>SCD2 where needed)]
        Sv2[Type-cast, deduped,<br/>nulls explicit, PII tagged]
        Sv3[Conformed dimensions<br/>+ surrogate keys]
        Sv4[Quality tests:<br/>Great Expectations<br/>+ dbt tests]
    end

    subgraph Gold[🥇 GOLD — Business / Aggregated]
        G1[(Star/snowflake schemas<br/>or wide flat tables)]
        G2[Business metrics<br/>+ KPIs]
        G3[ML feature tables]
        G4[Aggregates for BI]
    end

    subgraph Serve[Serving]
        BI[Power BI<br/>Direct Lake / Import]
        DAB[Data API Builder<br/>REST + GraphQL]
        AOAI[AI Agents<br/>RAG/GraphRAG]
        ML[Azure ML<br/>training + inference]
        Export[Reverse-ETL<br/>Salesforce, Sheets]
    end

    subgraph Governance[Cross-Cutting]
        Purv[Microsoft Purview<br/>catalog + lineage]
        KV[Key Vault<br/>secrets]
        LA[Log Analytics<br/>+ App Insights]
        Contracts[YAML data product<br/>contracts in git]
    end

    S1 --> ADF
    S2 --> ADF
    S3 --> ADF
    S4 --> EH
    S4 --> ASA
    S5 --> ADF

    ADF --> B1
    EH --> B1
    ASA --> B1

    B1 -- dbt run --> Sv1
    Sv1 -- dbt run --> G1

    G1 --> BI
    G1 --> DAB
    G1 --> AOAI
    G1 --> ML
    G1 --> Export

    Purv -. scans .-> B1
    Purv -. scans .-> Sv1
    Purv -. scans .-> G1
    Contracts -. validates .-> Sv1
    Contracts -. validates .-> G1
    KV -. secrets .-> ADF
    KV -. secrets .-> ASA
    KV -. secrets .-> AOAI
    LA -. logs .-> ADF
    LA -. logs .-> ASA
    LA -. logs .-> Sv1

Layer rules¶

Bronze (raw / immutable)¶

Silver (cleaned / conformed)¶

Gold (business / aggregated)¶

Where each Azure service fits¶

Lineage¶

Every transformation runs in dbt (with the exception of Spark-only workloads which run in Databricks notebooks). dbt + Purview together give you:

• Column-level lineage from bronze → silver → gold
• "What does this column mean" docs auto-generated from meta and description
• Upstream/downstream impact analysis when planning a schema change
• Data freshness SLAs automated as dbt source freshness

See ADR 0013 — dbt as Canonical Transformation for the rationale.

Trade-offs¶

✅ Why this pattern wins

• Each layer has one job → easier to reason about, easier to debug
• Bronze immutability = full replay any time
• Silver conformance = downstream consumers trust their inputs
• Gold business definitions = "the report says X" matches "the API returns X"
• dbt + tests + Purview = real lineage, not Confluence diagrams

⚠️ What you give up

• Three layers means 3× storage. Mitigate with tier'd lifecycle on bronze.
• Latency from raw to gold can be 15-60 minutes for batch. For sub-minute, use a separate streaming-gold path (Fabric RTI / KQL DB).
• Initial setup is heavier than "one giant ETL job." Pays back at scale 2; doesn't pay back at scale 0.5.
• Discipline is required. Skipping silver "just for one report" creates the spaghetti you tried to avoid.

Variants¶

Related¶

• Best Practices — Medallion Architecture (the long version)
• Best Practices — Data Engineering
• ADR 0001 — ADF + dbt over Airflow
• ADR 0003 — Delta Lake over Iceberg/Parquet
• ADR 0013 — dbt as Canonical Transformation
• Reference Architecture — Fabric vs Synapse vs Databricks
• Patterns — Streaming & CDC