Unifying a Federated, Multi-ERP Estate with a Databricks-Native Lakehouse
Aug 21, 2026 | 5 min read
Industry: Commercial HVAC & Mechanical Services
Business Function: Data & Analytics
Capability: Enterprise Data Governance | Lakehouse Architecture
Tech Stack: Databricks | Power BI | Genie Analytics | Azure | Legacy ETL / JDBC
Key Highlights
- Best practices for unifying a federated, multi-entity ERP estate across a nationally-connected, locally-branded services organization.
- Design of a metadata-driven Databricks lakehouse with reconciliation, audit, and lineage built into the ingestion framework from day one.
- How to sequence Phase 1 of an enterprise data platform investment: enterprise-wide breadth first, versus a single-subject-area proof point first.
- Real-world application of a Bronze → Silver → Gold medallion architecture feeding governed Power BI reporting and a Genie Analytics semantic layer.
Client Overview
The client is a North American commercial HVAC and mechanical services company delivering maintenance, repair, retrofit, replacement, building automation, and energy solutions to commercial and institutional facilities. Recognized as one of the largest privately held platforms in its industry, the client operates through a network of locally branded companies while delivering nationwide service capability across office, healthcare, industrial, data center, education, and hospitality sites.
The Challenges
The Client’s model had produced a naturally distributed technology landscape of 59+ businesses running on 30+ separate ERP systems, connected only by local, agent-based networks. The organization needed a way to unify this federated estate into a single governed platform: one place to see spend, revenue, and operations across the business, and a foundation ready for enterprise-wide analytics and AI.
- 59+ businesses operating on 30+ distinct ERP systems, each with its own schema, naming conventions, and embedded business logic, with no enterprise-wide network, only local agents and ad hoc JDBC connections.
- No reconciliation framework between source ERPs, the warehouse, and dashboard outputs, discrepancies typically surfaced by business users after the fact, not by the system.
- No governed golden dimension or fact tables — revenue, margin, and service metrics were instead duplicated across 700+ Power BI measures, slowing reports and fragmenting metric definitions.
- No consolidated view of spend across business units, and no mechanism for business leaders to make controlled, auditable adjustments to system-calculated revenue.
- Finance operating on stale, batch-refreshed data rather than near-real-time figures, adding risk at month- and quarter-end close.
- An existing Databricks investment left underutilized as a basic processing engine, without the governed foundation needed to unlock its full value.
Our Solution: A Databricks-Native Lakehouse for Reporting and AI
Centralized, Governed Lakehouse Architecture
Structured every ERP source into a Bronze → Silver → Gold medallion on Databricks, replacing siloed local agents and ad hoc JDBC patterns with one governed plane for reporting and AI across the full portfolio.
Metadata-Driven Ingestion Framework
Built a single, reusable ingestion framework rather than hand-coded pipelines per source — so onboarding the next ERP becomes a configuration exercise.
Reconciliation and Audit Built In, Not Bolted On
Embedded source-to-bronze reconciliation, audit, and lineage directly into the ingestion framework, closing the gap where discrepancies previously went undetected until a business user flagged a number that “looked wrong.”
Golden Dimension and Fact Tables
Designed Gold-layer golden tables to anchor a governed semantic layer replacing duplicated, hand-built DAX logic with a single reusable source of truth for revenue, margin, and service metrics, feeding both Power BI and a Genie Analytics layer for self-service and AI-assisted exploration.
Consolidated Finance and Spend Visibility
Defined consolidated spend and finance data marts with a tightened refresh cadence, alongside a controlled, auditable override capability so business leaders can adjust system-calculated revenue with current context without falling back to offline spreadsheets.
Business Impact
- A single, governed source of truth spanning all 59+ businesses, replacing fragmented local connectivity.
- Faster, more reliable reporting with discrepancies caught proactively instead of surfacing in front of business users.
- Reduced Power BI maintenance effort and consistent metric definitions across teams, with faster report load times from pre-aggregated golden tables.
- Controlled, auditable revenue adjustments available directly to business leadership.
- Consolidated, near-real-time visibility into spend and financial performance.
- A repeatable, configuration-based onboarding pattern that turns each future ERP addition into days of work.
A scalable enterprise data strategy requires moving beyond one-off integrations toward a governed, metadata-driven foundation. A modern lakehouse architecture embeds reconciliation, auditability, and lineage into the data lifecycle itself, creating a trusted foundation for analytics and AI. When data platforms are built for reuse and governance, every new source becomes an opportunity rather than an integration challenge. This shift from fragmented connections to a repeatable data ecosystem enables organizations to transform their data estate into a strategic asset for enterprise intelligence and innovation.
