Loading...

Skip to main content

6. Transformation Engine — BigQuery

Using BigQuery as the compute layer over lakehouse tables. The default choice for a GCP client, and the one with the narrowest catalog compatibility.

The role BigQuery plays​

Serverless SQL over open tables​

No cluster to size, no runtime to patch. Capacity is slots, priced on-demand by bytes or by reservation.

Where it sits in the three patterns​

Owner of the catalog in Pattern 1; a client of the catalog in Pattern 2; effectively absent from Pattern 3.

Catalog compatibility​

Read this against the same diagram in the other Chapter 3 modules — the frame is identical, so what changes is which pattern each engine is actually good at. Thick arrow: first-class read and write. Thin: works, with caveats. Dotted: read-only or degraded.

BigQuery is strongest exactly where it owns the catalog, and weakest where it does not.

Pattern 1 — BigQuery-managed Iceberg​

Full participation. BigQuery owns the commits and the maintenance, and is the only engine that can write. Governance stops at column level — no row access policies or authorized views.

Pattern 2 — Lakehouse runtime catalog​

Catalog namespaces appear as BigQuery datasets (project.catalog.namespace.table). Reads are GA; INSERT, UPDATE, DELETE, MERGE, and DDL are Preview, on by default only for tables BigQuery created (checked on 2026-09-25). Only table-level IAM applies — no policy tags, masking, row-level security, or authorized views — so governance requires the metadata-pinned external table workaround from Module 04.

Pattern 3 — Dataproc Metastore​

No current documented way for BigQuery to query Dataproc Metastore tables; Google's answer is migrating them to Pattern 2 (Module 05). The weakest of the three.

Plain Parquet in GCS​

BigLake external tables, read-only. Covered in Module 03.

The write path​

What works where​

Full DML and streaming on managed Iceberg (one mutating statement per table at a time); Preview DML and Storage Write API on Lakehouse runtime catalog tables; read-only on external Parquet.

Commit semantics​

BigQuery jobs versus Iceberg snapshots — where the transaction boundary actually sits, and what that means for multi-table consistency.

Migrating a native table in​

INSERT INTO ... SELECT as the initial load, plus type mapping and the unsupported-type CAST strategy.

Performance and cost shape​

Measured overhead​

Managed Iceberg at 1.13x native elapsed and 2.01x native slot-ms; external Parquet at 1.23x and 1.63x; direct catalog access at 4.26x elapsed. Full context and caveats in Module 04 and _research/benchmark_results.md.

On-demand versus editions​

The same overhead lands as a bytes-scanned bill or a slot-hours bill, and the two do not rank the patterns the same way. Establish which the client is on before quoting anything.

Where the overhead concentrates​

Mostly the input stage — joins and aggregation stay close to native for most variants. The exception is the cached Parquet external table, whose join and aggregate stages cost 2.30x and 2.22x the uncached variant's slot-ms, pointing to a different execution plan rather than a cache benefit (three repetitions, one workload).

Dry-run estimation is weaker​

A dry run over an external table can report a lower bound of 0 bytes even when rows are returned, and any table with row-level security always dry-runs at 0 bytes.

Governance​

The reason clients pick BigQuery​

Policy tags, column-level security, row access policies, dynamic data masking, and authorized views — the richest governance of the GCP-native engines; only Unity Catalog (Module 09) is comparable. But on lakehouse tables it shrinks: managed Iceberg keeps only the column-level part, Lakehouse runtime catalog tables keep only table-level IAM, and plain external Parquet paradoxically keeps all of it (checked on 2026-09-25).

Where it stops​

None of it applies to a Spark or Trino reader hitting the same table. Governance is per-engine.

Development experience and orchestration​

SQL-first transformation​

dbt and Dataform over lakehouse tables, and what changes versus native tables.

Scheduling and the post-commit metadata refresh​

In Pattern 2, the external-table URI refresh is a step in the transformation DAG, not a separate cron. Idempotency, staleness checks, and failure alerting belong in the orchestrator.

When BigQuery is the right transformation engine​

Decision gates​

The team is SQL-first, workloads are batch or micro-batch analytical SQL, fine-grained governance is required, and operational simplicity is valued over engine portability.

Anti-patterns​

Row-by-row processing, heavy non-SQL logic, table maintenance jobs better suited to Spark, and using BigQuery to write into a table another engine also writes to without a single-writer design.