Simplifying Multi-Company Data Consolidation with Medallion Architecture in Microsoft Fabric

[Royel]

Business Problem with Multi-Company Data

Managing data across multiple subsidiary companies creates unique challenges. Each company often uses different systems, table structures, and naming conventions. Yet leadership needs unified reporting to understand overall performance across all entities.

This scenario is common when parent companies acquire subsidiaries or when organizations expand through mergers. The result? Data is scattered across systems that should work together but don't.

The Solution: Medallion Architecture in Microsoft Fabric

The medallion architecture is Microsoft's recommended design pattern for organizing data between Lakehouse and warehouse, comprising three distinct layers: Bronze (raw data), Silver (validated data), and Gold (enriched data). This approach is perfect for multi-company scenarios because it provides a structured way to consolidate diverse data sources.

Here's a quick overview of the multi-company challenge:

Bronze Layer: Individual Company Data Storage

The Bronze layer serves as your landing zone for raw data from each subsidiary. In a typical medallion architecture implementation in Fabric, the bronze zone stores the data in the same format as the data source.

Key practices for multi-company Bronze layer:

• Create separate schemas for each company (Company1, Company2, Company3)
• Use parameterized pipelines to save development time and effort
• Preserve original data formats for audit trails

Physical Structure of Bronze Layer:

Schema	Tables
Company1	Table1 Table2 Table3
Company2	Table1 Table2
Company3	Table1 Table2 Table3 Table4

Silver Layer: Standardized Company Views

This is the place where we will do all kinds of transforms for our raw data into standardized, clean formats. This is where we are going to create consistent views across all companies.

Essential transformations:

• Standardize column names and data types across companies
• Add company identifier columns to every table
• Implement data quality rules (remove nulls, validate formats)
• Create star schema structures with dedicated dimension and fact views

Merging Company Data: The UNION ALL Approach

This is the crucial step that brings everything together. Creating a logical view in the silver layer under a new schema (All_Company) using UNION ALL is a clean and scalable way to bring those silver datasets together without physically merging them.

Here's how to implement it:

-- Consolidated Customer View

CREATE VIEW silver. all_company.Customers AS
SELECT
    'Company1' AS company,
    customer_id,
    customer_name,
    email,
    address
FROM silver.company1.customers
UNION ALL

SELECT
    'Company2' AS company,
    customer_id,
    customer_name,
    email,
    address
FROM silver.company2.customers

UNION ALL

SELECT
    'Company3' AS company,
    customer_id,
    customer_name,
    email,
    address
FROM silver.company3.customers;

The beauty of this method:

• No physical data movement required
• Easy to add new companies
• Maintains individual company data integrity
• Enables cross-company transformations in one place

Physical Structure of Silve Layer:

Schema	Views	Merged Views
All_Company		vw_Customers vw_Projects vw_Revenue
Company1	vw_company1_customers, vw_company2_Projects, vw_company3_Revenue
Company2	vw_company1_customers, vw_company2_ Projects, vw_company3_ Revenue
Company3	vw_company1_customers, vw_company2_ Projects, vw_company3_ Revenue

Gold Layer: Business-Ready Analytics

The Gold layer is our final business views that Power BI and other analytics tools or business applications can consume directly.

In here we are going to create views directly calling from the warehouse silver and schema “All_Comoany”

Simple SQL for view creation will look like this

CREATE VIEW dbo.Customers AS
SELECT * FROM silver.merged_views.customers;

[Note: If business wants to maintain large number of views it is recommended to maintain specific schema in gold layer]

This approach offers several advantages:

• Future-proof design: Adding new columns in Silver automatically appears in Gold
• Single source of truth: All analytics use the same underlying data
• Simplified maintenance: Changes made once in Silver propagate everywhere

Physical Structure of Gold Layer:

Schema	Views
dbo	vw_Customers vw_Projects vw_Revenue

Now let’s break down what types of benefits we are going to expect when we are going to  maintaining this architecture.

Operational Benefits:

• Single dashboard showing performance across all subsidiaries
• Consistent reporting metrics company-wide
• Faster time-to-insight into new acquisitions

Technical Benefits:

• Scalable data architecture that grows with your business
• Reduced development time for new data sources
• Improved data quality through standardized processes

Business Benefits:

• Better decision-making with complete data visibility
• Compliance reporting across all entities
• Cost optimization through consolidated analytics infrastructure

Conclusion

Designing a data warehouse using Medallion Architecture in Microsoft Fabric allows you to streamline the processing and transformation of data from raw ingestion to curated business insights. For multi-company scenarios, this pattern provides the structure needed to transform chaotic subsidiary data into unified business intelligence.

The key is starting simple, using proven patterns, and scaling gradually. With proper implementation, your organization will have a robust data platform that delivers consistent insights into all business entities.