Wire CRM signals through your warehouse

Write a field ownership register before connecting a destination. In this example, HubSpot owns crm_company_id, dealstage, pipeline, hubspot_owner_id, and next_step_due_at. The last field is an illustrative custom property, not a promised default. Your account registry owns canonical_account_id. The warehouse owns signal_state, signal_reason, rule_version, and evidence_as_of. A person owns disposition and review_notes. Never let the derived signal write back to its own input fields.

Define overdue_next_step_v1 as an active deal in an approved open stage whose next_step_due_at is before the evaluation time. Exclude closed, archived, ownerless, and unresolved-account records. Do not infer an overdue next step from missing data. Use an explicit unknown state instead. Agree with Sales whether an account with two eligible deals produces one summary or two review items. Choose one account summary containing the eligible deal IDs for this pilot.

Land each raw record with source_system=hubspot, source_account_id=74120, object_type=deals, source_record_id=90217, source_updated_at, extracted_at, sync_run_id, payload_hash, archived, and raw_payload. Treat IDs as strings. The stable key is source_system plus source_account_id plus object_type plus source_record_id. This prevents a deal ID from becoming a company ID or colliding with another CRM account. Store requested property names with the extraction configuration, not only with the row.

Publish canonical_account_id=acct_208, crm_company_id=50188, eligible_deal_ids=[90217], signal_state=review, reason_code=next_step_overdue, next_step_due_at=2026-10-12T16:00:00Z, evidence_as_of=2026-10-14T08:00:00Z, computed_at=2026-10-14T08:04:00Z, rule_version=overdue_next_step_v1, owner_id=117, and activation_status=pending_review. Require a current mapping, complete selected inputs, and a recorded source checkpoint before pending_review. Store source observation IDs so a reviewer can trace every reason to a landed record.

Use source_updated_at to describe when the CRM says the record changed. Use extracted_at for when your pipeline observed it and computed_at for when you calculated the signal. None of those timestamps is necessarily the time a commercial action happened. A next-step date is business evidence, not a replication cursor. HubSpot search can lag newly created or updated objects and excludes archived records. A recent computation therefore does not prove recent or complete source data.[1]

Set a pilot policy that blocks activation when the relevant completed extraction is more than two hours old. Show the last successful extraction and the business evidence date separately. The two-hour budget is a local operating choice, not a vendor guarantee. Keep a lateness allowance and scheduled reconciliation because an overlapping cursor alone cannot prove completeness. If a user fixes a next step during a source outage, the consumer sees unavailable evidence rather than an apparently fresh overdue recommendation.

Build an identity table with source_account_id, crm_company_id, canonical_account_id, valid_from, valid_to, mapping_method, reviewer_id, and mapping_status. Approve the mapping using existing account references. Do not make company domain the permanent join key. A holding company, regional subsidiary, and reseller can share a domain without sharing the same commercial owner. Preserve a separate parent_account_id when Sales needs a rollup. Quarantine a one-to-many mapping until RevOps chooses the intended account.

Retrieve deal-to-company associations as a separate dataset. HubSpot batch object reads do not return associations. Airbyte association streams expose from_id, to_id, association_type_id, category, and label, and fetch associations for modified source records incrementally. Schedule relationship reconciliation as well as object extraction. The acceptance example joins deal 90217 to company 50188 and account acct_208. A second unlabeled company link produces ambiguous_relationship, not a guessed owner.[2][4]

Select companies, deals, owners, pipeline definitions, and required associations. Leave unrelated engagement and web analytics streams out. Airbyte supports full-refresh and incremental HubSpot streams and offers a CRM Search Lookback Window. Pin the deployed connector version in your run record and inspect the actual selected catalog. A stream named incremental is not evidence that every related object, deletion, or history field uses the same cursor.[4]

For custom search, persist a manifest of half-open modification-time windows, ascending source time, and paging completion. HubSpot limits searches to 200 records per page, 10,000 results per query, and five requests per second per account. Subdivide oversized windows, but stop if a minimum-resolution bucket still exceeds the cap. An after cursor cannot bypass it. For that bucket, use separately verified record-ID partitions or complete paginated object-list enumeration, then filter the fixed window locally and reconcile completeness. Do not invent a secondary search sort. Keep the checkpoint unchanged and activation held until every partition completes. Replay an overlap next run. Test 10,001 equal-timestamp records and failure on the fallback's last page. Neither may advance a partial checkpoint.[1][2]

Read the supported archived-deal stream rather than trying to discover deletions through search. HubSpot object deletion moves records to its recycling bin, and restoration is available for up to 90 days. Airbyte documents a separate deals_archived stream. Do not generalize that stream to companies or contacts. For each selected object, document the available archive or approved deletion feed and a reconciliation method. An absent search result or an incomplete full scan is never sufficient evidence of deletion.[2][1][4]

Store a tombstone with source key, archive_observed_at, observation_run_id, and reason. Remove archived records from eligible signals immediately after confirmed observation. A delayed live payload must not revive a tombstoned record. Require a fresh authoritative live read and reconciliation approval to clear the tombstone after restoration. Where no trustworthy deletion feed exists, flag missing records as unresolved and hold their activation. Accept the delete test only when an AE loses the recommendation and a subsequent old live replay cannot restore it.

Validate timestamps, selected property presence, stage enumeration, account mapping, and owner mapping before calculation. Preserve distinctions between absent properties, explicit empty values, and rejected values. Confirm the shape in your chosen endpoint because HubSpot company retrieval documents different handling for undefined properties and unset company values. Treat the schema inventory as a required dependency. A renamed custom property must fail qualification instead of silently becoming a false overdue signal.[3]

Use a unique source key and conditional update rule that accepts a newer source_updated_at. PostgreSQL ON CONFLICT supports a conditional DO UPDATE and an atomic insert-or-update outcome within the database. That does not create exactly-once delivery across the CRM, extractor, and destination. Equal-time rows with different hashes need authoritative rereading or quarantine, not arrival-order selection. Keep rejected observations for diagnosis. Acceptance cases include an older update, an equal-time conflict, a missing owner, and a removed company association. Each must produce a visible hold reason rather than an active recommendation.[5]

Show the proposed account summary in a restricted review queue first. RevOps approves the rule version and eligible account cohort. The CRM administrator approves integration-owned output properties such as lab_signal_state, lab_signal_reason, lab_signal_as_of, and lab_signal_rule_version. Configure Hightouch Update mode and match by the existing record ID. Update changes existing records, while Upsert can create missing ones. Specify null handling per field because Always sync can erase existing values, whereas skipping nulls leaves them unchanged.[6]

Separate positive activation from clearing. A positive review write requires current qualifying evidence, an approved rule and cohort, and current recipient access. A withdrawn or unavailable write uses the last confirmed destination record ID and a separately approved clearing policy, even when freshness, positive eligibility, or current mapping fails. It changes only integration-owned fields and discloses no new restricted evidence. Do not use skipped nulls as withdrawal. Retain clearing rows until destination readback confirms the explicit state. Keep payload hash, approval, record ID, attempt, result, and readback in the activation ledger. Test a previously active account becoming stale, archived, or ownerless. Its last confirmed consumer record must receive the clearing state.

The AE sees "Review next step. Deal 90217 has a next-step date of October 12, 16:00 UTC. CRM extraction completed October 14, 08:00 UTC. Rule overdue_next_step_v1." Include links to the authorized deal record and a short instruction to confirm the next step before acting. The AE chooses accepted, dismissed, or data_issue. The AE edits commercial fields through the normal CRM process. The integration does not replace those edits with its recommendation.

Rehearse stale_extraction by stopping the reader and advancing evaluation time past the local budget. Rehearse out_of_order_update by applying 08:10 evidence before 08:05 evidence. Rehearse archived_then_replayed, ambiguous_company_link, and destination_partial_failure. Inspect the consumer record after each scenario. Stale evidence shows unavailable, old evidence cannot replace newer state, ambiguity creates a hold, and a failed destination row remains unconfirmed. Limit retries to the same approved payload version. Cancel pending work when the signal becomes ineligible.

Report qualified coverage as eligible source records with all required contract fields divided by all records in the approved pilot cohort, expressed as a percentage. Report mapping ambiguity as records held for multiple account mappings divided by records requiring a mapping. Measure extraction lag in minutes as extracted_at minus source_updated_at for accepted observations. Publish the observation count beside median and high-percentile lag so a small sample cannot look conclusive.

Measure activation agreement as readbacks matching the approved payload version divided by attempted destination updates. Measure withdrawal delay in minutes between a confirmed ineligibility observation and consumer-visible withdrawal. Report reviewed usefulness as accepted recommendations divided by recommendations with an explicit accepted or dismissed disposition. Keep data_issue and unanswered items separate. None of these measures establishes revenue impact. Investigate the concrete record IDs behind a worsening measure before changing a threshold.

Data engineering starts with raw landing and a shadow signal dataset. RevOps compares source records with review reasons and resolves every named negative case. The CRM administrator then approves a small account cohort and the exact output-property mappings. Sales management confirms who reviews the queue. Expand only after the owners accept reconciliation coverage, freshness behavior, and destination readback evidence. Keep extraction and activation on separate controls so a write incident does not stop source diagnosis.

On incorrect activation, the CRM administrator disables the destination sync first. RevOps marks affected summaries unavailable and coordinates correction of integration-owned fields from the activation ledger. Do not restore deal stages or owner fields because this workflow never owns them. Data engineering retains checkpoints, tombstones, and rejected observations for the agreed retention period. Resume only after replay produces the intended summaries and the administrator approves a new activation batch. Record the rule version and affected account IDs in the incident review.