Loading vocabulary tables into local database

Implementers
1

Does it make sense to load the vocabulary tables with _CONCEPT_ID columns with NULL values as long as it is not required?

When we are loading DRUG_STRENGTH table into our local database, we specify NULL values for all empty strings irrespective of column. So, we get NULL values in NUMERATOR_UNIT_CONCEPT_ID which is not required but when we ran DQ check we get Conformance fail because there are close to 50% NULL values for NUMERATOR_UNIT_CONCEPT_ID column. The result can be seen below

image
image1164×114 11.4 KB

Now the question becomes, should we load the columns with _CONCEPT_ID in the vocabulary tables with empty strings as 0 instead of NULL?

2

@Sam:

No, you cannot. Null means the value is not allowed, and will be checked for that. For example, a denominator is only useful if the drug is a liquid, and the strength is given as a concentration. For a solid preparation this makes no sense, and therefore the denominator should be null.

3

@Christian_Reich Thank you for your response.

4

thanks @Christian_Reich.

Then if these fields, for example NUMERATOR_UNIT_CONCEPT_ID in DRUG_STRENGTH is allowed to be null.
Why the DQD is telling that it cannot be null?
@clairblacketer, is this a bug. ?

It’s hard to believe this is a bug, bcs DRUG_STRENGTH comes straigth from athena. Every one shuld be having the same error.

Any ideas what we may we be doing wrong?
we are using BQ, @jposada, I believe to remeber that you also use BQ, have you experience this ?

5

@Javier
According to the v5.4 field-level thresholds file, the numerator_concept_id is not required (third column is isRequired).

image
image1610×1032 101 KB

Only drug_concept_id, ingredient_concept_id and valid dates are required.

6

What @MaximMoinat said, but it is more complicated. Anything below the level of Clinical Drug Component (=ingredient plus strength), like any actual drug product, has to have either information in the amount or the numerator/denominator fields in DRUG_STRENGTH. If it has neither, it is an error, but the logic is too complicated for the DDL. The DQD has to check that kind of thing.

7

Thanks @MaximMoinat and @Christian_Reich

We have now 3 sources of info that are not matching:

  1. @MaximMoinat (and the cdm specs) says that this particular filed NUMERATOR_UNIT_CONCEPT_ID can be NULL.

  2. @Christian_Reich says that can be NULL if other columns can are not null

  3. The DQD script says that it can never be NULL (it is not checking for values in other columns )

image
image895×787 15.7 KB

what im i missing ??

8

ok, it may be a bug in the sql translation to BQ, I will keep posting here

9

@Javier this was a bug in the cdmDatatype SQL which has been fixed in the latest release of DQD (v2.1.2). NULL values should not be failing that check. If you update your DQD these failures should go away :slight_smile:

1 Like
10

thanks so much @katy-sadowski , that was the solution

1 Like