Unifying Enterprise Context: The Power of SAP and Google Cloud Synergy

SAP systems run the core of the enterprise, including finance, supply chain, HR, and customer experience, through end-to-end processes like Order-to-Cash, Procure-to-Pay, Plan-to-Produce, Record-to-Report, and Hire-to-Retire. To make fast, data-driven decisions and put AI to work, organizations want to combine native SAP business semantics with Google Cloud data and AI capabilities.

Historically, connecting these platforms required data replication pipelines, which added complexity, latency, and challenges in maintaining native SAP business context and hierarchies.

To simplify this, Google Cloud and SAP co-engineered SAP Business Data Cloud (BDC) Connect for Google BigQuery — now generally available.

Using a zero-copy architecture powered by open-source Delta Sharing with built-in access controls, this integration lets you query live SAP BDC data products directly from BigQuery — and vice versa — without building replication pipelines. This helps bring the scale of BigQuery to your SAP data while keeping governance in place and data fresh.

Official Resources & Key Documentation

Before diving into the configuration steps, bookmark these official resources to reference during your rollout:

Google Cloud Blog: SAP and Google Cloud launch BDC Connect for BigQuery
Google Documentation: About SAP Business Data Cloud integration | Lakehouse Docs
SAP Community: Announcing GA of SAP BDC Connect for Google BigQuery
Google Cloud docs: Set up cross-cloud connection for SAP BDC
SAP Help: Obtaining Access to Data Products through SAP Business Data Cloud Connect

Keep these open in a tab. The rest of this post walks through each configuration step.

Roles & Responsibilities: Team Alignment Matrix

You’ll need three people (or one person wearing three hats):

• BigQuery Administrator: Handles BigQuery Enterprise Plus capacity setup, sets up the Lakehouse catalog, and executes queries on shared tables through the Google Cloud Console.
• SAP Administrator: Deploys the BDC Connect instance, links the Google Cloud Service Account, and generates the activation link (connector endpoint and invitation code) in SAP for Me.
• SAP BDC User: Finds data products in the SAP BDC catalog, requests access, and installs them to the BDC Connect instance for BigQuery (Step 7).

Prerequisites & System Readiness

Before you begin the step-by-step walkthrough, verify that you have completed these preparation steps on both platforms.

• Tenant deployment: Ensure your SAP BDC environment is active on a supported cloud platform (such as Google Cloud or AWS).
• Ensure your SAP administrator has access to your SAP BDC environment to configure the BDC Connect application
• You must have appropriate IAM permissions in Google Cloud to create and manage Lakehouse Catalogs: BigLake Admin (roles/biglake.admin) or BigQuery Admin (roles/bigquery.admin).
• The target Google Cloud project must use BigQuery Enterprise Plus Edition.

Step-by-Step Screenshot-Guided Walkthrough

• Phase 1: Google Cloud Environment Preparation (Steps 1–3) — Configure BigQuery Enterprise Plus capacity, assign your project to the reservation, and initiate the Lakehouse Delta Sharing catalog.
• Phase 2: SAP BDC Provisioning & Mutual Handshake (Steps 4–6) — Provision the connector in SAP for Me, exchange credentials to enroll the catalog, and link the connection into the SAP landscape formation.
• Phase 3: Data Federation & Live BigQuery Analytics (Step 7) — Share SAP data products from the SAP BDC Catalog & Marketplace and execute zero-copy SQL queries directly within BigQuery Studio.

Step 1: [BigQuery Admin | Google Cloud] Enable BigQuery Enterprise Plus Capacity

1. Access the BigQuery interface via the Google Cloud Console.

2. Navigate through the sidebar to Administration > Workload management > Capacity management.

3. Click Create Reservation.

4. Reservation Name: Assign a unique identifier, such as gcp-sap-bdc-connect-bq (the name used in this walkthrough).

5. Location: Choose the specific region hosting your datasets; this must align with your Lakehouse catalog setting.

6. Edition: Pick Enterprise Plus from the available options.

• Why Enterprise Plus? BDC Connect queries require BigQuery Enterprise Plus edition. With baseline slots set to 0 and autoscaling on (below), you pay for autoscaled slots while queries run instead of paying for idle baseline capacity.

7. Slots:

• Configure Baseline slots to 0: This avoids paying for idle baseline capacity.
• Turn on Auto Scaling so BigQuery adds slots only while queries are running, up to the maximum you set. For this demo we used Extra Small (50 slots) as the maximum, which is enough for a few test queries. For production, size the maximum to your own workloads as part of your sizing exercise. The BigQuery slot estimator can help.

8. Finalize the configuration by clicking Save.

Your new reservation now appears under Capacity management:

Step 2: [BigQuery Admin | Google Cloud] Assign Your Project to the Reservation

1. Navigate to the Capacity management page.

2. Select your newly created reservation ID, then click the More actions menu (three dots) under the Actions column.

3. Click + Create Assignment.

4. For Rule type, select Job Routing (Assignment).

5. Select a resource: Choose the Project, Folder, or Organization that will be running the BigQuery jobs.

6. Job type: Select Query (this routes query jobs to the Enterprise Plus reservation). You can also add assignments for Pipeline or Background jobs if required.

7. Click Continue.

8. Reservation: Select the Enterprise Plus reservation created in the previous step (e.g., “gcp-sap-bdc-connect-bq”).

9. Click Continue. Your project will appear under your reservation (in this walkthrough, the reservation name and project ID share the name gcp-sap-bdc-connect-bq).

Verification: Once assigned, query jobs run within that project use Enterprise Plus capacity. You can verify this by checking the Job Information in the BigQuery Job History; it should list the reservation name and edition.

Step 3: [BigQuery Admin | Google Cloud] Create the Lakehouse Delta Sharing Catalog

A Delta Sharing catalog in Lakehouse serves as your gateway to federating with the remote SAP BDC catalog.

1. Enable the BigLake API.

2. Review the roles required to enable APIs, then select Enable the API.

3. In the Google Cloud console, open the Lakehouse page: Go to Lakehouse

4. In the navigation menu, click Runtime catalog.

5. Click Create catalog and select Federated catalog.

6. In the Catalog configuration section, enter the following:

a. For Federated catalog source, select SAP (Business Data Cloud).

b. For Catalog name (in Lakehouse), enter a name for your catalog.

c. For Data location, select the same Lakehouse region as reservation.

Click Continue. The next screen, Connection details, asks for a Connector endpoint and an Invitation code. You don’t have these yet. Your SAP admin generates them in Step 4, and you’ll come back here to enter them in Step.5 For now, continue past this screen.

7. Click Continue.

8. Retrieve the Google Cloud Service Account ID displayed on the page and provide this ID to your SAP administrator.

Why this matters: This service account is how SAP BDC recognizes and trusts BigQuery. When your SAP administrator registers this ID during BDC Connect provisioning, SAP BDC authorizes BigQuery to query Delta Sharing endpoints directly without needing static credentials, passwords, or shared secret keys.

Step 4: [SAP Admin | SAP for Me] Configure the BDC Connect Instance

Your SAP administrator must sign in to SAP for Me and provision a new instance of the BDC Connect application:

1. Go to Portfolio & Products and select Business Data Cloud.

2. Under Business Data Cloud, click Application.

3. Locate SAP Business Data Cloud Connect and click Start Provisioning.

4. Enter the Display Name.

5. Select or create a Resource Group.

6. Allocate BDC Capacity Units (CUs). How many you need depends entirely on your SAP BDC sizing exercise. Estimate your expected workloads with SAP’s Capacity Unit Cost Estimator, then assign quota to match. We used 10 CUs for this demo, which may not a production recommendation.

7. In the External System Instance Identifier field, provide the Google Cloud service account ID you copied in Step 3.

8. Choose a Region. Ensure this matches the region where your SAP BDC tenant is located (the BDC Core region).

9. Click Review, verify the details, and then click Finish.

10. Upon selecting Finish, a notification prompt entitled Creating solution will appear:

Your provisioning request for the 'GCP BDC Demo' solution has started and its resources are being set up in the selected resource group. The provisioning process can take a few moments to complete.
The new Solution resource will achieve the Ready status when all tenants are successfully provisioned, and integrations are fully established.

11. Navigate to the Resources view to monitor deployment progress for provisioned components.

After completion, go to the tenant details view. Hold the pointer over the View Tenant Notifications button to get the Activation Link, which contains the SAP Connector Endpoint and the SAP Invitation Code.

Provide the following activation parameters to your BigQuery administrator to complete the connection handshake:

Deconstructing the Handshake URL

When enrolling the catalog in the Google Cloud Console, split the activation URL into its two core parameters:

• Connector Endpoint: The base URL preceding ?code= (e.g., https://d1xxxx-xxx-xxxxx-xxx-xxxxxx.sharing.xx.xxxx-xxxx.hanaxxxx.ondemand.com).
• Invitation Code: The alphanumeric token following ?code= (e.g., 11exxx-xxxx-xxxx-xxxxx-xxxxxx6).

Separating these parameters accurately helps prevent handshake authentication errors during catalog enrollment. The most common setup mistake is pasting the whole URL into the endpoint field.

The status of this connection in SAP BDC remains “Processing” until the enrollment steps in BigQuery are complete.

Step 5: [BigQuery Admin | Google Cloud] Complete the Handshake & Enroll Catalog

To finalize the authentication handshake between SAP BDC and the Google Cloud Console, enroll the catalog using the values from your SAP admin.

1. Navigate to the Lakehouse page in Google Cloud Console. If the session timed out or was closed, re-open it via the main menu by selecting Runtime catalog.

2. Open the Catalog details page by selecting your targeted SAP Delta Sharing catalog.

3. Locate the top banner and click Enroll catalog.

4. Fill in the required information under Connection details:

• Connector endpoint: Paste the URL supplied by your SAP administrator.
• Invitation code: Enter the authentication code provided by your SAP administrator.

5. Click Save.

This completes the authentication handshake between SAP BDC and Google Cloud. The two platforms now trust each other.

Before sharing SAP BDC data products with Google BigQuery using the SAP BDC connection, complete one final setup and configuration step within SAP BDC to finalize system integration and permit data access.

Step 6: [SAP Admin | SAP BDC Landscape] Link BDC Connection to System Formation

Once enrollment finishes, the connection status in SAP BDC changes from “Processing” to “Ready.”

Why Formation Linking is Essential: In SAP BDC, components operate inside isolated security boundaries. Deploying a connector instance establishes the runtime, but it cannot access your enterprise SAP tenant data products until it is formally bound into your Customer Landscape System Formation.

1. Add the newly created instance (bdc-connect-854cc5e7) directly into your designated system formation.

2. Navigate to the Customer Landscape overview tab.

3. Locate your specific target BDC tenant and select Include System.

4. Choose your established BDC connection from the list, click Next to review the system assignment details, and confirm by selecting Include.

5. The system initiates automated background synchronization across systems; once this workflow finishes, the fully configured integration appears within your assigned formation.

6. Following successful setup, the instance bdc-connect-854cc5e7 becomes fully visible and active in SAP BDC, enabling direct live data product sharing with Google BigQuery. If it doesn’t appear, sync the BDC formation and check again.

Step 7: [SAP BDC User & BigQuery Analyst] Share a Data Product and Query It in BigQuery

This is where everything comes together. The connection is in place, so now you decide which SAP data BigQuery can see. We’ll share one data product, Maintenance Order, and then query it from BigQuery Studio. In our demo, the SAP side took only a few minutes.

7a. Confirm the connector is in your landscape

In SAP BDC, open System Landscape. You should see your connector (bdc-connect-854cc5e7 in our case) listed in your formation with the type SAP BDC Connect for Google BigQuery. If it’s there, Step 6 worked and you’re ready to share.

7b. Find the data product

Open Catalog & Marketplace → Search and switch to the Data Products tab.

Search for Maintenance Order. You’ll probably see more than one result, because the same data product can come from different source systems. Check the system name on each card and pick the one from the source system connected to your formation.

7c. Choose BigQuery as the target

Click the cart icon at the top right to open the Request Cart. Under Target System, click Select System.

Search for your connector and select it.

Tip: If you see “Some systems are unavailable for selection,” don’t worry. It refers to other systems in the formation. Your connector just needs to appear in the list.

7d. Add the data product and send the request

With the target set, click Supported Data Products so you only see products this connector can receive. Then click the cart icon on the Maintenance Order card, check that it appears in the Request Cart, and click Proceed.

In the Request Access dialog, add a short comment (it’s required, so something like “SAP demo share” is fine), tick the terms-and-conditions box, and click Send. The end date is optional.

7e. Confirm the share is installed

A notification should appear shortly saying the data product is installed to your connector and ready to use.

In our demo tenant, approval came through in seconds. In your landscape, the request may wait for the data product owner to approve it, so check with your SAP administrator if nothing arrives.

To double-check, click on the Notification or open Catalog & Marketplace → Data Product Access and select the Agreements tab. Maintenance Order should show Installed with status Current. If it hasn’t been installed yet, open the agreement and click Install. The data product does not appear in BigQuery until this is done.

That’s everything on the SAP side. Over to BigQuery.

7f. Find the data in BigQuery Studio

In the Google Cloud console, open BigQuery → Studio. In the Explorer, expand your project and click Datasets. The shared data product appears as a new dataset with its source shown as SAP. You don’t need to import or load anything. If it isn’t there yet, give it a minute and click Refresh.

The dataset name may look long. It’s built from your catalog, the data product (share), and its schema: catalog.share.schema. Open it and you’ll see that one data product contains a whole set of related tables, such as the order header, operations, partners, and status texts, organized just as they are in SAP.

On the Details tab, the catalog info shows Source: SAP and Type: Delta Sharing. That confirms BigQuery is reading the data from SAP through Delta Sharing, not from a replicated copy.

Click a table, such as maintenanceorder, to see its schema. The field names are the SAP business names your teams already know.

7g. Run your first query

Click Query (or open a new query tab). Start with a simple SELECT to see the data, then try a more focused question, such as the 50 most recent preventive maintenance orders (PM01). Replace <source_system> with the dataset name you see in your Explorer:

SELECT *
FROM `gcp-sap-bdc-connect-bq.bdc-con-bq-catalog.maintenanceorder_v1_<source_system>.maintenanceorder.maintenanceorder`
LIMIT 1000;
 - Preventive maintenance orders (PM01), most recent first
SELECT
MaintenanceOrder,
MaintenanceOrderType,
MaintenanceOrderDesc,
MaintOrdBasicStartDate,
MaintOrdBasicEndDate
FROM `gcp-sap-bdc-connect-bq.bdc-con-bq-catalog.maintenanceorder_v1_<source_system>.maintenanceorder.maintenanceorder`
WHERE MaintenanceOrderType = 'PM01'
ORDER BY MaintOrdBasicStartDate DESC
LIMIT 50;

Both queries read live data straight from SAP BDC, so you don’t need to schedule copies or keep pipelines running. Here’s the first query running in BigQuery Studio:

Summary

In seven steps you connected SAP Business Data Cloud to BigQuery, without building a single pipeline:

• On Google Cloud, you set up Enterprise Plus capacity and created a Lakehouse Delta Sharing catalog.
• In SAP for Me, you provisioned BDC Connect and generated the activation link. Back in Google Cloud, you enrolled the catalog to complete the handshake, and then added the connector to your SAP BDC formation.
• Then your SAP BDC user installed the Maintenance Order data product, and it appeared in BigQuery as a dataset you could query right away, with native SAP field names intact.

The result: your analysts can work with up-to-date SAP data where it lives, governed by SAP at the source and by IAM on the Google Cloud side, with no copies to schedule or maintain.

Good to know before production

Before you move to production, review the current limitations in the Google Cloud documentation (Limitations and considerations) and the SAP BDC Connect documentation on SAP Help. They cover access control, supported data product names, metadata refresh and caching, and data residency, and they change as the product evolves.

Appendix: Key Concepts and Terms

• Lakehouse: The Google Cloud service that manages catalogs and metadata for open table formats such as Apache Iceberg, so engines like BigQuery can query data where it lives, including across clouds.
• Federated Delta Lake Catalog: A Lakehouse catalog type that connects to SAP BDC using the open Delta Sharing protocol. This is the catalog you create in Step 3.
• Federated Catalog: How BigQuery keeps SAP BDC metadata current. It asks the remote SAP BDC catalog for its shares, schemas, and tables, so you don’t copy or maintain that metadata by hand.
• Connection: The part of the Lakehouse catalog that links and authenticates BigQuery and SAP BDC, using the connector endpoint and invitation code from Steps 4 and 5.

SAP BDC data appears in BigQuery in a five-level hierarchy that follows the Delta Sharing protocol:

PROJECT_ID.CATALOG_ID.SHARE_NAME.SCHEMA_NAME.TABLE_NAME

The queries in Step 7g use exactly this path.

• Catalog ID: The Lakehouse catalog resource in your Google Cloud project, and the top-level container for everything shared from one SAP BDC Connector (bdc-con-bq-catalog in this walkthrough).
• Share (SAP Data Product): A logical group of tables shared for consumption, such as Maintenance Order. Each data product maps to a Delta Sharing share, the first level under the catalog.
• Schema (Delta Schema): The second level, inside a share. It groups related tables.
• Table (SAP Table): The data object inside a schema, such as maintenanceorder. This is what you query in BigQuery.
• SAP BDC Connector: The SAP BDC configuration that authorizes access from a specific Google Cloud project, through the service account you registered in Step 4.

Step-by-Step Guide: Configuring SAP BDC Connect for Google BigQuery was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

Deploying a model like PLLuM on GPU architecture is as simple as with any other model. However, when you consider running it on a TPU, you may discover that inference engines like vLLM don’t list it as a supported model. Does it mean you can’t run it on TPUs? No! In this article, I’ll show you how to set up a PLLuM inference service on Trillium TPU nodes in GKE.

Setting up the GKE cluster

We will start with setting up a simple GKE Autopilot cluster. This will allow us to focus on deploying the model, without manual node management.

Let’s begin with some environment variables:

export PROJECT_ID="YOUR_PROJECT_ID"
export CLUSTER_NAME="YOUR_CLUSTER_NAME"
export REGION="CLUSTER_REGION" 
export ZONE="CLUSTER_ZONE"
export RESERVATION="RESERVATION_NAME"
export HF_TOKEN="your-hugging-face-token"

In my example, I will be using a TPU reservation I made using DWS Calendar Mode. This way I won’t have to worry about hardware availability. Other deployment options are also available, but don’t guarantee hardware availability.

Create the GKE cluster, set up kubectl to work on this cluster and create a Kubernetes secret that will hold your Hugging Face read token:

gcloud container clusters create-auto $CLUSTER_NAME \
    --project=$PROJECT_ID \
    --region=$REGION

gcloud container clusters get-credentials $CLUSTER_NAME --location=$REGION --project=$PROJECT_ID
    
kubectl create secret generic hf-secret \
    --from-literal=hf_api_token=${HF_TOKEN} \
    --dry-run=client -o yaml | kubectl apply -f -

Deploying the model on a single TPU v6e chip

We can start with a simple deployment of CYFRAGOVPL/Llama-PLLuM-8B-instruct-2512 — this is a small model (16 GB) that fits just fine in the memory of a single TPU v6e chip (32 GB). That makes our deployment simple and easy, though limited in performance. It is the best option for development and testing. Use the following YAML to start your single-chip deployment (save it as pllum-1.yaml):

apiVersion: apps/v1
kind: Deployment
metadata:
  name: vllm-tpu
spec:
  replicas: 1
  selector:
    matchLabels:
      app: vllm-tpu
  template:
    metadata:
      labels:
        app: vllm-tpu
    spec:
      nodeSelector:
        cloud.google.com/gke-tpu-topology: 1x1
        # Following values can be used to run v6e TPUs with a reservation.
        cloud.google.com/gke-tpu-accelerator: tpu-v6e-slice
        cloud.google.com/reservation-affinity: "specific"
        cloud.google.com/reservation-name: RESERVATION_NAME
      containers:
      - name: vllm-tpu
        image: docker.io/vllm/vllm-tpu:latest
        command: ["python3", "-m", "vllm.entrypoints.openai.api_server"]
        args:
        - --host=0.0.0.0
        - --port=8000
        - --tensor-parallel-size=1
        - --max-model-len=4096
        - --model=CYFRAGOVPL/Llama-PLLuM-8B-instruct-2512
        - --max-num-batched-tokens=512
        - --max-num-seqs=128
        env:
        - name: HUGGING_FACE_HUB_TOKEN
          valueFrom:
            secretKeyRef:
              name: hf-secret
              key: hf_api_token
        ports:
        - containerPort: 8000
        resources:
          limits:
            google.com/tpu: 1
        readinessProbe:
          tcpSocket:
            port: 8000
          initialDelaySeconds: 1800
          periodSeconds: 60
          failureThreshold: 10
          timeoutSeconds: 5
        volumeMounts:
        - name: dshm
          mountPath: /dev/shm
      volumes:
      - name: dshm
        emptyDir:
          medium: Memory
---
apiVersion: v1
kind: Service
metadata:
  name: vllm-service
spec:
  selector:
    app: vllm-tpu
  type: LoadBalancer
  ports:
    - name: http
      protocol: TCP
      port: 8000
      targetPort: 8000

Remember to replace RESERVATION_NAME with a proper value. Then apply the file with kubectl apply -f pllum-1.yaml.

The deployment will take a while. You can watch how first the TPU node is created (kubectl get nodes) and how the vLLM pod starts (kubectl logs -l app=vllm-tpu).

Once the pod is ready to work, you can check the external IP of the created service with: kubectl get svc— just check what’s in the External IP column.

Now, when we know that external IP, we can send a request to our model:

curl http://${EXTERNAL_IP}:8000/v1/completions \
    -H "Content-Type: application/json" \
    -d '{
        "model": "CYFRAGOVPL/Llama-PLLuM-8B-instruct-2512",
        "prompt": "Jak to jest być skrybą?",
        "max_tokens": 150,
        "temperature": 0.7
    }'

The answer you get is a JSON containing various information, including the completion generated by the model!

Scaling to 8 TPUs

To handle more requests concurrently and increase the efficiency of your service, you may want to scale the vLLM service to work with more than one TPU chip. If you try to simply switch — tensor-parallel-size to 8, you will get an error message similar to:

(EngineCore pid=572) jax._src.sharding.IndivisibleError: One of device_put args was given the sharding NamedSharding(mesh=Mesh(‘data’: 1, ‘model’: 8, axis_types=(Auto, Auto)), spec=P((‘data’, ‘model’), None), memory_kind=device) implies that array axis 0 is partitioned 8 times, but the dimension size is 128259 (full shape: (128259, 4096), per-dimension tiling factors: (8, 1) should evenly divide the shape). This error occurs at source: /workspace/tpu_inference/tpu_inference/layers/common/utils.py:149:23 (general_device_put.<locals>._put)

The model we chose (Llama-PLLuM-8B-instruct) has a vocabulary size of 128259 — that’s not divisible by 8! The way vLLM spreads the work across multiple chips requires the model to have a vocabulary divisible by the number of chips that will be used. In this case, we will need to pad the model’s vocabulary to 128264 with “fake tokens”.

Here’s a script that will do just that. It will download the model locally, modify it, and store it in a new directory:

# pip install torch --index-url https://download.pytorch.org/whl/cpu
# pip install huggingface_hub safetensors

import json
import os
import torch
from huggingface_hub import snapshot_download
from safetensors.torch import load_file, save_file

MODEL_ID = "CYFRAGOVPL/Llama-PLLuM-8B-instruct-2512"
OUTPUT_DIR = "./Llama-PLLuM-8B-instruct-2512-padded"
TARGET_VOCAB_SIZE = 128264  # 16033 * 8

# 1. Download model snapshot
model_path = snapshot_download(repo_id=MODEL_ID)
os.makedirs(OUTPUT_DIR, exist_ok=True)

# 2. Copy and update config.json
with open(os.path.join(model_path, "config.json"), "r") as f:
    config = json.load(f)

old_vocab_size = config["vocab_size"]
pad_amount = TARGET_VOCAB_SIZE - old_vocab_size
config["vocab_size"] = TARGET_VOCAB_SIZE

with open(os.path.join(OUTPUT_DIR, "config.json"), "w") as f:
    json.dump(config, f, indent=2)

# 3. Copy other metadata files (tokenizers, generation configs, etc.)
for file_name in os.listdir(model_path):
    if not file_name.endswith(".safetensors") and file_name != "config.json":
        src = os.path.join(model_path, file_name)
        dst = os.path.join(OUTPUT_DIR, file_name)
        if os.path.isfile(src):
            os.system(f"cp {src} {dst}")

# 4. Pad embedding and lm_head tensors in safetensors
for file_name in os.listdir(model_path):
    if file_name.endswith(".safetensors"):
        tensors = load_file(os.path.join(model_path, file_name))
        modified = False

        for key in ["model.embed_tokens.weight", "lm_head.weight"]:
            if key in tensors:
                tensor = tensors[key]
                if tensor.shape[0] == old_vocab_size:
                    padding = torch.zeros(
                        (pad_amount, *tensor.shape[1:]), dtype=tensor.dtype
                    )
                    tensors[key] = torch.cat([tensor, padding], dim=0)
                    modified = True

        save_file(tensors, os.path.join(OUTPUT_DIR, file_name))

print(f"✅ Successfully padded model saved to: {OUTPUT_DIR}")

To make use of the new model, we need to upload it to a Cloud Storage Bucket and allow the vLLM pod access to it, using the Workload Identity bindings.

# We start with creating a new bucket
export BUCKET_NAME="padded-pllum-model"

gcloud storage buckets create gs://${BUCKET_NAME} \
    --project=${PROJECT_ID} \
    --location=${REGION}

# Upload the modified model to the bucket
gcloud storage cp -r ./Llama-PLLuM-8B-instruct-2512-padded gs://${BUCKET_NAME}/

# Create GCP Service Account and bind with GKE Service Account via Workload Identity for GCS Fuse
export GSA_NAME="vllm-pllum-gcs-sa"
export KSA_NAME="default"
export NAMESPACE="default"

gcloud iam service-accounts create ${GSA_NAME} \
    --project=${PROJECT_ID}

gcloud storage buckets add-iam-policy-binding gs://${BUCKET_NAME} \
    --member="serviceAccount:${GSA_NAME}@${PROJECT_ID}.iam.gserviceaccount.com" \
    --role="roles/storage.objectViewer"

gcloud iam service-accounts add-iam-policy-binding ${GSA_NAME}@${PROJECT_ID}.iam.gserviceaccount.com \
    --project=${PROJECT_ID} \
    --role="roles/iam.workloadIdentityUser" \
    --member="serviceAccount:${PROJECT_ID}.svc.id.goog[${NAMESPACE}/${KSA_NAME}]"

kubectl annotate serviceaccount ${KSA_NAME} \
    --namespace=${NAMESPACE} \
    iam.gke.io/gcp-service-account=${GSA_NAME}@${PROJECT_ID}.iam.gserviceaccount.com \
    --overwrite

At this point, all that’s left is to modify the pllum-1.yaml file to use the new, modified model and 8 TPUs. Let’s save it as pllum-2.yaml:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: vllm-tpu
spec:
  replicas: 1
  selector:
    matchLabels:
      app: vllm-tpu
  template:
    metadata:
      labels:
        app: vllm-tpu
      annotations:
        gke-gcsfuse/volumes: "true"
    spec:
      nodeSelector:
        cloud.google.com/gke-tpu-topology: 2x4
        # Following values can be used to run v6e TPUs with a reservation.
        cloud.google.com/gke-tpu-accelerator: tpu-v6e-slice
        cloud.google.com/reservation-affinity: "specific"
        cloud.google.com/reservation-name: RESERVATION_NAME
      containers:
      - name: vllm-tpu
        image: docker.io/vllm/vllm-tpu:latest
        command: ["python3", "-m", "vllm.entrypoints.openai.api_server"]
        args:
        - --host=0.0.0.0
        - --port=8000
        - --tensor-parallel-size=8
        - --max-model-len=4096
        - --model=/models/Llama-PLLuM-8B-instruct-2512-padded
        - --served-model-name=CYFRAGOVPL/Llama-PLLuM-8B-instruct-2512
        - --max-num-batched-tokens=512
        - --max-num-seqs=128
        env:
        - name: HUGGING_FACE_HUB_TOKEN
          valueFrom:
            secretKeyRef:
              name: hf-secret
              key: hf_api_token
        ports:
        - containerPort: 8000
        resources:
          limits:
            google.com/tpu: 8
        readinessProbe:
          tcpSocket:
            port: 8000
          initialDelaySeconds: 1800
          periodSeconds: 60
          failureThreshold: 10
          timeoutSeconds: 5
        volumeMounts:
        - name: dshm
          mountPath: /dev/shm
        - name: gcs-fuse-csi-ephemeral
          mountPath: /models
          readOnly: true
      volumes:
      - name: dshm
        emptyDir:
          medium: Memory
      - name: gcs-fuse-csi-ephemeral
        csi:
          driver: gcsfuse.csi.storage.gke.io
          readOnly: true
          volumeAttributes:
            bucketName: padded-pllum-model
            mountOptions: "implicit-dirs"
---
apiVersion: v1
kind: Service
metadata:
  name: vllm-service
spec:
  selector:
    app: vllm-tpu
  type: LoadBalancer
  ports:
    - name: http
      protocol: TCP
      port: 8000
      targetPort: 8000

Once again, remember to replace RESERVATION_NAME with the proper name of your TPU reservation. Then apply the new YAML with kubectl apply -f pllum-2.yaml. Make sure you trigger the recreation of the vLLM pod by manually deleting it with: kubectl delete pod -l app=vllm-tpu.

You can verify that it works by again using a simple curl command:

# Get the external ip from: kubectl get svc

curl http://${EXTERNAL_IP}:8000/v1/completions \
    -H "Content-Type: application/json" \
    -d '{
        "model": "CYFRAGOVPL/Llama-PLLuM-8B-instruct-2512",
        "prompt": "Jak to jest być skrybą?",
        "max_tokens": 150,
        "temperature": 0.7
    }'

When you’re done with your experiments, you can delete the deployment, the cluster, and the storage bucket with the following commands:

# Delete the deployment
kubectl delete -f pllum-2.yaml

# Delete the cluster
gcloud container clusters delete $CLUSTER_NAME --location=$REGION --project=$PROJECT_ID

# Delete the storage bucket
gcloud storage rm --recursive gs://BUCKET_NAME

Serving Polish Large Language Model (PLLuM) on TPUs was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

How to build a zero-lock-in Change Data Capture pipeline using Cloud SQL, Datastream, BigQuery Managed Iceberg Tables, and Cloud Storage — plus the real-world engineering patterns for turning Append-Only CDC logs into upserted Silver Iceberg tables.

Introduction: The Tension Between Real-Time CDC and Open Table Formats

Modern data engineering teams usually want two things that historically required painful trade-offs:

1. Real-Time Operational Analytics (CDC): Replicating INSERT, UPDATE, and DELETE transactions from operational PostgreSQL databases within seconds so business dashboards and AI agents never run on stale data.

2. An Open Data Lakehouse Architecture: Storing analytical tables in open formats — specifically Apache Iceberg backed by Parquet files in customer-owned Google Cloud Storage (GCS) buckets — so data is never locked into a single proprietary storage engine.

Traditionally, combining real-time PostgreSQL CDC with Apache Iceberg meant managing a complex, heavy stack: Debezium, Apache Kafka, Schema Registry, Spark Structured Streaming or Apache Flink, and custom compaction cron jobs to prevent the notorious Iceberg “small file problem.”

With Google Cloud Datastream and BigQuery Managed Tables for Apache Iceberg (BigLake), you can now stream PostgreSQL Write-Ahead Log (WAL) changes directly into governed Apache Iceberg tables in your own GCS bucket — completely serverless.

In this hands-on guide, we will build this pipeline end-to-end on Google Cloud and solve three real-world engineering nuances you will encounter in production:

• Configuring Cloud SQL for PostgreSQL logical decoding and Datastream blmtConfig (BigLake Managed Tables) via the gcloud CLI.
• Understanding why Datastream writes to Apache Iceberg in Append-Only mode (and how its datastream_metadata struct differs from native BigQuery Merge tables).
• Implementing both a Zero-Latency Deduplicated View and an atomic ACID MERGE INTO Silver Iceberg Table to handle row-level updates and deletes.

Architecture Overview

Our pipeline consists of four layers:

1. Operational Source (OLTP): A Cloud SQL for PostgreSQL 15 instance configured with cloudsql.logical_decoding=on, a PUBLICATION, and a pgoutput logical replication slot.

2. Serverless CDC Ingestion: Google Cloud Datastream reads the PostgreSQL WAL stream and writes via the BigQuery Storage Write API.

3. Bronze Open Lakehouse Layer (lakehouse_bronze.public_orders): Configured via Datastream’s blmtConfig (tableFormat: ICEBERG, fileFormat: PARQUET), Datastream lands every change event (INSERT, UPDATE-INSERT, UPDATE-DELETE, DELETE) into an Apache Iceberg table stored in our GCS bucket via a BigQuery Cloud Resource Connection.

4. Silver Current-State Layer (lakehouse_silver):

• Pattern A (v_orders_current): A real-time SQL View using QUALIFY ROW_NUMBER() that resolves the latest state per primary key on the fly.
• Pattern B (lakehouse_silver.orders): A materialized Apache Iceberg table updated via BigQuery’s native ACID MERGE INTO DML, with exported Iceberg V2 metadata (metadata.json & Avro manifest lists) ready for Apache Spark, Trino, or PyIceberg.

Step 1: Provisioning the Open Lakehouse Foundation & Cloud SQL

First, let’s define our environment variables and enable the required Google Cloud APIs (sqladmin, datastream, bigquery, bigqueryconnection, biglake, and storage):

export PROJECT_ID="your-gcp-project-id"
export REGION="us-central1"
export GCS_BUCKET="${PROJECT_ID}-iceberg-lakehouse"
export BQ_CONNECTION_ID="iceberg-gcs-connection"
export BQ_BRONZE_DATASET="lakehouse_bronze"
export BQ_SILVER_DATASET="lakehouse_silver"
export CLOUDSQL_INSTANCE="pg-cdc-source"
export PG_DATABASE="ecommerce"
export PG_USER="postgres"
export PG_PASSWORD="YourStrongPassword2026!"

1.1 Create the GCS Bucket & BigQuery Cloud Resource Connection

BigQuery Managed Iceberg tables require a **Cloud Resource Connection** whose service account has `roles/storage.admin` on your GCS bucket:

# Create the GCS bucket for Apache Iceberg data and metadata
gcloud storage buckets create "gs://${GCS_BUCKET}" \
 - project="${PROJECT_ID}" \
 - location="${REGION}" \
 - uniform-bucket-level-access
# Create the BigQuery Cloud Resource Connection
bq mk - connection \
 - connection_type=CLOUD_RESOURCE \
 - project_id="${PROJECT_ID}" \
 - location="${REGION}" \
"${BQ_CONNECTION_ID}"
# Retrieve the Connection's Service Account and grant Storage Admin on the bucket
BQ_CONN_SA=$(bq show - format=json - connection "${PROJECT_ID}.${REGION}.${BQ_CONNECTION_ID}" \
| python3 -c "import sys, json; print(json.load(sys.stdin)['cloudResource']['serviceAccountId'])")
gcloud storage buckets add-iam-policy-binding "gs://${GCS_BUCKET}" \
 - member="serviceAccount:${BQ_CONN_SA}" \
 - role="roles/storage.admin" \
 - project="${PROJECT_ID}"
# Create Bronze and Silver datasets in BigQuery
bq - location="${REGION}" mk - dataset "${PROJECT_ID}:${BQ_BRONZE_DATASET}"
bq - location="${REGION}" mk - dataset "${PROJECT_ID}:${BQ_SILVER_DATASET}"

1.2 Create the Cloud SQL for PostgreSQL Instance with Logical Decoding

Engineering Gotcha #1: On Cloud SQL for PostgreSQL, the database flag that sets wal_level = logical is named cloudsql.logical_decoding=on (not cloudsql.logical_replication=on, which is used by AlloyDB).

We also dynamically fetch Datastream’s regional static IPs so Datastream can connect to Cloud SQL:

DS_IPS=$(gcloud datastream locations fetch-static-ips "${REGION}" \
 - project="${PROJECT_ID}" \
 - format="value(staticIps)" | tr ';' ',' | tr '\n' ',' | sed 's/,$//')
gcloud sql instances create "${CLOUDSQL_INSTANCE}" \
 - project="${PROJECT_ID}" \
 - database-version=POSTGRES_15 \
 - edition=ENTERPRISE \
 - tier=db-custom-2–7680 \
 - region="${REGION}" \
 - root-password="${PG_PASSWORD}" \
 - database-flags=cloudsql.logical_decoding=on,max_replication_slots=10,max_wal_senders=10 \
 - authorized-networks="${DS_IPS}" \
 - storage-type=SSD \
 - storage-size=20GB
gcloud sql databases create "${PG_DATABASE}" \
 - instance="${CLOUDSQL_INSTANCE}" \
 - project="${PROJECT_ID}"

Step 2: Preparing PostgreSQL Publication & Logical Replication Slot

Next, connect to the ecommerce database on Cloud SQL and create an operational public.orders table, insert 5 initial seed rows, create a PostgreSQL publication, and initialize a pgoutput logical replication slot:

- 1. Create the operational `orders` table
CREATE TABLE IF NOT EXISTS public.orders (
order_id VARCHAR(20) PRIMARY KEY,
customer_id VARCHAR(50) NOT NULL,
product_category VARCHAR(50) NOT NULL,
order_status VARCHAR(30) NOT NULL,
total_amount NUMERIC(12, 2) NOT NULL,
shipping_city VARCHAR(50) NOT NULL,
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 - 2. Configure REPLICA IDENTITY DEFAULT so primary keys are logged on UPDATE/DELETE
ALTER TABLE public.orders REPLICA IDENTITY DEFAULT;
 - 3. Insert 5 seed orders (to test Datastream initial backfill into Iceberg)
INSERT INTO public.orders (order_id, customer_id, product_category, order_status, total_amount, shipping_city, updated_at)
VALUES
('ORD-1001', 'CUST-01', 'Electronics', 'PENDING', 450.00, 'Jakarta', NOW() - INTERVAL '2 hours'),
('ORD-1002', 'CUST-02', 'Home Office', 'PROCESSING', 180.50, 'Surabaya', NOW() - INTERVAL '90 minutes'),
('ORD-1003', 'CUST-03', 'Books', 'PENDING', 35.00, 'Bandung', NOW() - INTERVAL '60 minutes'),
('ORD-1004', 'CUST-04', 'Gaming', 'SHIPPED', 890.00, 'Singapore', NOW() - INTERVAL '30 minutes'),
('ORD-1005', 'CUST-05', 'Audio', 'DELIVERED', 220.00, 'Jakarta', NOW() - INTERVAL '15 minutes')
ON CONFLICT (order_id) DO NOTHING;
 - 4. Grant replication role and create Publication + Replication Slot
ALTER USER postgres WITH REPLICATION;
CREATE PUBLICATION datastream_iceberg_pub FOR TABLE public.orders;
SELECT pg_create_logical_replication_slot('datastream_iceberg_slot', 'pgoutput');

Step 3: Configuring Datastream for BigQuery Apache Iceberg Tables (blmtConfig)

Now we create the Datastream Source and Destination Connection Profiles:

PG_PUBLIC_IP=$(gcloud sql instances describe "${CLOUDSQL_INSTANCE}" \
 - project="${PROJECT_ID}" \
 - format="value(ipAddresses[0].ipAddress)")
gcloud datastream connection-profiles create pg-source-profile \
 - location="${REGION}" \
 - project="${PROJECT_ID}" \
 - display-name="pg-source-profile" \
 - type=POSTGRESQL \
 - postgresql-hostname="${PG_PUBLIC_IP}" \
 - postgresql-port=5432 \
 - postgresql-username="${PG_USER}" \
 - postgresql-password="${PG_PASSWORD}" \
 - postgresql-database="${PG_DATABASE}" \
 - static-ip-connectivity
gcloud datastream connection-profiles create bq-iceberg-dest-profile \
 - location="${REGION}" \
 - project="${PROJECT_ID}" \
 - display-name="bq-iceberg-dest-profile" \
 - type=BIGQUERY \
 - static-ip-connectivity

Configuring blmtConfig (BigLake Managed Tables for Apache Iceberg)

To instruct Datastream to create and stream into Apache Iceberg tables on GCS rather than proprietary BigQuery native storage, we pass a blmtConfig block inside bigquery_iceberg_destination_config.json.

Engineering Gotcha #2: When configuring blmtConfig via the Datastream API/CLI:

1. connectionName must follow the compact {project}.{location}.{name} format (not the full projects/…/locations/…/connections/… resource URI).

2. rootPath must begin with a leading slash / (e.g., "/bronze").

3. Streaming into Apache Iceberg tables requires "appendOnly": {} write mode.

{
  "singleTargetDataset": {
    "datasetId": "YOUR_PROJECT_ID:lakehouse_bronze"
  },
  "appendOnly": {},
  "blmtConfig": {
    "bucket": "YOUR_PROJECT_ID-iceberg-lakehouse",
    "rootPath": "/bronze",
    "connectionName": "YOUR_PROJECT_ID.us-central1.iceberg-gcs-connection",
    "fileFormat": "PARQUET",
    "tableFormat": "ICEBERG"
  }
}

Create and start the stream:

gcloud datastream streams create pg-to-iceberg-cdc-stream \
 - location="${REGION}" \
 - project="${PROJECT_ID}" \
 - display-name="pg-to-iceberg-cdc-stream" \
 - source="pg-source-profile" \
 - postgresql-source-config="configs/postgresql_source_config.json" \
 - destination="bq-iceberg-dest-profile" \
 - bigquery-destination-config="configs/bigquery_iceberg_destination_config.json" \
 - backfill-all
# Once the stream reaches NOT_STARTED state, start it:
gcloud datastream streams update pg-to-iceberg-cdc-stream \
 - location="${REGION}" \
 - project="${PROJECT_ID}" \
 - state=RUNNING \
 - update-mask=state

Step 4: Simulating Real-Time OLTP Changes (INSERT, UPDATE, DELETE)

Once the initial 5 seed rows are backfilled into lakehouse_bronze.public_orders, let’s simulate real-time operational transactions in PostgreSQL:

• INSERT 2 new orders (ORD-1006 and ORD-1007)
• UPDATE ORD-1001 (PENDING → SHIPPED) and ORD-1002 (PROCESSING → DELIVERED, amount updated to 195.00)
• DELETE cancelled order ORD-1003

INSERT INTO public.orders (order_id, customer_id, product_category, order_status, total_amount, shipping_city, updated_at)
VALUES
('ORD-1006', 'CUST-06', 'Smartphones', 'PENDING', 699.00, 'Jakarta', NOW()),
('ORD-1007', 'CUST-07', 'Wearables', 'PAID', 149.90, 'Medan', NOW());
UPDATE public.orders
SET order_status = 'SHIPPED', updated_at = NOW()
WHERE order_id = 'ORD-1001';
UPDATE public.orders
SET order_status = 'DELIVERED', total_amount = 195.00, updated_at = NOW()
WHERE order_id = 'ORD-1002';
DELETE FROM public.orders
WHERE order_id = 'ORD-1003';

Inspecting the Bronze Apache Iceberg Change Log in BigQuery

Engineering Gotcha #3: Unlike Datastream’s MERGE mode (which adds a boolean datastream_metadata.is_deleted field), Datastream’s APPEND_ONLY mode for Apache Iceberg uses the schema:

STRUCT<uuid STRING, source_timestamp INT64, change_sequence_number STRING, change_type STRING, sort_keys ARRAY<STRING>>.

Every mutation is recorded via datastream_metadata.change_type (INSERT, UPDATE-INSERT, UPDATE-DELETE, or DELETE).

Let’s query our Bronze Iceberg table:

SELECT
order_id,
customer_id,
order_status,
total_amount,
datastream_metadata.change_type,
TIMESTAMP_MICROS(datastream_metadata.source_timestamp) AS source_ts,
datastream_metadata.change_sequence_number AS csn
FROM `YOUR_PROJECT_ID.lakehouse_bronze.public_orders`
ORDER BY order_id, datastream_metadata.source_timestamp DESC, csn DESC;

Step 5: Turning Append-Only Bronze into an Upserted Silver Iceberg Table

Because our Bronze Iceberg table is an immutable append-only change log, downstream BI tools and AI agents need a clean, deduplicated Current State (Silver) layer where updates overwrite older versions and DELETE events remove deleted records.

Here are the two production patterns you can use on Google Cloud:

Pattern A: Zero-Latency Real-Time Deduplicated View

If you want zero replication delay between Bronze and Silver without storing a second copy of the data, create a view using QUALIFY ROW_NUMBER():

CREATE OR REPLACE VIEW `YOUR_PROJECT_ID.lakehouse_silver.v_orders_current` AS
SELECT
order_id,
customer_id,
product_category,
order_status,
total_amount,
shipping_city,
updated_at,
TIMESTAMP_MICROS(datastream_metadata.source_timestamp) AS last_cdc_timestamp,
datastream_metadata.change_type AS last_change_type
FROM `YOUR_PROJECT_ID.lakehouse_bronze.public_orders`
WHERE datastream_metadata.change_type != 'UPDATE-DELETE'
QUALIFY ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY
datastream_metadata.source_timestamp DESC,
datastream_metadata.change_sequence_number DESC
) = 1
AND datastream_metadata.change_type != 'DELETE';

Pattern B: Materialized Silver Apache Iceberg Table via ACID MERGE INTO

When external engines (like Apache Spark, Trino, or PyIceberg) need to read the curated Silver table directly from GCS without executing a BigQuery SQL view, or when your Bronze log grows to hundreds of millions of events, materialize a Silver Managed Iceberg Table and run an incremental MERGE INTO:

-- 1. Create the Silver Managed Apache Iceberg Table on GCS
CREATE TABLE IF NOT EXISTS `YOUR_PROJECT_ID.lakehouse_silver.orders` (
order_id STRING,
customer_id STRING,
product_category STRING,
order_status STRING,
total_amount NUMERIC,
shipping_city STRING,
updated_at TIMESTAMP,
last_cdc_timestamp TIMESTAMP
)
CLUSTER BY order_id
WITH CONNECTION `YOUR_PROJECT_ID.us-central1.iceberg-gcs-connection`
OPTIONS (
file_format = 'PARQUET',
table_format = 'ICEBERG',
storage_uri = 'gs://YOUR_PROJECT_ID-iceberg-lakehouse/silver/orders'
);
 
-- 2. Execute Atomic ACID MERGE INTO (Upserts + Physical Deletes)
MERGE `YOUR_PROJECT_ID.lakehouse_silver.orders` AS T
USING (
SELECT
order_id,
customer_id,
product_category,
order_status,
total_amount,
shipping_city,
updated_at,
TIMESTAMP_MICROS(datastream_metadata.source_timestamp) AS source_ts,
datastream_metadata.change_type AS change_type
FROM `YOUR_PROJECT_ID.lakehouse_bronze.public_orders`
WHERE datastream_metadata.change_type != 'UPDATE-DELETE'
QUALIFY ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY
datastream_metadata.source_timestamp DESC,
datastream_metadata.change_sequence_number DESC
) = 1
) AS S
ON T.order_id = S.order_id
WHEN MATCHED AND S.change_type = 'DELETE' THEN
DELETE
WHEN MATCHED THEN
UPDATE SET
customer_id = S.customer_id,
product_category = S.product_category,
order_status = S.order_status,
total_amount = S.total_amount,
shipping_city = S.shipping_city,
updated_at = S.updated_at,
last_cdc_timestamp = S.source_ts
WHEN NOT MATCHED AND S.change_type != 'DELETE' THEN
INSERT (
order_id, customer_id, product_category, order_status,
total_amount, shipping_city, updated_at, last_cdc_timestamp
)
VALUES (
S.order_id, S.customer_id, S.product_category, S.order_status,
S.total_amount, S.shipping_city, S.updated_at, S.source_ts
);

-- 3. Export Iceberg Metadata Snapshot for Multi-Engine Readers (Spark / PyIceberg)
EXPORT TABLE METADATA FROM `YOUR_PROJECT_ID.lakehouse_silver.orders`;

Querying lakehouse_silver.orders now returns the exact 6 active orders matching Cloud SQL (ORD-1001 as SHIPPED, ORD-1002 as DELIVERED at 195.00, ORD-1003 deleted, and ORD-1006/ORD-1007 inserted)!

Verifying Physical Open Parquet & Iceberg Metadata Files in Cloud Storage

Finally, let’s verify that both our Bronze and Silver tables are stored as genuine open-format Apache Iceberg tables inside our customer-owned GCS bucket (gs://${GCS_BUCKET}/):

gcloud storage ls - recursive "gs://${GCS_BUCKET}/"

When to Use Pattern A (View) vs. Pattern B (Materialized Iceberg MERGE)

Conclusion

By combining Cloud SQL for PostgreSQL, Google Cloud Datastream, and BigQuery Managed Tables for Apache Iceberg, you get the best of both worlds: a zero-maintenance, serverless CDC pipeline and an open Parquet/Iceberg lakehouse stored in your own Cloud Storage bucket.

You can find all the complete, reproducible bash and SQL scripts for this tutorial in my GitHub repository:

- GitHub Repository: https://github.com/saipulrx/cdc_lakehouse_gcp

Built for the Google Cloud Lakehouse/Iceberg Content Sprint 2026

Real-Time CDC to the Open Lakehouse: Streaming Cloud SQL (PostgreSQL) Changes into Apache Iceberg… was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

The short answer is: Absolutely not!

Allowing an AI to interact directly with your production data is a disaster waiting to happen. Here’s why, and how you can get the benefit of an AI using a database while keeping your data safe.

The Dual Nightmares: Hallucinations and Compliance

When you give an AI agent direct access to your live database, you open yourself up to two risks:

1. The Rogue Deletion Command

AI models are highly capable, but they are also prone to hallucinations. An agent trying to resolve a simple schema issue might misinterpret a prompt and generate a destructive query. One unintended bulk deletion or DROP TABLE command later, and your entire production database is history. Reddit is full of horror stories from vibe coders who lost production data.

Giving an AI agent production write-access is like writing critical code and just leaving a comment:

// Please do not crash!

2. The Privacy and Regulatory Trap

Even if your agent only has read-only access, feeding live personal data (PII) to an AI model can violate laws like GDPR, HIPAA, or CCPA. Passing real names, email addresses, and financial transactions to an external API is an easy way to trigger compliance audits and eye-watering fines.

Passing raw customer PII to an external AI API is the fastest way to turn your database into a GDPR horror story.

The Clean Pattern: A Safe Sandbox

Fortunately, the fix is simple. You don’t have to lock your AI agent in a closet. Instead, you can give it access to a development database.

To make the data realistic you can copy production data into the development database, as long as you have a script that anonymizes any personal data.

And Bob’s your uncle! Lower risk, greater security, and no more sleepless nights.

Over to You!

Take a look at your cloud console and revoke production access for your agents today.

Do you let your coding agent read or write to a database? What has worked well for you?

Should You Give Your AI Coding Agent Access to Your Production Database? was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

Running change-point detection, key-driver attribution, and counterfactual impact analysis inside BigQuery on CFPB complaints, and what it actually took to stop a conversational agent from guessing.

Every Monday in a consumer bank’s Dispute & Fraud Risk Operations center, someone in leadership asks a version of the same three questions:

1. “Are Regulation E disputes on our deposit, prepaid, wallet, and money-transfer products growing structurally, or are we just looking at normal weekly and seasonal cycles? And what is driving our handling cost?”
2. “When Reg E disputes spiked, when did that surge actually start and end, and which combination of product, sub-product, issue, channel, and state drove it?”
3. “We tightened our fraud controls on a given date. Did that change actually reduce disputes and cost, or was volume already moving on its own?”

In most banks, answering those questions sets off a slow relay race between BI analysts, data scientists, and governance teams.

First, an analyst builds a GROUP BY dashboard. Standard SQL aggregations are great at counting rows, but they cannot separate a multi-week regime shift from a routine Monday rebound, and they cannot separate the effect of a policy change from a trend that was already under way. So the dashboard reports an eyeballed spike window and a raw before/after average. Next, a data scientist exports millions of regulated complaint rows to a Python notebook to run ruptures, custom slice-attribution code, and CausalImpact. Meanwhile, an executive opens a text-to-SQL assistant directly, and the assistant has to guess what legacy column names like reg_e_elig_flg, cust_esc_tier2_flg, and mon_rel_ind mean.

That workflow breaks down in three places:

1. Regulated data leaves the warehouse just to run basic statistics. Exporting dispute tables to run change-point detection or counterfactual time-series models adds egress, notebook drift, and extra security reviews.
2. Manual slicing turns into GROUP BY CUBE spaghetti. Finding which combination of Product × Sub-product × Issue × Channel × State drove a surge means sifting through dozens of overlapping parent and child slices.
3. Naive before/after averages get causal questions backwards. If dispute volume was already climbing before an intervention date, comparing AVG(daily_disputes) before and after that date confuses the pre-existing trend with the effect of your rollout. In the 2020 CFPB data below, a plain pre/post average says disputes went up 22.5%, while a proper counterfactual shows no statistically significant portfolio effect at all (p = 0.59).

I wanted to see how far we could push this entire workflow inside BigQuery without exporting a single row to Python.

This post walks through what I built and tested on public CFPB complaints (2018–2022):

• Six BigQuery Augmented Analytics Table-Valued Functions (TVFs) (ML.TREND, ML.SEASONALITY, ML.CORRELATION, ML.DETECT_CHANGE_POINTS, AI.KEY_DRIVERS, and AI.CAUSAL_EFFECT) chained together in GoogleSQL, scanning about 1.9 GiB in total.
• Knowledge Catalog column descriptions, profiling scans, and a 6-term Business Glossary linked to 13 physical columns across two tables.
• BigQuery Conversational Analytics (BQCA) configured with domain instructions and three Verified Queries, tested side by side against an ungrounded agent on the exact same question, and audited through region-us.INFORMATION_SCHEMA.JOBS.

🔗 Code, SQL scripts, and Console runbook: github.com/Rajdipc/bq-agentic-analytics-finserv-tvfs

How the Pieces Fit Together

The core idea is simple: keep semantic interpretation in the catalog and agent layer, and keep statistical math inside BigQuery’s SQL engine.

Three practical rules govern how this works:

1. The agent writes SQL; the TVFs do the math. An LLM should never estimate p-values or invent attribution logic in token space. Its job is to resolve banking terms and emit SQL that calls BigQuery’s statistical TVFs where the rows already live.
2. Definitions live in Knowledge Catalog, not in someone’s scratchpad. Core-banking schemas are full of cryptic abbreviations. Enriched column descriptions and a Business Glossary (FinServ_Risk_Glossary) attach regulatory definitions directly to the physical columns.
3. Verified Queries lock in the statistical method for high-stakes questions. As we will see later, BigQuery’s conversational agent already knows the syntax of AI.KEY_DRIVERS. What it does not know on its own is your team's analytical methodology—such as finding the surge window with ML.DETECT_CHANGE_POINTS before slicing drivers, or using AI.CAUSAL_EFFECT instead of a before/after average.

Here is the end-to-end flow across the six SQL scripts in the repository:

When to Use These TVFs (and When I’d Still Use Python)

Whenever a data warehouse adds statistical functions in SQL, the fair question is: why not just pull the aggregated daily series into a Python notebook?

If you are doing a one-off study on an 1,800-row daily time series on your laptop, statsmodels, ruptures, and CausalImpact in Python are great. Where the in-warehouse TVFs genuinely earn their keep is when three requirements meet:

• You want to chain time-series change-point detection directly into million-row multidimensional slicing. ML.DETECT_CHANGE_POINTS runs on the daily series, and AI.KEY_DRIVERS immediately slices the row fact table across five dimensions (prod_line_cd × sub_prod_cd × iss_cat_cd × subm_chnl_cd × jur_state_cd) with automatic redundancy pruning. Doing both inside one SQL CTE means zero data export and one IAM boundary.
• You want a conversational agent to use real statistical methods. A BigQuery Data Agent cannot spin up a custom Python container mid-chat to run CausalImpact when a risk officer asks a question in BigQuery Studio. It can execute a single SQL query that calls AI.CAUSAL_EFFECT or chains ML.DETECT_CHANGE_POINTS into AI.KEY_DRIVERS.
• You want to retire brittle window-function SQL. Approximating step-adjusted trend smoothing or segmented correlation matrices in handwritten SQL takes dozens of lines of window functions. A single TVF call is much easier to read and review.

Where I would still reach for Python instead:

• User-level or cross-sectional causal inference. AI.CAUSAL_EFFECT is a time-series counterfactual tool built around an intervention_timestamp. If you need propensity-score matching, doubly robust uplift modeling, or instrumental variables across users with staggered treatment dates, use a dedicated Python econometrics pipeline.
• Sub-second application serving. These TVFs fit statistical models at query time and take a few seconds to run. They are designed for interactive analytics and decision support, not 50 ms checkout APIs.

The Data (and Three Traps I Hit Building the Mart)

To keep everything reproducible on real public data, sql/01_setup_finserv_risk_mart.sql transforms five years of the CFPB Consumer Complaint Database (bigquery-public-data.cfpb_complaints.complaint_database, 2018-01-01 to 2022-12-31, US multi-region) into two tables in finserv_risk_ops:

1. finserv_risk_ops.fct_consumer_disputes : partitioned by month on intake_dt and clustered by prod_line_cd, subm_chnl_cd, jur_state_cd. Across the five-year window, it contains 182,279 Reg E-scope disputes, 56,746 Tier-2 escalation-proxy cases, 67,220 monetary-relief cases, 24,932 SLA breaches, and $144.26M of modeled handling cost.
2. finserv_risk_ops.agg_daily_risk_kpis : one row per intake_dt × prod_line_cd across 9 product lines, rolling up daily_dispute_vol, reg_e_dispute_vol, avg_intake_fwd_lag_days, tier2_escalation_rate_pct, monetary_relief_rate_pct, sla_breach_rate_pct, total_ops_cost_usd, and avg_ops_cost_per_dispute_usd.

To mimic the tables you actually run into at a bank, sql/01 creates both tables with abbreviated column codes and blank schema descriptions:

Building this mart on real CFPB data surfaced three data traps right away.

Trap 1: Regulation E is not “any complaint mentioning fraud”

Regulation E (12 CFR 1005) covers electronic fund transfers to or from consumer asset accounts. In my first draft of sql/01, I took a lazy shortcut: I flagged any complaint mentioning fraud or unauthorized transactions, plus any card or money-transfer product. That quietly swept in credit cards—which are governed by Regulation Z (Truth in Lending / Fair Credit Billing Act), not Regulation E—along with gift cards, virtual currency, and mortgage or credit-bureau fraud complaints.

Tightening reg_e_elig_flg in sql/01_setup_finserv_risk_mart.sql to true Reg E deposit, prepaid, wallet, and money-transfer accounts cut the qualifying dispute count and changed every change-point and causal number downstream. That experience is the best argument I know for a Business Glossary: a scoping rule that specific belongs in governed metadata, not buried in a CASE WHEN block that every analyst rewrites differently.

Trap 2: consumer_disputed looks ideal until you profile it

The public CFPB table has a boolean column named consumer_disputed. From the schema alone, it looks like the exact flag you would use for Tier-2 customer escalations. When I profiled our 2018–2022 cohort, consumer_disputed was NULL across all rows—the CFPB retired that field in April 2017.

In sql/01, I dropped consumer_disputed and defined cust_esc_tier2_flg as an explicit operational proxy (SLA breach, untimely response, or fraud/scam/unauthorized issue), and called out that proxy definition in both the column description and the Knowledge Catalog glossary.

Trap 3: Spring 2020 breaks naive before/after comparisons

If you pick an intervention date in mid-2020 and compare average daily disputes before vs. after that date, the spring 2020 pandemic wave poisons the baseline. Because January through April 2020 had lower volume than May and June 2020, a raw pre/post average across 2020-01-01 to 2020-10-01 claims Reg E disputes jumped +22.5% after July 1, 2020—even though volume had already plateaued before July 1. We will see how AI.CAUSAL_EFFECT handles that in Act 3.

Running the Six TVFs Across Three Acts

Rather than running six disconnected toy examples, sql/02, sql/03, and sql/04 use the six TVFs to answer the three Monday-morning questions in sequence.

Act 1: Trend, Seasonality, and Cost Correlations (ML.TREND, ML.SEASONALITY, ML.CORRELATION)

In sql/02_act1_baseline_trend_seasonality_correlation.sql, Query 1A extracts the long-term trend of daily Reg E disputes using ML.TREND with adjust_step_changes => TRUE so sudden level shifts do not smear the 14-day smoothing window:

WITH daily_reg_e AS (
  SELECT
    intake_dt,
    SUM(reg_e_dispute_vol) AS total_reg_e_disputes
  FROM finserv_risk_ops.agg_daily_risk_kpis
  GROUP BY intake_dt
)
SELECT
  intake_dt,
  total_reg_e_disputes,
  ROUND(trend, 2) AS smoothed_long_term_trend,
  time_series_type,
  status
FROM ML.TREND(
  TABLE daily_reg_e,
  data_col => 'total_reg_e_disputes',
  timestamp_col => 'intake_dt',
  smoothing_window_size => 14,
  adjust_step_changes => TRUE
)
ORDER BY intake_dt;

Query 1B runs ML.SEASONALITY (seasonalities => ['Yearly', 'Monthly', 'Weekly']) on the same daily series. That weekly component turns out to be critical later: complaint intake drops sharply every Saturday and Sunday and rebounds every Monday.

Query 1C runs ML.CORRELATION across agg_daily_risk_kpis to measure how strongly each operational metric correlates with daily handling cost (total_ops_cost_usd), both across the whole portfolio and within each product line:

SELECT
  CASE
    WHEN prod_line_cd IS NULL
      AND NOT EXISTS (SELECT 1 FROM UNNEST(segment) s WHERE s.dimension_col = 'prod_line_cd')
      THEN 'ALL PRODUCT LINES (Global Aggregate)'
    WHEN prod_line_cd IS NULL
      THEN 'UNSPECIFIED PRODUCT'
    ELSE prod_line_cd
  END AS product_line_scope,
  target_col,
  corr_col AS correlated_operational_metric,
  ROUND(correlation, 4) AS pearson_correlation,
  segment_size AS days_observed,
  ROUND(segment_proportion * 100, 2) AS pct_of_total_observations
FROM ML.CORRELATION(
  TABLE finserv_risk_ops.agg_daily_risk_kpis,
  target_col => 'total_ops_cost_usd',
  target_correlation_cols => [
    'reg_e_dispute_vol',
    'avg_intake_fwd_lag_days',
    'tier2_escalation_rate_pct',
    'monetary_relief_rate_pct',
    'sla_breach_rate_pct'
  ],
  dimension_cols => ['prod_line_cd'],
  method => 'PEARSON'
)
WHERE segment_size >= 100
  AND NOT IS_NAN(correlation)
ORDER BY
  ARRAY_LENGTH(segment) ASC,
  ABS(correlation) DESC;

Here are the key rows from BigQuery:

Notice the contrast between the bottom row and the top two rows. Across all nine product lines pooled together, the correlation between reg_e_dispute_vol and cost is basically zero (r = 0.0445) because high-volume non-Reg E categories like credit reporting dominate total row counts. Once you pass dimension_cols => ['prod_line_cd'], ML.CORRELATION computes both the global rollup and every product slice in a single pass, showing r = 0.9405 in money transfers and r = 0.9141 in checking and savings. (Because total_ops_cost_usd is a modeled cost formula built from our operational flags, high correlation within Reg E products partly reflects the cost weights by construction; the useful diagnostic is where products diverge, such as Mortgage, where routing lag tracks cost at r = 0.5573.)

Act 2: Finding the Surge and What Drove It (ML.DETECT_CHANGE_POINTS → AI.KEY_DRIVERS)

In sql/03_act2_change_points_and_key_drivers_chain.sql, Query 2A runs ML.DETECT_CHANGE_POINTS on the five-year daily Reg E series (overall mean: 99.9 disputes/day). It identifies four distinct regimes:

regime_start_dateregime_end_dateAvg / dayMinMaxDays2020-05-252020-05-28111.52717442021-05-042021-05-19161.166228162022-05-022022-05-16123.42203152022-06-252022-07-0597.25119711

Instead of copy-pasting '2021-05-04' and '2021-05-19' into a second query, Query 2B pipes the top regime from ML.DETECT_CHANGE_POINTS directly into AI.KEY_DRIVERS inside a single CTE chain. It labels rows inside the 16-day surge (2021-05-04 to 2021-05-19) as is_surge = TRUE and compares them against the immediately preceding 16 days (2021-04-18 to 2021-05-03, is_surge = FALSE):

WITH daily_reg_e AS (
  SELECT
    TIMESTAMP(intake_dt) AS intake_ts,
    SUM(reg_e_dispute_vol) AS total_reg_e_disputes
  FROM finserv_risk_ops.agg_daily_risk_kpis
  GROUP BY intake_ts
),
detected_regimes AS (
  SELECT
    DATE(begin_timestamp) AS regime_start,
    DATE(end_timestamp) AS regime_end,
    metrics.avg AS regime_avg
  FROM ML.DETECT_CHANGE_POINTS(
    TABLE daily_reg_e,
    data_col => 'total_reg_e_disputes',
    timestamp_col => 'intake_ts'
  )
  WHERE begin_timestamp IS NOT NULL
),
primary_surge_regime AS (
  SELECT
    regime_start,
    regime_end,
    DATE_DIFF(regime_end, regime_start, DAY) + 1 AS regime_days
  FROM detected_regimes
  ORDER BY regime_avg DESC
  LIMIT 1
),
dispute_comparison_window AS (
  SELECT
    f.prod_line_cd,
    f.sub_prod_cd,
    f.iss_cat_cd,
    f.subm_chnl_cd,
    f.jur_state_cd,
    1 AS dispute_count,
    f.intake_dt >= r.regime_start AS is_surge
  FROM finserv_risk_ops.fct_consumer_disputes AS f
  CROSS JOIN primary_surge_regime AS r
  WHERE f.reg_e_elig_flg = 1
    AND f.intake_dt BETWEEN DATE_SUB(r.regime_start, INTERVAL r.regime_days DAY)
                        AND r.regime_end
)
SELECT
  drivers AS contributing_segment_drivers,
  metric_interest AS surge_window_disputes,
  metric_reference AS baseline_window_disputes,
  difference AS net_dispute_change,
  ROUND(relative_difference * 100, 1) AS pct_growth,
  ROUND(unexpected_difference, 1) AS unexpected_excess_disputes,
  ROUND(contribution, 1) AS absolute_contribution,
  ROUND(apriori_support * 100, 2) AS population_support_pct
FROM AI.KEY_DRIVERS(
  (SELECT * FROM dispute_comparison_window),
  metric_col => 'dispute_count',
  interest_label_col => 'is_surge',
  dimension_cols => [
    'prod_line_cd',
    'sub_prod_cd',
    'iss_cat_cd',
    'subm_chnl_cd',
    'jur_state_cd'
  ],
  top_k => 15,
  enable_pruning => TRUE
)
ORDER BY unexpected_difference DESC;

Here are the top slices returned by AI.KEY_DRIVERS for the 2021-05-04 → 2021-05-19 surge:

Across the entire Reg E portfolio, disputes rose by +423 (+19.6%) over the prior 16 days. Checking or savings account accounts for +424 of that net increase (+33.6%), concentrated specifically in Checking account × Managing an account (+365 disputes, +61.0%) and Referral submissions (+49.5%). Meanwhile, money-transfer and wallet complaints barely moved (+4.4%). Passing enable_pruning => TRUE drops redundant parent/child slices so the top 15 results stay readable.

💡 A bug I hit while writing Query 2B: In my first attempt, I hardcoded a ±60-day comparison window around regime_start. Because the May 2021 surge only lasted 16 days, a 60-day post-window diluted those 16 high-volume days with 44 normal days and made total Reg E disputes look like they fell by 5.2%. Computing DATE_DIFF(regime_end, regime_start, DAY) + 1 AS regime_days and comparing the surge against an equal-length baseline window fixed the dilution immediately.

Act 3: Did the July 2020 Control Change Work? (AI.CAUSAL_EFFECT vs. Naive AVG())

Now suppose risk leadership rolled out tightened step-up authentication controls on July 1, 2020 (a hypothetical intervention date overlaid on the public CFPB timeline) and wants to know whether it reduced Reg E disputes and handling cost over the next three months.

sql/04_act3_intervention_causal_effect.sql runs both the naive before/after average (Query 3D) and AI.CAUSAL_EFFECT (Queries 3A, 3B, 3C) on the same 9-month window (2020-01-01 to 2020-06-30 pre-intervention baseline, 2020-07-01 to 2020-10-01 evaluation period):

WITH daily_reg_e_window AS (
  SELECT
    TIMESTAMP(intake_dt) AS intake_ts,
    SUM(reg_e_dispute_vol) AS total_reg_e_disputes
  FROM finserv_risk_ops.agg_daily_risk_kpis
  WHERE intake_dt BETWEEN '2020-01-01' AND '2020-10-01'
  GROUP BY intake_ts
)
SELECT
  ROUND(absolute_effect, 0) AS cumulative_incremental_disputes,
  ROUND(relative_effect * 100, 2) AS relative_effect_pct,
  ROUND(prob_causal_effect * 100, 2) AS causal_confidence_pct,
  ROUND(p_value, 6) AS p_value,
  status
FROM AI.CAUSAL_EFFECT(
  (SELECT * FROM daily_reg_e_window),
  data_col => 'total_reg_e_disputes',
  timestamp_col => 'intake_ts',
  intervention_timestamp => '2020-07-01 00:00:00',
  confidence_level => 0.95,
  output_time_series => FALSE
);

Query 3B flips output_time_series => TRUE to plot the daily actuals against the predicted counterfactual and 95% confidence bounds, and Query 3C adds id_cols => ['prod_line_cd'] to evaluate daily operational cost per product line in parallel.

Here is what the four queries return:

The naive AVG() query says daily Reg E disputes rose +22.47% after July 1 (89.9/day in Jan–Jun vs. 110.1/day in Jul–Sep), which would make a leadership team think the July 1 rollout backfired.

AI.CAUSAL_EFFECT tells the real story. Because dispute volume had already stepped up in May 2020, the post-July trajectory sits well inside the 95% counterfactual band (−13.31%, p = 0.5948). There is no statistically credible portfolio-wide effect. And when Query 3C breaks cost out by product line via id_cols => ['prod_line_cd'], only Money transfer, virtual currency, or money service shows a notable post-July drop (−$188,165, −33.42%), which at p = 0.0907 (90.93% posterior probability) is worth investigating further, not declaring a win at alpha = 0.05$.

What Actually Happened When I Tested the Agent Before and After Grounding

Running the TVFs in SQL is half the problem. The other half is what happens when a risk officer asks a BigQuery Conversational Analytics (BQCA) Data Agent a question in plain English.

When I first set up the ungrounded agent (Demo-FinservRiskOps-Before-Grounding) pointed at the raw fct_consumer_disputes table with blank descriptions and zero Verified Queries, I asked it:

“When did daily unauthorized electronic fund transfer claims under 12 CFR 1005 experience their biggest surge, and which product, sub-product, issue, submission channel, and state drove that surge compared to the baseline right before it?”

I expected the ungrounded agent to fail on the cryptic column name reg_e_elig_flg and write a plain GROUP BY. It didn’t. On its very first attempt, the ungrounded agent filtered WHERE reg_e_elig_flg = 1 and called AI.KEY_DRIVERS.

Curious how it knew AI.KEY_DRIVERS with zero instructions from me, I ran sql/06_audit_bqca_agent_jobs.sql against region-us.INFORMATION_SCHEMA.JOBS. Every query submitted by Conversational Analytics carried a label I hadn't noticed before: ca-skill: bigquery-ai-ml-ca-rules. Out of the box, BQCA attaches a built-in system skill that teaches the agent the SQL syntax for ML.TREND, ML.SEASONALITY, AI.KEY_DRIVERS, and AI.CAUSAL_EFFECT. And because the physical column identifier contains the substring reg_e, the LLM connected "12 CFR 1005" to Regulation E and guessed reg_e_elig_flg = 1.

So the syntax worked. The analytical answer was still completely wrong.

When I inspected the four SQL queries that Demo-FinservRiskOps-Before-Grounding executed, here is what it actually did:

1. Query 1 — It skipped ML.DETECT_CHANGE_POINTS and used a 1-day LAG() difference: Remember from Act 1 that dispute intake drops every Sunday and rebounds every Monday. A 1-day LAG() difference does not find structural surges; it finds Mondays. Every single top date in the ungrounded agent's output (2022-02-28, 2022-04-11, 2022-07-11) was a Monday rebounding off a quiet Sunday (212 claims on Monday 2022-02-28 vs. 42 on Sunday 2022-02-27 = +170).
2. Query 2 — It pulled two weeks of daily counts around February 28, 2022 to eyeball its own LAG() result.
3. Query 3 — It fed a 2-day Sunday-vs-Monday window into AI.KEY_DRIVERS with pruning turned off: It filtered WHERE intake_dt IN ('2022-02-27', '2022-02-28') with (intake_dt = '2022-02-28') AS IS_SURGE and ran AI.KEY_DRIVERS without enable_pruning => TRUE. Because pruning was off, Row 1 was the trivial all slice (212 vs 42), and redundant parent/child combinations crowded out every single jur_state_cd slice from LIMIT 10.
4. Query 4 — It fell back to a manual GROUP BY jur_state_cd query between Sunday 2022-02-27 and Monday 2022-02-28 (CA: +21, FL: +20) because no state had appeared in Query 3.

What It Took to Ground the Agent

To fix that, I applied three layers of context (sql/05_enrich_metadata_for_knowledge_catalog.sql, scripts/link_knowledge_catalog_glossary.py, and docs/knowledge_catalog_and_bqca_assets.md):

1. Column descriptions on all 29 columns (sql/05): Populated descriptions on all 19 columns of fct_consumer_disputes and all 10 columns of agg_daily_risk_kpis, including which columns map to timestamp_col, data_col, and dimension_cols in the six TVFs.
2. Knowledge Catalog scans + 6 Business Glossary terms linked to 13 physical columns (scripts/link_knowledge_catalog_glossary.py): Generated Data Profile and Data Insights scans on both tables, created FinServ_Risk_Glossary with six terms (Reg E Qualifying Dispute, Tier-2 Customer Escalation, Monetary Relief Ratio, Intake-to-Network Routing Lag, Regulatory SLA Breach, and Dispute Operational Cost), and attached those terms to 13 physical columns across fct_consumer_disputes and agg_daily_risk_kpis using Dataplex EntryLink resources (entryLinkTypes/definition).
3. Agent instructions + 3 Verified Queries (docs/knowledge_catalog_and_bqca_assets.md): Added explicit table-routing and TVF rules (agg_daily_risk_kpis for daily time-series TVFs, fct_consumer_disputes for 5-dimension AI.KEY_DRIVERS), plus three Verified Queries covering the chained ML.DETECT_CHANGE_POINTS → AI.KEY_DRIVERS workflow (VQ-1), segmented ML.CORRELATION (VQ-2), and AI.CAUSAL_EFFECT (VQ-3).

When I created the grounded agent (Demo-FinservRiskOps-After-Grounding) and asked the exact same prompt word-for-word (images/07_bqca_after_grounding_tvf.png), it matched Verified Query 1 (✓) and ran two clean queries:

• Query 1: Chained ML.DETECT_CHANGE_POINTS on agg_daily_risk_kpis into AI.KEY_DRIVERS(..., enable_pruning => TRUE) on fct_consumer_disputes, isolating the true 16-day surge (May 4 – May 19, 2021) against the prior 16 days (April 18 – May 3, 2021), attributing the +423 net jump to Checking or savings account (1,685 vs. 1,261 = +424, +33.6%), and automatically rendering a horizontal bar chart of the top drivers.
• Query 2: Extracted the ML.DETECT_CHANGE_POINTS CTE as a standalone query (ORDER BY regime_avg DESC) so it could display the full table of four detected regimes (2021-05-04 → 2021-05-19 at 161.06 disputes/day) right above the driver breakdown.

Reproducing it in the Cloud Console

You can run the entire sequence from the Google Cloud Console (BigQuery Studio, Knowledge Catalog, and BigQuery Conversational Analytics). All eight screenshots from our test run in rajdipch-project-2 are in images/.

Step 1: Build the Mart & Inspect the Un-Enriched Schema

In BigQuery → Studio, set your query location to US (multiple regions in United States) and run sql/01_setup_finserv_risk_mart.sql. When it finishes, open finserv_risk_ops.fct_consumer_disputes → Schema to confirm the cryptic column names and empty Description fields (-).

Step 2: Run Act 1 — Trend, Seasonality & Cost Correlations

Run sql/02_act1_baseline_trend_seasonality_correlation.sql in BigQuery Studio. Inspect the three result tabs for ML.TREND (Query 1A), ML.SEASONALITY (Query 1B), and segmented ML.CORRELATION (Query 1C).

Step 3: Run Act 2 — Chained ML.DETECT_CHANGE_POINTS → AI.KEY_DRIVERS

Run sql/03_act2_change_points_and_key_drivers_chain.sql. Result Tab 1 shows the four change-point regimes led by 2021-05-04 → 2021-05-19 (161.1/day); Result Tab 2 shows the pruned AI.KEY_DRIVERS attribution table.

Step 4: Run Act 3 — Counterfactual AI.CAUSAL_EFFECT vs. Naive Average

Run sql/04_act3_intervention_causal_effect.sql. On Result Tab 2 (Query 3B), switch to Chart → Line chart with dispute_date on the X-axis and total_reg_e_disputes, predicted_counterfactual_disputes, counterfactual_lower_95, and counterfactual_upper_95 on the Y-axis.

Step 5: Test the Ungrounded Agent (“Before Grounding”)

Before running sql/05, create a Data Agent (Demo-FinservRiskOps-Before-Grounding) in BigQuery → Agents pointed only at finserv_risk_ops.fct_consumer_disputes, with empty instructions and no Verified Queries, and ask the surge-and-drivers prompt from docs/knowledge_catalog_and_bqca_assets.md.

Step 6: Enrich Metadata, Run Scans & Link the Business Glossary

1. Run sql/05_enrich_metadata_for_knowledge_catalog.sql in BigQuery Studio to populate descriptions on both tables and all 29 columns.
2. Trigger Data profile and Insights scans on both tables from their BigQuery Studio tabs.
3. Create FinServ_Risk_Glossary in Dataplex → Knowledge Catalog → Glossaries (US multi-region) and run python3 scripts/link_knowledge_catalog_glossary.py in Cloud Shell to create the 6 Business Glossary terms and attach them to all 13 physical BigQuery columns.

Step 7: Configure & Test the Grounded Agent (“After Grounding”)

Create a new Data Agent (Demo-FinservRiskOps-After-Grounding or FinServ_Payments_Risk_Agent) connected to both fct_consumer_disputes and agg_daily_risk_kpis, paste the Agent Instructions and three Verified Queries from docs/knowledge_catalog_and_bqca_assets.md, and run the exact same prompt from Step 5.

Step 8: Audit Both Agents in INFORMATION_SCHEMA.JOBS

Run sql/06_audit_bqca_agent_jobs.sql in BigQuery Studio. Filtering region-us.INFORMATION_SCHEMA.JOBS on ca-bq-job = 'true' shows every query executed by both the grounded agent and the ungrounded agent, along with ca_skill (bigquery-ai-ml-ca-rules), invoked_augmented_analytics_tvfs, mb_billed, and generated_sql.

When you are finished testing, run sql/99_teardown.sql (DROP SCHEMA IF EXISTS finserv_risk_ops CASCADE) and delete the Data Agents and glossary in the Console.

Gotchas Worth Saving

Every one of these tripped me up at least once while building and testing this project:

1. Set your query location to US multi-region. The public CFPB dataset lives in US. If your BigQuery Studio tab defaults to a regional location like us-central1, cross-dataset queries fail with Not found: Dataset … was not found in location ….
2. Do not prefix built-in TVFs with a project or dataset name. Writing my_project.AI.KEY_DRIVERS(...) or finserv_risk_ops.ML.TREND(...) fails with Table-valued function not found. Qualify only the input table inside TABLE my_dataset.my_table.
3. AI.KEY_DRIVERS rejects top_k and min_apriori_support together. Those two arguments are mutually exclusive; pass one or the other.
4. AI.KEY_DRIVERS needs both TRUE and FALSE rows in interest_label_col. If your WHERE clause only includes the surge dates, AI.KEY_DRIVERS fails because the baseline reference group is empty.
5. Match the baseline window length to the surge window length. Comparing a 16-day surge against a fixed ±60-day window dilutes the spike with 44 normal days. Compute DATE_DIFF(regime_end, regime_start, DAY) + 1 from ML.DETECT_CHANGE_POINTS so both windows have equal length.
6. AI.CAUSAL_EFFECT requires intervention_timestamp to be a compile-time literal. Subqueries and column expressions are rejected, which is why Verified Query 3 provides a literal timestamp template that the agent edits when a user asks about a different date.
7. Filter out NaN rows in ML.CORRELATION. Product lines where a metric is constant (for example, reg_e_dispute_vol is 0 on every row of Mortgage and Student loan) return NaN; add WHERE NOT IS_NAN(correlation).
8. Test ungrounded vs. grounded behavior in separate Data Agents. BQCA chat threads cache schema metadata and conversation history. Always create a fresh Data Agent (or start a new chat thread after saving instructions and Verified Queries) so earlier turns do not bleed into your test.

Closing Thoughts

What stood out to me most while building this wasn’t that BigQuery could run ML.DETECT_CHANGE_POINTS, AI.KEY_DRIVERS, or AI.CAUSAL_EFFECT in SQL—it was seeing why an ungrounded agent gets analytical questions wrong even when it already knows the function syntax.

Thanks to BQCA’s built-in bigquery-ai-ml-ca-rules skill, our ungrounded agent had no trouble calling AI.KEY_DRIVERS. It still gave a misleading answer because it used a 1-day LAG() difference instead of change-point detection, mistook a routine Monday rebound (2022-02-28 vs. Sunday 2022-02-27) for the biggest surge in five years, and forgot to turn on slice pruning. Grounding an agent isn't just about teaching it what column names mean; it's about locking in the statistical workflow so the right functions get chained in the right order.

If you want to try this on your own GCP project, everything — the six SQL scripts, the Dataplex glossary linker script, the BQCA agent config, and the Console walkthrough — is in github.com/Rajdipc/bq-agentic-analytics-finserv-tvfs.

References & Further Reading

Companion GitHub Repository & Runbooks

• GitHub Repository: Rajdipc/bq-agentic-analytics-finserv-tvfs
• Console Runbook & Architecture Guide (README.md)
• Knowledge Catalog Glossary, Agent Instructions & Verified Queries (docs/knowledge_catalog_and_bqca_assets.md)
• Automated Knowledge Catalog Glossary & Column Linker (scripts/link_knowledge_catalog_glossary.py)
• Teardown script (sql/99_teardown.sql)

BigQuery Augmented Analytics TVFs

• Streamline data analysis with BigQuery Augmented Analytics TVFs (Google Cloud Blog)
• ML.TREND Function Reference
• ML.SEASONALITY Function Reference
• ML.CORRELATION Function Reference
• ML.DETECT_CHANGE_POINTS Function Reference
• AI.KEY_DRIVERS Function Reference
• AI.CAUSAL_EFFECT Function Reference

Knowledge Catalog & BigQuery Conversational Analytics

• Knowledge Catalog / Dataplex Universal Catalog Overview
• Generate Automated Data Insights in BigQuery & Knowledge Catalog
• Create and Manage a Business Glossary in Knowledge Catalog
• Configure Automated Data Profiling Scans
• BigQuery Conversational Analytics Overview
• Create and Configure Data Agents with Verified Queries in BigQuery
• INFORMATION_SCHEMA.JOBS View Reference
• CFPB Consumer Complaint Database on BigQuery Public Datasets

Grounded Agentic Analytics in BigQuery: Augmented Analytics TVFs, Knowledge Catalog, and… was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

Optimizing your docs for translation is an important and necessary part of increasing your global reach and safeguarding your brand reputation. Even though you cannot fully eliminate translation errors from technical documentation, by following a few simple tips like the ones in this post, you can make your content significantly more translation friendly.

Let’s discuss machine translation, its pros and cons

Ask any translation or localization professional and they’ll likely tell you: mistranslations are extremely common, even with advanced translation engines like Google Translate and Microsoft Translator. Errors can range from wrong words or awkward phrasing to high-stakes blunders with the potential to be extremely expensive and damage your brand reputation.

Just ask HSBC. In the 2000s, this global bank launched a worldwide marketing campaign built around the catchphrase: “Assume Nothing.” But likely because “assume” in this sense has false friends in languages like French and because HSBC may have used machine translation without proper human review, the phrase effectively turned into “Do Nothing” in several languages. To fix this blunder, HSBC reportedly spent up to $10 million on an emergency rebranding campaign.

So mistranslations are common and can be expensive, and machine translation might be flawed. But there’s a flip side to this. Not only is machine translation improving as the technology advances, but it also gives us the opportunity to enhance our technical documentation at scale.

Because machine translation is fast and cheap, as documentation teams we no longer have to split hairs about how expensive fixing typos or inaccuracies might be given the downstream localization costs. In other words, with machine translation, we have a real opportunity to iteratively and continually improve our English docs to be more compatible with an ever-growing number of machine-assisted technologies and AI tools and agents, and, of course, to be more translation friendly.

How to make tech docs more translation friendly

Given how inaccurate machine translations can be, improving your translation outcomes can seem like a big ask. But a good start to improving translations is by preventing ambiguity in the English source. There are two major types of ambiguity that frequently creep up into our writing:

• Lexical ambiguity, where a word has multiple meanings or interpretations—for example: “Verify the driver is running.” Here, both driver and running are ambiguous. Driver could refer either to a hardware or software driver or to a person who operates a motor vehicle. Running could refer to software or hardware processes or to a person running. This could cause mistranslations in Spanish, for instance, where many of these meanings map to very different words.
• Structural ambiguity, where the grammar or word order is ambiguous—for example: “Running containers without resource limits.” Here, running containers could refer to “containers that are running” or “the act of running containers.” In addition, it’s unclear if “without resource limits” describes the act of running containers or the containers themselves. Phrases like this can easily cause confusion and mistranslations in many languages including German and Japanese.

Seven tips to write more translation friendly

Here are seven simple tips to help prevent ambiguity and mistranslations. This isn’t an exhaustive list but should give you an idea of what to look for.

Beyond ambiguity, keeping your content clear and concise can also improve machine translations. Check out my post Simple formulas for concise writing.

How to prevent lexical ambiguity

These tips help you to avoid using ambiguous words.

1. Use words in their primary sense. A golden rule of simplified technical communications is “one word, one part of speech, one meaning.” Communication is often much more context dependent than you may realize. Machine translation errors help to reveal this underlying ambiguity. To prevent mistranslations, aim to limit your use of words to their primary industry-relevant sense and use those words consistently. Avoid unnecessary variation or synonyms. Consider these examples:

• detail → detail (an item or fact) or describe (to explain)
• secure → secure (to protect) or obtain (to acquire, as in secure funding)
• leave → leave (to depart) or keep (to allow to remain, as in leave blank)
• since → since (from the time that) or because (for the reason that)
• type → type (a kind or category) or enter (to input text using a keyboard)

These are all examples of polysemy, which is when one word has multiple meanings or uses. Yes, they’re not always ambiguous. But by limiting how you use these words, you can help prevent ambiguity.

In addition, it’s also helpful to provide clear context clues. Consider this scenario. The Chinese translation of your cloud infrastructure docs seemingly out of nowhere tells customers to “publish the picture to the registry,” where it should’ve said “deploy the container image to the container registry.” This could happen because Chinese uses different words for visual images and container images. Without keywords like container, words like “image” could be mistranslated.

2. Avoid homographs. Homographs are unrelated words that are spelled the same. Because we’re typically working with written translation, homographs can often be at the root of many mistranslations. Let’s go over a few likely culprits:

• resume (ˈrɛzəmeɪ, a work summary) or resume (rɪˈzuːm, to restart)
• record (ˈrɛkərd, a stored item or entry) or record (rɪˈkɔrd, to capture)
• default (ˈdiːfɔlt, a preselected option) or default (dɪˈfɔlt, to fail to pay)
• close (kloʊz, to shut or seal) or close (kloʊs, near or nearby)

Keep in mind that these words are likely completely unrelated words in other languages, so mistranslations may feel like nonsense. Of these, perhaps the most commonly mistranslated is close, which can cause a lot of frustration. Consider this complaint on Reddit.

To address this issue, try to avoid homographs where possible or at least clarify the context. For example, consider changing Resume to a less ambiguous Play/Pause. Or expand click Resume to click the Resume button. The second might not prevent mistranslation but it’ll at least provide users with more context, say if the translation is incorrect elsewhere.

3. Be careful how you use abbreviations. Not only can abbreviations, acronyms, or initialisms be unfamiliar to some users but they can be ambiguous or have different meanings in other languages with some potentially inappropriate meanings. For example, “TOC” in Spanish is actually the Spanish version of “OCD,” and while “AI” is the abbreviation we use in English, in French it’s “IA.” And abbreviating “cryptography user” to “CU” may seem perfectly fine in English, but it would likely cause a few dirty looks in Brazil. To prevent such problems, consider potential localization issues before you create a new term or abbreviation, determine if abbreviation is even necessary, and follow recommended best practices for spelling out and using abbreviations.

How to prevent structural ambiguity

These tips help you to avoid using ambiguous grammar.

4. Add structure with “of,” “that,” “the.” Think of this tip as a golden rule of translation-friendly grammar: When in doubt, use helper words, also called function words, which help to prevent ambiguity by clarifying the structure or focus of your sentences. Without them, sentences can often be misunderstood. Put another way, helper words can make your writing more readable and easier to parse, which matters especially when your reader is a machine. Some helper words to remember:

• Articles like a, an, and the.
• Prepositions like to, in, for, on, and of.
• Conjunctions and adverbs like and, that, and then.

Helper words allow machines to parse your writing correctly, clarifying the intended meaning. Some examples of helper words in action:

• empty trash → Empty the trash
• Verify target host connection → Verify that the target host is connected

5. Watch out for invisible plurals. In English, modifiers that occur in front of a noun are typically grammatically singular–that is, they usually can’t take a plural s–even if they’re conceptually plural, creating the grammatical mismatch called invisible plural. Invisible plurals are ambiguous and can cause mistranslations in languages that have plural modifiers like Spanish, Polish, and Finnish.

To help prevent mistranslations, reword the sentence to make clear whether the invisible plural is singular or plural:

• Verify the user account credentials. → Verify the credentials of the user accounts.
• After file recovery,.… → After you recover the files, …

6. Stop “verbing” around! In English, verbing can make your sentence harder to parse or ambiguous (remember running containers?) because verbing can act as different parts of speech. As a result, natural language processing systems like translation engines can struggle to figure out the precise meaning. Let’s go over how verbing is used in English:

• As a verb (present participle) in “I’m going to work.”
• As a noun (gerund) in “Smoking’s bad for you.”
• As an adjective (present participle) in “That’s interesting.”

Besides this, verbing, as a gerund, is a type of zombie noun, which can make your writing indirect by displacing concrete subjects. So, to prevent mistranslations, avoid verbing and add back concrete subjects or fold in helper words. For example, change using to by using, that use, or you use. Seem complicated? Consider these examples:

• Configuring the VPC firewall rules is required before deploying the VM instance. → You must configure the VPC firewall rules before you deploy the VM instance.
• the folder containing the configuration details → the folder that contains the configuration details

7. Be careful what you omit. Not all languages allow for the same patterns of omission. What sounds natural in English might sound strange in another language. If literally translated, the first example below would be awkward in Chinese or Spanish, which typically can’t reduce a predicate to just an auxiliary verb alone. In addition, omitting words can also create ambiguous sentences, like in the second example where it’s unclear whether we’re referring to “database” alone or “database encryption.” Let’s go over the examples:

• A Standard tier Compute Engine instance supports automated backups, but a Basic tier instance doesn’t. → A Standard tier Compute Engine instance supports automated backups, but a Basic tier instance doesn’t support automated backups.
• Enable database and storage encryption. → Enable database encryption and storage encryption.

The tip here is, when in doubt, use grammatically complete sentences and repeat words to prevent ambiguity and mistranslations.

Want to learn more about how to write translation-friendly documentation? Check out the Write for a global audience page of the Google developer documentation style guide.

References

• Document 360. “Writing for translation.” Published July 2026.
• Patrioli, Daniele. “Translation Mistakes That Cost Millions: Real Cases and What They Can Teach Your Business.” Translated.
• Translation Centre for the Bodies of the European union. “Writing for machine translation.” Cdt.europa.eu. Last updated December 2021. Accessed October 2026.
• Urban, Adrienne. “Don’t Get Lost in Translation: A Discussion of Best Practices for Creating Translation-Friendly Text and Related Curriculum for Technical Communication.” Cornerstone.lib.mnsu.edu. Published in 2018.

About the author

Isaac is a technical editor at Google who works on content for the Cloud Security services and helps maintain the Google developer documentation style guide. Passionate about creating superior content experiences, Isaac enjoys sharing his knowledge about language and grammar to help technical writers and other content creators create a frictionless documentation experience for Google customers.

Writing for machine translation: Seven tips to make your docs translation friendly was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

I wanted an AI assistant without another distracting screen.

So, I built a small, quiet E ink device. E Ink. No feed to refresh. No apps to browse. Just enough information to tell me what’s happening, and then leave me alone but with the power of an agent behind it.

My earlier screenless experiment used Gemini Live and a Hermes agent to handle everyday tasks through voice using raspberry Pi. The one thing that made it limited was it was wired in and lets face it — the form factor wasn’t great :).

This is the next version: a tiny wireless device I can carry in my pocket.

The question is still the same: can I keep the useful parts of technology while spending less time looking at it?

What can it do?

Press a button and talk to it. Ask a question, post a thought to X, create slides and email them to me, or find a coloring page and print it on my home printer.

Gemini Live handles the conversation. Hermes handles the tasks that need my tools and workflows. And I don’t have to stand there waiting.

Watch the live demo on YouTube →

Gemini Live: just talk to it

The conversational side uses Gemini Live. I can speak naturally instead of typing a prompt into an app.

In this setup, Google Search is enabled too. In the demo, I ask for Google’s latest stock price while another task is being processed. The useful part is being able to ask for current information without opening a browser.

It talks back-and I can keep talking

Listening and speaking have their own screen states. The little face changes expression, so I can tell what the device is doing at a glance.

I can also continue having a live conversation while Hermes works in the background. Asking it to make a presentation doesn’t mean I have to wait before asking my next question.

Hermes: where things actually get done

Gemini Live hands tool-based requests to my Hermes agent, which is connected to my tools, connectors, skills, and home network.

For example: “Create five slides on AI agents and send them to my email.”

Hermes (with gemini as the brain) researches the topic, creates the slides using my existing slide creation skills, uploads the result, and emails me the link. When I open it, the presentation follows the style I already use.

That’s the useful part for me: starting an existing workflow with my voice.

Post a thought to X

In the demo, I ask it to post a thought about AI requiring less of our attention.

It acknowledges the request and hands it to the agent. When the task finishes, I can hear the completion result, then check the published post.

Ask, then carry on

Longer tasks run asynchronously. I can keep talking to Gemini Live, or end the conversation while the agent continues working.

The live WebSocket connection doesn’t need to stay open just because a background task is still running. Keeping those separate avoids holding an unnecessary live session while waiting.

When a result arrives, a bell appears above the home character. I can press BOOT to start a conversation and hear the result.

A small visual signal is enough to tell me something is ready.

A voice request that turns into something on paper

“Look for a cute coloring page picture and print it out on my printer.”

Because Hermes is connected to my home network, it can handle finding the picture and sending it to my printer.

A little later, there’s an actual coloring page on paper. That was probably my favorite part of the demo. :)

The sleep screen actually means sleep

E Ink can retain the image without continuously powering the display. But a sleeping character alone wouldn’t tell us much about the rest of the device.

In this firmware, idle light sleep closes the microphone and speaker, turns Wi-Fi off, puts the display into sleep, and then enters ESP32 light sleep. Pressing BOOT wakes it and the device reconnects.

The firmware checks for pending agent work before entering idle sleep. While asleep, Wi-Fi is off, so it isn’t receiving new network notifications.

I haven’t measured battery life yet. But the power-saving behavior is real, rather than just a different picture on the screen.

Why E Ink?

I wanted something that could sit quietly until I needed it. No glowing display. No scrolling. No stream of unrelated notifications competing for my attention.

Gemini Live and the remote agent still need network access. But around the house, this gives me a deliberate way to reach the assistant without picking up my phone.

Could I do many of these things from a phone? Yes. But I know what happens when I pick it up for one small thing. There’s usually a second thing waiting. And a third. :)

Build your own version

The source code and complete build guide are on GitHub.

I chose E Ink because a quiet display was the point. You could use another ESP32 device, including one with an AMOLED display, and build on the same idea.

For me, the interesting question is how little interface I need before the assistant becomes useful.

AI should require less of our attention, not more.

Originally published on LinkedIn.

E Ink AI Assistant with Gemini Live was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

Enter AI Skills — specifically, Google’s open-source dbt-sf-to-bq-translator agent skill.

In this post, we’ll explore how this specialized AI skill works under the hood, why traditional transpilation falls short for dbt projects, and how you can orchestrate a seamless Snowflake-to-BigQuery migration using your favorite LLM.

Why Dbt Migrations Break Traditional Translators

Automated translation tools (like the BigQuery Translation Service) are incredible at converting raw Snowflake SQL into standard BigQuery-compliant syntax. However, they hit a wall when introduced to dbt projects for two primary reasons:

1. Jinja Macro Collisions: Standard transpilers don’t understand dbt templating tags ({{ config(...) }}, {{ ref() }}, {{ source() }}, and custom macros). Feeding raw dbt .sql files directly into a transpiler usually results in syntax errors or mangled references.
2. Dialect-Specific Edge Cases: Snowflake allows shorthand syntactic sugar like column::date, dateadd(), negative intervals (- interval '5 month'), and specific JSON extraction patterns that BigQuery handles differently via standard functions like CAST(... AS DATE) and DATE_ADD().

The dbt-sf-to-bq-translator skill solves this by establishing a rigorous, multi-stage pre-processing, translation, and post-processing lifecycle.

Inside the Translation Lifecycle

When an AI agent loads the dbt-sf-to-bq-translator skill, it doesn’t just guess how to rewrite your code. It follows a structured, script-driven workflow designed to protect your project architecture:

1. Pre-Processing & Placeholder Masking

Before touching any translation engine, the skill processes your source .sql files:

• Strips and isolates the {{ config(...) }} header block from the top of each model.
• Masks dbt macros with safe placeholder identifiers. For example:
• {{ ref('my_model') }} becomes _DBT_REF_my_model_DBTSEP_
• {{ source('src', 'tbl') }} becomes _DBT_SOURCE_src_DBTSEP_tbl_
• Eliminates Jinja curly braces so the underlying transpiler can process 100% valid, clean SQL.

2. The Migration Execution

The isolated, Jinja-free files are staged and passed through the BigQuery migration workflow. Because the syntax is now standardized, the translation service executes cleanly without choking on custom templating tokens.

3. Post-Processing & AST Reconstruction

Once the BigQuery-translated SQL is returned, the skill performs deep reconstruction:

• AST-Based Config Transformation: Parses the original {{ config(...) }} blocks, safely stripping Snowflake-specific properties (copy_grants, transient, secure) and cleaning up incompatible hooks (pre_hook, post_hook).
• Reference Resolution: Scans the translated SQL for hardcoded paths in FROM and JOIN clauses, automatically mapping them back to native dbt {{ ref() }} and {{ source() }} macros.

Syntax & Edge-Case Auditing: Converts type-cast suffixes (::date— >CAST(... AS DATE)), handles nested dateadd() operations, and logs warnings for any un-allowlisted or custom database-specific macros.

• File Assembly: Prepends mandatory headers, preserves your exact directory structure, and copies over all associated .yml / .yaml configuration files.

Step-by-Step: How to Use the Skill

Using the skill is straightforward whether you are working inside an AI coding assistant or an agent-enabled interface like TypingMind:

1. Install the Skill:
   Add the skill folder directly from the public repository:

npx skills add https://github.com/google/skills --skill dbt-sf-to-bq-translator

1. Configure Your Environment:
   Ensure your Google Cloud CLI session and billing are active:

gcloud auth login gcloud auth application-default login 
gcloud config set project {project_id} 
gcloud services enable bigquerymigration.googleapis.com storage.googleapis.com bigquery.googleapis.com

1. Run the Migration Prompt:
   Point your AI assistant to your dbt project directory and invoke the skill by requesting a migration. The agent will initialize a task tracker (tasks.md), verify your prerequisites, and guide you through transforming your models layer-by-layer (staging $\rightarrow$ intermediate →marts).

Conclusion

Migrating data stacks used to mean weeks of tedious, manual regex-replacement and debugging broken Jinja templates. By packaging expert migration workflows into open-source AI Skills, teams can now automate the tedious boilerplate of dialect conversion while maintaining strict control over their dbt project structure, lineage, and configurations.

Have you tackled a Snowflake-to-BigQuery migration recently? Check out the google/skills repository on GitHub to try out the translator skill on your own codebase.

Automating Snowflake-to-BigQuery dbt Migrations with AI Skills was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

From Local Conductor Loops to CI Governance

In two previous deep dives, I explored running code reviews in two distinct domains of the modern software development lifecycle:

• DIY PR Code Reviews with Antigravity CLI in GitHub Actions: In this earlier post, I walked through building automated, stateless pull request code reviews in the outer loop (CI/CD pipeline) using Google Antigravity CLI (run-antigravity-cli). I demonstrated how to leverage Workload Identity Federation and service account impersonation to let an agent inspect incoming PR diffs via the GitHub REST API and post review feedback without needing local checkouts or stored secrets. A small note: I have since updated the code to direct Workload Identity Federation, eliminating the need for service account impersonation.

• Extending Conductor with CodeMender: AI-Powered Threat Modeling and Remediation: In this follow-up article, I shifted security left into the inner loop (the local developer environment). I showed how embedding CodeMender CLI ( cm ) into Conductor’s review phase enabled deep threat modeling, trust boundary exploration, sandboxed proof-of-concept exploit verification, and automated patching ( cm fix ) directly on the engineer’s workstation before code is committed.

In the same Conductor post, I posed a central architectural thesis: catching security defects locally in the inner loop is essential for developer velocity, but robust enterprise governance still demands an unbypassable gatekeeper in the outer loop (CI). No matter how diligent developers are locally, pull request pipelines must independently verify that incoming code changes introduce zero security regressions.

Naturally, the logical next step was: Why not also run CodeMender CLI directly inside GitHub Actions alongside Antigravity CLI?

What’s New in This Blog Post

This post bridges the inner and outer loops by documenting the exact configurations required to run CodeMender CLI in GitHub Actions. Specifically, I cover:

• Auth Reality Check (Direct WIF vs. ADC File Impersonation): Why the pure 1-hop Direct WIF architecture celebrated in the Antigravity CLI post does not work out-of-the-box for CodeMender CLI, and how to use the 2-hop WIF with service account impersonation and physical ADC credentials generation together.

• The CI Sandboxing Dilemma & Path Constriction: Why the Linux namespace and bubblewrap sandboxing highlighted in our Conductor post fails inside CI runners. We dissect how passing relative paths triggers severe 🚫 SANDBOX VIOLATION errors that blind the agent, how I solved it with workspace root declarations and absolute paths, and when it is safe (or unsafe) to disable sandboxing in CI.

• Hardening Cross-Repository PRs Against Fork Abuse: How to safely allow CodeMender scans on open-source fork PRs using pull_request_target without opening your Google Cloud WIF pool to credential exfiltration.

• Operating in Gated Public Preview: How to manage CodeMender CLI’s gated preview status, Artifact Registry distribution, project allowlisting, and automated preflight dry-run validation.

Cloud Authentication Architecture: Direct WIF vs. ADC File

One of the most consequential differences between running Antigravity CLI alone and orchestrating CodeMender CLI in CI lies in how each tool handles Google Cloud authentication.

Antigravity CLI: Stateless 1-Hop Direct Workload Identity Federation

When running Antigravity CLI in consumer repositories, such as in nedtargaryen243870-code/modern-app, I make use of Direct Workload Identity Federation (Direct WIF). Google Cloud IAM permissions are bound directly to the federated external GitHub identity without needing any intermediate service account.

As detailed in modern-app PR #10 (Remove service account email from GCP auth), eliminating the service account compressed the authentication handshake from a traditional 2-hop flow down to a direct 1-hop flow:

• Legacy 2-Hop Impersonation: GitHub OIDC Token → Security Token Service (STS) → IAM Credentials API (impersonating a service account) → Access Google Cloud Agent Platform.

• Direct 1-Hop WIF: GitHub OIDC Token → Security Token Service (STS) → Direct Access to Google Cloud Agent Platform.

By granting roles/aiplatform.user directly to the federated principal set:

principalSet://iam.googleapis.com/projects/<PROJECT_NUM>/locations/global/workloadIdentityPools/<POOL_ID>/attribute.repository/<REPO_OWNER>/<REPO_NAME>

the workflow in .github/workflows/test.yml and consumer repos no longer specifies a service_account parameter in google-github-actions/auth@v3 (formalized in run-antigravity-cli PR #14).

CodeMender CLI: Standard Application Default Credentials (ADC) Requirements

Unlike lightweight API clients, CodeMender CLI ( cm) is an integrated, packaged command-line binary. Under the hood, it utilizes Google Cloud client libraries that expect standard Application Default Credentials (ADC) materialized as a file on disk.

When running CodeMender CLI in CI, attempting to use pure in-memory Direct WIF environment variables causes CodeMender to fail with missing credentials. As resolved in Fork PR #2 (fix: configure ADC credentials file and export GOOGLE_CLOUD_PROJECT) and upstreamed in .github/workflows/test-cm.yml, the auth step must generate a physical credentials file and export environment variables:

      # -------------------------------------------------------------------------
      # Authenticate to Google Cloud via Workload Identity Federation (OIDC)
      # -------------------------------------------------------------------------
      - name: Authenticate to Google Cloud (OIDC)
        id: gcp-auth
        uses: google-github-actions/auth@v3
        continue-on-error: ${{ inputs.dry_run == true }}
        with:
          project_id: ${{ vars.GCP_PROJECT_ID }}
          workload_identity_provider: ${{ vars.GCP_WORKLOAD_IDENTITY_PROVIDER }}
          service_account: ${{ vars.GCP_SERVICE_ACCOUNT_EMAIL }}
          create_credentials_file: true
          export_environment_variables: true

Additionally, CodeMender reads project settings from ~/.codemender/config.yaml. The automated setup script (scripts/setup_cm_config.py) writes the required server.project and server.endpoint fields, ensuring CodeMender CLI has full ADC alignment before scanning starts.

The Sandboxing Dilemma: Container Namespaces vs. Ephemeral CI Runners

Sandboxing is a core architectural pillar of autonomous coding agents. When an AI agent executes autonomously on your workstation, you do not want it reading your private SSH keys ( ~/.ssh ), querying sensitive credentials, or tampering with system binaries. CodeMender enforces this locally using Linux unprivileged user namespaces and bubblewrap isolation.

The Relative-Path Constriction Trap

However, running that same sandbox inside GitHub Actions runners created unexpected failures. When my initial CI workflow attempted to scan changed files in a pull request by passing relative paths (e.g., cm find tests/test_workflow_cm.py ) with an empty default project_paths” [] , CodeMender’s internal agent sandbox constricted allowed roots strictly to:

• /home/runner/work/run-antigravity-cli/run-antigravity-cli/tests/test_workflow_cm.py
• /home/runner/.codemender
• /tmp

As the agent reasoned through the test file, it naturally attempted to inspect project imports, configuration helpers in scripts/, and workflow files in .github/. CodeMender intercepted these calls as sandbox violations. Citing the exact log preserved in Fork PR #2 comment #5346219652:

2026–08–20T21:18:02Z [WARN] 🚫 SANDBOX VIOLATION #1: Agent requested path "/home/runner/work/run-antigravity-cli/run-antigravity-cli" - ⛔ blocked (allowed roots: [/home/runner/work/run-antigravity-cli/run-antigravity-cli/tests/test_workflow_cm.py /home/runner/.codemender /tmp])
2026–08–20T21:18:17Z [WARN] 🚫 SANDBOX VIOLATION #2: Agent requested path ".." - ⛔ blocked
2026–08–20T21:18:38Z [WARN] 🚫 SANDBOX VIOLATION #4: Agent requested path "../scripts/setup_cm_config.py" - ⛔ blocked
2026–08–20T21:18:54Z [WARN] 🚫 SANDBOX VIOLATION #5: Agent requested path "../.github/workflows/test-cm.yml" - ⛔ blocked

Because the sandbox roots were too narrow, the agent was blinded to the broader codebase, unable to follow cross-file references or inspect schemas.

The Fix: Explicit Workspace Roots and CI Sandbox Disabling

In run-antigravity-cli PR #12 (fix: disable namespace sandbox in CI and pass absolute paths), I let my coding agent engineer a threefold fix:

• Declared Workspace Roots: Updated scripts/setup_cm_config.py to default project_paths to [“.”], ensuring the entire repository root is registered inside the agent’s allowed boundaries.
• Absolute Path Normalization: Added the --absolute flag to scripts/resolve_pr_files.py, ensuring target paths are fully qualified and never accidentally restrict directory traversal.
• Disabled Namespace Sandbox in CI: Set sandbox.enabled: false in the headless CI configuration.

With these changes, CodeMender executed flawlessly across 5 target files with zero boundary violations, publishing the clean PR #12 Security Scan Report.

In PR #19 feat(ci): adopt CodeMender native find — diff mode with impact analysis, the workflow further evolved to use CodeMender’s native Git diff mode, i.e. cm find . --diff=... --sandbox=false, scanning from the repository root while retaining --sandbox=false. It also produced a clean PR #19 Security Scan Report.

Security Analysis: Why It Is Safe in CI (And When It Isn’t)

Disabling a security sandbox should never be done lightly. Why is it acceptable here?

• Why It Is Safe on GitHub-Hosted Runners: GitHub-hosted runners ( ubuntu-latest ) are ephemeral, isolated virtual machines provisioned on demand and destroyed immediately after job completion. No persistent secrets, credentials, or state linger after the workflow finishes. Furthermore, cm find is a read-only detection scan. It analyzes ASTs, code paths, and data flows to detect flaws without compiling, executing untrusted binaries, or modifying the host filesystem. Combined with short-lived Workload Identity Federation (OIDC) tokens and isolated CI scripts, there are no static credentials for untrusted code to exfiltrate.

• When It Is NOT Safe (Cautionary Scenarios): If your organization operates self-hosted or persistent CI runners that run multiple jobs sequentially, disabling sandboxing is dangerous. A malicious dependency or prompt injection could inspect neighboring repositories or pivot across the runner host. Similarly, if you execute active remediation ( cm verify and cm fix ) or run build scripts on untrusted fork code, namespace or container isolation is mandatory to prevent arbitrary code execution.

Securing Cross-Repository PRs: Preventing Fork Abuse

A recurring vulnerability in CI pipelines involves handling pull requests submitted from external forks. In public open-source projects, anyone can fork the repository, modify code, and open a PR.

Standard GitHub Actions pull_request triggers run in the context of the fork with read-only repository tokens. Crucially, their OIDC tokens possess external repository claims, preventing them from minting Google Cloud WIF credentials for your cloud project. If you switch blindly to pull_request_target to grant WIF access, a malicious contributor could modify workflow steps to exfiltrate tokens or abuse cloud quota.

To resolve this safely, I approved to have my coding agent implement a defense-in-depth architecture in run-antigravity-cli PR #13 and run-antigravity-cli PR #18:

• Dual-Trigger Architecture & Declarative Association Gating: Workflows listen to both pull_request for internal branches and pull_request_target for external forks. Internal PRs and trusted collaborators ( COLLABORATOR, MEMBER, OWNER ) are evaluated natively in GitHub Actions if: expressions before runners are provisioned, avoiding unnecessary credential minting.
• TOCTOU-Resistant Label Gating ( safe-to-test ): External fork PRs from non-collaborators run when an authorized maintainer explicitly attaches the safe-to-test label after reviewing the diff. To eliminate Time-of-Check to Time-of-Use (TOCTOU) exploits, where an attacker pushes malicious code ( synchronize ) after receiving approval, the scan job only triggers on action == ‘labeled’. In addition, an automated remove-stale-approval job immediately revokes the safe-to-test lable upon any subsequent commit push or PR reopen.
• Trusted Script Isolation ( $RUNNER_TEMP ): Under pull_request_target, the workflow YAML executes from the trusted base branch. However, because CodeMender must inspect the actual PR commit ( head.sha ), untrusted workspace code is checked out on the runner. To prevent the runner from executing attacker-tampered helper scripts, the workflow performs a sparse checkout of scripts/ from the trusted base ref into .trusted-ci, and executes all CI tooling ( setup_cm_config.py, resolve_pr_files.py, report_cm_results.py ) strictly from this isolated directory.

The Operational Reality: Gated Public Preview

For teams eager to adopt CodeMender CLI in their CI pipelines today, there is an important operational prerequisite: CodeMender CLI is currently in a Gated Public Preview.

While the CLI binary is distributed via Google Cloud Artifact Registry, invoking the backend scanning and verification services on Google Cloud Agent Platform requires the calling Google Cloud Project to be allowlisted.

To enable teams to build, test, and wire their GitHub Actions workflows before allowlisting is complete, my workflow and scripts provide a built-in preflight mode:

python3 scripts/setup_cm_config.py - project-id "${{ vars.GCP_PROJECT_ID }}" - project-paths "." - sync-workspace
python3 scripts/resolve_pr_files.py - base-ref "${{ github.base_ref }}" - absolute - dry-run

This allows continuous integration teams to validate WIF token issuance, ADC file materialization, configuration generation, and target file resolution without triggering backend permission rejections.

Key Takeaways & Best Practices Checklist

Deploying autonomous AI security reviewers in GitHub Actions bridges the gap between passive code review and proactive defense. Here is the operational checklist for success:

• Choose the Right Auth Model: Use Direct WIF (1-hop) for stateless API tools like Antigravity CLI to eliminate service accounts and keys; use ADC with service account impersonation (2-hop) with create_credentials_file: true for CodeMender CLI.
• Declare Project Roots & Normalize Paths: Avoid relative path constriction by setting project_path: [“.”] and passing fully qualified absolute paths to cm find.
• Evaluate Sandbox Safety: Disabling namespace sandboxing is safe on ephemeral, single-use GitHub-hosted VMs for read-only scans, but must be strictly preserved or containerized on persistent self-hosted runners.
• Protect Against Untrusted Forks: Always gate external PR execution under pull_request_target with manual review guardrails.
• Plan for Preview Allowlisting: Validate your CI pipeline using dry-run preflights while coordinating Google Cloud Project allowlisting for CodeMender CLI access.

Automating Agentic Security Reviews: Running CodeMender CLI in GitHub Actions was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

Introduction: The Catchment Myth vs. Regional Reality

In the UK housing market, few narratives are as deeply entrenched as the “Catchment Area Premium.” Across London and major metropolitan hubs, property listings routinely trumpet proximity to “Outstanding” primary schools, commanding eye-watering price premiums. Families stretch their borrowing capacity simply to fall on the fortunate side of an administrative line.

In my previous article on mapping Defra flood risks, I explored the physical geography of Shrewsbury — a historic county town bounded and periodically besieged by the unembanked River Severn. Having engineered a fast GeoParquet pipeline to map Flood Zones 2 and 3 in Looker Studio, a natural economic question emerged:

In a historic regional town facing recurring flood risks, does the primary school catchment premium still reign supreme, or is it eclipsed by environmental vulnerability and urban geography?

To answer this, I extended our spatial architecture in Google BigQuery GIS, joining HM Land Registry transaction data across 22 primary school catchments, 3 secondary school zones, and localized flood boundaries. What the data revealed was not a triumph of school branding, but a masterclass in urban economics and topography.

Data Architecture: Extending the Spatial Pipeline

Rather than visualizing individual, noisy property sales on a raw map, my objective was to design a clean, high-performance analytical model in BigQuery that preserves both property-type distinctions and environmental context.

Our data stack incorporates four foundational layers:

1. HM Land Registry (Price Paid Data): Transaction records including sale prices, transfer dates, and property classifications: Detached (D), Semi-detached (S), Terraced (T), and Flat (F).
2. ONS Postcode Directory (ONSPD): Providing precise geographic coordinates (ST_GEOGPOINT) for postcode centroids.
3. Shropshire Council School Catchments: Shapefiles defining 22 localized primary catchments and 3 broader secondary school zones.
4. Environment Agency Flood Zones 2 & 3: Our pre-optimized flood boundary table (shrewsbury_flood_zones), processed via QGIS and GeoParquet in the previous project.

Balancing Performance and Granularity

Directly piping hundreds of thousands of individual transactions into Looker Studio introduces latency and skews aggregate calculations. Conversely, rolling up metrics strictly by school name discards vital property characteristics.

To solve this, I engineered an intermediate aggregation layer grouped by postcode × property_type × is_flood_area, retaining sample size (total_transactions) and baseline prices to enable dynamic weighted averaging downstream.

WITH property_points AS (
  -- 1. Geocode transaction records to Point geometries
  SELECT
    lr.price,
    lr.property_type,
    lr.postcode,
    ST_GEOGPOINT(ons.longitude, ons.latitude) AS geom_point
  FROM
    `your_project.land_registry.price_paid` AS lr
  JOIN
    `your_project.geo_reference.onspd` AS ons
    ON lr.postcode = ons.postcode
  WHERE
    ons.outcode IN ('SY1', 'SY2', 'SY3')
),
joined_catchments AS (
  -- 2. Spatial Join with Primary & Secondary boundaries
  SELECT
    p.price,
    p.property_type,
    p.postcode,
    p.geom_point,
    pri.school_name AS School_name,
    sec.school_name AS secondary_school_name
  FROM
    property_points AS p
  LEFT JOIN
    `your_project.catchments.primary_schools` AS pri
    ON ST_CONTAINS(pri.geometry, p.geom_point)
  LEFT JOIN
    `your_project.catchments.secondary_schools` AS sec
    ON ST_CONTAINS(sec.geometry, p.geom_point)
)
-- 3. Intersect with Flood Zones and generate pre-aggregated analytical table
SELECT
  jc.School_name,
  jc.secondary_school_name,
  jc.property_type,
  jc.postcode,
  CASE 
    WHEN fz.geometry IS NOT NULL THEN TRUE 
    ELSE FALSE 
  END AS is_flood_area,
  ROUND(AVG(jc.price), 0) AS average_sale_price,
  COUNT(1) AS total_transactions
FROM
  joined_catchments AS jc
LEFT JOIN
  `your_project.environment_agency.shrewsbury_flood_zones` AS fz
  ON ST_INTERSECTS(fz.geometry, jc.geom_point)
WHERE
  jc.School_name IS NOT NULL
GROUP BY
  1, 2, 3, 4, 5;

Visualization in Looker Studio: Uncovering the Baseline

To prevent sampling skew across postcodes with unequal turnover, I configured a custom calculated metric in Looker Studio for weighted average pricing:

Weighted Average Price = SUM(average_sale_price * total_transactions) / SUM(total_transactions)

When rendered across all 22 primary school catchments, the property hierarchy behaves impeccably: Detached houses sit firmly at the top, followed by Semi-detached, Terraced, and Flats.

At first glance, significant price gaps appear between catchments. Rural and edge-of-town schools like Longden CofE show massive detached averages (£650k+) driven by expansive rural parcels. Within town boundaries, St George’s Junior and Coleham Primary lead the urban pack, maintaining semi-detached averages near £400k.

The Reality Check: Flood Risk as the Ultimate Arbitrator

The dynamic shifts dramatically the moment we apply our is_flood_area boolean filter.

By isolating properties directly touching EA Flood Zones 2 and 3, an unyielding market penalty surfaces:

Coleham Primary School:
• Non-Flood Semi-detached: ~£390k
• Flood Zone Semi-detached: ~£300k
• Net Discount: -£89,854 (-23%)

St George’s Junior School (Frankwell Lowlands):
• Non-Flood Semi-detached: ~£400k
• Flood Zone Semi-detached: ~£295k
• Net Discount: -£105,022 (-26%)

Greenfields Primary School:
• Non-Flood Semi-detached: ~£274k
• Flood Zone Semi-detached: ~£226k
• Net Discount: -£47,336 (-17%)

The Martin Wilson School (Castlefields):
• Non-Flood Terraced: ~£222k
• Flood Zone Terraced: ~£192k
• Net Discount: -£29,965 (-13%)

Regardless of academic accolades or community reputation, crossing the threshold into the River Severn’s flood path imposes an immediate, non-negotiable 20% to 25% price discount within the very same school boundary.

Deconstructing the Myth: Why Geography Beats Catchments

Seeing Coleham’s flooded semi-detached homes hold a £300k baseline — still higher than non-flooded homes in outer suburbs like Mereside (~£270k) — one might be tempted to argue: “The catchment premium is so strong it cushions the flood hit!”

However, looking at the structural geography of Shrewsbury completely disproves this assumption.

1. Urban Gravity vs. Academic Prestige

Coleham sits immediately across the English Bridge from the town center. Its price floor is sustained not by school gates, but by walkable lifestyle amenities, independent shops, and direct pedestrian access to the town core.

When comparing Coleham to Castlefields (The Martin Wilson School catchment) — which shares an identical urban-edge location and River Severn flood exposure — flooded terraced homes in Castlefields trade around £190k, compared to £280k in Coleham. This £90k chasm is rooted in housing typology: Castlefields comprises compact, industrial 19th-century worker terraces, whereas Belle Vue and Coleham boast wider Victorian and Edwardian villas with substantial gardens.

2. The Absence of Feeder Pressure

In London, primary catchments matter immensely because admissions funnel into competitive secondary schools. In Shrewsbury, secondary education across the urban core is unified under a single shared catchment (Shrewsbury Schools) encompassing The Priory, Meole Brace, Belvidere, and Shrewsbury Academy. With no hyper-localized secondary gating mechanism, the primary school “feeder race” largely evaporates.

3. Stock Eras Across Suburban Rings

Comparing adjacent southwestern residential suburbs further confirms the absence of school-driven pricing:

• Radbrook Primary vs. Meole Brace CE Primary: Detached homes in both catchments trade almost identically at ~£460k–£470k. Yet, semi-detached properties in Meole Brace trade ~£70k higher than Radbrook. This reflects architectural epochs: Radbrook’s housing stock is dominated by mature, uniform 1980s–90s suburban estates, whereas Meole Brace’s catchment captures a significant concentration of recent high-spec new-build developments (particularly along the southern urban extensions) alongside historic village plots, commanding a notable new-build premium.

Conclusion: Spatial Intelligence Over Conventional Wisdom

When I set out to analyze the relationship between school catchments and property values, conventional wisdom suggested I would find a distinct educational premium.

Instead, my BigQuery GIS pipeline uncovered an entirely different truth:

• Physical hazard is absolute: Flood risk enforces an undeniable 20–25% valuation cut within identical school boundaries.
• School catchments are secondary to urban structure: In regional towns with unified secondary systems, micro-catchment premiums are practically non-existent. Market prices are driven overwhelmingly by walkability to town, architectural epoch, and topography.

Relying on high-level averages often reinforces market myths. By joining open geospatial datasets at granular resolution, spatial data analysts can cut through generalized assumptions and illuminate the true mechanics of local real estate.

Looking Ahead: The Final Synthesis

Over the course of my spatial data projects, I have dissected Shrewsbury from multiple angles:

• Environmental Risk: Engineering optimized Defra Flood Zone boundaries via GeoParquet.

• Healthcare Infrastructure: Mapping travel-time access to GP clinics and hospitals.

・Urban Development: Tracking planning permissions and heritage conservation in real time.

・Social Economics: Evaluating school catchments against property market fundamentals.

In the upcoming capstone article of my spatial analytics work, I will bring all these disparate spatial layers together into a comprehensive Urban Spatial Intelligence Framework — demonstrating how modern data engineering can build a holistic, 360-degree digital twin of a regional town. Stay tuned!

Does the “Catchment Premium” Really Exist? was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.