Branch Test Plan: Sunbelt Senior Insurance Client Hub and Source System Seed (Arrival and Identity)

Test Plan

1. Executive summary

2. Logic areas

Logic area A: client number hard rule and hashing (stg_sunbelt_client)

Dimensions: presence (present, empty string, whitespace only, NULL) and form relative to an existing number (exact, case differs, surrounding spaces, internal form differs). Brute force 4 x 4 = 16. Collapse: form matters only when a value is present (12 combinations reduce to the 4 forms), and exact form is the baseline every other path compares against; empty and whitespace-only behave alike after design decision D-4. Unique paths: surface variant (P4), internal-form variant (P5), NULL (P6), empty or whitespace only (P7).

Logic area B: keep the first sighting (client_h)

Dimensions: arrival (first load only, both loads, second load only), rows per key within one load (one, two), technical key across loads (same, new), hub state (empty first build, incremental build). Brute force 3 x 2 x 2 x 2 = 24. Collapse: the hub ignores the technical key, so both technical-key cases behave alike (24 reduce to 12); a first build and an incremental build differ only in whether existing keys are skipped, which the arrival paths already separate. Unique paths: new key (P1), key in both loads keeps the first (P2, run with a new CRM row ID), key first seen in a later load on an incremental build (P3), two rows of one key in one load (P8).

Each logic area states its dimensions, brute-force count, collapse and reason, then provides this table:

Logic area C: client-number form (inspector_sunbelt_client_number_form)

Dimension: the trimmed, uppercased client number matches the designed form (letter A, hyphen, four digits) or does not. Brute force 2, no collapse. Paths: I1, I2.

Logic area D: source against hub (auditor_sunbelt_client_hub)

Dimension: the count of distinct usable source keys equals the count of non-ghost hub rows, or does not. Brute force 2, no collapse. Paths: A1, A2.

Logic area E: policy by source and load (policy_h)

Dimensions: which source sends the policy (sales data, carrier data, both), whether both sources arrive in the same load or different loads, and the hub’s state (first build, incremental build). Brute force 3 x 2 x 2 = 12. Collapse: when one source alone sends the policy, the load relation does not apply, and the hub state is already separated by the load paths. Unique paths 4 (profile P-12):

Logic area F: policy key form (stg_sunbelt_policy_sale)

Dimensions: carrier code (present, missing), policy number (present, empty), and form when present (exact, case variant, internal variant). Brute force 2 x (1 + 3) = 8. Collapse: a missing carrier code and an empty number with a code present behave alike, one part missing; the exact form is the baseline. Unique paths 4:

Logic area G: client numbers from the sales data (client_h, stg_sunbelt_policy_sale)

Dimension: the sale’s client number is already in the hub, new, or empty. Brute force 3, no collapse. Paths:

Logic area H: insured numbers (insured_h, stg_northfield_policy)

Dimensions: arrival (new, resent), policies per insured (one, several), number (present, empty). Brute force 2 x 2 x 2 = 8. Collapse: an empty number goes to the ghost record whatever its arrival. Unique paths 4:

Logic area I: roles on a policy (client_policy_l)

Dimensions: people per policy (one, two), roles per person (one, two), role code (documented, undocumented), and arrival (new, resent). Brute force 2 x 2 x 2 x 2 = 16. Collapse: the resend behaves alike for every role combination and is tested once (area J). Unique paths 4 here:

Logic area J: one person under two role keys, and the resend (insured_policy_l, client_policy_l)

Dimensions: the same person sent by both sources (yes, no), and arrival (new, resent). Brute force 4. Collapse: a person sent by one source only is the baseline of areas H and I. Unique paths 2:

Logic area K: agents (agent_h, stg_sunbelt_policy_sale)

Dimensions: which source names the agent (sales, carrier, both), policies per agent (one, several), number (present, empty). Brute force 3 x 2 x 2 = 12. Collapse: an empty number goes to the ghost record whatever its source. Unique paths 5 (profile P-19):

Logic area L: the agent on a policy (agent_policy_l)

Dimensions: the two sources agree on the policy’s agent (yes, no), and arrival (new, resent). Brute force 4. Collapse: agreement on a new pair is path AG1’s link row. Unique paths 2:

Logic area M: satellite versions (policy_northfield_s, insured_northfield_s, client_sunbelt_s, client_policy_sunbelt_s)

Dimensions: arrival (first, resent), values on a resend (unchanged, changed, letter case only), and presence in load 2 (present, absent). Brute force 2 x 3 x 2 = 12. Collapse: a first arrival has no prior values to compare, and absence applies only to a key already held. Unique paths 5 (profile P-15). The four satellites share the toolkit’s satellite macro: all five paths are proven on Northfield Life’s data, where the profile plants them, and ST1 to ST3 on each of the other three satellites (Ray, decision 1a, 2026-10-25):

Logic area N: policy number form (inspector_sunbelt_policy_number_form)

Dimension: the trimmed, uppercased policy number matches NF, a hyphen and six digits, or does not. Brute force 2, no collapse:

Logic area O: each new source table against the policy hub (auditor_sunbelt_policy_hub, auditor_northfield_policy_hub)

Dimension, per auditor: distinct usable policy keys in the source table equal the policy hub rows that source fed, or do not. Brute force 2 per auditor, no collapse:

Logic area P: change hashes (stg_sunbelt_client, stg_sunbelt_policy_sale, stg_northfield_policy)

Dimension: the descriptive columns of a key’s two rows are the same or differ. Brute force 2 per model, no collapse. Expected hashes are literals computed with the toolkit’s own expression, checked first against all 81 live staged rows (guide G-8):

Insured details are covered by ST4’s staging test (area M).

Logic area Q: load end dates (client_sunbelt_s1, client_policy_sunbelt_s1, insured_northfield_s1, policy_northfield_s1)

Dimension: a key holds one version or several. Brute force 2 per view, no collapse. A version ends 1 microsecond before the next version’s load time; the latest ends at the end of all times, 8888-12-31 23:59:59:

Red proof for areas M (the 9 added tests), P and Q, 2026-10-25: each test failed for its content under a planted defect, with no compilation error, and passed after the defect was removed. Defects: a hash column dropped from each change hash; a satellite with no change detection, one that never adds a second version, and one that writes nothing; views with no end dating, and views that end each version at its own load time.

Every increment 2 unit test is built from the planted source rows of profile P-16, written and run before the code it proves; severity is not set on unit tests, which always fail as errors under the Fusion engine.

Logic area Z: missing keys (the known-key views, client_sunbelt_s, insured_northfield_s, policy_northfield_s and the missing-key inspectors)

Dimension: a source row’s business key is present or missing, as landing keeps the row, a blank key counting as missing (D-44, D-45). Brute force 2: the satellite side and the finding side are two consequences of the one dimension. A satellite built after the fix is a third path, since rows stored before the fix stay until D-46 removes them. Unique paths 3:

The ten satellite unit tests of area M give the satellites their new sources, the known-key views, with the same rows.

Logic area Y: the classification and the row of a finding (finding_fact)

Dimensions: the finder that wrote the finding (a form inspector, a missing-key inspector, an auditor) and the keys a row misses (one, two). Brute force 3 x 2 = 6. Collapse: the number of missing keys matters only for a missing-key inspector, so form and audit findings each give one path, and a row missing two keys is the one that shows finding_row naming it once. Unique paths 1, proved by one test that holds a finding of each finder and a row missing two keys:

2.1 Coverage by changed file, all seven layers

Every file the branch changes or adds, from git diff --name-status -M origin/qa...HEAD at commit 94639b4, 2026-10-25 05:57Z: 70 files, one row each (COVERAGE: EVERY FILE, EVERY LAYER, K#2 I#226). Existing tests on a changed file are kept and named beside the new. Unit tests are named by their path; the full names are in section 2.

The fix to release 1 (DIG-594) changes or adds 16 files, from git diff --name-status origin/qa...HEAD at commit 976744e on its branch, 2026-10-28 21:00Z, one row each:

The release 1 mart, the views that serve the semantic layer, adds 6 files at commit f48c24b, one row each; the fix changes two of them and adds a seventh at commit 2a921a1 (D-47):

2.2 Lab 1-2 coverage (Ray’s branch, planned)

Lab 1-2 lands Pinecrest Mutual’s policy data (design D-37 to D-39; profile 2.1.5 and P-20). It adds no business-logic model, so it has no logic area and no unit test; its proofs are layers 1 and 2 and the live proof of land_act. The files it changes, every layer walked:

2.3 Mart: the agreed design

The mart’s unit-test paths fall into seven logic areas. Each area’s paths sit in the tables of sections 2.3.1 to 2.3.3, beside the layer 1, 2 and 4 tests that guard the same objects.

2.3.1 The carrier feed: landing, staging, organization hub and satellite

2.3.2 Dimensions: person_dim, organization_dim, policy_dim, source_dim, day_dim, and the role views client_dim, insured_dim, agent_dim, carrier_dim

2.3.3 Facts: client_policy_role_fact, agent_policy_fact, policy_terms_fact, finding_fact

2.3.4 Semantic layer and Mary’s questions: metrics, the six saved queries, the masking mock (D-9), consumer probes

3. Seven-layer proof plan

3.1 Full-build guardrails

4. Consumer-question probes

4.1 Finance workbook and initial UAT proofs

For finance reporting branches, name row/key/value parity checks against the shared report query, totals at the declared grain, Excel types/date/rounding checks and refresh preservation of consumer observations. State consumer-review cases, dispositions and acceptance evidence for the exact workbook revision under K#12 I#27. Technical verification does not imply consumer acceptance. Otherwise state NA with a reason.

Not applicable: this branch is not a finance reporting branch.

5. QA on production-shaped data

5.1 Added and changed tests on QA data

5.2 Regression against production

Before against after inside the same fresh QA environment, never QA against production (QA standard, 4.2): capture_baseline after the fresh clones and before the build, verify_build after it, both steps of the QA job. Every movement is declared in the QA item’s ledger (section 1.3); the first QA build creates every vault object, so each moves from absent to its declared count. Design D-13, which made this section not applicable, was superseded on 2026-10-25.

5.3 Consumer probes and reconciliations on QA data

Probes Q-1 to Q-6 (section 4) rerun on DV_QA and LANDING_QA.CONFIG, through the QA role, with the expected values the QA item declares for the landing state cloned; the three auditors are the reconciliations, expected to write no finding. Recorded in the QA item, section 9.

5.4 Layer 7 in the QA collection

Applies: the protected semantic set is the release 1 mart’s metrics records_not_accepted and rows_missing_key (D-47), and, once built, the conformed mart’s metrics and its six saved queries (section 2.3.4); the fix’s QA record, , lists them.

6. Test artifact inventory

The 137 tests of layers 1 to 4 (45, 5, 81 and 6) equal the 137 that dbt ls --resource-type test --resource-type unit_test returns at commit 2a921a1. Lab 1-2 adds 3 tests (one in layer 1, two in layer 2) on Ray’s branch once its code exists. Guardrails: one capture and one verification per build (3.1). Consumer probes: 6 (4).

7. Origin

This plan cites the design’s decisions and adds none. A test requiring a missing decision returns to the design.

8. Compliance record

Built by the author before the reviewer reads the plan (Standard: Branch Delivery Documents, , compliance record before agreement), at 2026-10-28 21:15Z against commit f1f3ce0, and for the mart against the mart design at 2026-10-31 22:20Z. Verdict: compliant for the fix to release 1, with three deviations each carried by a recorded decision and one API gap recorded. For the mart, written before its code as the Test-Driven Development standard requires: logic areas R to X, 85 tests across layers 1 to 7, 6 consumer probes and the origin rows; its coverage by file (section 2.1) follows the code. Lab 1-2’s extension (section 2.2) walks every layer for each file Lab 1-2 changes, names its 3 tests by layer with their decisions in section 7, and states why it has no unit test: land_act is an operations macro, proved live.

8.1 Template

8.2 Branch delivery documents standard, test plan section

8.3 Testing standard (knowledge 2)

8.4 Live design decisions not proved by a test, and what carries them