Lynn Lee's Site
← AI Agents

Data Agents: Executable SQL Is Only the Start of a Trustworthy Answer

Use an advertising analytics example to connect query permissions, metric definitions, scan budgets, and result validation.

Lynn LeeCompiled 2026-11-074 min read
Read in Chinese →

Engineering notes with synthetic examples. Version scope and references are included in each article.

A user asks why yesterday’s conversion rate fell. A data agent generates SQL and reports an 8% decline. The task is not complete yet. Is the denominator clicks or impressions? Which time zone defines yesterday? Are conversions grouped by occurrence date or attributed click date? Has the data finished arriving?

A trustworthy data agent must access the right data, use the right metric definition, and connect its conclusions to concrete evidence. The model is one component of that query workflow.

Establish a query contract

For an ambiguous question, consult a maintained metric catalog before creating a structured contract. An illustrative contract might be:

metric: attributed_order_cvr_v2
grain: campaign_id, business_date
numerator: deduplicated attributed orders
denominator: valid clicks
date_basis: attributed click date
timezone: Asia/Shanghai
window: 2026-11-05 through 2026-11-06
freshness_requirement: partition completion status is ready

Under this definition, conversion rate does not necessarily mean the probability that a clicking user places an order: one click may be associated with multiple orders. A user-probability metric needs a distinct-user numerator and a corresponding user denominator. The agent must preserve the agreed definition instead of silently changing it to simplify the SQL.

Fields outside the user’s authorized scope should not appear in the contract. A prompt saying “do not look at these fields” is not an access-control mechanism.

Enforce read-only behavior below the model

The first layer is identity and data authorization: use a restricted identity appropriate to the task, authorized views, and necessary row and column restrictions. AWS Lake Formation supports row-, column-, and cell-level filtering, with limitations depending on the engine and data type. Validate the actual integration. See Lake Formation data filtering .

The second layer is a SQL gateway. Parse the entire syntax tree, permit only supported single queries and allowlisted functions, and recursively validate CTEs, subqueries, tables, and columns. Checking that the string starts with SELECT is insufficient: a dialect may allow file output, functions with side effects, or access to restricted system objects. Return structured rejection reasons so the model can make a bounded number of corrections.

The third layer is a resource budget: constrain date ranges, concurrency, runtime, output rows, and scanned bytes. LIMIT 100 generally limits returned rows and does not guarantee a small scan. Athena’s per-query scan limit can cancel an over-budget query. Workgroup aggregate thresholds primarily trigger alerts and should not be mistaken for an automatic hard stop. See Athena data usage controls .

Read-only means no changes to business source data; it does not mean no side effects. Queries incur costs and create audit logs and result files. Result storage still requires access controls and lifecycle management.

Attach provenance to results

Joining fact tables at different grains can inflate metrics. The following illustrative SQL aggregates each side to the same grain before joining. It assumes orders have already been attributed and deduplicated:

WITH clicks AS (
  SELECT campaign_id, attribution_date, COUNT(*) AS click_count
  FROM approved_valid_clicks
  WHERE attribution_date = :day
  GROUP BY campaign_id, attribution_date
), orders AS (
  SELECT campaign_id, attribution_date,
         COUNT(DISTINCT order_id) AS order_count
  FROM approved_attributed_orders
  WHERE attribution_date = :day
  GROUP BY campaign_id, attribution_date
)
SELECT c.campaign_id, c.click_count,
       COALESCE(o.order_count, 0) AS order_count,
       1.0 * COALESCE(o.order_count, 0) / NULLIF(c.click_count, 0) AS cvr
FROM clicks c
LEFT JOIN orders o
  ON c.campaign_id = o.campaign_id
 AND c.attribution_date = o.attribution_date;

The left join retains click cohorts with no conversions. However, a key with attributed orders but no clicks is excluded, so a separate anti-join quality check is necessary. Return a query ID, SQL hash, parameters, metric version, data freshness, row count, and scanned bytes alongside the results. Numbers in the report should cite that query ID so the conclusion can be traced to the calculation.

Rule out definition changes before explaining a movement

A useful validation order is partition completeness, agreement of base counts with a trusted report, join multiplication, stability of deduplication keys, and changes to dimension mappings. Only then inspect publisher or campaign breakdowns. Overall conversion rate is total orders divided by total clicks, not the unweighted average of group rates. A shift toward lower-converting groups can reduce the overall rate even if no group’s own rate falls.

“This group accounts for most of the decline” is a decomposition result. Without an experiment or another identification strategy, it does not establish that the group caused the decline. When evidence is insufficient, explain what is missing and propose the next query instead of inventing a definitive cause.

Acceptance cases should include zero denominators, late partitions, duplicate orders, time-zone boundaries, unauthorized fields, and excessive scans. Track numerical correctness, authorization enforcement, definition consistency, and correct abstention when the question cannot be answered. Compare result sets with appropriate tolerances instead of requiring identical SQL text: several queries may be equally valid.

References

← More topicsBack to top ↑