Why AI Data Agents Still Fail in BigQuery, dbt, and Airbyte

An agent writes a valid BigQuery query, edits a dbt model, and clicks "sync" in Airbyte. The demo looks finished. In production, the query ran against the wrong project, the model changed a column type used by finance, and the sync completed with zero new rows.

None of those failures is a SQL-generation problem.

AI data agents are already useful at writing queries and explaining errors. They are much less dependable at the operational chain around the code: selecting the right environment, applying company definitions, managing credentials, checking dependencies, recovering from failures, and proving downstream state.

That gap explains why a strong text-to-SQL demo is weak evidence for autonomous data engineering. The safe enterprise path is to expose structured tools, constrain the operating surface, and evaluate the final data state rather than the plausibility of the generated code.

A Data Workflow Is Bigger Than SQL

Spider2-V tested exactly this gap. The NeurIPS 2024 benchmark contains 494 real-world tasks across 20 professional applications, including BigQuery, dbt, and Airbyte. Tasks require code, command-line work, and graphical interaction across data warehousing, transformation, ingestion, orchestration, visualization, processing, and service management.

The strongest evaluated baseline in the Spider2-V paper completed 14.0 percent of full workflows. On tasks requiring more than 15 actions, success fell to 1.2 percent. Even an oracle-style step-by-step plan raised overall performance only to 16.2 percent in the paper's analysis.

Those figures describe the models and interfaces tested in that benchmark, not a permanent ceiling. Only 5.7 percent of its tasks were pure command-line work. The other 94.3 percent used a graphical interface, either alone or alongside CLI work. The 14 percent result does not establish the same ceiling for an API-first data agent. It shows that giving a browser-and-desktop agent a plan did not solve action grounding, state tracking, application knowledge, or remote-workspace operation.

A production data task often includes all of these steps:

1. Identify the correct cloud account, project, dataset, branch, and environment.
2. Find the governed definition of the requested metric.
3. Inspect schemas, freshness, lineage, and recent failures.
4. Write or modify SQL, YAML, Python, or connector configuration.
5. Estimate cost and validate the plan before execution.
6. Run the smallest safe job.
7. Inspect logs, tests, schemas, row counts, and data quality.
8. Confirm downstream models, dashboards, or syncs reached the expected state.
9. Record the change, evidence, and approval.

Code generation covers part of step four. A model can produce elegant SQL against the wrong table and cause more damage than a syntax error would. Syntax failures stop. Semantic errors run successfully.

The uncomfortable truth is that data agents inherit the quality of the operating environment. Duplicate sources, undocumented metrics, shared credentials, manual production changes, and weak tests are not made safer by adding a model. Our guide on duplicate data and AI agents covers why entity ambiguity must be fixed or explicitly managed before automation.

Start by treating the workflow as a controlled deployment system, not a chat interface that happens to know SQL.

Give the Agent Structured Tools and Narrow Identities

Graphical interfaces are designed for people. Buttons move, forms change, modals hide state, and an animation can make a completed action look pending. Spider2-V found particularly weak performance on fine-grained GUI actions and cloud-hosted workspaces. Enterprise architecture should reduce that source of uncertainty.

Prefer tools in this order:

1. Typed application API
2. Version-controlled configuration or code
3. Stable CLI with structured output
4. Browser automation only where no supported interface exists

A typed tool should expose the smallest business action, validate parameters, and return structured evidence. run_bigquery_job(project, dataset, sql, maximum_bytes) is easier to constrain than "open the console and run this." create_dbt_pull_request(branch, files, test_selection) is safer than giving a browser agent free access to a production IDE.

Every tool needs a contract:

Identity must be specific to environment and task. BigQuery's access-control documentation separates roles for job execution, data viewing, editing, and administration. Use that separation. A development agent that drafts and tests queries does not need production data-editor access. A production release tool does not need permission to browse every dataset.

Create distinct identities for development, staging, and production. Impersonate or mint short-lived credentials for the authorized user where the platform supports it. Restrict projects, datasets, connector workspaces, repositories, branches, and action types. Never let retrieved instructions or a user prompt widen that scope.

Browser automation sometimes remains necessary for a product setting without API coverage. Constrain it to a dedicated workspace, save a screenshot or accessibility-tree snapshot before and after the change, whitelist destinations, and require approval before any production mutation. Treat the visual action as an unreliable adapter around a governed tool contract.

This structure also improves debugging. A typed tool returns "dataset outside allowed scope" or "schema assertion failed." A GUI trace often returns a screenshot of a spinner.

Put Business Meaning in Governed Contracts

An agent cannot infer the company's definition of active customer, recognized revenue, or qualified pipeline from table names alone. Even humans disagree when definitions live in dashboard formulas, wiki pages, and remembered exceptions.

Give the agent a governed layer containing:

• Metric name, definition, grain, dimensions, and owner
• Canonical source tables and accepted joins
• Time-zone, currency, and fiscal-calendar rules
• Freshness expectations and known delays
• Sensitive fields and approved aggregations
• Model contracts, tests, and downstream consumers
• Deprecation status and replacement sources

The objective is not to place a giant glossary in the system prompt. Make definitions retrievable by stable identifiers and connect them to the code that implements them. When the user asks for net revenue, the agent should retrieve the owned metric definition and referenced models before it invents a query.

dbt model contracts can enforce column names and data types at build time. Contracts do not prove business correctness, but they stop an agent from silently changing a published interface. Data tests can assert uniqueness, accepted values, relationships, and company-specific rules. Both belong in the gate before merge.

Version control should be the default path for transformation changes. The agent creates a branch, edits a bounded set of files, explains the intended effect, and opens a pull request with test evidence. dbt's guidance on continuous integration jobs describes building modified resources in a temporary schema so proposed changes can be tested without altering production tables.

Lineage provides impact context. Before changing a model, list downstream models, exposures, dashboards, and owners. After the change, compare artifacts and affected schemas. A low-risk-looking type cast can break a finance extract three hops away.

Source priority also matters. A warehouse table, dbt semantic definition, dashboard calculation, and finance policy may disagree. Decide which source owns which claim and when the agent must escalate. The retrieval-path framework gives a practical method for authority and freshness rules.

The counter-intuitive result is that a semantic layer helps agents most when it also helps humans. If definitions are not governed enough for analysts to agree, they are not ready to drive autonomous changes.

Verify State, Not Generated Code

The agent's answer is not the deliverable. The resulting data state is.

Use checks before, during, and after execution.

Before execution:

• Resolve project, dataset, location, branch, and target environment explicitly.
• Parse and compile generated code.
• Check referenced objects against an allowlist and current catalog.
• Estimate scan volume and cost.
• Detect destructive statements and production mutations.
• Verify requester permission and required approval.
• Capture current schema, row count, freshness, and relevant job state.

BigQuery supports dry runs for query validation and byte estimates. A dry run does not prove semantic correctness, but it can catch invalid queries and unexpectedly expensive scans before execution. Set maximum bytes billed as an independent control rather than trusting the agent's estimate.

During execution:

• Assign trace, job, and idempotency identifiers.
• Enforce time, cost, and row limits.
• Capture structured logs and tool responses.
• Stop on permission drift, unexpected targets, or test failure.
• Prevent automatic retries for non-idempotent mutations.

After execution:

• Read job status from the system of record.
• Compare actual and expected schema.
• Check row counts, null rates, uniqueness, freshness, and key distributions.
• Run data and unit tests for changed resources.
• Confirm downstream jobs and syncs reached the intended state.
• Attach evidence to the pull request or change record.
• Roll back or quarantine output when a postcondition fails.

For Airbyte, a sync marked complete is only one piece of evidence. Use the platform's API documentation to retrieve job state and configuration through a supported interface, then validate the destination tables independently. Check expected streams, cursors, row movement, schema changes, and freshness. "Connector job succeeded" and "the business has correct current data" are different claims.

Verification must be independent of the model that proposed the change. Use warehouse queries, manifests, contracts, test runners, job APIs, and policy code. Asking the same model "did this work?" is not a postcondition.

Read-only work needs checks too. For a generated analysis, record the tables, versions, filters, query job, result count, and metric definitions. A polished chart with an incorrect population is still a failed task.

Use an Autonomy Ladder for Data Work

Do not begin with production pipeline repair. Move through increasing levels of consequence, and require evidence at each level.

Level 1 is often valuable immediately. Data teams spend significant time gathering logs, reading run histories, locating owners, and explaining failures. An agent can assemble that context without changing state.

At Level 2, measure edit distance and reviewer time, not query volume. If engineers rewrite every join, the agent is a typing aid rather than a trusted assistant. That may still be useful, but it should not justify a production credential.

Level 3 creates the cleanest control point. A pull request records the change, test evidence, discussion, and rollback commit. Require the agent to state assumptions and list affected resources. Keep protected branches and mandatory reviews outside model control.

Levels 4 and 5 need isolated targets, seeded test cases, and state comparisons. Run known failure scenarios: expired credential, missing source column, duplicate records, rate limit, partial connector outage, stale manifest, and a production-like object with a confusing name.

Level 6 should stay narrow. Retrying a known idempotent sync after a transient network error is a better autonomous task than diagnosing and repairing an unknown schema change. Expand by failure class, not by giving the agent a general administrator role.

Our stability-gates framework explains why the current workflow must meet quality and operating thresholds before scope grows.

Run a Production-Like Data-Agent Evaluation

A useful test suite includes code, tool use, state, security, and business meaning.

Create 50 to 100 representative tasks from incident tickets, pull requests, analyst questions, and recurring operations. For each task, record the starting environment, allowed actions, expected files or queries, final state, forbidden side effects, and escalation rule.

Score five layers:

1. Interpretation: Did the agent identify the correct project, data product, metric, and requested outcome?
2. Plan: Did it choose supported tools and a safe sequence?
3. Code: Does the SQL or configuration parse, compile, and satisfy local rules?
4. Execution: Did tools run against allowed targets with correct credentials and limits?
5. State: Are schemas, data, jobs, lineage, and downstream consumers correct afterward?

Test adversarial ambiguities. Create analytics-prod and analytics-production-old. Include two revenue models with similar names. Remove a column between planning and execution. Return a successful API response while leaving a downstream job pending. The purpose is to prove that the agent checks identity and state instead of trusting names and responses.

Run the suite after changes to the model, prompt, tool schema, connector, warehouse permissions, dbt version, semantic definitions, or source schema. Production drift can break a stable agent without any agent-code change. The agent sandbox playbook covers the fixtures, state capture, and release gates needed when this evaluation spans several systems.

Track cost per accepted task, elapsed time, reviewer minutes, rollback rate, permission violations, and failures by layer. A cheaper model that drafts accepted pull requests may beat a larger model attempting fragile end-to-end GUI execution.

Spider2-V does not say data agents are useless. It shows that full workflows are difficult and must be evaluated as workflows. Reduce unstructured interaction, expose state clearly, and automate only the portions whose success can be checked.

How OpenNash Can Help

OpenNash helps data teams choose a bounded starting workflow and build the controls around it. We map BigQuery, dbt, Airbyte, orchestration, lineage, and approval boundaries; define typed tools; set environment-specific identities; and turn schema, test, job, and downstream checks into release gates.

A packaged analytics assistant is a good choice for read-only exploration over a clean, governed warehouse. Custom work fits workflows that cross proprietary systems, company-specific metrics, multiple control planes, or strict write and audit requirements. Where metric ownership and environment separation are missing, those foundations come before mutation access.

The client owns the code, tool contracts, evaluation cases, traces, and operating runbook. Book a call to benchmark one recurring data workflow in a controlled environment before giving an AI data agent production access.