The client is a leading warranty management organization offering extended protection and service programs for major automotive and equipment brands across North America. With a rapidly growing network of dealers and OEM partners, they manage a complex web of financial, sales, and claims operations, all of which depend on timely, accurate data to function.
For years, that data came through manual extracts from their PCMI system. But as the business scaled, reporting delays, data inconsistencies, and operational gaps became too costly to work around. We connected and arrived at a decision to replace the manual dependency with something built to last.
The Challenges
The core problem was a data workflow built on manual effort, and manual effort doesn’t scale. The client’s reporting environment relied entirely on periodic database extracts pulled from the PCMI system by hand. Every step in the chain, from data retrieval to dashboard refresh, required human intervention, making each step a potential source of delay, error, or inconsistency.
- Manual & delayed refreshes: Static backups meant the data feeding dashboards and reports was always behind. By the time a report was ready, the numbers it showed were already out of date.
- Data inconsistency: Without a direct connection to source systems, reconciling figures across reports was a routine headache — one that consumed time and introduced uncertainty into decisions that needed to be clean.
- Fragmented data sources: Multiple extracts lived in separate silos with no unified repository tying them together. Linking claims data to sales performance, for instance, was far harder than it should have been.
- Performance bottlenecks: Power BI dashboards were running on unoptimized SQL queries, making routine reporting slower than the business could afford.
- Limited scalability: As dealer and OEM volumes grew, the manual workflow had no room to absorb the load. More business meant more strain on a process that was already stretched.
How We Helped
We built a fully automated, cloud-based data pipeline that removed manual intervention from every stage of the reporting process. The starting point was the source. Rather than waiting on manual PCMI extracts, we connected directly to the system to pull daily data backups automatically, i.e., no human trigger required. From there, a fully orchestrated pipeline moved the data through each processing stage, transforming and validating it along the way before it reached the reporting layer. A few key decisions shaped the quality of what was built:
- Delta tracking & historical accuracy: SQL-based delta logic and SCD Type 2 ensured incremental loads were processed efficiently and historical records stayed intact and query-ready over time.
- Power BI modernization: Datasets were rebuilt with optimized data models and reusable DAX measures, replacing the heavy queries that had been slowing down dashboards.
- Automated refresh & delivery: Power Automate and an On-Premise Data Gateway handled scheduled refreshes and report distribution — the right reports to the right people, on time, automatically.
- End-to-end monitoring: Lambda health checks, SQL validations, and alerting frameworks were put in place to catch pipeline issues before they could affect reporting downstream.
The Results
The shift from manual to automated changed how the business related to its own data. Report delivery time dropped from days to minutes. Data that previously arrived stale now refreshes automatically, giving teams across finance, sales, and claims access to current information without waiting for anyone to pull it. Dynamic Power BI dashboards that reflect what’s happening in the business today, not last week, have improved performance noticeably. Queries that once ran slowly now return results quickly.
Beyond the technical wins, what stood out was how the engagement evolved. As the automated pipeline proved its reliability, the client’s confidence in our team grew with it. What started as a focused automation project gradually opened into broader conversations about where their data infrastructure could go next. That kind of trust forms when a team shows up consistently, gets the details right, and treats the client’s problems as their own.
