The warehouse under the city’s utility reporting
ETL pipelines and Kimball-style dimensional models in a Databricks medallion architecture, establishing the data foundation for predictive maintenance, long-term planning and compliance reporting on critical city infrastructure.
Reporting on utility infrastructure had been assembled per question — each analysis pulling from source systems in its own way, with its own assumptions about what a meter, a main or a service connection was.
That works until the questions get long-range. Predictive maintenance and long-term asset planning need a stable dimensional history, and compliance reporting needs the same numbers to come out the same way twice. Neither is achievable against ad hoc extracts.
- Designed and built ETL pipelines landing enterprise SAP data into a Databricks medallion architecture — bronze for raw fidelity, silver for conformed entities, gold for the reporting layer.
- Modelled the gold layer in Kimball-style dimensional form, so facts and conformed dimensions are shared across reporting rather than rebuilt per report.
- Integrated the modelled output into the organisation’s reporting platforms as the common source for utility infrastructure analysis.
- Reviewed legacy code and system workflows along the way to troubleshoot data issues and reduce the technical debt the old extracts had accumulated.
- Data foundation established for predictive maintenance on critical city utility infrastructure.
- Long-term infrastructure planning and compliance reporting run from the same modelled base.
- Reporting logic consolidated into conformed dimensions instead of being re-derived per analysis.
Have something like this sitting on your desk?
If any of the above sounds like a problem you're carrying, a free, no-obligation consultation is the cheapest way to find out whether it's tractable.