Hello All,
I am a Data Engineer at Penn Medicine. I recently started supporting the OMOP ETL process. We are using EPIC data as well as some of our legacy data from before EPIC was implemented on an AZURE (or Microsoft SQL Server) system. I wasn’t part of the original OMOP implementation, but now as I have one update under my belt. I found that there are several of the fundamental tables (PERSON, VISIT_OCCURRENCE, CONDITION_OCCURRENCE, etc.) where the IDENTITY(1,1) property was used to create new keys each time the update is processed.
The majority of the ETL was using a Kill and Fill process, so all the tables that used the IDENTITY property were basically getting a new key each time an update was run. It doesn’t make sense to me why this was done; and I am wondering if any OHDSI members have designed their OMOP tables like this? Or do they use persistent keys that remain the same from update to update?
As an aside, the current Kill and Fill process has been unwieldy and I am converting the ETL to update incrementally. So, this key question is even more important to resolve.
Also, is there any kind of ERD that shows the Primary Key - Foreign Key relationship between the tables? I have been searching for it on the OHDSI site and not having luck finding what I need.
Thanks So Much!
Patrice Chan