d3b-center/d3b-warehouse-redcap

🏭 D3b Warehouse Pipeline for REDCap

D3b REDCap Warehouser

Purpose

1. Extracts clinical data from REDCap
2. De-identifies it via BRP-eHB
3. Stores everything into a warehouse database.

CLI Help

python warehouse_project.py --help

Known invocations

Where:

• REDCAP_TOKEN_##### is an environment key storing the API token for that REDCap project
• BRP_TOKEN is an environment key storing the user API token for the BRP
• CID_MAGIC_NUMBER is an environment key storing the magic number used for generating CIDs from eHB IDs
• D3B_WAREHOUSE_DB_URL is an environment key storing an authenticated URL (protocol://user:password@address:port/db) for the D3b warehouse

Oligo Nation, REDCap project 27084, BRP protocol 159, eHB organization 102

python warehouse_project.py REDCAP_TOKEN_27084 BRP_TOKEN 159 CID_MAGIC_NUMBER D3B_WAREHOUSE_DB_URL --redcap_organization_override_value 102 --redact description_of_chemotherap --redact other_rad_treat --redact describe_predis

DGD, REDCap project 33723, BRP protocol 95, (only if CID exists)

python warehouse_project.py REDCAP_TOKEN_33723 BRP_TOKEN 95 CID_MAGIC_NUMBER D3B_WAREHOUSE_DB_URL --redcap_id_within_organization_field mrn --only_warehouse_if_CID_already_exists --fillmask diagnosis_id=dgd_diagnosis=d3b_event_identifiers

d3b-warehouse-redcap's Issues

Add a note in diagnosis link filed to include completeness of diagnosis form

CRU team does not know that the diagnosis form must be complete to choose the links in updates, treatment, and specimen only instruments. We should email redcap admin to do this for us.

export redcap metadata to study_metadata table

Add organization name and id in redcap instrument

to keep the organizations between different d3b-center projects consistent, we should add BRP organization id and name in Enrollment instrument because of query parameters of BRP does not work and they have inconsistent organizations.

Delete enrollment data from redcap import code after subject creation in BRP

Create additional text field if Other is selected in vps_etv(shunt) in Diagnosis Form

cli argument names and descriptions don't match usage

The the brp_protocol and naut_irb_protocol cli arguments are described as numbers, but they are being treated as environment keys. That's inconsistent with the other arguments which explicitly describe themselves as env keys.

https://github.com/d3b-center/d3b-redcap/blob/11666a33127ab24e2ab293e58a95935cd0fb5f47/d3b_warehouse_etl/oligo_export.py#L106-L125

Containerize Extraction and De-Identification Processes

Containerize the extraction and de-identification Python processes for data from REDCap and Nautilus data. It would be helpful to create a Dockerfile to build an image that can install all the dependencies and run the scripts necessary for the ETL which will be deployed to the K8s cluster on the IS premise.

Change the site_of_progression in diagnosis form to take check boxes multiple choices

Currently, we have multiple choice Radio button with a single answer option which does not let to choose both metastatic and primary progression.

Consider Changing the Repo Name

Cross-referencing d3b-center/d3b-chordoma#11, let's consider changing this repo name to a more representative name following KF's naming convention. Possible candidates include:

• d3b-lib-datawarehouse-redcap
• d3b-lib-data-warehouse-redcap
• d3b-datawarehouse-redcap
• d3b-data-warehouse-redcap

Support upsert on global tables

we need to support upsert for project_info and sample_information, now we are appending to the table which is going to create multiple entries if we run the same study.

Auto-redact dates found in non-redacted free text fields

Maybe with something like https://dateparser.readthedocs.io/en/latest/#dateparser.search.search_dates or https://datefinder.readthedocs.io/en/latest/index.html#datefinder.find_dates

Discuss Infrastructure for the Implementation of Data Warehousing

Discuss infrastructure for the implementation of a data warehouse for D3b reporting. We need to discuss the following, but not limited to, items:

• Purpose
  • Who are the audience of the data warehouse, CRU/BRU, engineers, and/or leaderships?
  • Do we aim to 1) simply provide automated reporting pulls per study protocol or 2) add additional layers of analytical capability against approved protocols?
  • Do we want to run reporting/analytics solely per study or across the entire protocols?
• Location
  • Where will this warehousing be residing? (REDCap, Nautilus, and BRP reside inside of the CHOP network)
  • Which level of security should the werehouse be kept with?
• Data flow
  • Does it include PHIs in any part of the system? Where and how do PHIs move around?
  • Do we need to ingest raw data pulls from REDCap, Nautilus, and EHB (historical) or just periodical reports?
  • How often will the ETL be kicked in and the periodic ETL preserve the history of changes?

Handle Multiple Protocols Import

The current pipeline ETLs only one study against REDCAP_TOKEN_ENV_KEY and BRP_PROTOCOL given in compose.env. Implement a multiple studies import.

Create docker-compose

It would be helpful for local development if there was a docker-compose.yml file that ran the ETL code and also ran the target database. The compose file should also configure all the environment variables for the ETL job so that it may be run without additional configuration.

Bonus points for being able to mock out the source data layers in some way.

Design Data Warehouse Schemas

Design new data warehouse schemas to 1) stage raw and transformed data and 2) load reports. The following schemas can be considered:

1. Staging: this schema is intended to store raw data pulls (possibly de-normalized) and transformed, normalized data. Each of these data sets can be stored in different namespaces, conventionally, staging and user_maintained. Tables in staging are for historical, sanity checking and others in user_maintained are for integrated, merged data
2. Warehouse (Target): it stores periodical reports run and pulled against pre-defined metrics