Data Migration & Scripting for Regulated Environments
Information Technology (IT) - 13 April 2016
As part of our monthly SOLABS technology blog series, we’re speaking with our software engineers on their reflections on the industry, its development and rapid changes, and how their area of current focus may be pertinent to compliance/regulatory and the future of EQMS software.
For this instalment, we’re talking with SOLABS’ Software Developer and Analyst Antonio Hernández, whose current work finds him collaborating closely with a Development team, and performing Data Migration activities to migrate client data from various legacy systems into SOLABS QM.
Gordon Bradley [GB]: So, you’ve been with SOLABS for almost two years?
Antonio Hernández [AH]: Yeah, about two years now. They’ve been great with me. I started off in data migration—where I am now. I guess I was a good fit because they needed someone with my background in that role, who was also a fast learner and detail-oriented.
GB: I try to approach these R&D interviews as a layperson, so that we don’t skip any details we might otherwise take for granted as being common knowledge. Data migration is obviously a valuable service that SOLABS provides: how are you involved in that whole picture and when?
AH: You’re right, data migration is one of the first and most key activities in the majority of our projects: either when we implement SOLABS QM initially, or when we add new document types to the system—such as SOPs, work instructions, methods and trial master files, for example. Clients want to start using SOLABS QM and have their historical data available and ready for use right away, in context. So yes: this is a service we proudly offer, and I work as part of a seriously great internal team.
GB: How do you guys normally proceed with that? How does it all get under way?
AH: In all cases, our Product Analysts speak with clients before the internal team even starts on a project. Mostly they’ll gather requirements and write functional specifications. Then, once the project order is delivered to us, my role is to write data migration scripts, and to discuss particular challenges with our team, since no two data migrations are alike. Then I finesse the scripts, and we test them.
"Clients want to start using SOLABS QM and have their historical data available and ready for use right away, in context. So yes: this is a service we proudly offer, and I work as part of a seriously great internal team."
GB: I observed a few steps involved in past data migrations; it was very meticulous and detailed. One aspect of the validation that struck me was the highly-demanding sampling rate1 that needs to be achieved for migration to be considered accurate.
AH: The step you saw was the checking of the source material to ensure that all necessary fields have been completed as required, because if there’s an error or inconsistency there, it’s going to derail the script later. After that step, it goes through several other gates and verifications, at which point it gets to me.
GB: I’m sure the migration is not a one-to-one thing, for example, copying Column A to Column A, right?
AH: This may not be easy for non-programmers to get, but it’s not a ‘Save As’. We don’t deal directly with the database. We actually re-build the instance or environment, through sequential keystrokes, step by step, as though it were a human rebuilding it, but in an automated way.
GB: Can you expand on that concept for those of us who are non-programmers?
AH: Yes, sure! The data migration scripts I write build the SOLABS QM world in the same order, in the same way, even with the same keystrokes as users would–but all of it virtually.
The migrating script enters all the information into SOLABS QM, but instead of using the software’s interface, it uses a façade2. This has big advantages over directly reading from/writing to a database–because it avoids errors, illegal operations and values. For example, if there’s a spreadsheet with a training activity whose curriculum is not defined, its creation is not allowed, because users can’t do that through the SOLABS QM interface.
PRECISION IN DATA MIGRATION: Sampling tables, such as the one above, are used to ensure the cross-section of data sampled is accurate to an extremely high, measurable and verifiable degree.
GB: So the way we avoid errors is by pre-verifying sources, multiple times, and if there is any irregularity, the whole thing stops?
AH: Yes, everything must be migrated correctly, or SOLABS QM, in combination with the script, will stop the migration: the script won’t run. The script replicates the process of a human inputting data into SOLABS QM. If an incorrect field were to cause a human entering the data to receive an error, then, likewise, my script encounters an error and the whole process comes to a halt.
You can see why all the pre-preparation is required. We’re not feeding SOLABS QM tables from a database, and we’re not feeding it Excel spreadsheets as such: we’re feeding it pre-checked information that is accurate to an extremely high degree, and we’re re-creating a data-modelled world, from the ground up, the same way it was created the first time. So it’s literally impossible, at my stage of data migration anyway, for the script itself to make an error—because it’ll stop running before it makes an error.
GB: So again: facade migration avoids errors by duplicating existing setups through the interface, but with a machine doing it–and all the info is pre-verified and thus entered at 100% accuracy during the migration.
AH: That’s exactly it! And it’s fairly common that migrations are done through having restricted access through facades, because you give your software a single way to communicate with the outside world—and it’s easier to control access to and from one door than it is 20.
GB: Wow. Thanks, Antonio. Lastly, I ask this of some of our engineers–is there anything that’s like an unexpected joy of your job?
AH: Well… I love getting back good code reviews… There’s a real satisfaction in that. [Reflects] Oh, and I love coming to work in the winter for the first couple of big snowfalls, and the old stone building3 is all blanketed in a layer of soft snow. It looks like a giant, stone house in the middle of a quiet, snowy forest. That’s nice.
1 Zero Acceptance Number Sampling Plans, Fifth Edition by Nicholas L. Squeglia ISBN 0873897390
2 Facade pattern – Wikipedia, the free encyclopedia
3 Entrepot Buchanan, built around 1845, historic Old Montreal stone building