Move away from SQLite as core data persistence layer

[jdangerx]

Overview

• in order to write individual re-materializations of assets with new schemas, we needed some way of managing schema changes w/o blowing away whole db (Alembic!)
• Alembic migrations are confusing & painful when dealing with many schema changes at once
• Alembic migrations don't include CheckConstraints which means that in nightly/local builds we aren't checking e.g. "this string matches this regex" in SQLite. Though they are nominally being checked in Pandera rn - see Field constraints missing from Dagster SQLite outputs #4169 for details.
• The only checks that live solely in SQLite, then, are FK/PK checks, and if we can replicate those checks elsewhere we can avoid both Alembic and SQLite.

Success criteria

• we don't need to use Alembic to manage migrations anymore
• we have foreign key and primary key checks on our data, still

Out of scope, but good follow-up:

• we confirm that Pandera is successfully checking all CheckConstraints OR
• we port CheckConstraints to dbt and confirm their enforcement there

Next steps

• Migrate FK/PK checks to dbt validations as data_tests on each table.
  • feat: add foreign key check for dbt + tests #5207 We'll need to add our own bespoke foreign_keys generic data test, because there doesn't appear to be one online that supports composite FKs.
  • (optional) We can add our own primary_key generic data test that just glues together existing 'unique columns' and 'not-null' checks.
• Automatically generate these based on contents of PUDL_PACKAGE.
  • feat: split human/machine-generated dbt schemas for great automation #5228 have a schema.human.yml (human-owned tests) and schema.machine.yml (everything auto-generated from PUDL_PACKAGE) and then have dbt_helper merge them into an actual schema.yml
  • Add some mechanism to automatically trigger re-generation of the schema.yml files #5268
  • Add the PK and FK checks to the DbtTable.from_table_name method so they are machine generated.
• Ditch SQLite and Alembic, add script that converts pile of Parquets back to SQLite for distribution only.
  • probably worth thinking about how to do this in smaller steps