DOCSEARCHbyAlgolia
Submit Docs

PUDL (Public Utility Data Linkage)

repository·main·Indexed 20 days ago

https://github.com/catalyst-cooperative/pudl

An open data processing pipeline for US energy data. PUDL provides tools and notebooks for processing, analyzing, and modeling public utility data, including workflows for validating Excel column mappings, debugging EIA ETL processes via Dagster assets, and managing FERC-EIA manual mapping spreadsheets.

Tokens
55.7K
Snippets
129
Records
243
Agent score
70%

What's inside PUDL

  1. Overview of the PUDL Project

    main
    PUDL (Public Utility Data Liberation Project) is a data processing pipeline that cleans, integrates, and standardizes widely used US public energy datasets. It is designed to make complex, semi-structured government data accessible for researchers, activists, and policymakers by providing standardized, machine-readable versions (primarily Parquet files and SQLite databases).

  2. Overview of PUDL data validation

    main

    PUDL uses dbt for data validation. In this setup, every validation test is a SQL query designed to select rows that fail the test; a successful test returns no results.

    Validation relies on:

    • dbt built-in data tests.
    • The dbt_expectations package.
    • Custom data tests defined within PUDL.

    Validation Standards:

    • Full ETL vs. Fast ETL: All tests should pass on both outputs, except for row count checks, which only apply to Full ETL outputs.
    • Row Counts: All tables should have row count checks unless they have a non-deterministic number of rows. Non-deterministic tables should use expect_table_row_count_to_be_between.
    • Null Columns: Full ETL outputs should not have entirely null columns. For Fast ETL outputs containing deprecated columns, per-year nullness expectations should be added using the pudl.scripts.pudl_null_cols script.

  3. Asset Naming Conventions in PUDL

    main

    PUDL uses Dagster assets organized into three processing layers: Raw, Core, and Output. Asset names are used as table or file names and follow a specific hierarchical pattern to indicate their processing stage and origin.

    General Pattern

    {layer}_{source}__{asset_type}_{asset_name}

    • layer: The processing stage (raw, core, or out).
    • source: An abbreviation of the data source (e.g., eia860, ferc1).
    • asset_type: Describes how the asset is modeled (e.g., assn, entity, scd).
    • asset_name: Describes the entity, categorical code type, or measurement.

    Layer-Specific Conventions

    Raw Layer

    Contains direct extracts from source data, typically persisted as pickle files.

    • Pattern: raw_{source}__{asset_name}
    • Note: asset_type is omitted here as data is not yet modeled to PUDL standards.

    Core Layer

    Contains well-modeled, denormalized tables (Tidy Data) that serve as building blocks. Stored as parquet or database tables.

    • Pattern: core_{source}__{asset_type}_{asset_name}
    • Note: source may be pudl if the asset is a derived connection created by PUDL contributors.

    Output Layer

    Contains wide, complete tables designed for end-user analysis.

    • Pattern: out_{source}__{asset_type}_{asset_name}
    • Note: source and asset_type are optional in this layer.

    Intermediate Assets

    Logical steps within a process that are not intended for user access or persistence. These are denoted by a preceding underscore (e.g., _core_eia860__plants).

    {layer}_{source}__{asset_type}_{asset_name}

  4. LLM usage policy for PUDL contributions

    main

    PUDL allows the use of Large Language Models (LLMs) under the following conditions:

    • Human Review is Mandatory: All PRs (internal and external) are reviewed by humans. Code must be optimized for human readability and synthesis.
    • Disclose LLM Usage: If you use an LLM to generate content (e.g., tests or implementation plans), you must briefly describe how it was used in the PR description (e.g., "I used an LLM to generate these tests, and cleaned them up by hand").
    • No Vendor Lock-in: Any additions to Catalyst LLM tooling will be rejected if they require a specific proprietary model or harness. The project prioritizes open tooling and openly licensed models.

  5. Understand PUDL Dagster abstractions

    main

    PUDL uses Dagster for data orchestration. The following core abstractions are used:

    • Definitions: The top-level collection of Dagster objects (assets, jobs, resources, etc.). In PUDL, pudl.dagster is the canonical assembly, while pudl.definitions is the stable entry point for the dg CLI.
    • Assets: The primary building blocks representing data entities (e.g., database tables or Parquet files). Most assets in PUDL are pandas.DataFrame objects.
    • Resources: Objects providing access to external systems. Key PUDL resources include pudl.workspace.datastore.Datastore (for Zenodo data), pudl.workspace.datastore.ZenodoDoiSettings, and pudl.dagster.resources.GlobalDataConfigResource.
    • IO Managers: Handle the separation of processing logic from data persistence. PUDL uses managers for FERC SQLite databases, Parquet files, and SQLite files. pudl.dagster.io_managers.PudlMixedFormatIOManager provides a single interface for both SQLite and Parquet.
    • Jobs: Preconfigured collections of assets, resources, and IO Managers. Major jobs include:
      • ferc_to_sqlite: Rebuilds raw FERC prerequisite databases.
      • pudl: Runs the main ETL (requires raw FERC databases).
      • pudl_with_ferc_to_sqlite: Full end-to-end build.
      • ferceqr: DuckDB-based pipeline for large FERC EQR datasets.
    • Configs: Runtime settings passed to jobs/assets, typically stored in YAML files like dg_fast.yml or dg_full.yml.

  6. Provide Complete and Continuous Time Series

    main

    Most PUDL data consists of time series (hourly to annual). To ensure compatibility with time series analysis tools, follow these requirements:

    • Contiguous Time Series: Provide continuous ranges of time. Avoid gaps (e.g., providing 2009-2010 and 2016-2018 while skipping 2011-2015).
    • Complete Time Series: Ensure all timesteps are present in the series. If a value is missing for a specific timestep, provide it as NA rather than omitting the row entirely.

  7. Use subsidiary_company_id_sec10k for tracking

    main

    The identifier subsidiary_company_id_sec10k is used to track subsidiaries over time. It is constructed from three components:

    1. The filer company’s Central Index Key (CIK)
    2. The subsidiary company name
    3. The subsidiary’s location of incorporation

    Usage Constraints:

    • Consistent Tracking: The same subsidiary reported by the same filer across multiple years will have a consistent subsidiary_company_id_sec10k.
    • No Cross-Filer Linking: If the same subsidiary appears in filings from different parent companies, it will receive a different identifier in each context. Do not use this ID to link the same subsidiary across different filers.

  8. Understand the SEC 10-K Ownership Data Extraction Model

    main

    The SEC 10-K ownership data extraction process uses machine learning to transform unstructured Exhibit 21 attachments from SEC Form 10-K filings into structured tables.

    Key Workflow:

    1. Extraction: A fine-tuned LayoutLMv3 model performs named entity recognition (NER) on Exhibit 21 documents to classify tokens as subsidiary name, location of incorporation, ownership percentage, or other.
    2. Structuring: A rules-based heuristic model uses bounding box coordinates and entity tags to organize these tokens into structured tables.
    3. Linkage: Probabilistic record linkage (using Splink) connects these extracted companies to EIA (Energy Information Administration) utilities, enabling connections to PUDL data.

    Note: This dataset is currently in beta. The contents and connections are probabilistic in nature.

  9. Understand PUDL's dbt architecture and quirks

    main

    PUDL's use of dbt is specialized because Dagster coordinates the pipeline, and dbt is used strictly for validation rather than transformation.

    Key Architectural Details:

    • Tables as Sources: Most PUDL tables are treated as dbt sources (external tables) rather than models. dbt assumes these tables exist and does not attempt to create them.
    • DuckDB & Parquet: Tables are stored as Apache Parquet files and queried via DuckDB. Because of this, some dbt functionality—specifically the dbt adapter object—is unavailable in test definitions because it cannot access the underlying database schema.
    • Intermediate Tables: Some intermediate tables are defined as true dbt models to simplify tests. These are materialized as views in a DuckDB database located at $PUDL_OUTPUT/pudl_dbt_tests.duckdb. When debugging these specific tables, you must connect to this DuckDB database.

  10. How PUDL releases data

    main

    PUDL is designed as an end-use application that produces data for consumption by other applications, rather than a library for Python dependencies. Consequently, PUDL does not release installable packages on PyPI or conda-forge.

    Release Process:

    • Versioned releases are tagged on main using a calendar-based format (e.g., v2023.07.15).
    • Tagged releases trigger an archive of the repository on Zenodo.
    • Nightly build outputs for tagged releases are archived on Zenodo and made available via the AWS Open Data Registry.

  11. Identify PUDL Entity Table Types

    main

    For each entity type, PUDL typically produces two distinct types of tables to handle different data lifecycles:

    1. Static Entity Tables: Contain attributes expected to be stable over time (e.g., core_eia__entity_plants). These tables always include entity in their name and have one row per unique entity.
    2. Yearly Slowly Changing Dimension (SCD) Tables: Contain attributes expected to vary slightly over time (e.g., core_eia860__scd_plants). These tables always include scd in their name and have one row per entity per report year.

    Supported EIA Entities and IDs:

    • Utilities: utility_id_eia
    • Plants: plant_id_eia
    • Boilers: plant_id_eia, boiler_id
    • Generators: plant_id_eia, generator_id

    For forensic auditing, PUDL also provides tables with forensics_entity_resolution in their names, which contain all raw values used during the resolution process.

  12. Access raw government data inputs

    main

    PUDL archives minimally processed versions of the raw data published by governments. This is useful if you need to see original data that is difficult to find, or if you want to see how data looked before it was revised or deleted by the source.

    These datasets are unmodified except for minimal processing (such as compression or grouping into ZIP archives) and the addition of metadata to assist identification. You can find these archives in the Catalyst Cooperative Community on Zenodo.