Snowflake-Based Risk Assessment Platform
A leading pharmaceutical company from around the world, Roche Pharma, collaborated with our team to build a Snowflake-based data product that evaluates risks in their direct materials supply chain. Our data integration and analytics solutions, combined with visualization capabilities, enhanced Roche’s risk management operations by providing a scalable platform that supports informed strategic decision-making.
industry
Pharmaceutical
type
Data Risk Assessment, Supply Chain Analytics
region
Global
Highlights
- Merged data from SAP, Google Sheets, and additional sources into a Snowflake Data Vault.
- The risk-score algorithm in dbt operates automatically.
- Interactive Tableau dashboards enable procurement teams to quickly analyze vendor and material data.
- Documented and CI/CD-validated through GitLab, ensuring the solution is equipped for future challenges and audit processes.
Challenge
The project revealed several major challenges that required careful organization and cooperative efforts. Merging information from diverse and complex origins, including large-scale ERP systems and spreadsheets, necessitated effective ETL workflows and meticulous data standardization. The reliance on manual risk assignments led to additional inefficiencies and heightened the likelihood of human mistakes, underscoring the necessity for automation.
During the project’s progression, additional business needs emerged, increasing the complexity and necessitating the team’s swift adaptation while maintaining deadlines and data integrity. These elements collectively rendered the project challenging both from a technical and organizational perspective.
Do you face a similar project challenge?
Solution & functionality
To tackle the obstacles, the team implemented a robust and adaptable data solution aimed at automating and improving risk assessment workflows. They consolidated data from various sources through Talend ETL pipelines and organized it within Snowflake using a Data Vault framework, which facilitated consistency, scalability, and auditability.
dbt was utilized to streamline intricate data transformations and establish uniform logic for determining risk scores based on vendor dependability, material availability, and other supply chain elements.
An internal automated testing framework, integrated with GitLab CI/CD pipelines, was employed to ensure data integrity at every stage, guaranteeing precise and reliable results.
For business users, interactive dashboards in Tableau were created to illustrate risk profiles, supply chain patterns, and critical risk indicators, featuring options like filters and drill-downs for in-depth analysis. The solution also encompassed thorough documentation (including URDs, data models, source-to-target mappings, and test cases) along with version control through GitLab to promote maintainability, traceability, and seamless collaboration.
Overall, the system automated tasks that were previously manual, enhanced data reliability, and equipped stakeholders with real-time insights for proactive risk management.
Tech stack
Data platform & modelling
Snowflake (Data Vault 2.0)
ETL / ingestion
Talend
Transformations & tests
dbt + internal automated-testing framework (GitLab CI/CD)
Visualization
Tableau
Results and business value
The project provided a flexible, automated system that greatly enhanced the precision and effectiveness of risk evaluation procedures.
By utilizing Talend and dbt for automation, the team has significantly decreased the amount of manual work, allowing them to dedicate their efforts to tasks of greater importance.
The Data Vault design adopted by Snowflake guaranteed the capacity for expansion, accountability, and the effortless addition of fresh data sources.
All intended sources were successfully incorporated without any significant data problems, due to the implementation of automated testing through GitLab CI/CD.
A logic-driven risk model replaced outdated manual processes, tailored to the client’s specific needs
The use of interactive Tableau dashboards allowed for immediate awareness of vendor and material risks, leading to swifter and better-informed choices.
Our work
Get in touch with us
Book a call or fill out the form below and we’ll get back to you once we’ve processed your request.
What happens next?
An expert contacts you after thoroughly reviewing your requirements.
If necessary, we provide you with a Non-Disclosure Agreement (NDA) and initiate the Discovery phase, ensuring maximum confidentiality and alignment on project objectives.
We provide a project proposal, including estimates, scope analysis, CVs, and more.
We're here to help!
Relationship Manager
Marketing Lead