ADK Java + BigQuery, in depth: schema discovery, parameterised metric tools, data-level security and agent analytics

Architecture: who enforces what

The architecture separates three concerns. What the agent may see is enforced by BigQuery: a dedicated dataset of authorized views, row access policies on the source tables, and a service account with read roles on that dataset only. What a query may cost is enforced by the tools: dry runs, setMaximumBytesBilled and job timeouts. What the agent did is recorded by the analytics plugin in a separate dataset the agent cannot read.

Keeping these apart matters because the model writes the SQL. Any rule that lives only in the instruction or in a string check on SQL text can be talked around. A rule that lives in IAM or a view definition cannot, because the job simply has no access to the rows.

Schema discovery tools

Text-to-SQL fails most often on schema, not syntax: the model guesses a column name or misses the partition column. Give it discovery tools that return exactly what it needs and nothing more. Column descriptions are the cheapest accuracy improvement available, so write them in BigQuery and let the tool surface them:

public final class CatalogTools {
  private static final BigQuery BQ = BigQueryOptions.getDefaultInstance().getService();
  private static final String PROJECT = "acme-retail";
  private static final String DATASET = "sales_agent";        // authorized views only
  private static final Pattern NAME = Pattern.compile("[A-Za-z0-9_]{1,128}");

  @Annotations.Schema(description = "List the views the agent may query, with descriptions.")
  public static Map<String, Object> listTables() {
    List<Map<String, Object>> out = new ArrayList<>();
    for (Table t : BQ.listTables(DatasetId.of(PROJECT, DATASET)).iterateAll()) {
      Table full = BQ.getTable(t.getTableId());
      out.add(Map.of("name", t.getTableId().getTable(),
                     "description", Objects.requireNonNullElse(full.getDescription(), "")));
    }
    return Map.of("tables", out);
  }

  @Annotations.Schema(description = "Columns, types and descriptions for one view from listTables.")
  public static Map<String, Object> describeTable(
      @Annotations.Schema(name = "table", description = "A name returned by listTables") String table) {
    if (!NAME.matcher(table).matches()) return Map.of("error", "Invalid table name.");
    Table full = BQ.getTable(TableId.of(PROJECT, DATASET, table));
    if (full == null) return Map.of("error", "No such table. Call listTables first.");
    Schema schema = full.getDefinition().getSchema();
    List<Map<String, Object>> cols = new ArrayList<>();
    for (Field f : schema.getFields()) {
      cols.add(Map.of("name", f.getName(),
                      "type", f.getType().name(),
                      "description", Objects.requireNonNullElse(f.getDescription(), "")));
    }
    return Map.of("table", table, "columns", cols);
  }
}

Because DATASET is fixed in code, the model cannot list or describe anything outside it. getTable returns null for a missing table, and returning an error map rather than throwing lets the model recover, the pattern from writing a FunctionTool. For views with many columns, cache the result per process: schemas change rarely, and every metadata call is a round trip on the agent's critical path. Sample values help the model with enum-like columns, but fetching them runs a query. Prefer a hand-written list in the column description.

Metric tools with named parameters

Free-form SQL is flexible and hard to get right. For the ten questions your users ask most, give the model a metric tool: a fixed, reviewed query with typed parameters. The model's job shrinks to choosing the tool and filling in dates, which it does far more reliably than writing joins:

@Annotations.Schema(description = "Net revenue by region between two dates (inclusive), in EUR.")
public static Map<String, Object> revenueByRegion(
    @Annotations.Schema(name = "start_date", description = "YYYY-MM-DD") String start,
    @Annotations.Schema(name = "end_date", description = "YYYY-MM-DD") String end)
    throws InterruptedException {
  String sql = """
      SELECT region, ROUND(SUM(net_amount_eur), 2) AS revenue_eur
      FROM `acme-retail.sales_agent.orders_v`
      WHERE order_date BETWEEN @start AND @end
      GROUP BY region ORDER BY revenue_eur DESC""";
  QueryJobConfiguration cfg = QueryJobConfiguration.newBuilder(sql)
      .addNamedParameter("start", QueryParameterValue.date(start))
      .addNamedParameter("end", QueryParameterValue.date(end))
      .setMaximumBytesBilled(1L << 30)
      .setJobTimeoutMs(30_000L)
      .setLabels(Map.of("agent", "sales-analyst", "tool", "revenue_by_region"))
      .build();
  try {
    TableResult r = BQ.query(cfg);
    List<Map<String, Object>> rows = new ArrayList<>();
    for (FieldValueList row : r.iterateAll()) {
      rows.add(Map.of("region", row.get("region").getStringValue(),
                      "revenue_eur", row.get("revenue_eur").getDoubleValue()));
    }
    return Map.of("rows", rows, "currency", "EUR");
  } catch (BigQueryException e) {
    return Map.of("error", e.getMessage());
  }
}

Named parameters keep model-supplied values out of the SQL text, so a date argument cannot become an injection. A malformed date fails as a query error, which the tool returns as data. The query filters on order_date, assumed here to be the view's partition column, so cost is bounded by the date range. Register metric tools alongside the general runQuery and say in the agent instruction to prefer them. Watch the ratio of metric-tool calls to free-SQL calls: questions that keep falling through to runQuery are candidates for the next metric tool.

LlmAgent analyst = LlmAgent.builder()
    .name("sales_analyst")
    .model(MODEL_ID)
    .instruction("""
        Answer sales questions with data. Prefer revenueByRegion when it fits.
        Otherwise call listTables, then describeTable, then runQuery with a
        single SELECT that filters on order_date. Report numbers with units and
        the date range used. If a tool returns an error, fix the query and retry once.""")
    .tools(
        FunctionTool.create(CatalogTools.class, "listTables"),
        FunctionTool.create(CatalogTools.class, "describeTable"),
        FunctionTool.create(MetricTools.class, "revenueByRegion"),
        FunctionTool.create(BigQueryTools.class, "runQuery"))
    .build();

Security in BigQuery, not in the prompt

Put access control where the model cannot reach it. The pattern has three layers:

1. Authorized views in an agent dataset. Create sales_agent.orders_v as a view over sales.orders that selects only the columns the agent needs, with no email, address or payment fields. Authorize the view on the source dataset. Grant the agent's service account BigQuery Data Viewer on sales_agent only, and BigQuery Job User on the project that runs jobs. The account can query the view without any access to the source tables.
2. Row access policies on the source. If one deployment serves EU staff only, filter rows for that service account at the source table, so every view inherits the filter.
3. No write roles. A statement-type check in runQuery is a convenience. The real guarantee is that the identity holds no role that allows DML or DDL.

CREATE ROW ACCESS POLICY eu_agent_only
ON `acme-retail.sales.orders`
GRANT TO ("serviceAccount:sales-agent-eu@acme-retail.iam.gserviceaccount.com")
FILTER USING (region = "EU");

Once any row access policy exists on a table, principals not granted by some policy see no rows. Add policies for your human analysts and pipelines in the same change, or their dashboards go empty. Test the deployed identity, not your own credentials: an agent that works locally under your user account and returns nothing on Cloud Run is almost always an IAM or policy gap.

Agent analytics: logging the agent into BigQuery

The other direction records what the agent does. The BigQuery Agent Analytics plugin hooks ADK's plugin callbacks and writes events through the BigQuery Storage Write API. The documented Java setup passes it to the runner:

Plugin bqLogging = new BigQueryAgentAnalyticsPlugin(
    BigQueryLoggerConfig.builder()
        .projectId("acme-retail")
        .datasetId("agent_ops")
        .tableName("agent_events")
        .batchSize(50)
        .batchFlushInterval(Duration.ofSeconds(2))
        .build());

InMemoryRunner runner = new InMemoryRunner(analyst, "sales_analyst", ImmutableList.of(bqLogging));

Import the classes from the packages your resolved ADK version provides. Per the adk.dev page, batchSize defaults to 1 and batchFlushInterval to one second. Larger batches mean fewer appends, but more events are lost if the process dies before shutdownTimeout lets the buffer drain. maxContentLength truncates large payloads, defaulting to 500 KB, and rows carry an is_truncated flag. eventAllowlist and eventDenylist filter event types, and contentFormatter lets you mask content before it is written. Use it: prompts and tool results contain customer data, and this table is now a copy of it. The writer needs BigQuery Job User on the project and Data Editor on the table.

The table has typed columns, including timestamp, event_type, agent, session_id, invocation_id, trace_id, status and error_message, plus JSON columns content, attributes and latency_ms. Java emits events such as LLM_REQUEST, LLM_RESPONSE, TOOL_STARTING, TOOL_COMPLETED and TOOL_ERROR, but the docs list some Python-only types, AGENT_ERROR among them, so do not build alerts on events Java never writes. Queries over the typed columns are stable:

-- Error and event volume over the last day
SELECT event_type, status, COUNT(*) AS n
FROM `acme-retail.agent_ops.agent_events`
WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
GROUP BY event_type, status
ORDER BY n DESC;

-- Replay one invocation in order
SELECT timestamp, event_type, agent, status, error_message
FROM `acme-retail.agent_ops.agent_events`
WHERE invocation_id = @invocation_id
ORDER BY timestamp;

Before writing dashboards over the JSON columns, inspect a few rows with TO_JSON_STRING and build queries on the keys you actually see. The key layout is not specified in the overview documentation and can differ between Python and Java. For the metrics worth tracking per tool, see tool observability metrics.

Worked example: a follow-up that falls through

A user in the EU deployment asks: "Which region grew fastest last week compared with the week before?"

1. The model sees revenueByRegion and calls it twice, once per week. Each call is one parameterised job with a 1 GiB cap. Because of the row access policy, both results contain only EU regions. The model was never told that; the data simply has nothing else.
2. It computes growth per region from the two result sets and answers with the date ranges stated.
3. The user follows up: "Break that down by product category." No metric tool fits, so the model calls describeTable on orders_v, finds category and order_date, and calls runQuery. The first draft omits a date filter, and the dry run estimates 14 GB against a 2 GiB budget. The tool returns that as an error, and the model retries with a filter that the dry run estimates at 600 MB.
4. In agent_events, the invocation shows LLM requests and responses, five tool starts and completions, and no TOOL_ERROR, because the budget rejection was returned as data. If you want rejected queries counted, the tool must log them itself or the analysis must parse tool results.

Totals are illustrative: three BigQuery jobs and a handful of dry runs, all labelled with the agent and tool so billing exports attribute cost per tool. Step 4 is the subtle one. Choosing to return errors as data improves recovery but hides failures from event-type counts, so decide which signal your alerting reads.

Evaluating answers and choosing trade-offs

Finally, test whether answers are right. Keep a golden set of 30 to 100 questions with a reference query each, written by an analyst. For each question, run the agent and capture the SQL it executed from the event log or a tool-level log. Execute both queries, then compare result sets rather than SQL text, since many queries are equivalent. Normalise first: sort rows, round floats, and ignore column aliases. Track three rates: exact match, tolerable mismatch such as rounding, and wrong. Run the suite on every model, instruction or view change, and fail the release on a regression. The query cost of the suite is small next to one confidently wrong revenue number in a board deck.

Trade-offs to weigh:

What to do next

1. Create a dedicated agent dataset of authorized views with column descriptions, and grant the agent's service account read access to it alone.
2. Add listTables and describeTable tools pinned to that dataset, and cache their results.
3. Write metric tools with named parameters for your ten most common questions.
4. Keep the guarded runQuery for everything else, with dry run, byte cap, job timeout and labels.
5. Add row access policies where deployments must see different rows, covering existing readers in the same change.
6. Attach BigQueryAgentAnalyticsPlugin with a content formatter that masks customer data.
7. Inspect the JSON columns, then build error-rate and latency queries on the keys you find.
8. Build a golden-question suite that compares result sets, and gate releases on it. See ADK Java callbacks for capturing executed SQL per invocation.