BQBrains | Analyzing PDFs and Images in BigQuery: Object Tables, ObjectRef, and AI.GENERATE

Most warehouses stop at rows and columns. The invoices, inspection photos, call recordings and scanned contracts sit in Cloud Storage, and any question that spans both worlds — "which of last quarter's supplier invoices are missing a PO number?" — turns into a Python service, a queue, and a table nobody trusts.

BigQuery's multimodal features close that gap. Object tables put a governed SQL row in front of every file in a bucket, the ObjectRef type lets a normal table column point at a file, and the AI.GENERATE* functions send those files to Gemini and return structured columns. The result is one query, one permission model, one audit log.

This tutorial builds a working pipeline over a bucket of PDFs and images: catalogue the files, extract structured fields, join them back to your existing tables, and keep the cost sane.

Prerequisites

A Google Cloud project with the BigQuery, BigQuery Connection and Vertex AI APIs enabled
A Cloud Storage bucket holding the files (gs://acme-docs/invoices/*.pdf, gs://acme-docs/photos/*.jpg)
A BigQuery connection of type Cloud Resource in the same region as your dataset. Grant its service account Storage Object Viewer on the bucket and Vertex AI User on the project.
A dataset in that region — the examples use US and a connection called us.vertex-ai

Check the connection's service account before you go further; roughly nine out of ten "permission denied" errors in this workflow are the connection identity missing one of those two roles:

SELECT * FROM `demo`.INFORMATION_SCHEMA.OBJECT_PRIVILEGES LIMIT 0;
-- and in the console: BigQuery Studio -> Explorer -> External connections -> us.vertex-ai

Step 1: Catalogue the bucket with an object table

An object table is a read-only external table whose rows are file metadata, not file contents:

CREATE OR REPLACE EXTERNAL TABLE `demo.invoice_files`
WITH CONNECTION `us.vertex-ai`
OPTIONS (
  object_metadata = 'SIMPLE',
  uris = ['gs://acme-docs/invoices/*.pdf'],
  max_staleness = INTERVAL 1 HOUR,
  metadata_cache_mode = 'AUTOMATIC'
);

Query it like any other table:

SELECT uri, content_type, size, updated
FROM `demo.invoice_files`
WHERE updated >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
ORDER BY size DESC
LIMIT 20;

Two things to internalise now, because they cause most of the surprises later:

The metadata is cached. max_staleness plus metadata_cache_mode = 'AUTOMATIC' means new files appear within the staleness window, not instantly. With MANUAL, you call BQ.REFRESH_EXTERNAL_METADATA_CACHE('demo.invoice_files') yourself — the right choice when a pipeline drops files and then immediately queries them.
Access is delegated. Anyone with SELECT on the object table reads those objects through the connection's identity, even without direct bucket access. That is the feature — it is also why the connection service account should be scoped to exactly one prefix, not to a project-wide bucket.

Step 2: From object tables to ObjectRef columns

An object table is a catalogue of a bucket. Usually you want the opposite: an existing business table where one column points at a file. That is the ObjectRef type.

CREATE OR REPLACE TABLE `demo.invoices` (
  invoice_id   STRING,
  supplier_id  STRING,
  received_at  TIMESTAMP,
  document     STRUCT<uri STRING, version STRING, authorizer STRING, details JSON>
);

-- Populate the reference from the object table
INSERT INTO `demo.invoices` (invoice_id, supplier_id, received_at, document)
SELECT
  REGEXP_EXTRACT(uri, r'/([^/]+)\.pdf$')            AS invoice_id,
  REGEXP_EXTRACT(uri, r'/invoices/([^/]+)/')        AS supplier_id,
  updated                                           AS received_at,
  OBJ.MAKE_REF(uri, 'us.vertex-ai')                 AS document
FROM `demo.invoice_files`;

OBJ.MAKE_REF(uri, connection) builds the reference; OBJ.FETCH_METADATA(ref) refreshes size, content type and generation into the details field; OBJ.GET_ACCESS_URL(ref, 'r') mints a short-lived signed URL when a dashboard needs to display the file. The reference is a normal column, so it partitions, clusters, joins and inherits row-level security like everything else.

A table mixing scalar columns with ObjectRef columns is what Google calls a multimodal table. It is the shape you want in production: structured facts and their source documents in one row.

Step 3: Ask Gemini a question per file

AI.GENERATE takes a prompt that can interleave text and object references, and returns generated text:

SELECT
  invoice_id,
  AI.GENERATE(
    ('Summarise this invoice in one sentence: total, currency, due date.', document),
    connection_id => 'us.vertex-ai',
    endpoint      => 'gemini-2.5-flash'
  ).result AS summary
FROM `demo.invoices`
WHERE received_at >= CURRENT_TIMESTAMP() - INTERVAL 1 DAY;

There are typed siblings that save you from parsing model prose:

AI.GENERATE_BOOL — "does this photo show visible rust?"
AI.GENERATE_INT, AI.GENERATE_DOUBLE — counts and amounts
AI.GENERATE_TABLE — a whole struct of fields in one call

SELECT
  invoice_id,
  AI.GENERATE_BOOL(
    ('Does this invoice reference a purchase order number?', document),
    connection_id => 'us.vertex-ai'
  ).result AS has_po
FROM `demo.invoices`;

Step 4: Structured extraction with AI.GENERATE_TABLE

This is the workhorse. You declare an output schema and get typed columns back:

CREATE OR REPLACE TABLE `demo.invoice_fields` AS
SELECT
  invoice_id,
  supplier_id,
  po_number,
  invoice_total,
  currency,
  due_date
FROM AI.GENERATE_TABLE(
  MODEL `demo.gemini_flash`,
  (
    SELECT
      invoice_id,
      supplier_id,
      ('Extract the fields from this invoice document.', document) AS prompt
    FROM `demo.invoices`
    WHERE received_at >= CURRENT_TIMESTAMP() - INTERVAL 1 DAY
  ),
  STRUCT(
    'po_number STRING, invoice_total FLOAT64, currency STRING, due_date DATE'
      AS output_schema
  )
);

with the model created once:

CREATE OR REPLACE MODEL `demo.gemini_flash`
REMOTE WITH CONNECTION `us.vertex-ai`
OPTIONS (ENDPOINT = 'gemini-2.5-flash');

Field names and types in output_schema are part of the prompt — due_date DATE is a far stronger instruction than "return the date". Keep the schema small and specific; ten vague fields extract worse than four precise ones.

Endpoint names move fast. Confirm the current Gemini model IDs available in your region in the BigQuery ML documentation before copying these verbatim.

Step 5: Join the unstructured result to your warehouse

The payoff is that everything above lands in ordinary tables:

SELECT
  s.supplier_name,
  COUNT(*)                                  AS invoices,
  COUNTIF(f.po_number IS NULL)              AS missing_po,
  ROUND(SUM(f.invoice_total), 2)            AS total_billed
FROM `demo.invoice_fields` f
JOIN `demo.dim_supplier` s USING (supplier_id)
WHERE f.due_date BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY 1
HAVING missing_po > 0
ORDER BY missing_po DESC;

No export, no separate service, no second copy of the documents.

Step 6: Similarity search over images

For "find more like this" rather than "extract fields", embed the objects instead. A multimodal embedding model turns images and text into the same vector space, so a text query can retrieve photos:

CREATE OR REPLACE MODEL `demo.mm_embedder`
REMOTE WITH CONNECTION `us.vertex-ai`
OPTIONS (ENDPOINT = 'multimodalembedding@001');

CREATE OR REPLACE TABLE `demo.photo_embeddings` AS
SELECT uri, ml_generate_embedding_result AS embedding
FROM ML.GENERATE_EMBEDDING(
  MODEL `demo.mm_embedder`,
  TABLE `demo.photo_files`,
  STRUCT(TRUE AS flatten_json_output)
);

From there it is the same CREATE VECTOR INDEX and VECTOR_SEARCH workflow covered in our vector search tutorial — index the embedding column, embed the text query, and rank by cosine distance.

Cost, throughput and failure modes

Inference is the expensive part, and the pattern that keeps it affordable is simple: extract once, store the result, query the table. Never put AI.GENERATE_TABLE in a dashboard query.

Bill in two places. BigQuery charges for the query; Vertex AI charges per input and output token. A PDF page is worth roughly a few hundred to a couple of thousand tokens depending on density, and images are billed per tile.
Process incrementally. Filter on received_at or updated so each run touches only new files, and MERGE into the extraction table keyed by file URI plus generation.
Expect partial failures. The AI.GENERATE* functions return a status field alongside result; land it and retry the non-empty ones rather than re-running the whole batch.

SELECT uri, status
FROM `demo.extraction_log`
WHERE status != ''
ORDER BY run_ts DESC;

Long jobs hit quotas. Vertex AI enforces per-minute request limits per project and region; large backfills should be chunked (a few thousand rows at a time) or run through a reservation with job_type = 'ML_EXTERNAL' assigned so inference cannot starve your ELT slots.
Non-determinism is real. Set a low temperature, pin the model version explicitly, and record which endpoint produced each row — so that when a field's quality changes, you can prove why.

Governance notes

Because the references live in normal columns, all your existing controls apply: policy tags on document, row access policies that limit which suppliers an analyst can see, and audit logs on every read of the signed URL. That is the strongest argument for doing this in BigQuery rather than a bespoke pipeline — the documents inherit the warehouse's access model instead of getting a second, weaker one. If you have not built that layer yet, start with data governance and compliance.

Where to go next

Good first projects are narrow and measurable: invoice field extraction with a hand-labelled accuracy check on 100 documents, defect classification over inspection photos, or contract clause flagging against a fixed checklist. Build the evaluation table before the pipeline — extraction accuracy is the only metric that matters, and you cannot improve a prompt you are not scoring.

If you would like help designing multimodal pipelines on governed data, our AI and Gemini services cover architecture, prompt evaluation and cost modelling. Get in touch with a description of your documents and the fields you need.

Analyzing PDFs and Images in BigQuery: Object Tables, ObjectRef, and AI.GENERATE_TABLE