ObjectRef Multimodal Data Tables

Overview

Multimodal analytics often fails for mundane data-engineering reasons. Structured records live in tables, while images, PDFs, audio, and video live in object storage. Teams then build side pipelines to download files, transcribe or caption them, write derived text back to a warehouse, and finally join that output to the original business rows. The result is fragile lineage, duplicated access control, stale derived data, and expensive movement of large binary files.

An object-reference table pattern solves this by storing durable pointers to unstructured objects beside structured columns. BigQuery ObjectRef is one concrete implementation: a structured value records the object URI, an authorizer connection, an optional object version, and metadata such as content type and size. The table stores references, not raw media bytes, so analytical queries can join, filter, govern, and enrich multimodal records without embedding binary payloads in the database.

The transferable design is not BigQuery-specific: treat media as externally stored, versioned objects; persist stable references in relational rows; use warehouse-side functions or pushed-down DataFrame operations to resolve those references only when needed. This keeps the table queryable, keeps object access governed, and makes AI enrichment reproducible because the reference can pin the exact generation of a file.

Core Concepts

Reference, Not Payload

An ObjectRef column stores a pointer to a Cloud Storage object rather than the image, audio, document, or video itself. That distinction matters operationally: tables remain small enough for normal SQL operations, while large files stay in object storage where lifecycle policies, storage classes, and media-specific tooling already exist.

Authorizer-Bound Access

The reference carries an authorizer, usually a cloud resource connection. Query users do not need direct object-bucket credentials. Instead, the database engine resolves access through the connection when a query, model function, or DataFrame operation needs the object. This lets data teams apply table, row, column, and connection-level policies without scattering bucket permissions across every analyst and service account.

Versioned Reproducibility

A reference can include an object generation or version. Versioning protects AI pipelines from subtle drift: if a product image or audio recording is overwritten, historical enrichment jobs can still point at the exact object generation used to produce earlier captions, embeddings, or classifications.

How It Works

Creating Reference Columns

The simplest path is to create an object table over a bucket prefix. Object tables expose a reference column for each object and can be joined into standard analytical tables. Dynamic workflows can construct references from URI strings and enrich them with metadata before insertion.

-- Discover objects in storage and expose a ref column.
CREATE EXTERNAL TABLE `analytics.raw_product_images`
WITH CONNECTION `project.region.media_connection`
OPTIONS (
  object_metadata = 'SIMPLE',
  uris = ['gs://product-media/images/*.jpg']
);

-- Persist stable references next to structured product data.
CREATE TABLE `analytics.products_multimodal` AS
SELECT
  p.product_id,
  p.product_name,
  p.category,
  o.ref AS image_ref
FROM `analytics.products` AS p
JOIN `analytics.raw_product_images` AS o
  ON ENDS_WITH(o.uri, CONCAT(p.product_id, '.jpg'));

Generating Structured Output

Once references live in rows, model functions can use them as inputs. The model receives a prompt assembled from structured columns and media references, then writes a schema-constrained table of enriched fields. The important pattern is to materialize intermediate candidate rows first so inference only runs over the intended subset.

CREATE TABLE `analytics.product_enrichment` AS
SELECT *
FROM AI.GENERATE_TABLE(
  MODEL `analytics.gemini_model`,
  (
    SELECT
      (
        'Describe this catalog item and generate search facets.',
        product_name,
        category,
        OBJ.GET_ACCESS_URL(image_ref, 'r')
      ) AS prompt,
      product_id
    FROM `analytics.products_multimodal`
    WHERE category = 'laptops'
  ),
  STRUCT(
    '''
    description STRING,
    visual_attributes ARRAY<STRING>,
    likely_query_terms ARRAY<STRING>
    ''' AS output_schema
  )
);

Practical Applications

Support and Compliance Analytics

Support systems often combine ticket metadata, call recordings, chat transcripts, screenshots, and uploaded photos. Object-reference tables let analysts ask questions that require both structured filters and media interpretation: find high-value accounts with unresolved tickets where the call audio indicates frustration and the uploaded image shows a damaged product.

Catalog Enrichment

E-commerce teams can generate SEO fields, visual attributes, accessibility alt text, and moderation signals directly from product images while preserving the join to SKU, supplier, inventory, and pricing rows. Because the media remains referenced, enrichment jobs can be rerun with new prompts or models without rebuilding an object ingestion pipeline.

Multimodal Retrieval Preparation

Reference columns are a useful staging layer before embedding generation. A pipeline can generate captions, OCR text, audio summaries, or multimodal embeddings from referenced objects, then store the derived vectors and text alongside the same primary key. This preserves a clear lineage from vector index entry back to source object version.

Implementation Patterns

Two-Table Staging

Keep a raw object-discovery table separate from a curated multimodal table. The raw table mirrors bucket contents and may change frequently. The curated table joins object references to business entities, validates content types, pins object versions, and exposes only approved media columns to downstream users.

CREATE OR REPLACE TABLE `analytics.curated_ticket_media` AS
SELECT
  t.ticket_id,
  t.customer_id,
  t.priority,
  OBJ.FETCH_METADATA(OBJ.MAKE_REF(t.screenshot_uri, 'region.media_connection')) AS screenshot_ref,
  OBJ.FETCH_METADATA(OBJ.MAKE_REF(t.call_recording_uri, 'region.media_connection')) AS call_ref
FROM `analytics.support_tickets` AS t
WHERE t.screenshot_uri IS NOT NULL
   OR t.call_recording_uri IS NOT NULL;

Python Pushdown Instead of Download Loops

DataFrame APIs can expose object references as blob-like columns. The client code remains Pythonic, but expensive media scanning and model calls are pushed into the warehouse. This avoids downloading thousands of objects to a notebook just to call a hosted model row by row.

import bigframes.pandas as bpd
from bigframes.ml import llm

df = bpd.from_glob_path(
    "gs://catalog-media/products/*.jpg",
    name="image_blob",
)

model = llm.GeminiTextGenerator(model_name="gemini-2.5-flash")
result = model.predict(
    df,
    prompt=["Write one concise product description for this image.", df["image_blob"]],
)

Performance Considerations

• Inference cost: Model functions can charge per object, token, or row. Filter and materialize candidate rows before inference so accidental full-table scans do not become full-media inference jobs.
• Reference storage: Object references are small compared with media, but metadata still contributes to table storage. Store only metadata needed for filtering, validation, and reproducibility.
• Access URL lifetime: Runtime access URLs are temporary. Persist durable ObjectRef values and generate runtime access values only inside the workflow step that needs to read the object.
• Object locality: Keep warehouse datasets, object buckets, and authorizer connections in compatible regions to avoid latency, egress surprises, and connection-limit failures.
• Batching strategy: Large AI enrichment jobs should be chunked by partition, object type, or business priority. This makes retries tractable and prevents a single malformed object from blocking the entire corpus.

Common Pitfalls

• Treating references as raw bytes: An ObjectRef is a pointer with metadata, not an embedded file. Code that assumes it can read bytes directly from the table will break or create unnecessary export loops.
• Ignoring object mutation: If object versions are not pinned, overwrites can change future AI outputs for the same table row. Use versioned references for audit-sensitive enrichment.
• Over-broad authorizers: A single connection with access to every bucket defeats fine-grained governance. Use separate connections for different sensitivity classes when policy requires it.
• Running inference too early: Generate captions, descriptions, or embeddings after deduplication and filtering. Otherwise the model processes duplicate media and rows that will be dropped later.
• Forgetting derived-data lineage: Store model name, prompt version, source reference, source version, and job timestamp with generated fields. Without this metadata, teams cannot explain or reproduce enrichment changes.