Locate JGI Isolate submission metadata in the JGI Dremio lakehouse

[turbomam]

During example data work for PR #2884, we confirmed that GOLD organism_v2 data is queryable from the JGI Dremio lakehouse (lakehouse.jgi.lbl.gov), but JGI Isolate (NA) v19 submission form metadata was not found.

Possible locations to investigate:

• "gold-db-2 postgresql".gold.bioproject_submission_2
• "gold-db-2 postgresql".gold.biosample_submission_2
• portal-db-1 (JGI portal database)
• Samples source
• sdm dev mongo source

This data would enable creating a JGI Isolate authority example file for OrganismSample and validating the jgi_isolate subset slot mappings.

See also: ~/Desktop/markdown/gold-organism-v2-schema-audit-2026-03-18.md

Part of #2803.

[aclum]

What you are looking for is the ITS Samples table. so of this list 'Samples' is likely what you are looking for.

[aclum]

The information on the JGI side is stored in several different tables that would be part of a dw (data warehouse) source. Tables include samples, ribosomal_sequence, sample_taxonomy_info, sample_tissue_contact where the key on all of those tables is sample_id. An example isolate sample ID is 399933

[turbomam]

Thanks @aclum that's helpful

Yes, these all look relevant:
samples, ribosomal_sequence, sample_taxonomy_info, and sample_tissue_contact, especially when joined on sample_id.

In the lakehouse I can find what look like the DW versions of two of those:

• "gold-db-2 postgresql".gold.dw_samples → covers the physical/QC fields from the Excel (concentration, volume, absorbance, tube/plate, format, DNAse, pure culture, isolation method, etc.)
• "gold-db-2 postgresql".gold.dw_sample_taxonomy_info → covers genus, species, strain, isolate, NCBI tax ID, genome size, GC%, ploidy, reference genome

Your example sample_id=399933 pulls fine from both:

dw_samples[399933]:
  sample_name: 2615_16s_226
  dna_isolation_method: Chemgaic DNA Kit
  current_concentration_ngul: 9.9
  tube_plate_label: JGI_JPL_Plate_9 | plate_location: A8
  sample_format: Aqueous | dna_treated: N | starting_culture_pure: Y
  sample_isolated_from: Clipper environmental surface
  collection_site_growth: 25˚C; AC-R
  qc_status: Pass

dw_sample_taxonomy_info[399933]:
  Stenotrophomonas lactitubi strain C-FC-06-B-C1
  NCBI: 2304513 | genome_size_estimated_mb: 4.6

I can't find in the lakehouse but i can live with that for now

• ribosomal_sequence — not present under gold-db-2, portal-db-1, or numg
• sample_tissue_contact — also not found. Is this the sample contact / PI info?