Challenges
- Client is a biopharmaceutical company
- Client uses Salesforce Health Cloud but it does not maintain history.
- Project objective is to
- Snowflake to be leveraged for DWH
- To handle full & incremental workloads
- To support dynamic schema update from Salesforce to Snowflake
- Maintain history (SCD Type2)
- IICS is used for ELT; it has a limitation for Dynamic Schema
Solutions
- Migrate identified objects from Sales Force Health Cloud to Snowflake (CDL) & To maintain transaction history
- PII/PHI data encryption at rest; to retain deidentified data copy
- Soln. to support Data Quality Management, Data Lineage & Data Governance;
- Framework to cover DQM for data issues (like Duplicates records, Incorrect calculations, Missing/ inconsistent data,etc.)
- Near real time incremental build (/30 min)
Outcomes
150+ objects replicated
In 3 months
Dynamic Schema enabled
Column addition from SFHC to Snowflake
Reusable Solution Delivered
Is Source type agnostic
Parametrized Stored Procedure
To handle SCD Type2 and Dynamic Schema
