Enterprise Data Warehouse on a Medallion Architecture

My role

BI Consultant & Data Engineer — solo end-to-end design and build

The challenge

An organization running separate CRM and ERP systems had no unified, trustworthy data layer to build analysis on. Each system held its own version of customer, product and sales data, structured differently, updated independently, and with no reconciliation between them. Cross-system reporting meant manually stitching exports together, with no lineage back to the source and no repeatable process behind it.

The data problem

CRM held customer, product and transactional sales detail. ERP held complementary data such as customer birthdate and location. On top of both, some 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, no raw data retention for debugging when something looked wrong downstream, and no layered process separating what the source actually sent from what had been cleaned and what was ready for business use.

My 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 for traceability: if a number downstream ever looks wrong, there is an unmodified copy of what each source provided. No reporting touches it directly.

From Bronze I built the Silver layer using Data Factory pipelines and SQL transformations in the Fabric Warehouse — cleaning, standardizing and normalizing the CRM, ERP and Excel data, resolving inconsistent keys and formats, building derived columns and enriching records so all three sources could be joined reliably.

Each layer transition was validated before moving on: schema and completeness checks on Bronze ingestion, correctness checks after Silver cleaning, and integration checks once Gold business logic was applied. Errors get caught at the layer where they originate rather than surfacing three steps downstream in a report.

The Gold layer is where cleaned data becomes business-usable. I built it as views rather than physical tables, applying integration logic, aggregations and business rules to produce a proper star schema: a sales fact table linked to customer and product dimensions, with business-readable naming applied at this stage only, since Bronze and Silver intentionally preserve source naming for traceability. These Gold views are what's exposed for ad-hoc SQL querying.

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 in one person's head.

The result

The warehouse gave the organization a single, trustworthy source of truth spanning two previously disconnected systems plus supplementary static data, with clear separation between raw, cleaned and business-ready data at every stage. Analysts can run ad-hoc SQL directly against the Gold-layer star schema with confidence in the numbers, instead of reconciling CRM, ERP and spreadsheet data for every new question. When data quality issues do arise, they can be traced to the exact layer and transformation step responsible rather than triggering a full pipeline investigation.

Tools & tech stack

• Microsoft Fabric Data FactoryIngestion pipelines pulling CRM and ERP data via API alongside static Excel files into Bronze
• Fabric LakehouseHosts the raw, untransformed Bronze layer for traceability and debugging
• Fabric Data Warehouse & T-SQLSilver layer (cleaning, standardization, normalization, derived columns) and Gold layer (integration, aggregation, business logic)
• Data modelingStar schema at the Gold layer — fact_sales linked to dim_customers and dim_products
• GitVersion control and documentation of transformation logic across all three layers
• Consumption layerGold-layer views exposed for ad-hoc SQL querying