Hoeksche Waard’s Leap to the Cloud: How Aurai Transformed Municipal Data Management

Municipalities manage an enormous amount of data that comes from a wide variety of sources. From demographic data to police records, public transport schedules to cadastral information, the influx of data is constant and diverse. Without a solid data foundation, it can quickly become chaotic. Organizing this data effectively is crucial for informed decision-making and seamless operations.

Building a cloud platform can offer significant advantages for municipalities like Hoeksche Waard. These include greater scalability, security, accessibility, and the ability to centralize and unify data from numerous sources.

Like many municipalities, Hoeksche Waard faced significant challenges in managing the vast and diverse range of data. Without a solid data foundation, their operations became chaotic, making it difficult to access the right data for valuable insights and causing data processes to become slow and inefficient. To address these issues, Hoeksche Waard enlisted the help of Aurai to modernize their data infrastructure.

Previously, the municipality relied on an on-premise database system that created several operational bottlenecks. Their data was scattered across various sources, including Excel sheets floating across different departments and scripts that pulled data from isolated APIs. This decentralized setup made it difficult to get a clear, unified view of their data.

They needed a solution that would centralize their data, providing better organization, security, and insights. The goal was to streamline their data workflows and enable easy access to valuable insights that could inform strategic decisions.

Choosing the Right Stack

The first step was selecting the right technology stack to meet the municipality’s needs. Understanding what tools were already in place and what skills their development team had was key to determining the best approach.

Since many of the municipality’s systems were already integrated within Microsoft’s ecosystem, an Azure-native solution was a natural fit. The municipality also used Power BI for their dashboarding needs, making it an obvious choice to continue with this tool for visualization.

Additionally, their on-premise systems used a low-code, block-based ETL (Extract, Transform, Load) software. Azure Data Factory (ADF) is similar in functionality to these legacy systems, so it was a logical replacement. However, the development team expressed interest in learning and leveraging Python for some of their ETL processes. With this in mind, we opted for Azure Synapse instead of ADF. Azure Synapse not only supports ADF-style pipelines but also allows developers to write Python notebooks that run on Spark clusters, making it capable of handling large datasets and giving the team flexibility in the future.

With cost approximations made and approved, we were ready to start building the new cloud-based platform.

Building the First Proof of Concept

To demonstrate the value of the cloud platform and provide immediate benefits to the municipality, we began by developing a proof of concept (POC). The focus of the POC was to provide insights into the liveability of different neighborhoods and areas within the municipality using only public data.

We sourced data from several publicly available platforms, such as the Chamber of Commerce (KvK), Statistics Netherlands (CBS), and the Basic Registration of Addresses and Buildings (BAG). Using the medallion architecture, we divided the data into layers:

  • Bronze Layer: Raw data was copied into this layer.
  • Silver Layer: Data was cleaned and modeled here.
  • Gold Layer: The processed data was made available for analysis and dashboarding.

With this structure in place, we developed a Power BI dashboard that displayed key insights on liveability, projected onto a map of the municipality. The POC successfully showcased the power and flexibility of the cloud platform.

Moving to Production

The success of the POC generated enthusiasm within the municipality, and we were given the green light to further develop the platform.

The next phase focused on creating a robust foundation for the data platform. We defined the resource groups using infrastructure as code (IaC), which ensured that the infrastructure could be easily reproduced and minimized the risk of errors. Networking was also handled through IaC, ensuring secure access to sensitive data, a critical requirement for municipalities.

Once the core infrastructure was in place, we built additional data pipelines to include internal municipal data into the data lake. This enriched the dashboard with deeper insights into the liveability of various areas, incorporating both public and internal data sources.

The result of the project was a dynamic Power BI dashboard that provided the municipality with essential insights into liveability across the region. This dashboard empowered the local government to make more informed decisions and shape policies based on data-driven insights.

Perhaps more importantly, the municipality now has a scalable cloud platform that can grow with them as they continue to develop data products and enhance their data maturity over time. With this solid foundation, they are well-positioned to further leverage their data and improve operational efficiency.

Aurai continues to support the Municipality of Hoeksche Waard as they evolve their data infrastructure and strategy, ensuring they can stay ahead in an increasingly data-driven world.

LET’S TALK!
MORE CASES