Run at warehouse scale on BigQuery¶
BigQueryScoreStore and BigQueryAnnotationSource keep a cohort's scores, annotations, cache checks and exports inside
BigQuery. A job then reads millions of variants without downloading them: the store joins the scores to the annotation
tables, evaluates every prioritization rule, and writes its exports to Cloud Storage, each in SQL.
The BigQuery stores and sources are Preview. Their queries run in tests on the BigQuery
emulator and on DuckDB. The EXPORT DATA and CREATE TABLE ... AS jobs, which the emulator cannot run, are checked
with a recording client for the SQL they send. None of it is run against live BigQuery. Run the opt-in BigQuery tests
in a scratch dataset before you rely on it.
Before you start¶
- Install
altar[bigquery], which addsgoogle-cloud-bigquery. Bindings whose sources read BigQuery, such asaltar-popfreqandaltar-clinvar, need it too. - Have a Google Cloud project, a dataset for the score table, and a Cloud Storage bucket in the same location for the exports.
- Give the job credentials: Application Default Credentials for
connect(), or agoogle.cloud.bigquery.Clientyou build and pass to the constructor. A store or source closes only a client it built.
from altar.results import BigQueryDetailStore, BigQueryScoreStore
scores = BigQueryScoreStore.connect(
"my-project.altar.variant_scores",
lineage_table_id="my-project.altar.score_lineages",
)
details = BigQueryDetailStore.connect("my-project.altar.detail_rows")
A run spec reaches the same store with [store] type = "bigquery" and a gcp connection; see
Keep scores in BigQuery and store.
ensure_schema reads the table's metadata and issues DDL only for a missing column, so you can call it before every
write. BigQuery counts each DDL statement toward a table's daily limit, even one that changes nothing. The score table
is clustered on genome, model_id, chr, variant_id, and every row carries genome, chr, pos, ref and alt
beside variant_id.
Fused sources¶
A fused source joins into the store's own query. It implements SqlAnnotationSource and returns a JoinSpec from
sql_join. BigQueryAnnotationSource is one: its table, or its relation= subquery, joins to the scores in SQL. The
{variant_filter} token in a relation limits the read to the job's variants.
Calls treat the two kinds of source differently:
| Call | Fused source | Other source |
|---|---|---|
materialize |
joined in the query; its rule is evaluated in SQL | read through annotate(); its rule is evaluated in Python |
export_download, export_prioritized, export_occurrence_buckets and the materialize_*_table calls |
joined in the query | refused with ValueError |
A column a model's rule reads must come from a fused source, so the rule can run in SQL. With variants_relation and no
variant_ids, a source that cannot fuse has no IDs to annotate, so materialize raises ValueError; stage it, leave
it out, or also pass the IDs.
Read gnomAD and ClinVar in SQL¶
Most of a cohort's variants are in gnomAD, so read it as a fused source rather than through Python. Load the published
Parquet into a table with altar pull. altar pull --bigquery is Provisional and
Preview: it is tested against a fake BigQuery client only.
The command loads the table clustered on variant_id and records the release as table labels. A release you built
yourself loads the same way from your own bucket. ClinVar's repeated columns need list inference to load as ARRAYs:
gcloud storage cp -r ./clinvar-20261005-hg38 gs://my-bucket/
bq load --source_format=PARQUET --parquet_enable_list_inference --clustering_fields=variant_id \
my-project:reference.clinvar_20261005_hg38 'gs://my-bucket/clinvar-20261005-hg38/*.parquet'
Each binding's bigquery_source builds the fused source over such a table, with the binding's name, columns and
identity:
from altar_clinvar import ClinVarRelease
from altar_clinvar import bigquery_source as clinvar_source
from altar_popfreq import GnomADRelease
from altar_popfreq import bigquery_source as gnomad_source
gnomad = gnomad_source(
scores.client,
"my-project.reference.gnomad_4_1_1_genomes",
release=GnomADRelease(version="4.1.1", dataset="genomes"),
)
clinvar = clinvar_source(
scores.client,
"my-project.reference.clinvar_20260928_hg38",
release=ClinVarRelease(release_date="20260928", genome_build="hg38"),
)
Pass them in annotation_sources to the store's materialize and export calls. Its relation comes from the binding's
bigquery_relation(table_id), which selects the contract's columns and keeps the {variant_filter} token. The
ClinVar source takes prioritize_pathogenic=True as its Python source does. In a run spec, a source's table option
with a connection builds the same join.
What each path checks¶
The Python path is the source's annotate() over any backend: a Parquet dataset, SQLite, or a BigQuery table read by
a store other than BigQueryScoreStore. The SQL path is bigquery_relation, which bigquery_source and a table
source in a BigQueryScoreStore query use. On a table the builder wrote, including one altar pull loaded, both return
the same values, because the builder already guarantees every rule. A table you made or edited yourself holds only what
you checked.
For gnomAD:
| Rule | Python path (GnomADSource.annotate) |
SQL path |
|---|---|---|
gnomad_af is null when gnomad_an is 0 |
rejects the row | computes it: CASE WHEN gnomad_an = 0 THEN NULL ELSE gnomad_af END |
gnomad_af_max_ancestry is the greatest non-null ancestry AF |
rejects the row | computes it from the ancestry columns, with the null-ignoring Greatest |
| one row per variant | rejects the result | not checked: a repeated variant_id multiplies rows in the store's join |
| a NaN frequency | reads it as null | not checked: a NaN reaches GREATEST and comparisons |
For ClinVar:
| Rule | Python path (ClinVarSource.annotate) |
SQL path |
|---|---|---|
clinvar_pathogenic, clinvar_low_penetrance and clinvar_review_stars follow from the classification and review status |
rejects the row | reads the stored values |
clinvar_origins decodes clinvar_origin |
rejects the row | reads the stored values |
each clinvar_genes record has a symbol and a gene ID |
rejects the row | reads the stored values |
| repeated columns are arrays | rejects a record-shaped column, such as a load without list inference | selects the column as it is |
| one row per variant | rejects the result | not checked: a repeated variant_id multiplies rows in the store's join |
ClinVar's PATHOGENIC_PREDICATE reads clinvar_pathogenic and clinvar_review_stars, so on the SQL path it trusts the
stored flags. Both paths check the release the same way: a table source reads the table's labels once, when it is
built. Check a table you loaded yourself once for repeated variants:
SELECT variant_id, COUNT(*) FROM `my-project.reference.gnomad_4_1_1_genomes`
GROUP BY variant_id HAVING COUNT(*) > 1 LIMIT 10
result_buckets collapses each source to one row per variant before it joins it,
taking any non-null value of each column as the SQL-pivot exports do, so exact copies of a row change nothing in its
files. Copies that differ give one of their values, chosen once per export.
Stage a sparse source computed in Python¶
staged_annotation_source runs a source that has no SQL of its own for one job's variants, and loads its rows into a
scratch table. Inside its async with block it yields a BigQueryAnnotationSource bound to that table, which the store
joins like any fused source. The table is dropped when the block exits, and expires after six hours if a crash leaves
it behind.
from altar.sources import staged_annotation_source
async def export_with_alphamissense(alphamissense, coverage, *, models):
async with staged_annotation_source(
alphamissense,
scores.client,
staging_dataset="my-project.scratch",
variants_relation="`my-project.jobs.variants_123`",
coverage=coverage,
) as staged:
await scores.export_download(
variant_ids=[],
models=models,
annotation_sources=[staged, gnomad],
destination_uri="gs://my-bucket/job-123/all",
variants_relation="`my-project.jobs.variants_123`",
)
Use it only when all three hold:
- The binding's Python code defines the annotations, so a SQL
relationwould be a second copy of the logic. - The logic does more than select and rename columns, such as picking the most damaging of several transcript records.
- The source is sparse: few of a job's variants have records, as with missense predictions.
With coverage=, a BigQueryAllelicRecordBackend over the source's table, one query finds the job's variants that have
records and reads them, so only those variants go through Python. A dense source staged this way sends most of a job's
variants through Python and back; keep such a source as a SQL relation.
Scope a job and export results¶
variant_ids passes a job's variants inline. Past 50,000 IDs, the store loads them into a scratch table in the score
table's dataset and drops it after the query. For a larger job, keep the variants in a table and pass it as
variants_relation, a backtick-quoted table ID with a variant_id column. variant_ids may then be empty.
Export from your own code¶
result_buckets produces a job's export from one scan of the score table and its sources, and the bucket writers
render it into any storage: a local directory, a GCS or S3 prefix, or anything else a Storage reaches. The wide table
has no column per model in BigQuery, so the export works past BigQuery's 10,000-column limit. This is the export to
use for a job with many models. A script that re-exports an existing job looks like this:
from altar.execution import FsspecStorage, LocalStorage
from altar.results import (
BigQueryScoreStore,
DisplayRider,
ExportScanLimitExceeded,
ExportStaging,
RiderColumn,
render_prioritized_ndjson,
render_wide_tsv,
write_column_manifest,
)
RIDER = DisplayRider(
order_by="is_prioritized DESC, ABS(logfc) DESC",
columns=[RiderColumn("most_active_celltype", "model_name"), RiderColumn("most_active_celltype_logfc", "logfc")],
)
async def reexport(client, models, sources, out):
store = BigQueryScoreStore(
client,
"my-project.lake.variant_scores",
lineage_table_id="my-project.lake.score_lineages",
export_staging=ExportStaging("gs://my-jobs/export-staging", FsspecStorage()),
export_maximum_bytes_billed=5 * 2**40,
)
try:
async with store.result_buckets(
variant_ids=[],
variants_relation="`my-project.jobs.job_123_variants`",
models=models,
annotation_sources=sources,
rider=RIDER,
) as buckets:
await render_wide_tsv(buckets, f"{out}/download", storage=LocalStorage())
await render_wide_tsv(buckets, f"{out}/prioritized", storage=LocalStorage(), prioritized_only=True)
await write_column_manifest(buckets.layout, f"{out}/download", storage=LocalStorage())
await render_prioritized_ndjson(buckets, f"{out}/database", storage=LocalStorage())
except ExportScanLimitExceeded as refused:
print(f"not exported: an estimated {refused.estimated_bytes:,} bytes scanned")
What happens:
- Dry runs estimate the scan of the two jobs that read your tables (step 2), and the estimate is their sum. Over
export_maximum_bytes_billed(or a call'smaximum_bytes_billed=), the store raisesExportScanLimitExceededbefore any job reads data (only the free load of its small selection table and the free creation of an empty annotations table come first). Once the annotations are collapsed, the second job is estimated again, since it reads them too, and checked before it runs. There is no limit unless you set one, and the limit is also set on the jobs that scan, so BigQuery enforces it on the bytes billed. To check what an export cost, readtotal_bytes_billedfrom the jobs in the region'sINFORMATION_SCHEMA.JOBS_BY_USERview, such as`region-us-west1`.INFORMATION_SCHEMA.JOBS_BY_USERfor a dataset inus-west1. It does not cap the Storage Read API, which is billed by the bytes read from the export's own result table. The estimate and the bytes billed are logged. - One job reads each source once and writes the job's variants' annotations to a table in the score table's
dataset, one row per variant: a source that holds several rows for a variant is collapsed to one. Then one
CREATE TABLE … ASreads the job's rows of the score table once, with that table, into a table of the job's results: one row per variant, annotations once, the models' scores nested. A model's decision and a source's flag read the same annotation value the files show. Both jobs read only the columns the export needs, both tables expire after 24 hours, and the annotations table is dropped once the results table is built. A small query then counts the table's rows and checks that no variant has two, reading only the table's ordinal column; if one does, the store raisesExportDuplicateRowsErrorbefore it reads any bucket. - The writers read that table a bucket of variants at a time, in genomic order. With
export_staging, a GCS prefix separate from where the files go,EXPORT DATAwrites the buckets there as Parquet, at mostexport_staging_max_jobs(100) jobs per export. Without it, the store reads them with the BigQuery Storage Read API, which thealtar[bigquery]extra installs. The store builds the Read API client from your BigQuery client's credentials; if you pass a wrapper that does not expose them, passread_client=google.cloud.bigquery_storage_v1.BigQueryReadClient(credentials=...)to the store. - When the
async withblock ends, also on an error, the store drops its tables and deletes every file under the export's own staging prefix,<export_staging uri>/<table name>/, which it lists through the staging storage. A process that is killed outright deletes nothing, so give the staging prefix a GCS lifecycle rule that deletes objects after a day or two.
The buckets can be read more than once, so one scan feeds every file. A writer holds one bucket in memory;
bucket_rows= or bucket_bytes= (default 128 MiB) sets the size. On a storage that can join files where they are
(GCS, the local disk), a gzipped file is written a bucket at a time and joined; elsewhere it is written locally and
uploaded once. The bytes are the same either way. GCS objects get Content-Type: application/gzip and no content
encoding, so a download is not decompressed on the way.
from_options takes the same settings from a run spec's [store] options: export_staging_uri,
export_maximum_bytes_billed and export_staging_max_jobs. render_occurrence_buckets writes the augmented VCF's
buckets from the same export. Export results describes the files.
The exports that pivot in SQL¶
These older exports each write under a gs:// prefix with one EXPORT DATA job. They build a column per model in
SQL, so they stop at about 1,650 models with five score columns. They will be retired in a later release:
| Method | Writes |
|---|---|
export_download |
The wide table, gzipped and tab-separated. header=False writes headerless parts, and download_header returns the header line to prepend when you compose them. |
export_prioritized |
The prioritized variants as gzipped NDJSON, with model_scores as an array of structs. |
export_occurrence_buckets |
The wide rows joined to an ingest's occurrences, as Parquet buckets for the augmented VCF. |
A job that writes several exports can scan the score table once. materialize_scored_table writes the joined rows to a
table, and each export then reads it through scored_relation=. materialize_scores_table and
materialize_annotations_table split those rows into a scores table and an annotations table, which the exports read
through scored_relation= and anno_relation=. Give each table an expiration_timestamp, such as
TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 6 HOUR), so a crashed job's tables expire.
rider= adds display columns to the exports, such as the most active cell type per variant. Its types,
DisplayRider and RiderColumn, are provisional. Every export picks the same row: a model's missing decision counts
as not prioritized, and a tie goes to the model listed first.
wide_column_manifest describes the columns export_download writes, one entry per column of download_header,
when you pass wide_layout the same models and sources, the rider's column names as rider_columns, and
score_lineages=True for a store with a lineage_table_id. See
List a wide table's columns.
Lineage tables¶
A score table built with lineage_table_id keeps lineages: each row carries a score_lineage_id, and the catalog
table holds each lineage's metadata once. Reuse scores across model updates explains what a lineage is
and how a job accepts older ones. Three tables hold the lineages:
| Table | Holds | Created |
|---|---|---|
<lineage_table_id> |
one row per lineage: its plugin, columns and representative run identity | by the store, when missing |
<lineage_table_id>__provenance |
every exact run identity that wrote under a lineage | by the store, when missing |
<lineage_table_id>__descriptors |
one LineageDescriptor per lineage, for equivalence |
only by ensure_descriptor_table() or by you |
The store's lineage_provenance_table_id and lineage_descriptor_table_id properties give the last two names. Every
score store has them: SqliteScoreStore returns its SQLite table names, and a store without lineages returns None.
To create the tables yourself, take their BigQuery column types from altar.results.LINEAGE_CATALOG_COLUMNS,
LINEAGE_PROVENANCE_COLUMNS and LINEAGE_DESCRIPTOR_COLUMNS. These properties and constants are stable. Without the
descriptor table, the engine scores without recording descriptors and warns once per store, and lineages a binding
declares equivalent are not reused.
BigQueryDetailStore(client, table_id) keeps its rows in table_id, with table_id__tables and
table_id__provenance beside it for each detail table's schema and the run identities that wrote it.
To adopt an existing score table, register its lineage and backfill score_lineage_id; see
Register existing scores.
Export unscored variants¶
A warehouse job finds its cache misses in SQL and exports them as the variant files a container model reads, without
downloading the cohort. prepare_unscored_table is the warehouse form of prepare_scoring_batches: it writes every
miss of a candidate relation to a job table, numbered into batches. export_prepared_batch then writes one batch's
variant file:
async def export_batches(plugin, plan, *, model_id, reuse, detail_store):
missing = await scores.prepare_unscored_table(
dest_table="`my-project.jobs.job_123_missing`",
variants_relation="`my-project.jobs.job_123_candidates`",
model_id=model_id,
plugin_identity=plan.plugin_identity,
columns=plugin.score_columns(),
reuse_policy=reuse,
max_variants_per_batch=100_000,
variant_eligibility=plugin.manifest.variant_eligibility,
detail_store=detail_store,
detail_schemas=plugin.manifest.detail_schemas,
ineligible_table="`my-project.jobs.job_123_ineligible`",
)
for batch in range((missing + 99_999) // 100_000):
await scores.export_prepared_batch(
prepared_relation="`my-project.jobs.job_123_missing`",
scoring_batch=batch,
destination_uri=f"gs://my-bucket/job-123/batch-{batch}/part-*.tsv",
)
The candidate relation holds the loci (chr, pos, ref, alt) and their variant_id, on the request's genome
build. A candidate is a miss unless an accepted lineage holds its primary row and, for each table in detail_schemas,
detail_store holds a row for it. A detail table whose row_count_field is 0 in the cached primary row needs no row.
The query cannot check artifact coverage, so a request whose output selects artifacts (artifact_schemas) raises
CapabilityNotSupported; prepare it with prepare_scoring_batches.
Preparation sets candidates aside before the cache query, as variant eligibility describes:
- a
variant_idthat is not canonical, or does not spell its locus, isnot_normalized; variant_eligibility, the model's manifest rule, excludes a candidate asunsupported_variant_classorallele_too_long. Without it, an SNV-only model's batches include indels, andrun_scoringrejects the batch;- with
check_representation=True, alleles that are not in minimal representation arenot_normalized; - with
reference_fasta, the misses are read back and checked against the reference:contig_missing,reference_mismatch, thennot_normalizedagainst left alignment.
By default no representation check runs. ineligible_table keeps every set-aside candidate, in the columns of
INELIGIBLE_VARIANT_COLUMNS; without it the counts are logged as a warning.
The job table expires after one day by default (expiration_timestamp). The caller owns the relation names, their
lifetime, the transfers and the scoring requests. Preparation is a snapshot, not a reservation: concurrent jobs may
prepare the same misses, and scores written after preparation do not change its batches.
Other calls answer smaller questions:
count_unscoredtakes the same cache arguments andcheck_representation, and returns the miss count before the reference check, without writing a table. It is provisional.get_missing_scores(..., variants_relation=...)returns the missing IDs, for a miss set small enough to fetch.export_unscoredexports misses byscoring_batchandscoring_batch_count, and recomputes the miss query on every call.export_unscored_checkedalso applies the reference and normalization checks to the files it writes, and returns anUnscoredExport. Prefer the prepared table for many batches.
Other public names¶
Code that runs the BigQuery store in production may also need these names:
| Name | What it gives you |
|---|---|
altar.models.generated_lineage_id |
The lineage ID a scoring run writes under, from its PluginRunIdentity. |
BigQueryScoreStore.client, BigQueryScoreStore.table_id, and the same on BigQueryAnnotationSource and the source staged_annotation_source yields |
The client and table the object was built with. Both are read-only: assigning the value it already holds is accepted, and any other value raises AttributeError. |
altar.results.VariantIdentity, DisplayRider, RiderColumn |
The types BigQueryScoreStore's constructor and export methods take. |
altar.results.export_col_alias, safe_identifier |
The wide-export column name of one model field, and the identifier check the SQL stores apply. |
altar.testing.FakeBigQueryClient |
A client that records the SQL it is asked to run, for testing your own code without BigQuery. |
generated_lineage_id and the properties are stable. The other names are
provisional, as are count_unscored
(Export unscored variants) and the ingest's Arrow schemas
(What the pipeline writes).
Next¶
- Reuse scores across model updates explains lineages and how a job accepts older scores.
- Store and assemble results describes the export columns.