# NexusCRM Salesforce Staging Parity Check

- Status: Open
- Priority: P0
- Owner: Unassigned
- Created: 2026-06-24
- Last updated: 2026-06-24
- Related branch: `codex/nexuscrm-phase1-runtime-fixes`
- Related PR: https://github.com/pingcap-inc/dbt-dev/pull/16

## Objective

Record the parity result for the Phase 1 NexusCRM migration of Salesforce staging models and one downstream layer, and identify the follow-up work required before cutover.

## Scope

Compared the sandbox NexusCRM-backed models in `gcp-digital-bi.codex_nexuscrm_phase1` against current prod tables for:

- enabled `models/digital_bi_dbt/staging/salesforce` models
- one downstream layer from those staging models
- `salesforce_campaign_attributions`, added explicitly because `dws_campaign_attribution` depends on it but it was outside the initial one-layer selector

This check did not run the full `--select path:models/digital_bi_dbt` build.

## Evidence

Generated artifacts:

- `target/nexuscrm_parity/row_counts.csv`
- `target/nexuscrm_parity/key_coverage.csv`
- `target/nexuscrm_parity/schema_diff.csv`
- `target/nexuscrm_parity/selected_models.json`
- `target/nexuscrm_parity/sandbox_columns.csv`

Build commands used:

```bash
uv run dbt seed --no-partial-parse \
  --profiles-dir /tmp/dbt_nexuscrm_phase1_profile \
  --select opportunity_stage_categorisation salesforce_campaign_member_status_mapping payment_type_mapping tenant_country_to_region_conversion

uv run dbt run --no-partial-parse \
  --profiles-dir /tmp/dbt_nexuscrm_phase1_profile \
  --select path:models/digital_bi_dbt/staging/salesforce+1

uv run dbt run --no-partial-parse \
  --profiles-dir /tmp/dbt_nexuscrm_phase1_profile \
  --select salesforce_campaign_attributions dws_campaign_attribution
```

Build result:

- Initial scoped run built 38 of 39 selected models.
- The only initial failure was `dws_campaign_attribution`, because `salesforce_campaign_attributions` had not been built by the selector.
- Follow-up run for `salesforce_campaign_attributions dws_campaign_attribution` succeeded.

Schema result:

- 40 compared relations had exact schema parity.
- `missing_in_sandbox=0` for every relation.
- `new_in_sandbox=0` for every relation.
- `type_differences=0` for every relation.

## Findings

### Cutover Blockers

| Model | Sandbox rows | Prod rows | Delta | Key finding |
| --- | ---: | ---: | ---: | --- |
| `salesforce_opportunity_products` | 0 | 2,607 | -2,607 | Downstream output is empty. |
| `stg_salesforce_opportunity_field_history` | 6,591 | 41,219 | -34,628 | No overlapping keys with prod; source/history semantics differ. |
| `stg_salesforce_revenue` | 5,393 | 7,766 | -2,373 | Revenue source is materially short. |
| `salesforce_revrec` | 5,393 | 7,766 | -2,373 | Downstream revenue output inherits the revenue gap. |

`salesforce_opportunity_products` is the clearest hard failure. The staging model has 2,607 rows, but `product_c` is null for every sandbox row, so the downstream join to `stg_salesforce_product.product_id` returns zero rows. Prod has 2,607 opportunity product rows with 20 distinct product refs. Sample checks against `gcp-digital-bi.nexuscrm_final.nexuscrm_opportunity_product_c` showed `Product__c` is also null, so this appears to be an upstream NexusCRM source-data issue.

### Large Source-Scope Gaps

| Model | Sandbox rows | Prod rows | Delta | Prod keys missing in sandbox | Sandbox keys not in prod |
| --- | ---: | ---: | ---: | ---: | ---: |
| `stg_salesforce_lead_history` | 2,547,928 | 3,731,025 | -1,183,097 | 1,183,099 | 2 |
| `stg_salesforce_task` | 233,915 | 557,106 | -323,191 | 323,559 | 368 |
| `stg_salesforce_opportunity_field_history` | 6,591 | 41,219 | -34,628 | 41,219 | 6,591 |

These are not schema issues. They are source-scope or history-semantics differences that need explicit acceptance or upstream correction.

### Current-State Models With Smaller Deltas

| Model | Sandbox rows | Prod rows | Delta | Prod keys missing in sandbox | Sandbox keys not in prod |
| --- | ---: | ---: | ---: | ---: | ---: |
| `stg_salesforce_account` | 3,361 | 3,308 | +53 | 7 | 60 |
| `stg_salesforce_campaign` | 1,993 | 1,991 | +2 | 1 | 3 |
| `stg_salesforce_campaign_member` | 704,778 | 705,119 | -341 | 363 | 22 |
| `stg_salesforce_contact` | 7,102 | 7,087 | +15 | 1 | 16 |
| `stg_salesforce_lead` | 390,928 | 391,098 | -170 | 220 | 50 |
| `stg_salesforce_opportunity` | 3,627 | 3,588 | +39 | 7 | 46 |
| `stg_salesforce_order` | 5,016 | 5,241 | -225 | 230 | 5 |
| `stg_salesforce_org` | 142,046 | 142,151 | -105 | 109 | 4 |
| `stg_salesforce_user` | 334 | 319 | +15 | 0 | 15 |

These deltas are small enough to investigate as record-level cutover differences, but they are still not exact parity.

### Exact Or Near-Exact Objects

| Model | Sandbox rows | Prod rows | Delta | Key coverage |
| --- | ---: | ---: | ---: | --- |
| `stg_salesforce_order_product` | 5,303 | 5,303 | 0 | exact key parity |
| `stg_salesforce_record_type` | 45 | 45 | 0 | exact key parity |
| `stg_salesforce_user_role` | 43 | 43 | 0 | exact key parity |
| `stg_salesforce_opportunity_product` | 2,607 | 2,607 | 0 | 2 prod keys missing, 2 sandbox keys new |
| `stg_salesforce_product` | 72 | 71 | +1 | 1 sandbox key new |
| `stg_salesforce_quote` | 913 | 899 | +14 | 14 sandbox keys new |
| `stg_salesforce_quote_product` | 1,037 | 1,035 | +2 | 2 sandbox keys new |

## Required Follow-Ups

- Fix `nexuscrm_opportunity_product_c.Product__c` or provide an approved replacement mapping so `salesforce_opportunity_products` is non-empty and matches prod-level key coverage.
- Resolve the revenue gap by switching `stg_salesforce_revenue` to the approved BigQuery data-foundations extract or adding missing upstream NexusCRM revenue coverage.
- Decide whether `stg_salesforce_opportunity_field_history` is expected to use the new audit source with different identifiers. If yes, define metric-level parity checks instead of primary-key parity; if no, add the missing old-compatible history source.
- Investigate the large `stg_salesforce_lead_history` and `stg_salesforce_task` row gaps and document whether they are expected source-scope differences.
- Perform record-level diffs for smaller current-state deltas before cutover: account, campaign member, lead, opportunity, order, org, and user.
- Add this parity check to the PR description or migration checklist so the known blockers are visible during review.

## Acceptance Criteria

- `salesforce_opportunity_products` has non-zero sandbox rows and key coverage comparable to prod.
- `stg_salesforce_revenue` and `salesforce_revrec` use the approved Phase 1 revenue source and have accepted row/key parity.
- Opportunity field history parity is either key-compatible with prod or has an approved metric-level acceptance check.
- Lead history and task gaps are resolved or explicitly accepted by data owners.
- All 40 scoped relations retain exact schema parity.
- A rerun of the scoped staging plus one-layer downstream build succeeds without manual extra models beyond the documented selector.

## Notes

The comparison establishes contract-level schema safety but not cutover readiness. The current state is best interpreted as: model contracts compile and schemas match, but several data-source parity gaps remain open and at least one downstream business output is currently broken.