chore(datalake): catalog namespacing redesign + storage-format decision (post-demo migration)

[thecaffiend]

Background

The bactopia-single-sample-analysis report broke because it queried the Glue table input_ccd_dlh_t_seqauto_input_clean_vbkt_s3_b1f75c7, which Glue's default crawler grouping froze and orphaned once a second sibling prefix (manifest/) appeared under the crawled bucket root. New meta partitions now land only in input_meta, so the metadata query returned zero rows.

This exposed a deeper, known-in-advance issue: everything added to the catalog so far was built haphazardly to demonstrate capability, and there is no deliberate design for how catalog objects should be named, grouped, and schematized across tributaries. This issue captures that design and the migration to it.

Phase A stopgap (already shipped): #371 / PR #372 repoints the report to input_meta and guards the empty-result case. It is intentionally format-agnostic and unblocks the demo. This issue is Phase B: the durable redesign, to be done after the demo.

All current catalog data is disposable test data. The migration can tear down and rebuild the catalog rather than doing an in-place, dual-table transition.

Namespacing model (verified, and the basis for the locked decisions)

Catalog objects are namespaced on three levels (verified in capeinfra/datalake/datalake.py and capeinfra/pipeline/data.py):

• Tributary -> one Glue catalog database ({trib}-catalog, e.g. ccd-dlh-t-seqauto-catalog_*). Tributary identity lives in the database, so cross-tributary differentiation is by database, not table name.
• Bucket role -> table-name prefix (today the free-form crawler prefix: input for input-clean, result for result-clean). All buckets in a tributary share the one database; the prefix disambiguates them.
• Folder within a bucket -> table name (meta/ -> input_meta). Full identity: {trib-db}.{prefix}_{folder}.

Consumers resolve a dataset by database (tributary) then {prefix}_{folder} table. The report selects the tributary DB via a substring match against athena.list_databases.

Locked decisions (durable regardless of storage format)

These hold whether we stay on CSV+crawler or move to Iceberg, so they are safe to implement now:

• Keep one Glue catalog database per tributary. Already true.
• Derive the table prefix from the bucket key with - swapped to _ (input-clean -> input_clean_), and drop the standalone free-form prefix: config knob so a prefix can never drift from the bucket it describes. Validate the derived name is a legal Glue identifier ([a-z0-9_], lowercase) and fail the build loudly otherwise. Apply uniformly across all tributaries (seqauto, genomics, hai), not just seqauto.
• Reserve the prefixes input-raw / input-clean / result-raw / result-clean (and their _ forms) platform-wide; document them. Since tables today originate only from crawlers bound to a bucket, deriving prefix from bucket key inherently prevents cross-bucket prefix squatting. Forward-looking guard: if a user-facing "add an arbitrary table to a tributary" path is ever added, it must validate the requested name against the reserved-prefix set.
• Do not crawl raw buckets for anything we have now (matches the existing convention), but keep the option open for a future pipeline that needs to track raw. Reserving input_raw/result_raw means an ad hoc raw table would land at a predictable, non-colliding name if ever needed.
• Document the one-crawler-per-bucket limitation explicitly. configure_bucket creates exactly one aws.glue.Crawler per bucket, and the crawler-attrs DDB maps bucket_name -> crawler_name 1:1. Deriving a single prefix from a bucket key assumes this invariant; multiple crawlers per bucket would make it ambiguous.

Gated decision (TBD): storage format

The remaining design components ride on a storage-format decision that is upstream of them:

• Option 1 - stay on CSV + crawler (near-term). Keep the crawler model. Then we must also add a Glue grouping Configuration knob to DataCrawler and force folder-level grouping so the root-table-vs-per-folder flip (the bug that started this) can never recur. Table identity becomes {trib-db}.{bucketkey_}_{folder}, deterministic.
• Option 2 - move to Iceberg (long-standing on our radar; CSV was the easy first target). Iceberg makes much of the CSV machinery moot: no crawler grouping heuristic to flip (so the grouping Configuration fix is unnecessary), no CSV classifier, declared + evolvable schemas (kills the drift class we hit), and hidden partitioning (no partition projection / crawler partition discovery). The cost relocates to the write path and ops:
  • Every producing ETL currently builds a CSV buffer and calls etl_job.write_sink_file(...). Iceberg requires writing through an Iceberg-aware engine (Glue Spark with --datalake-formats iceberg, Athena INSERT/CTAS, or PyIceberg). These are already Glue jobs, so it is feasible in-model, but each ETL is a genuine rewrite.
  • The S3-object-landing notification model (fires on meta/, manifest/, etc.) must be rethought, since Iceberg manages its own data+metadata file layout under the table location.
  • New operational surface: snapshot expiration, compaction, orphan-file cleanup.
  • Current sample_id/year/month/... path partitioning (including the recent result-clean/input-clean partition alignment) must be re-expressed as Iceberg partition transforms.

Decision needed: is Iceberg near-term enough to adopt during this migration, or do we ship the CSV grouping stopgap now and defer Iceberg? Do not over-invest in the CSV-crawler determinism machinery if Iceberg is imminent - that work is throwaway once we switch.

Deferred design components (TBD, folded under the format decision)

These were discussed but intentionally not locked; resolve them at implementation time under whichever branch wins:

• Schema pinning vs inference. The shared classifier cape-csv-standard-classifier (OpenCSVSerDe, contains_header=PRESENT, no explicit header list) pins header detection but still infers column names/set from each file's header, and types are all-string. These schemas are ETL-owned (code), so inference lets column changes silently reshape the catalog.
  • Under CSV+crawler: either declare the tables explicitly (fixed columns/types) or accept inference and document the ETL-owned column contract. Per-dataset CSV classifiers are a weak middle ground (two same-format CSVs disambiguate poorly, still all-string).
  • Under Iceberg: solved by declared, evolvable schemas; classifier is irrelevant.
• Partition strategy. Partition projection vs declared tables vs crawler-discovered partitions (CSV), or Iceberg partition transforms. Subsumed by Iceberg if we go that way.
• Table-name readability. Confirm the resulting names read well (input_clean_meta, input_clean_manifest, result_clean_software_versions).

Migration mechanics (Phase B)

Because current catalog data is disposable test data:

1. capeinfra: implement the locked namespacing decisions (prefix-from-bucket-key, drop prefix: knob, validation) plus the format-gated crawler-grouping Configuration (only if staying on CSV).
2. Rebuild the catalog clean: drop the tributary catalog databases/tables and let crawlers (or the Iceberg writers) recreate them with the new names, rather than leaving deprecated orphans.
3. Consumer cutover for the result_* -> result_clean_* (and input_meta -> input_clean_meta) rename:
   • assets/report/bactopia-single-sample-analysis/data_function.py (input_meta, result_software_versions, result_sourmash_gtdb_rs207_k31, result_amrfinderplus, result_caerbannog_stoplight)
   • assets/etl/etl_bactopia_results.py, assets/etl/etl_caerbannog_results.py
   • any external saved Athena queries / dashboards
4. Validate with pulumi preview --diff -s cape-cod-dev, reconcile every planned action, then deploy (user's step; agents never run pulumi up).
5. Re-test the report end-to-end and confirm the new {prefix}_{folder} tables populate.

Out of scope

• The Phase A report repair (fix(reports): fix broken table reference in the current bactopia report data function #371 / fix(reports): repoint bactopia single-sample data function to input_meta #372) - already shipped.
• The pandas empty-DataFrame crash - already guarded in Phase A.

[thecaffiend]

Follow-up: the crawler-collision / immediacy problem is separable from the storage-format decision

Operational driver worth recording: the scheduled crawler model is becoming untenable, and the natural fix (trigger a crawl on pipeline completion) runs into a collision problem. A Glue crawler runs one execution at a time, so a per-completion trigger either serializes (a second trigger is rejected or queued behind a minutes-long run) or forces multiple crawlers over the same S3 location that then race on the same catalog tables with no transactional guarantee. Crawlers also work by scanning to discover changes, so they get slower as data grows. We need catalog output almost as soon as a pipeline finishes, and many pipelines can run concurrently - which the 02:00 schedule cannot satisfy and per-completion crawls cannot satisfy safely.

Key point: this is a crawler-mechanism problem, not a storage-format problem. Dropping the crawler in favor of write-time catalog registration is a smaller, separable decision that can land sooner than (and independently of) the CSV-vs-Iceberg choice. Once the table schema is declared once (already the agreed direction for code-owned schemas), there are two crawler-free options that give immediate, collision-free catalog visibility on CSV/Parquet:

• Programmatic partition registration. The ETL knows exactly which partition it just wrote, so after writing it calls Glue CreatePartition / BatchCreatePartition to register that one partition on the pre-declared table. No scan, no crawler, immediate; each job registers its own partition and distinct partitions do not contend. This is the CSV-world analog of Iceberg's write-time registration and works today.
• Partition projection. Declare the table once with projection matching the sample_id=.../year=.../month=.../day=.../... path scheme; Athena derives partitions from the query predicate at read time, so there is zero catalog mutation on write - nothing to trigger, nothing to collide. Nuance: sample_id is unbounded, so it would be an injected projection type (queries must filter on sample_id, which the report already does), while the date parts are integer-range projections.

Caveats on the cheap fixes:

• Partition projection is an Athena-specific read feature. If any non-Athena engine (Spark, EMR, Trino outside Athena) ever reads these tables, projection is not portable - use programmatic registration or Iceberg instead.
• Neither cheap fix provides schema evolution, row-level updates/deletes, or time travel. They solve only the immediacy-and-concurrency problem.

How this relates to the storage-format decision above:

• Iceberg solves the same immediacy/concurrency problem more completely: the write commit is the catalog update (immediate), and optimistic concurrency control lets many pipelines commit to the same table at once, each an atomic metadata swap with conflicting commits retrying. Our access pattern (each run appends its own sample_id/date partition) is the near-zero-conflict ideal. Iceberg additionally buys schema evolution, upserts/deletes, and snapshots.
• Delta offers the same ACID/concurrency conceptually, but on a pure AWS/Athena stack its catalog-visibility story is weaker (historically a symlink/manifest refresh), so it does not give the clean no-crawler immediacy that Iceberg + Glue does. Iceberg remains the better fit for this stack.

Decision framing:

• If the only driver is immediate, concurrency-safe catalog updates without crawler collisions, then partition projection or programmatic partition registration solves it now on CSV/Parquet without the Iceberg write-path rewrite - and the crawler goes away for controlled pipelines either way.
• Iceberg is justified when we also want schema evolution (the drift that broke the report), row-level updates/deletes, or time travel.

Net: "eliminate the crawler for controlled data in favor of write-time registration" can be decided and implemented independently of, and earlier than, the storage-format choice. (This analysis intentionally scopes out genuinely external/uncontrolled data, where a crawler may still be the right tool.)

[thecaffiend]

Root-cause detail: how Glue default grouping split the root table (preserved from working notes)

Fuller mechanism behind the "default crawler grouping froze and orphaned the root table" summary above, kept here so the detail survives without a separate tracked doc.

A root-targeted Glue crawler with a null Configuration (default grouping) models the catalog differently depending on how many folder prefixes exist under the crawled root, and the flip is deterministic, not a glitch:

• With only one folder prefix under the bucket root (e.g. meta/), Glue creates a single bucket-root table named after the bucket (input_ccd_dlh_t_seqauto_input_clean_vbkt_s3_b1f75c7) and captures the first folder as a partition_0 key.
• The moment a second, heterogeneously-schemed sibling prefix appears under the same root (manifest/ alongside meta/), Glue's default table-grouping heuristic switches to per-folder tables (input_meta, input_manifest).
• Because the crawler's SchemaChangePolicy.DeleteBehavior is DEPRECATE_IN_DATABASE, the old bucket-root table is not deleted - it is frozen with stale partitions and silently orphaned. New partitions land only in the new per-folder tables.

Concrete instance: the bactopia-single-sample-analysis report queried the bucket-root table, which stopped receiving meta partitions after commit c9ea7e9 started writing a manifest/ sibling prefix. Live data moved to input_meta; the report crashed at meta_df.iloc[0] on an empty result (Phase A fix: #371 / PR #372).

Implications that motivate the Option 1 grouping Configuration fix:

• Re-running the crawler does NOT restore the old table; as long as two prefixes coexist, Glue keeps the per-folder tables.
• Any tributary that starts with a single crawlable prefix and later adds a second will hit the same flip.
• Forcing folder-level grouping via an explicit Glue Configuration makes table identity always {db}.{prefix}_{folder} regardless of prefix count or arrival order. (Moot under the Iceberg option, which has no crawler grouping heuristic.)