compliance datasets do not include data for 2022 - Production Issue

[hamezene]

I was checking on bulk data files since we are expecting the transaction datasets to be updated with 2023 data next week, but noticed that the following compliance datasets do not include data for 2022 but they should. Can you look into this? I'm hoping this is just a database issue that can be resolved without a code change. The APIs/Custom Data Download are working fine and have the data. This is a production issue. Thanks!

CSOSG1

CSOSG2

CSOSG3

CSNOX

CSSO2G1

CSSO2G2

TXSO2

[djw4erg]

Bulk Files API Information:

Repository: easey-camd-services repository Swagger Page: camd-services Swagger page

[djw4erg]

Updated Information:

I asked Mark about the unfamiliar Bulk Data File process and he indicated the following:

• The Bulk Data Files are updated by a Quartz job. Mark, also determined that the job for compliance files runs the 15th of every month. The key file for the Quartz job is easey-quartz-scheduler/.../AllowanceComplianceBulkDataFiles.cs.
• For the compliance files, the job indicates what runs using the CAMDAUX.VW_ALLOWANCE_BASED_COMPLIANCE_BULK_FILES_TO_GENERATE view, which contains a bug.
• The bug essentially has prevented the update of compliance files, particularly those that should have been produced based on June updates.

The View Bug: The view compares the Last Update Dates in the ACCOUNT_COMPLIANCE_DIM and ACCOUNT_FACT tables to the current timestamp for the database. Using the current timestamp is wrong. The timestamp should be the begin time for the last time the specific Quartz job successfully ran.

Proposed Course:

• Produce a temporary version of the view designed to "catch-up" with missing compliance files.
• Produce a permanent version of the view that corrects the bug.
• Determine how to force the Quartz job to run. Is there an accessible page?
• Implement the temporary view and force the Quartz job to run.
• Implement the permanent view.

[djw4erg]

Additional Issue: easey-quartz-scheduler/.../AllowanceComplianceBulkDataFiles.cs references 'Emissions Compliance' instead of 'Allowance Compliance' at line 72.

[maheese]

@djw4erg I was looking at the runbook and it does address database deployments in the context of a code release. Can we get the approach we discussed this morning for just database updates updated in the database deployments section of the runbook https://github.com/US-EPA-CAMD/easey-docs/blob/develop/CAMDRunbook.md#93-deployments as part of this issue.

[ergjustin]

quartz-scheduler bug fix PR : https://github.com/US-EPA-CAMD/easey-quartz-scheduler/pull/165

[djw4erg]

Updates for View Changes:

View: camdaux.vw_allowance_based_compliance_bulk_files_to_generate C# Class: BulkDataFileMaintenance class in EASEY-QUARTZ-SCHEDULER repository in //admin/Job/BulkDataFilesMaintenance.cs.

• Use the most resent START_DATE in JOB_LOG where JOB_SYSTEM is "Quartz", JOB_CLASS is "Bulk Data File", and JOB_NAME is "Allowance Compliance" as the comparison timestamp in the view instead of CURRENT_TIMESTAMP.
• Default comparison time to CURRENT_TIMESTAMP - 90 if a JOB_LOG row does not exist..
• Change the SQL to remove JOB_LOG rows in the BulkDataFileMaintenance class in EASEY-QUARTZ-SCHEDULER > admin > Jobs > BulkDataFilesMaintenance.cs to only delete rows older than 90 days instead of 30 days.

Notes:

• The view changes will allow the view to return updates that occurred since the last run of the job.
• The class change will prevent delete JOB_LOG records needed by the view.

[ergjustin]

db-script PR: https://github.com/US-EPA-CAMD/easey-db-scripts/pull/14

[djw4erg]

Follow-up Issues

1. ACCOUNT_COMPLI(ANCE_DIM LAST_UPDATED_DATE is null for every row in Dev. What updates this date?