Branch Profile: Sunbelt Senior Insurance and Northfield Life Source Data, and the Source System Seed (Scope Statement)

Branch Data Profiling

1. Quality standard

This profile follows Standard: Profiling (K#10 I#15): field intent, value discipline, and population for every column, and a verdict of Integrate, Defer, or Reject for each, naming its check. It does not redefine the criteria.

2. Data profile

Source ERD

The branch’s source tables as they land, with their real columns: Sunbelt Senior’s client list and policy sales data, Northfield Life’s policy data, Pinecrest Mutual’s policy data for Lab 1-2, and the team’s source system seed. No source declares a relationship to another table’s rows, so every relationship is dashed and carries its match rate, counted in LANDING on 2026-10-26: 10 of the sales data’s 11 client numbers are in the client list (A-1033 is not); 3 of its 9 Northfield policy numbers are in Northfield Life’s data; and of 7 producer numbers across the sources, the sales data’s 5 include 4 found in another source, Northfield Life’s 4 are all found elsewhere, and Pinecrest Mutual’s 6 are all found elsewhere. The agent is an entity no source sends. No landed column refers to the seed; the vault uses its codes as record sources.

2.0 Population context

The rows are the clients of one fictional agency that an insurance distributor has acquired. Load 1 is the agency’s first full extract. Load 2 is a delta: it carries only new clients and rows that changed or reappeared, so a client absent from load 2 has not left the agency. That matters in a later increment, because an effectivity satellite can read absence as departure only from full extracts.

Names are invented and belong to no real person. There is no test data to filter: every unusual row is a scenario written for the course, listed in profile result P-1 with the path it exercises. The base population for every percentage below is the 43 rows.

2.1 Tables

One subsection per table. Each opens with its grain and, for a history, its freshness, volume trend, change rate, and disappearance; then one subsection per column with the analyses section 5 of the standard requires.

2.1.1 Table SUNBELT.CLIENT

Grain. One row per CRM row per load: the pair (LOADED_AT, CRM_ROW_ID) is unique. Specified duplicate count: 0. Check: grain-unique on that pair.

History measures. Two loads, one day apart; freshness: the latest load is 2026-10-02 02:00 UTC, and the lag behind the source cannot be measured, because the extract carries no source change time. Volume: 33 rows, then 10. Change rate: 2 CRM rows arrive in both loads, and neither changed (0 of 2). Disappearance does not apply to a delta extract, which sends only what it sends, and is stated here as not applicable for that reason.

2.1.1.1 CRM_ROW_ID

Field intent: technical key, a number the agency’s CRM assigns to each row. Populated 43 of 43 (100 percent); distinct 41, because path P2 repeats the CRM row IDs of A-1001 and A-1002 in load 2. It identifies the CRM row, not the client: client A-1003 arrives in load 2 under a new CRM row ID (path P2). Reversal risk: none; the verdict follows from what the column identifies.

2.1.1.2 CLIENT_NUMBER

Field intent: business key, the number the agency uses to identify a client, designed as the letter A, a hyphen, and four digits. Populated: 42 of 43 non-null (97.7 percent); 40 of 43 carry a value after trimming (93.0 percent). Distinct raw values: 40. Discipline, read as conformance to the designed form among the 40 values: 35 conform exactly (87.5 percent, the mixed tier). The five nonconforming values are a-1004 and a-1010 (case), the trailing-space value A-1005 (surrounding space), and A1007 and A-01008 (internal form). Reversal risk: case and synonym variants (Standard: Profiling, section 4). Case and surrounding-space variants unify at hashing, because the toolkit trims and uppercases every business key before hashing. Internal-form variants do not unify and stay separate keys; whether they name the same client is an identity question this increment does not answer. Samples: A-1001, a-1004, A-1005 followed by a space, A1007, A-01008, an empty string, three spaces, NULL.

2.1.1.3 FIRST_NAME, 2.1.1.4 LAST_NAME

Field intent: genuinely free text, invented names. Populated 43 of 43; distinct up to 43. Not analyzed further: the descriptive columns wait for the satellites increment.

2.1.1.5 STATE

Field intent: controlled discrete, two-letter US state codes. Populated 43 of 43; clean tier by construction (every value is a valid code). Not analyzed further, for the same reason.

2.1.1.6 LOADED_AT

Field intent: the time the row arrived in the agency’s extract, delivered as text in the form YYYY-MM-DDTHH:MI:SS. Populated 43 of 43; distinct 2 (2026-10-01T02:00:00 and 2026-10-02T02:00:00), which are its minimum and maximum. Not near-unique: every row of a load carries that load’s one time. Parseability: 43 of 43 (100 percent). Missing-value semantics: none occur. The text type is deliberate: staging enforces the timestamp type, which is a hard business rule: Data Vault’s hard rules are technical changes such as type casting, formatting and null handling, while changes made for business requirements are soft rules, applied later (VaultSpeed, Data Vault 2.0 concepts, https://docs.vaultspeed.com/space/VPD/3011870744.md, read 2026-10-23).

2.1.2 Table SEED_SOURCE_SYSTEM (increment 1.1)

What it is. The source system seed: one row per source system the vault receives data from, giving each source’s permanent code its current name, description and owner. It is reference data authored by the team, kept in the repository as the dbt seed seed_source_system.csv (Ray, 2026-10-23). The vault rows carry only the code.

Grain. One row per code: three codes, SSI for Sunbelt Senior’s client file and SYSTEM and ERROR for the vault’s own ghost records (P-9). Specified duplicate count: 0. Check: grain-unique on SOURCE_SYSTEM_CODE.

History. The seed holds the source systems as they stand. Its history is the file’s history in git: every change is a reviewed commit, with who, when and why (Ray, 2026-10-23: a file the team edits is not data that arrives, so it needs no vault objects to keep its history). To show that a rename touches no vault row, the SSI row changes once, between two commits:

2.1.2.1 SOURCE_SYSTEM_CODE

Field intent: controlled discrete, the permanent identifier of a source system; short, upper case, never changed. Populated 3 of 3 (100 percent); distinct 3. Value discipline: clean by construction. Reversal risk: none; a code is assigned once.

2.1.2.2 SOURCE_NAME, 2.1.2.3 DESCRIPTION, 2.1.2.4 OWNER

Field intent: free text describing the source; allowed to change. Populated 3 of 3. For SSI each changes between the two commits, which is the rename written for the course; SYSTEM and ERROR do not change.

2.1.3 Table SUNBELT.POLICY_SALE (increment 2)

What it is. Sunbelt Senior’s record of the policies its agents sold: one row per person per policy, saying which Sunbelt Senior client holds which carrier’s policy and in what role. It is the second table of the same source system, so it carries the same record source code as the client file, SSI: one code per source system (Ray, 2026-10-23).

Grain. One row per policy, client and role. Specified duplicate count: 0. Check: grain-unique on POLICY_NUMBER, CLIENT_NUMBER and ROLE within a load.

Columns. SALE_ROW_ID (the source’s row number); POLICY_NUMBER (issued by the carrier, as the carrier writes it, for example NF-100003); CLIENT_NUMBER (Sunbelt Senior’s own, the same namespace as the client file); CARRIER_CODE (the carrier that issued the policy, NFL for Northfield Life, because a policy number is unique only within its issuing carrier’s namespace; Ray, 2026-10-23); ROLE (owner or insured, the documented values; act 2 also carries one undocumented value, payor, path L5); SOLD_ON (date, as text); AGENT_NAME; AGENT_NPN (the selling agent’s national producer number, assigned through the National Insurance Producer Registry and the same in every source); PLAN_NAME (Sunbelt Senior’s name for the product); LOADED_AT (text, as in the client file).

Population. Act 1 (2026-10-01): 9 rows. Act 2 (2026-10-02): 5 rows. Every row exercises at least one path (P-16).

History measures. Freshness: the latest load is 2026-10-02 02:00 UTC; the lag behind the source cannot be measured, because the extract carries no source change time. Volume: 9 rows, then 5. Change rate: one sale (NF-100001, A-1001, owner) arrives in both loads, unchanged (0 of 1). Disappearance does not apply to a delta extract.

The base population for every percentage below is the table’s 14 rows.

2.1.3.1 SALE_ROW_ID

Field intent: technical key, the source’s row number. Populated 14 of 14 (100 percent); distinct 14. It identifies a row, not a sale. Reversal risk: none.

2.1.3.2 POLICY_NUMBER

Field intent: business key, issued by the carrier, designed as NF, a hyphen and six digits. Populated 13 of 14 (92.9 percent): one row carries an empty string (path PH7). Distinct raw values: 10. Discipline, read as conformance to the designed form among the 13 values: 11 conform (84.6 percent, the mixed tier); the two nonconforming values are nf-100001 (case) and NF100009 (internal form). Reversal risk: case and synonym variants. The case variant unifies at hashing; the internal-form variant stays a separate key and is disclosed by an inspector. Samples: NF-100001, nf-100001, NF100009, NF-100012, an empty string.

2.1.3.3 CLIENT_NUMBER

Field intent: business key, Sunbelt Senior’s client number, the same namespace and form as the client list’s. Populated 13 of 14 (92.9 percent): one row carries an empty string (path CH3). Distinct 10. Discipline: 13 of 13 conform to the designed form (100 percent, clean). Overlap with the client list: 9 of the 10 numbers are in it; A-1033 is first seen here (path CH2). Reversal risk: none in this data. Samples: A-1001, A-1008, A-1033, an empty string.

2.1.3.4 CARRIER_CODE

Field intent: controlled discrete, the code of the carrier that issued the policy. Populated 12 of 14 (85.7 percent): one row has no code (path PH8) and one has neither code nor policy number (path PH7). Distinct 1, NFL; clean. Reversal risk: a single-value column. It is integrated anyway, as part of the policy’s key, because a policy number is unique only within its carrier; the single value reflects this increment’s one carrier, not the column’s meaning. Samples: NFL, NULL.

2.1.3.5 ROLE

Field intent: controlled discrete, what the client is on the policy; the source documents owner and insured. Populated 14 of 14; distinct 3: owner 10 rows (71.4 percent), insured 3 (21.4 percent), payor 1 (7.1 percent). Discipline: 13 of 14 in the documented set (92.9 percent, predominantly controlled). The one other value, payor, is a role the source added without documenting it (path L5); it is kept exactly as sent, because a role code is data, not spillover. Reversal risk: none from the catalog; the undocumented code is the case written for it.

2.1.3.6 SOLD_ON, 2.1.3.7 AGENT_NAME, 2.1.3.8 PLAN_NAME

Field intent: SOLD_ON is a date delivered as text; AGENT_NAME is free text, a person’s name; PLAN_NAME is free text, Sunbelt Senior’s name for the product. Populated 14 of 14 each. SOLD_ON parses for 14 of 14 and falls on or before its row’s load date; earliest 2026-09-02, latest 2026-09-28; 10 distinct dates in 14 rows, so dates repeat and are not near-unique; no NULL occurs, and a NULL would mean the sale date was not sent. Values come from the implementation guide’s fixed invented lists. Reversal risk: none.

2.1.3.9 AGENT_NPN

Field intent: business key of the agent, a national producer number: up to ten digits, no leading zeros, assigned through the National Insurance Producer Registry, so the same agent carries the same number in every source. Populated 13 of 14 (92.9 percent): the row of NF-100014 carries an empty string (path AG5). Distinct 4; 13 of 13 conform (clean). Overlap with Northfield Life’s WRITING_AGENT_NPN: 17001001 and 17001006 appear in both; on NF-100011 the two sources name different agents (path LA2). Reversal risk: none. Samples: 17001001, 17001007, an empty string.

2.1.3.10 LOADED_AT

Field intent: the time the row arrived, as text in the form YYYY-MM-DDTHH:MI:SS. Populated 14 of 14; distinct 2 (2026-10-01T02:00:00 and 2026-10-02T02:00:00), which are its minimum and maximum. Parseability 14 of 14. Not near-unique: every row of a load carries that load’s one time. No NULL occurs; a NULL would mean a row with no load.

2.1.4 Table NORTHFIELD.POLICY (increment 2)

What it is. Northfield Life’s policy data, as a carrier sends it to the distributor: one row per policy, with the insured person described the carrier’s way. Northfield Life is a carrier invented for the course. It is a new source system with its own record source code, NFL, added to the source system seed (Ray, 2026-10-23).

Grain. One row per policy. Specified duplicate count: 0. Check: grain-unique on POLICY_NUMBER within a load.

Columns. POLICY_NUMBER (Northfield Life issues it: NF-, a hyphen, six digits); INSURED_ID (Northfield Life’s own number for the insured person: NLI-, a hyphen, five digits); INSURED_NAME (one field, “Last, First”); BIRTH_DATE; ADDRESS (one field); FACE_AMOUNT (the death benefit, in dollars); STATUS (Northfield Life’s codes: IF in force, LP lapsed, TR terminated); EFFECTIVE_DATE; WRITING_AGENT_NPN (the selling agent’s national producer number); LOADED_AT.

Population. Act 1 (2026-10-01): 9 policies. Act 2 (2026-10-02): 3 rows: one unchanged resend, one status change and one change in letter case only (P-15).

History measures. Freshness: the latest load is 2026-10-02 02:00 UTC; the lag behind the source cannot be measured, because the extract carries no source change time. Volume: 9 rows, then 3. Change rate: 3 policies arrive in both loads (NF-100002, NF-100003, NF-100008), and 2 of them changed (67 percent). Disappearance does not apply to a delta extract.

The base population for every percentage below is the table’s 12 rows.

2.1.4.1 POLICY_NUMBER

Field intent: business key, issued by Northfield Life, NF, a hyphen and six digits. Populated 12 of 12; distinct 9; 12 of 12 conform (clean). Reversal risk: none. Samples: NF-100001, NF-100013.

2.1.4.2 INSURED_ID

Field intent: business key, Northfield Life’s number for the insured person, NLI, a hyphen and five digits. Populated 11 of 12 (91.7 percent): the row of NF-100013 carries an empty string (path IH4). Distinct 7; 11 of 11 conform (clean). NLI-55001 is the insured on two policies (path IH3). Reversal risk: none. Samples: NLI-55001, NLI-55007, an empty string.

2.1.4.3 INSURED_NAME, 2.1.4.4 BIRTH_DATE, 2.1.4.5 ADDRESS

Field intent: INSURED_NAME is free text in the form Last, First; BIRTH_DATE is a date as text; ADDRESS is free text in one field. Populated 12 of 12 each; BIRTH_DATE parses for 12 of 12; earliest 1947-01-30, latest 1955-11-23; 8 distinct in 12 rows, not near-unique; no NULL occurs, and a NULL would mean the carrier did not send the birth date. ADDRESS carries one case variant on purpose: NF-100008’s address is 12 Elm St in act 1 and 12 ELM ST in act 2 (path ST4). Reversal risk: case variants. Treatment: kept as sent; a change in letter case alone is a new satellite version, because change hashes are case-sensitive. Samples: Alvarez, Rosa; Garrity, Irene; 12 Elm St; 12 ELM ST.

2.1.4.6 FACE_AMOUNT, 2.1.4.7 EFFECTIVE_DATE

Field intent: FACE_AMOUNT is a number of dollars, the death benefit; EFFECTIVE_DATE is a date as text. Populated 12 of 12 each; both parse for 12 of 12; no zero amounts. Values come from the implementation guide’s fixed invented lists. Reversal risk: none.

FACE_AMOUNT, in dollars, over the 12 rows (4 distinct values):

Mean 13,750; standard deviation 4,826.54; zero count 0; parseability 12 of 12. The spread is a small set of round death benefits, as a senior final-expense and term book would show; no unit ambiguity or outlier.

EFFECTIVE_DATE: earliest 2026-05-15, latest 2026-09-25; 9 distinct in 12 rows, not near-unique; no NULL occurs, and a NULL would mean the policy has no effective date yet.

2.1.4.8 STATUS

Field intent: controlled discrete, Northfield Life’s codes IF (in force), LP (lapsed) and TR (terminated). Populated 12 of 12; distinct 2 in this data: IF 11 rows, LP 1 (NF-100003 in act 2, path ST3). Clean. Reversal risk: none.

2.1.4.9 WRITING_AGENT_NPN

Field intent: business key of the agent who wrote the policy, the same national producer number as the sales data’s AGENT_NPN. Populated 12 of 12; distinct 4; 12 of 12 conform (clean). 17002001 is an independent agent Sunbelt Senior’s data never names (path AG2). Reversal risk: none once the number keys its own hub (P-18). Samples: 17001001, 17002001, 17001005.

2.1.4.10 LOADED_AT

As in 2.1.3.10: populated 12 of 12; distinct 2, the same two load times as minimum and maximum; parseability 12 of 12; not near-unique, one time per load; no NULL occurs.

The two sources’ data are shaped differently on purpose. Sunbelt Senior names a person in two fields under its own number; Northfield Life names the same person in one field under its own number, inline in the policy row. The paths below decide what the vault can and cannot match.

2.1.5 Table PINECREST.POLICY (Ray’s labs, from Lab 1-2)

What it is. Pinecrest Mutual’s policy data, as a carrier sends it to the distributor: one row per policy. Pinecrest Mutual is a carrier invented for the course, shown in the course guide’s figure A2-1, and a new source system with its own record source code, PMC. Its rows are written for Ray’s labs, one group for each of lessons 2 to 7 (result P-20).

Grain. One row per policy. Specified duplicate count: 0. Check: grain-unique on POLICY_NUMBER within a load, rows with no usable number excepted.

Columns. POLICY_NUMBER (Pinecrest Mutual issues it: PM, a hyphen, six digits); PLAN_NAME; FACE_AMOUNT (the death benefit in dollars, sent as text); STATUS (IF in force, LP lapsed); EFFECTIVE_DATE (a date, as text); WRITING_AGENT_NPN (the selling agent’s national producer number); LOADED_AT.

Population. Act 1 (2026-10-01): 8 rows. Act 2 (2026-10-02): 4 rows: one unchanged resend, one status change, one with no usable number and one new policy (P-20).

History measures. Freshness: the latest load is 2026-10-02 02:00 UTC; the lag behind the source cannot be measured, because the extract carries no source change time. Volume: 8 rows, then 4. Change rate: 2 policies arrive in both loads (PM-300001, PM-300002), and 1 of them changed (50 percent). Disappearance does not apply to a delta extract.

The base population for every percentage below is the table’s 12 rows.

2.1.5.1 POLICY_NUMBER

Field intent: business key, issued by Pinecrest Mutual, PM, a hyphen and six digits. Populated with a usable number 10 of 12 (one empty, one only spaces); distinct usable numbers after trimming and capitals, 8; conforming to the form, 9 of 10 (PM300006 has no hyphen). Lower case (pm-300003) and a trailing space (PM-300004 followed by a space) are hard-rule variants that hash to the conforming key. Reversal risk: none; the nonconforming form is disclosed by an inspector (P-20, path PM5). Samples: PM-300001, PM-300009.

2.1.5.2 PLAN_NAME, 2.1.5.3 STATUS

Field intent: PLAN_NAME is free text naming Pinecrest Mutual’s product; STATUS is controlled discrete, IF or LP. Populated 12 of 12 each. Distinct plan names 3; STATUS: IF 11 rows, LP 1 (PM-300002 in act 2, path PM7). Clean. Reversal risk: none.

2.1.5.4 FACE_AMOUNT

Field intent: a number of dollars, sent as text. Populated 12 of 12; parses as a number for 12 of 12; no zero amounts; 8 distinct values. The cast from text to a number is a hard rule (P-20, path PM8).

Mean 14,750; standard deviation 6,047.16; zero count 0. Reversal risk: none.

2.1.5.5 EFFECTIVE_DATE

Field intent: a date as text. Populated 12 of 12; parses for 12 of 12; earliest 2026-08-12, latest 2026-09-30; 10 distinct in 12 rows, not near-unique (a resent policy repeats its date); no NULL occurs, and a NULL would mean the policy has no effective date yet. Reversal risk: none.

2.1.5.6 WRITING_AGENT_NPN

Field intent: the selling agent’s national producer number, the key agent_h already uses. Populated 12 of 12; distinct 6; 12 of 12 conform. Landed but not used by Labs 1-2 to 1-7, which stay with the policy; a later lab may feed agent_h from it. Reversal risk: none.

2.1.5.7 LOADED_AT

As in 2.1.4.10: populated 12 of 12; distinct 2, the two load times, which are its minimum and maximum; parseability 12 of 12; not near-unique, one time per load; no NULL occurs.

2.2 Overlap with existing sources

Increment 2’s two new source tables overlap on purpose, and only as far as the paths need: they share policies (paths PH3, PH4, PH5), differ in one written form (PH6) and in one missing carrier code (PH8), and each holds policies the other does not (PH1, PH2). One person appears under both issuers’ unrelated numbers (L4). Agents are the opposite case: both tables carry the national producer number, so an agent sold through both sources matches (AG1), while the two sources disagree about one policy’s agent (LA2). A second acquired agency arrives in a later increment.

Join path between the three tables, measured on the landed rows with blank keys excluded:

2.3 Machine-assisted analyses

Not applicable: no column in scope is free text or degraded discrete with more than 50 distinct values, and the data is synthetic, so no panel trigger applies.

3. Profile results

Numbered P-1, P-2, … in one series. An anomaly carries SOLUTION NEEDED and closes with RESOLVED, the resolver, the date, and the solution, in that order.

P-1. The eight paths and their rows. The Permutation Coverage Method enumerated five input dimensions (client number presence; form relative to an existing number; arrival across loads; technical key across loads; rows per key within a load): 4 x 4 x 3 x 2 x 2 = 192 combinations, collapsing to 8 distinct behaviors of the staging model and the hub.

Totals: 43 rows; expected hub rows 34 (A-1001 to A-1032 and the two internal-form keys) plus the two ghost records. Check: hub row count equals 36 after the build, and distinct non-ghost keys equal 34.

P-2. Case and surrounding-space variants unify; internal-form variants do not. Rows: a-1004, A-1005 with a trailing space and a-1010 resolve to existing keys; A1007 and A-01008 become new keys. Basis: the toolkit’s hash pipeline (CAST, TRIM, UPPER, removal of line breaks and tabs), read in its code on 2026-10-22. Check: the P4, P5 and P8 unit tests.

P-3. Internal-form variants leave a possible duplicate client. SOLUTION NEEDED. A1007 may be client A-1007 and A-01008 may be A-1008; the vault keeps both as separate keys and cannot tell. This is deliberate and stays open: resolving identity is integration work that later increments address. Disclosure until then: an inspector writes one finding for each client number that does not match the designed form. Check: the inspector writes two findings for this data, one for A1007 and one for A-01008.

P-4. Empty and whitespace-only client numbers would hash as a real value. SOLUTION NEEDED. Inferred from the hash code: only NULL maps to the missing-value marker, so an empty string after trimming is hashed as a value and would create a hub row whose business key is empty. RESOLVED by Ray Serrano, 2026-10-22: a staging hard rule turns empty and whitespace-only client numbers into NULL, so they take the all-zeros key. Confirmation pending: the P7 characterization unit test is written and run first, to show the current behavior before the rule is coded.

P-5. A NULL client number takes the all-zeros key and lands on the unknown ghost record. Check: the P6 unit test; after the build, no hub row has a NULL business key other than the ghost records.

P-6. A changed technical key does not change identity. A-1003 arrives in load 2 under a new CRM row ID and still resolves to the same hub key. Check: the P2 unit test.

P-7. LOADED_AT parses for every row. 43 of 43 values cast to a timestamp. Check: parse count equals populated count.

P-8. The rename (increment 1.1). Code SSI keeps its code while its name, description and owner change between two commits of the seed. Expected: the seed’s git history holds both versions; no client-file or hub row changes. Check: the seed’s git log shows the change; every client hub row still carries SSI.

P-9. The ghost records carry record sources that are not source systems. RESOLVED by Ray Serrano, 2026-10-23: add them to the seed. The toolkit gives its unknown ghost record the record source SYSTEM and its error ghost record ERROR (its variables default_unknown_rsrc and default_error_rsrc, not overridden by this project). Neither is a source system, so a join from vault rows to the seed would find no name for them, and every consumer naming sources would need a special case. The seed therefore holds two more rows, describing the vault itself: SYSTEM (the unknown ghost record) and ERROR (the error ghost record), with the vault’s team as owner. Check: every record source value in the vault resolves to a seed code.

P-10. Every client-file row’s record source becomes the code SSI (increment 1.1). Today the staging view stamps the name SUNBELT.CLIENT; after increment 1.1 it stamps the code SSI, and the name lives in the source system seed. The workstation’s development vault is rebuilt with dbt’s scoped full refresh (Ray, 2026-10-23), never by dropping schemas. Check: after the build, every hub row from the client file carries SSI, and none carries SUNBELT.CLIENT.

P-11. Increment 2’s paths, by the reverse use of the Permutation Coverage Method (Ray, 2026-10-24). The dimensions of the two new source tables were enumerated against the models that read them and collapsed to 33 distinct behavioral paths. Each path has its own row set; a row set carries several paths only where one real instance naturally does. Results P-12 to P-15 and P-19 list the paths by model; P-16 lists the rows.

P-12. Policy hub paths. By source and load:

By key form:

P-13. Role hub paths. Each number identifies one instance of a role at its source’s grain: a Sunbelt Senior client, a Northfield Life insured. The person behind the roles, and any matching between them, is built later, outside the raw vault. Sunbelt Senior’s client hub, now also read from the sales data:

Northfield Life’s insured hub:

P-14. Role-to-policy relationship paths. Roles, from Sunbelt Senior’s sales data:

Identity and resend:

P-15. Satellite paths.

P-16. The rows written for the course. Descriptive values not named here (birth dates, amounts, agents, plans) come from fixed invented lists in the implementation guide.

Agent numbers. AGENT_NPN in the sales data: 17001001 on NF-100001 (both rows and the act 2 resends) and NF-100007; 17001006 on NF-100005, NF-100006, NF-100010, NF100009 and the sale with no policy number; 17001007 on NF-100012; 17001004 on NF-100011; an empty string on NF-100014. WRITING_AGENT_NPN in the carrier data: 17001001 on NF-100001 and NF-100007; 17001006 on NF-100005 and NF-100009; 17001005 on NF-100011; 17002001 on NF-100002, NF-100003, NF-100008 and NF-100013, in both acts where resent.

Policies sold through Sunbelt Senior that the carrier has not sent (NF-100006, NF-100010, NF-100012, NF-100014) also follow path PH1.

P-17. Questions for the design (increment 2). (1) What Sunbelt Senior’s client table holds: RESOLVED by Ray Serrano, 2026-10-24 (answer 2b): its client list, one row per client, so its grain is a role. (2) A missing person number: RESOLVED by Ray Serrano, 2026-10-24 (answers 1a and the role hubs): each role hub is keyed by its number alone, so a missing number takes the unknown ghost record, as in increment 1. (3) Open, for the design: how the two role hubs join the policy hub (one relationship per role hub, or another placement), and the hubs’ names.

P-18. The writing agent’s number is a business key carried among descriptions (increment 2). RESOLVED, Ray Serrano, 2026-10-24: an agent hub keyed by the national producer number, fed by both tables, and a link from the agent to the policy; the sales data carries the agent’s number as AGENT_NPN, and the agent’s name stays a description of the sale (paths in P-19). WRITING_AGENT_NPN identifies an agent, a concept of its own, yet the design places it in the policy’s satellite as a description. In a Data Vault a business key belongs in a hub, with the relationship in a link; kept in a satellite, the agent cannot be joined across sources. Open for the design: an agent hub and a policy-agent link, or the satellite placement with its reason stated.

P-19. Agent paths (increment 2). The national producer number is assigned through the National Insurance Producer Registry, so it is the one key in this increment two sources share. The agent hub:

The link from the agent to the policy:

P-20. Pinecrest Mutual’s rows (Ray’s labs, from Lab 1-2). Twelve rows over the same two acts, written so each lab exercises its lesson. Descriptive values are invented.

P-21. The carriers’ organization records (design , D-11). [C1] Two new landing tables, NORTHFIELD.ORGANIZATION and PINECREST.ORGANIZATION, each with the text columns CARRIER_CODE, CARRIER_NAME and LOADED_AT: one row per organization record per load. Each carrier sends its own organization record, its code and its name, through its feed.

The name change of path OT3 lives only in its unit test’s rows, not in these.

4. Cleansing advice for source administration

Not applicable: the source is synthetic and has no administrators. Its defects are scenarios written for the course, listed in P-1.

5. Integration actions

1. Integrate CLIENT_NUMBER as the client’s business key, with two treatments: case and surrounding-space variants unify through the toolkit’s hashing, and empty or whitespace-only values become NULL through a staging hard rule (P-4).
2. Integrate LOADED_AT as the load timestamp, cast in staging to a timestamp without time zone that means UTC (the increment’s timestamp convention).
3. Record the source of every row as the static value SUNBELT.CLIENT.
4. Disclose internal-form variants with an inspector (P-3) instead of correcting them.
5. Defer the descriptive columns to the satellites increment.
6. Integrate the source system seed (increment 1.1): SOURCE_SYSTEM_CODE as the permanent code every vault row carries; SOURCE_NAME, DESCRIPTION and OWNER as its current description, with history in git.
7. Stamp every client-file row with the record source code SSI instead of a name (P-10).
8. Integrate increment 2’s source data as the paths of P-11 to P-16 require: policy numbers, qualified by the issuing carrier’s code, as the policy hub’s key; each source’s person number as the key of its own role hub, at its source’s grain: Sunbelt Senior’s client number for its clients, Northfield Life’s insured number for its insureds (Ray, 2026-10-24, answers 2b and 1a, returning to the decision of 2026-10-23); each role’s relationship to the policy, with the role; the agent’s national producer number, from both tables, as the key of an agent hub, with the agent’s relationship to the policy; one satellite per source. P-17 states what the design still places. Placement (which hub, which role) is the design’s job and is not decided here.

6. Verdict summary

Each verdict names its check.

6.1 Integrate

Sunbelt Senior’s policy sales table (SUNBELT.POLICY_SALE):

Northfield Life’s policy table (NORTHFIELD.POLICY):

The agent, in both tables:

6.2 Defer

6.3 Reject

Appendix — Column inventory

Compliance record

Built by the author against the profiling template and Standard: Profiling, at 2026-10-25 08:28Z, on the text above. Verdict: compliant, with one anomaly, P-3, open by design. The three analyses this record first found missing were added on 2026-10-25 (Ray, answer 1a). The Lab 1-2 extension (Pinecrest Mutual, written 2026-10-25 17:24Z) carries every analysis the standard requires for its table: grain with duplicate count, history measures, the number’s form and variants, FACE_AMOUNT’s distribution table with mean and standard deviation, EFFECTIVE_DATE’s range and near-uniqueness, LOADED_AT, the join path hop, verdicts with checks, and seven appendix rows. Its descriptive columns and the agent’s number are deferred with their unblocking conditions.

Template and deliverable structure

Required analyses (section 5 of the standard)

Results and wording (section 6 of the standard)