Capturing Transformation Lineage for BigQuery GIS Jobs

Part of: Structuring JSON/XML Lineage Documents

A BigQuery GIS query that runs ST_Intersects or ST_Union across billions of rows is a transformation whose inputs, outputs, and geometry operations should be recorded, yet the SQL itself leaves no lineage behind once the results land. This how-to reads INFORMATION_SCHEMA.JOBS and each job’s referenced_tables to reconstruct what a spatial query touched, then writes a JSON lineage document. It sits under Structuring JSON/XML Lineage Documents and feeds the wider Storage, Indexing & Query Optimization practice.

Prerequisites

  • Python 3.10+ and google-cloud-bigquery 3.14+.
  • A service account with roles/bigquery.jobUser plus roles/bigquery.resourceViewer (needed to read INFORMATION_SCHEMA.JOBS beyond your own jobs).
  • The GOOGLE_APPLICATION_CREDENTIALS environment variable pointing at the key file, or Application Default Credentials configured.
  • Knowledge of the region your jobs run in: INFORMATION_SCHEMA.JOBS is region-qualified, so a job run in the EU is invisible to a US query.
What the job record gives you, and what it does not BigQuery's own job metadata supplies query text, referenced tables, destination and statistics; CRS, step semantics and pipeline linkage must be supplied by the caller. Free from the job record query text, exactly as submitted referencedTables — the real inputs destinationTable — the output bytes processed, slot time principal, start and end time a genuinely good starting point You must supply CRS in and out — BigQuery GEOGRAPHY is always WGS 84, so the change happened on the way IN, unlogged step semantics — what it means pipeline context — run, batch, step id output digest, if you need one The CRS point is the one that catches spatial teams — the reprojection is invisible from inside BigQuery.

BigQuery’s GEOGRAPHY type is always WGS 84 with geodesic edges, which is convenient and quietly removes CRS from the warehouse’s world entirely. That means every reprojection happened before the data arrived — in the load job, the export from PostGIS, or the tool that produced the file — and none of it appears anywhere in the job metadata. A lineage record assembled purely from BigQuery’s own view of the world describes a pipeline in which coordinates never changed system, which is exactly the silent-drift failure mode the rest of this site warns about.

Capture the CRS at the load boundary and carry it forward as an attribute of the table, so the record for a query over that table can state what the input coordinates originally were. Geodesic edges are worth recording alongside it: a polygon whose edges are straight in a projected CRS becomes a different shape when interpreted geodesically, and that transformation is a real geometric change with no step in the job history to represent it.

Implementation

The function runs a spatial query, then queries the region-scoped INFORMATION_SCHEMA.JOBS view for that job id to recover its referenced tables, bytes processed, and slot time. It extracts the ST_* function names from the SQL text and assembles a lineage document.

from __future__ import annotations

import json
import re
from datetime import datetime, timezone
from pathlib import Path
from typing import Any

from google.cloud import bigquery

ST_PATTERN = re.compile(r"\b(ST_[A-Z_]+)\s*\(", re.IGNORECASE)


def run_and_capture_lineage(
    client: bigquery.Client,
    sql: str,
    region: str,
    lineage_dir: str | Path,
) -> dict[str, Any]:
    """Run a BigQuery GIS query and write a JSON lineage document for the job.

    Args:
        client: An authenticated BigQuery client.
        sql: The GIS SQL to execute (may contain ST_* functions).
        region: Region qualifier for INFORMATION_SCHEMA, e.g. "region-us".
        lineage_dir: Directory that receives the .json lineage document.

    Returns:
        The lineage document written to disk.
    """
    out_dir = Path(lineage_dir)
    out_dir.mkdir(parents=True, exist_ok=True)

    query_job = client.query(sql)
    query_job.result()  # block until the job completes
    job_id = query_job.job_id

    # Pull authoritative job metadata from the region-scoped JOBS view.
    meta_sql = f"""
        SELECT
          job_id,
          creation_time,
          total_bytes_processed,
          total_slot_ms,
          destination_table,
          referenced_tables
        FROM `{region}`.INFORMATION_SCHEMA.JOBS
        WHERE job_id = @job_id
    """
    cfg = bigquery.QueryJobConfig(
        query_parameters=[bigquery.ScalarQueryParameter("job_id", "STRING", job_id)]
    )
    row = next(iter(client.query(meta_sql, job_config=cfg).result()))

    def _fqtn(t: Any) -> str:
        return f"{t['project_id']}.{t['dataset_id']}.{t['table_id']}"

    inputs = [_fqtn(t) for t in (row.referenced_tables or [])]
    dest = row.destination_table
    output = _fqtn(dest) if dest else None
    spatial_ops = sorted({m.group(1).upper() for m in ST_PATTERN.finditer(sql)})

    lineage: dict[str, Any] = {
        "event": "bigquery_gis_transform",
        "job_id": job_id,
        "inputs": inputs,
        "output": output,
        "spatial_operations": spatial_ops,
        "total_bytes_processed": int(row.total_bytes_processed or 0),
        "total_slot_ms": int(row.total_slot_ms or 0),
        "job_created_at": row.creation_time.isoformat(),
        "captured_at": datetime.now(timezone.utc).isoformat(),
    }

    (out_dir / f"{job_id.replace(':', '_')}.json").write_text(
        json.dumps(lineage, indent=2), encoding="utf-8"
    )
    return lineage


if __name__ == "__main__":
    bq = bigquery.Client()
    doc = run_and_capture_lineage(
        client=bq,
        sql="""
            CREATE OR REPLACE TABLE geo.flood_parcels AS
            SELECT p.parcel_id, p.geom
            FROM geo.parcels AS p, geo.flood_zones AS f
            WHERE ST_Intersects(p.geom, f.geom)
        """,
        region="region-us",
        lineage_dir="./lineage",
    )
    print("Captured", doc["spatial_operations"], "over", doc["inputs"])

The referenced_tables array is the trustworthy source of inputs — parsing table names out of the SQL string is fragile against aliases, CTEs, and wildcard tables, whereas BigQuery populates referenced_tables from the actual query plan.

Job Labels Are the Cheapest Instrumentation

Labels connect a job to the pipeline that ran it Attaching pipeline, step and run labels to the job configuration makes INFORMATION_SCHEMA queries able to group jobs by pipeline context. Job config labels pipeline=parcel-etl step=reproject · run_id=… INFORMATION_SCHEMA.JOBS labels are queryable alongside referencedTables and destination Labels cost one line at submission and turn the job history into pipeline-aware lineage. Without them, INFORMATION_SCHEMA holds thousands of anonymous jobs nobody can attribute. Note the retention window on the view — export what you need to keep before it ages out.

Labels are the highest-value change available here for the least effort. Attaching a pipeline name, step name and run identifier to every job configuration makes the platform’s own job history queryable in pipeline terms — grouping by pipeline, tracing a run’s jobs in order, and finding every job that wrote a given table become straightforward INFORMATION_SCHEMA queries rather than exercises in correlating timestamps.

The retention caveat matters more than it looks. INFORMATION_SCHEMA.JOBS retains history for a limited window, so a lineage strategy that relies on querying it is a lineage strategy with an expiry date. Export the relevant fields into your own store on a schedule — daily is ample — and treat the platform view as a convenient source rather than as the archive. That export is also where you attach the CRS and semantic context the job record never had.

Verification

Confirm the document names the tables the job really read by comparing against the JOBS view directly:

SELECT job_id, referenced_tables, destination_table
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE job_id = 'your_project:US.bquxjob_1a2b3c4d_00'

The referenced_tables returned here must match the inputs array in the JSON document element-for-element. If your document lists fewer tables than the view, the job read a partitioned or wildcard source that resolved to more tables than the SQL text suggests — a strong reason to trust referenced_tables over string parsing.

Table-Level Lineage in a Warehouse

Three granularities, only one of which is free Table-level edges come from the job record; column-level requires query parsing; row-level is generally unavailable. TABLE level — free referencedTables → destinationTable, straight from the job record answers "what fed this table" for almost every audit question COLUMN level — needs query parsing parse the SQL to map output columns to inputs worth it only where a specific column carries obligation ROW level — effectively unavailable an aggregate has no per-row ancestry to record record the rule that produced it instead, as elsewhere

Table-level lineage is the right target for warehouse work, and it is genuinely enough for the questions warehouses get asked. “Which tables fed this published extract” and “which downstream tables depend on this one” both resolve from referencedTables edges alone, and those edges are recorded by the platform whether or not anybody instrumented anything.

The row-level row is worth stating plainly because people ask for it. A GROUP BY collapsing a million rows into a thousand has no per-row ancestry that could be recorded — the relationship is the aggregation itself. This is the same conclusion the raster case reaches about resampling: record the rule, which is the query text you already have, rather than attempting to enumerate contributions that would be larger than the data.

Partitioned and Clustered Destinations

Warehouse tables are rarely written whole, and that changes what a lineage record should say about the output. A query writing into a single partition of a date-partitioned table has not produced a new table — it has amended one — and a record describing destinationTable alone loses that distinction entirely.

Capture the write disposition alongside the destination. WRITE_TRUNCATE against a partition means the previous contents of that partition were replaced, which is a materially different fact from WRITE_APPEND adding to them, and it is the difference between “this data superseded that data” and “this data joined that data”. Both appear in the job configuration and neither is inferable from the destination table name.

Record the partition decorator too, where one was used. A record naming the destination as dataset.table when the job actually wrote dataset.table$20250601 describes a whole-table write that did not happen, and any downstream reasoning about what changed will be wrong by the size of the table. This matters most during backfills, which are precisely the jobs that write many partitions in quick succession and are hardest to reconstruct afterwards.

Clustering is less consequential for lineage but worth noting once: a change to clustering columns rewrites the table’s physical layout without altering a single value. It produces a job, a new storage footprint, and no semantic change — so a lineage consumer that treats every job touching a table as a derivation will report a transformation that did nothing. Tag maintenance jobs with a label that distinguishes them, and let queries filter accordingly.

Gotchas & edge cases

  • CRS is implicit and unlogged. BigQuery GIS GEOGRAPHY values are always WGS84 (EPSG:4326) with geodesic edges; there is no per-column CRS. If a source table stored planar coordinates that were force-cast to GEOGRAPHY, the geometry is silently wrong and no lineage field will flag it. Record the ingestion CRS assumption upstream, since the transform record cannot recover it.
  • Region scoping loses cross-region jobs. A single logical pipeline that runs staging in region-eu and marts in region-us needs two INFORMATION_SCHEMA.JOBS queries. Iterate over every region your datasets live in, or lineage for half the pipeline silently goes missing.
  • Script and multi-statement jobs. A CREATE OR REPLACE TABLE ... AS SELECT runs as a parent script job whose child statements carry their own job ids; referenced_tables on the parent can be empty. Query JOBS with parent_job_id = @job_id to gather child references. Route the finished documents into the schema conventions described in Structuring JSON/XML Lineage Documents so BigQuery lineage is queryable alongside every other source.