Using a de-identified CDM

[artgoldberg]

Hi Nic and colleagues

Our clinical data warehouse (DW) takes responsibility for creating a de-identified schema itself, as multiple downstream applications will use it. Using this de-identified schema I obtain results that diverge between Leaf and a DBMS client (DBeaver).

E.g., searching for persons over 55, Leaf returns 1 patients (sic). But the same query issued directly against the dbms obtains 4.3M patients, which makes sense in a dbms of 13M:

WITH wrapper (personId) AS (
    SELECT
        P0.person_id
    FROM
        (
            SELECT
                _S000.person_id
            FROM
                omop.cdm_deid.person AS _S000
            WHERE
                /* Current Age */
                (
                    DATEDIFF(DAY, _S000.birth_datetime, GETDATE()) / 365.25
                ) >= 55
        ) AS P0
)
SELECT
    COUNT(personId)
FROM
    wrapper

My suspicion is that the problem is caused by the type of person_id. It's a binary (32 long) field, which is the hashed, de-identified version of the PHI person_id.

Can Leaf handle this?

Thanks
Arthur

[ndobb]

Hi @artgoldberg,

Immediately after running the query in the Leaf UI, can you check the LeafDB app.Cohort table PersonId field to see if the hashed de-id person_ids were transferred by the Leaf API properly?

SELECT TOP 1000 C.PersonId, Q.Owner, Q.Created
FROM app.Query AS Q
     INNER JOIN app.Cohort AS C
	ON Q.Id = C.QueryId
WHERE Q.Created = (SELECT MAX(Q2.Created)
                   FROM app.Query AS Q2)

The app.Cohort table is used for patient ID caching. This table may thus give us a clue as to why the patient count is not the expected 4.3m. Note you'll either need to raise the Cohort.RowLimit to > 4.3m in order to force Leaf to cache that number of patients, or add some additional criteria to get a cohort below that number but still greater than 1.

The Leaf API does a very simple ToString() call to retrieve the patient identifiers from the clinical database while iterating through results returned from the query. I can't think of any reason why the API would mis-count. Hopefully this will give us some clues.

Best,
-nic

[artgoldberg]

Appreciate your quick reply @ndobb

I ran my query above with an age limit of 77, which the CDM says has 5452 patients. And Leaf says 1.

Your query generates this result:

System.Byte[]	arthur.p.goldberg@mssm.edu@urn:leaf:issuer:leaf.mssm.edu	2021-08-24 10:01:16

What think ye?
Arthur

[ndobb]

Hmmm, @artgoldberg as an experiment would you be able to create a SQL view around omop.cdm_deid.person, keeping all fields the same except CASTing person_id to a string, perhaps something like CAST(person_id AS NVARCHAR(50)?

If you're able to do that let's try it, then run the same query again using the view. I'm wondering if perhaps the reading the de-id field into a hash-table in the API is failing due to the underlying column type. If so, coercing into a string on the DB side may resolve.

[artgoldberg]

Hi @ndobb
I can achieve this objective, but in another way since I cannot create views. I'll copy person to a DBMS I can write, transforming as you suggest, and reconfigure Leaf to use it.
A

[ndobb]

Thanks @artgoldberg. As an alternative (if it's easier), we can also accomplish this using Leaf alone, with a subquery as the ConceptSqlSet:

Not a great solution but this should allow us to at least test.

Best,
-nic

[artgoldberg]

Thanks @ndobb

I've done this:

USE rpt

-- 1,000
SELECT TOP 1000 *
INTO LEAF_SCRATCH.person
FROM omop.cdm_deid.person

ALTER TABLE rpt.LEAF_SCRATCH.person ADD old_person_id BINARY(32)

UPDATE rpt.LEAF_SCRATCH.person
SET old_person_id = person_id

ALTER TABLE rpt.LEAF_SCRATCH.person
ALTER COLUMN person_id NVARCHAR(50);

UPDATE rpt.LEAF_SCRATCH.person
SET person_id = CAST(old_person_id AS NVARCHAR(50))

UPDATE LeafDB.app.ConceptSqlSet
SET SqlSetFrom = 'rpt.LEAF_SCRATCH.person'
WHERE SqlSetFrom = 'omop.cdm_deid.person';

But it didn't work.

SELECT TOP 1000 person.person_id 'person.person_id', old_person_id, ls_person.person_id 'ls_person.person_id' 
FROM rpt.LEAF_SCRATCH.person ls_person,
   omop.cdm_deid.person person

just returns fields that look empty.

I need a break, and then I'll try your suggestion.

A

[artgoldberg]

With help from Nic @ndobb we have resolved this problem by transforming all of the BINARY(32) de-identified person ids in our de-identified CDM into 64 character strings when the person ids are first obtained by Leaf.

The transformations are incorporated into the SqlSetFrom field of the LeafDB.app.ConceptSqlSet, which specifies the tables that contain data from OHDSI clinical domains which Leaf queries. For example, if the type of the person id was an OMOP standard-compliant INT, then Leaf queries that query conditions in our system would use a ConceptSqlSet.SqlSetFrom value of omop.cdm_deid.condition_occurrence. To transform BINARY(32) de-identified patient ids into 64 character strings we replace this value with a subquery which transforms the person_id into a NVARCHAR(64):

(SELECT [condition_occurrence_id],
        [person_id] = CONVERT(NVARCHAR(64), [person_id], 2),
        [condition_concept_id],
        [condition_start_date],
        [condition_start_datetime],
        [condition_end_date],
        [condition_end_datetime],
        [condition_type_concept_id],
        [stop_reason],
        [provider_id],
        [visit_occurrence_id],
        [visit_detail_id],
        [condition_source_value],
        [condition_source_concept_id],
        [condition_status_source_value],
        [condition_status_concept_id]
 FROM omop.cdm_deid.condition_occurrence)

This transformation is applied to each of the current and planned clinical domains in our system that will be queried by Leaf, as shown in the 1_sqlsets.sql script in our clone of the leaf-scripts repo.

Arthur