Our client, a global leader in healthcare solutions, initially faced challenges with manual executive reporting for drug development. They needed an integrated dashboard to view key business performance metrics. HCLTech built a data foundry, integrating various data sources into the Snowflake data lake platform. This integration allowed near real-time data processing, predictive analytics and AI-based applications. Using Snowflake, data was accurately published on strategic dashboards and tactical reports were generated using data science principles. This streamlined planning, tracking and reporting processes, providing a scalable and adaptive platform for the client.
The Challenge
Addressing data silos and enhancing reporting with Snowflake integration
Executive leaders at our client organization faced challenges due to a heavy reliance on skilled resources for manually compiling business performance metrics related to drug development. This process involved data from CDCS, CTMS, MDM, Finance and HR, leading to data silos and slow report generation. With no integrated dashboard or single source of truth, accuracy in data processing was compromised. The client sought improved data sharing and time travel features for better insights, leading to the selection of the Snowflake platform for its integrated data management, reporting and analytics capabilities.
The Objective
Data integration and process optimization using Snowflake and AWS S3
Our client's key objectives were to:
- Utilize the Snowflake data platform for advanced visualization
- Integrate various data sources into a unified system
- Streamline planning, tracking and reporting processes
- Eliminate manual processes and reduce Excel usage
- Provide real-time analytics dashboards for executive leadership
- Store current and historical data in a unified cloud-based Snowflake data warehouse and AWS S3
The Solution
A unified data platform with Snowflake for integration, transformation and advanced analytics
HCLTech implemented a Snowflake Data Cloud-based Data Lake platform for data curation, standardization, harmonization, aggregation and transformation. This platform integrates data from various sources, including Tibco, into Snowflake in near real-time.
Data is stored in three layers:
- Raw (source replica)
- Integration (consolidated and transformed)
- Presentation (published to consumers)
Snowflake’s scalability and adaptability support seamless data sharing, predictive analysis, machine learning and AI applications. Matillion was used for ETL automation and orchestration. The platform enabled accurate data publication on dashboards, tactical reporting using data science principles and support for various downstream applications.
The Impact
Enhancing data quality and efficiency with Snowflake on AWS
The Snowflake platform on AWS offered a unified source of truth, managing data storage, structure and attributes with minimal user dependency. It utilized Snowpipe, SnowSQL and Snowsight for efficient data handling. Matillion was used for data extraction, loading and transformation, while Tibco facilitated data replication. The Erwin predictive data modeling repository automated operational tasks.
Benefits of adopting Snowflake included:
- 7-8% increased revenue opportunities through advanced data-sharing capabilities, which provided deeper business insights and fostered collaboration
- Faster, data-driven decision-making enabled by near real-time reporting, with enhanced data quality and consistency ensuring more accurate, timely insights.
- Achievement of data transformation goals via the deployment of a 24/7 self-service model, empowering stakeholders to independently access the data they need, improving operational efficiency and reducing dependency on IT.
- 5% improvement in site enrollment quality, driving more precise site selection and better alignment with project needs, which ultimately led to higher success rates in clinical trials.
- Annual savings of ~6,000 hours through automation and optimization of the site list generation process, significantly reducing manual effort and freeing up resources for strategic tasks.
