Tabular Store

A tabular store is the layer where your molecular data stops being files and becomes rows: fixed columns, typed values, an index, and a query engine that can answer a question without rereading 40 GB of text. Tabular data means every record has the same fields, each field has a declared type, and the order of records carries no meaning beyond what a sort key encodes. A VCF row, a salmon quant.sf line, an Olink NPX measurement, a CGM reading, a CBC result: all tabular. A FASTQ read, a BAM alignment record, a protein structure, a Hi-C contact map: not, or not comfortably.

How it works

Three storage models show up in bioinformatics, and you will use all three.

Relational stores (PostgreSQL, SQLite) give you constraints, joins, and transactions. They are the backbone of curated resources and of breeding and clinical schemas where referential integrity matters more than scan speed ¹. People have pushed them further than you would expect, including custom column types for storing large biosequences directly in the relational engine ².

Columnar and array stores (Parquet, Arrow, DuckDB, HDF5, Zarr) store each column contiguously, so reading one field out of sixty costs one field’s worth of I/O. This is the model for genome-scale tables, and it predates the current tooling: wormtable built a read-only column store plus Berkeley DB indexes over VCF specifically because text parsing dominated runtime on whole-genome tables ³. Modern Parquet plus DuckDB is the same idea with better compression and a SQL front end.

Graph stores (Neo4j and friends) hold entities and typed edges, which suits gene–regulator–region relationships where the query is a traversal, not a filter. GenomicKB assembles exactly this over the human genome ⁴. You will query someone else’s graph more often than you build one.

The practical distinction: relational for correctness across many small tables, columnar for scanning one enormous table, graph for questions shaped like paths.

In your own data

Start by converting, once, and never parsing text again.

# VCF -> flat table -> Parquet
bcftools query -f '%CHROM\t%POS\t%REF\t%ALT\t%QUAL\t%FILTER\t[%GT\t%DP\t%GQ]\n' \
  -H sample.vcf.gz > variants.tsv

duckdb profile.db "
  CREATE TABLE variants AS SELECT * FROM read_csv_auto('variants.tsv');
  COPY variants TO 'variants.parquet' (FORMAT PARQUET, COMPRESSION ZSTD);
"

A 4.5M-variant WGS VCF lands around 150–350 MB as ZSTD Parquet and queries in tens of milliseconds for a position range. Partition by chromosome (PARTITION_BY (CHROM)) if you filter by region constantly.

Things to check before you trust the table:

Coordinate system. VCF is 1-based inclusive, BED is 0-based half-open. If you join a variant table to a BED of regions without converting, every boundary variant is wrong and nothing errors. Put the convention in the column name: pos_1based.

Multi-allelic rows. One VCF line can carry ALT=A,AT. Run bcftools norm -m -any -f GRCh38.fa before loading or your per-allele counts will be silently off.

Missingness. . in VCF, NA in R exports, empty string in CSV, and 0 in a CGM gap all mean different things. Force them to SQL NULL at load time and check COUNT(*) FILTER (WHERE x IS NULL) per column.

Genome build. GRCh37 and GRCh38 positions differ by megabases in places. Store the build as a table property and refuse to join across builds.

Joining across assays is where the store earns its keep. Your RNA-seq quant is keyed by Ensembl transcript ID with version suffix (ENST00000456328.2), your proteomics panel by UniProt accession, your variants by chromosome and position. You need a mapping table, and you need to decide whether to strip version suffixes (usually yes, but record the annotation release you used). Mismatched identifier versions across assays are the single most common cause of joins that return far fewer rows than expected. Always check row counts before and after a join.

Time-series data (CGM at 5-minute intervals, labs at irregular dates) joins to everything else on timestamp, not identifier. Store UTC, store the offset separately, and use ASOF JOIN in DuckDB to attach the nearest preceding lab draw to a glucose window.

Limitations

Tabular layouts fight data that is genuinely ragged. VCF’s INFO and per-sample FORMAT fields are nested and sample-varying, so flattening either explodes the column count or shoves JSON into a string column. GVF was designed partly to give variation records a more consistent, attribute-based structure than early flat formats allowed ⁵. Single-cell matrices are large and sparse enough that AnnData/HDF5 beats Parquet on access patterns ⁶. Read-level data (BAM, CRAM) stays in its own format because the queries are positional intervals, not column scans.

Schema drift is the quiet failure. Add a lab panel with a new analyte and your union breaks, or worse, appends with nulls you never notice. Define the schema explicitly and validate on load rather than relying on type inference. Interpreting any clinical value in these tables, including flagged variants, requires a clinician.

Woolf Software builds longitudinal molecular profiles of individuals: whole-genome sequencing, RNA sequencing, proteomics, blood biomarkers, and continuous glucose data, integrated into one model of you. Build your profile.