Existing tools for managing our metadata?

[zaneselvans]

We have a lot of metadata describing the hundreds of tables and thousands of columns that are part of PUDL, and a somewhat homebrew system for managing it, using a mix of Pydantic and SQLAlchemy. It's hard to believe that we're facing a novel problem here, and I wonder if there are some off-the-shelf solutions we could be using or working toward?

Exactly how and where the data ends up being output can change over time, and we don't want to have to maintain and synchronize several different definitions manually, so at a high level, we have generic descriptions of the tables (Resources) and columns (Fields), originally modeled on the Frictionless tabular data package standard. Database-like information is stored in the Table Schemas that are part of the Resource definition.

What do we do with our metadata?

Manage Data Types

• We need to be able to cast individual columns to appropriate data type when we switch between one data storage medium and another. E.g. when we read a dataframe out of the SQLite DB, we need to impose the appropriate nullable Pandas data types to it.
• We currently need to be able to translate generic abstract column types for use with SQLite, Pandas, and PyArrow (for Parquet).
• In the future we might need to work with other databases like DuckDB or Google BigQuery too. Or we might want to switch to using PyArrow backed Pandas dataframes.

Create database schemas

• We use the metadata to define our output DB (and Parquet) schemas, including defining which columns are grouped into tables, PK/FK constraints, enumerating allowed values or regex patterns, nullability, etc. Most of this work is done by SQLAlchemy, but it takes its direction from our metadata.
• We're also setting up Alembic now, to allow migrations between different versions of the database schema, and this also relies on our metadata (which is used to generate SQLAlchemy metadata objects, used by Alembic)

Validate Data Contents / Structure

• Beyond the kinds of individual value and data integrity constraints that can be defined as part of the database schema, we also have aggregate expectations about the data, which it would be nice to define alongside our other constraints using a similar language.
• Right now these validation constraints are very DIY and only used in the validation tests, but it would be nice to check them along the way as the data is transformed so we can catch problems early.

Export metadata for use by other systems

• Datasette metadata (YAML) to annotate the live DBs folks can browse.
• datapackage.json files that will annotate SQLite, Parquet, or other file-based tabular data products we distribute. This will allow the creation of relatively lightweight data catalogs, and export to systems like Kaggle.

Build human-readable documentation

• We use our generic abstract metadata to build data dictionaries that document our data products on Read The Docs with some Jinja templates and custom output functions that are run by Sphinx.

What don't we do with our metadata?

ORM stuff

• The PUDL DB is pretty solidly on the OLAP (analytical) DB side of things, and we aren't really translating between individual sets of database records and object instances, so we aren't really using the ORM tooling that's part of SQLAlchemy.

Existing tools

All of these intersect with the above needs somehow, and we're already using many of them. Is there a better way for them to be glued together into a coherent whole?

• SQLAlchemy
• Pydantic does rich data classes & data validation.
• Dagster Pythonic types
• SQLModel Pydantic models that are also SQLAlchemy models.
• Pandera: A data validation library that's integrated pretty tightly with Pandas, Pydantic, and Dagster, and which allows easy dataframe schema definitions
• Great Expectations for data validation
• Dagster also has a Great Expectations integration too.
• A comparison of Pandera and Great Expectations
• dbt-expectations is a plug-in for using Great Expectations to validate data models in dbt
• Pandas which as of v2 is starting to support PyArrow backed dataframes / types.
• PyArrow Schemas
• Hypothesis integrates with Pandera and pytest to automatically generate data for testing.
• The Frictionless Framework provides tools for validating and annotating tabular data in a variety of formats, including CSV, SQLite, and Parquet using high-level generic types and structures.

[cosmicBboy]

Sounds like we need some kind of meta-tool to standardize the metadata across all these frameworks :)

[zaneselvans]

😭 Standards

[Ludee]

Hi there,

we are developing a metadata standard for energy data: https://github.com/OpenEnergyPlatform/oemetadata
We have a (beta) tool called OMI for the generation and validation of the metadata: https://github.com/OpenEnergyPlatform/omi
It is based on frictionless and has an active user and developer community.
We would be very happy to collaborate on this topic!