An automated cloud data platform connecting 5+ source systems into a unified data lake with quality gates, delta detection, and medallion architecture — powering real-time dashboards for a large professional services firm.
Azure Data FactoryData Lake Gen2ParquetSQL ServerREST APIsPower BIKey VaultSelf-Hosted IR
ProblemSourcesPipelineData LakeImpact
Before Data Trapped in Silos
Critical business data was scattered across 5+ disconnected systems. Finance teams manually exported spreadsheets, reports were built by copy-pasting into Excel — slow, error-prone, and not scalable.
📊
Manual Exports
Finance manually downloading spreadsheets from the ERP every week
📋
Survey Copy-Paste
Client feedback manually downloaded and reformatted
🔒
No Automation
People & Culture had no automated access to partner data
Result: No single source of truth, stale data, Excel-based reporting, and no firm-wide visibility.
Sources 5 Connected Systems
The platform ingests data daily from 5 source systems — cloud APIs, on-premises databases, and file servers.
☁️
Survey Platform API
Client surveys & feedback via REST API with async export polling
🗄️
Finance ERP
Actuals, budgets, org structure via SQL Server
🗄️
Practice Management
Partners & clients via SQL Server
📁
On-Prem File Servers
Excel & CSV exports via Self-Hosted IR
🗄️
Legacy ERP
Org structure from legacy accounting system
Pipeline Azure Data Factory Orchestration
Three business domain pipelines run in parallel — each with quality gates, delta detection, and domain-specific logic.
Finance
Row Count→Quality Gate ±15%→Copy to Parquet→Delta Hash→Dim & Fact Models
Clients & Markets
List Surveys→ForEach ×4→Start Export→Poll Status→Download ZIP
People & Culture
Extract Partners→Copy to Parquet→Delta Processing
Data Lake Medallion Architecture
Data lands in a three-tier medallion architecture — Bronze (raw), Silver (modelled), Gold (analytics-ready).
🥉 Bronze — Raw / Landing
Date-partitioned raw data in original format
ParquetJSONCSVDate-partitioned
🥈 Silver — Cleansed & Modelled
Star schema with dimension and fact tables, delta-detected
Dim_OrgStructureDim_DatesFact_ActualsFact_Budget
🥇 Gold — Analytics Ready
Optimised views powering real-time Power BI dashboards across the firm
Power BIDaily @ 2:00 AM AESTFirm-wide KPIs
Results Architecture & Impact
Automated daily data platform replacing manual Excel-based reporting across the entire firm.
Azure Data Factory
Orchestration engine with parallel pipeline execution
Data Lake Gen2
Medallion architecture: Bronze → Silver → Gold
Quality Gates
±15% row count threshold protection per load
Delta Detection
Hash-based change tracking, only new/changed rows flow
Async API Polling
Survey exports with parallel ForEach processing
Key Vault + SHIR
Secure secrets, on-prem connectivity via runtime
5+
Source systems unified into one data lake
3
Business domains running in parallel daily
±15%
Quality gate threshold protecting every load
Daily
Automated execution at 2:00 AM AEST
From Excel Silos to Unified Analytics
5 source systems, 3 business domains, medallion architecture, quality gates — all automated daily.