Case Study
Centralised Dashboards
Overview
The client’s subcontractor ecosystem was fragmented: each project team used separate spreadsheets, vendor portals and project-management tools to track subcontractor commitments, performance, change-orders and payments. Executives lacked a unified view of subcontractor health, cost impact and risk. Leadership was making decisions with outdated or incomplete data, leading to delayed responses, cost overruns and scheduling conflicts.
The contractor engaged Sphere to break down data silos, unify subcontractor-related information across ERP and field updates, and deliver a live dashboard layer. So leadership could make faster, more confident decisions regarding subcontractor performance, allocation and cost.
Challenges
− Subcontractor agreements, commitments and performance lived in the ERP; field updates and punch lists were managed in separate project systems or spreadsheets. − No single repository or view of subcontractor cost vs. performance across projects.
− PMOs compiled subcontractor performance and cost data manually each quarter; reporting lagged by 5-7 business days. − Inconsistent metrics and definitions (e.g., “subcontractor delay” or “corrective cost”) reduced trust in the data.
− Without early warning mechanisms, subcontractor issues (poor performance, high change-orders, corrective work) caused ripple-effects on schedule and budget. − Cross-project benchmarking of subcontractor performance was impossible.
− Executives lacked up-to-date metrics on subcontractor schedule adherence, change-order frequency, corrective work or cost variance. − Delays and overruns often surfaced too late to be addressed proactively.
Our Solution
We delivered a 10-week engagement broken into key phases:
Outcome: A single governed data warehouse in Snowflake served as the authoritative source for subcontractor analytics — replacing spreadsheets and manual exports entirely.
Outcome: Executives gained live access to accurate subcontractor data, refreshed nightly. Field directors could track under-performing subs in real time, and PMOs no longer prepared static Excel reports.
1. Data Collection and Staging We connected to…
Data Collection and Staging We connected to the client’s ERP (financials, POs, invoices), and Excel field logs using Fivetran and custom Python ETL jobs. Raw data was staged in a Snowflake Lakehouse environment hosted on AWS.
2. Database & Schema Design
Database & Schema Design
3. Built a star
schema model with dimension tables for subcontractors, trades, projects, and cost categories.
4. Created fact tables for commitments, change
orders, corrective work, and schedule adherence.
5. Designed cross
system ID mapping to standardize subcontractor names and vendor codes.
6. Establishing the Data Pipeline
Establishing the Data Pipeline
Key Achievements
Unified data across ERP, scheduling, and field systems exposed hidden inefficiencies and recurring change-order patterns, allowing leadership to act early and contain costs.
Automated ETL pipelines in Snowflake and live Power BI dashboards replaced manual roll-ups, delivering real-time visibility into subcontractor performance and project health.
With consistent, accurate data and intuitive visual dashboards, executives and project directors began using the system daily for decision-making and risk oversight.
Snowflake Lakehouse consolidated financial, operational, and schedule data to create a governed, scalable foundation for future analytics, forecasting, and vendor benchmarking.
Schedule a Free Session with our Data Experts
Flexible, fast, and focused — Sphere solves your tech challenges as you scale.
Luke Suneja
Client Partner
