Skip to content

BuilderIO/agent-native / templates/analytics/.agents/skills/bigquery

bigquery

Unverified

Query the BigQuery warehouse. Use for warehouse SQL, BigQuery tables, Amplitude-in-BigQuery events, or a metric the data dictionary says lives in BigQuery.

BigQuery

CRITICAL: BigQuery is a Native Agent Tool

bigquery is available directly in the agent's tool list as a native callable tool.

  • If you see bigquery in your available tools — call it directly with your SQL. Do not use HTTP workarounds, web-request hacks, or scripts as a substitute.
  • The server/lib/bigquery.ts description below is the underlying implementation. It does not mean BigQuery is only accessible via terminal commands or scripts.
  • When uncertain if the tool works, call it — don't reason your way to "it won't work". Empirically test by calling the tool.

Data-Dictionary-First Discipline

Before writing any SQL, verify the metric definition and exact table/column names.

  1. Call find-data with the user’s metric or entity question first; it ranks dictionary definitions, saved SQL examples, and generated source metadata together.
  2. Use search-bigquery-schema to confirm exact dataset, table, and column names.
  3. Only write SQL after you know the correct table and columns. Do not guess.

Why this matters: column names commonly differ from spec (see "Column Name Differences" below). Querying the wrong column returns wrong numbers silently. Confirming with search-bigquery-schema or INFORMATION_SCHEMA.COLUMNS before writing the query is always faster than debugging a wrong result.

Source Of Truth Order

Before writing SQL, use the highest-confidence source available:

  1. Owner-defined dbt metric question, even unnamed: follow the dbt path in data-querying/SKILL.md, including its status rules, retry, and warehouse conditions. Its definition outranks any table. Only a successful get-dbt-connection result with readiness: "not_connected" and an empty connections[] permits normal source selection. If any row is ready, keep dbt as the source even when top-level readiness is null. If rows exist but none is ready, report or resolve their readiness and stop. Treat connected: false, null readiness, and failed or unreadable lookups as unknown, not disconnected. A warehouse figure for this metric needs an explicit user request.
  2. find-data with a focused metric or entity question.
  3. Existing dashboard SQL or saved analyses that already answer the same metric.
  4. search-bigquery-schema metadata for exact datasets, tables, and columns.
  5. A concise user clarification when the business meaning cannot be inferred.

Do not invent dataset, table, or column names. BigQuery is a warehouse, not one table; BIGQUERY_PROJECT_ID is only the default project.

Warehouse Table Priority (CRITICAL)

Always prefer dbt-built warehouse tables before raw source tables. They are warehouse data, not owner-approved Semantic Layer definitions; for an owner-defined metric, use the dbt step (step 0 of the source order) first.

  1. dbt_mart.* — dbt-built, first choice for business-level data (deals, contracts, subscriptions, customers, companies).
  2. dbt_analytics.* — dbt-built, first choice for reporting views and aggregated metrics.
  3. dbt_staging_bigquery.* — for raw staged data (pageviews, signups) when dbt_mart/analytics don't have it.
  4. dbt_intermediate.* — for joins/transforms when no mart or analytics table exists.
  5. analytics.* / amplitude.* / raw tables — last resort only, when no dbt equivalent exists.

Never query raw source tables if a dbt model covers the same data. dbt models are deduplicated, tested, and have canonical column names. Raw tables may have duplicates, schema drift, and inconsistent naming.

Avoid dbt_dev.* — development schema, excluded globally.

Always Bound Queries by Date

Always include a date or timestamp filter in any query that touches large event or pageview tables. Unbounded queries scan full table history, hit byte limits, and return unusable result sets. Use the most restrictive date range that still answers the question.

-- Always add: WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
-- Or for TIMESTAMP columns: WHERE event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)

For account health, verify the reporting period, as-of field, and freshness. Never label a current partial-month snapshot as last month's completed usage.

Actions

ActionUse
data-source-status --key bigqueryCheck whether BigQuery credentials and project are configured.
find-data --question <topic>Find ranked metric definitions and saved query examples before SQL.
search-bigquery-schemaList datasets, list tables in a dataset, or describe table columns.
estimate-query-cost --sql "<sql>"Free dry run: bytes, $ per run, and $ per month for scheduled runs.
bigquery --sql "<sql>"Run a real warehouse query after table/column names are known.
query-dbt-semantic-metric --metricName <exact dbt name>Run an owner-defined dbt Semantic Layer metric before warehouse SQL.
get-dbt-connectionCheck each connections[].readiness and its missing setup step.

Saved Dashboard Cost Estimates

External connectors can call estimate-dashboard-cost with a saved dashboard ID, an optional panel ID, and filter values. It uses the existing free dry-run path and requires read access to the saved dashboard. It cannot accept raw SQL or change a saved query. Partial and unpriced results are not zero-cost results.

Check Cost Before Big Queries

Before running a query over a large event or pageview table, call estimate-query-cost with the same SQL. It dry-runs the query (BigQuery reads and bills nothing) and prices it at on-demand rates. Pass runsPerMonth for anything scheduled, such as 8640 for an alert that polls every 5 minutes. Estimates over $1 per run or $50 per month are flagged: tell the user the number before going ahead.

For direct Amplitude/PostHog/Mixpanel product-event data (outside the warehouse copy), there is no first-class action — call provider-api-catalog / provider-api-docs then provider-api-request (provider amplitude, posthog, or mixpanel), and aggregate in run-code for corpus-wide event analysis.

Schema Discovery

Use search-bigquery-schema instead of asking the user to manually print field lists:

pnpm action search-bigquery-schema
pnpm action search-bigquery-schema --dataset=product_events
pnpm action search-bigquery-schema --table=product_events.events
pnpm action search-bigquery-schema --dataset=product_events --search=rootOrganizationId

When describing a table, copy exact field names from the action result. If a field has a nested path such as event_properties.rootOrganizationId, use the proper BigQuery JSON or STRUCT access pattern for that field type.

Dictionary Trust

Approved dictionary entries are canonical. Human-authored but unreviewed entries are usable with light verification. AI-generated unapproved entries are suggestions only: verify the table and columns with search-bigquery-schema and prefer saving a reviewed dictionary update once the meaning is clear.

Common Patterns

  • Use @project.dataset.table when you want the configured project placeholder.
  • Use @app_events only for the optional app-events table; it is not the whole warehouse and it is not a connection-status signal.
  • For metrics or dashboard panels, run at least one real data query before presenting numbers.
  • An unknown table or column error is a normal, recoverable signal — not a stopping point. Use search-bigquery-schema (or INFORMATION_SCHEMA) to get the exact datasets, tables, and columns, correct the query based on the error, and run it again. Iterate until it succeeds or you have made a few corrective attempts; only surface to the user if it still fails or the error is non-recoverable (missing credentials, permission, quota).

Column Name Differences (Common Bug Sources)

Spec ColumnActual ColumnTable
first_pageview_datecreated_date (TIMESTAMP)first_pageviews
channel (pageviews)first_touch_channelall_pageviews
referrerc_referrerall_pageviews
referrer_channelsession_channelall_pageviews
user_create_dateuser_create_dproduct_signups
deal_stagestage_namedim_deals
deal_amountamountdim_deals

Always verify with search-bigquery-schema before writing SQL against an unfamiliar table.

Join Paths — Identity Stitching Rule

Always match on BOTH a stable id AND email when joining contacts/users across tables:

-- CORRECT: both user_id and email
ON signups.user_id = contacts.user_id
AND LOWER(signups.email) = LOWER(contacts.email)

-- WRONG: id alone — IDs can be reassigned or recycled
ON signups.user_id = contacts.user_id

IDs can be reassigned after deletes/merges. Email alone over-matches shared addresses. Require both for exact matches; flag email-only or id-only joins as low-confidence caveats.

SQL Patterns

Timestamps vs Dates

  • TIMESTAMP columns need TIMESTAMP('2025-11-01') comparisons, not date strings.
  • DATE columns need DATE('2025-11-01').
  • Use DATE(timestamp_col) before DATE_TRUNC to avoid type mismatch errors.
  • Use QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) = 1 for deduplication — cleaner than a subquery. Put the partition-date filter in the same WHERE: a dedupe over a whole table, or a view that dedupes before your filter, scans every partition. For first-party events, read first_party_analytics_events_raw_query_range(start_date, end_date), not the first_party_analytics_events_raw_query view. It leaves out http.response, which holds most of the properties bytes; read that event from the raw table with a narrow date range and an event_name filter.

Type Casting

  • Some warehouse tables store booleans as STRING 'true'/'false' — always CAST(col AS STRING) = 'true' rather than treating as BOOL.
  • Amount/ARR fields may be stored as STRING — CAST(amount AS FLOAT64) before arithmetic.

Avoid Double WHERE

-- WRONG: syntax error
WHERE date BETWEEN '...' AND '...'
WHERE col IS NOT NULL

-- CORRECT: single WHERE with AND
WHERE col IS NOT NULL AND date BETWEEN '...' AND '...'

Warehouse usage receipts

BigQuery query responses may include a usageAccounting receipt for the original warehouse job. A recorded status means an observation was persisted; only usageStatus complete confirms that the provider supplied both processed and billed bytes plus cache provenance. Pending and unavailable statuses mean accounting is incomplete, never zero cost.

App-cache responses do not launch another job. Any retained receipt describes the original result, not new spend. The ledger covers read jobs launched through the common Analytics runner; external jobs and delivery/backfill writes are outside it. Billed bytes are provider metadata, not invoice dollars or measured savings. Budget enforcement and recurring reconciliation are separate decisions.

deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 117kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 117kUpdated Claude CodeCodexVerifiedInstall