Drop table SOURCE_TO_CONCEPT_MAP

This table is a remnant of OMOP CDM Version 3, when we didn't have the notion of source concepts. All sources were codes. That created havoc, because many vocabularies had both source only and standard content. Of course, some standard content is also used in the source. In addition, names from the source codes were not available anywhere. Finally, searching for source codes could only be done in the STCM table, which meant, we had to add a ton of records without any mapping. So, it was a mess.

The solution was to have all codes as concepts, and indicate their nature in the standard_concept field. That system, together with the "Maps to" CONCEPT_RELATIONSHIP records are the comprehensive solution to the problem. STCM is now completely redundant and only left in there for convenience, so not everybody had to change their mechanism to manage their mappings.

That was what? 7 years ago? It's time to move on. Codes in STCM are essentially hidden from any activity except ETLing, which could easily pivot to CONCEPT_RELATIONSHIP. ATLAS doesn't use it, and no other analytic for whatever use case uses it.

Start by locating the SOURCE_TO_CONCEPT_MAP definition and every reference to it in the Common Data Model DDLs and related workflows. Read how CONCEPT, standard_concept, and CONCEPT_RELATIONSHIP represent source codes, then determine the changes needed for consumers to use those structures instead. Done means the redundant table and its dependencies are removed without breaking the model or its supported workflows.