More general explanations of the overall categories of the tables

[JesseWeinstein]

I'd love to see a few paragraphs at the start of the documentation that provide more general explanations of the overall categories of the tables, and what are relevant places to start.

i.e. Which tables (if any) include mailing/physical addresses for stuff (and what stuff)? Which tables have most-recent-date data vs which ones contain historical data (or is this arranged differently)? What are the different types of entities discussed in the different tables (utility companies, power plants, generators, smokestacks, other?)?

[JesseWeinstein]

@bendnorman answered (on the OpenStreetMap US Slack team, where I originally asked this):

plants_entity_eia contains location data for plants. Entity tables contain static information that don’t change year to year. For example the state a plant is in shouldn’t change! Tables that have report_date and report_year columns have data that change yearly or monthly. For example, the generation_fuel_eia923 table contains monthly net generation and fuel consumption information. All of the PUDL data is current with the raw data from FERC and EIA. You can view the years available for each datasource on the data sources page

[JesseWeinstein]

It looks like there are 4 "entity_eia" tables: boilers, generators, plants and utilities. Boilers are in plants (linked by the boilers_entity_eia table) as are generators (linked by the generators_entity_eia table).

@bendnorman explained how plants and utilities are linked:

Plants are linked to utilities in the utilities_entity_eia table via the plants_eia860 table. The utility_id_eia column is in plant_eia860 instead of plants_entity_eia because a plant’s utility can change.

It looks like utilities are also linked directly to generators via the generators_eia860 table, and to "plant parts" the via plant_parts_eia table.

[JesseWeinstein]

It might be good to organize the tables by their suffix, which I've done here:

('accounts', 'ferc_accounts'),
('assn', 'utility_plant_assn'),
('datasources', 'datasources'),
('dbf', 'utilities_ferc1_dbf'),

('eia', 'balancing_authorities_eia'),
('eia', 'boiler_generator_assn_types_eia'),
('eia', 'boilers_entity_eia'),
('eia', 'coalmine_types_eia'),
('eia', 'contract_types_eia'),
('eia', 'energy_sources_eia'),
('eia', 'epacamd_eia'),
('eia', 'fuel_receipts_costs_aggs_eia'),
('eia', 'fuel_transportation_modes_eia'),
('eia', 'fuel_types_aer_eia'),
('eia', 'generators_entity_eia'),
('eia', 'momentary_interruptions_eia'),
('eia', 'operational_status_eia'),
('eia', 'plants_eia'),
('eia', 'plants_entity_eia'),
('eia', 'prime_movers_eia'),
('eia', 'reporting_frequencies_eia'),
('eia', 'sector_consolidated_eia'),
('eia', 'steam_plant_types_eia'),
('eia', 'utilities_eia'),
('eia', 'utilities_entity_eia'),

('eia860', 'boiler_generator_assn_eia860'),
('eia860', 'generators_eia860'),
('eia860', 'ownership_eia860'),
('eia860', 'plants_eia860'),
('eia860', 'utilities_eia860'),

('eia923', 'boiler_fuel_eia923'),
('eia923', 'coalmine_eia923'),
('eia923', 'fuel_receipts_costs_eia923'),
('eia923', 'generation_eia923'),
('eia923', 'generation_fuel_eia923'),
('eia923', 'generation_fuel_nuclear_eia923'),

('ferc1', 'balance_sheet_assets_ferc1'),
('ferc1', 'balance_sheet_liabilities_ferc1'),
('ferc1', 'cash_flow_ferc1'),
('ferc1', 'depreciation_amortization_summary_ferc1'),
('ferc1', 'electric_energy_dispositions_ferc1'),
('ferc1', 'electric_energy_sources_ferc1'),
('ferc1', 'electric_operating_revenues_ferc1'),
('ferc1', 'electric_opex_ferc1'),
('ferc1', 'electric_plant_depreciation_changes_ferc1'),
('ferc1', 'electric_plant_depreciation_functional_ferc1'),
('ferc1', 'fuel_ferc1'),
('ferc1', 'income_statement_ferc1'),
('ferc1', 'other_regulatory_liabilities_ferc1'),
('ferc1', 'plant_in_service_ferc1'),
('ferc1', 'plants_ferc1'),
('ferc1', 'plants_hydro_ferc1'),
('ferc1', 'plants_pumped_storage_ferc1'),
('ferc1', 'plants_small_ferc1'),
('ferc1', 'plants_steam_ferc1'),
('ferc1', 'power_purchase_types_ferc1'),
('ferc1', 'purchased_power_ferc1'),
('ferc1', 'retained_earnings_ferc1'),
('ferc1', 'transmission_statistics_ferc1'),
('ferc1', 'utilities_ferc1'),
('ferc1', 'utility_plant_summary_ferc1'),

('maturities', 'data_maturities'),
('pudl', 'plants_pudl'),
('pudl', 'utilities_pudl'),
('subdivisions', 'political_subdivisions'),
('xbrl', 'utilities_ferc1_xbrl')

[JesseWeinstein]

Most of the tables whose name ends with _eia (as opposed to _eia860 or _eia923) appear to be coding tables (i.e. providing descriptions of short codes used elsewhere):

balancing_authorities_eia
boiler_generator_assn_types_eia
coalmine_types_eia
contract_types_eia
energy_sources_eia
fuel_transportation_modes_eia
fuel_types_aer_eia
momentary_interruptions_eia
operational_status_eia
prime_movers_eia
reporting_frequencies_eia
sector_consolidated_eia
steam_plant_types_eia

Then there's a confusing one: epacamd_eia that isn't year-based, and seems to connect to the EPA data, but not a way I fully understand yet.The only year-based (i.e. include report_date field) eia table appears to be:

fuel_receipts_costs_aggs_eia

The plant_parts_eia table seems to be very important. It has lots of foreign keys, and is described as an "output table". It should probably be highlighted in documentation... but it also doesn't exist. 😮And there are the plants and utilities tables linking them to pudl_ids and the entity tables.
That covers all the _eia suffixed tables.

[JesseWeinstein]

All 5 _eia860 suffixed tables are year-based.

coalmine_eia923 is, oddly, not year-based. The other 5 _eia923 tables are date-based, but seem to be monthly, not year-based.

There's one coding table among the ferc1 tables: power_purchase_types_ferc1

[JesseWeinstein]

@bendnorman clarified some of the tables:

The utility_plant_assn table links the FERC and EIA data together. The epacamd_eia links the EIA data with the hourley_emissions_epacems data. The emissions data is too large for sqlite so we distribute it as a parquet file. You can read about how the EIA and EPA data is linked together here.

You can download the full parquet file from an s3 bucket. If you’re comfortable with python you can also play with all of this data in a hosted jupyter notebook. You can request access here.

[bendnorman]

The plant_parts_list is a complex "output" table that is used to associate parts of plants like units and generators between FERC and EIA data. The output tables are wide tables that join some of our normalized tables together to make the data more usable. For example, the plants_eia860 output table joins the plants_entity_eia, plants_eia860, plants_eia, utilities_eia together so all of the static and annual plant information and pudl ids can be access in one table.

Unfortunately, output tables are only available via the pudl.output.pudltabl.PudlTabl class. You can visit the pudl-examples repo to see how to use the PudlTabl class. You can also request an account on our hosted jupyter lab with pudl already installed.

We're planning on moving a lot of these output tables into the database so they can be accessed by downloading pudl.sqlite or the datasette UI #1973. This will remove the python / jupyter lab barrier for interacting with the more user-friendly output tables.

[JesseWeinstein]

plants_entity_eia is the only table containing latitude/longitude values.

[zaneselvans]

Plants are the only entities that have a well-defined point location associated with them, though boilers and generators are subsumed within plants, so they can have the same lat/lon as their parent plant.

We also have annual, county-level geometries of the utility and balancing authority service territories, from like 2006 to 2020 (we haven't updated it for 2021 yet I don't think), based on data in the EIA-861.

There will also be location data for mines from the MSHA data, which we're finally started archiving. See #209.

[bendnorman]

Thanks for all of the questions and feedback @JesseWeinstein! Our table and column descriptions could probably be more verbose. We have an open PR #1910 that improves some of our table-level descriptions. @zaneselvans when do you think we should revisit this PR?

[zaneselvans]

I mean, every table should have a verbose description documenting what it's all about. We just have to set aside the time to fill them in (and someone who knows about the guts of the tables).

@JesseWeinstein the plant_parts_eia table has metadata because we use the same type-enforcement tooling on it as on the real DB tables, but right now it's compiled on the fly (as are many other derived tables) in software, and not written into the DB. However, we are in the process of migrating all those denormalized and derived value outputs into the DB. See #1973. The goal is to have that transition done by the end of 2023-Q1.

[aesharpe]

Hey! Chiming in -- There are a few levels of categorization to consider here. I think we want to organize by SOURCE (as seen in pudl.metadata.sources.py) but also by etl_group. To my knowledge, etl_group is defined as a parameter to the Resource class, and the potential values are stipulated as literals right in the function. There is no other place, however, where these groups are defined or given any meaning. Maybe we should consider making this list into something more concrete so that we can use it to distinguish between the tables.

    etl_group: Literal[
        "eia860",
        "eia861",
        "eia923",
        "entity_eia",
        "epacems",
        "ferc1",
        "ferc1_disabled",
        "ferc714",
        "glue",
        "outputs",
        "static_ferc1",
        "static_eia",
        "static_eia_disabled",
        "eia_bulk_elec",
        "static_pudl",
    ] = None

Checkout my draft PR #2336