retained_earnings_ferc1 transform can't add previous year factoids for XBRL data

[jdangerx]

When using the newly-extracted 2021 and 2022 data, we are getting this error when trying to materialize retained_earnings_ferc1:

ValueError: not enough values to unpack (expected 1, got 0)
...
  File "/Users/dazhong-catalyst/work/pudl/src/pudl/transform/ferc1.py", line 4764, in add_previous_year_factoid
    [missing_year] = [
    ^^^^^^^^^^^^^^

Which seems to be coming from there being no values in earnings_type that match current_year_types:

        current_year_types = [
            "unappropriated_undistributed_subsidiary_earnings",
            "unappropriated_retained_earnings",
        ]

It looks like we expect there to be corresponding columns in the retained_earnings_118_duration SQLite table.

The version from extractor 0.8.3, which is what we use in dev, has it, and has one record with a non-null value:

sqlite> .mode line
sqlite> select * from retained_earnings_118_duration where unappropriated_retained_earnings_contra_primary_account_affected is not null;
                                                                                          index = 0
                                                                                      entity_id = C004936
                                                                                    filing_name = 9f744bfd-6080-47d4-9a2e-08715503961a
                                                                                     start_date = 2021-01-01
                                                                                       end_date = 2021-12-31
                               changes_unappropriated_undistributed_subsidiary_earnings_credits = 
                     equity_in_earnings_of_subsidiary_companies_contra_primary_account_affected = 
transfers_from_unappropriated_undistributed_subsidiary_earnings_contra_primary_account_affected = —
                                             dividends_received_contra_primary_account_affected = 
  appropriated_retained_earnings_including_reserve_amortization_contra_primary_account_affected = 
                               unappropriated_retained_earnings_contra_primary_account_affected = —
                                                                dividends_declared_common_stock = 
                                                            appropriations_of_retained_earnings = 
               unappropriated_undistributed_subsidiary_earnings_contra_primary_account_affected = 
                                                             dividends_declared_preferred_stock = 
                                                        adjustments_to_retained_earnings_credit = 
    appropriated_retained_earnings_amortization_reserve_federal_contra_primary_account_affected = 
                                                                balance_transferred_from_income = 839436.0
                                              retained_earnings_primary_contra_account_affected = 
                                                         adjustments_to_retained_earnings_debit =

but the version from extractor 1.1.1 doesn't have that - here are the records from the same entity ID:

sqlite> select * from retained_earnings_118_duration where entity_id = 'C004936';
                                                                                      entity_id = C004936
                                                                                    filing_name = ITC_Interconnection_LLC_form1_Q4_1679698059
                                                                                     start_date = 2021-01-01
                                                                                       end_date = 2021-12-31
                                                        adjustments_to_retained_earnings_credit = 
                                                                balance_transferred_from_income = 839436.0
                                                                dividends_declared_common_stock = 
                                                         adjustments_to_retained_earnings_debit = 
                                                            appropriations_of_retained_earnings = 
                                                             dividends_declared_preferred_stock = 
                               changes_unappropriated_undistributed_subsidiary_earnings_credits = 
transfers_from_unappropriated_undistributed_subsidiary_earnings_contra_primary_account_affected = 
    appropriated_retained_earnings_amortization_reserve_federal_contra_primary_account_affected = 

                                                                                      entity_id = C004936
                                                                                    filing_name = ITC_Interconnection_LLC_form1_Q4_1679698059
                                                                                     start_date = 2022-01-01
                                                                                       end_date = 2022-12-31
                                                        adjustments_to_retained_earnings_credit = 
                                                                balance_transferred_from_income = 802308.0
                                                                dividends_declared_common_stock = 300000.0
                                                         adjustments_to_retained_earnings_debit = 
                                                            appropriations_of_retained_earnings = 
                                                             dividends_declared_preferred_stock = 
                               changes_unappropriated_undistributed_subsidiary_earnings_credits = 
transfers_from_unappropriated_undistributed_subsidiary_earnings_contra_primary_account_affected = 
    appropriated_retained_earnings_amortization_reserve_federal_contra_primary_account_affected = 

Checking the XBRL schema, it does seem like these missing columns are expected, and that we just aren't including them because there are no actual values, so enforcing that schema might help.

I do also see a lot of UnappropriatedRetainedEarnings facts reported in the XBRL filings, but those are different from UnappropriatedRetainedEarningsContraPrimaryAccount. I see UnappropriatedRetainedEarnings as part of the XBRL taxonomy for the retained earnings table. But, I don't see it in the table schema when we extract it.

[jdangerx]

Enforcing the schema does allow the asset to build, which is good.

The only remaining thing here, then, is figuring out why UnappropriatedRetainedEarnings doesn't appear to be in the table schema for retained_earnings_118_duration or retained_earnings_118_instant...

CREATE TABLE retained_earnings_118_duration (
        entity_id TEXT, 
        filing_name TEXT, 
        start_date TEXT, 
        end_date TEXT, 
        adjustments_to_retained_earnings_debit FLOAT, 
        dividends_declared_common_stock FLOAT, 
        balance_transferred_from_income FLOAT, 
        unappropriated_undistributed_subsidiary_earnings_contra_primary_account_affected FLOAT, 
        transfers_from_unappropriated_undistributed_subsidiary_earnings_contra_primary_account_affected FLOAT, 
        equity_in_earnings_of_subsidiary_companies_contra_primary_account_affected FLOAT, 
        appropriated_retained_earnings_including_reserve_amortization_contra_primary_account_affected FLOAT, 
        appropriations_of_retained_earnings FLOAT, 
        unappropriated_retained_earnings_contra_primary_account_affected FLOAT, 
        appropriated_retained_earnings_amortization_reserve_federal_contra_primary_account_affected FLOAT, 
        changes_unappropriated_undistributed_subsidiary_earnings_credits FLOAT, 
        dividends_received_contra_primary_account_affected FLOAT, 
        dividends_declared_preferred_stock FLOAT, 
        adjustments_to_retained_earnings_credit FLOAT, 
        retained_earnings_primary_contra_account_affected FLOAT
);
CREATE TABLE retained_earnings_118_instant (
        entity_id TEXT, 
        filing_name TEXT, 
        date TEXT, 
        appropriated_retained_earnings_amortization_reserve_federal FLOAT, 
        appropriated_retained_earnings FLOAT
);

[jdangerx]

If the "why is unretained earnings not in the table schema" issue causes problems in the future we will re-open.