HubSpot ↔ BigQuery Integration Playbook: ELT Schemas, Reverse ETL, and Attribution

Published by Bles Software, a custom software and AI company based in Yehud-Monoson, Israel, building web apps, AI agents and API integrations for clients in Israel, the US, the UK and the EU.

This playbook details how to integrate HubSpot with Google BigQuery to power analytics, audiences, and activation. You will learn how to land and model HubSpot data in BigQuery, design efficient tables with partitioning and clustering, publish reverse ETL properties and lists back to HubSpot, govern PII, and measure the pipeline with observable SLOs.

Primary keyword: hubspot bigquery integration (US monthly searches ≈ 50; CPC high; intent: informational/commercial). Supporting terms: hubspot to bigquery, bigquery to hubspot, reverse etl hubspot, hubspot events bigquery.

Why BigQuery for RevOps

BigQuery’s serverless architecture, low‑latency partitioned storage, and SQL semantics make it ideal for joining HubSpot with product, billing, and support systems. The pay‑per‑scan model rewards tidy schemas and narrow queries. When paired with a reverse ETL step, BigQuery becomes the truth engine for targeting and reporting while HubSpot remains the action surface for marketing and sales.

End‑to‑End Flow

  1. Land HubSpot data: ingest Contacts, Companies, Deals, Tickets, Activities, and Campaigns into BigQuery staging datasets via APIs or a managed ingest tool.
  2. Model: transform staging tables into curated datasets using dbt or Dataform with incremental models, partitioned by date and clustered on natural keys.
  3. Reverse ETL: publish compact tables mapped to HubSpot properties and audiences; write idempotently and within rate budgets.
  4. Analytics: build attribution and funnel models and serve them to BI tools (Looker, Power BI) and finance.
  5. Observe and govern: freshness, cost, error budgets, and PII controls.

Landing HubSpot in BigQuery

Ingest via API or a HubSpot connector. Regardless of the method, follow these practices:

If you must backfill, batch by date ranges and avoid rewriting partitions unnecessarily. Document load cadence per table.

Ingestion Tooling

Whether you use a managed connector, custom code, or an EL(T) tool, insist on features that matter in production: incremental loads by high‑water mark, schema drift detection, backfill support, and transparent logs/metrics. If your connector hides load internals, keep a parallel control table so you can reason about data freshness and debug without vendor UI access.

Table Design: Partitioning and Clustering

Design for speed and cost:

Keep row counts per partition in a telemetry table so you can detect skew and run cost‑aware maintenance.

Core Models

Build these curated models:

Add a spine_identity model that ties contacts to product users and billing accounts; this enables account‑level modeling and B2B aggregation.

Identity Spine Details

Identity is a graph. Start with edges: contact email ↔ product user, product user ↔ account, account ↔ company domain. Represent edges in tables with source system, confidence score, and first/last seen timestamps. Derive a “best link” table for operational joins (e.g., contact → account) using deterministic tie‑breakers. Store merge history so when marketing merges contacts in HubSpot, your spine updates and historic analytics remain correct.

Reverse ETL Patterns

Reverse ETL models should be tiny, purposeful tables with just a handful of columns: hubspot_contact_id, the property value(s), and a checksum or last_calculated_at. Examples include active_seats, pql_score, churn_risk, and invoice_overdue.

When pushing to HubSpot:

For lists, either write membership explicitly (static lists) or update properties and rely on dynamic lists. Publish a README and an owner for each audience.

Tool vs. Build

Managed reverse ETL tools accelerate delivery and handle retries, batching, and schema changes for you. Building your own can lower cost at scale and give you precise control over rate limits, PII handling, and change windows. A hybrid approach is common: start with a tool, then bring the top two pipelines in‑house later. Either way, treat reverse ETL as a product: version models, publish owners, and expose a dry‑run preview before writes.

Events to HubSpot

Sometimes you need real‑time triggers. Consider publishing a small set of product or billing events into HubSpot via a serverless function fronted by Pub/Sub. Define an event dictionary with required fields, keep IDs idempotent, and guard against accidentally promoting marketing contacts. Only a handful of events are worth the maintenance burden—keep the list short and well‑owned.

Attribution in BigQuery

Compute multi‑touch attribution in BigQuery with SQL window functions:

This keeps complex math out of HubSpot and BI while offering flexibility.

Identity Resolution

For Contacts, use a hashed email as a natural key and maintain linkages to product user IDs. For Companies, use normalized domain and billing IDs. For Deals, the HubSpot Deal ID is the join key; keep a mapping to finance IDs for bookings. Write deterministic collision rules and keep a log of merges for audit.

Observability and SLOs

Instrument your pipeline:

Publish a status page (internal) that shows green/amber/red for freshness, errors, and HubSpot marketing contact usage.

Error Handling and Dead Letters

Classify errors explicitly. Permanent errors (unknown picklist values, missing required properties, permission denied) go to a dead‑letter table with the payload keys and a remediation hint. Transient errors (timeouts, 429s) retry with exponential backoff and jitter. Keep dead letters small and replayable: after an operator fixes the source, a job picks them up and replays without code changes.

Incident Response

Create a short runbook for common incidents: “freshness stale,” “reverse ETL errors spiking,” and “marketing contacts surged.” Each runbook should include: where to look (dashboards, logs), first actions (pause pushes, check credentials), and links to owners. Practice once a quarter.

Security and PII

Use IAM least privilege. Service accounts for ingestion, modeling, and reverse ETL should have separate roles scoped to a single project. Apply column‑level security or dynamic data masking for PII when sharing datasets broadly. Keep raw engagement payloads in a restricted dataset; only curated tables flow to BI. Rotate keys quarterly and use workload identity for long‑running jobs where possible.

Secrets and Keys

Keep HubSpot tokens in Secret Manager and grant access only to the runtime service account. Rotate on a fixed schedule and after any incident. Avoid printing tokens in logs; log correlation IDs and counts only. For BigQuery, avoid service account keys entirely where possible; use Workload Identity Federation to remove static keys from the environment.

Disaster Recovery and Backfills

Store raw HubSpot payloads for a limited retention (e.g., 30–60 days) in Cloud Storage so you can re‑land if loads go wrong. Keep idempotent load jobs keyed by natural IDs and timestamps so replays do not duplicate. For reverse ETL, record the last successful push per property and the checksum sent so you can rebuild the outbound state deterministically if needed.

Publishing to BI

Even if Power BI or Looker is not your immediate goal, design models with BI in mind: star schemas, documented measures, and minimal surprises. Publish a curated dataset to which BI tools connect with read‑only service accounts. This separation keeps operational and analytical concerns decoupled.

Audience Examples (Narrative)

“Contract Renewal Soon” selects accounts with renewal dates within 60 days and usage below a custom threshold. A reverse ETL job updates a renewal_window property and adds eligible contacts to a dynamic list for a CSM playbook.

“Expansion Potential” picks customers whose average weekly active users exceed 85% of their license for three weeks. The job sets expansion_potential = true and writes a usage_pct metric; SDRs run a nudge campaign. Because the model runs daily and writes only changes, HubSpot rate budgets and marketing contacts remain within targets.

Cost Control

BigQuery can be inexpensive if you partition, cluster, and pre‑aggregate. Use scheduled queries and materialized views for expensive transforms. Keep reverse ETL models tiny; avoid writing large text blobs to properties. When analysts need full‑history ad‑hoc analysis, provide a separate sandbox project and make the cost explicit.

Practical Cost Examples

For a marketing database with 5M emails and 200M activity events, a well‑partitioned design that scopes queries to the last 180 days often scans under 5–10 GB per dashboard refresh. The same model with unbounded, unpartitioned scans can blow through terabytes quickly. Track bytes scanned by report and celebrate reductions as real wins.

Testing and Quality Gates

Add tests at multiple layers:

Add a quality gate that blocks reverse ETL from running if core curated tables are stale or tests fail; fail closed to protect HubSpot from bad writes.

Schema Contracts and Registry

Define contracts for reverse ETL tables: column list, types, allowed ranges, and whether nulls are permitted. Version them and store in a small registry table. Jobs validate contracts at runtime and fail fast with actionable messages when a contract is violated. This prevents accidental wide changes from breaking HubSpot properties or lists.

Authorized Views and RLS for BI

When sharing BigQuery data to BI tools, use authorized views or row‑level security policies to restrict access by region or team. Keep the logic simple and auditable—prefer a user_access table that maps user principals to allowed territories. Document the policy so compliance reviews are smooth.

Change Management and Testing

Treat SQL like code: version control, code review, and automated tests. Add dbt tests for not‑null, uniqueness, and referential integrity. Build data diff checks that compare today’s aggregates to last week’s and alert on anomalies. For reverse ETL, add a “dry run” mode to preview counts and sample rows before writing to HubSpot.

Deployment and Scheduling

Use Cloud Composer, Cloud Run jobs, or GitHub Actions on a schedule that aligns with business needs: hourly for hot signals, daily for aggregates. Keep jobs idempotent and order‑independent where possible. Use Pub/Sub to trigger small real‑time updates (e.g., invoice overdue) while keeping most of the pipeline batch.

SLO Math and Cadence

Define SLOs from the end user’s perspective. If SDRs need PQL scores updated within 15 minutes of a user action, work backward: five minutes for data to land, five minutes for the model to update, five minutes for reverse ETL to push. Document these budgets and measure each stage so you can see where headroom exists or where you are consistently running hot.

Performance Tuning in BigQuery

Push heavy joins into materialized views when they stay within limits; otherwise schedule them as incremental tables. Remove unused columns; wide tables waste scans. Leverage clustering—filter by clustered columns to reduce scanned blocks. Prefer UNNEST patterns that minimize row explosion. When building stage change facts, compute them incrementally by scanning only the latest partitions of the source change log.

Extended FAQ

How do I handle contacts without emails?

Create temporary keys based on form submissions or product IDs and delay marketing promotion until a verified email appears. Merge when the email arrives and keep a merge log so analytics remain correct.

Should I copy email bodies into BigQuery?

Not by default. Store metadata (sent, delivered, opened, clicked) and subject lines. If analytics truly requires bodies, encrypt and restrict access to a small group with a strong business case.

Where should I compute lifecycle stages?

Compute canonical lifecycle transitions in BigQuery so Power BI and HubSpot agree. Push a small set of derived properties back to HubSpot (“lifecycle_bucket”) if workflows need them; avoid duplicating complex logic in both places.

Can I push dashboards back into HubSpot?

Link out instead. Use HubSpot for action and Power BI or Looker for analytics. Embedding is possible but often complicates authentication and governance.

How do I avoid promoting too many marketing contacts by mistake?

Keep promotion behind an explicit gate with a preview count. Use allowlists and budgets per property/list. If a run would exceed budget, abort and alert owners instead of pushing blindly.

Case Study (Narrative)

A PLG company moved from spreadsheet exports to a BigQuery model. Within a month, they had a curated fct_deal_stage and dim_company with clean domains. Reverse ETL pushed active_seats and a PQL tier to HubSpot, powering dynamic lists and sequences. SDRs focused on the right accounts; conversion from MQL to SQL improved by 12%. Finance trusted the attribution because BigQuery computed it once and Power BI and HubSpot consumed the same table. Incident volume dropped after the team added freshness dashboards and a simple dead‑letter replay tool.

Example: PQL Pipeline End‑to‑End

Product events land in BigQuery within minutes via a streaming pipeline. An hourly transform aggregates account usage and emits a pql_score table. Reverse ETL converts scores into a pql_tier property for Contacts who are admins or decision‑makers. HubSpot dynamic lists pick up pql_tier = high and enroll them into outreach sequences. A dashboard tracks conversion and response times by tier so the team tunes thresholds confidently. Every step has owner, SLA, and an alert when it falls behind.

Monitoring Dashboards

Create an operational “data plane” dashboard showing: max updated_at per curated table; reverse ETL record counts, successes, and failures; bytes scanned per job; and HubSpot rate usage. A second “business plane” dashboard shows the effect: number of contacts updated, list sizes, campaigns triggered, and downstream pipeline movement. Review both weekly with stakeholders and capture agreed improvements in a backlog.

Rollout Plan

  1. Land a subset of objects; validate volumes and types.
  2. Model core dimensions and the stage fact; publish to BI.
  3. Ship a single reverse ETL property and one dynamic list; verify no marketing contact spikes.
  4. Add attribution and audience expansion; harden with SLOs and runbooks.
  5. Extend to events only where necessary; keep the dictionary tight.

FAQ

Should I stream HubSpot into BigQuery in real time?

Batch is sufficient for most RevOps use cases. Stream only the events that drive immediate action, and keep the surface area small. The operational burden of real time is high; earn it.

How do I keep BigQuery costs under control?

Partition and cluster; avoid SELECT *; pre‑aggregate for top reports; educate analysts to filter by date and keys. Monitor bytes scanned and celebrate teams that reduce it.

Can I write HubSpot custom objects from BigQuery?

Yes, but start with Contact/Company properties and lists. Custom objects add power and complexity; adopt them when there’s a clear operational need and governance in place.

What is the fastest path to value?

Land core objects, model a stage fact, ship one reverse ETL property that drives a campaign, and create a single certified dashboard. Iterate from there.

More RevOps Playbooks from Bles Software