...
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.
| Code Block | ||
|---|---|---|
| ||
SELECT DISTINCT SAMPLEs, agg_ids, proc_loc FROM (
SELECT
rg.flowcell_barcode, rg.lane, rg.molecular_barcode_name,
count(DISTINCT a.id ) n,
concat_string_csv(DISTINCT a.SAMPLE ) samples,
concat_string_csv(DISTINCT a.id) agg_ids
, concat_string_csv(DISTINCT a.processing_location) proc_loc
FROM metrics.aggregation a ,
metrics.aggregation_read_group rg
WHERE
a.id = rg.aggregation_id
AND a.is_latest <> 0
AND a.SAMPLE IN (
SELECT DISTINCT SAMPLE
FROM metrics.aggregation a
WHERE
a.SAMPLE <> 'NA12878'
AND nvl(a.modified_at, a.workflow_end_date) >= <START DATE FROM ETL dashboard>
AND nvl(a.modified_at, a.workflow_end_date) < <END DATE FROM ETL dashboard>
)
GROUP BY rg.flowcell_barcode, rg.lane, rg.molecular_barcode_name
HAVING count(DISTINCT a.id )>1
) |
Issue: Designation ETL fails with "ORA-30926: unable to get a stable set of rows in the source tables" error
...