Enterprise Data Warehouse
Medallion Architecture · Microsoft Fabric · Bronze/Silver/Gold Pipeline · CRM & ERP Integration
01The challenge
An organization running separate CRM and ERP systems had no unified, trustworthy data layer to build analysis on top of. Each system held its own version of customer, product, and sales data, structured differently, updated independently, and with no reconciliation between them. Any attempt at cross-system reporting meant manually stitching together exports, with no clear lineage back to the original source and no repeatable process behind it.
02The data problem
CRM held customer information, product information, and transactional sales details. ERP held complementary data like customer birthdate and location. In addition to both systems, some supplementary business data existed only as static Excel files outside either system. Neither system was designed to talk to the other, there was no shared customer or product key logic across them, no raw data retention for debugging when something looked wrong downstream, and no layered process separating "what the source actually sent" from "what's been cleaned" from "what's ready for business use." Building anything reliable on top of this required establishing that structure from scratch, and accounting for a source that wasn't a live system at all.
03My approach
I connected directly to the CRM and ERP systems via API, and separately ingested the static Excel files, into a Bronze layer inside Microsoft Fabric, landing all three source types as raw, untransformed tables exactly as received. This layer exists specifically for traceability, if a number downstream ever looks wrong, there's an unmodified copy of what each source actually provided, whether that source was a live API connection or a static file drop, and no analysis or reporting is meant to touch this layer directly.
From Bronze, I built the Silver layer using Data Factory pipelines and SQL-based transformations in the Fabric Warehouse, cleaning, standardizing, and normalizing the CRM, ERP, and Excel-sourced data, resolving inconsistent keys and formats, building derived columns, and enriching records so data from all three sources could actually be joined reliably. Each layer transition was validated before moving forward: schema and completeness checks on Bronze ingestion, correctness checks after Silver cleaning, and integration checks once Gold-layer business logic was applied, so errors were caught at the layer where they originated rather than surfacing three steps downstream in a report.
The Gold layer is where the raw, cleaned data actually became business-usable: I built it as views rather than physical tables, applying data integration logic, aggregations, and business rules to produce a proper star schema, a fact table for sales transactions linked to customer and product dimension tables, with friendly, business-readable naming applied at this stage rather than earlier, since Bronze and Silver intentionally preserve source naming for traceability. This Gold-layer star schema and its aggregated views are what's exposed for ad-hoc SQL querying, giving analysts direct, reliable access to business-ready data without needing to understand the transformation logic sitting behind it.
Throughout the build, each layer's transformation logic was documented and version-controlled in Git, so the pipeline's logic, not just its output, is auditable and repeatable rather than living only inside someone's head.
04The result
The finished warehouse gave the organization a single, trustworthy source of truth spanning two previously disconnected systems plus supplementary static data, with a clear separation between raw data, cleaned data, and business-ready data at every stage. Analysts could run ad-hoc SQL queries directly against the Gold-layer star schema with confidence in the numbers, instead of manually reconciling CRM, ERP, and spreadsheet data for every new question. The layered structure also meant that when data quality issues did arise, they could be traced back to the exact layer and transformation step responsible, rather than requiring a full pipeline re-investigation.
Tools & tech stack
- Microsoft Fabric Data Factory: used to build ingestion pipelines pulling CRM and ERP data via API, alongside static Excel file ingestion, into the Bronze layer
- Fabric Lakehouse: hosted the raw, untransformed Bronze layer for traceability and debugging
- Fabric Data Warehouse & T-SQL: used to build the Silver layer (cleaning, standardization, normalization, derived columns) and the Gold layer (data integration, aggregation, business logic)
- Data Modeling: star schema design at the Gold layer (fact_sales linked to dim_customers and dim_products)
- Git: used for version control and documentation of transformation logic across all three layers
- Consumption Layer: Gold-layer views exposed for ad-hoc SQL querying and analytical use
Have data that isn't telling you anything yet?
Send me what you are working with — even if it is four spreadsheets and a CRM export that don't agree with each other.