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:** | Use Case | Table to Use | Key Columns | Notes | | -------------------- | ------------------------------------ | --------------------------------------------------------------------- | -------------------------------------- | | Customer contracts | `dbt_mart.dim_contracts` | contract_id, company_id, start_date, end_date, contract_value, status | Canonical source for all contract data | | HubSpot deals | `dbt_mart.dim_deals` | deal_id, amount, stage_name, is_closed_won, close_date | NOT `deal_amount` or `deal_stage` | | Active subscriptions | `dbt_mart.dim_subscriptions` | subscription_id, root_id, plan, status, subscription_arr | Filter `status = 'active'` | | Enterprise customers | `dbt_mart.enterprise_companies` | Joins hubspot_companies + dim_contracts + organizations | Has health_status, renewal dates | | ARR | `finance.arr_revenue_tracker_latest` | unique_id, product, plan, status, arr_change, event_date | arr changes on unique id level | | All Traffic | `dbt_staging_bigquery.all_pageviews` | | | **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 | Logical Name | Actual Table | Key Columns | | -------------------- | --------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------- | | First pageviews | `dbt_staging_bigquery.first_pageviews` | visitor*id, url, referrer, created_date (TIMESTAMP), channel, utm*\*, user_id. **No page_type** — derive from URL. | | All pageviews | `dbt_staging_bigquery.all_pageviews` | Has `page_type`, `sub_page_type`, `first_touch_channel`, `session_channel`, `c_referrer`, full utm fields | | Signups | `dbt_staging_bigquery.signups` | visitor*id, user_id, root_organization_id, utm*\*, signup_url, created_date | | Signups (enriched) | `dbt_analytics.product_signups` | user*id, user_create_d (TIMESTAMP), channel, icp_flag, top_subscription, referrer, utm*\* | | Blog metadata | `sigma_materialized.SIGDS_82deb8e2_40f8_4fb4_b3cb_caa011a72d29` | Cryptic column names — see mapping below. 858 rows, deduplicate by blog slug. | | Blog content (old) | `test.builder_blog_content` | contentId, name (blog TITLE not author), handle, topic. Only 75 rows, no author. **DO NOT use for author data.** | | CRM contacts | `dbt_mart.dim_hs_contacts` | contact_id (INT64), b_visitor_id, builder_user_id, ql_score, date_entered_mql/sal/s0/s1, lifecycle_stage_name | | Deals | `dbt_mart.dim_deals` | deal_id, amount (not deal_amount), stage_name (not deal_stage), is_closed_won (string), arr_amount, close_date, create_date | | Subscriptions | `dbt_mart.dim_subscriptions` | subscription_id, root_id, space_id, subscription_arr, start_date, plan, status | | Enterprise companies | `dbt_mart.enterprise_companies` | Joins hubspot_companies + dim_contracts + organizations. Has upcoming_renewal_date, health_status, customer_stage. | | HubSpot companies | `dbt_staging.hubspot_companies` | company_name, company_id, company_domain_name, upcoming_renewal_date, root_org_id, current_enterprise_arr | ### Sigma Blog Metadata Column Mapping | Cryptic Column | Meaning | Example Values | | -------------- | ------------------------ | ------------------------------------------------------------ | | `SUOHFYGIOG` | Blog URL | `https://www.example.com/blog/sample-post` | | `H5YIATNDT5` | Author | Jane Doe, Alex Chen, Sam Patel, Taylor Kim | | `ZZJ6XRJAII` | Publish date (TIMESTAMP) | | | `FTRKLGZM1R` | Purpose | Acquisition, Awareness | | `IFHWPU1IDO` | Persona | Developers, Product Managers, Engineering Leaders, Designers | | `Z52LFY52AK` | Topic | AI, CMS, Web Development, Design | | `_DGCBJNKLE` | Sub-type | Tooling, Development, Prototyping | | `JQL-G1QE-B` | Sub-topic | AI Design, AI Prototyping, AI Tools | **Deduplication**: Table has duplicates (http:// vs https://). Always deduplicate: `REGEXP_EXTRACT(SUOHFYGIOG, r'/blog/([^/?#]+)')` with ROW_NUMBER or DISTINCT. ### Column Name Differences (bug sources) | Spec Column | Actual Column | Table | | ----------------------- | -------------------------- | --------------- | | `first_pageview_date` | `created_date` (TIMESTAMP) | first_pageviews | | `channel` (pageviews) | `first_touch_channel` | all_pageviews | | `referrer` | `c_referrer` | all_pageviews | | `referrer_channel` | `session_channel` | all_pageviews | | `user_create_date` | `user_create_d` | product_signups | | `deal_stage` | `stage_name` | dim_deals | | `deal_amount` | `amount` | dim_deals | | `visitor_id` → contacts | `b_visitor_id` | dim_hs_contacts | | `user_id` → contacts | `builder_user_id` | dim_hs_contacts | ### 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** ```sql 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 ```sql 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 ```sql -- 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