Hands-on with the BigQuery Data Engineering Agent

Google's BigQuery Data Engineering Agent reached general availability on April 22, 2026. The pitch is straightforward: describe a pipeline in plain English, and the agent produces a Dataform workflow — source declarations, SQLX transformations, assertions, and a schedule — that you review and deploy. We spent two weeks putting it through its paces on a realistic retail schema. This is a hands-on walkthrough followed by an honest assessment of where it saves time and where it needs a senior engineer standing behind it.

What the agent actually is

The agent lives inside BigQuery Studio and works against a Dataform repository. You give it a goal; it inspects the tables you point it at (schemas, sample values, existing Dataform definitions), drafts SQLX files, runs them in a workspace, reads the errors, and iterates. The output is ordinary Dataform code in your repository. There is no proprietary runtime: once generated, the pipeline runs on Dataform's scheduler exactly as a hand-written one would.

That design choice is the most important thing about it. Everything the agent does is visible as a diff and can be reviewed, edited, rejected, or version-controlled like any other code.

Walkthrough: from prompt to pipeline

1. Set up the repository

Create a Dataform repository connected to a Git remote (GitHub, GitLab, or Cloud Source Repositories), and a development workspace. Grant the agent's service account — the Dataform service agent plus the identity you are logged in with — BigQuery Data Viewer on the source dataset and BigQuery Data Editor on the target dataset, nothing broader.

2. Write the prompt

Agents do well with prompts that name tables, grain, and rules explicitly. This one produced a clean first draft against our test schema:

Build a daily pipeline from raw.orders and raw.order_items into analytics.daily_revenue_by_category. Grain: one row per order_date × product_category. Metrics: gross_revenue (sum of item price × quantity), refunded_revenue (items where status = 'REFUNDED'), net_revenue. Exclude orders with is_test = TRUE. Partition the output by order_date, cluster by product_category. Add assertions that net_revenue is never negative and that order_date has no gaps over the last 30 days.

3. Review what it generated

The agent produced three files. The core transformation, lightly trimmed:

config {
  type: "incremental",
  schema: "analytics",
  name: "daily_revenue_by_category",
  bigquery: {
    partitionBy: "order_date",
    clusterBy: ["product_category"]
  },
  assertions: {
    nonNull: ["order_date", "product_category"],
    rowConditions: ["net_revenue >= 0"]
  }
}

SELECT
  DATE(o.created_at) AS order_date,
  i.product_category,
  SUM(i.unit_price * i.quantity) AS gross_revenue,
  SUM(IF(i.status = 'REFUNDED', i.unit_price * i.quantity, 0)) AS refunded_revenue,
  SUM(i.unit_price * i.quantity)
    - SUM(IF(i.status = 'REFUNDED', i.unit_price * i.quantity, 0)) AS net_revenue
FROM ${ref("raw", "orders")} o
JOIN ${ref("raw", "order_items")} i USING (order_id)
WHERE NOT o.is_test
${when(incremental(),
  `AND DATE(o.created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)`)}
GROUP BY 1, 2

Plus a source declaration for the two raw tables and a separate assertion file for the 30-day gap check using GENERATE_DATE_ARRAY and a LEFT JOIN — which it got right on the first try.

4. What we changed before merging

• Incremental window. The 3-day lookback is a guess. In most retail businesses refunds can arrive 30 days after the order, so we widened the window and changed the config to uniqueKey: ["order_date", "product_category"] with updatePartitionFilter so late refunds update the right rows instead of duplicating them.
• Currency. unit_price is in the order's local currency. The agent had no way to know that; the prompt did not say. We added a join to the FX table. This is the canonical example of business context that lives in people's heads.
• Timezone. DATE(o.created_at) truncates in UTC. The business we modelled reports in America/Chicago. One-line fix, material numbers.

5. Deploy

Merged to main, created a release configuration and a daily workflow configuration in Dataform, and set a Cloud Monitoring alert on failed assertions. Total elapsed time, including review: about ninety minutes for a pipeline that would have been half a day by hand.

Where it accelerates

• Boilerplate. Source declarations, config blocks, partition/cluster settings, and standard assertions are produced correctly and consistently — arguably more consistently than humans.
• Iteration on errors. When a run fails, the agent reads the BigQuery error and fixes the obvious cases (type mismatches, unqualified column names, missing GROUP BY columns) without being asked.
• Onboarding. Junior engineers learn Dataform idioms faster by reviewing agent output than by reading docs.
• Migrations. Given an existing scheduled query or a legacy stored procedure, it does a reasonable job of restructuring into Dataform's dependency graph.

Where it needs a human — every time

• Semantics the schema does not express. Currency, timezone, soft deletes, late-arriving data, "status" columns with undocumented meanings. The agent will produce confident, wrong SQL.
• Incremental correctness. It defaults to simple append-style increments. Merge semantics, backfills, and idempotent reruns need explicit instruction and review.
• Cost. It does not reason about bytes scanned. We have seen it propose full-table rescans for an incremental step that could have used a partition filter.
• Security. It will happily read any table its credentials can see. Scope IAM before you start, not after.
• Assertions as a false comfort. Generated assertions check what was asked, not what matters. Row-count drift and referential checks still need a human to specify.

A review checklist

Before any agent-generated Dataform change merges, we check:

1. Grain is stated in the file header and the GROUP BY matches it
2. Incremental strategy handles late data and reruns idempotently
3. Timezone and currency handling are explicit
4. Every ${ref()} points at a declared source, and no raw string table names appear
5. Bytes billed for a full run and an incremental run have been measured
6. Assertions cover nulls on keys, uniqueness on grain, and at least one business rule

Verdict

The Data Engineering Agent is the first generative tool we are comfortable recommending for production pipeline work, because its output is reviewable code rather than a black box. Used as a fast junior engineer with a senior reviewer, it shortens delivery meaningfully. Used unsupervised, it produces pipelines that are syntactically perfect and semantically wrong in ways that surface months later in a board deck. We help teams set up the review loop that makes the first mode the default; see our AI and Gemini services or contact us.