RAPS Data ETL Integration

Regulatory Affairs Professionals Society

Data ETL

About the Regulatory Affairs Professionals Society

The Regulatory Affairs Professionals Society (RAPS) is the largest global organization for those involved in the regulation of healthcare and related products, which include medical devices, pharmaceuticals, biologics, and nutrition products. RAPS helped establish the regulatory profession and continues to actively support the professional and lead the profession as a neutral, non-lobbying nonprofit organization.

Executive Summary

RAPS had approached Pumex to assist with the migration of their members and learning data from a legacy LMS system to a newer and more robust solution. This data had been through several different systems to date and prior to the move and the organization wished to undertake a data ETL process to ensure that the data was clean, properly conformed to the source requirements, they maintained the integrity of their data through the migration process, and legacy data that was no longer needed would be archived separately from the new platform.

The Pumex BA team had to work closely with the RAPS data stakeholders to properly define what were a loosely communicated set of data requirements. Through this effort, our BA team pinpointed the exact criteria to ensure the data met all organizational standards.


Because the data RAPS was moving into the new system had been through several different systems there were significant challenges around data integrity, duplication of data, and differentiating old data that did not need to be migrated. As with all projects, Pumex implemented standardized success processes. These processes included of a) reviewing data tables for project inclusion, b) validating the data inclusion assumptions, c) collecting appropriate organizational business rules, d) performing a thorough data analysis within the below framework, e) providing data cleanup recommendations to RAPS Stakeholders, f) completing stakeholder approved recommendations.

large large

Because of the complexity of the project, Pumex broke the effort down into two phases. The first phase was designed to capture all organizational business rules around the data migration effort, understand data quality challenges, and provide an exacting estimate to complete the actual data migration work. The second phase was the actual migration of the data, which included, developing the ETL layer, implementing the defined business rules, performing the data cleansing activities, and finalizing the migration of the data to the new solution and archived database.

Present and Future Outcomes

This Data ETL and migration project was completed on time, on budget, and to client quality expectations.

There were 0 reported data issues by RAPS in the new LMS system.

Having gone through this process, RAPS now has a clean set of learning data, that has been standardized so they can perform accurate data analysis and reference.

Future Goals

Pumex is RAPS vendor of choice for all web development, data migration, cloud infrastructure management, and cybersecurity requirements. Pumex is proud to continue supporting the overly complex RAPS website with many custom web parts and their overall IT (Information Technology) environment.

IAIW Data ETL Development and Salesforce Integration

Internation Association of Ironworkers

Data ETL Development and Ongoing Synch Process

About the International Association of Ironworkers

The International Association of Ironworkers (IAI) is a union helping to secure stronger benefits, better pay, safer working conditions, and a higher level of job security for its 120,000 members across North America. Their members have been involved in major construction projects such as the Golden Gate Bridge, the World Trade Center, and the Freedom Tower.

Executive Summary

IAI needed the data on their legacy systems database (DB) to be synchronized with their partnership forum: IMPACT and their Salesforce instance. This data contained an initial 2.5 million records with about 10,000 weekly updates, and the data sync is needed to maintain all relationships within the Salesforce objects.

After an unsuccessful implementation of Heroku due to capability gaps and high costs, IAI reached out to Pumex to develop a custom solution.


IAI’s Heroku implementation was not able to handle the initial data sync of 2.5M records and the ongoing update of 10K records per week. Source member data was incorrect and didn’t have unique identifiers, there was Unicode within text, incorrect country/state values, and invalid date ranges within the data. Important member data was spread across multiple legacy databases and did not utilize a common primary key. The sync to the IMPACT Salesforce forum required that member certifications and course relationships be maintained, members correctly map to the appropriate Union locations, and members are located under the correct District Councils.

large large

Pumex implemented a client side ETL layer to extract, load, and store the data into an MSSQL Server 2016 intermediary DB prior to synchronizing with Salesforce. To manage the business rules, we used a .NET Core application. This DB updates every 10 minutes to ensure all data is properly synchronized on an ongoing basis. We used Salesforce’s Streaming API to associate IDs with the correct record, and Salesforce’s API to synchronize all 2.5M records into Salesforce.

Immediate Results

Data sync with refresh

An initial sync of 2.5M records from IAI’s legacy systems and refreshed any changes in the data every 10 minutes.

Data records integrity

All updates and deletes to any records were professionally managed and correctly synchronized with Salesforce.

Low sync error rate

We achieved a sync error rate of .0008% (~2,000/2.5M records)

Future Goals

Pumex developed an additional “Gold Source DB” for IAI. All data will be migrated here once IAI decommissions all their legacy systems. Over a 3-year period, IAI has projected that our Unity Data Sync work will reduce their ongoing data management operational costs by 40%.