6.4 AI Data Enrichment inside Data Pipelines
Key Takeaways
- AI data enrichment is a named transformation type in the v4.2 blueprint, and the core decision is whether to enrich at rest in BigQuery or in flight in Dataflow.
- BigQuery enrichment runs through remote models created with CREATE MODEL REMOTE WITH CONNECTION over a Cloud resource connection, exposing ML.GENERATE_TEXT, AI.GENERATE, ML.UNDERSTAND_TEXT, ML.TRANSLATE, and ML.GENERATE_EMBEDDING.
- Unstructured files in Cloud Storage must be exposed through an object table before ML.PROCESS_DOCUMENT, ML.ANNOTATE_IMAGE, or ML.TRANSCRIBE can read them.
- BigQuery remote functions call Cloud Run or Cloud Run functions endpoints for custom enrichment; they do not support ARRAY, STRUCT, INTERVAL, or GEOGRAPHY types and are tuned with max_batching_rows.
- The Apache Beam Enrichment transform, available from version 2.54.0, provides batching, client-side throttling, exponential backoff, and optional Redis caching with handlers for Bigtable, BigQuery, Cloud SQL, and Vertex AI Feature Store.
6.4 AI Data Enrichment inside Data Pipelines
The v4.2 exam guide lists four kinds of transformation under Building the pipelines: Batch, Streaming, Processing logic, and AI data enrichment. The last one is the newest addition and the one most candidates arrive without. It asks a specific architectural question: when a pipeline needs to attach information that the source data does not contain — a sentiment score, a translated string, an entity extracted from a scanned invoice, a semantic embedding, a feature value from a store — where in the architecture does that call happen, and what does it cost you in latency, quota, and failure modes?
There are two legitimate topologies, and picking between them is the whole skill.
Topology 1: Enrich at Rest, in BigQuery
BigQuery can call models and services directly from SQL. This is the low-operations path, and it is the right answer whenever the enrichment can wait for the next batch and the data already lives in BigQuery.
The mechanism is a remote model: CREATE MODEL ... REMOTE WITH CONNECTION, backed by a Cloud resource connection whose auto-created service account is granted access to the target Vertex AI or Cloud AI service. Once the model exists, enrichment is a SELECT.
| Function | What It Produces | Requires an Object Table |
|---|---|---|
ML.GENERATE_TEXT | Model-generated text from Gemini or Gemma models | No |
AI.GENERATE | Scalar generated text inside a query | No |
AI.GENERATE_TABLE | Structured, tabular generated output | No |
AI.GENERATE_BOOL | A boolean classification result | No |
ML.GENERATE_EMBEDDING | Vector embeddings of text, image, or video | Only for unstructured inputs |
ML.UNDERSTAND_TEXT | Natural-language tasks such as classification and sentiment analysis | No |
ML.TRANSLATE | Translated text | No |
ML.ANNOTATE_IMAGE | Cloud Vision labels, face detection, object detection | Yes |
ML.PROCESS_DOCUMENT | Structured fields extracted from documents via Document AI | Yes |
ML.TRANSCRIBE | Text transcribed from audio via Speech-to-Text | Yes |
The pattern to internalize: structured text in a column goes straight into the generative and NLP functions, while unstructured files in Cloud Storage must first be exposed through an object table — a BigQuery table of references to the underlying objects — before ML.ANNOTATE_IMAGE, ML.PROCESS_DOCUMENT, or ML.TRANSCRIBE can read them.
Custom Logic: Remote Functions
When the enrichment is not a model call at all — a proprietary scoring service, an internal address-normalization API, a licensed data vendor — the tool is a BigQuery remote function. A remote function is a GoogleSQL function backed by an HTTP endpoint running on Cloud Run functions or Cloud Run, wired up through the same CLOUD_RESOURCE connection type.
Two limits matter operationally:
- Data types are restricted. Remote functions accept and return Boolean, Bytes, Numeric, String, Date, Datetime, Time, Timestamp, and JSON.
ARRAY,STRUCT,INTERVAL, andGEOGRAPHYare not supported directly — complex payloads must be serialized intoJSONorSTRING. - Batching is a tuning knob.
max_batching_rowsin the function'sOPTIONScontrols how many rows BigQuery packs into each HTTP request. Set it too low and you pay per-request overhead on every row; set it too high and you risk the backing service timing out.
Topology 2: Enrich in Flight, in Dataflow
When enrichment must happen before the data lands — a fraud score that has to accompany the event, a lookup that must reflect state at event time, a streaming pipeline with a seconds-level freshness SLO — the call belongs inside the Beam pipeline.
Beam gives you three escalating options:
- Side inputs for small, slowly changing reference data. The lookup table is broadcast to every worker, so there is no per-element network call at all. This is the cheapest enrichment there is, and it is correct whenever the reference data fits comfortably in worker memory.
- The
Enrichmenttransform for key-value lookups against a remote service. Available from Apache Beam 2.54.0 in the Python SDK, it ships handlers for Cloud Bigtable, BigQuery, Cloud SQL (PostgreSQL, MySQL, SQL Server), and Vertex AI Feature Store. Critically, it provides the production behavior you would otherwise have to hand-roll: client-side throttling so the remote service is not overwhelmed, exponential backoff on service-side errors, batching that groups elements into a single lookup per batch, and optional Redis caching for frequently requested keys. - A custom
DoFncalling an external model endpoint when no handler fits. This is where pipelines go wrong, because every production concern becomes your responsibility: batching inside@ProcessElement, connection reuse via@Setup, retry with backoff, a dead-letter side output for elements the service rejects, and idempotency so that a retried bundle does not double-charge a metered API.
Why Batching Dominates the Cost Model
A per-element call to a model endpoint on a 50,000 events-per-second stream is 50,000 API calls per second, which will hit a quota long before it hits a budget. Both the Enrichment transform's built-in batching and max_batching_rows on a remote function exist for the same reason: the unit of cost and quota is the request, not the row. Any exam scenario reporting rate-limit errors, RESOURCE_EXHAUSTED, or stuck watermarks on an enrichment step is asking you to batch, throttle, or cache — not to add workers.
Choosing Between the Two Topologies
| Requirement in the Scenario | Enrich at Rest (BigQuery) | Enrich in Flight (Dataflow) |
|---|---|---|
| Data already lands in BigQuery and batch latency is acceptable | Yes | Overkill |
| Enriched value must accompany the event downstream in real time | No | Yes |
| Lowest operational overhead, SQL-only team | Yes | No |
| Lookup must reflect feature values at event time | No | Yes (Vertex AI Feature Store handler) |
| Unstructured files in Cloud Storage need document, image, or audio extraction | Yes (object table plus ML.PROCESS_DOCUMENT, ML.ANNOTATE_IMAGE, ML.TRANSCRIBE) | Possible but heavier |
| Proprietary internal scoring API | Remote function on Cloud Run | Custom DoFn |
| Reference data is small and rarely changes | Join to a table | Side input |
Governance and Regional Constraints
Three rules that show up as one-line disqualifiers in exam answers:
- De-identify before you enrich. If the column contains regulated identifiers, run Cloud DLP de-identification first. Sending raw PII to a model endpoint moves that data across a boundary your data-sovereignty controls may not cover.
- Keep the connection, the model, and the dataset regionally consistent. A Cloud resource connection and the dataset it serves must be regionally compatible; a scenario that mixes an EU dataset with a US endpoint is describing a residency violation, not just a latency problem.
- Enrichment failures need a destination. Whether it is a BigQuery error column, a Beam tagged side output, or a Pub/Sub dead-letter topic, a pipeline that silently drops elements the model could not process has no completeness SLI worth reporting.
Exam Traps and Antipatterns Summary
| Scenario Cue | Wrong Answer | Correct Architecture |
|---|---|---|
| "Extract fields from 2 million scanned invoices in Cloud Storage" | Read the files with a Dataflow TextIO source | Object table over the bucket plus ML.PROCESS_DOCUMENT with a Document AI remote model |
| "Sentiment score every support ticket nightly" | Build a streaming Dataflow pipeline | ML.UNDERSTAND_TEXT in a scheduled BigQuery query |
| "Fraud score must travel with the event in real time" | Enrich in BigQuery after loading | Enrich in flight in the Beam pipeline before the sink |
| "Enrichment step is throwing rate-limit errors" | Increase maxNumWorkers | Batch the calls, enable client-side throttling, and cache hot keys |
| "Attach a 5,000-row currency table to every event" | Call an external API per element | Beam side input broadcast to workers |
| "Feature values must match what the model saw at event time" | Query the warehouse table | Vertex AI Feature Store enrichment handler |
| "Vendor scoring service returns a nested payload" | Declare a STRUCT return type on the remote function | Return JSON or STRING and parse it in SQL; remote functions do not support STRUCT |
An insurance company stores 2 million scanned claim PDFs in a Cloud Storage bucket and needs the policy number, claim amount, and incident date extracted from each one into a BigQuery table. The extraction can run overnight, and the team writes SQL but not Java or Python. What is the most appropriate architecture?
A streaming Dataflow pipeline processes 40,000 payment events per second and enriches each one with a merchant risk profile by calling a Vertex AI endpoint from a custom DoFn, one request per element. The pipeline now reports persistent RESOURCE_EXHAUSTED errors and a watermark that will not advance. What is the correct remediation?
A data engineer wants to call an internal address-normalization microservice from BigQuery SQL. The service returns a nested object containing a normalized street, city, postal code, and a confidence score. The engineer creates a remote function backed by a Cloud Run service and declares the return type as STRUCT, but the function fails to create. What should the engineer do?