Direct Material Risk Assessment: Snowflake-Based Data Solution
A global pharmaceutical leader partnered with us to build a direct material risk assessment solution using Snowflake. By combining advanced data integration, automation, and interactive dashboards, the platform provides real-time risk insights across the supply chain. Our data integration and analytics solutions, combined with visualization capabilities, enhanced the client’s risk management operations by providing a scalable platform that supports informed strategic decision-making.
*Due to NDA restrictions, specific client details and case study insights cannot be disclosed.
industry
Pharmaceutical
type
Data Integration, Data Analytics, Automated Testing, BI Dashboard Developemnt
region
Global
Highlights
Automated Risk Scoring: dbt-based logic replaced manual processes for consistent and scalable risk evaluation.
Unified Data Foundation: Integrated SAP, spreadsheets, and internal systems into a Snowflake Data Vault.
Real-Time Insights: Tableau dashboards provided instant visibility into vendor and material risks.
Validated Data Quality: CI/CD-driven testing ensured 100% integration accuracy with zero critical data issues.
Challenge
The project surfaced several challenges common in implementing a scalable direct material risk assessment solution. Data originated from multiple, complex sources—including large-scale ERP systems (like SAP), spreadsheets, and internal tools—requiring robust ETL pipelines and careful data standardization to ensure accuracy across the supply chain. The client’s existing process relied heavily on manual risk assignments, which introduced delays, inconsistencies, and a higher risk of human error, making a strong case for automation and logic-driven calculations.
As the project progressed, evolving supply chain risk management needs added layers of complexity. Business stakeholders introduced new data requirements and risk indicators mid-project, prompting the team to adapt quickly without compromising delivery timelines or data integrity. The combination of fragmented data, outdated manual processes, and shifting priorities made the project both technically demanding and organizationally intensive—necessitating a flexible, automated, and auditable solution.
Do you face a similar project challenge?
Solution & functionality
To address the challenges, the team delivered a scalable and fully automated solution for direct material risk assessment, designed to support complex supply chain risk management needs. Data from ERP systems, spreadsheets, and internal sources was integrated using Talend ETL pipelines and modeled in Snowflake using a Data Vault 2.0 architecture, ensuring data consistency, traceability, and scalability across the supply chain.
The team implemented automated risk scoring using dbt, transforming raw data into standardized, logic-driven risk indicators tailored to vendor reliability, material quality, and availability.
An internal automated testing framework, executed via GitLab CI/CD, ensured robust data quality validation at every stage—minimizing errors and maintaining trust in the outputs.
For business users, interactive Tableau dashboards were developed, offering real-time visibility into vendor performance and material-level risks through filters and drill-downs.
The system architecture supports an end-to-end direct material risk assessment process by consolidating data from ERP systems, spreadsheets, and internal sources into a unified risk management platform. At the core are two databases: the RM Repo Database (hosted on the Hemes platform) and the PTE Database, built on Snowflake using a Data Vault model. Together, these serve as the backbone for all data processing, transformation, and reporting workflows, enabling a scalable, auditable structure for handling complex supply chain data. This architecture ensures fast query performance, supports real-time supply chain analytics, and provides a reliable foundation for proactive and automated risk management.
The solution also featured comprehensive documentation, including URDs, data models, source-to-target mappings, and test cases, all version-controlled for collaboration and audit readiness.
Overall, the platform eliminated manual risk processes, increased data reliability, and empowered teams with real-time, actionable insights to proactively manage supply chain risks.
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
Within 10 months, our team 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.
Related projects
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