Why AI Agents Fail on Production Data Stacks

An agent usually tests its dbt change in a dev schema. That schema holds a sample of the data. The developer's own role owns every object in it. No dashboard reads from it. Production changes all three conditions on the day the change merges.

That is why AI agents fail on production data in places the sandbox never tests. The failures appear after the merge, as a grant the agent should not hold, a credit spike or a broken sync. Cleanlab's 2025 survey found 95 of 1,837 engineering and AI leaders with agents live in production. That is about 5% of the leaders it asked.

Ten failure modes of AI coding agents covers the mistakes an agent makes while it writes code. The seven failures below start after that code merges. Each one has a control in SQL, YAML or a CI job, plus what Altimate Code covers and where it stops.

Seven Ways AI Agents Fail on Production Data That a Sandbox Never Shows

A sandbox passes all seven failures because it lacks the condition that triggers each one.

Six conditions differ between a dev schema and production. A sandbox test exercises none of them.

An Agent Role That Inherits Write Grants Can Change Production Tables

A Snowflake role inherits every privilege of the roles granted to it. So an agent that runs as a developer's role can hold more access than the developer expects. If that role inherits from a transform role, the agent can write to production schemas. The sandbox hides this, because the developer owns every object in the dev schema anyway.

Give agent sessions their own role with read grants only. Future grants extend the read access to tables that other teams create later.

CREATE ROLE agent_readonly;
GRANT USAGE ON WAREHOUSE agent_wh TO ROLE agent_readonly;
GRANT USAGE ON DATABASE analytics TO ROLE agent_readonly;
GRANT USAGE ON ALL SCHEMAS IN DATABASE analytics TO ROLE agent_readonly;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE analytics TO ROLE agent_readonly;
GRANT SELECT ON ALL TABLES IN DATABASE analytics TO ROLE agent_readonly;
GRANT SELECT ON FUTURE TABLES IN DATABASE analytics TO ROLE agent_readonly;

Altimate Code adds a second layer inside the agent. Analyst mode runs SELECT statements only. It blocks INSERT, UPDATE, DELETE and DROP without a prompt. Builder mode asks before each write and always blocks DROP DATABASE, DROP SCHEMA and TRUNCATE.

The --yolo flag approves every prompt automatically. Explicit deny rules still apply under --yolo, because they fire before any prompt exists. So put production write denials in the config file:

{
  "permission": {
    "sql_execute_write": "deny",
    "bash": { "*": "ask", "DROP *": "deny" }
  }
}

Altimate Code's finops_role_grants tool reports the grants that each role holds. On Snowflake, finops_role_hierarchy shows which roles inherit from which. Run both before you connect an agent. The role design itself stays with your platform team.

A Query That Is Cheap in Dev Can Burn Credits on a Production Warehouse

A query that scans a dev sample in two seconds can scan billions of rows in production. The SQL text is the same in both places. Only the table size and the warehouse change. Snowflake bills warehouse time per second, with a 60-second minimum each time a warehouse resumes.

Put the agent on its own warehouse and cap each statement. An X-Small warehouse bills 1 credit an hour, so a 600-second cap stops one statement at about 0.17 credits. The same cap on an X-Large, at 16 credits an hour, stops it at about 2.7 credits.

CREATE WAREHOUSE agent_wh WITH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  STATEMENT_TIMEOUT_IN_SECONDS = 600;

-- 50 credits is an example quota. Set your own.
CREATE RESOURCE MONITOR agent_monthly WITH
  CREDIT_QUOTA = 50
  TRIGGERS ON 100 PERCENT DO SUSPEND_IMMEDIATE;

ALTER WAREHOUSE agent_wh SET RESOURCE_MONITOR = agent_monthly;

Only the ACCOUNTADMIN role can create a resource monitor. A monitor covers warehouses only, so serverless features need their own limits.

Ask for the plan before a large query runs. EXPLAIN compiles the statement without executing it, and it needs no running warehouse.

EXPLAIN USING TEXT
SELECT region, SUM(amount) FROM analytics.fct_orders GROUP BY region;

Altimate Code's finops_expensive_queries and finops_warehouse_advice tools read warehouse history after queries run. Altimate has not published a method for its pre-run cost estimates. Treat the timeout and the monitor as your hard limits. When a spike does get through, a workload root cause analysis traces it to the query that caused it.

A Schema That Changed Since the Agent Last Read It Breaks Its SQL

Another team renames a column on Tuesday. The agent's schema snapshot dates from Monday. So the agent writes SQL against a column that no longer exists, and the model fails on its next scheduled run.

Two dbt settings turn schema drift into an early, loud failure. An incremental model with on_schema_change='fail' stops the run when the source and target columns diverge. The default value, ignore, keeps the run going. A model contract fails the build when the output columns stop matching the declared list.

{{ config(
    materialized='incremental',
    unique_key='order_id',
    on_schema_change='fail'
) }}

The --empty flag builds each selected model against zero input rows. dbt still runs the model SQL on the warehouse, which proves the model builds against today's schema. It skips the expensive reads of input data.

dbt build --select state:modified+ --state prod-artifacts/ --empty

Altimate Code's altimate_core_validate checks table and column names against a supplied or cached schema. It makes no warehouse call, so a stale cache gives a stale answer. Run schema_index after a schema change to refresh the local cache.

The dbt-schema-verify validator compares each modified model's columns against schema.yml after the agent reports that it is done. Validators are off by default. Set ALTIMATE_VALIDATORS_ENABLED=1 to turn them on.

Incremental Models and Backfills Fail on State the Dev Build Never Had

A dev build usually creates an incremental model from an empty target. dbt then runs the full-refresh path of the model. The is_incremental() branch runs for the first time in production, so a bug inside that branch never met a test.

Three ADE-Bench airbnb task failures in our runs trace to a broken is_incremental() filter in the benchmark's own models. The fix is pull request 145 in the dbt-labs/ade-bench repository.

Null values in the key cause a second silent failure. dbt's documentation warns that nulls in a unique_key column can stop the merge from matching rows. The model then writes duplicate rows. Test the key columns directly:

models:
  - name: fct_orders
    columns:
      - name: order_id
        data_tests:
          - not_null
          - unique

Build each changed incremental model twice in CI, against a copy of production. The first build creates the table. The second build runs the incremental branch against that table.

dbt build --select state:modified+,config.materialized:incremental --state prod-artifacts/ --full-refresh
dbt build --select state:modified+,config.materialized:incremental --state prod-artifacts/

A backfill carries the opposite risk. A --full-refresh on a large production table rebuilds the whole table and bills for the full scan.

Compare the incremental result with a full rebuild over the same date range before you trust either one. Altimate Code's data_diff tool runs that comparison. On one database it uses a single FULL OUTER JOIN. Across two databases it compares checksums by bisection. The rows stay in their own databases. The tool needs a warehouse connection and a primary key that you confirm.

A Model Diff Hides the Dashboards and Syncs That Read the Model

A pull request shows the SQL that changed. It does not show the dashboard, the reverse ETL sync or the notebook that reads the table.

dbt exposures declare those readers in YAML. A selector then lists every exposure that a change reaches.

exposures:
  - name: crm_account_sync
    label: CRM account sync
    type: application
    owner:
      name: Revenue Operations
      email: revops@example.com
    depends_on:
      - ref('dim_accounts')

dbt ls --select state:modified+ --resource-type exposure --state prod-artifacts/

Altimate Code's impact_analysis tool combines the dbt manifest with column-level lineage. It lists the affected models, tests and exposures for one model or column change. In one blast radius run, a change to stg_orders had 5 dependent files. The report covered 8 files, with 5 that needed changes and 3 that were safe. The follow-up altimate-dbt build ran 34 models, with 36 passes and 0 errors.

The limit sits in the manifest. A dashboard that no exposure declares is outside that lineage. Declare the readers you know about before you let an agent change the models they read.

Each control sits at one stage, and a change has to pass all four before it reaches production.

Production Rows Carry Personal Data Into the Agent's Context

A dev schema often holds masked or synthetic data. Production holds real email addresses and phone numbers. When an agent reads query results, those rows become part of its prompt. With a hosted model, the prompt goes to the model provider.

Mask sensitive columns for the agent role inside the warehouse. Snowflake masking policies need Enterprise Edition or higher.

CREATE OR REPLACE MASKING POLICY email_mask AS (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() = 'AGENT_READONLY' THEN '*********'
    ELSE val
  END;

ALTER TABLE analytics.dim_customers MODIFY COLUMN email SET MASKING POLICY email_mask;

Altimate Code's altimate_core_classify_pii flags likely personal-data columns from their names and data types. It runs offline, with no model call. altimate-code check --checks pii runs the same class of check in CI. These checks read names and SQL, and they cannot see the values. The masking policy is the control that acts on the rows.

An Agent With Write Access Needs a Gate Between Its Branch and Production

An agent that can open a pull request can ship a change that nobody read closely. The gate belongs in CI, where it runs on every change, whatever wrote it.

name: agent-pr-gate
on: [pull_request]
jobs:
  sql-check:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: pip install dbt-snowflake && npm install -g altimate-code
      - run: dbt compile --target ci
      - run: altimate-code check "target/compiled/**/*.sql" --checks lint,safety,pii --fail-on error

altimate-code check needs no model provider and no API key. For dbt pull requests, dbt_pr_review adds a verdict of APPROVE, COMMENT or REQUEST_CHANGES. Each blocking finding comes from the deterministic engine. The LLM reviewer adds advisory comments, and those comments never block.

The review bot always posts a COMMENT review event on GitHub. So the bot alone cannot satisfy branch protection, and a person still approves the merge. Where each deterministic check sits in the agent loop covers the checks that run inside the session, before CI.

Altimate's Agentic Data Engineering Platform applies the same pattern to agents that change compute settings. Its Databricks SQL warehouse Auto Tune runs each change as snapshot, apply and verify. It rolls a change back when p95 query latency passes 1.5 times its baseline-week level and also rises by a couple of seconds.

Start With a Read-Only Role and a Statement Timeout for Agent Sessions

Set up the two warehouse controls first, because neither one needs a new tool. Create the agent_readonly role and the timed agent_wh warehouse from the SQL above. Then add the CI gate, so every agent branch passes the same checks as a human branch.

Altimate Code is an AI Data Engineering Agent that installs with npm install -g altimate-code. The same package ships Analyst mode, deny rules and the check command. The missing harness in your data stack covers the wider design for data agents. An agent-first data platform covers the same problem at the platform level.