pudl.validate.integrity

Database integrity validation checks for PUDL data.

This module implements checks for structural database constraints such as foreign key relationships. These checks are applied after all data has been loaded into the database, since the parallel nature of the ETL pipeline means that foreign key constraints cannot be enforced during loading. As these checks are migrated into dbt, this module should shrink accordingly.

Attributes

logger

Exceptions

ForeignKeyError	Raised when data in a database violates a foreign key constraint.
ForeignKeyErrors	Raised when data in a database violate multiple foreign key constraints.

Functions

_get_fk_list(→ pandas.DataFrame)	Retrieve a dataframe of foreign keys for a table.
check_foreign_keys(engine)	Check foreign key relationships in the database.

Module Contents

pudl.validate.integrity.logger

exception pudl.validate.integrity.ForeignKeyError(child_table: str, parent_table: str, foreign_key: str, rowids: list[int])

Bases: sqlalchemy.exc.SQLAlchemyError

Raised when data in a database violates a foreign key constraint.

child_table

parent_table

foreign_key

rowids

__str__()

Create string representation of ForeignKeyError object.

__eq__(other)

Compare a ForeignKeyError with another object.

exception pudl.validate.integrity.ForeignKeyErrors(fk_errors: list[ForeignKeyError])

Bases: sqlalchemy.exc.SQLAlchemyError

Raised when data in a database violate multiple foreign key constraints.

fk_errors

__str__()

Create string representation of ForeignKeyErrors object.

__iter__()

Iterate over the fk errors.

__getitem__(idx)

Index the fk errors.

pudl.validate.integrity._get_fk_list(engine: sqlalchemy.Engine, table: str) → pandas.DataFrame

Retrieve a dataframe of foreign keys for a table.

Description from the SQLite Docs: ‘This pragma returns one row for each foreign key constraint created by a REFERENCES clause in the CREATE TABLE statement of table “table-name”.’

The PRAGMA returns one row for each field in a foreign key constraint. This method collapses foreign keys with multiple fields into one record for readability.

pudl.validate.integrity.check_foreign_keys(engine: sqlalchemy.Engine)

Check foreign key relationships in the database.

The order assets are loaded into the database will not satisfy foreign key constraints so we can’t enable foreign key constraints. However, we can check for foreign key failures once all of the data has been loaded into the database using the foreign_key_check and foreign_key_list PRAGMAs.

You can learn more about the PRAGMAs in the SQLite docs.

Raises:

ForeignKeyErrors – if data in the database violate foreign key constraints.