How to Configure Pydantic AI Bigquery Prevent Delete

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up pydantic ai bigquery prevent delete controls, you restrict cloud roles and check agent SQL strings. You give the agent a read-only or append-only service account and block destructive statements in your tool logic. Many teams start by giving their AI agent a standard editor role in Google Cloud. That broad role lets a bad prompt wipe out whole tables or remove key rows in seconds. How pydantic ai bigquery prevent delete works in native tools A Pydantic AI app uses Python functions a

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

To set up pydantic ai bigquery prevent delete controls, you restrict cloud roles and check agent SQL strings. You give the agent a read-only or append-only service account and block destructive statements in your tool logic. Many teams start by giving their AI agent a standard editor role in Google Cloud. That broad role lets a bad prompt wipe out whole tables or remove key rows in seconds.

How pydantic ai bigquery prevent delete works in native tools

A Pydantic AI app uses Python functions as tools to talk to external data systems. When the model wants data, it builds a structured call and passes a query string to your function. Your function then uses the Google Cloud client library to send that job to BigQuery.

To stop deletes at the cloud level, you change the Identity and Access Management role for the service account. You remove table editor rights and assign a custom role that drops table delete and data update permissions. You can also split duties by giving the agent read rights on main datasets and write rights on scratch tables.

Inside the Pydantic AI app, developers add a second check before the query runs. You can use a model validator or a tool check to scan the incoming SQL text. If the text holds words like delete, drop, or truncate, the function raises a retry error. The agent sees the error message and writes a safer query instead.

Where native permissions and prompt checks fall short

Cloud IAM roles decide what a user or service account can do across an entire dataset. Still, BigQuery groups insert, update, and delete rights together in many standard data roles. If your agent must add new rows or update status fields, native roles often allow row deletes too. Building custom IAM roles for every table takes a lot of time as your team adds new agents.

String checks inside Python tool code also leave big gaps in your safety net. A simple word check fails when an agent writes complex SQL scripts, stored procedures, or multi-statement jobs. Plus, code checks only live inside one single app repo. When another team builds a second agent, they have to copy and maintain those same filter rules.

AI Data Exfiltration Prevention + BigQuery IAM: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Native setups also struggle to protect sensitive columns during read queries. An agent with read rights can pull raw emails, phone numbers, or card numbers into its context window. Standard IAM roles decide who logs in, not what individual statements do in flight or what fields need masking.

Checking SQL statements in flight before they run

A stronger way to stop bad queries is to inspect each SQL statement right before execution. Instead of relying on text filters in Python, you parse the query tree at the network level. This lets you block delete, drop, and truncate commands while still allowing safe inserts and reads.

You can also add human review for high-risk actions without changing your cloud IAM roles. When an agent tries to run a write job, the system pauses the request and pings an engineer. Once the engineer approves the step, the query runs and the result returns to the agent. Data masking rules can also rewrite emails and phone numbers in query results before the model processes them.

Add runtime guardrails and masking beside your databases and APIs

Login rules, role grants, and CI checks govern who connects to your stack. Yet any allowed script, engineer, or AI agent still runs commands with the full scope of its role. For workloads on Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes, SSH, or internal HTTP services, hoop.dev sits beside the service as an open-source proxy, a sidecar for your database or API.

Before a query runs, hoop.dev guardrails check the statement against your rules. An AI Analyzer you configure can score intent or hold risky calls for human review. Next, hoop.dev data masking rewrites sensitive fields in responses, and each verdict is logged per listener. Explore the MIT-licensed project on GitHub.

Frequently asked questions

Can BigQuery IAM block row deletes while allowing updates?

Standard roles bundle table data changes together, so you must build custom IAM roles to separate them. Even then, managing table-level permissions across dozens of datasets creates heavy upkeep for your data team.

Why are Pydantic validators not enough to block SQL deletes?

Validators check text patterns in Python, which can miss multi-statement queries, dynamic SQL, or stored procedure calls. They also require every developer to keep the exact same filter code updated across every agent app.

How do we protect sensitive data when agents query BigQuery?

You can apply dynamic data masking to rewrite matched fields like emails, phone numbers, and national IDs in results. This keeps raw personal records out of model prompts while letting the agent finish its analysis.

The query runs. The emails stay hidden.