Page Comparison

...

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.

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

...