An Ontario-based Health Professional Regulatory Firm uses an Azure-based Data Lakehouse for Centralized Data and Analytics
Client Overview
Our client, an Ontario-based Health Professional Regulatory Body, had data spanning multiple SaaS platforms for registrant regulation, public protection, policy development, and stakeholder communications, manual reporting, and no governance. Operating under the Regulated Health Professions Act (RHPA), unified intelligence directly translates to compliance adherence for the PHIPA (Personal Health Information Protection Act) and more efficient operations for them. But in the absence of unified data, their teams were spending more time on inefficient processes for manual data extraction and conflicting KPIs— far from centralized governance at scale.
Business Challenge
Businesses in regulated industries need unified data from all sources not just for governance and compliance, but also reliable decision-making, team alignment, and operational efficiency. But their existing process relied on fragmented data from multiple SaaS platforms, which delayed decisions, created compliance loopholes, and put operational capacity at risk. Key challenges included:
Delayed decisions with inefficient manual reporting requiring teams to download source exports, transform data in Microsoft Excel, and upload to Tableau.
Fragmented insights from existing Tableau dashboards disconnected from automated pipelines, data from multiple SaaS platforms.
Risked business continuity with KPI computation logic embedded in individual Excel files that limited team members could access.
Absence of any organization-wide governance standards, data catalog, lineage documentation, access control framework, and field-level PII classification for ensuring data access to the right people and meeting PHIPA requirements.
Proportional staff overhead while adding new data sources or expanding analytics at scale.
Our Approach & Solution
Algoscale implemented the Medallion Architecture (Bronze / Silver / Gold) on Microsoft Azure and Microsoft Fabric, organizing data into three distinct layers — each serving a specific purpose in the data lifecycle to facilitate data unification, data integration from all source systems, and establishing centralized governance for analytics at scale.
Raw Ingestion
Landing zone for raw data from all sources. Parquet format, partitioned by source/YYYY/MM/DD. Schema validation on every run.
Transformation
Explicit typecasting and conformance checks. Valid records processed; failed records routed to rejected table and logged.
Analytics-Ready
Domain KPI dashboards for all six business areas. Governed, documented, and validated with business owner sign-off.
Key Components:
We integrated data from all source systems, including Thentia, the registrant database for all practitioner data, Clobba, the MS Teams call analytics and call centre data, to GA4 web analytics, Hootsuite for social media analytics, Mailchimp for emails, Martus for finance and KnowBe4 for IT security/phishing in Full Load or Incremental load types.
Each source follows one of the two production-grade ingestion patterns:
Pattern A SFTP / CSV
Thentia, Clobba — Fabric Data Factory SFTP connector polls CMTO’s designated SFTP server on a scheduled trigger. Every new file detected that matches the expected naming convention and schema is copied to the Bronze container partitioned by source/YYYY/MM/DD. Schema is validated on every run and files failing validation are routed to a quarantine container, with an alert raised every 5 minutes.
Pattern B REST API
Google Analytics, HootSuite, Mailchimp, KnowBe4, Martus — Fabric Data Factory REST connector calls the source API using credentials stored in Key Vault via Managed Identity. Incremental extraction uses the last successful run timestamp as the watermark variable stored in Fabric pipeline metadata. The response is serialized in the Parquet format in Bronze, partitioned by source/YYYY/MM/DD.
A schema-drift strategy, particularly for data sources like Thentia and Clobba— known for unannounced schema changes:
Detection
Azure Data Factory validates schemas at the Bronze ingestion layer. Whenever a new column is added or an existing column is removed, the pipeline halts and alerts, eliminating the risk of corrupted data getting silently uploaded, for reliable decisions and compliance.
Silver Layer Protection
Transformation happens with explicit typecasting and conformance checks. Valid records continue processing, while those that fail are moved to a rejected records table and logged.
File Naming Failures
Missing or misnamed CSV files trigger alerts to Algoscale and the client’s operations teams in 5 minutes, so no discrepancy flows downstream.
Remediation Responsibility
Algoscale owns the responsibility for all pipeline logic updates needed due to schema drifts during the active support plan.
We designed, built, and validated domain-based KPI dashboards, each following the Bronze-Silver-Gold pipeline:
We delivered 131 KPI definitions— all documented in the KPI register with business owner sign-off.
All data pipelines are orchestrated using Microsoft Fabric Data Factory with production-level reliability and error handling, with all 7 ingestion pipelines triggering on a daily schedule, pipeline failures generating immediate P1 alerts, and a daily pipeline health summary report delivered to the CMTO IT distribution list by 07:00 EST.
We designed and implemented a RBAC framework for our client’s 61 named users across six defined roles. Row-Level Security is applied in the Power BI semantic model for domain-restricted roles using USERPRINCIPALNAME() matched against department fields.
We designed the platform for cost efficiency and eliminated all idle compute, with Microsoft Fabric Data Factory billing only when pipelines are executing, OneLake lifecycle management policies applying tiered storage based on data age per layer, and Fabric Lakehouse SQL endpoint using serverless billing — compute capacity scales automatically with workload demand.
A comprehensive data quality framework was implemented across all Medallion layers with every rule formally documented in the Data Quality Rule Catalogue, with results accessible to relevant stakeholders via Power BI or direct query.
Impact & Results
Conclusion
Algoscale helped an Ontario-based Health Professional Regulatory Body replace fragmented, manual reporting processes into a centralized, governed Azure Data Lakehouse built for compliance, scalability, and operational efficiency. By integrating multiple SaaS platforms into a unified analytics ecosystem with automated pipelines, role-based access control, and PHIPA-aligned governance, we helped the organization achieve faster and reliable decision-making and reduce operational overhead, while empowering their teams to focus on core competencies.
As a leading Data Consulting and AI Services Company, Algoscale combines cloud-native engineering, governance expertise, modern analytics architecture, and proprietary battle-tested frameworks to help regulated organizations build secure, scalable, and insight-driven data platforms that support compliance, business continuity, and data-driven growth.
