# Table Consolidation And Denormalization

> Use when when you have cleaned, normalized organism, structure, and reference tables from separate cleaning pipelines (e.g., after 2_curating stage) and must integrate them into a single queryable table while maintaining referential integrity and generating lookup dictionaries.

- Skill: `holobiomicslab/table-consolidation-and-denormalization` (Agent Skill)
- Install (CLI): `npx skillmds@latest add holobiomicslab/table-consolidation-and-denormalization`
- Raw SKILL.md: https://api.skillmd.com/api/skills/holobiomicslab/table-consolidation-and-denormalization/raw
- Safety review: PASS (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- License: CC-BY-4.0
- Author: HolobiomicsLab (https://skillmd.com/u/holobiomicslab)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/holobiomicslab/table-consolidation-and-denormalization

---


# table-consolidation-and-denormalization

> **License: restricted** — no clear open-source license detected for the underlying tool; verify licensing before commercial use or redistribution. <!-- asb-license-banner -->
## Summary

Consolidate normalized organism, structure, and reference tables into a single denormalized curated table by performing sequential left-join operations and appending external annotations (e.g., NP-classifier), while preserving all original rows and generating derived dictionaries for each entity type. This skill is essential when integrating multi-source biological and chemical data into a unified structure-organism-reference knowledgebase.

## When to use

When you have cleaned, normalized organism, structure, and reference tables from separate cleaning pipelines (e.g., after 2_curating stage) and must integrate them into a single queryable table while maintaining referential integrity and generating lookup dictionaries. Specifically triggered when you possess cleaned organism data, translated/cleaned structure data, cleaned reference data, and external structural annotations (e.g., NP-classifier), and your goal is to produce a denormalized curated table (e.g., interim/tables/3_curated/table.tsv.gz) that preserves all original rows from the source table and enables downstream validation and analysis.

## When NOT to use

- Input tables are already denormalized or merged; consolidation would introduce redundancy or lose reference integrity.
- Original table identifiers are not available or have been lost; you cannot preserve row lineage and validation status.
- Cleaning and translation pipelines have not been completed; tables contain inconsistent or unvalidated entries that would propagate downstream.

## Inputs

- interim/tables/0_original/table.tsv.gz (original integrated table with record identifiers and lineage)
- interim/tables/2_cleaned/organism/cleaned.tsv.gz (cleaned organism data)
- interim/tables/1_translated/structure/final.tsv.gz (final structure data)
- interim/tables/2_cleaned/structure/named.tsv.gz (named structure data)
- interim/dictionaries/structure/npclassifier/smiles_np_classified.tsv.gz (NP-classifier structure annotations)
- interim/tables/2_cleaned/reference/cleaned.tsv.gz (cleaned reference data)

## Outputs

- interim/tables/3_curated/table.tsv.gz (denormalized curated structure-organism-reference table with all rows preserved and validated entries flagged)
- interim/dictionary/organism/dictionary.tsv.gz (derived organism lookup dictionary)
- interim/dictionary/organism/metadata.tsv.gz (organism metadata)
- interim/dictionary/structure/dictionary.tsv.gz (derived structure lookup dictionary)
- interim/dictionary/structure/metadata.tsv.gz (structure metadata)
- interim/dictionary/reference/dictionaryOrganism.tsv.gz (reference-to-organism mapping dictionary)
- interim/dictionary/reference/metadata.tsv.gz (reference metadata)

## How to apply

Load the original integrated table (interim/tables/0_original/table.tsv.gz) to anchor all row identifiers and preserve lineage. Sequentially load cleaned organism data (interim/tables/2_cleaned/organism/cleaned.tsv.gz), final structure data (interim/tables/1_translated/structure/final.tsv.gz), named structure data (interim/tables/2_cleaned/structure/named.tsv.gz), NP-classifier annotations (interim/dictionaries/structure/npclassifier/smiles_np_classified.tsv.gz), and cleaned reference data (interim/tables/2_cleaned/reference/cleaned.tsv.gz) using data.table or equivalent. Perform left-join operations in sequence: join organisms by organism_id, structures by structure_id, references by reference_id, and append NP-classifier annotations by SMILES or structure identifier. Extract unique organism, structure, and reference entries to generate derived dictionaries (organism/dictionary.tsv.gz, structure/dictionary.tsv.gz, reference/dictionaryOrganism.tsv.gz) and corresponding metadata tables. Consolidate all columns into a single denormalized table, compress to interim/tables/3_curated/table.tsv.gz, and flag validated entries while ensuring all rows from the original table are preserved.

## Related tools

- **R** (Execute 1_integrating.R script for join operations and denormalization logic) — https://github.com/lotusnprod/lotus-processor
- **data.table** (Perform efficient left-join operations on cleaned and translated tables in R)
- **gzip/compression utilities** (Compress consolidated table to interim/tables/3_curated/table.tsv.gz)

## Examples

```
Rscript 1_integrating.R --original interim/tables/0_original/table.tsv.gz --organism interim/tables/2_cleaned/organism/cleaned.tsv.gz --structure interim/tables/1_translated/structure/final.tsv.gz --reference interim/tables/2_cleaned/reference/cleaned.tsv.gz --npclassifier interim/dictionaries/structure/npclassifier/smiles_np_classified.tsv.gz --output interim/tables/3_curated/table.tsv.gz
```

## Evaluation signals

- All rows from interim/tables/0_original/table.tsv.gz are preserved in the output table; row count matches or exceeds original count (accounting for splits/denormalization).
- All organism_id, structure_id, and reference_id foreign keys are populated post-join with no unexpected nulls (unless intentional for unmatched rows in outer joins).
- Derived dictionaries (organism, structure, reference) contain unique entries corresponding to joined data; no orphaned or duplicate entries in dictionaries.
- Validated entries are flagged with consistent, boolean validation status column; distribution of validated vs. unvalidated rows is documented.
- Output file (interim/tables/3_curated/table.tsv.gz) decompresses without corruption; column schema matches documented specification (all organism, structure, reference, and NP-classifier columns present).

## Limitations

- Row count may increase if 1:many or m:m joins occur (e.g., one organism mapped to multiple structures); document join cardinality and verify denormalization is intentional.
- NP-classifier annotations are appended by SMILES or structure identifier; missing or incorrectly formatted SMILES values will result in annotation gaps; validate SMILES format before join.
- Lineage and validation status depend on correct preservation of original table identifiers; if primary keys are corrupted or renamed during cleaning, traceability is lost.
- No changelog is documented; version history and updates to cleaning, translation, or annotation pipelines are not tracked, complicating reproducibility and debugging.

## Evidence

- [methods] Consolidation via sequential joins and dictionary extraction: "Perform left join operations in sequence: join organisms by organism_id, join structure data by structure_id, join reference data by reference_id, and append NP-classifier annotations by SMILES or"
- [methods] Input table inventory and sources: "Load cleaned organism data from interim/tables/2_cleaned/organism/cleaned.tsv.gz, final structure data from interim/tables/1_translated/structure/final.tsv.gz, named structure data from"
- [methods] Lineage preservation and original table role: "Load the original integrated table from interim/tables/0_original/table.tsv.gz to preserve original record identifiers and lineage."
- [methods] Dictionary derivation procedure: "Generate derived organism dictionary (interim/dictionary/organism/dictionary.tsv.gz) and metadata (interim/dictionary/organism/metadata.tsv.gz) by extracting unique organism entries and their"
- [methods] Output specification and row preservation: "Consolidate all columns into a single denormalized table and compress to interim/tables/3_curated/table.tsv.gz, ensuring all rows from the original table are preserved and validated entries are"