Folks,

I have two pieces to the question

1. Who has the codes for Snomed and ICD10 loaded and could they export that data from their OMOP Vocabulary tables for MGB? It looks like MGB has only ICD10 and SnoMed codes to load. I'll be working to confirm that with EDW folks at MGB.
2. Who else ran into issues with CSV not being CSV but tab separated and failing copy into Postgres because of the delimiters interfering with NULL values ?

• The Athena gzip files say CSV yet they are all tab separated.
• Is tab the only option for gzipped file output from Athena?
• Is there an Athena API I could use to curl the data into a comma separated value output file?
• Using the copy command into a Postgres database test area on my laptop. The command fails on parsing some of the files due to the placement of NULL values vs tabs.
• Loading the data as a test into OMOP CDM tables in a test Snowflake account and exporting that data as CSV got around the issues when loaded locally on my laptop into a Postgres database with the copy command.

Referential integrity:
The main concern I have below is about referential integrity.

• Applying the database foreign key constraints after test loading showed data issues (will have to replicate to see if I can reproduce it)
• In the process of loading the CSV's in the SnoMed zipfile that I downloaded from Athena - I had run into an issue with applying the fk key constraint on table concept_synonym where it said the language_concept_id 4180186 is not in concept table.

The thing is... the concept id is in OMOP CDM concept table and confirmed with this SQL below:

      select count(*)
from concept
      where concept_id in
            (SELECT concept_id
            FROM public.concept_synonym
           WHERE language_concept_id = 4180186); -- yields 386