AWS Data Warehouse Modernization for a Leading Physical Security & Fire Systems Integrator
Industry
Physical Security & Fire System
Subsidiaries
14+ Operating Companies
Platform
Amazon Web services
Engagement
End-to-end Data Engineering
Timeline
2025-2026
About the Company.
A large, US-based physical security and fire systems integrator operating across 14+ subsidiary companies spanning Integration, Security, Fire, and related divisions. The organization had grown rapidly through acquisitions and relied on multiple independent ERP systems, a CRM platform, HR management software, and financial planning tools all operating in silos with no unified data layer across the enterprise.
Solution Summary
Algoscale designed and delivered an end-to-end cloud data warehouse on Amazon Web Services, built on the Medallion Architecture Bronze → Silver → Gold. The platform unified data from 14+ ERP instances, Dynamics 365 CRM, UKG HRMS, and OneStream FPA into a single governed data lake, powering 20+ real time Power BI KPI dashboards across Finance, Operations, HR, and Sales. A custom Master Data Management service and fine-grained Lake Formation access controls were also delivered as part of the engagement.
Customer Challenges.
Rapid acquisition-led growth had created a data infrastructure that couldn’t keep up with business ambitions. Leaders lacked visibility — not because the data didn’t exist, but because it was locked in 14 different places.
Fragmented Data Across 14+ Subsidiaries
Each acquired company ran its own independent ERP instance with no unified reporting layer, making enterprise-wide visibility impossible.
No Single Source of Truth
Financial metrics such as revenue, billing, and backlog were computed differently across subsidiaries, leading to conflicting numbers in leadership meetings.
Fully Manual Reporting Workflows
Finance and operations teams spent significant time each week exporting, transforming, and reconciling data across Excel files.
Zero Operational KPI Visibility
Difficulty identifying equivalent products across different retailer catalogs, limiting accurate “apple-to-apple” comparisons.
Workforce Analytics Gap
No centralized HR analytics existed for headcount, employee attrition, or turnover trends across the organization’s subsidiaries.
Disconnected Sales and Financial Data
CRM pipeline data was not integrated with financial actuals, making pipeline-to-revenue reconciliation impossible for the sales and finance teams.
No Master Data Management
Customer accounts were duplicated and inconsistently named across subsidiaries, with no canonical identifiers to enable cross-company analysis.
“We had grown to 14 companies and still couldn’t answer a simple question , what did we bill last month across the whole business? Algoscale gave us that answer, plus 20 dashboards we didn’t even know we needed. For the first time, our leadership team looks at the same numbers.”
Architecture
How the Data Platform Works.
The Medallion Architecture on AWS takes raw source data through three progressive layers — each adding cleanliness, structure, and business context — until it reaches decision-ready dashboards.
Algoscale Solution
What We Built.
Algoscale engineered a custom AI/NLP-based contract intelligence pipeline to automate translation, extraction, and governance
Built 20+ production AWS Glue PySpark ETL jobs covering all KPI domains like HR Turnover, MTTR, Revenue Bridge, Sales Pipeline, Customer 360, FPA, Velocity/Backlog, FPA Revenue and AR/GL brigde.
Reverse-engineered all existing Power BI DAX measures and reimplemented them in PySpark, with full reconciliation validation against existing dashboard outputs.
Developed a custom Lambda-based Master Data Management service with a 75+ brand dictionary and confidence scoring to resolve canonical customer names across all subsidiaries, backed by an Amazon RDS PostgreSQL Ledger.
Deployed production grade pipeline orchestration using AWS Step functions with synchronous execution, parallel Gold-layer job processing, per-job retry logic with exponential backoff and structured catch handlers.
Enforced fine grained, regional data access using AWS Lake formation by applying row-level and column-level security per territory without any infrastructure changes for business users.
Architected a full Medallion Data Lake on AWS with AmazonS3 as the centralized storage layer for all raw, cleansed, and business ready data.
Implemented real time CDC ingestion from 14+ Microsoft Business Central ERP instances and Dynamics 365 CRM using AWS DMS.
Algoscale Differentiators
Why Algoscale.
What set this engagement apart wasn’t just the technology. It was engineering depth, business judgment, and the kind of partnership discipline that shows up in production.
Deep expertise in multi-entry ERP unification, proven patterns for ingesting and normalizing data from 14+ independent Microsoft Business Central Instances into a single governed layer.
Engineering mindset focused on long-term scalability, with every pipeline delivered using enterprise-grade orchestration, retry mechanisms, business logic, alerting, and observability from day one, not retrofitted later.
Proprietary MDM Capability- custom Lambda-based canonical name resolution service that solves a cross-subsidiary identity problem no off-the-shelf tool could address out of the box.
DAX-to-PySpark parity expertise ability to reverse engineer complex Power BI calculation logic and reimplement it in the data layer, eliminating dependency on dashboard level transformations.
Security-by-design approach- Lake formation row-level and column-level security implemented as part of the core architecture, ensuring regional data governance without friction for end users.
Values Delivered.
Through this engagement, Algoscale delivered measurable improvements:
Unified all 14+ subsidiary ERPs, CRM, HR and financial planning systems into a single AWS data lake for the first time in the organization’s history.
Eliminated manual Excel reporting workflows entirely with data refreshes automatically twice daily with full pipeline observability.
Reduced time-to-insight for Finance, Operations, HR, and Sales from days of manual effort to real time, self-service Power BI dashboards.
Delivered regional data security for business users with zero infrastructure changes, through Lake formation row-level filtering per territory.
70% reduction in reporting preparation time across Finance and Operations teams.
Achieved 100% automation of daily pipeline execution with automated retries, dependency, enforcement, and failure alerting via CloudWatch.
Technologies We Use.
Get this case study in PDF to your inbox.
We care about your data in our privacy policy.
More case studies.
Experience excellence in digital craftsmanship with our team of skilled professionals dedicated to delivering exceptional results.
Result:
Result:
Result:
Ready to Transform your Business with AI?
Partner with a team that values confidentiality and results.
