BigQuery warehouse

BigQuery is the analytical plane. Every re-engagement segment, every priority score, and every "who should we call today" decision is a SQL query against it — not against PostgreSQL.

The reason is join shape. Deciding that a contact clicked an email three days ago, has an open deal in a stalled stage, was last called by a rep who has since gone quiet, and has no active task, means joining HubSpot engagements, deals, contacts, and application tables across millions of rows. PostgreSQL holds the operational truth for a single record; BigQuery is where a population is scanned.

Dataset Origin Contents

hubspot4

Stitch export of the HubSpot portal 9298797

contacts, engagements, deals, plus portal-managed views

outreach_app

Mirror of the operational PostgreSQL database

contacts, CampaignTarget, Call, email_campaign_targets, and the Morpheus-authored views

Both live in project bigqueryexport-183608.

Nothing in Morpheus writes to BigQuery. Both datasets are replicas maintained outside this repository — Stitch for the HubSpot side, a database replication job for the application side. Morpheus only issues SELECT. The one exception is vm_contacts_summary, a table that is rebuilt by running its CREATE OR REPLACE TABLE statement by hand.

Export lag

The warehouse is a periodic export, so it is always behind reality by some interval. This is not an edge case to be engineered away — it is a property every consumer is written to tolerate.

Concrete consequences:

  • A contact who booked a meeting five minutes ago can still appear in a re-engagement segment, because the deal stage change has not exported yet.

  • A watermark advanced to exactly the newest lastupdated seen can skip records that land late.

  • A view that says a contact has no active task may be describing a task created after the export ran.

The mitigations are consistent across handlers:

Suppression is re-checked in PostgreSQL

A segment produces a candidate list. Before a handler creates work it calls has_active_task_for_contact() against the live operational database, which sees the task BigQuery does not yet know about. The warehouse proposes; PostgreSQL disposes. See Re-engagement segments.

Overlapping windows are safe

Every watermark consumer upserts on a natural key, so reprocessing a window updates rows instead of duplicating them. That makes it safe to rewind a watermark — see Runtime state in configs.

The deduplication pattern

The Stitch export is append-only: updating a HubSpot contact appends a new row rather than replacing the old one. Every raw table therefore holds a full version history, and a naive SELECT returns a contact once per edit it has ever received.

Every query against hubspot4 opens by collapsing to the newest version:

SELECT * FROM (
  SELECT
    contacts.*,
    ROW_NUMBER() OVER (PARTITION BY contacts.id ORDER BY contacts.updatedat DESC) AS rn
  FROM `{bq-project}.{bq-dataset-hubspot}.contacts` AS contacts
)
WHERE rn = 1

The same shape appears with PARTITION BY on the association key and ORDER BY on a timestamp wherever "the latest one" is needed — the last call engagement per contact, the most recent deal per contact. vm_contacts_summary uses it twice: once to collapse contact versions, once to pick each contact’s most recent CALL engagement.

Omitting the ROW_NUMBER() filter does not fail — it silently multiplies rows. A segment that suddenly returns three times as many contacts as expected, all of them plausible, is almost always a missing rn = 1.

Engagements need a second unnesting step, because associations.contactids is a repeated field — one engagement can touch several contacts:

FROM `{bq-project}.{bq-dataset-hubspot}.engagements` AS e,
  UNNEST(e.associations.contactids) AS contact_id
WHERE e.engagement.type = 'CALL'

Access from Lambda

The morpheus-bigquery layer exposes exactly one function:

from morpheus_bigquery import create_bigquery_client

client = create_bigquery_client(credentials_json=secrets.bigquery_credentials)

It parses a service-account JSON string, builds service_account.Credentials.from_service_account_info(…​), and returns a bigquery.Client whose project is taken from the credentials rather than from configuration.

The library never reads an environment variable and never touches Secrets Manager. The credential string is passed in by the Lambda handler, which fetched it from the BIGQUERY_CREDENTIALS key of agentcore-configs. That is the same rule the MCP tool factories in morpheus-llm follow, and it is what keeps a library testable without AWS. See Configuration and secrets.

There are no repository classes, query builders, or ORM mappings in morpheus-bigquery — the layer is a credential adapter and nothing more. SQL lives either in a BQ_*_QUERY constant next to the handler that runs it, or in a view under bigquery/. See morpheus-bigquery.

Which functions get the layer

MorpheusBigQueryLayer is attached to three of the five functions:

Function Reads

enrichment-scraper

hubspot4.contacts — mirrors HubSpot contacts into PostgreSQL

morpheus-hubspot

hubspot4.engagements and four re-engagement views

phone-agent

v_amazon_seller_contact_engagement_summary

enrichment-scorer and email-agent have no warehouse access at all.

Parameterised queries

Inline queries bind parameters rather than interpolating them:

job_config = bigquery.QueryJobConfig(
    query_parameters=[
        bigquery.ScalarQueryParameter("watermark", "TIMESTAMP", watermark),
        bigquery.ScalarQueryParameter("row_limit", "INT64", row_limit),
    ]
)
rows = client.query(BQ_ENGAGEMENT_QUERY, job_config=job_config).result()

Beyond the injection argument, parameter binding lets BigQuery reuse a cached query plan across runs, and it keeps the SQL constant readable as SQL.

@row_limit appears on both incremental syncs. It bounds a single run’s cost and keeps the job inside the function’s timeout: a very wide watermark window yields a truncated batch and an advanced-but-not-current watermark, and the next scheduled run continues from there.

Cost

Queries are billed by bytes scanned, and the raw tables are wide and version-heavy. Two habits keep it in hand:

  • Select named columns. SELECT * against hubspot4.contacts scans several hundred property columns, most of which no handler uses.

  • Filter on the partitioning/watermark column before joining, so the deduplication window only spans changed rows.

The re-engagement views are the expensive objects, because each collapses full version history across contacts, engagements, and deals. They run on a rate(6 hours) cadence — see EventBridge schedules — which is a deliberate ceiling on spend as much as a freshness target.

Objects

Every view, its defining file, its columns, and its consumer: BigQuery views.