Nanosonics: Boosting Data Infrastructure with Snowflake for Enhanced Performance and Scalability

Nanosonics, a global leader in infection prevention, sought to modernise its data infrastructure to support its mission of enhancing patient safety and healthcare outcomes. 

Pivot Analytics partnered with Nanosonics to deploy Snowflake, migrate complex SQL Server pipelines to dbt, and empower the internal team through extensive training. The 15-week project also included implementing AWS Glue and Snowpipe for efficient data handling and ensuring robust security with PrivateLink.

Key Highlights

1. Successful Migration to Snowflake and dbt: Addressed complex legacy systems and improved data process performance.

2. Empowered Internal Team: Extensive training enabled the team to confidently manage and extend data pipelines.

3. Robust and Scalable Solution: Established a secure and scalable data infrastructure ready for future integrations.

Challenge

Nanosonics faced significant challenges with their existing data infrastructure. The on-premise legacy database systems were difficult to maintain and not scalable, posing a major obstacle to their growth and operational efficiency. Additionally, the existing stored procedures in SQL Server were highly complex, making them hard to manage and maintain. These complications increased the risk of errors and also consumed valuable time and resources.

Nanosonics experienced reliability issues with their PowerBI dashboards due to the lengthy execution times of their data pipelines. With the daily PowerBI data loads often triggering before the pipeline load had completed. This delay affected the timeliness and accuracy of their data reporting, crucial for making informed business decisions. The team were keen to deploy a modern data warehouse, however with limited experience outside of their Data Team Leader, the team faced a steep learning curve, making the transition to modern data solutions a challenging task.

Solution

Pivot Analytics delivered a comprehensive solution to address these challenges. We started by deploying Snowflake, a scalable, cloud-based data warehouse solution that provided the flexibility and efficiency Nanosonics needed. This was a crucial step in modernising their data infrastructure and ensuring it could handle growing data demands.

Next, we tackled the migration of complex legacy stored procedures from SQL Server to dbt. This migration streamlined the data transformation processes, making them easier to manage and maintain. By simplifying these processes, we significantly reduced the risk of errors and the time required for maintenance.

To resolve the reliability issues with PowerBI dashboards, we developed a custom script that loads PowerBI data through the API after the dbt pipeline completion. This solution ensured timely and reliable data updates, enhancing the accuracy and dependability of their dashboards.

In addition, we implemented AWS Glue to load data into S3 and used Snowpipe to ingest this data into Snowflake efficiently. This setup ensured seamless data handling and processing, further improving the performance of Nanosonics' data infrastructure.

Security was another critical aspect of the solution. We utilised PrivateLink to ensure secure data transfer, safeguarding sensitive information and maintaining compliance with data protection standards.

A key component of the project was the extensive training and mentoring provided to Nanosonics’ internal team. Starting with no experience in Snowflake or dbt, the team received comprehensive training that enabled them to manage and extend their data pipelines independently by the end of the project. This empowerment was essential for the sustainability and continued success of their data initiatives.

Results

The project delivered several significant outcomes:

1. Improved Performance and Reliability: The migration to Snowflake and dbt, along with the custom PowerBI integration, significantly improved the performance and reliability of Nanosonics' data infrastructure.

2. Enhanced Scalability: The deployment of Snowflake provided a scalable solution that could grow with Nanosonics' data needs, ensuring long-term sustainability.

3. Empowered Internal Team: By the end of the project, the internal team at Nanosonics had gained substantial expertise in Snowflake and dbt, enabling them to manage and extend their data solutions independently.

4. Foundation for Future Expansion: This project laid the groundwork for the next phase of Nanosonics' enterprise warehouse deployment, which will include integrating Dynamics 365 as a data source.

   
   

Conclusion

The collaboration between Nanosonics and Pivot Analytics exemplifies the impact of strategic data infrastructure modernisation. By leveraging Snowflake, dbt, AWS Glue, and PrivateLink, Nanosonics enhanced its data capabilities, ensuring better performance, reliability, and security. The project also highlights the importance of training and mentorship in enabling internal teams to succeed independently.