12 Systems. Zero Single Source of Truth. How a Financial Firm Built Their Data Foundation in 10 Weeks
A 400-person financial services company had data scattered across 12 systems. Their data team spent 70% of their time just connecting things — not analyzing them. We unified everything into a Fabric Lakehouse, and 50+ business users got self-service analytics for the first time.
70%
Less Data Prep Time
12
Sources Unified
50+
Self-Service Users
10
Week Delivery
Industry
Financial Services
Size
400+ employees
Team
Data Engineering (6) + 50 business users
Before
12 disconnected systems, manual connectors, Excel-based reporting
After
Fabric Lakehouse, Spark pipelines, Power BI semantic models
Stakeholder
CTO + Head of Data
The Full Story
The Problem: "Ask Three Systems, Get Three Answers"
Vikram, the Head of Data, was blunt: "We don't have a data problem. We have a fragmentation problem. Our CRM says one thing. Our ERP says another. The portfolio management system says a third. Nobody knows which number is right."
The data landscape was typical of a fast-growing financial firm:
- 12 data sources: 2 CRMs, 2 ERPs, portfolio management, trading platform, market data feeds, accounting system, 3 SQL databases, and Excel files
- 70% of the data team's time spent building and maintaining connectors — not doing actual analytics
- No governance: Anyone could pull data, transform it locally, and present different numbers in meetings
- 4–6 week lead time for new data requests from business teams
"Our data engineers were glorified pipe-builders. They joined this company to do machine learning, not babysit 12 different connectors." — Vikram, Head of Data
The Approach: A Single Source of Truth with Self-Service on Top
The strategy was a 3-layer architecture: ingest everything into one lake, model it once, and let business users explore it themselves.
Bronze: Raw Ingestion
Dataflows + Spark notebooks pull from all 12 sources into OneLake. Incremental loads. Schema drift detection. All raw data preserved for audit.
Silver: Cleaned & Modeled
Deduplication, standardization (all customer IDs normalized), business logic applied. Star schema semantic model built once — used by everyone.
Gold: Self-Service Analytics
50+ business users create their own Power BI reports from certified datasets. Row-level security. Data catalog with business glossary.
The Challenges We Overcame
Challenge 1: The Legacy System That Refused to Cooperate
One of their core trading platforms was a legacy on-prem system with no API. The only way to extract data was a scheduled CSV dump to a shared folder. The format changed slightly every quarter because the vendor updated their export tool.
How we solved it:
We built a "schema-agnostic" ingestion pipeline that auto-detected column changes and mapped them to a canonical schema. When new columns appeared, the pipeline flagged them for review instead of breaking. Combined with an automated data quality check (row counts, null percentages, value distributions), we caught drift before it corrupted downstream reports.
Challenge 2: Governance Without Becoming the Bottleneck
The CTO wanted strict data governance. The business teams wanted unrestricted access. The data team was stuck in the middle, fielding ad-hoc requests that took weeks.
How we solved it:
We implemented a "certified dataset" model. The data engineering team certified 12 core semantic models — clean, governed, always up-to-date. Business users could build any report they wanted on top of these models. For non-certified data, they could request access through a self-service portal with automatic approval workflows for low-risk data. Governance was enforced at the model layer, not the user layer.
Challenge 3: The "I'll Just Export to Excel" Habit
Despite having Power BI dashboards, several senior analysts kept exporting data to Excel "just to double-check." This created exactly the fragmentation we were trying to solve.
How we solved it:
We didn't fight the Excel habit — we upgraded it. We enabled the "Analyze in Excel" feature with a live connection to the certified semantic models. Analysts got their familiar Excel interface with a direct pipeline to governed data. No more offline copies. Over 3 months, Excel exports dropped by 80% as trust in the dashboards grew.
The Results
12 → 1
All data sources unified into a single Lakehouse
70%
Reduction in data preparation time
50+
Self-service analytics users enabled
4 wks → 2 days
New data request turnaround time
"For the first time in three years, our Monday morning executive meeting uses one report. One source. One truth. My data engineers are actually doing data science now instead of playing plumber." — Vikram, Head of Data
Technology Used
Got Data Scattered Across Multiple Systems?
A unified Fabric Lakehouse gives your team a single source of truth — and your analysts get to do actual analysis. Let's talk architecture.