• Prefix every

  gcloud

  invocation, whatever the subcommand, with the metrics environment variables. Set them inline on each command; shell state may not persist between commands:
  bash

  CLOUDSDK_METRICS_REQUEST_ATTRIBUTION="agent-skills/1.0 (skill:bigquery-udfs)" \
  gcloud <command> [flags]

  Do not use

  gcloud config set

  for this: it would persist beyond the current task and mislabel unrelated usage.
• On direct HTTP calls to the BigQuery APIs (for example with

  curl

  ) or HTTP requests to the MCP server, append this exact string to the end of the existing User-Agent header — the collection pipeline parses the

  agent-skills/<version>

  and

  skill:<name>

  tokens, so any rewording breaks attribution:

  User-Agent: <existing_user_agent> agent-skills/1.0 (skill:bigquery-udfs)

• Constraint for Generated Code: The tracking prefix is strictly for commands you execute directly in the terminal. Do NOT include this tracking prefix in generated SQL, scripts, Terraform templates, or code provided to the user for their own repositories.

1. Install and Authenticate Google Cloud SDK: Install the Google Cloud SDK and authenticate:
   bash

   gcloud auth login

   Pass

   --project={project_id}

   and

   --region={region}

   (or

   --location

   for

   bq

   ) explicitly on each command instead of running

   gcloud config set

   , which persists beyond the task. Default to

   us-central1

   when the user has not specified a region (see BigQuery Locations).
2. Enable Required APIs:
   • For SQL, JavaScript, and offline Python UDFs:

     bigquery.googleapis.com

   • Additionally for Python UDFs calling APIs (

     WITH CONNECTION

     ) and Remote Functions:

     bigqueryconnection.googleapis.com

     (and

     run.googleapis.com

     for Remote Functions)
   bash

   CLOUDSDK_METRICS_REQUEST_ATTRIBUTION="agent-skills/1.0 (skill:bigquery-udfs)" \
   gcloud services enable bigquery.googleapis.com \
     bigqueryconnection.googleapis.com \
     run.googleapis.com --quiet

3. Verify Required IAM Roles:
   • All UDFs:

     roles/bigquery.dataEditor

     on the dataset and

     roles/bigquery.jobUser

     on the project.
   • When using

     WITH CONNECTION

     (API-calling Python UDFs & Remote Functions):
     • UDF Creator:

       roles/bigquery.connectionAdmin

       on the project (plus

       resourcemanager.projects.setIamPolicy

       , e.g.,

       roles/resourcemanager.projectIamAdmin

       , when granting project-level IAM roles to the connection service account via

       GRANT ... ON PROJECT

       ).
     • UDF Caller:

       roles/bigquery.connectionUser

       on the connection.

WITH CONNECTION

packages=[...]

library=['gs://...']

pd.DataFrame

pyarrow.RecordBatch

pd.Series

[!WARNING] Vectorized operations must be row-independent: BigQuery calls vectorized UDFs on arbitrary row batches (up to

max_batching_rows

). Do NOT compute global column statistics (such as z-scores, dataset mean/std, or rank) across

df

, because each batch only sees an arbitrary slice of rows. Use SQL window functions (

(val - AVG(val) OVER()) / NULLIF(STDDEV(val) OVER(), 0)

) for dataset-wide statistics. Vectorized Python UDFs should perform row-independent calculations (e.g. geometric distances or multi-column math).

CREATE OR REPLACE FUNCTION `{project_id}.{dataset_id}.vectorized_distance`(
  x1 FLOAT64, y1 FLOAT64, x2 FLOAT64, y2 FLOAT64
)
RETURNS FLOAT64
LANGUAGE python
OPTIONS (
  runtime_version = 'python-3.11',
  entry_point = 'compute_distance'
)
AS r'''
import numpy as np
import pandas as pd

def compute_distance(df: pd.DataFrame) -> pd.Series:
    return pd.Series(
        np.hypot(df['x2'] - df['x1'], df['y2'] - df['y1']),
        index=df.index,
    )
''';

WITH CONNECTION

try...except

• Transient errors only →

  NULL

  : Catch retryable exceptions (

  google.api_core.exceptions.TooManyRequests

  ,

  ServiceUnavailable

  ,

  DeadlineExceeded

  ,

  InternalServerError

  , and

  RetryError

  ), log them with

  logging.warning

  , and return

  None

  so a transient blip does not abort the query. Do not catch the base class

  GoogleAPICallError

  here: it also matches

  PermissionDenied

  ,

  Unauthenticated

  , and

  InvalidArgument

  .
• Fatal errors → raise: Let auth, permission, and invalid-argument errors propagate (or log and re-raise as

  RuntimeError

  ) so they surface as a user error on the query instead of silently producing

  NULL

  s.
• Never log row values: Anyone with Cloud Logging access can read UDF logs regardless of BigQuery table permissions. Log exception types and messages, not input or output data. Logs are only emitted when the session sets

  SET @@udf.properties = JSON '{"enableDebugOutput": true}';

  .
• Set a per-request

  timeout

  :

  Retry(timeout=...)

  only caps the total retry duration; also pass

  timeout=

  on each client call.

1. Create a Cloud Resource Connection and Retrieve its Service Account:
   bash

   bq mk --connection --connection_type=CLOUD_RESOURCE \
     --location={region} --project_id={project_id} {connection_id}
   
   bq show --connection {project_id}.{region}.{connection_id}

2. Grant Permissions to the Connection Service Account: Grant

   roles/serviceusage.serviceUsageConsumer

   on

   {project_id}

   plus the required API role (e.g.,

   roles/cloudtranslate.user

   ) on the target project. Running

   GRANT ... ON PROJECT

   modifies the project's IAM policy and requires

   resourcemanager.projects.setIamPolicy

   (for example,

   roles/resourcemanager.projectIamAdmin

   ); confirm with the user before running project-level IAM grants. The DCL

   connection:

   grantee takes the connection path (

   [project_id.]location.connection_id

   ), not the service account email; BigQuery resolves the connection's service account automatically (use

   "serviceAccount:{connection_sa_email}"

   to grant by email instead):
   sql

   GRANT `roles/serviceusage.serviceUsageConsumer`
   ON PROJECT `{project_id}`
   TO "connection:{project_id}.{region}.{connection_id}";
   
   GRANT `roles/cloudtranslate.user`
   ON PROJECT `{project_id}`
   TO "connection:{project_id}.{region}.{connection_id}";

3. Create the Python UDF with

   WITH CONNECTION

   (keep top-level Python statements flush-left at column 0):

CREATE OR REPLACE FUNCTION `{project_id}.{dataset_id}.translate_to_es`(
  text STRING
)
RETURNS STRING
LANGUAGE python
WITH CONNECTION `{project_id}.{region}.{connection_id}`
OPTIONS (
  entry_point = 'do_translate',
  runtime_version = 'python-3.11',
  packages = ['google-cloud-translate>=3.11', 'google-api-core']
)
AS r'''
import logging
from google.api_core import exceptions
from google.api_core.retry import Retry
from google.cloud import translate

client = translate.TranslationServiceClient()

TRANSIENT_ERRORS = (
    exceptions.TooManyRequests,
    exceptions.ServiceUnavailable,
    exceptions.DeadlineExceeded,
    exceptions.InternalServerError,
    exceptions.RetryError,
)

def do_translate(text: str) -> str:
    if not text:
        return None
    try:
        # Note: Replace {project_id} with your Google Cloud Project ID
        response = client.translate_text(
            request={
                "parent": "projects/{project_id}/locations/us-central1",
                "contents": [text],
                "target_language_code": "es",
                "mime_type": "text/plain",
            },
            retry=Retry(timeout=30.0),  # Total retry budget.
            timeout=10.0,  # Per-attempt deadline.
        )
        return response.translations[0].translated_text
    except TRANSIENT_ERRORS as e:
        # Log the error type only; never log row values.
        logging.warning("Transient Translation API error: %s", type(e).__name__)
        return None
    except exceptions.GoogleAPICallError as e:
        # PermissionDenied, Unauthenticated, InvalidArgument, etc. are fatal.
        logging.error("Fatal Translation API error: %s: %s", type(e).__name__, e)
        raise RuntimeError(f"Translation failed: {type(e).__name__}") from e
''';

container_cpu

container_memory

container_request_concurrency

SET @@udf.properties = JSON '{"enableDebugOutput": true}';

INFORMATION_SCHEMA.ROUTINES

1. Grant

   roles/run.invoker

   to the Connection Service Account (confirm with the user before modifying the Cloud Run service IAM policy):
   bash

   CLOUDSDK_METRICS_REQUEST_ATTRIBUTION="agent-skills/1.0 (skill:bigquery-udfs)" \
   gcloud run services add-iam-policy-binding {cloud_run_service} \
     --region={region} \
     --member="serviceAccount:{connection_sa_email}" \
     --role="roles/run.invoker"

2. Register the Remote Function: The endpoint must accept

   {"calls": [[arg1, ...], ...]}

   and return

   {"replies": [res1, ...]}

   with matching element count:
   sql

   CREATE OR REPLACE FUNCTION `{project_id}.{dataset_id}.enrich_address`(
     street STRING, postal_code STRING
   )
   RETURNS JSON
   REMOTE WITH CONNECTION `{project_id}.{region}.{connection_id}`
   OPTIONS (
     endpoint = 'https://{cloud_run_service_url}',
     max_batching_rows = 1000
   );

• Step 1 — Validate Python code before BigQuery DDL: For Python UDFs, validate the Python function and

  packages

  dependencies before submitting the BigQuery DDL—either locally with

  python3

  test inputs (

  None

  , edge cases,

  pd.DataFrame

  batches) or in a containerized Cloud Run Job (

  gcloud run jobs deploy {job_name} --source . --region={region}

  and

  gcloud run jobs execute {job_name} --region={region} --wait

  ; see Python UDFs Validation).
• Step 2 — Execute DDL and poll container build status: Run

  CREATE OR REPLACE FUNCTION

  . Because the DDL statement returns before the Python container image finishes building, poll

  INFORMATION_SCHEMA.ROUTINES

  until

  build_state

  transitions from

  IN_PROGRESS

  to

  SUCCEEDED

  (or fix and re-run if

  FAILED

  ):
  sql

  SELECT build_status.*
  FROM `{project_id}.{dataset_id}`.INFORMATION_SCHEMA.ROUTINES
  WHERE routine_name = '{udf_name}';

• Step 3 — Test the UDF with SQL

  SELECT

  : Once

  build_state = 'SUCCEEDED'

  (or immediately after DDL for SQL/JS UDFs), run a test

  SELECT

  query covering

  NULL

  and valid inputs.

• SQL and JavaScript UDFs: Templated SQL parameters (

  ANY TYPE

  ), aggregate UDFs, JavaScript type mappings, and Cloud Storage library loading (

  https://docs.cloud.google.com/bigquery/docs/user-defined-functions.md.txt

  ).
• Python UDFs: Offline vs.

  WITH CONNECTION

  online service access, Pandas/PyArrow vectorization, container resource limits (

  container_cpu

  /

  container_memory

  ), and Cloud Logging debugging (

  https://docs.cloud.google.com/bigquery/docs/user-defined-functions-python.md.txt

  ).
• Remote Functions: Cloud Run HTTP request and response JSON schema, batch sizing, retry semantics, and VPC egress (

  https://docs.cloud.google.com/bigquery/docs/remote-functions.md.txt

  ).
• IAM, Authorized Routines, and Quotas: Sharing routines across datasets (

  Authorized Routines

  ), container quotas, and troubleshooting errors.

• BigQuery Basics (

  bigquery-basics

  ): Dataset, table, and job management.
• BigQuery DataFrames: Creating Python UDFs from Python via

  @bpd.udf

  .
• BigQuery AI & ML: Built-in

  ML.GENERATE_TEXT

  and

  AI.GENERATE

  functions.