Reduce coupling in ETL and shift toward EtLT

[zaneselvans]

Description

Coupling between different datasets and between different phases of the data processing has often been a limiting factor in our ability to publish new data quickly, or to work on several different aspects of data integration in parallel. This epic is focused on refactoring the pipeline to reduce coupling so we can integrate new data, and more datasets, more quickly and less monolithically.

Motivation

• Many of our most involved data integration tasks currently happen before the data has been loaded into the database, and often make use of data that has yet to be fully cleaned and standardized, and which may not be accessible later on in the same form, making development and debugging of these processes difficult.
• One way that this is addressed in other data engineering contexts is to load the initially extracted data into a "data warehouse" (database) with a well defined structure prior to further involved transformations. This arrangement is often referred to as ELT (vs. ETL).
• Our inputs are really only semi-structured, so to even get to the point of having a well structured data warehouse, we still need to do a bunch of transformations, so that different years of data can be concatenated with consistent column names, data types, enumerated constraints, etc. However many of these transformations can be applied on a table-by-table basis without the need to integrate between tables or datasets, which makes them much less involved.
• Doing "lightweight" transformations after initial extraction, and before loading into a data warehouse that is used for subsequent more involved transformations is sometimes referred to as EtLT.
• PUDL was initially designed as a monolithic all-at-once data processing pipeline, but as we add more integrations between datasets, and more datasets, this is becoming less and less usable. We are already planning to migrate a version of our existing analytical outputs into the database in a post-ETL step. Moving to EtLT and writing progressively more refined data into the output DB in several stages could be a part of that already planned refactoring.
• Especially as we consider the integration of computationally intensive ML processes for entity matching (e.g. our CCAI grant proposal) and deduplication of records, being able to run those processes optionally on an existing DB will be advantageous.
• This will potentially move a lot of existing code around, but might not need to involve a lot of detailed changes in the guts.

Scope

Processing that could probably happen before loading into an interim data warehouse:

• Per-column value and data type cleanup
• Conversion from annual to monthly records (for EIA 923 tables where monthly data is reported in columns)

Data processing steps that could be deferred until after a simplified data warehouse has been loaded:

• Entity harvesting and full DB table normalization
• Enforcement of foreign key constraints
• Enforcement of primary key uniqueness
• Inferring the boiler generator associations
• Linkage between EPA CEMS and EIA plants/boilers/generators
• FERC Plant ID generation
• FERC to EIA Plant & Utility ID mapping
• Plant Part List generation and generator level linkage between FERC and EIA
• MCOE analysis
• State hourly demand estimations
• Historical utility and balancing authority service territory construction

Logistics

Billing

A new time-tracking project should be created for this Epic, taking time from the Sloan Maintenance & Refactoring category.

Priority/Timing

This should probably happen in conjunction with (shortly after) we get the Prefect based pipeline up and running nightly.

[bendnorman]

Should we add our airbyte and dagster research issues to this?

[zaneselvans]

Hmm. Yeah that does seem right. And maybe my "enumerate all the transformations" discussion / design issue too.

[bendnorman]

This issue is out of date. The design issues mentioned in this issue were mostly addressed by our migration to dagster.