Bigquery

BigQuery Integration

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.
• Scripts (pnpm action) and the server lib are for dashboard UI code and CLI use. The agent calls BigQuery directly via its native tool.

Behavioral rule: "When uncertain if a tool works, call it — don't reason your way to 'it won't work'." Escalating to HTTP/web-request hacks before verifying the simple path fails is a bug in agent behavior.

Connection

• Client: @google-cloud/bigquery Node.js client
• Auth: GOOGLE_APPLICATION_CREDENTIALS_JSON env var (JSON credentials string), falls back to Application Default Credentials
• Project: BIGQUERY_PROJECT_ID env var, defaults to your-project-id
• Caching: 24-hour in-memory cache (sha256 of SQL), max 200 entries
• Byte limit: maximumBytesBilled: 750GB per query

Server Lib

• File: server/lib/bigquery.ts
• Key export: runQuery(sql: string): Promise<QueryResult>
• Return type: { rows, totalRows, schema, bytesProcessed, cached? } — NOT an array, always access .rows
• Placeholder: @app_events resolves to analytics.events_partitioned (fully qualified)

General Table Usage Guidelines

Always use these canonical tables for specific use cases:

Schema preferences:

• Use dbt_mart.* for business-level queries (deals, contracts, subscriptions, customers)
• Use dbt_staging_bigquery.* for raw event data (pageviews, signups)
• Use dbt_analytics.* for reporting views
• Avoid dbt_dev.* - development schema excluded globally

Table Map

Sigma Blog Metadata Column Mapping

Deduplication: Table has duplicates (http:// vs https://). Always deduplicate: REGEXP_EXTRACT(SUOHFYGIOG, r'/blog/([^/?#]+)') with ROW_NUMBER or DISTINCT.

Column Name Differences (bug sources)

Join Paths

• Visitor → Signup: first_pageviews.visitor_id = signups.visitor_id
• Visitor → Contact: first_pageviews.visitor_id = dim_hs_contacts.b_visitor_id
• Signup → Contact: CRITICAL: Match on BOTH user_id AND email

  signups.user_id = dim_hs_contacts.builder_user_id
  AND signups.email = dim_hs_contacts.email
  

  Matching on both prevents mismatches from reassigned user IDs or data sync issues.
• Signup → Subscription: signups.root_organization_id = dim_subscriptions.root_id
• Contact → Deal: No direct FK. Use dbt_intermediate.deal_first_contact or lifecycle stage dates on dim_hs_contacts.
• Blog → Author: builder_blog_content.contentId = content_with_models.content_id → dim_users.name

Datasets Overview

dbt_staging_bigquery (raw staged), dbt_analytics (reporting views), dbt_mart (dimensional models), dbt_intermediate (joins/transforms), analytics (raw events), finance (ARR tracking), sigma_materialized (Sigma-generated views with UUID names).

SQL Patterns

Deriving page_type from URL

CASE
  WHEN url LIKE '%/blog/%' THEN 'blog'
  WHEN url LIKE '%/docs/%' THEN 'docs'
  WHEN REGEXP_CONTAINS(url, r'example\.com/?(?:\?|$)') THEN 'marketing'
  WHEN url LIKE '%/sign-up%' THEN 'webapp'
  ELSE 'other'
END

Timestamps vs Dates

• first_pageviews.created_date is TIMESTAMP — wrap: TIMESTAMP('2025-11-01')
• product_signups.user_create_d is TIMESTAMP — same
• dim_hs_contacts.sign_up_time_stamp is DATE — no wrapping
• Use DATE(timestamp_col) before DATE_TRUNC to avoid type mismatch

Avoid double WHERE

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

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

Analytics Events (Amplitude)

Event column vs Name column

Agent chat events use event column, NOT name column (often NULL). Use event = 'agent chat message submitted'.

Preferred table for agent chat events

Use Amplitude (amplitude.EVENTS_182198) instead of @app_events — smaller, stays within byte limits:

• Use event_type (not event) and event_time (not createdDate)
• rootOrganizationId and organizationId in event_properties JSON
• builderSpaceId is NULL for agent chat events
• Always use capTo30Days() helper for Amplitude time series

Enterprise customer identification

• data.isEnterpriseCompany is unreliable — most enterprise events have it false/missing
• Preferred: JOIN to dim_subscriptions on rootOrganizationId, filter LOWER(plan) = 'enterprise' AND status = 'active'

Key agent chat event types

Customer agent chat message lookup pipeline

1. HubSpot deal → company → contacts
2. dim_hs_contacts (contact_id is INT64) → builder_user_id
3. signups → root_organization_id
4. Amplitude events WHERE JSON_VALUE(event_properties, '$.rootOrganizationId') IN (...)
5. Always filter out internal team emails — internal SEs show up in customer org events

Dashboard Data Fetching (CRITICAL)

NEVER use scripts for dashboard UI data. Use useMetricsQuery(queryKey, sql) with direct BigQuery SQL:

• Define SQL in queries.ts alongside the dashboard
• Queries go through authenticated /api/query endpoint
• For customer lookups, use CTEs with JOINs to dim_hs_contacts
• Scripts are for CLI/agent use only