Page Comparison

Request:SUPPORT-9123
My samples are missing BAM file paths in PDO Tracker report. This translates into analysis are missing in the data source (cognos.pdo_star5_aux). The BAM file path is contructed constructed in the report based on the aggregation analysis.

...

1. Reproduce the issue in the report and then in SQL.
   

   Code Block
   language sql
   SELECT a.aggregation_project, a.analysis_id, a.data_type, a.runs_end, a.bsp_collaborator_sample_id, a.* FROM pdo_star5_aux a WHERE a.pdo_sample ='SM-JVCPT'

   
   ;
   -- returns NULL for aggregation_project and analysis_id. This means the PDO Star ETL didn't pick the aggregation. This data comes from aggregation data mart (cognos.slxre2_pagg_sample)

2. Check the aggregation data
   

   Code Block
   language sql
   SELECT * FROM slxre2_pagg_sample a WHERE a.SAMPLE ='microbial_pos_1159'; -- this is the bsp_collaborator_sample_id from Q1.

   
   -- returns no records.

3. Check whether there are aggregations in the raw data - where the pipeline records the metrics.

   Code Block
   language sql
   SELECT * FROM metrics.aggregation a WHERE a.SAMPLE ='microbial_pos_1159' -- this is the bsp_collaborator_sample_id from Q1.

   1. -- returns no records. This means that the aggregation has not been run yet. It can be queued (but we don't have visibility to that ) or the sequenced read groups could have been blacklisted. Blacklisted read groups are not included in the aggregation.

4. Check for sequencing data. Set a run_date filter that should capture the runs.

   Code Block
   SELECT SYSDATE, SYSDATE - 2/24, a.* FROM slxre_readgroup_metadata a WHERE a.collaborator_sample_id ='microbial_pos_1159' AND a.run_date >= SYSDATE - 20 --'15-nov-2022'

   -- returns the read groups that the sample was sequenced on.

...

1. Check whether the read groups were blacklisted.
   5.1. By looking up the blacklist table:
   

SELECT *
FROM seq20.sequence_data_blacklist b
WHERE -- b.flowcell_barcode = 'H3GKKDSX5' AND b.molecular_barcode_name = 'Illumina_P5-Nahok_P7-Loten'
--AND b.lane IN (1,2)
(b.flowcell_barcode, b.lane, b.molecular_barcode_name) IN (
('H3GKKDSX5',	1,	'Illumina_P5-Nahok_P7-Loten'),
('H3GKKDSX5',	2,	'Illumina_P5-Nahok_P7-Loten')
);

...