Make quicker quarterly updates to PUDL EPA CEMS, EIA 923 and EIA 860 data

[e-belfer]

Description

With the support of RMI, the goal of the project is to make it possible to integrate quarterly updates for CEMS, EIA 923M and EIA 860M data within 1-2 weeks of new data release. To do this, we are going to 1) automate archiving of new data, with the support of additional data validation checks on our archiver, 2) redesign PUDL to handle quarterly and monthly data formats and 3) integrate YTD data to test it all.

Archiver infrastructure updates

Goal: Generate robust report of archiver results to enable quick (<.25 hour per dataset) manual review and approval of draft production archives run by Github action.

Current state: Currently, archive runs check for 1) missing files, 2) valid file types, 3) emptiness of zips (in progress) and produce a summary of all changed files. The new default behavior of the ‘auto-publish’ flag allows for the production of a draft production archive in Zenodo for manual approval, removing the need for sandbox runs of an archiver that is known to work. @zschira has seriously improved the archiver’s robustness to large file uploads but this is always a trouble spot and we should anticipate some time required to handle things that come up.

Tasks

Preview Give feedback

1. Run archiver for EIA 860, EIA 860m, EIA 923, and EPA CEMS on an automated schedule #3066
   +0
2. GHA should notify #pudl-deployments on archiver run success/failure #3067
   0 of 2
   +0
3. Point Github action at production archives. #3068
   0 of 1
   +0
   
4. Identify and implement 1-2 additional generic validations for all datasets to help us catch remaining gaps (e.g. size of all files hasn’t changed by more than 10%). pudl-archiver#217
   +0
   
5. Write down manual process for archive approval #3119
   0 of 1
   +0
   
6. Ensure continuous data availability for CEMS and 860M archives pudl-archiver#218
   0 of 2
   +0
   

Non-coding tasks required (likely RMI tasks):

• Identify expected frequency of dataset releases in order to schedule automated archiving
• Identify any known dataset quirks relevant to the archiving process (e.g., 2015 Q1 CEMS data is mislabelled).

Handle monthly data in PUDL

Goal: Design a mechanism to handle monthly data in a system that is designed for annual data. Make structural changes required for each dataset to make this possible, designating new data as YTD data and excluding it from annually aggregated tables.

Current state:
EIA 860M data is ‘annual’ in nature and already appended on to EIA 860 data. No changes required here.
EIA 923M data has the same format as EIA 923 data, with slightly fewer ‘pages’ (Schedules 2-5 only, which means no emission control table). The column names and layout are the same, with YTD data and blank rows for the months not yet covered.
CEMS is currently downloaded by year-state and will need to be downloaded by quarter instead (one file per quarter, ~2-3Gb per file).

Tasks

Preview Give feedback

1. CEMS: Rewrite and test archiver to pull quarterly files rather than state-year #2929
   +0
   
2. CEMS: Repartition extraction process and parquet files. #2973
   +0
   
3. EIA923M: Add data maturity flag and 2023 quarterly data #2930
   eia923 rmi +2
   
4. Reconfigure EIA 860M archive to meet new Zenodo file number restrictions pudl-archiver#190
   zenodo +1
   
5. CEMS: Retool extraction to handle listed partitions #3185
   +0
   
6. EIA 860M: Retool extraction to handle listed partitions #3186
   +0
   

Doc updates

Goal: make it easier for external contributors to make progress on the annual updates
Current status: Annual doc updates are relatively up to date but require additional elaboration for a few steps.

Tasks

Preview Give feedback

1. Update documentation about how to integrate new data updates #3130
   docs +1
   
2. Make issue template for adding new year of data #3116
   new-data rmi +2
   

Integrate YTD data

Goal: test new infrastructure on YTD data (anticipated Q3 2023).

Tasks

Preview Give feedback

1. EIA 860: Quarterly work of mapping files, tabs and columns. Running new data through ETL and debugging any errors. Add any new plants and utilities into plant ID generator. Data validation.
2. Integrate newest monthly 923 data #3010
   +0
3. CEMS: Quarterly work of running new data through ETL and debugging any errors. Add any missing plant timezones. Data validation.
4. Integrate September Monthly EIA923 Data #3101
   eia923 new-data rmi +3
   

If time, but not essential to project success

• Explicitly specify expected missing partitions for ingested datasets (e.g. no HI data in 2013).
• Write a suite of tests to characterize changes in ingested data at the end of the ETL (data coverage, number of entities, other metrics of completion) and output as report. Will speed up our process of knowing how ‘complete’ monthly data is and how revisions affect data.

Future projects that could complement this work

• Remaining tasks in Improve and Automate raw data archiving/access #1418
• Automating an attempted PUDL ETL on new data (when new data archive released, produce a PR automatically attempting to run it through the ETL to streamline integration fixes).

[e-belfer]

With the merging of #3185 and #3186, this issue is complete. Further ongoing quarterly updates will be tracked in individual issues.