đŸ§Ŧ PhageScope Database Validation Report

Generated: 2026-07-21T13:05:02.772484

Database: /data/processed/databases/phage_database_optimized.duckdb

📊 Database Overview

Phages
1,350,644
Proteins
71,971,209
Terminators
6,885,944
Anti-CRISPR
418,595
Virulent Factors
34,244
Transmembrane
9,735,407
tRNA/tmRNA
1,734,794
CRISPR Arrays
98,143
AMR Genes
3,107

🗂ī¸ Database Schema & Relationships

All dimension tables are linked to fact_phages via Phage_ID

dim_proteins
71,971,209 rows
FK: Phage_ID
dim_terminators
6,885,944 rows
FK: Phage_ID
fact_phages
1,350,644 rows
PK: Phage_ID
dim_anti_crispr
418,595 rows
FK: Phage_ID
dim_virulent_factors
34,244 rows
FK: Phage_ID
dim_crispr_arrays
98,143 rows
FK: Phage_ID
dim_transmembrane_proteins
9,735,407 rows
FK: Phage_ID
dim_trna_tmrna
1,734,794 rows
FK: Phage_ID
dim_antimicrobial_resistance_genes
3,107 rows
FK: Phage_ID

🏷ī¸ Provider Version & Provenance

Provider Name
PhageScope
Pinned Release
rolling
Pinned Snapshot Date
2026-07-05
Schema Profile
phagescope_v2
Provenance Mode
config_pinned
PBI Version
None
Git Commit
None
Provenance Rows
229
Non-success Provenance Rows
13

Public Data Provenance Status Distribution

StatusCount
success216
failed13

📋 Table Details

fact_phages
1,350,644
Columns: 18
Columns with NULLs: 14
Data Types:
  • VARCHAR: 16
  • INTEGER: 1
  • DOUBLE: 1
Column Names:
  • Phage_ID (VARCHAR)
  • Source_DB (VARCHAR)
  • Length (INTEGER)
  • GC_content (DOUBLE)
  • Taxonomy (VARCHAR)
  • Completeness (VARCHAR)
  • Host (VARCHAR)
  • Lifestyle (VARCHAR)
  • Cluster (VARCHAR)
  • Subcluster (VARCHAR)
  • Provider_Name (VARCHAR)
  • Provider_Release (VARCHAR)
  • Provider_Snapshot_Date (VARCHAR)
  • Provider_Schema_Profile (VARCHAR)
  • Input_Source_Key (VARCHAR)
  • Input_File (VARCHAR)
  • Input_Retrieved_At (VARCHAR)
  • source_type (VARCHAR)
dim_proteins
71,971,209
Columns: 20
Columns with NULLs: 3
Data Types:
  • VARCHAR: 9
  • INTEGER: 2
  • DOUBLE: 9
Column Names:
  • Phage_ID (VARCHAR)
  • Protein_ID (VARCHAR)
  • Protein_source (VARCHAR)
  • Function_prediction_source (VARCHAR)
  • Start (INTEGER)
  • Stop (INTEGER)
  • Strand (VARCHAR)
  • Product (VARCHAR)
  • Protein_classification (VARCHAR)
  • Molecular_weight (DOUBLE)
  • Aromaticity (DOUBLE)
  • Instability_index (DOUBLE)
  • Isoelectric_point (DOUBLE)
  • Helix_fraction (DOUBLE)
  • Turn_fraction (DOUBLE)
  • Sheet_fraction (DOUBLE)
  • Reduced_coefficient (DOUBLE)
  • Oxidized_coefficient (DOUBLE)
  • Source_DB (VARCHAR)
  • source_type (VARCHAR)
dim_terminators
6,885,944
Columns: 9
Columns with NULLs: 0
Data Types:
  • VARCHAR: 6
  • INTEGER: 2
  • DOUBLE: 1
Column Names:
  • Phage_ID (VARCHAR)
  • terminator_type (VARCHAR)
  • terminator_start (INTEGER)
  • terminator_end (INTEGER)
  • confidence_score (DOUBLE)
  • Sense (VARCHAR)
  • Loc (VARCHAR)
  • Source_DB (VARCHAR)
  • source_type (VARCHAR)
dim_anti_crispr
418,595
Columns: 5
Columns with NULLs: 0
Data Types:
  • VARCHAR: 5
Column Names:
  • Phage_ID (VARCHAR)
  • Protein_ID (VARCHAR)
  • Source (VARCHAR)
  • Source_DB (VARCHAR)
  • source_type (VARCHAR)
dim_virulent_factors
34,244
Columns: 5
Columns with NULLs: 0
Data Types:
  • VARCHAR: 5
Column Names:
  • Phage_ID (VARCHAR)
  • Protein_ID (VARCHAR)
  • aligned_protein_vfdb (VARCHAR)
  • Source_DB (VARCHAR)
  • source_type (VARCHAR)
dim_transmembrane_proteins
9,735,407
Columns: 19
Columns with NULLs: 10
Data Types:
  • VARCHAR: 8
  • INTEGER: 8
  • DOUBLE: 3
Column Names:
  • Phage_ID (VARCHAR)
  • Protein_ID (VARCHAR)
  • protein_length (INTEGER)
  • predicted_tmhs_number (INTEGER)
  • exp_aa_in_tmhs (DOUBLE)
  • exp_first_60_aa (DOUBLE)
  • total_prob_n_in (DOUBLE)
  • possible_n_term (VARCHAR)
  • inside_source (VARCHAR)
  • inside_start (INTEGER)
  • inside_end (INTEGER)
  • tm_helix_source (VARCHAR)
  • tm_helix_start (INTEGER)
  • tm_helix_end (INTEGER)
  • outside_source (VARCHAR)
  • outside_start (INTEGER)
  • outside_end (INTEGER)
  • Source_DB (VARCHAR)
  • source_type (VARCHAR)
dim_trna_tmrna
1,734,794
Columns: 12
Columns with NULLs: 3
Data Types:
  • VARCHAR: 9
  • INTEGER: 3
Column Names:
  • Phage_ID (VARCHAR)
  • trna_tmrna_id (VARCHAR)
  • source (VARCHAR)
  • trna_type (VARCHAR)
  • start_pos (INTEGER)
  • stop_pos (INTEGER)
  • Strand (VARCHAR)
  • length (INTEGER)
  • permuted (VARCHAR)
  • sequence (VARCHAR)
  • Source_DB (VARCHAR)
  • source_type (VARCHAR)
dim_crispr_arrays
98,143
Columns: 27
Columns with NULLs: 0
Data Types:
  • VARCHAR: 9
  • INTEGER: 9
  • DOUBLE: 9
Column Names:
  • Phage_ID (VARCHAR)
  • crispr_id (VARCHAR)
  • duplicated_spacers (INTEGER)
  • crispr_start (INTEGER)
  • crispr_end (INTEGER)
  • crispr_length (INTEGER)
  • potential_orientation_at_percent (VARCHAR)
  • crispr_direction (VARCHAR)
  • consensus_repeat (VARCHAR)
  • repeat_id_crisprdb (VARCHAR)
  • nb_crisprs_same_repeat (INTEGER)
  • repeat_length (INTEGER)
  • spacers_count (INTEGER)
  • mean_spacer_size (DOUBLE)
  • std_dev_spacers (DOUBLE)
  • repeats_matching_consensus (INTEGER)
  • ratio_repeats_match_total (DOUBLE)
  • conservation_repeats_pct (DOUBLE)
  • ebcons_repeats (DOUBLE)
  • conservation_spacers_pct (DOUBLE)
  • ebcons_spacers (DOUBLE)
  • repeat_plus_spacer_length (DOUBLE)
  • ratio_repeat_spacer_length (DOUBLE)
  • crispr_found_in_db (VARCHAR)
  • evidence_level (INTEGER)
  • Source_DB (VARCHAR)
  • source_type (VARCHAR)
dim_antimicrobial_resistance_genes
3,107
Columns: 5
Columns with NULLs: 0
Data Types:
  • VARCHAR: 5
Column Names:
  • Phage_ID (VARCHAR)
  • Protein_ID (VARCHAR)
  • aligned_protein_card (VARCHAR)
  • Source_DB (VARCHAR)
  • source_type (VARCHAR)
dim_hosts
5,522
Columns: 12
Columns with NULLs: 8
Data Types:
  • VARCHAR: 10
  • BIGINT: 1
  • DOUBLE: 1
Column Names:
  • Host_ID (VARCHAR)
  • Species_Name (VARCHAR)
  • Strain_Name (VARCHAR)
  • Assembly_Accession (VARCHAR)
  • Assembly_Name (VARCHAR)
  • Assembly_Level (VARCHAR)
  • Genome_Length (BIGINT)
  • GC_Content (DOUBLE)
  • RefSeq_Category (VARCHAR)
  • Download_Date (VARCHAR)
  • Source (VARCHAR)
  • source_type (VARCHAR)
dim_assembly_metadata
5,517
Columns: 17
Columns with NULLs: 0
Data Types:
  • VARCHAR: 12
  • INTEGER: 2
  • BOOLEAN: 3
Column Names:
  • Assembly_Accession (VARCHAR)
  • Assembly_Name (VARCHAR)
  • Organism_Name (VARCHAR)
  • Species_TaxID (INTEGER)
  • Strain (VARCHAR)
  • Assembly_Level (VARCHAR)
  • RefSeq_Category (VARCHAR)
  • BioSample (VARCHAR)
  • BioProject (VARCHAR)
  • FTP_Path (VARCHAR)
  • Submission_Date (VARCHAR)
  • Is_Latest (BOOLEAN)
  • Quality_Score (INTEGER)
  • Is_RefSeq (BOOLEAN)
  • Download_Status (VARCHAR)
  • Download_Date (VARCHAR)
  • Metadata_Only (BOOLEAN)
dim_phage_host_links
1,240,897
Columns: 7
Columns with NULLs: 0
Data Types:
  • VARCHAR: 7
Column Names:
  • Phage_ID (VARCHAR)
  • Host_Species (VARCHAR)
  • Host_Full_Name (VARCHAR)
  • Assembly_Accession (VARCHAR)
  • Assembly_Level (VARCHAR)
  • RefSeq_Category (VARCHAR)
  • Link_Quality (VARCHAR)

✅ Data Quality Checks

CheckStatusDetails
All expected tables present ✅ PASS All 9 tables found
Duplicate Phage IDs ❌ FAIL 22720 duplicates found
Orphaned Proteins ✅ PASS 0 proteins without matching phages
Duplicate Protein IDs ⚠ī¸ WARNING 1447750 duplicate protein IDs
Orphaned Terminators ✅ PASS 0 terminators without matching phages
Orphaned Anti-CRISPR ⚠ī¸ WARNING 28468 anti-CRISPR entries without matching phages
Duplicate Protein IDs (Anti-CRISPR) ⚠ī¸ WARNING 2732 duplicate protein IDs
Orphaned Virulent Factors ⚠ī¸ WARNING 4 virulent factors without matching phages
Duplicate Protein IDs (Virulent Factors) ⚠ī¸ WARNING 1 duplicate protein IDs
Orphaned Transmembrane Proteins ✅ PASS 0 transmembrane proteins without matching phages
Duplicate Protein IDs (Transmembrane) ✅ PASS 0 duplicate protein IDs
TMH Statistics ℹī¸ INFO Avg TMHs: 0.86, Range: 0-56
Orphaned tRNA/tmRNA ✅ PASS 0 tRNA/tmRNA entries without matching phages
Duplicate tRNA IDs ✅ PASS 0 duplicate tRNA IDs
Orphaned CRISPR Arrays ✅ PASS 0 CRISPR arrays without matching phages
Duplicate CRISPR IDs ⚠ī¸ WARNING 1277 duplicate CRISPR IDs
Orphaned AMR Genes ✅ PASS 0 AMR genes without matching phages
Duplicate Protein IDs (AMR) ⚠ī¸ WARNING 77 duplicate protein IDs

🔧 Database Objects

Indexes: idx_amr_phage, idx_amr_protein, idx_amr_source, idx_amr_source_type, idx_anti_crispr_phage, idx_anti_crispr_source, idx_anti_crispr_source_type, idx_assembly_accession, idx_assembly_organism, idx_assembly_taxid, idx_crispr_evidence, idx_crispr_id, idx_crispr_phage, idx_crispr_source, idx_crispr_source_type, idx_dataset_provenance_feature, idx_dataset_provenance_source_key, idx_hosts_accession, idx_hosts_id, idx_hosts_source_type, idx_hosts_species, idx_phages_id, idx_phages_source, idx_phages_source_type, idx_phage_host_assembly, idx_phage_host_phage, idx_proteins_phage, idx_proteins_source, idx_proteins_source_type, idx_terminators_phage, idx_terminators_source, idx_terminators_source_type, idx_transmembrane_phage, idx_transmembrane_source, idx_transmembrane_source_type, idx_trna_phage, idx_trna_source, idx_trna_source_type, idx_virulent_phage, idx_virulent_source, idx_virulent_source_type

Views: amr_gene_summary, crispr_array_summary, crispr_evidence_distribution, host_amr_profile, host_crispr_profile, host_genome_summary, phage_complete_profile, phage_defense_profile, phage_host_associations, phage_host_genomes, phage_size_distribution, phage_summary

🔒 Private Data Overview

Private data is ingested from local sources and merged with the public PhageScope database.

Private Phages
8
Private Hosts
5
Private Interactions
8

Interactions per Private Source

test_private
5
test_private_2
3

Lifestyle / Interaction Distribution

Interaction TypeCount
virulent5
temperate3