Versions Compared

Version Old Version 13 New Version 14
Changes made by

Rafael Sanchez (Deactivated)

Rafael Sanchez (Deactivated)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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. 


Synchronizing data types between metrics tables and Agg DM

Issue: Project sample records may not have the same data type in the Sample and Library level aggregation tables (slxre2_pagg_sample and slxre2_pagg_library). 
Cause:

Remedy:

  1. Compare the data types in Sample and Library level aggregations:


    Code Block
    languagesql
    SELECT  sa.data_type, a.*
FROM metrics.aggregation a ,
slxre2_pagg_sample sa 
WHERE
a.id = sa.analysis_id
AND  (a.project, a.SAMPLE) IN ( 
('P', 'S'),
...
)
AND a.library IS NULL 
AND a.is_latest <>0
;

SELECT  sa.data_type, a.*
FROM metrics.aggregation a ,
slxre2_pagg_library sa 
WHERE
a.id = sa.analysis_id
AND  (a.project, a.SAMPLE) IN ( 
('P', 'S'),
...
)
AND a.library IS NOT  NULL 
AND a.is_latest <>0
;


  2. Update Analytics DMs

    Code Block
    languagesql
    MERGE INTO slxre2_pagg_sample sa 
USING (
    SELECT a.id, a.data_type 
    FROM metrics.aggregation a
    WHERE (a.project, a.SAMPLE) IN ( 
        ('P', 'S'),
        ...
        )
    AND a.library IS NULL 
    AND a.is_latest <>0

) d 
ON (sa.analysis_id = d.id)
WHEN MATCHED THEN UPDATE SET 
sa.data_type = d.data_type 
;
MERGE INTO slxre2_pagg_library sa 
USING (
    SELECT a.id, a.data_type 
    FROM metrics.aggregation a
    WHERE (a.project, a.SAMPLE) IN ( 
        ('P', 'S'),
        ...
        )
    AND a.library IS NOT NULL 
    AND a.is_latest <>0

) d
ON (sa.analysis_id = d.id)
WHEN MATCHED THEN UPDATE SET 
sa.data_type = d.data_type 
;
MERGE INTO slxre2_library_agg_qc sa 
USING (
    SELECT a.id, a.data_type 
    FROM metrics.aggregation a
    WHERE (a.project, a.SAMPLE) IN ( 
        ('P', 'S'),
        ...
        )
    AND a.library IS NOT NULL 
    AND a.is_latest <>0

) d
ON (sa.analysis_id = d.id)
WHEN MATCHED THEN UPDATE SET 
sa.data_type = d.data_type 
;
MERGE INTO slxre2_sample_agg_qc sa 
USING (
    SELECT a.id, a.data_type 
    FROM metrics.aggregation a
    WHERE (a.project, a.SAMPLE) IN ( 
        ('P', 'S'),
        ...
        )
    AND a.library IS NOT NULL 
    AND a.is_latest <>0

) d
ON (sa.analysis_id = d.id)
WHEN MATCHED THEN UPDATE SET 
sa.data_type = d.data_type 
;


  3. Review queries in step 1 to confirm data_type changes.