ETL troubleshooting recipes

Issue: Repatienting (rename externalID well after work on sample has started)

System: GAP

Remedy: Request a refresh for Sample_Attributes datamart by issuing SQL statement like this in GAP_PROD.ANALYTICS:

INSERT INTO sample_attr_special_refreshes

SELECT 'SM-' || a.sample_id, SYSDATE 

FROM sample_attributes a

WHERE a.collaborator_sample_id IN ('old_externalID_1','old_externalID_2')

Issue: When upstream data change, some BSP plating info in BSP_PLATED_SAMPLES might need to be refreshed.

Remedy:  Request a refresh for BspPlatingRequestAgent by issuing SQL like this in GAP_PROD.ANALYTICS:

INSERT INTO bsp_plating_special_refreshes VALUES('6E3K2', SYSDATE);

Note there is no "SM-" prefix,

Issue: When upstream data change, some samples in PDO_STAR5 might need to be refreshed.

Remedy: Request a refresh for PdoStar5Agent by issuing SQL like this in SEQPROD.COGNOS:

INSERT INTO pdo_star_special_refreshes VALUES('PDO-1', 'SM-12345', sysdate)

Issue: When upstream data change, some samples in ReadgroupMetadata might need to be refreshed.

Remedy: Request a refresh for RunMercuryAgent by issuing SQL like this in SEQPROD.COGNOS:

CALL cognos.data_warehouse_api.request_refresh(P_FLOWCELL_BARCODE=>'XYZ', P_REQUESTED_BY=>'JohnDoe', P_COMMENTS=>'Test', P_DEFERRAL_ANALYSIS_DATE=>null)

No COMMIT needed in this particular call - it's implicitly done for you.

Issue: How to prevent an entire FC from getting into ReadgroupMetadata.

Remedy: Blacklist it by issuing SQL like this in SEQPROD.COGNOS:

UPDATE slxre_rghqs_targets a SET a.blacklist_timestamp=SYSDATE WHERE a.run_name='run123'