Introduction¶

The Data Quality Assessment Plan (LSE-63) defines four levels of QA summarized here:

• Level 0 QA: test the DM software during pre-commissioning as well as test software improvements during commissioning and operations, quantifying the software performance against known expected outputs.
• Level 1 QA: data quality in real time during commissioning and operations. Analyzes the data stream in real time, information about observing conditions (sky brightness, transparency, seeing, magnitude limit) as well as characterize subtle deterioration in system performance.
• Level 2 QA: assesses the quality of data releases (including the co-added image data products) performing quality assessment for astrometric and photometric calibration and derived products, looking for problems with the image processing pipelines and systematic problems with the instrument.
• Level 3 QA: data quality based on science analysis performed by the LSST Science Collaborations and the community. Level 0-2 visualization and data exploration tools will be made available to the community.

During pre-commissioning, the LSST DM Software and its data products are being evaluated by processing precursor datasets from surveys like DECam, CFHT and HSC. These verification datasets were selected to span a wide range of regimes and to test Level 1 (Alert Production) and Level 2 (Data Release Production) data products.

SQuaRE is concerned about preserving the knowledge developed in this effort and reuse as much as possible the code in the production Data Quality Assessment Framework (DQAF), possibly at all levels of QA.

In practice, that can be achieved by developing a prototype of the DQAF that would be adopted, maintained and refined by the verification scientists. At the same time, they have similar needs in terms of data access, data processing and visualization and would benefit from a common infrastructure that facilitates all those aspects.

The verification datasets use case has specific needs that must be distinguished from the production system. Since we are processing multiple datasets, it makes sense to accumulate QA information across multiple runs and compare the results later. That motivates an unified database model capable of storing QA results for different cameras. However,in production, each run will result in a different database with associated QA tables (http://ldm-135.readthedocs.org/). Despite those differences, the end goal of a prototype at this point is to leverage the design of the production QA database.

Single Image Requirements¶

LPM-17 is the basis for the present design, it specifies single image requirements and how to select the samples to compute them. In this section we summarize the single image requirements and discuss the sample selection based on the outputs available in the baseline database schema (https://lsst-web.ncsa.illinois.edu/schema/index.php?t=RefObjMatch&sVer=baseline) as specified by LSE-163.

The samples for computing Image Depth, Image Quality, Astrometry and Photometry accuracy requirements should be create as views in the production database for convenience.

TODO: include tables summarizing the single image requirements in LPM-17

TODO: write queries to select datasets for image depth, image quality, astrometry and photometry accuracy based on LPM-17 from the baseline database schema and double check with LSE-163

TODO: if we don’t have the ingestion code ready for the prototype development (https://jira.lsstcorp.org/browse/DM-4811) how we are going to proceed?

SELECT visitId,
       ccdVisitId,
       psFlux,
       psFluxSigma
FROM Source s
INNER JOIN CcdVisit c ON s.ccdVisitId=c.ccdVisitId
INNER JOIN Visit v ON c.visitId=v.visitId
WHERE v.filterName='r'
AND c.seeing < 0.7
AND s.skySigma > 21
AND v.airmass < 1.2
AND v.expTime*v.nExposures = 30
AND s.flags=0;

The QA database¶

The database is being designed according to some general guidelines:

• Should store the results of the science requirements and summary information for CCDs and Visits;
• Should be easily extended, i.e additional tests should be included without changing the schema;
• Should be optimized for interactive visualization, i.e the results of the science requirements and summary information

are pre-computed and stored in ‘materialized views’ for performance improvements; - Must be camera agnostic, i.e support DECam, CFHT and HSC images processed by the stack;

The main difference between the verification datasets use case and the production system is that here the QA results from multiple runs will be accumulated in a single database, including basic provenance information. Thus, in addition to the science requirements and summary information table we also have process information tables.

Figure 1 QA database model.

The source code is maintained in this repo https://github.com/lsst-sqre/qa-database

The proposed database has three sets of tables:

Science Requirements

Are based on the single image specification contained in the Science Requirements Document (LPM-17) and associated to each single ccd. Science Requirement descriptions, results, conditions and thresholds are stored in the metrics table.

Summary Information

They store medians and MADs (Median Absolute Deviations) of interesting properties of each CCD, it enables fast visualization and aggregate quantities computed at the visit level. These properties are computed from a subset of high S/N point sources also stored in the database.

Process information

They store basic provenance information such as configuration, data repository path, code version and logs of each process. If the full image and source catalogs are required for futher inspection they can be retrieved from the data repository using the butler.

It is also being designed to be a common model for the different instruments supported by the stack. An advantage of that is the comparison of metrics and results accross different processes of the same dataset or accross different datasets. The mechanism for translating camera-specific metadata to this common model is still under discussion.

Sample queries¶

SELECT d.name,
    p.processId,
    p.start,
    p.end - p.start as duration,
    p.status,
    u.username
FROM Process p
INNER JOIN Dataset d ON p.datasetId = d.datasetId
INNER JOIN user u ON p.userId = u.userId;

SELECT ptv.nFailure/(ptv.nSuccess + ptv.nFailure) as fraction
FROM ProcessToVisit ptv
INNER JOIN Process p ON ptv.processId=p.processId
WHERE processId = 'xxxx';

SELECT visit
FROM Visit v
INNER JOIN ProcessToVisit ptv ON v.visitId = ptv.visitId
INNER JOIN Process p ON ptv.runId = p.runId
WHERE ptv.nFailure > 0 AND p.processId = 'xxxx';

SELECT c.llra,
   c.lldec,
   c.urra,
   c.urdec
FROM Ccd c
INNER JOIN Visit v ON c.visitId = v.visitId
INNER JOIN ProcessToVisit ptv ON v.visitId = ptv.visitId
INNER JOIN Process p ON ptv.processId = p.processId
WHERE p.processId = 'xxxx';

SELECT p.config,
    p.stackVersion
FROM Visit v,
INNER JOIN ProcessToVisit ptv ON v.visitId = ptv.visitId
INNER JOIN Process p ON ptv.processId = p.processId
WHERE v.visit = 'yyyy'
AND p.processId = 'xxxx';

SELECT v.filter,
    v.exposureTime,
    v.zenithDistance,
    v.airMass,
    v.hourAngle,
    c.medianFwhm,
    1.0-c.medianMinorAxis/c.medianMajorAxis as ellipticity,
    c.medianSkyBg,
    c.medianScatterRa,
    c.medianScatterDecl
FROM Visit v,
    Ccd c
WHERE v.visitId = c.visitId
AND v.visit = 'yyyy';

SELECT v.visit,
   v.filter,
   v.exposureTime,
   v.zenithDistance,
   v.airMass,
   v.hourAngle,
   scisql_median(c.medianFwhm) as fwhm,
   scisql_median(1.0-c.medianMinorAxis/c.medianMajorAxis) as ellipticity,
   scisql_median(c.medianSkyBg) as skyBg,
   scisql_median(c.medianScatterRa) as scatterRa,
   scisql_median(c.medianScatterDecl) as scatterDecl
FROM Ccd c
INNER JOIN Visit v ON c.visitId = v.visitId
INNER JOIN ProcessToVisit ptv ON v.visitId = ptv.visitId
INNER JOIN Process p ON ptv.processId = p.processId where ProcessId = 'xxxx'
GROUP BY v.visit,
     v.filter,
     v.exposureTime,
     v.zenithDistanced,
     v.airMass,
     v.hourAngle
ORDER BY v.visit;

SELECT c.log
FROM Visit v,
    Ccd c
WHERE v.visitId = c.visitId
AND v.visit = 'yyyy'
AND c.status = 1;

SELECT p.processId as run,
    v.visit,
    scisql_median(c.medianScatterRa) as ra_scatter,
    scisql_median(c.medianScatterDecl) as dec_scatter,
    p.stackVersion,
    p.config
FROM Ccd c
    INNER JOIN Visit ON c.visitId = v.visitId
    INNER JOIN ProcessToVisit ptv ON v.visitId = ptv.visitId
    INNER JOIN Process p ON ptv.processId = p.processId
    INNER JOIN dataset d ON p.datasetId = d.datasetId
WHERE d.name = 'zzzz'
AND p.start > 'yyy-mm-dd'
GROUP BY v.visit
ORDER BY p.processId;

SELECT p.processId as run,
   d.name as dataset,
   v.visit,
   scisql_median(c.medianFwhm) as fwhm,
   scisql_median(c.madFwhm) as scatter
FROM Ccd c
INNER JOIN Visit ON c.visit_id = v.visit_id
INNER JOIN ProcessToVisit ptv ON v.visitId = rv.visitId
INNER JOIN Process p ON ptv.processId = p.processId
INNER JOIN Dataset d ON p.datasetId = d.datasetId
WHERE d.camera = 'decam'
AND p.start > 'yyyy-mm-dd'
AND p.end < 'yyyy-mm-dd'
GROUP BY v.visit
ORDER BY p.processId;