search

catalyst-cooperative / pudl

The Public Utility Data Liberation Project provides analysis-ready energy system data to climate advocates, researchers, policymakers, and journalists.
https://catalyst.coop/pudl
MIT License
456 stars 105 forks source link

Scope 2023 FERC 1 data integration #3544

Open aesharpe opened 2 months ago

aesharpe commented 2 months ago

Not all of the FERC data is in, but there's enough to test the extractor and see if there are any major changes to the data format that would beget a refactor

Keep this exploration cursory. Don't try and fix any major errors, just SCOPE them. Check-in after attempt to extract new year of data and then proceed to transformation if fixes are strait forward / RMI gives explicit permission to address them.

Success criteria

### Tasks
- [ ] https://github.com/catalyst-cooperative/pudl-archiver/issues/318
- [ ] https://github.com/catalyst-cooperative/pudl/issues/3575
- [ ] https://github.com/catalyst-cooperative/pudl-archiver/issues/349
- [ ] https://github.com/catalyst-cooperative/pudl/issues/3650
- [ ] https://github.com/catalyst-cooperative/pudl/issues/3698
jdangerx commented 1 month ago

FYI @aesharpe

@zschira and I just spent some time looking into these issues - broadly we see three areas of changes: archival, extraction, and downstream transforms.

archival

These changes enable the extractor changes below, which then will fix the issues found in #3575.

We expect this to take roughly 10h.

extraction

These changes are a little more entangled with the rest of the system - we expect them to take about 15h. Combined with the archiver changes above, this should address all the issues found in #3575 .

transformation

This is the first time we are encountering a new schema for the FERC1 data - which means the transformations will likely need various fixes to get them running again, in addition to the normal utility mapping, validation tests/data investigation, etc.

Last year, according to Harvest, it took us 240 hours to handle all of the 2022 data - probably about half of that dedicated to FERC (looks like I put 90h or so into it).

It was easier, since we didn't have a new schema to deal with, but also harder, in that it was the first time dealing with multiple years of XBRL data. I would be comfortable budgeting for 120 hours of time to integrate the new FERC data, though I know that sounds like a huge number and I really hope it takes less time.

Overall that means 145 hours of work.

I think we should aim to complete this in June or July - May is already quite booked. As of right now, we see 221 filings for 2023 in the latest archive, which is 85% of the number of filings for 2022. So I do also expect some more filings to trickle in over the next month or so.

cmgosnell commented 1 month ago

hey @jdangerx when i was trying to compile the general pudl maintenance budget (an internal doc for now!) i looked into the 2022 update and the ferc hours a bit. I pulled out all of the time entries that had either "ferc" or "xbrl" in there and it was just under 60 hours. It included the saving of a bunch of recrds we had previously been dropping during the archiving... which was hopefully a one-off but actually seems pretty representative for FERC weirdness. So I think this estimate is high!

jdangerx commented 1 month ago

I went through and looked at each of the time entries under software development or data wrangling for the FERC 2022 project - there was a bunch of stuff that didn't explicitly have the words FERC or XBRL that was related to one of those things. Fortunately most of the work was done by me so I understand the cryptic notes I left 😅

It all sums to:

Dealing with utility mapping, FERC transforms, investigating why validation tests were failing and whether that's a real problem: 64h

Dealing with the duplicate data issue from having multiple filings from different years representing the same data: 19h

Additionally Austen looks like she spent about 3h on FERC stuff. So that all comes out to 86h.

Intuitively, a new data schema seems like a big source of uncertainty, I think we should have some buffer above "what we did last year." 120 does still sound high but not outrageous.

To get a sense of how much the schemas really changed, I could probably pull like an hour to:

That would probably be a good investment of our scoping time.

jdangerx commented 1 month ago

There are a total of 10 tables which appear to have some sort of schema change between the 2022-01-01, 2023-04-01, and 2023-11-01 FERC 1 taxonomies.

None of these tables appear to be actively used in our current FERC1 ETL - I pulled the list of used tables from pudl.extract.ferc1.TABLE_NAME_MAP_FERC1. That means I don't expect to have to make big changes to the transform code to handle the new year's data.

We will, however, still have to investigate validation errors for the new data, do the utility/plant mapping, etc. Which will probably take 30-40h depending on how thoroughly we want to investigate the validation errors.

Here's the rundown of the schema changes:

Changes from adding energy storage operations data for small plants

Removing electricity sales by rate schedules in C&I in 2023-04