How to Configure Pydantic AI Snowflake Prevent Delete

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up pydantic ai snowflake prevent delete by using strict database roles and schema rules. You give the agent a read-only role and block drop or delete actions in code. Many teams start by giving the agent a broad role with write permissions. That wide role lets one bad prompt wipe out whole tables in seconds. You must lock down both the agent code and the database role. How pydantic ai snowflake prevent delete works First, you set up a custom role inside your Snowflake account. You gra

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.

You set up pydantic ai snowflake prevent delete by using strict database roles and schema rules. You give the agent a read-only role and block drop or delete actions in code. Many teams start by giving the agent a broad role with write permissions. That wide role lets one bad prompt wipe out whole tables in seconds. You must lock down both the agent code and the database role.

How pydantic ai snowflake prevent delete works

First, you set up a custom role inside your Snowflake account. You grant this role select permissions on the exact tables the agent needs. You never grant delete, drop, or truncate rights to this specific role. Next, you create a service user and assign it to that read-only role. When the agent starts a session, it logs in as this limited user.

On the app side, you build tools inside your Pydantic AI agent. Each tool uses a strict data schema to check the input from the model. Instead of letting the model write raw SQL, you give it safe search tools. The model fills in plain filter values like dates or status names. Your Python code then takes those safe values and runs a fixed read query.

Why you should not let agents write raw queries

Some workflows need the agent to write custom queries for complex data tasks. In those cases, teams often write string checks in Python to block bad words. The code checks the query text for words like delete or drop before running. This text check catches simple mistakes made by the language model.

String checks alone fail when a model writes complex SQL with multiple statements. A prompt trick can hide a delete command inside a stored procedure call. That is why the Snowflake role must always act as the final wall. Even if the Python check fails, the database role rejects the delete command.

AI Data Exfiltration Prevention + Snowflake Access Control: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Where native roles and code checks fall short

Native roles and code rules solve basic read-only needs, but gaps remain. Many agents need to update rows or insert logs while still blocking deletes. In Snowflake, table permissions can get messy across dozens of schemas and databases. Teams often reuse one broad service role across many different agent apps. If an engineer adds delete rights for one task, every agent gets that power.

Native setup decides who logs in, not what each statement does in flight. Static roles also do not rewrite sensitive fields like emails or phone numbers. When an agent runs a select query, the database returns raw card numbers. Also, database logs only show the shared service user, not which prompt ran. You need a way to inspect every query and mask data before it returns.

Govern statements and responses across databases and APIs

Good config and scoped roles are needed setup. They do not check single queries in flight or mask sensitive fields in returned rows. Across Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, and internal HTTP endpoints, hoop.dev runs beside each supported service as an open-source proxy, a sidecar for your database or API.

First, hoop.dev guardrails block banned queries before they run. Then hoop.dev data masking rewrites matched sensitive values in responses in memory. The audit trail records every statement and verdict per resource. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.

Frequently asked questions

Can a prompt trick bypass Python schema checks?

Yes, if your tool passes raw strings into a SQL runner, tricks work. You must pair code checks with strict database roles that reject delete commands.

How do I let an agent insert rows but block deletes?

You grant only select and insert rights to the Snowflake role. You leave out delete, drop, and truncate privileges on all tables in that schema.

How can I protect sensitive columns when the agent reads data?

Data masking rewrites matched fields like national IDs or emails in query results. This keeps raw user details out of the model prompt and logs.

The query runs. The emails stay hidden.