ETL troubleshooting recipes (To make changes permanent in DB don't forget to issue COMMIT at the end.)
Issue:Missing BAM file path in PDO Tracker or WGS/WES Project Tracker report (PDO_STAR5_Aux DM) (for on-prem only)
Cause:
- Seq Only PDOs do not get BAM files in PDO_STAR5_Aux DM
- Plated sample in BSP_Plated_Samples is different than slxre_readgroup_metadata.sample_id
- Read group of the PDO Sample is missing in SLXRE2_RGHQS, most likely because there isn't a record in metrics.basecall_analysis for metrics_type Indexed or Unindexed.
- PM billed sample before PDOSTAR5 picked the most recent aggregation or before sample met full coverage
Remedy:
- None
- Check if Mercury DWH and Mercury Run API still do not agree about the plated sample. If they don't - seek help from Mercury team. If they match, a refresh of the flowcell should correct it. BSP exported samples might be associated with the wrong PDO in BSP_Plated_samples because the mapping in BSP is not accurate. If it is important to fix the data , BSP team could help and then request a refresh for the affected plated sample as instructed in the recipe on this page to update BSP Plating DM. Finally, make sure to refresh in PDO Star5 if it won't happen automatically.
- If there is data for Indexed or Unindexed on metrics.basecall_analysis, then the flowcell needs to be refreshed in the datamarts. Otherwise, make ticket for the Pipeline and refer to PO-13231 as being the same issue.
- PDOs created within past year will get refreshed on Friday 2:55 am or Analytics manually refresh for faster data delivery (put link to Checking Metadata Changes 5/5/19). PDOs older than 1 year MUST be manually refreshed.
Issue: When upstream data change, some samples in ReadgroupMetadata might need to be refreshed. Instructions already on Checking Metadata Changes
Remedy: Request a refresh for RunMercuryAgent by issuing SQL like this in SEQPROD.COGNOS:
BEGIN
cognos.data_warehouse_api.request_refresh(P_FLOWCELL_BARCODE=>'XYZ', P_REQUESTED_BY=>'JohnDoe', P_COMMENTS=>'Test', P_DEFERRAL_ANALYSIS_DATE=>null);
END;
Note: No COMMIT needed in this particular call - it's implicitly done for you. Just replace the XYZ with a flowcell barcode. Leave all other parameters there and unchanged. (put link to Checking Metadata Changes 5/5/19)
Issue: PicardAggregator ETL fails with (COGNOS.SLXRE2_PAGG_SAMPLE_PK) violated error.
Cause:
- Most likely Is_latest flag is set for more than 1 aggregations.
- Another case is when samples are reprocessed on the cloud, sample renaming and research project renaming, where the old aggregation needs to be set to "not latest".
Remedy:
- There is an agent that looks for certain Is_latest flag violations (IsLatestCop) that specifies the list of metrics.aggregation records with the issues and automatically opens a PO ticket in the pipeline.
- Find the sample that causes it (run the query below) and make a blocker PO ticket + notify somebody from Pipeline team to address it ASAP because the ETL won't recover until this is fixed.
Issue: Designation ETL fails with "ORA-30926: unable to get a stable set of rows in the source tables" error
Diagnosis: This is usually caused by a flowcell with 2 runs valid run (uncanceled), one of which should have been cancelled. Run the query below to determine the trouble flowcell:
SELECT r.flowcell_barcode, count(DISTINCT r.run_name) FROM slxre2_organic_run r WHERE r.is_cancelled = 0 AND r.run_type <> 'UNDECIDED' AND r.run_date >= <SOME DATE> GROUP BY r.flowcell_barcode HAVING count(DISTINCT r.run_name)>1
Remedy: There is an agent (OrganicRun) that reviews if a flowcell has 2 valid runs, and emails the Sequencing team to clear the bad run from their dashboard. Designation ETL will recover as soon as the run is cleared.