KBase Data Lakehouse: Common Pitfalls & Gotchas

Raw report: pitfalls Feeds into: adversarial-research-quality-assurance, analysis-provenance-and-reproducible-outputs, callability-limited-comparative-inference, capability-versus-kinetic-predictability, classifier-database-compatibility-in-taxonomic-quantification, composite-functional-annotation, computational-pathway-prediction-validation, confirmatory-exploratory-ecological-association-discordance, credential-and-tenant-access-lifecycle, cross-cohort-microbiome-portability, cross-species-fitness-transferability, cross-tenant-data-bridging, cultivation-collection-bias-in-ecological-genomics, data-landscape-ownership-and-coverage-bias, driver-side-result-collection-limits, ec-less-reaction-annotation, ecotype-clustering-validity, ecotype-environment-gene-content, environment-embedding-geography, evidence-triangulation-for-functional-annotation, execution-environment-dependent-access-paths, fitness-condition-coverage-prioritization-bias, fitness-module-detection-sensitivity, foreign-dump-ingestion-fidelity, functional-marker-validation, gene-essentiality, homology-search-negative-evidence, join-strategy-and-table-partitioning-cost, long-running-analysis-execution-reliability, metal-cross-resistance, multi-omics-integration, numeric-type-fidelity-across-engine-boundaries, occurrence-versus-catabolic-activity, ontology-and-category-schema-sensitivity, pangenome-core-boundary-and-clade-size-bias, pangenome-integration, phenotype-database-coverage-bias, pooled-run-pseudoreplication-and-metadata-label-noise, provenance-aware-resource-discovery, runtime-budgeted-resampling-and-embedding, sample-size-aware-phenotype-consensus, sampling-depth-and-downsampling-effects, selection-on-outcome-leakage, shared-stress-versus-stressor-specific-fitness, silent-failure-modes-in-distributed-queries, structural-annotation-gap, study-batch-confounding-of-environmental-associations, taxonomic-nomenclature-reconciliation, taxonomic-resolution-dependent-functional-inference, transposon-callability-bias, vectorization-versus-row-wise-iteration

Overview

This document is a practical reference for avoiding failures, silent misjoins, biased analyses, irreproducible workflows, and misleading interpretations when querying KBase Data Lakehouse databases. It emphasizes live, access-aware catalog discovery, direct Spark SQL for large workloads, explicit schema and type inspection, provenance-preserving joins, and independent validation of analysis designs. ₁

Key Findings

Namespace migration and access

KBase Data Lakehouse is migrating collections from Delta to Iceberg. Migrated tables use catalog.namespace.table, such as kbase.ke_pangenome.genome, whereas the former Delta form flattened the namespace, such as kbase_ke_pangenome.genome. Underscore-form references occur in hundreds of archived files, which are intentionally not rewritten. Only the leading underscore changes; the table name itself is unchanged. A query copied verbatim from an older README, REPORT, notebook, or script uses the underscore form and fails with TABLE_OR_VIEW_NOT_FOUND or an unresolved-relation error against a migrated catalog. Migration remains incomplete: a collection whose dotted namespace cannot be found may still be Delta-only, in which case the underscore form is the correct address. Neither form should be hardcoded. Code must resolve the current address through live, access-aware catalog discovery, such as SET and parsing spark.sql.catalog.* or the notebook helpers, and should prefer the dotted form when it is available. ₁

The data_lakehouse_ingest tenant is the MinIO governance-group name rather than the database prefix; the library auto-prepends the tenant name to the dataset field to form the database namespace. The kbase_ke_pangenome database is under tenant kbase, with dataset ke_pangenome; using tenant kbase_ke causes access failure, while combining tenant kbase with dataset kbase_ke_pangenome incorrectly creates kbase_kbase_ke_pangenome. ₁

Access failures should be translated to a plain permissions explanation identifying the unreachable table and tenant and directing the user to the KBase Data Lakehouse Tenant Browser. The underlying failure usually surfaces from S3 or the Spark catalog as S3 access denied, 403 Forbidden, Token denied, or AccessControlException rather than as a table-level permission message. Tenant membership can be checked with get_group_sql_warehouse(tenant_name); an ErrorResponse with error 20040 means the user is not in that group. User-facing messages must not expose internal strings such as “S3”, “token”, “403”, “access denied”, or internal service URLs. ₁

The authentication token variable in .env is KBASE_AUTH_TOKEN, not KB_AUTH_TOKEN. This token can expire during long analysis sessions, after which all KBase Data Lakehouse API calls return 401/403 errors. On JupyterHub a fresh token is always available at the literal file path ~/.berdl_kbase_session, synced every 30 seconds by the IPython startup script ~/.ipython/profile_default/startup/05-token-sync.py. The .env file is not updated automatically, so long-running CLI sessions outside JupyterHub must re-export the token manually. In the essential_metabolome project, off-cluster mc (MinIO client) commands timed out unless proxy environment variables were set. Spark-session creation also depends on the execution environment. The source distinguishes three environments with different import patterns and warns that using the wrong one causes ImportError. In JupyterHub notebooks, spark = get_spark_session() needs no import, because it is injected by /configs/ipython_startup/00-notebookutils.py. Regular Python scripts on the cluster use an explicit import of get_spark_session from the same notebook-utilities module (setup_spark_session); fitness_modules discovered that this import works from regular Python scripts as well as notebooks. A local machine uses from get_spark_session import get_spark_session. Calling bare get_spark_session() in a CLI script on JupyterHub raises NameError, because auto-import applies only to notebook kernels. In conservation_vs_fitness, the Spark Connect service ran as a Java process on port 15002. Killing Java processes, for example while cleaning up stale notebook processes, took down Spark Connect, after which get_spark_session() failed with RETRIES_EXCEEDED / Connection refused. The reported recovery is to log out of JupyterHub, start a new session, and run get_spark_session() from a notebook to restart the Spark Connect daemon; the daemon cannot be restarted from the CLI. The ingest skill’s initialize() is off-cluster-only, so on JupyterHub it should be bypassed and Spark and MinIO clients should be built directly. ₁

The REST API can return 504 Gateway Timeout, 524 Origin Timeout, 503 executor-restart errors, or empty responses. The source’s troubleshooting table attributes 504 to a query that took too long (remedy: simplify the query, add filters, or use direct Spark), 524 to a server that did not respond (retry after a few seconds), and 503 “cannot schedule new futures after shutdown” to a restarting Spark executor (wait 30s, then retry). Direct Spark SQL is preferred for complex or large queries; the REST /count endpoint is particularly unreliable for loops over many tables, frequently returning errors or timing out on tables that Spark queries handle instantly, and /schema frequently times out on large tables, where DESCRIBE database.table through the /query endpoint gives more reliable schema introspection. Transient 503 (“cannot schedule new futures after shutdown”) and 504 errors on large queries should be handled by retrying or by breaking the work into per-species batches. Pagination requires ORDER BY, because LIMIT/OFFSET without ordering returns inconsistent results. For cold databases the source records the opposite pattern: the /count endpoint often returns correct results while the /query and /sample endpoints return 0 rows or empty results. Its workaround is to use /count to verify that data exist and then retry /query with exponential backoff, because the Spark cluster may take several minutes to warm up for a cold database. This is in tension with the same source’s warning that /count is unreliable in loops over many tables. The two notes describe different failure contexts, and neither establishes /count as generally reliable. ₁

Provenance, identifier, and join integrity

Short ENIGMA strain names are not globally unique. In one example, ENIGMA MT20 was Rhodanobacter glycinis, whereas GTDB’s MT20 was Streptococcus pneumoniae; the erroneous match involved 8,434 genomes, including 1,751 clinical genomes. Of 32 pangenome linkages through ncbi_strain_identifiers, 12 were incorrect genus matches. The spurious match contaminated genus–environment profiles with clinical data. The genus from the source database, such as enigma.genome_depot_enigma.browser_taxon, must be cross-checked against the genus of the GTDB match, and linkages must be rejected when the genera disagree. For any project linking ENIGMA CORAL or genome-depot strains to the pangenome, assembly-accession (GCF_*) matching is preferred when possible because it is unambiguous. ₁

Cross-cohort analyses that pivot on a string key such as taxon_name_original can silently split the same species into non-overlapping rows when cohorts use different MetaPhlAn3 database vintages or curatedMetagenomicData processing stages. Such analyses therefore require a synonymy layer rather than simple string normalization. Observed divergences included short names versus full lineage strings, GTDB-renamed Bacteroides vulgatus versus Phocaeicola vulgatus, Eubacterium rectale versus Agathobacter rectalis, Ruminococcus gnavus versus Mediterraneibacter gnavus, and multiple Clostridium, Lactobacillus, and related reclassifications. In the CrohnsPhage mart, failure to reconcile names produced log₂FC approximately 28, equivalent to approximately 2.68 × 10⁸ fold, because one cohort’s abundance collapsed to a pseudocount; CMD_IBD used the modern names and CMD_HEALTHY used the legacy names. The committed ref_taxonomy_crosswalk does not fully resolve this. Legacy Bacteroides_X rows have ncbi_taxid = NaN and are orphaned in taxid joins, and B. vulgatus and P. vulgatus remain separate rows with different canonical_name values. After format normalization, a hand-curated 23-entry synonym map (SYNONYM_MAP in NB00 of ibd_phage_targeting) was used for a targeted battery, but full-taxonomy aggregation requires an NCBI-taxid-backed, GTDB-version-aware reconciliation layer. ₁

The pangenome taxonomy tables must be joined through genome_id, not gtdb_taxonomy_id, because the genome table stores a genus-level taxonomy string while gtdb_taxonomy_r214v1 stores a species-level string. In the prophage_ecology example, genome.gtdb_taxonomy_id is truncated at g__Staphylococcus while gtdb_taxonomy_r214v1.gtdb_taxonomy_id continues to s__Staphylococcus aureus, and joining on gtdb_taxonomy_id returns zero rows. gtdb_taxonomy_r214v1 also has a column named order (taxonomic rank), a reserved Spark SQL keyword that causes a parse error if used unquoted in a SELECT or JOIN. gapmind_pathways.clade_name uses the full gtdb_species_clade_id, including the representative accession, rather than the shorter GTDB_species value (for example s__Rhizobium_phaseoli--RS_GCF_001234567.1 rather than s__Rhizobium_phaseoli); joining gapmind_pathways on a temp view populated from gtdb_species_clade.GTDB_species therefore returns 0 rows because the two formats do not match. ₁

Pangenome gene-cluster identifiers are species-specific and cannot be compared directly across species. Identically named cluster IDs must not be treated as equivalent; the source’s example is that cluster “X_123” in E. coli is unrelated to “X_123” in Salmonella. Cross-species comparisons should use shared representations such as COG (Clusters of Orthologous Groups) categories from eggnog_mapper_annotations, KEGG orthologs (the KEGG_ko column), or Pfam domains. eggnog_mapper_annotations.query_name joins to gene_cluster.gene_cluster_id, not gene.gene_id; Bakta and InterProScan annotations similarly join at the gene-cluster level, because their annotations are made at cluster-representative level rather than per gene. In gene_cluster the clade column is gtdb_species_clade_id, not clade_name; clade_name is the column in gapmind_pathways. The gene table has only gene_id and genome_id, with no species or clade column. gtdb_species_clade_id follows the format s__<Genus_species>--<RS_GCF_XXXXXXXXX.X | GB_GCA_XXXXXXXXX.X>, for example s__Escherichia_coli--RS_GCF_000005845.2. Because -- can be parsed as a SQL line comment inside IN (...) clauses, LIKE 's__Genus_species%' patterns are recommended for these IDs. Behavior depends on the query method: in direct Spark SQL a -- inside a quoted string literal is not a problem, whereas the REST API rejects any query containing -- regardless of quoting, treating it as a SQL comment metacharacter and returning a 400 error. genome_id values take the form RS_GCF_XXXXXXXXX.X or GB_GCA_XXXXXXXXX.X and require exact equality. In GenomeDepot databases, browser_genome has no species column, so species filtering requires a join through browser_taxon. The GenomeDepot browser_protein_cog_classes table uses cog_class_id, not cogclass_id, and the source advises checking the schema with DESCRIBE before filtering. Species-specific cluster scope also changes counts. A query of eggnog_mapper_annotations returned 2,962 DUF4041 annotations, but joining to gene_cluster produced 4,572 rows, because the same eggNOG annotation (query_name) can map to gene-cluster entries in multiple species pangenomes. The post-join row count is the number of species-level gene clusters, and the eggNOG count is the number of unique annotation records. Both counts are valid for different purposes, and reports must document which one they use. ₁

The ncbi_env table is entity-attribute-value data with columns including accession, attribute_name, content, display_name, harmonized_name, id, and package_content; it is not a flat genome-metadata table. The pitfall, recorded from amr_strain_variation, is that ncbi_env has no flat columns such as genome_id, isolation_source, or collection_date; accession holds the BioSample ID. Metadata extraction requires joining genome.ncbi_biosample_id to ncbi_env.accession, filtering attribute_name IN ('isolation_source', 'collection_date', 'geo_loc_name', 'host'), and pivoting from long to wide format. ₁

Experimental and statistical design

The curatedMetagenomicData CMD_HEALTHY and CMD_IBD buckets bundle samples from ~50+ published studies. Their healthy and disease sub-studies are disjoint, with no sub-study containing both CD and HC samples, making a pooled log_abundance ~ diagnosis + (1 | substudy) mixed model structurally unidentifiable for the CD-versus-HC contrast. In an ecotype-assigned set of 8,489 samples, 45 sub-studies had at least 10 HC samples, 5 had at least 10 CD samples, and 0 had at least 10 of both. Four IBD sub-studies had at least 10 CD and 10 nonIBD samples, comprising 242 CD and 369 nonIBD samples; a within-substudy contrast combined by inverse-variance meta-analysis is the design-consistent alternative. The same caveat generalizes to any pooled public-dataset analysis, such as SRA study inventories or Qiita studies, in which the case and control cohorts were collected by different groups; there, the within-cohort CD-versus-nonIBD contrast is the design-consistent alternative. Because the sub-study random effect perfectly predicts diagnosis, the pooled model either silently fails to converge or absorbs the CD effect into the random intercept. statsmodels.mixedlm with lbfgs returned without error but gave no usable fixed-effect estimate. If a pooled CD-versus-HC effect must be reported, it should be quoted as-is with an explicit caveat that study, cohort, sequencing center, and sample preparation are all collinear with diagnosis, and it should be sanity-checked against the within-substudy meta-analysis. For IBD samples, sub-study identity can be recovered from the middle colon-separated token of participant_id, as in CMD:HallAB_2017:SKST006. For HC samples it comes from the "study" key of dim_samples.external_ids. Together these sources cover ≈ 80 % of cMD samples, whereas short-prefix regex on sample IDs covers < 20 % and produces mostly noise categories. ₁

Clustering samples on a taxon-abundance matrix and then testing those same taxa within clusters creates selection-on-outcome leakage. Within-cluster effect sizes are mechanically inflated for cluster-defining taxa, and the within-cluster top-N list is substantially the cluster definition itself. In ibd_phage_targeting NB04, with ecotypes trained on MetaPhlAn3 species abundances and within-ecotype CD-versus-HC CLR Mann-Whitney tests run on the same species matrix, K=4 LDA (latent Dirichlet allocation, a topic model used to assign samples to ecotypes) ecotypes produced a 33-species Tier-A list with CLR-Δ (difference in centered log-ratio-transformed abundance) effect sizes from +0.5 to +3.0, but held-out-species sensitivity in NB04b yielded Jaccard values of 0.230 for E1 and 0.064 for E3. That check used 5 random 50/50 species splits, refit LDA ecotypes on half A, re-tested half B within the refit ecotypes, and computed the Jaccard overlap between the refit top-30 and the original top-30 intersected with half B (not an unrestricted top-30 overlap); both values fall below the stated < 0.3 leakage-dominates bound. The NB04b leave-one-species-out refit took each battery species in turn, refit the ecotypes on all other species, re-derived the cluster labels, and re-tested the held-out species. Under this refit C. scindens (Clostridium scindens) changed from NB04’s “n.s. within both ecotypes” call, which had been the basis for a “paradox RESOLVED” claim, to CD increases in both E1, with CI +0.68 to +0.87, and E3, with CI +1.13 to +1.71; the source concludes that the within-ecotype non-significant call was a self-selection effect. In NB04c, Tier-A status required species to pass both the possibly leakage-contaminated within-ecotype differential abundance and an independent CD-versus-control contrast; this independent-evidence gate reduced the Tier-A list from 33 candidates to 3. An independent-design cross-check, the confound-free within-IBD-substudy CD-versus-nonIBD contrast, found that most of the NB04 Tier-A E1 candidates had negative within-study effects, so the source interprets them as ecotype markers rather than CD drivers. ₁

When clusters and tests use related data, leakage should be assessed with held-out-feature clustering, leave-one-feature-out refitting, or clustering on a genuinely different functional matrix such as KEGG pathways, MetaCyc, or EC numbers. In the proposed pathway-level design, clusters are defined on the functional matrix and taxonomic differential abundance is then tested, so because the two matrices are genuinely different, leakage is structurally impossible rather than empirically bounded. The document treats a Jaccard value above 0.5 as bounded leakage and below 0.3 as leakage dominating for the cited sensitivity procedure; these are project-specific decision thresholds, not universal statistical guarantees. Rebuilding the ecotype analysis on a functional feature matrix was flagged as future work rather than completed. The same leakage applies to microbiome enterotypes, to single-cell type-stratified differential expression when cell types are clustered on the gene-expression features that are then tested, and to any analysis whose cluster labels are a function of the tested features. ₁

In Web of Microbes, organism observations use action codes I for increased, E for emerged, and N for no change, with occasional D for decreased; the binary “produced” definition is action IN ('I', 'E'). For the “The Environment” control, D instead means detected and N means not detected, so actor context is required. The 2018 snapshot has no organism-level decreased or consumption action, and approximately 56% of compound names begin with Unk_. The raw action code is not itself a produced or consumed label. Including N in the produced set makes every strain look like a production generalist and collapses the signal, while excluding E misses genuinely novel production that crossed the detection limit. The control-specific code meanings apply to “The Environment” actor (organism_id = 1), and mixing control and organism codes without the actor distinction is wrong. Across organism observations, E and I were mutually exclusive, with zero overlap. The missing decreased or consumption action is by design rather than a query bug, so consumption cannot be inferred from these action codes. Unk_* compounds should be filtered out for named-metabolite analyses. The data are a frozen 2018 snapshot, and newer data live elsewhere (GNPS2). ₁

BacDive utilization is four-valued rather than binary: +, -, produced, and +/-. For Pseudomonas fluorescens, indole had 60 “produced” entries and 1 actual utilization test, illustrating why only explicit + and - observations should enter utilization percentages. The four values mean + (can utilize), - (cannot utilize), produced (organism produces it), and +/- (variable/ambiguous). Computing pct_positive from raw counts without excluding produced and +/- entries gives incorrect species-level utilization rates. The source therefore computes the percentage from explicit + and - tests only, and tracks all four categories separately (n_positive, n_negative, n_produced, n_ambiguous) for transparency. ₁

Coverage and sparsity

AlphaEarth environmental embeddings cover 83,227 of 293,059 genomes, or 28.4%. A separate quality check found 3,838 of 83,287 genomes, or 4.6%, with at least one NaN among 64 dimensions; filtering left 79,449 genomes. Coverage depends on valid latitude and longitude, which are especially sparse for clinical isolates. The ncbi_lat_lon field in gtdb_metadata is often NULL (the most common case), a malformed string such as “missing” or “not collected”, or low precision, as in “37.0 N 122.0 W”. Joins to the embeddings should therefore use LEFT JOIN with an explicit coverage check. ₁

Per-genome NCBI environment classification yielded 52.7% unknown labels, or 94,957 of 180,025 genomes, because most BioSample records lack structured isolation metadata. Species-level majority-vote labels reached 91% coverage through keyword classification. ₁

AlphaEarth geographic analyses can be diluted by human-associated samples. The pooled geographic distance–embedding distance curve had a 2.0x near-versus-far ratio, while environmental samples had 3.4x and human-associated samples had 2.0x; analyses should therefore stratify by environment category or exclude human-associated samples when embeddings are used as environmental proxies. ₁

Scale, typing, and Spark behavior

Many KBase Data Lakehouse numeric fields are strings, including all Fitness Browser (kescience_fitnessbrowser) columns, kbase_genomes coordinates and lengths, and relevant pangenome and genome metadata fields. Values must be explicitly cast before comparisons, ordering, arithmetic, or aggregation. Spark DECIMAL values arrive in pandas as decimal.Decimal; CAST(... AS DOUBLE) in SQL or .astype(float) after collection prevents mixed-type arithmetic failures. For example, abundance in kbase.nmdc_arkin.centrifuge_gold is a DECIMAL column, and arithmetic with float values, such as those from AVG() aggregates, raises TypeError: unsupported operand type(s) for *: 'float' and 'decimal.Decimal'. AVG(CASE WHEN condition THEN 1.0 ELSE 0.0 END) also returns DECIMAL, because Spark treats the literal 1.0 as DECIMAL(2,1), not DOUBLE. The stated rule of thumb is to wrap any AVG() over integers or decimal literals in CAST(... AS DOUBLE) and to add .astype(float) after .toPandas() as a defensive safety net. ₁

SELECT DISTINCT col, COUNT(*) without GROUP BY fails in Spark strict mode with MISSING_GROUP_BY; GROUP BY col alone is the correct replacement. Spark Connect’s SQL analyzer rejects SELECT DISTINCT combined with an aggregate function when no GROUP BY is present, reporting MISSING_GROUP_BY with SQLSTATE 42803. Spark Connect temporary views can be silently destroyed by a server reconnect during a long-running query, for example one triggered by an expensive 305M-row full-table scan; subsequent cells that join against the lost view return 0 rows with no error. Views should therefore be re-registered immediately before any cell that uses them in a join; the Python variable holding the data persists in the kernel even when the Spark server reconnects. ₁

The pangenome contains billion-row tables, including gene and gene_genecluster_junction, each approximately 1B rows; genome_ani is approximately 421M rows; eggnog_mapper_annotations approximately 93M rows; interproscan_domains approximately 833M rows; bakta_db_xrefs approximately 572M rows; and gapmind_pathways approximately 305M rows. These tables require key filters before joins, and results should remain in Spark until the final small output. ₁

A Spark Connect driver result-size cap of 1 GB serialized data makes large .toPandas() collections unsafe. For example, a filtered contig-feature result involving 218K Bacteroidota contigs produced more than 30M rows and approximately 1.5 GB serialized; MinIO parquet staging or server-side aggregation is required. Pandas spatial merges can also exceed 9 GB of working memory at approximately 24M intermediate rows, and iterrows() over 27K focal features took more than 30 minutes where a vectorized merge completed in approximately 10 seconds. The cap is spark.driver.maxResultSize = 1024 MB. In the cited case, 1B-row tables (for example kbase.genomes.feature × contig_x_feature × pangenome.gene_genecluster_junction) were joined and filtered to >200K elements via broadcast, and calling .toPandas() on the result threw Total size of serialized results bigger than spark.driver.maxResultSize. The cap cannot be raised with SET spark.driver.maxResultSize at runtime, because the property is read-only after session start. The source’s scale estimate is 218K Bacteroidota contigs × ~140 features/contig = 30M+ rows × ~50 bytes serialized ≈ 1.5 GB, which it calls a standard pattern in any project doing genome-context cross-walks at >200 species. The workaround writes the Spark result to MinIO with df.coalesce(N).write.mode("overwrite").parquet("s3a://cdm-lake/...") and reads it back with spark.read.parquet(path).toPandas(). The read-back goes through Spark Connect’s parquet streaming path and is reported not to hit the cap. The stated alternative is to read the file directly with pyarrow.parquet.read_table(path) via s3fs, if PyArrow is configured. Driver memory also limits work after collection. An in-pandas spatial-range merge (focal features × contig features on contig_id, then filtering by ±NEIGHBOR_BP) creates a Cartesian-product-then-filter pattern. For 80K focal × 21K contigs × ~105 features/contig avg, the merged DataFrame was ~24M rows × 9 columns ≈ 9 GB working set, which ran out of memory on a 16 GB driver. In P4-D2 NB26h, a Bacteroidota PUL gene-neighborhood analysis, 723K focal × 210K contigs failed, and sampling down to 309 species still ran out of memory at 80K × 21K. A PSII analysis at 27K focal × 16K contigs with a 16M-row merge fit in memory, whereas the PUL and mycolic analyses at full scale did not. The proposed workarounds are to process focal features in batches of ~10K, merging per batch and accumulating the aggregate result, or to push the spatial-range filter (the BETWEEN clause) into Spark and collect only per-feature aggregates to the driver. Row-wise iteration is a separate bottleneck. Stage 6 of the gene-neighborhood pipeline (NB26e) used for _, row in focal_features.iterrows() with a per-row groupby lookup over 27K focal features × 218K contigs, and the loop ran 30+ minutes without completing. A vectorized pd.merge(focal_features, contig_features, on="contig_id") followed by a boolean filter on the merged frame completed the same operation in ~10 seconds. ₁

Disabling spark.sql.autoBroadcastJoinThreshold with -1 can harm performance: an NB10 job joining 13.7M rows with 18,989 species-taxonomy rows hung for 17+ minutes when automatic broadcasting was disabled. The optimizer should generally be trusted, with explicit broadcast hints only when needed. The disabling setting was a defensive carry-over from prior projects, intended to “force shuffle joins”. The hung NB10 KO atlas job, a 13.7M × 18K join, was waiting for a shuffle that never materialized. Its small side (18,989 species_tax rows) is well below the default 10MB broadcast threshold and would have been auto-broadcast had the setting been left at its default. The stated workaround is to remove the disable, trust the optimizer for small-table joins, and use explicit F.broadcast(small_df) hints when the optimizer does not auto-detect a small table. ₁

Long-running JupyterHub kernels can be silently killed after approximately 17–25 minutes without user activity. The recommended recovery pattern is to convert notebooks to scripts, run them with nohup python3 -u, write intermediate parquet checkpoints, and provide finalization scripts. jupyter nbconvert --inplace can also exit successfully while dropping all cell outputs; writing to a new executed notebook or logging a standalone script is safer. The idle timeout affects JupyterHub kernels that have received no user activity for ~17–25 minutes. Long-running cells, such as Spark jobs taking 30+ min like NB10 KO atlas construction or NB10b M22 attribution at 17M events, are silently killed mid-execution, and the kernel disappears with no error message. The reported workaround pattern converts the notebook to a standalone .py script (jupytext --to py 10_p2_ko_atlas.ipynb), runs it via nohup python3 -u <script>.py > /tmp/<name>.log 2>&1 & from a terminal rather than a JupyterHub kernel, writes intermediate parquets at each stage so partial results are recoverable from disk, and provides _finalize.py recovery scripts that load the intermediates and complete the work from where the killed run stopped. Headless nbconvert kernels have get_spark_session() available, but long-running notebooks may time out unless --ExecutePreprocessor.timeout is set appropriately. The source also recommends designing long pipelines with checkpointing, saving intermediate files and skipping steps that already have output, so that they can be re-run after interruptions without repeating completed work. On this JupyterHub, jupyter nbconvert --to notebook --execute --inplace ... can exit with code 0 yet leave the notebook file on disk with zero cell outputs. --inplace therefore should not be relied on to capture outputs. The source recommends writing to a separate file via --output or, for long-running production ingests, running the equivalent logic as a standalone Python script with stdout and stderr streamed to a log file. ₁

Database-specific interpretation rules

GapMind score_simplified at sequence_scope = 'core' is binary rather than a continuous confidence score, containing only 0.0 (pathway incomplete) and 1.0 (pathway complete), a pitfall recorded from pgp_pangenome_ecology. That note describes gapmind_pathways as genome-level, with one row per genome-pathway pair, to be aggregated to species level with MAX(score_simplified) GROUP BY clade_name, pathway before joining to species data; this differs from the source’s separate statement, below, that the broader table has multiple rows per genome-pathway pair. The broader GapMind table has multiple rows per genome-pathway pair, with categories complete, likely_complete, steps_missing_low, steps_missing_medium, and not_present; the best score must be selected before pathway-level aggregation. The stored metabolic categories are aa (amino-acid pathways) and carbon (carbon-source pathways), not amino_acid, so filter code using metabolic_category == 'amino_acid' returns 0 rows. Core-scope scoring is unsuitable for broad taxonomic comparisons. Using gapmind_pathways with sequence_scope = 'core' in a broad taxonomic comparison yielded 0% completeness across all compartments, because the core-level threshold is too stringent for genus-level or cross-species comparisons in which pathway genes may not be universally present. The document recommends max-aggregation at the genus level instead. ₁

Pangenome core, auxiliary, and singleton flags are integer 0/1 values. Core and auxiliary counts sum to total gene clusters, while singletons are a subset of auxiliary clusters; the flags themselves are mutually exclusive. The pangenome identities are no_core + no_aux_genome = no_gene_clusters, with no_singleton_gene_clusters contained within no_aux_genome. In gene_cluster, is_core = 1 denotes presence in ≥95% of genomes, is_auxiliary = 1 denotes presence in <95% of genomes but more than one genome, and is_singleton = 1 denotes presence in exactly 1 genome. Only one flag is 1 per row, and the flags are stored as integers rather than booleans. The per-row flags therefore exclude singletons from the auxiliary flag, whereas the pangenome-level no_aux_genome count includes singletons, so ‘auxiliary’ is defined differently at the two levels. ₁

Fitness Browser data requires exact case for orgId, filtering by organism because genefitness has approximately 27M rows, and casting string-valued fit and t, for example CAST(t AS DOUBLE) before any ABS(...) or comparison. There is no single (orgId, locusId) to KO table in kescience_fitnessbrowser; KO mapping is a two-hop join in which besthitkegg maps each locus by best hit to a KEGG organism and gene (keggOrg, keggId) and keggmember maps each (keggOrg, keggId) pair to its KEGG ortholog group (kgroup), and neither table alone is sufficient. kgroupdesc uses desc rather than description (desc AS description gives a tidier alias), and the experiment grouping column is expGroup; SELECT Group fails with an UNRESOLVED_COLUMN error. Essential genes are absent from transposon fitness records: genefitness-only analyses miss approximately 14.3% of protein-coding genes, while genes absent from genefitness provide only an upper bound on essentiality. The string-typed numeric columns include fit, t, begin, end, cofit, rank, ratio, and protein_length, which need a cast such as CAST(fit AS FLOAT) before any numeric comparison or ORDER BY. orgId must be written in exact case, for example 'Keio' rather than 'keio'. cofit has ~13.6M rows, so genefitness and cofit should both be filtered by orgId, and ideally by locusId, before scanning. Gene joins are composite on (orgId, locusId), because joining on locusId alone collides across organisms. Per-organism fitbyexp_{orgId} tables, such as fitbyexp_keio and fitbyexp_dvh, are pre-pivoted convenience tables with non-standard schemas that must be discovered per organism. Attempting to join kescience.fitnessbrowser.organism to kbase.ke_pangenome.gtdb_metadata via NCBI taxonomy IDs returned zero matches, because the ncbi_taxid (or equivalent) column in gtdb_metadata contains the boolean string tokens "t" / "f" rather than numeric taxonomy IDs. The suggested alternatives are another join key, such as organism-name string matching or an orgId-based lookup, or a different taxonomy column; in metabolic_capability_dependency, the fallback was to match organisms directly by orgId without a clade-level link. ₁

Phylogenetic tree distance tables use bare accessions such as GCA_001038305.1, while other pangenome tables use GB_ or RS_ prefixes; stripping the first three characters produced 100% overlap in tested species, including 399/399 for Koxy and 287/287 for Btheta. Large ANI (average nucleotide identity, a pairwise genome-similarity measure) analyses are quadratic: Klebsiella pneumoniae has 14,240 genomes and Staphylococcus aureus has 14,526, and ANI queries for species with >500 genomes can take 30+ minutes per species and may time out Spark connections, because these species generate IN clauses with 14K+ IDs that overwhelm the query planner (recorded from amr_strain_variation); analyses should therefore cap samples at <=500 genomes for Mantel tests or subsample. genome_ani contains only within-species comparisons; cross-species ANI is not stored. Its columns are genome1_id, genome2_id, and ANI, not genome_id_1, genome_id_2, and ani, and the uppercase ANI capitalization matters. ₁

NMDC classifier and metabolomics tables use file_id, but their file-id namespaces do not overlap: classifiers use nmdc:dobj-11-* and metabolomics uses nmdc:dobj-12-*. They must be bridged through sample_id using omics_files_table, which contains 385,562 rows and includes file_id, sample_id, study_id, workflow_type, and file_type. The NMDC taxonomy_features table is a wide matrix with numeric taxon-ID columns rather than tidy sample identifiers. ₁

NMDC abiotic_features uses mostly _has_numeric_value column suffixes, has no annotations_water_content column, and stores unmeasured variables as 0.0 rather than NULL; zeros should be converted to NaN before analysis. metabolomics_gold compound identifiers include kegg, chebi, name, inchi, inchikey, and smiles, while annotation_terms_unified is a gene-annotation lookup rather than a metabolite lookup. ₁

Reproducibility and workflow hygiene

Three ENIGMA analysis notebooks, NB08, NB09, and NB10, initially existed only as committed outputs rather than notebook history. The analyses had been run interactively, so their figures and data files were committed while the generating code existed only in the session transcript. The project therefore looked reproducible to reviewers reading the README and REPORT, even though git log held no history for those notebooks. Reconstruction took a few hours once the gap was identified. Every analysis worth saving should have a committed notebook, and git log --all --oneline -- projects/{project_id}/notebooks/NB*.ipynb should be checked before submission. ₁

Concurrent agent sessions must use separate Git worktrees or verify git branch --show-current immediately before every commit. In the documented 2026-04-24 incident, a checkout by another process moved a commit from ibd_phage_targeting onto plant_microbiome_ecotypes; the files were recoverable from Git, but the working tree appeared to lose them. Sessions in one clone share a single .git directory and working tree. A checkout by one session therefore silently reshapes the other session’s files, and later commits land on whichever branch HEAD currently points at. ₁

The observatory stores computed outputs flat in data/, not in a data/results/ directory. Detailed synthesis belongs in REPORT.md, while README.md should remain a concise overview with status, links, metadata, reproduction information, and a link to the report. ₁

Notebook and environment failures include numpy str_ values, such as those returned by np.random.choice() on a list of Python strings, that PySpark createDataFrame() cannot infer (raising PySparkTypeError: [CANNOT_INFER_TYPE_FOR_FIELD]), invalid notebook cells missing outputs, non-UTF-8 SQLite text that requires a permissive text_factory, SQL schema parsers that mistake /* ... */ comments for columns, broken symlinks to local Mac paths, Python 3.13 compatibility problems with statsmodels.api, and headless kaleido 1.x requiring Chrome. The stated remedies are native str conversion, notebook JSON validation, permissive decoding, comment stripping, broken-symlink removal, statsmodels.formula.api, and kaleido 0.2.1. ₁

Exporting a SQLite database to TSV with sqlite3.connect() fails when TEXT columns contain non-UTF-8 byte sequences, because Python’s sqlite3 module decodes strictly and raises OperationalError: Could not decode to UTF-8 column ... during cursor iteration, before any row-level cleaning such as _clean() runs. The workaround sets conn.text_factory = lambda b: b.decode('utf-8', errors='replace') immediately after connecting and before executing any queries. Iteration then succeeds, but invalid bytes become U+FFFD rather than being preserved. ₁

The ingest pipeline’s parse_sql_schema() extracts column definitions from CREATE TABLE statements with a regex; if the table body begins with an inline comment, such as /* geneId is actually a fully specified protein id like YP_006960813.1 */, the parser treats /* as a column name. The resulting invalid schema string, such as /* STRING, geneId STRING, ..., causes the pipeline to skip the entire table silently, with no error message or quarantine entry. MySQL dumps raise further ingestion problems. The described data files have no header row, so column names come from the CREATE TABLE statement rather than the first line. NULL is encoded as the literal \N, not as an empty string or NULL. A MySQL-specific DDL trailer, such as ) ENGINE=InnoDB AUTO_INCREMENT=... DEFAULT CHARSET=utf8mb3;, follows the column list. The ingest_lib.parse_sql_schema regex \)\s*; requires the closing parenthesis to be followed only by whitespace and a semicolon, so it does not match these schemas. For the schema, the proposed preprocessing strips the ENGINE=...; trailer, keeps just CREATE TABLE name (cols);, and concatenates all per-table .sql files into one schema.sql. For the data, it streams each .txt file through csv.reader (comma-delimited, doublequote=True), replaces \N with an empty string, and writes TSV with a prepended header derived from the CREATE TABLE. The cited reference implementation (~/data/genome_depot_enigma/preprocess.py) reportedly handles 13 GB / 32 tables in ~5 minutes. ₁

Annotation queries, orphan records, and strain representativeness

Querying bakta_pfam_domains with Pfam accessions such as 'PF00771' returned 0 hits across all 11 domains tested. The table may use domain names or versioned accessions such as PF00771.1, or it may require a different join logic; the correct query format had not been established. These zero hits therefore reflect an unresolved query-format problem and should not be read as evidence that the domains are absent. Related checklist items require verifying Pfam accessions against InterPro or UniProt rather than assuming them from names, and searching the eggNOG PFAMs column by domain name rather than by accession. The source records the mappings DUF4041 = PF13250 (IPR025280), not PF13291 (ACT_4), and SNIPE nuclease = PF13455 (Mug113, GIY-YIG clan CL0418), not PF01541 (canonical GIY-YIG). The eggnog_mapper_annotations.PFAMs column stores Pfam domain names (for example DUF4041, GIY-YIG) rather than Pfam accession IDs (for example PF13250, PF01541), so searching by accession returns 0 results. WHERE PFAMs LIKE '%PF13250%' returned 0 rows, whereas WHERE PFAMs LIKE '%DUF4041%' returned 2,962. The column holds comma-separated lists of domain names, so matching should use LIKE with % wildcards rather than exact equality. Looking up Pfam accessions by domain name alone can also give the wrong accession. PF13250 and PF13291 are numerically close but completely unrelated families, and PF01541 (canonical GIY-YIG) and PF13455 (Mug113) are distinct families in the same clan. Symptoms include zero co-occurrence hits for domains that should co-occur, such as DUF4041 + GIY-YIG in SNIPE proteins, or unexpected functional annotations for domain hits. Such symptoms can reflect a mistaken accession lookup rather than biological absence. The stated fix is to verify Pfam accessions against a known protein in InterPro or UniProt. ₁

In GenomeDepot-based databases (protect_genomedepot, phagefoundry_paeruginosa_genome_browser), pslA–pslO genes are rarely annotated with canonical gene names; only PAO1’s reference genome has those canonical-name annotations. Pangenome bakta_annotations has better coverage, but pslA, pslG, and pslK annotations remain sparse. KEGG orthologs K20997–K21005 via eggnog_mapper_annotations gave ~95% prevalence for psl detection versus <1% by gene name alone. ₁

The pangenome table references 12 species clades that are absent from gtdb_species_clade; these are orphan single-genome species or symbionts that are valid pangenomes but are filtered from species metadata, so they should be handled with a LEFT JOIN, and an anti-join may be needed to identify them. gene_genecluster_junction has 141 orphan genes relative to gene, and an INNER JOIN silently drops them. pangenome_build_protocol, genomad_mobile_elements, and IMG_env are referenced by other tables or project documents but do not exist in the KBase Data Lakehouse, which leaves protocol_id as a dangling reference. ₁

Codon-usage bias (CUB) is described as valid within a species, for example when comparing PA strain groups. Laboratory growth data should be used as ground truth for cross-species growth-rate comparisons. In cf_formulation_design, cross-species CUB comparisons, such as PA versus commensals, were confounded by GC content variation of 31–73%. Organisms with extreme GC composition have inflated CUB scores regardless of growth optimization, so the document limits meaningful CUB comparisons to within species or across species with similar GC content. ₁

In cf_formulation_design, PA14 (ExoU+, Pel-only, ladS mutant) represents <5% of cystic-fibrosis Pseudomonas aeruginosa isolates by virulence genotype, whereas CF isolates are 94% ExoS+ and 92% Pel+Psl+ (PAO1-like). PA14-based inhibition assays therefore test the formulation against a minority variant. Amino-acid catabolism showed no FDR-significant (false-discovery-rate-controlled) differences between ExoU+ and ExoS+ strains, so the competitive-exclusion mechanism is expected to transfer. This expectation is an inference, and the report states that direct validation against ExoS+ strains is still needed before PA14 results are generalized to CF. Also in cf_formulation_design, the PROTECT Gold tables fact_pairwise_interaction and fact_carbon_utilization contained identical values (correlation = 1.0, mean difference = 0.0). The endpoint optical-density (OD) data therefore do not capture co-culture metabolic interactions, and per-substrate co-culture analysis is impossible from them. NB08 discovered this and switched to the RFU-based competition assay as the only source of pairwise interaction effects. ₁

Biochemistry, structure, phenotype, literature, and genome-database rules

In the ModelSEED biochemistry tables, reaction has no EC-number column, and no EC-to-reaction mapping exists in the KBase Data Lakehouse. The document suggests bridging through eggNOG KEGG_ko or KEGG_Reaction to reaction.abbreviation (KEGG R-numbers), or using GapMind pathways for pathway-level analysis. That bridge is incomplete: only ~80% of reactions (44,904 / 56,012) populate reaction.abbreviation, and the other 20% have no external ID at all. The BiGG_Reaction field referenced by eggNOG annotations is absent from the ModelSEED tables, so no biochemistry-side BiGG mapping exists. ModelSEED IDs use a prefix-colon form, such as seed.reaction:rxn00001 and seed.compound:cpd00001, rather than bare rxnNNNNN. reaction_similarity is ~671M rows and should always be filtered by reaction_1 or reaction_2. deltag has extreme outlier values, and aggregations need a sanity bound such as deltag > -10000000. Many molecule rows lack SMILES or InChIKey, so structure should be joined with LEFT JOIN, because an INNER JOIN would drop those molecules. ₁

In the AlphaFold metadata, ~9% of entries have MSA (multiple sequence alignment) depth < 30 and are treated as low-confidence predictions, so structure-based analyses should filter on msa_depth. All entries are model version v6, and the model_version column currently has no variation. Coverage is UniProt-only, so proteins absent from UniProt have no AlphaFold entry. Per-residue pLDDT confidence is stored in the structure files rather than in this metadata. Joining UniRef100 to UniProt requires stripping the UniRef100_ prefix with REPLACE(uniref100, 'UniRef100_', '') = uniprot_accession. This is a per-row string operation, so large joins may benefit from a materialized mapping table. ₁

In the PDB tables, r_work and r_free are NULL for non-X-ray methods (EM, NMR), and resolution is NULL for NMR. Method-specific filters are therefore needed before averaging. pdb_uniprot_mapping.pdb_beg and pdb_end are strings and can literally contain "None" for unmapped regions, so they must be coerced or filtered before casting. pdb_entries.organism reflects only the first polymer entity, which makes multi-organism complexes appear single-organism. pdb_entries has one row per entry rather than per chain, so chain-level data must come from pdb_uniprot_mapping. SIFTS mapping coverage is SwissProt/TrEMBL only, which leaves some PDB chains with no UniProt mapping. The four Phenix structural-biology tables start empty and grow only with Phenix agent usage, so cross-project queries return nothing until projects accumulate. In refinement_cycles, the X-ray metrics (r_work, r_free, r_gap) are NULL for cryo-EM rows, and the cryo-EM metric (map_model_cc) is NULL for X-ray rows. parameters_json is a string containing JSON, so its inner fields require JSON parsing functions rather than column access. ₁

BacDive renames taxonomy columns to tax_class and tax_order to avoid the SQL reserved words class and order. BacDive sequence_info.accession uses GCA_*, whereas pangenome genome_id uses GB_GCA_* or RS_GCF_*, so prefixes must be stripped or prefix-matched before joining. NCBI-taxid links from BacDive to the Fitness Browser are species-level rather than strain-level, which creates many-to-one ambiguity. Physiology fields are sparse, with Gram stain ~15K/97K and oxygen ~23K/97K, so IS NOT NULL filters are usually required. The metabolite test panel is biased toward trehalose, arginine, and glucose, which makes frequency-based ranking misleading. ₁

In PaperBLAST, year is a string and needs CAST(year AS INT) for comparisons or ORDER BY. protein_length in curatedgene and uniq is also a string and must be cast before numeric operations. isOpen in genepaper is mostly NULL and should not be used to filter for open-access papers. genepaper is ~3.2M rows and should be filtered by geneId or pmId rather than full-scanned. geneId spans multiple namespaces, including RefSeq NP_*, WP_*, and VIMSS, so cross-namespace queries must go through seqtoduplicate. snippet.snippet is raw PMC text with formatting artifacts and partial sentences, and it should not be displayed unfiltered. ₁

In the KBase genomes database, all primary keys are CDM UUIDs rather than human-readable identifiers. An external gene_id, such as a pangenome gene_id, maps to internal IDs through the name table (name.name = '<external_id>' → name.entity_id). There is no direct genome_id column: genomes are identified by contig_collection_id and cross-referenced via name or gtdb_taxon_id. Numeric columns, including length, start, end, gc_content, and coordinates, are stored as strings and must be cast before comparing. feature, encoded_feature, and the *_x_* junction tables are each ~1B rows and must never be scanned without a key filter. protein is deduplicated, with 253M unique sequences for ~1B features, so many features share the same protein_id. ₁

In NMDC Arkin, abiotic_features numeric measurement columns, such as annotations_ammonium_has_numeric_value, annotations_ph, and annotations_temp_has_numeric_value, are described as strings that need casting before numeric comparison. This is one side of the string-versus-double inconsistency noted under Caveats. trait_features exposes ~90 functional groups as individual functional_group:* columns rather than rows, so long-form analysis requires stack(...) rather than GROUP BY. Embedding dimensions vary across NMDC Arkin tables: embeddings_v1 is 256-dim, while the abiotic, biochemical, taxonomy, and trait embeddings differ, so a fixed dimensionality must not be assumed. ₁

In enigma_coral, tables follow a strict prefix scheme: sdt_* are scientific data, ddt_* are numerical bricks or arrays, and sys_* are system or metadata tables that are not primary scientific data and should not be queried for biology. ddt_brick* columns hold numerical arrays with a non-tabular shape, so standard SQL projections may not work as expected. Many enigma_coral tables timed out during introspection at ingest, so schemas may be partial and should be confirmed with DESCRIBE EXTENDED before queries are written. ₁

PhageFoundry comprises five databases: phagefoundry_acinetobacter_genome_browser, phagefoundry_klebsiella_genome_browser_genomedepot, phagefoundry_paeruginosa_genome_browser, phagefoundry_pviridiflava_genome_browser, and phagefoundry_strain_modelling. The four genome browsers share the same GenomeDepot schema; a separate table in the source describes them under the pattern phagefoundry_{species}_genome_browser_genomedepot (a suffix that not every listed database name carries) as holding genome annotations only (eggNOG, COG, Pfam) in browser_* tables (10+ tables each). phagefoundry_strain_modelling is separate; one passage describes its schema as unknown, while another lists 18 tables (organism, interaction, feature, etc.) holding experimental phage-host interaction data plus an ML model. Because the strain-modelling database contains the actual experimental results (Gaborieau et al. 2024, Nature Microbiology), searching only the genome browsers would miss them. Its strainmodelling_gene table has 933K genes but only locus_tag and protein_seq columns, with no gene_name, product, or functional-annotation fields, so genes cannot be searched by name; the workaround is the strainmodelling_feature and strainmodelling_protein_family tables, which link gene clusters to ML model features with SHAP importance scores. There is no unified table, so cross-species queries must run the same SQL once per database. protect_genomedepot exposes sampled table variants (browser_gene_sampled, browser_annotation_sampled) instead of full tables, and queries should use the sampled tables rather than the unsuffixed names. ₁

kbase_uniref50, kbase_uniref90, and kbase_uniref100 are partial or sample datasets rather than full UniRef releases, which would contain hundreds of millions of clusters, so no cluster lookup should assume completeness. ₁

PlanetMicrobe is available as a curated database (planetmicrobe_planetmicrobe) and a raw database (planetmicrobe_planetmicrobe_raw). In the PlanetMicrobe databases, the project and library tables are empty (count=0), so joins through them return nothing. The _raw database mirrors the curated one structurally, so each query should pick one explicitly. Both the NMDC covstats_taxonomy_rollup and Planet Microbe run_to_taxonomy abundance tables key on a taxonomy.name that is species-level, not genus-level. In enigma_carbon_census_1, an initial marine-abundance contrast showed 0/68 genera present in Planet Microbe, not because the genera were absent but because the genus-name filter matched only genus-rank reference rows instead of aggregating their constituent species. The stated solution is to aggregate species to genus before filtering: derive the genus from the species name, GROUP BY genus, SUM(abundance), and only then filter to the genera of interest. ₁

Query performance, pandas handling, Fitness Browser schema, and module-pipeline details

The source recommends direct spark.sql() on the cluster rather than the REST API when a query involves >1M rows, joins across large tables, aggregates billion-row tables, or keeps timing out through the REST API. Most junction tables in the KBase genomes database have ~1 billion rows and should never be queried without filters. ₁

Joining gene → gene_genecluster_junction → gene_cluster → eggnog_mapper_annotations can be slow for species with >500 genomes. Building genome × cluster presence matrices by joining gene_genecluster_junction (~1B rows) with gene (~1B rows) took 3-5 minutes per species even on Spark, because neither table is partitioned by gene_cluster_id or genome_id and every query requires a full table scan. In profiling for Smeli (241 genomes, 6K target clusters), extraction took ~300s per organism without broadcast, ~274s (8% improvement) with /*+ BROADCAST(tc), BROADCAST(tg) */ on the filter tables, and ~310s (no improvement) with a two-stage approach that filtered the junction first and then looked up genome_ids; .toPandas() on 1-2M result rows took <2s and was not the bottleneck. The source attributes the cost to unpartitioned parquet storage and states that partitioning gene by genome_id and the junction by gene_cluster_id would dramatically reduce scan time but requires rebuilding the lakehouse tables; this remedy is proposed rather than tested. ₁

By contrast, gene_cluster is partitioned by gtdb_species_clade_id. In conservation_vs_fitness, extracting cluster-representative FASTAs across 154 clades with LIMIT N OFFSET M pagination (5000 rows per batch) was orders of magnitude slower than a single WHERE gtdb_species_clade_id = 'X' query per clade, because Spark re-scans all rows up to the offset on each query; the source advises paginating only when the result set would exceed memory. ₁

.toPandas() pulls all data from the Spark cluster to the driver node, which is slow for large results and can cause out-of-memory errors, so filtering, joins, and aggregations should be done in Spark first. A gene-cluster extraction for K. pneumoniae (250 genomes × ~5,500 genes per genome) via gene JOIN gene_genecluster_junction WHERE genome_id IN (...) succeeded in Spark but exceeded Spark’s spark.driver.maxResultSize (1GB default) when results were collected to the driver. For species with >200 genomes the source recommends chunking the genome list into batches of 50-100 and concatenating results, or alternatively increasing spark.driver.maxResultSize in the Spark session config; the per-species extraction script catches this error and continues to the next species. The session-config suggestion should be read alongside the separate note above that the cap cannot be raised with SET at runtime because the property is read-only after session start. ₁

In NB28b tree-based donor inference (M26), computing per-(donor genus × KO) donor-candidate event counts by exploding 6.3M gain events × ~20 candidate-donor genera per event produced a 126M-row exploded DataFrame of ~19 GB and ran out of memory. An algebraic identity replaced the explode: for each (family F × KO K) with R_FK total recipient events, every genus G in F that has K present is a candidate donor for (R_FK − recipient_events_for_G), computable as a single join plus subtraction; Stage 2b then ran in 142s instead of running out of memory. ₁

The source’s generalizable rule is never to use iterrows() for >10K rows and to vectorize through merge plus boolean filter or numpy array operations; it asserts that the 1000× speedup is consistent across pandas use cases, a general claim not separately demonstrated beyond the cited cases. In core_gene_tradeoffs, filtering a 961K-row DataFrame with a row-wise df.apply(lambda r: (r['orgId'], r['locusId']) in some_set, axis=1) took minutes, whereas df.merge(keys_df, on=['orgId','locusId'], how='inner') gave the same result in seconds. ₁

Numeric columns from Spark can arrive as strings after .toPandas() and need explicit conversion before numeric comparison. In cog_analysis, mapping COG (Clusters of Orthologous Groups) categories to descriptions with a dictionary returned NaN for composite categories such as “LV” and “EGP” that were not in the dictionary, and string operations on those NaN values failed; the fix is to check pd.notna() or pd.isna() before string slicing or other operations on potentially-NaN columns. After merge(..., how='left') creates a boolean flag column, unmatched rows get NaN, and .fillna(False) leaves the column as object dtype rather than boolean, so the ~ operator produces silently wrong results instead of boolean negation. In module_conservation, merging module_families.csv with family_annotations.csv on familyId created duplicate n_modules_x and n_modules_y columns; explicit suffixes=('_obs', '_annot') avoids ambiguous column names in downstream analysis and saved TSV files. ₁

In the Fitness Browser (kescience_fitnessbrowser), every column is stored as a string and must be cast before numeric comparisons, orgId matching requires exact case (WHERE orgId = 'Keio', not 'keio'), and the genefitness table is large (27M rows). In fitness_modules, the fitbyexp_* tables were found to be long format (columns expName, locusId, fit, t), not pre-pivoted wide tables as the schema docs suggest, and equivalent to a per-organism slice of genefitness, which the source recommends using directly with an orgId filter. This contradicts the database-specific note above describing the same tables as pre-pivoted with non-standard schemas; the source’s own resolution is to inspect the live schema (see Caveats). ₁

keggmember has no orgId or locusId columns, only keggOrg, keggId, kgroup; the documented gene-to-KEGG join starts from besthitkegg (filtered by orgId, e.g. 'DvH'), joins keggmember on both keggOrg and keggId, and left-joins kgroupdesc on kgroup for descriptions. kgroupec uses the column ecnum, not ec. In pathway_capability_dependency, the experiments table is experiment (not exps), with columns expName (not name), expGroup (not Group), and condition_1 (not Condition_1); expGroup provides clean condition categories such as “carbon source”, “nitrogen source”, “stress”, “pH”, and “temperature” and is recommended over pattern matching on condition_1. The SEED role hierarchy requires the join chain seedannotation (orgId, locusId, seed_desc) → seedannotationtoroles (seed_desc → seedrole) → seedroles (seedrole → toplevel, category, subsystem), because seedannotationtoroles has only seed_desc and seedrole; seedroles columns are toplevel, category, subsystem, and seedrole. In fitness_modules, seedclass (columns orgId, locusId, type, num) was found to store EC numbers rather than SEED subsystem categories, so seedannotation (orgId, locusId, seed_desc) should be used for functional descriptions. ₁

In conservation_vs_fitness, Fitness Browser protein sequences at fit.genomics.lbl.gov/cgi_data/aaseqs used RefSeq-style locus tags for some organisms (e.g., ABZR86_RS00005 for Dyella79), while the Fitness Browser gene table uses the original annotation locus tags (N515DRAFT_0001); DIAMOND hits from databases built on aaseqs then join back to the gene table with silently zero matches. Dyella79 had 0% overlap, whereas other organisms had >94% overlap, so locusId overlap should be checked before analysis. ₁

After checking all 45 tables in kescience_fitnessbrowser, conservation_vs_fitness found no explicit essentiality column, insertion count, or “has fitness data” flag; gene.type encodes feature type (1=CDS, 5=pseudo, 7=rRNA, 2=tRNA), not essentiality. Putative essential genes are therefore identified by comparing gene (type=‘1’) with genefitness: genes absent from genefitness had no viable transposon mutants, which gives only an upper bound on true essentiality. fitness_effects_conservation notes that a genefitness-only analysis misses ~14.3% of protein-coding genes — the essential genes with no transposon insertions — which it describes as the most functionally important genes (82% core); fitness-versus-conservation analyses must include them from the gene table or acknowledge that the most extreme fitness category is missing. Because absence is only an upper bound, this group is putatively rather than confirmedly essential. ₁

In conservation_fitness_synthesis, the ”% core” value depended on the denominator: using all genes, including unmapped genes, gave lower percentages (e.g., essential = 82% core) than using mapped genes only (86% core), and different projects may use different denominators. This caused a critical review issue in which figures showed 86%/78% but prose said 82%/66%; the source does not identify which figures. It recommends always documenting the denominator and staying consistent within a synthesis or comparison. ₁

For the fitness_modules pipeline, FastICA (independent component analysis) on fitness matrices should keep n_components ≤ 40% of n_experiments, because higher ratios cause frequent convergence failures and each failed run hits max_iter (very slow); for an organism with 150 experiments, at most 60 components should be used. Because ICA weight distributions are intentionally heavy-tailed, the D’Agostino K² normality test always rejects normality, which led to a permissive z-threshold (2.5) admitting 100-280 genes into each module; the source instead recommends absolute weight thresholds (|Pearson r| ≥ 0.3 with the module profile, max 50 genes) and calls this the single most impactful parameter in the pipeline. Pairwise cosine distances (1 - |cosine_similarity|) for DBSCAN can contain tiny negative values from floating-point precision, which DBSCAN with metric="precomputed" rejects; np.clip(cos_dist, 0, 1, out=cos_dist) before clustering avoids this. ₁

Cross-organism ortholog groups must use BBH (bidirectional best hit) pairs extracted for all organisms in the analysis, not a pilot subset: using 5-organism orthologs for a 32-organism analysis produced 6x fewer module families and missed all families spanning 5+ organisms, because the ortholog graph gains transitive connections from each new organism. For module annotation, min_annotated=3 with KEGG KOs (~1.2 genes per KO) gave enrichment for only 8% of modules, since almost no KEGG term has 3+ genes in a single 5-50 gene module; lowering to min_annotated=2 (described as still valid with FDR, false-discovery-rate, correction) and adding PFam domains (814 terms with 2+ genes) alongside TIGRFam (88 terms) raised module annotation from 8% to 80%. These are project-specific results from fitness_modules. ₁

Before Benjamini–Hochberg FDR correction, p-value columns should be detected by a substring pattern (for example columns containing _p while excluding derived q-value columns) or an explicit p-column allowlist rather than a suffix-only rule. For notebook models, moderate bootstrap sizes (for example 250-400) with fixed seeds, and confidence-interval estimation restricted to key coefficients or endpoints, keep runtime practical while still giving uncertainty intervals. Notebooks committed with empty output cells, as happens when analysis is prototyped as Python scripts, defeat their purpose as the primary audit trail: the /synthesize skill reads notebook outputs to extract results, the /submit reviewer checks outputs to verify claims, and human readers rely on outputs to follow the analysis without re-running it. ₁

In the KBase genomes database all primary keys are CDM UUIDs, and the name table maps external gene IDs to them. A table-status audit dated 2026-02-11 recorded phylogenetic_tree (species trees from core genes) and phylogenetic_tree_distance_pairs (pairwise phylo distances) as now available, while pangenome_build_protocol, genomad_mobile_elements, and IMG_env were not found, although a protocol_id column exists. phylogenetic_tree_distance_pairs uses bare NCBI accessions (GCA_001038305.1, GCF_000005845.2), whereas genome, gene, and gene_genecluster_junction use GTDB-prefixed IDs (GB_GCA_001038305.1, RS_GCF_000005845.2), so a direct join with presence matrices produces zero matches because no IDs overlap. Stripping the GB_ or RS_ prefix (first 3 characters) gives 100% overlap for all tested species (399/399 for Koxy, 287/287 for Btheta, etc.). ₁

Of 83,287 AlphaEarth genomes, 3,838 (4.6%) have NaN in at least one of the 64 embedding dimensions, and UMAP, cosine distance, and other operations will fail or silently produce NaN; filtering with ~df[EMB_COLS].isna().any(axis=1) before any embedding computation reduces the dataset from 83,287 to 79,449 genomes. Geographic distance–embedding distance analyses should be stratified: the pooled near-versus-far ratio of 2.0x blends environmental samples (3.4x, strong signal) with human-associated samples (2.0x, weak signal) and so underestimates the true geographic signal. At minimum, results should be computed separately for environmental and human-associated samples, and excluding human-associated samples entirely should be considered when embeddings serve as environment proxies, for example in ecotype analysis. ₁

Additional correctness, bridging, and validation pitfalls

A boolean-filtering error produced an orphan essential gene count of 41,059, which was the total number of essentials, instead of 7,084 actual orphans. The source describes this as a silently incorrect result with no error message. ₁

In NB02, setting feats_relaxed = feats_strict.copy() produced numerically identical strict and relaxed downstream outputs. Mapping-mode sensitivity conclusions are then invalid, because the two modes are no longer independently constructed. The stated fix is to compute strict and relaxed features independently. A scalable pattern precomputes clade-level annotation counts once on the union of clades and then aggregates those counts separately per mode. ₁

ENIGMA taxonomy in the dataset ddt_brick0000454 currently includes only the Domain through Genus levels. Species-level bridge logic applied directly to it will either fail or silently reuse genus-level mappings while appearing to have higher resolution. One stated option is an explicit species-proxy mode, in which a genus maps uniquely to a single GTDB clade, with the resulting loss of mapped coverage reported. ₁

A multiple-testing correction that targets only columns ending in _p misses p-value columns named like adj_cov_p_contamination or adj_frac_p_contamination. In metal_specificity, sequential statistical tests, such as a Fisher exact test for H1c followed by a Fisher exact test for H1d, reused generic variable names like odds, p = stats.fisher_exact(table). The second test overwrote the first, and a downstream summary cell printed the wrong values under the wrong labels. ₁

Running umap.UMAP(metric='cosine') on 83K genomes with 64 dimensions took >60 minutes and did not complete on a single-CPU JupyterHub pod. UMAP (uniform manifold approximation and projection) is a dimensionality-reduction method, and the source attributes the cost to the O(n^2) pairwise precomputation that the cosine metric requires. The reported solution L2-normalizes the embeddings and then uses metric='euclidean'. For L2-normalized vectors Euclidean distance is monotonically related to cosine distance (||a-b||^2 = 2(1-cos(a,b))), so the source states that the UMAP topology is equivalent. It also fits UMAP on a 20K subsample (reducer.fit(subsample)) and then transforms the full dataset (reducer.transform(all_data)). Together these changes reduced runtime from >60 min to ~7 min in this single reported analysis. ₁

An initial GapMind analysis counted exactly 80 pathways for every species with 0% present. The analysis looked for score_category = 'present', a category that does not exist. GapMind has exactly 80 pathways total, and each genome-pathway pair has multiple rows, one per step or component in the pathway. The correct approach takes the best score for each genome-pathway pair before aggregating to species level. The score categories are complete, likely_complete, steps_missing_low, steps_missing_medium, and not_present; GapMind has no simple binary present flag. ₁

Keyword matching on RAST function descriptions, such as if 'protocatechuate' in func.lower(), misclassified enzymes with non-obvious names. ACIAD1710 (4-carboxymuconolactone decarboxylase, EC 4.1.1.44) is a core pca pathway enzyme, but it was classified as “Other” because the keyword list checked for “muconate” and not “muconolactone”. The stated solution uses keyword categorization only as an initial pass and then validates it with co-fitness correlations. In this case co-fitness analysis correctly recovered pcaC (r=0.978 with the Aromatic pathway). Keyword classifiers for gene functions should be tested against known members of each category, with missing synonyms added. ₁

Using n_sick >= 1 (at least one NaCl experiment with fit < -1) as an NaCl-importance threshold is biased by the number of NaCl experiments per organism. Synechococcus elongatus (SynE) has 12 NaCl dose-response experiments (0.5–250 mM) and flags 32.6% of genes as NaCl-important, 3× higher than the next organism. This inflates cross-condition overlap statistics: SynE shows 88.6% metal–NaCl shared-stress. When gene-level importance is compared across conditions with unequal experiment counts, the source recommends either requiring n_sick >= 2 or using mean_fit < threshold instead of n_sick >= 1, or reporting results with and without outlier organisms as a sensitivity check. Excluding SynE, overall metal–NaCl overlap drops from 39.8% to 36.7%, which the source calls modest but worth documenting. ₁

The kbase.ke_pangenome.gapmind_pathways table stores genome IDs without the GTDB RS_ or GB_ prefix (for example GCF_001307155.1), whereas the pangenome genome table uses the prefixed form (RS_GCF_001307155.1). A direct equality match between a pangenome genome_id and a GapMind genome_id therefore returns zero rows. The stated solution strips the RS_ or GB_ prefix before matching. Alternatively, a fallback chain tries the original ID, then the prefix-stripped ID, and then a partial match on the accession. ₁

The NMDC tables metabolomics_gold, kraken_gold, centrifuge_gold, and gottcha_gold use file_id and file_name as their primary identifiers, not sample_id. Queries with WHERE sample_id = ... or COUNT(DISTINCT sample_id) throw AnalysisException: UNRESOLVED_COLUMN and stop notebook execution. kbase.nmdc_arkin.taxonomy_features has neither a sample_id nor a file_id column. Its columns are numeric NCBI taxon IDs (for example 7, 11, 33, 34), and the source describes it as a pivoted matrix whose rows are only likely samples. It therefore should not be used for tidy-format joins. The tidy classifier tables (kraken_gold, centrifuge_gold, gottcha_gold), with file_id, rank, name/label, and abundance columns, should be used instead, and a row count of taxonomy_features should be checked against the expected number of samples. A direct file_id join between a classifier table and metabolomics_gold always returns zero rows. Classifier files use nmdc:dobj-11-* prefixes (metagenomics workflow outputs) and metabolomics files use nmdc:dobj-12-* (metabolomics workflow outputs). These are different workflow output types for the same biosample and are linkable only through the biosample sample_id (for example nmdc:bsm-11-*). The bridge table is kbase.nmdc_arkin.omics_files_table, confirmed at 385,562 rows, with file_id, sample_id, study_id, workflow_type, and file_type columns. ₁

A pandas DataFrame produced by calling .toPandas() on a Spark Connect DataFrame has columns backed by PyArrow ChunkedArray objects. Passing such a DataFrame back to spark.createDataFrame() raises an error. The stated solution avoids the pandas-to-Spark roundtrip entirely and keeps filtering and joining in Spark SQL using subqueries and the original table name. For bridge joins the full table name is used directly in SQL rather than a bridge materialized to a temp view. ₁

Most numeric columns in kbase.nmdc_arkin.abiotic_features use a _has_numeric_value suffix, such as annotations_tot_org_carb_has_numeric_value, but annotations_ph has no suffix. Using a bare name where the suffix is required raises UNRESOLVED_COLUMN. The table has no annotations_water_content column, and the source suggests annotations_diss_org_carb_has_numeric_value and annotations_conduc_has_numeric_value instead. ₁

In metal_specificity, fitness matrices from fitness_modules/data/matrices/ use integer-typed locusId indices (for example 206065), whereas the Metal Atlas metal_important_genes.csv stores locusIds as strings (for example '206065'). A pandas index lookup (locus not in fit_mat.index) silently fails when the types do not match, so the gene appears absent rather than raising an error. This mismatch dropped DvH (495 metal-important genes) and Btheta (276 genes) completely from the analysis without any warning. The organism processing loop reported “SKIPPED (no fitness matrix or no non-metal experiments)” even though the matrix existed. ₁

Also in metal_specificity, per-module z-scores computed across all experiments to identify metal-responsive modules produced max |z| < 2.0 for metal experiments when metals were a small fraction of the total, for example 12/176 for MR1. The source attributes this to the z-score denominator, the standard deviation across all experiments, being dominated by the non-metal majority. Metal Atlas NB05 avoided the problem by using precomputed z-scores from metal_modules.csv, standardized per module across all experiments rather than per experiment type. Recomputing z-scores from raw module_conditions.csv activity values does not reproduce the same responsiveness threshold. ₁

In bacdive_metal_validation, GTDB, the taxonomy used by the pangenome, renames many species with suffixes like _A and _B (for example Pseudomonas fluorescens A), adds genus-level splits (for example Pantoea A), and defines species boundaries differently from LPSN/DSMZ, the taxonomy used by BacDive. Direct species-name matching captured only 34.5% of strains. Adding base-name matching with GTDB suffixes stripped added another 8.9%, for 43.4% total. The source attributes the remaining 56.6% to genuinely different species concepts or to genera not yet in GTDB r214. The recommended fix is two-pass matching, first on exact species name and then on base name with the GTDB suffix stripped. For higher coverage the source recommends matching via the GCA genome accession to the pangenome genome_id, which requires handling of the GB_ prefix and version suffix. ₁

BacDive has 31 strains tagged with cat3=#Heavy metal contamination, but only 10 matched pangenome species with metal tolerance scores. Research plans should not assume that the full BacDive category size will survive matching. Post-matching sample sizes and power should always be computed before results are interpreted. ₁

kescience.bacdive.sequence_info.bacdive_id is INT, whereas kescience.bacdive.isolation.bacdive_id is STRING. A direct join returns zero rows, because Spark does not implicitly cast across INT and STRING types. The stated solution is CAST(si.bacdive_id AS STRING) = iso.bacdive_id for cross-table joins. ₁

The 93M-row eggnog_mapper_annotations table can produce aggregated results that exceed spark.driver.maxResultSize (1024 MiB) when species×OG matrices or similar large cross-tabulations are collected to the driver. The stated solution computes Fisher-test contingency inputs (counts per group) server-side in Spark SQL and collects only the summary counts needed for the statistical tests. ₁

Caveats

The document repeatedly warns that table schemas, namespace availability, access permissions, API behavior, database contents, and naming conventions can change. Queries should therefore begin with live catalog and schema discovery rather than copied historical SQL, and archived reports and notebooks should be treated as historical records rather than automatically valid executable instructions. ₁

Several recommendations are project-specific safeguards rather than universal thresholds. Examples include the held-out-feature Jaccard boundaries of 0.5 and 0.3, bootstrap sizes of 250–400, the ICA (independent component analysis) limit of at most 40% of the number of experiments, the |r| >= 0.3 module-membership rule, the ANI cap of <=500 genomes, and the 50–100 genome extraction batches for large species. These values should be reported with their analysis context rather than generalized without validation. ₁

The document records apparent method- or interface-specific inconsistencies that require care: direct Spark accepts a quoted species ID containing --, whereas the REST API rejects queries containing --; one section describes per-organism Fitness Browser convenience tables as long format while another database-specific note says they may be pre-pivoted and have non-standard schemas; and NMDC schema notes differ on whether abiotic numeric fields arrive as strings or doubles. The stated resolution is to inspect the live schema and query interface before relying on either description. ₁

Coverage limitations are not equivalent to biological absence. Missing AlphaEarth coordinates, sparse NCBI environment attributes, BacDive–GTDB naming differences, unmatched species concepts, orphan pangenomes, absent tables, and workflow-specific file identifiers can all produce zero or incomplete joins without indicating that the underlying biology is absent. ₁

Slots Into

• provenance-aware-resource-discovery — live catalog discovery, schema inspection, tenant resolution, identifier reconciliation, and the requirement to preserve provenance across database joins.
• cross-tenant-data-bridging — tenant-versus-database naming, permissions, namespace migration, and bridges between NMDC, classifier, metabolomics, pangenome, and Fitness Browser resources.
• environment-embedding-geography — AlphaEarth coverage, NaN filtering, geographic signal dilution by human-associated samples, and coordinate sparsity.
• ecotype-environment-gene-content — cluster-stratified differential-abundance leakage, ecotype confounding, and the need for independent within-substudy validation.
• multi-omics-integration — NMDC file_id versus sample_id namespaces, bridge-table discovery, and tidy versus wide data representations.
• gene-essentiality — the absence of essential genes from Fitness Browser transposon records and the resulting upper-bound interpretation.
• pangenome-integration — pangenome identifiers, gene-cluster scope, taxonomy joins, GapMind aggregation, orphan records, and annotation join keys.
• condition-specific-fitness — Fitness Browser typing, experiment grouping, KO mapping, condition-specific thresholds, and unequal experiment-count effects.
• environmental-resistome — environmental metadata sparsity, NCBI EAV handling, and the danger of treating missing environment classifications as absence.
• analysis-provenance-and-reproducible-outputs — NB08/NB09/NB10 committed as outputs without notebook history, and the cost of reconstructing them.
• taxonomic-nomenclature-reconciliation — ENIGMA short-strain-name misjoins, GTDB r214+ renames, the incomplete taxonomy crosswalk, and the need for a taxid-backed synonymy layer.
• classifier-database-compatibility-in-taxonomic-quantification — MetaPhlAn3 database-vintage and format divergences that produce artifactual log₂FC.
• cross-cohort-microbiome-portability — disjoint curatedMetagenomicData sub-studies and within-IBD-substudy CD-versus-nonIBD meta-analysis.
• study-batch-confounding-of-environmental-associations — unidentifiable pooled mixed models and the requirement to flag pooled effects as sub-study-confounded.
• pooled-run-pseudoreplication-and-metadata-label-noise — recovery of sub-study labels from participant IDs and external_ids versus noisy prefix regex.
• cross-condition-metabolic-comparability — Web of Microbes action-code semantics for a binary production call.
• homology-search-negative-evidence — zero-hit Pfam-accession queries against bakta_pfam_domains with an unresolved query format.
• functional-marker-validation — psl detection by KEGG orthologs versus gene names, and Pfam accession and name checks.
• evidence-triangulation-for-functional-annotation — annotation-source-dependent prevalence of psl genes.
• capability-versus-kinetic-predictability — codon-usage bias restricted to within-species growth comparisons.
• competitive-exclusion-consortium-design — PA14 representativeness for CF P. aeruginosa and the untested transfer of competitive exclusion to ExoS+ strains.
• pangenome-core-boundary-and-clade-size-bias — core, auxiliary and singleton count identities, and the failure of core-scope GapMind scoring in broad comparisons.
• computational-pathway-prediction-validation — core-scope GapMind scoring too stringent for genus-level or cross-species comparisons, and genus-level max-aggregation as the alternative.
• ec-less-reaction-annotation — no EC column or EC-to-reaction mapping on ModelSEED reaction, and KEGG R-numbers populated for only part of the reactions.
• structural-annotation-gap — AlphaFold low-MSA-depth entries, UniProt-only coverage, and SIFTS chains lacking UniProt mappings.
• phenotype-database-coverage-bias — sparse BacDive physiology fields and a metabolite test panel biased toward trehalose, arginine, and glucose.
• selection-on-outcome-leakage — same-feature clustering-then-testing inflation, the NB04b held-out-species Jaccard check, the NB04c independent-evidence gate, and the single-cell analog.
• ecotype-clustering-validity — LDA ecotypes trained on the same MetaPhlAn3 species matrix later used for within-ecotype tests.
• confirmatory-exploratory-ecological-association-discordance — Tier-A shrinkage once exploratory within-ecotype hits had to pass an independent CD-versus-control contrast.
• credential-and-tenant-access-lifecycle — token variable naming, expiry, refresh paths, and tenant-membership checks.
• execution-environment-dependent-access-paths — REST error codes, unreliable /count and /schema endpoints, and the off-cluster MinIO proxy requirement.
• silent-failure-modes-in-distributed-queries — permission failures disguised as S3 or catalog errors.
• foreign-dump-ingestion-fidelity — strict SQLite decoding failures, lossy permissive decoding, and comment-misparsed schemas that silently drop tables.
• data-landscape-ownership-and-coverage-bias — tenant-versus-namespace ownership and partial or sampled collections such as UniRef samples and sampled GenomeDepot tables.
• occurrence-versus-catabolic-activity — the absence of consumption actions in the frozen Web of Microbes snapshot.
• numeric-type-fidelity-across-engine-boundaries — Spark DECIMAL values arriving as decimal.Decimal, decimal-literal AVG() results, and the cast-to-DOUBLE rule of thumb.
• taxonomic-resolution-dependent-functional-inference — species-level taxonomy.name keys in NMDC and Planet Microbe abundance tables and the need to aggregate species to genus before filtering.
• callability-limited-comparative-inference — the false Planet Microbe genus absence produced by filtering genus-rank rows.
• ontology-and-category-schema-sensitivity — GapMind aa/carbon category values and shared COG, KEGG and Pfam representations for cross-species comparison.
• sampling-depth-and-downsampling-effects — ANI genome caps and subsampling for very large species.
• cultivation-collection-bias-in-ecological-genomics — missing coordinates and isolation metadata, especially for clinical isolates.
• sample-size-aware-phenotype-consensus — species-level max-aggregation of genome-level GapMind scores.
• driver-side-result-collection-limits — the read-only 1024 MB driver-result cap, MinIO parquet staging, and out-of-memory in-pandas spatial merges with batch and Spark-pushdown workarounds.
• join-strategy-and-table-partitioning-cost — the hung 13.7M × 18K join with auto-broadcast disabled, and explicit broadcast hints.
• long-running-analysis-execution-reliability — JupyterHub idle-kernel kills, nohup script execution, finalization recovery scripts, nbconvert timeouts, and parquet checkpointing.
• composite-functional-annotation — composite COG categories returning NaN in dictionary lookups and the need for missing-value checks before string operations.
• fitness-module-detection-sensitivity — FastICA component limits, absolute module-membership thresholds, DBSCAN negative-distance clipping, and enrichment min_annotated settings.
• cross-species-fitness-transferability — ortholog extraction restricted to a pilot subset of organisms undercounting cross-organism module families.
• transposon-callability-bias — genes absent from genefitness giving only an upper bound on essentiality, with no explicit essentiality flag in the Fitness Browser.
• adversarial-research-quality-assurance — denominator-dependent % core figures that diverged between figures and prose, silent boolean-negation errors, p-column selection before FDR, and empty committed notebooks.
• runtime-budgeted-resampling-and-embedding — moderate fixed-seed bootstrap sizes with confidence intervals limited to key endpoints.
• vectorization-versus-row-wise-iteration — a row-wise iterrows() lookup that ran 30+ minutes without completing, versus a vectorized merge that completed in ~10 seconds.
• fitness-condition-coverage-prioritization-bias — the n_sick >= 1 NaCl-importance threshold inflated by SynE’s 12 NaCl experiments, and masked metal-module z-scores when metal experiments are rare.
• shared-stress-versus-stressor-specific-fitness — metal–NaCl shared-stress overlap inflated by an outlier organism, with an exclusion sensitivity check.
• metal-cross-resistance — DvH and Btheta silently dropped from metal-specificity analysis by a locusId type mismatch. ₁