Unifying Multi-Brand Operational Data Across a Visa Program Portfolio with Azure Medallion Architecture
A US-based education and workforce placement company had built its operations across five independently managed brands, each running its own SQL Server database with its own data and no integration between them. Each brand tracked participants, placements, visa processing, and employer relationships in isolation. There was no consolidated view of performance across the portfolio and no reliable way to build one without starting from scratch every time leadership needed cross-brand numbers. Every reporting cycle required analysts to manually pull data from five separate systems, reconcile figures with no standard definitions in common, and produce a consolidated picture that was already stale by the time it reached decision-makers. Placement rates, visa approval trends, and participant volumes were each calculated differently depending on which brand produced the report. With no shared data layer, cross-brand comparison was unreliable and strategic decisions were being made on numbers that could not be verified. OptiSol designed and delivered a unified corporate data warehouse on Microsoft Azure, implementing a Medallion architecture across Bronze, Silver, and Gold layers to consolidate all five brand databases into a single governed analytics foundation. Incremental ETL pipelines built on Azure Functions replaced manual extraction entirely, with a centralized Power BI and Metabase reporting layer built on top. The platform gives leadership a single authoritative view across all brands for the first time, with infrastructure that scales cleanly as the portfolio grows.
Key Outcomes
Challenges and Solutions
No Consolidated View Across Five Independently Operated Brand Databases
Each brand operated its own SQL Server database with its own schema and no integration path to any other. There was no automated consolidation across brands, no shared data layer, and no common framework for cross-portfolio reporting. Analysts spent the majority of each reporting cycle pulling data by hand from five separate systems, with no way to verify that figures produced by one brand matched what another had independently calculated from a different source in the same week.
OptiSol built an incremental ETL framework using Azure Functions that connects to all five brand databases, extracts only changed records since the previous sync using timestamp-based logic, and loads raw data into a Bronze stage layer with full fidelity preserved. Brand identifiers are attached at ingestion so every record is traceable to its originating source. Automated scheduling, execution logging, and failure alerting are built into every pipeline. Onboarding an additional brand into the framework requires no architectural changes.
Inconsistent Definitions and No Shared Reporting Standard Across the Portfolio
Without a central data warehouse or shared semantic layer, each brand built its own reporting independently using a mix of manual exports and isolated tools. Placement rates, visa approval timelines, and participant counts were each calculated differently depending on which brand produced the report. When two business units presented conflicting numbers for the same KPI in the same leadership meeting, there was no governed layer to resolve it. Strategic decisions on resource allocation and portfolio performance were being made on data that could not be trusted.
OptiSol built a Silver layer that cleanses, standardizes, and integrates data from all five brand databases, merging them into unified common entity structures while applying data quality rules to remove duplicates and standardize formats. Metadata attributes including brand identifier, source system, and load timestamp are attached to every record. A fully materialized Gold layer followed, with purpose-built structures for executive KPIs covering participant volumes, placement performance, visa approval rates, and brand-wise comparisons. Consistent business definitions are enforced so every metric carries the same meaning regardless of which brand originated the data.
Fragmented Dashboards with No Enterprise-Level Reporting View
Reporting existed across brands but each team had built its own view connected to its own source data. Dashboards ran slowly because they queried operational databases directly, and there were no executive-level views available for leadership or board reporting. When dashboards from two brands produced different answers to the same question, there was no authoritative layer to resolve the conflict. There was no role-based access model to separate what brand-level users saw from what corporate leadership needed.
OptiSol replaced all disconnected reporting with a centralized dashboard layer built on the Gold layer in Azure SQL Database, accessible through both Power BI and Metabase depending on user need. Role-based access control separates brand-level operational views from consolidated corporate dashboards, so each team accesses only their own data while leadership has a full cross-brand view. Automated refresh schedules aligned to pipeline completion ensure dashboards always reflect the latest available data without manual intervention. All data lineage is tracked end-to-end, so every number on every dashboard can be traced back to its originating brand database.
No Scalable Infrastructure as the Brand Portfolio Continued to Grow
Each new brand or data requirement triggered a bespoke effort with no standard process and no shared architecture to build on. The operational cost of maintaining disconnected systems was growing alongside the portfolio, and there was no clear path to AI-ready analytics without first building a governed data foundation. As the business scaled, the data estate was moving in the opposite direction, becoming more fragmented with every new brand that joined the portfolio.
OptiSol designed the Medallion architecture and ETL framework for repeatability from the outset, so each new brand database connects to the existing parameterized pipeline with minimal additional development. The architecture is sized for current startup-scale workloads at a monthly infrastructure cost between $30 and $80, while maintaining clear upgrade paths to Azure Synapse Analytics, Azure Data Factory, and real-time streaming as the business grows. The governed Gold layer also positions the business to activate AI and predictive analytics capabilities without requiring platform changes when those initiatives move forward.
Our approach
Mapped All Five Brand Databases Before Building Anything
OptiSol began with a thorough analysis of all five brand source systems, documenting schemas, data quality gaps, naming inconsistencies, and entity relationships across the portfolio. All databases shared a common schema structure, which significantly accelerated integration planning. This mapping established the sequencing framework for the entire build, ensuring the highest-value reporting domains were identified first and every source system was fully accounted for before a single pipeline was constructed.
Built an Incremental ETL Framework Designed to Grow with the Portfolio
Azure Function pipelines were designed as parameterized, reusable components from day one so the framework scales with the business rather than requiring a custom build each time a new brand joins the portfolio. Incremental loading using timestamp-based sync logic keeps infrastructure cost low and source system load minimal. Brand identifiers are attached at ingestion, execution logs are written to Azure Blob Storage, and automated alerts fire on any pipeline failure. The result is a framework that operates reliably at current scale and onboards new sources without architectural rework.
Implemented Medallion Architecture with Deliberate Separation Across Every Layer
The Bronze layer preserves raw data exactly as ingested from each brand, maintaining full auditability and replay capability for every source system. The Silver layer applies quality rules, resolves entity mismatches, merges all brands into unified common structures, and attaches metadata for full traceability. The Gold layer is purpose-built per analytical domain, covering participant performance, placement outcomes, visa processing, and cross-brand executive comparisons. Where Silver data was already in a reporting-ready state, structures were applied directly to reduce storage cost and avoid unnecessary duplication.
Delivered a Governed Reporting Layer Accessible to Both Operational and Executive Users
The reporting layer was built on the Gold layer in Azure SQL Database, with dashboards delivered through Power BI for structured executive reporting and Metabase for day-to-day operational access. Role-based access control separates what each user group can see. Executive dashboards cover consolidated participant volumes, placement performance, visa approval trends, revenue, and brand-wise comparisons. Operational dashboards surface pending placements, processing delays, school allocation status, and post-placement completion. Every metric on every dashboard traces back to its originating brand database through full end-to-end lineage.