Building Data Pipelines With the BigQuery Data Engineering Agent

This helps the agent understand the source before generating transformations.

Check whether it identifies important issues such as missing order IDs, null timestamps, duplicate records, and unexpected order statuses.

Do not assume that every suggestion is correct. Compare its observations with the actual business rules.

Step 2: Generate a staging model

Ask the agent to create a staging model:

Create a staging model named clean_orders.

Use project_id.raw.orders as the source.

Requirements:
1. Exclude records with a null order_id.
2. Exclude cancelled orders.
3. Keep the latest record for each order_id
   using updated_at.
4. Preserve the customer_id, order_timestamp,
   total_amount, and order_status columns.

Use Dataform SQLX.

A generated SQLX model could look like this:

config {
  type: "table",
  schema: "staging",
  description: "Validated order records"
}

WITH ranked_orders AS (
  SELECT
    order_id,
    customer_id,
    order_timestamp,
    order_status,
    total_amount,
    updated_at,

    ROW_NUMBER() OVER (
      PARTITION BY order_id
      ORDER BY updated_at DESC
    ) AS row_number

  FROM ${ref("orders")}

  WHERE order_id IS NOT NULL
    AND LOWER(order_status) != 'cancelled'
)

SELECT
  order_id,
  customer_id,
  order_timestamp,
  order_status,
  total_amount,
  updated_at

FROM ranked_orders
WHERE row_number = 1

This example assumes that the orders source has already been declared in Dataform.

The ROW_NUMBER() function selects the latest version of each order. The staging model removes cancelled records and records without an order identifier.

In production, consider how ties in updated_at should be handled. If two records have the same update timestamp, add a reliable secondary ordering column, such as a source event sequence or ingestion timestamp.

Also confirm whether cancelled orders should be excluded permanently. Some reporting requirements need cancelled orders to remain available for refund and cancellation analysis.

Step 3: Create the reporting model

Once the staging model is ready, ask the agent to create the daily sales table.

Create reporting.daily_sales using staging.clean_orders.

Group records by order date.

Calculate:
- Total number of orders
- Total sales amount
- Average order value

Use order_timestamp to determine the date.

Add a description for the table and columns.

A corresponding SQLX model might look like this:

config {
  type: "table",
  schema: "reporting",
  description: "Daily sales summary"
}

SELECT
  DATE(order_timestamp) AS order_date,

  COUNT(DISTINCT order_id) AS total_orders,

  SUM(total_amount) AS total_sales,

  SAFE_DIVIDE(
    SUM(total_amount),
    COUNT(DISTINCT order_id)
  ) AS average_order_value

FROM ${ref("clean_orders")}

GROUP BY DATE(order_timestamp)

The ${ref("clean_orders")} expression tells Dataform that this model depends on the staging model.

Dataform uses that dependency to determine the execution order.

The SAFE_DIVIDE() function prevents a division-by-zero error if the denominator is zero.

One business decision deserves attention: this example groups orders by the date stored in the timestamp. If reports must follow a specific business timezone, convert the timestamp to that timezone before extracting the date.

Add Data Quality Assertions

A pipeline can compile successfully and still produce incorrect data.

For example, a report might contain duplicate order records or missing customer identifiers. Data quality assertions help detect these issues.

Ask the agent:

Add data quality checks to staging.clean_orders.

Requirements:
1. order_id must not be null.
2. order_id must be unique.
3. total_amount must not be negative.
4. order_timestamp must not be null.

Use Dataform assertions and explain
what each check validates.

A SQLX model can define assertions in its configuration:

config {
  type: "table",
  schema: "staging",

  assertions: {
    nonNull: ["order_id", "order_timestamp"],
    uniqueKey: ["order_id"]
  }
}

These checks help detect missing values and duplicate identifiers.

For business rules such as preventing negative order amounts, you can also create a custom assertion:

config {
  type: "assertion",
  schema: "data_quality"
}

SELECT
  order_id,
  total_amount

FROM ${ref("clean_orders")}

WHERE total_amount < 0

A Dataform assertion passes when its query returns no rows. If it returns records, the assertion identifies data that violates the rule.

Review the generated assertions carefully. For example, a negative amount might be invalid for an ordinary sale but valid for a refund record in a different dataset.

Review the Agent's Plan Before Applying Changes

For larger requests, ask the agent to generate a plan before creating or modifying files.

For example:

Prepare a plan for a complete sales pipeline.

Include:
1. Source table identification.
2. Staging transformations.
3. Duplicate handling.
4. Data quality assertions.
5. Daily reporting model.
6. Partitioning and clustering suggestions.

Show the files you plan to create.
Do not apply changes until I approve the plan.

The plan should explain the intended changes, assumptions, and files that will be modified.

Review whether the proposed design matches the actual data model.

If the agent suggests creating several intermediate tables, check whether they are necessary. If it proposes an incremental model, verify that the source has a reliable change-tracking column and that late-arriving records are handled.

After reviewing the plan, provide corrections and approve the changes through the interface.

The agent can then generate or modify the SQLX files in the workspace.

Optimize the Pipeline

As data volumes increase, a pipeline that works well with a small dataset may become expensive.

The Data Engineering Agent can suggest optimizations based on table metadata and available usage information.

For the daily sales table, partitioning by order date may reduce the amount of data read by queries that filter on a date range.

A model configuration could look like this:

config {
  type: "table",
  schema: "reporting",

  bigquery: {
    partitionBy: "order_date",
    clusterBy: ["customer_id"]
  }
}

Use partitioning and clustering only when they match actual query patterns.

For example, partitioning a table by a date column is useful when most reports filter by date. Clustering can improve queries that frequently filter or group by selected columns.

The agent can also suggest incremental models, column pruning, predicate pushdown, and removing repeated calculations.

Do not accept an optimization simply because it reduces the amount of SQL. Compare query execution details and bytes processed before and after the change.

Troubleshoot Failed Pipeline Executions

Pipeline failures can happen because of schema changes, invalid SQL, missing permissions, or unexpected source data.

The Data Engineering Agent can help investigate failed Dataform executions.

Open the failed execution in the Executions tab and select Investigate.

The agent can analyze the failure and provide a troubleshooting report.

For example, suppose the source system changes total_amount from NUMERIC to STRING. A downstream aggregation using SUM(total_amount) may fail.

The agent can help identify the affected transformation and suggest a correction.

However, review the proposed fix before applying it. A cast that converts invalid values to null may make the pipeline compile while silently removing important financial records.

For critical pipelines, validate the correction against sample data and rerun the relevant assertions.

Use GEMINI.MD for Reusable Instructions

Teams often follow common rules for naming, formatting, and data quality.

Instead of repeating these requirements in every prompt, create a GEMINI.MD instruction file in the Dataform workspace.

For example:

# Pipeline Development Rules

1. Use snake_case for table and column names.
2. Place raw data transformations in staging.
3. Place business reporting models in reporting.
4. Add descriptions to new tables and columns.
5. Add data quality assertions for primary keys.
6. Use incremental models only when a reliable
   change-tracking column is available.
7. Avoid SELECT * in production models.
8. Review partitioning and clustering based
   on actual query patterns.

The agent can use these instructions as reusable guidance when generating pipeline code.

This is particularly useful when multiple developers work on the same repository.

Common Mistakes to Avoid

Applying generated code without reviewing it

AI-generated SQL can contain incorrect assumptions about business rules. Review joins, filters, aggregations, and data types before applying changes.

Ignoring duplicate records

Source systems often send updated records more than once. Use an appropriate deduplication strategy and confirm which record should be considered authoritative.

Using incremental processing without a reliable key

Incremental models need a reliable way to identify new or changed records. Without it, updates and late-arriving data can be missed.

Skipping data quality checks

A successful pipeline run does not prove that the output is correct. Add assertions for important business rules and investigate failures.

Assuming the agent executes everything

The agent generates and modifies pipeline code, but developers must review and run or schedule the pipeline through the supported execution workflow.

Summary

The BigQuery Data Engineering Agent helps developers build and maintain data pipelines using natural-language instructions. It works with Dataform to generate SQLX models, organize dependencies, add data quality checks, and troubleshoot pipeline failures.

A practical approach is to begin with a clear source table, ask the agent to inspect its schema, generate staging and reporting models, and add assertions for important business rules.

For larger changes, review the agent's plan before applying modifications. Use reusable GEMINI.MD instructions to maintain consistent coding standards across the team.

The agent can reduce repetitive pipeline development work, but it does not remove the need for data engineers to validate transformations, review business logic, control execution, and monitor production results.