Branch Design: Sunbelt Senior Insurance Client Hub and Source System Seed (Arrival and Identity)
Design Document
| Effort | Linear DIG-574; DIGEST Data Vault Implementation, increments 1 (arrival and identity), 1.1 (source system seed) and 2 (the two-source raw vault), elaborating this single document set; branch claude_ws/dig-574-arrival-and-identity; the fix to release 1, keys and missing keys (Linear DIG-594), branch claude_ws/dig-594-release-1-keys-rebased-2 |
| Governed by | Standard: Branch Delivery Documents (K#12 I#27) |
| References | Profiling: Branch Profile: Sunbelt Senior Insurance and Northfield Life Source Data (Scope Statement), Pilot item 267; related records: the course’s raw vault naming rule (course record, Pilot knowledge 34); the implementation record (Pilot knowledge 35); the course record (Pilot knowledge 34) with the candidate pattern on issuer-qualified business keys (item 268) and the insurance lexicon (item 269); the mart design, , which carries Mary’s questions Q-9 to Q-15 |
| Audience | Ray Serrano as reviewer and learner; the implementer of the increment’s guide; a reader new to Data Vault returning months later |
| Author | Ray Serrano; edited by Claude — 2026-10-22 |
| Classification | Internal — Data Management |
| Official copy method | Copied from this governed template by AGENTIC.DATASKILLS.KNOWLEDGE_ITEM_FROM_TEMPLATE; template SHA-256 recorded in the created item |
0. Target ERD
The vault this design builds, drawn from the UDM map (section 6.1) and from the profile’s source ERD: four hubs, three links and four satellites, in plain names with each table’s type in parentheses, and Pinecrest Mutual’s policy details, Lab 1-2’s extension (D-37 to D-39), dashed. Each link row and each satellite row belongs to exactly one hub row or link row; a hub row has zero or more of each, and the zero is real: the client hub holds A-1033, which arrived only in the sales data and has no client details. The dated views (the _s1 views) are left out.
Every line is drawn solid because the hash keys are computed from the same business keys on both sides: read on 2026-10-26, none of the three links’ 45 rows and none of the four satellites’ rows lacks its parent. Every line is checked by a relationships test, the links’ lines by D-42’s six and the satellites’ by D-40’s four; the layer 4 assertion assert_increment_2_link_hub_keys_exist checks the links’ lines a second way, and D-43 declares every line as a foreign key in the warehouse.
1. Purpose
Build the first piece of a classic Data Vault for the DIGEST Data Vault Course: Sunbelt Senior’s client file lands in the LANDING database, a staging model prepares it, and a client hub records every client number Sunbelt Senior has sent, with the time it first arrived and where it came from.
The increment teaches arrival and identity: what a source table, a load timestamp and a record source are; what staging may and may not change; how a business key differs from a technical key; how a business key becomes a hash key; and what a hub and its ghost records hold. The data is synthetic and specified by the agreed profile, whose eight scenarios are the eight behavioral paths of the staging model and the hub.
Increment 1.1 gives every vault row a permanent record source code instead of a name, and keeps each code’s name, description and owner in one source system seed. A rename then changes one row of the seed, recorded in git, and no vault row. A three-row table the team edits needs no vault objects to keep its history.
Increment 2 builds the first two-source raw vault. Sunbelt Senior’s policy sales data and Northfield Life’s policy data land beside Sunbelt Senior’s client list, and the vault records each source’s roles at its own grain, the policies, each role’s relationship to a policy, and the agents who sold and wrote the policies, with descriptive history. The two sources meet at the policy, matched on the carrier’s number, while one person stays under two unrelated numbers, one per source: A-1007 at Sunbelt Senior and NLI-55007 at Northfield Life.
2. Consumer questions
2.1 Questions from consumers
| ID | Asked by | Asked at | Origin | Question in the asker’s words | Answer | Decision(s) | Test probe | State and reason |
|---|---|---|---|---|---|---|---|---|
| Q-1 | Ray Serrano | 2026-10-22 | Course increment 1 | Which clients has Sunbelt Senior sent, and when did each first arrive? | The client hub answers it: one row per client key with its first load timestamp and record source | D-3, D-5 | Probe Q-1 | Answered: probe Q-1 on 2026-10-25 returns 35 client keys under SSI, A-1031, A-1032, A1007, A-01008 and A-1033 at act 2 |
| Q-2 | Ray Serrano | 2026-10-22 | Course increment 1 | Which client numbers might name the same client? | The hub cannot answer it; the client-number form inspector lists the candidates, one finding each (A1007, A-01008) | D-9 | Probe Q-2 | Answered: probe Q-2 on 2026-10-25 returns the two findings, A1007 and A-01008 |
| Q-3 | Ray Serrano | 2026-10-22 | Course increment 1 | How many source rows became how many client keys, and why do the counts differ? | 43 rows become 34 keys plus two ghost records; the reconciliation accounts for every row | D-10 | Probe Q-3 | Answered: on 2026-10-25 the client list’s 43 rows give 34 keys and the current auditor writes no finding; the two findings of its first version stay disclosed |
| Q-4 | Ray Serrano | 2026-10-23 | Increment 1.1 | Where did this row come from, by name, now and when it arrived? | Join the row’s record source code to the source system seed for the current name; the seed’s git history gives any earlier name | D-15, D-21 | Probe Q-4 | Answered: probe Q-4 on 2026-10-25 resolves every hub and link row to a named source system |
| Q-5 | Ray Serrano | 2026-10-24 | Increment 2 | Do we know if these are paying customers, beneficiaries, or something else? | Sunbelt Senior’s source documentation states that its people table is its client list, so each client number identifies a client of Sunbelt Senior. The sales data says, for each policy, what each client is on it: owner, insured, or any other code the source sends. No source in scope sends beneficiaries | D-32, D-33 | Probe Q-5 | Answered: probe Q-5 on 2026-10-25 returns each client’s role on each policy, owner, insured and payor, A-1010 holding two roles on NF-100010 |
| Q-6 | Ray Serrano | 2026-10-23 | DIG-574 | we need to be ready for when Mary calls. | Partially: this increment builds the two-source raw vault the conversation rests on, where the sources meet at the policy and one person stays under two unrelated numbers; the lessons and the milestone diagram that complete the readiness are course work after the build | D-26, D-32, D-33, D-34 | Probe Q-6 | Partially answered: lessons and diagram follow the build |
| Q-7 | Mary Mink (predicted by Ray and Claude) | 2026-10-25 | DIG-574; the Data Consumption Design standard’s elicitation principle: a prediction of her initial questions, ratified as such (Ray, answer 4a) | How many distinct people are our clients, across every agency we’ve acquired? | Needs the integration layer: matching the person behind role keys and across agencies (DD-5) | None yet | None yet: a probe is written by the branch that answers it | Later: recorded before any branch serves Mary; a release answers each once it ships |
| Q-8 | Mary Mink (predicted by Ray and Claude) | 2026-10-25 | DIG-574; the Data Consumption Design standard’s elicitation principle: a prediction of her initial questions, ratified as such (Ray, answer 4a) | Which clients came to us through more than one agency? | Needs a second acquired agency and the integration layer (DD-1, DD-5) | None yet | None yet: a probe is written by the branch that answers it | Later: recorded before any branch serves Mary; a release answers each once it ships |
| Q-9 | Mary Mink (predicted by Ray and Claude) | 2026-10-25 | DIG-574; the Data Consumption Design standard’s elicitation principle: a prediction of her initial questions, ratified as such (Ray, answer 4a) | Which policies does each client hold, with which carriers, in which roles (owner, insured, payor)? | The client-policy-role fact, grouped by client, carrier, policy and role | mart design D-1, D-3 | P-9, test plan 2.3.4 | Answered: release 1 serves it; the mart design rebuilds it in CONFORMED_DM |
| Q-10 | Mary Mink (predicted by Ray and Claude) | 2026-10-25 | DIG-574; the Data Consumption Design standard’s elicitation principle: a prediction of her initial questions, ratified as such (Ray, answer 4a) | How much coverage does each carrier provide through us? | The current face amount of the policy-terms fact by carrier; only Northfield Life sends amounts today | mart design D-1, D-4 | P-10, test plan 2.3.4 | Answered: release 1 serves it for Northfield Life, the one carrier that sends amounts; the mart design rebuilds it |
| Q-11 | Mary Mink (predicted by Ray and Claude) | 2026-10-25 | DIG-574; the Data Consumption Design standard’s elicitation principle: a prediction of her initial questions, ratified as such (Ray, answer 4a) | Which agent sold each policy, and where do our sources disagree about it? | The agent-policy fact: the agents named per policy; a policy with two or more agents named is where the sources disagree | mart design D-1, D-8 | P-11, test plan 2.3.4 | Answered: release 1 serves it; the mart design rebuilds it |
| Q-12 | Mary Mink (predicted by Ray and Claude) | 2026-10-25 | DIG-574; the Data Consumption Design standard’s elicitation principle: a prediction of her initial questions, ratified as such (Ray, answer 4a) | Which independent agents sell through us without an agency? | Needs the rule for what makes an agent independent (mart design C-1) | mart design DD-1 | None yet | Later: deferred by the mart design until C-1 is answered |
| Q-13 | Mary Mink (predicted by Ray and Claude) | 2026-10-25 | DIG-574; the Data Consumption Design standard’s elicitation principle: a prediction of her initial questions, ratified as such (Ray, answer 4a) | Where did this client’s record come from, and when did we first know it? | The client-policy-role fact’s metrics grouped by the client’s source and first-known date | mart design D-1, D-12 | P-13, test plan 2.3.4 | Later: served by this release’s conformed mart |
| Q-14 | Mary Mink (predicted by Ray and Claude) | 2026-10-25 | DIG-574; the Data Consumption Design standard’s elicitation principle: a prediction of her initial questions, ratified as such (Ray, answer 4a) | What did we know about this policy on a given date, and what changed since? | The policy-terms fact grouped by policy and by each version’s valid-from and valid-to days | mart design D-1, D-4 | P-14, test plan 2.3.4 | Later: served by this release’s conformed mart |
| Q-15 | Mary Mink (predicted by Ray and Claude) | 2026-10-25 | DIG-574; the Data Consumption Design standard’s elicitation principle: a prediction of her initial questions, ratified as such (Ray, answer 4a) | How many records arrive in a form we don’t accept, from which source? | Release 1 answers it with records_not_accepted, the form findings (D-47); the mart design rebuilds it in CONFORMED_DM | D-47; mart design D-1, D-5 | P-15, test plan 2.3.4 | Answered: release 1 serves it |
| Q-16 | Mary Mink (predicted by Ray and Claude) | 2026-10-25 | DIG-574; the Data Consumption Design standard’s elicitation principle: a prediction of her initial questions, ratified as such (Ray, answer 4a) | When we acquire the next agency, how much of its book already overlaps ours? | Needs a second acquired agency and the integration layer (DD-1, DD-5) | None yet | None yet: a probe is written by the branch that answers it | Later: recorded before any branch serves Mary; a release answers each once it ships |
2.2 Questions to consumers
| ID | Asked of | Raised by and when | Question | Decision blocked | Interim rule | Measured stakes and clock | State or answer |
|---|---|---|---|---|---|---|---|
| none | No question to a consumer is open |
2.3 Questions to source owners
| ID | Asked of | Raised by and when | Question | Decision blocked | Interim rule | Measured stakes and clock | State or answer |
|---|---|---|---|---|---|---|---|
| none | Not applicable: the source is synthetic and has no owner |
3. Concept
A Data Vault requires every row to carry its record source and load date, so values can be traced back to the source (Wikipedia, Data vault modeling, https://en.wikipedia.org/wiki/Data_vault_modeling). Linstedt and Olschimke define hard business rules as never changing the meaning of incoming data, only the way it is stored, and list the consolidation of data from multiple sources among the soft business rules (Building a Scalable Data Warehouse with Data Vault 2.0, section 2.2, read from Ray’s copy on 2026-10-22). So the vault does not consolidate data from different sources; sources meet in one place only: a hub holds one row per business key, shared by every source that carries it (Snowflake, Data Vault techniques: hub tables are always multi-source and unique by business object, https://snowflake.com/blog/data-vault-techinques-hub-locking). Staging applies only hard business rules, such as type casting, formatting and null handling; rules that change data according to business requirements are soft business rules, applied in the business vault (VaultSpeed, Data Vault 2.0 concepts, https://docs.vaultspeed.com/space/VPD/3011870744.md; GFT, Data Vault 2.0, https://www.gft.com/pl/en/blog/data-vault-2-0-jako-nowa-metodyka-projektowania-i-implementowania-hurtowni-danych). All read 2026-10-23. Increment 1 builds the vault’s first two pieces; increment 2 adds the policy, a second role, and the relationships and descriptions.
Staging takes one landed source table and prepares it without changing its meaning. It enforces types (the load timestamp arrives as text and becomes a timestamp), fixes the record source, and computes the hash key. These are hard rules: they change how a value is stored, never what it means.
The hub is the list of business keys of one concept, here Sunbelt Senior’s client numbers. Each row holds the hash key, the business key, the load timestamp of the key’s first arrival and the record source of that arrival. Later sightings of a key add nothing. Two ghost records, one for an unknown key and one for an error, complete later equi-joins: a point-in-time table references a satellite’s ghost record until a real record exists (Snowflake, point-in-time constructs and join trees, https://www.snowflake.com/en/blog/point-in-time-constructs-and-join-trees, read 2026-10-23); the toolkit creates them in staging, with the record sources SYSTEM and ERROR by default (datavault4dbt’s global variables default_unknown_rsrc and default_error_rsrc).
The hub records identifiers, not resolved people. If one person reached Sunbelt Senior under two client numbers, the hub holds two rows and cannot tell; that is identity work for a later increment.
Roles at their sources’ grain. A source’s number identifies what the source keeps one row for. Sunbelt Senior’s client list keeps one row per client, so A-1007 identifies a client: a role, not a person. Northfield Life’s NLI-55007 identifies an insured. Each role gets its own hub, keyed by its own number; the person behind the roles is in no source, and matching it belongs after the raw vault.
Links and satellites. A link records a relationship between hub keys at the grain its source sends it: the sales data relates a client to a policy in a role, and the carrier data relates an insured to a policy. A satellite holds one source’s descriptive columns for one hub or link, with a new version whenever they change.
A key an outside issuer gave. The vault matches sources only where they carry the same key. A client number and an insured number are each issued by one company, so they never match. An agent’s national producer number is assigned through the National Insurance Producer Registry and is the same in every source, so the agent hub matches an agent sold through both sources without any rule.
4. Data reality
| Profile result | Measured fact | Design consequence |
|---|---|---|
| P-1 | 43 rows over two loads plant eight behavioral paths; expected 34 client keys plus two ghost records | The hub’s row count after the build is a check (36 rows) |
| P-2 | Case and surrounding-space variants hash to the same key; internal-form variants do not | Trust the toolkit’s TRIM and UPPER; do not add normalization of internal form |
| P-3 | A1007 and A-01008 may name existing clients; SOLUTION NEEDED, open by design | Disclose with an inspector (D-9); do not merge |
| P-4 | Empty and whitespace-only client numbers would hash as a value; RESOLVED by design decision D-4 | Staging hard rule turns them into NULL (D-4); the P7 characterization test runs first |
| P-5 | A NULL client number takes the all-zeros key | The unknown ghost record absorbs it; no extra hub row; no satellite stores the row (D-44), and an inspector raises it as a finding (D-45) |
| P-6 | A changed CRM row ID does not change identity | The hub ignores the technical key; CRM_ROW_ID is rejected |
| P-7 | Every LOADED_AT value parses | The cast in staging needs no error path for this data |
| P-8 | The planted rename: code SSI keeps its code while name, description and owner change between two commits of the seed | The seed’s git history holds both versions; no vault row changes |
| P-9 | The ghost records carry record sources SYSTEM and ERROR; RESOLVED by design decision D-17: add them to the seed | The seed has three codes; every vault record source resolves |
| P-10 | Client-file rows’ record source becomes the code SSI | The development client hub is rebuilt by scoped full refresh (D-20) |
| P-11 | The two new source tables plant 33 paths, one row set each | Each path has its own test (section 8) |
| P-12 | Eight policy paths, by source and load and by key form; a key missing one part hashes a placeholder in that part | One policy hub keyed by carrier and policy number (D-26); an inspector for the nonconforming form (D-30) |
| P-13 | Sunbelt Senior’s client list keeps one row per client; Northfield Life’s number identifies an insured | Two role hubs at their sources’ grains (D-32) |
| P-14 | The sales data relates a client to a policy in a role and can repeat the pair; the carrier data relates one insured to each policy | Two links at their sources’ grains (D-33) |
| P-15 | Each source describes its own objects in its own shape; changes arrive as new rows | One satellite per source on its own parent (D-34) |
| P-16 | 14 sales rows and 12 carrier rows across the two acts | Two landing tables loaded in the same two acts (D-22) |
| P-17 | What the client table holds, and how a missing number behaves, are resolved | D-32 |
| P-18 | The agent’s number in the carrier data, WRITING_AGENT_NPN, is a business key, and the sales data carries the same national producer number | An agent hub (D-35) and a link from the agent to the policy (D-36) |
| P-19 | Seven agent paths: one agent from both sources, from one source only, on several policies, with an empty number, the sources disagreeing about a policy’s agent, and a resend | D-35, D-36 |
5. Decisions
Decision ids are D-1, D-2, D-3, numbered in order within the document.
| ID | Decision | Rationale | Supporting evidence |
|---|---|---|---|
| D-1 | The LANDING database holds landed sources (one schema per source system: SUNBELT, and NORTHFIELD from increment 2), STORAGE and CONFIG | One database gives findings and baselines a home beside the landed sources | infra/snowflake/landing.sql |
| D-2 | The classic Data Vault is built into one vault database per tier, each reading its own landing database: DV with LANDING in production, DV_QA with LANDING_QA in QA, and DEV__DV__ |
Building both approaches from the same landed data makes the comparison controlled; a developer’s tier has production’s shape, so a privilege fault shows in development first | infra/snowflake/environment_tiers.sql |
| D-3 | The hub is named client and keyed by the client number alone; no UDM role taxonomy is applied (D-32 names client_h a role hub at its source’s grain) | Plain Data Vault first; the UDM taxonomy and the issuer-qualified key wait for later increments | Candidate pattern, course record item 268 |
| D-4 | Staging turns empty and whitespace-only client numbers into NULL before hashing | Without the rule, a blank number becomes a real hub row with an empty business key | Profile P-4 |
| D-5 | LOADED_AT is cast to a timestamp without time zone that means UTC; the project sets datavault4dbt.timestamp_default_dtype to TIMESTAMP_NTZ | A time-zone-aware type takes the session’s zone for zone-less values, so results differ by user | Commit 1ace099; datavault4dbt code read 2026-10-22 |
| D-6 | Superseded by D-15 | See D-15 | See D-15 |
| D-7 | Ghost records stay enabled, with the toolkit’s defaults | The unknown record absorbs NULL keys (P-5) | datavault4dbt documentation, staging page |
| D-8 | Hashing keeps the toolkit’s defaults: MD5, business keys uppercased, change hashes case-sensitive | The defaults produce P-2’s unification; changing them would mean rehashing later | datavault4dbt dbt/dbt_project.yml read 2026-10-22 |
| D-9 | An inspector, inspector_sunbelt_client_number_form, writes one finding to LANDING.CONFIG.DATA_FINDING for each distinct client number whose trimmed, uppercased form does not match the designed form (letter A, hyphen, four digits) | Discloses P-3 without hiding or correcting the rows; one finding per value, because the findings table has no count column | Testing methodology, layer 5; infra/snowflake/landing.sql; layer 5 is named inspector in this project |
| D-10 | An auditor, auditor_sunbelt_client_hub, compares the source with the hub: every distinct usable client key staged from the client list must exist in client_h, and rows with no usable number fall to the unknown ghost record. It writes one summary finding, carrying the counts, only when a key is missing. From increment 2 the hub also reads the sales data (D-32), so a count comparison would report keys the client list never sent | Makes the gap between 43 rows and 34 keys explicit, by comparing one system against another as an auditor does | Testing methodology, layer 6; layer 6 is named auditor in this project |
| D-11 | Data lands in two acts, load 1 then load 2, with a build after each | The second build exercises the hub’s incremental path (insert only new keys) | Profile P-1 paths P2 and P3 |
| D-12 | Four generic macros serve the vault: landing_storage, merge_into, capture_baseline, verify_build; LANDING is created by a committed script | Findings and baselines need the same machinery the standards assume | The four macros in dbt/macros |
| D-13 | Course increments promote through qa to production with certification | The vault serves a consumer, Mary, through a semantic layer and an agent, and only a release can answer her questions | QA item ; infra/snowflake/environment_tiers.sql |
| D-14 | The datavault4dbt stage and hub are new patterns for this project, recorded as items under the course record | No pattern record covers them yet | None beyond the rationale |
| D-15 | Record source codes are short mnemonic system codes, never changed (SSI for Sunbelt Senior Insurance’s client file); every vault row carries the code, and the staging view stamps it as its static record source | A code that never changes keeps kept rows correct forever; the name lives in the source system seed | D-21 |
| D-16 | Superseded by D-21 | See D-21 | See D-21 |
| D-17 | SYSTEM and ERROR, the record sources of the toolkit’s ghost records, are codes in the source system seed describing the vault itself, with the vault’s team as owner | Every record source in the vault then resolves in one join, with no special case in any consumer | Profile P-9 |
| D-18 | Superseded by D-21 | See D-21 | See D-21 |
| D-19 | Superseded by D-21 | See D-21 | See D-21 |
| D-20 | Development rebuilds use dbt’s scoped full refresh (full refresh of a model and everything downstream of it) through a documented script; never hand-written schema drops; never in QA or production | Rebuilding is legitimate only where data can be replayed; the scope stays as small as the change | setup/scripts/dev_scoped_rebuild.sh |
| D-21 | The source system seed: seed_source_system.csv (schema REFERENCE), columns SOURCE_SYSTEM_CODE, SOURCE_NAME, DESCRIPTION, OWNER; vault rows carry the code and join to the seed for the name; the seed’s history is its git history | The simplest object that keeps every guarantee: the permanent code, a rename in one place, every change reviewed and kept. First instance of the pragmatic principle: build an object only when it buys a guarantee nothing simpler gives | None beyond the rationale |
| D-22 | Two landing tables, LANDING.SUNBELT.POLICY_SALE and LANDING.NORTHFIELD.POLICY, loaded by the land_act macro in the same two acts | Same landing contract as the client list | D-1, D-11; profile 2.1.3, 2.1.4 and P-16 |
| D-23 | The source system seed gains one row, NFL (Northfield Life’s policy data); both of Sunbelt Senior’s tables carry SSI | One code per source system | D-21; profile 2.1.3 and 2.1.4 |
| D-24 | Two staging models, stg_sunbelt_policy_sale and stg_northfield_policy, apply only hard rules: type casts, and empty or whitespace-only numbers (client, insured, policy, carrier code, agent) turned into NULL, as D-4 does; hash keys use the toolkit’s defaults | Hard rules are technical changes such as type casting, formatting and null handling (VaultSpeed, Data Vault 2.0 concepts, https://docs.vaultspeed.com/space/VPD/3011870744.md) | D-4, D-8, D-14; profile P-12 and P-13 |
| D-25 | Superseded by D-32 | See D-32 | See D-32 |
| D-26 | The policy hub, policy_h, is keyed by the issuing carrier’s code and the policy number together; Northfield Life’s staging supplies NFL as the carrier code. A key missing one part hashes a placeholder in that part; only a key missing every part takes the all-zeros key | A policy number is unique only within its carrier’s namespace; a practitioner source confirms a collision code is used where keys from different sources could clash (https://blog.devgenius.io/data-vault-agility-on-snowflake-747a04485499) | datavault4dbt hash and hash_standardization macros, read at the fork’s pinned commit; profile P-12 |
| D-27 | Superseded by D-33 | See D-33 | See D-33 |
| D-28 | Superseded by D-34 | See D-34 | See D-34 |
| D-29 | No column is merged across sources and no person is matched across hubs in this increment | Changes of meaning are soft rules, applied in the business vault (VaultSpeed, as D-24) | The course’s milestone: the raw vault keeps the two sides apart |
| D-30 | An inspector, inspector_sunbelt_policy_number_form, reports policy numbers not in the carrier’s usual form (NF-, a hyphen, six digits), one finding per value | Same pattern as the client number inspector | D-9; profile P-12, path PH6 |
| D-31 | The client relationship to Sunbelt Senior is not built as a separate object: it is the grain of client_h, one row per Sunbelt Senior client | No source sends the relationship apart from the client row; a relationship between two party roles belongs in an integration layer | Profile P-13 and P-17 |
| D-32 | Two role hubs at their sources’ grains: client_h, keyed by Sunbelt Senior’s client number and read from the client list and the sales data; insured_h, keyed by Northfield Life’s insured number. A missing number takes the unknown ghost record | Each number identifies one instance of a role in one source; the person behind the roles, and any matching between them, is built after the raw vault | Profile P-13 and P-17 |
| D-33 | Two links at their sources’ grains: client_policy_l (client and policy, with the role as a dependent child key: owner, insured, and any code the source adds, such as payor) from the sales data; insured_policy_l (insured and policy, no role key) from the carrier data | A link records a relationship at the grain its source sends it; a dependent child key is needed only where one source row can repeat the same pair; a new role code adds rows, never structure | Red Gate, Data Vault modeling basics (https://www.red-gate.com/blog/data-vault-series-data-vault-2-0-modeling-basics/); datavault4dbt dependent child keys page; profile P-14 |
| D-34 | Satellites, one per source on its own parent, named by the course’s raw vault naming rule: client_sunbelt_s (first name, last name, state); insured_northfield_s (name, birth date, address); policy_northfield_s (face amount, status, effective date); client_policy_sunbelt_s (sale date, agent, plan). Each is a version 0 satellite with a version 1 view | Satellites can sit one per source system; change hashes are case-sensitive (D-8), so a change in letter case alone is a new version | Navicat (https://www.navicat.com/company/aboutus/blog/3970-designing-better-databases-with-data-vault-2-0-and-navicat-data-modeler-4); datavault4dbt standard satellite pages; profile P-15 |
| D-35 | An agent hub, agent_h, keyed by the national producer number and fed by both sources: AGENT_NPN from the sales data and WRITING_AGENT_NPN from the carrier data. An empty number takes the unknown ghost record. The agent’s name stays a description of the sale | A business key belongs in a hub; the number is assigned through the National Insurance Producer Registry and is the same in every source, so the hub matches an agent across sources | National Insurance Producer Registry, NPN lookup (https://pdb.nipr.com/html/NPNsearch.htm); profile P-18 and P-19 |
| D-36 | One link, agent_policy_l, from the agent to the policy, fed by both sources, with no dependent child key; when the sources name different agents for one policy, both rows are kept | Both sources send the same relationship at the same grain, one agent per policy row, between the same two hubs, so one link holds both; deciding which agent is right changes meaning and belongs after the raw vault (D-29) | Red Gate, Data Vault modeling basics (https://www.red-gate.com/blog/data-vault-series-data-vault-2-0-modeling-basics/); profile P-19, paths AG1 and LA2 |
| D-37 | LANDING gains the schema PINECREST and the table PINECREST.POLICY, created by the teacher as the stand-in source for Ray’s Lab 1-2, since populating landing is outside the vault: POLICY_NUMBER, PLAN_NAME, FACE_AMOUNT, STATUS, EFFECTIVE_DATE, WRITING_AGENT_NPN and LOADED_AT, every column text, as Pinecrest Mutual sends it | Landing keeps a source’s columns as sent; turning the amount and the dates from text into numbers and dates is staging’s hard rule, Lab 1-3’s subject | D-1, D-11; profile 2.1.5 and P-20 |
| D-38 | land_act gains a fourth table, pinecrest_policy, carrying the twelve rows of profile P-20 in the same two acts, delivered to LANDING; QA and developer landing receive them only by cloning LANDING | The same landing contract as D-22, under the rule that landing data flows down from production | Course record, “Landing data flows down”; land_act, commit 0472975; profile P-20, path PM1 |
| D-39 | dbt declares LANDING.PINECREST.POLICY as a source, through landing_storage(), with LOADED_AT not null and the grain (LOADED_AT, POLICY_NUMBER) unique, both at severity warn | Every landed source carries layer 1 and layer 2 proofs, as the other three tables do | Section 8, layers 1 and 2; profile 2.1.5, grain |
| D-40 | Each satellite’s hash key to its parent hub or link carries a dbt relationships test, severity warn: four tests, on client_sunbelt_s, client_policy_sunbelt_s, insured_northfield_s and policy_northfield_s. The links’ hub keys keep their existing proof, the layer 4 assertion assert_increment_2_link_hub_keys_exist, which checks all six link-to-hub pairs | Section 0 draws these relationships solid, and the delivery standard draws a line solid only where the build tests it; the satellites’ lines had no check, while the links’ already had one, and the test plan rule reuses existing proof | Section 0; read on 2026-10-26, 0 link or satellite rows without their parent across the three links’ 45 rows and the four satellites |
| D-41 | Hashdiffs are case-sensitive: a change only in letter case is a change, and the satellite keeps it as a new version. dbt_project.yml sets datavault4dbt.hashdiff_input_case_sensitive to true in the root project, and the layer 4 assertion assert_hashdiff_keeps_case_only_change, at severity error, fails when two staged versions of one insured differ only in case yet share a hashdiff | The package sets the variable itself, and the workstation’s dbt v1 1.12.6 and v2 both read it case-sensitive; dbt Cloud’s QA build of 2026-10-26 (version 2026.10.14+638bb1a) compiled every hashdiff upper-cased and lost Martin Lowe’s “12 ELM ST” version. The cause in dbt Cloud is not known; the root setting removes the dependence on how an engine reads a package’s variables, and the assertion proves the behavior on any engine | QA item ; DV_QA against the development vault, 2026-10-26: insured_northfield_s 10 rows against 11, hub and link keys identical |
| D-42 | Every link’s hub keys carry a dbt relationships test to their hub, and every satellite’s primary key, its parent hash key with its load time, carries a unique-combination test and a not-null test on each of the two columns, all at severity warn: six relationships tests on the three links and twelve tests on the four satellites | The Code Review Standard requires a relationships test on every foreign key, in layer 1; the links had only the layer 4 assertion, and no satellite’s key was tested for uniqueness | Code Review Standard, layer 1; the first run of uq_client_sunbelt_s_pk failed on the zero key, which held three client rows without a client number as one false client |
| D-43 | The vault declares its keys from those layer 1 tests: the macro declare_vault_keys, run as the project’s on-run-end hook, turns each unique test into its table’s primary key and each relationships test into a foreign key, 12 primary keys (11 in the raw vault, 1 in reference) and 10 foreign keys. Snowflake keeps them as information and never enforces them, and a key already present is left alone | The keys reach the DDL a modeling tool imports, since Snowflake’s GET_DDL reports them; one definition serves the tests and the keys, so the two cannot disagree; an informational key never fails a load | The development vault, read on 2026-10-28: 22 constraints in its information schema; GET_DDL of client_policy_l shows its primary key and both foreign keys |
| D-44 | A satellite stores only source rows whose key is known: client_sunbelt_s, insured_northfield_s and policy_northfield_s read the staging views stg_sunbelt_client_known_client, stg_northfield_policy_known_insured and stg_northfield_policy_known_policy, which keep the rows whose business key is not NULL. Links to the unknown keys stay | A row without its business key describes no one: on the zero key every such row becomes a version of one false entity. A link row records a real sale or policy whose other parts are known, so it stays | Code Review Standard, no silent record loss; the development vault, read on 2026-10-28: no zero-key source row in any satellite |
| D-45 | Each source row without a business key is a finding: the inspectors inspector_sunbelt_client_key_missing, inspector_sunbelt_policy_sale_key_missing and inspector_northfield_policy_key_missing each read its landing table, the persistent staging that keeps every row as sent, and treat a key as missing when staging’s rule NULLIF(TRIM(key), ‘’) makes it NULL; each writes one finding per row and missing key to LANDING.CONFIG.DATA_FINDING through merge_into, naming the row so it can be found in landing | The rows D-44 keeps out of the satellites stay visible, so nothing is lost silently; the inspector reads the history the rows arrived in, so the finding needs no vault object to hold the row | Code Review Standard, no silent record loss; the development vault, read on 2026-10-28: 7 missing-key findings beside the 3 form findings |
| D-46 | Production’s satellite rows stored on the zero key before D-44, three client rows and one insured row, are removed once, at the promotion that carries the fix, by infra/snowflake/correct_zero_key_satellite_rows.sql. The package’s placeholder rows (record source SYSTEM or ERROR) stay; a vault built after the fix holds none, so the script removes nothing there | D-44 keeps new rows out of the satellites but does not remove the rows production’s incremental satellites already hold | The script run on the development vault with production’s four rows copied in: 3 and 1 before, 0 and 0 after, placeholders kept, a rerun removing nothing, a database outside the vault tiers refused |
| D-47 | The meaning of a finding is defined in the semantic layer: finding_fact carries finding_kind, taken from the finder that wrote the finding (form: a record arrived in a form the vault does not accept and was not accepted; missing_key: a row arrived without a business key and was kept and flagged; audit: a count that does not reconcile), and finding_row, the row a finding names. The metric records_not_accepted counts the findings whose finding_kind is form and answers Q-15; rows_missing_key counts the distinct rows named by findings whose finding_kind is missing_key | After D-45 the findings metric counted both, so Q-15 answered 10, not 3; the meaning of the data belongs in the semantic layer, where every consumer reads it, not in instructions to a model | Development vault at commit 2a921a1: 3 form findings naming 3 rows, 7 missing_key findings naming 6 rows; unit test unit_finding_fact_kind_and_row red before the model change and green after; dev build 174 of 174 |
| D-48 | Nothing from the reference architecture appears in the repository, its documents or the site: its mentions are removed from comments with no code changed, and the site build holds its pages in memory and writes none when a built page, code views included, carries a term of site/excluded_terms.txt | The reference architecture is a reference only, and a published page that names it can’t be taken back; a guard in the build stops a return that a review could miss | The build refused 120 hits in 11 pages before the removal and wrote 22 pages with none after; the committed tree carries no listed term outside the list itself |
| D-49 | The home page opens with an executive summary: what the vault answers about an acquired agency’s business and what can be shown a regulator, the proof behind it, and where to start; its Databricks line appears only while DATABRICKS_SOON is on | A first-time reader has to get the point before the tour of layers; a line about work not yet done stays hidden until it is true | The summary first on the built home page, word for word as approved; the switch off omits the line and on shows it; every address in it resolves |
| D-50 | The release page sends a reader to the Ask page, with the access code Ray sends, to try its questions; it names no connector | A reader of the site has no connector address, and the Ask page needs none | The release page’s prompts read as Ask page questions; no connector instruction on any page |
| D-51 | The QA retest runs inside the workstation container, accepting the container’s gateway certificate only where that certificate is installed, and counts a number given as digits or spelled out | The container’s gateway re-signs HTTPS with its own authority, which the test browser refused, so no question reached the function; a reply that spells out a number is still correct | The retest passed 26 of 26 from the container against the QA function |
| D-52 | The counts Mary’s site shows about the branch documents and the dbt project are read from their sources at every build: the design decisions from the design’s decision table, the finders and auditors from the inspector and auditor models | Typed counts went stale: the site showed 41 design decisions against 51, and 5 finders and auditors against 8 | The build’s guard compares each shown count with its source and refuses a mismatch |
6. Domain specification
6.1 UDM map
| Profile verdict | Source concept and column | UDM area | Destination table and column | Identity recipe | Pattern | Additive? | Decision | Deliberate collapse, omission, or deferral |
|---|---|---|---|---|---|---|---|---|
| Integrate | Client identifier: SUNBELT.CLIENT.CLIENT_NUMBER | Tabled until the vanilla Data Vault increments are done | DV hub client_h, columns hk_client_h and client_number | Hash of the trimmed, uppercased client number after D-4; NULL takes the all-zeros key | datavault4dbt stage and hub (D-14) | Yes: new keys insert; existing keys never change | D-3, D-4, D-8 | Internal-form variants are not unified (P-3) |
| Integrate | Arrival time: SUNBELT.CLIENT.LOADED_AT | Tabled, and a technical attribute in any case | The load timestamp ldts of the staging view and the hub | Not an identity column | Staging hard rule | Yes | D-5 | None |
| (derived) | Record source: the code SSI (increment 1.1; was the name SUNBELT.CLIENT) | Tabled | The record source rsrc of the staging view and the hub | Not an identity column | Staging | Yes | D-15 | None |
| Integrate | Record source code: SEED_SOURCE_SYSTEM.SOURCE_SYSTEM_CODE | Tabled | The source system seed | The permanent code every vault row carries | dbt seed | No: edited by commit | D-21 | None |
| Integrate | Source name, description, owner: SEED_SOURCE_SYSTEM.SOURCE_NAME, DESCRIPTION, OWNER | Tabled | The source system seed | Not identity columns | dbt seed | No: edited by commit; history in git | D-21 | None |
| Defer | FIRST_NAME, LAST_NAME, STATE | Absent | Absent until the satellites increment | Profile 6.2 | Deferred by the profile | |||
| Reject | CRM_ROW_ID | Absent | Absent | Profile 6.3 | Rejected as a technical key | |||
| Integrate (profile section 5, item 8) | Client details: SUNBELT.CLIENT.FIRST_NAME, LAST_NAME, STATE | Tabled | client_sunbelt_s | Parent key hk_client_h; change hash over the three columns | datavault4dbt satellite | Yes: insert-only versions | D-34 | None |
| Integrate (profile section 5, item 8) | Policy identifier: SUNBELT.POLICY_SALE.CARRIER_CODE and POLICY_NUMBER; NORTHFIELD.POLICY.POLICY_NUMBER with NFL supplied in staging | Tabled | policy_h, columns hk_policy_h, carrier_code, policy_number | Hash of the carrier code and the policy number, each trimmed and uppercased; one missing part hashes a placeholder; both missing take the all-zeros key | datavault4dbt stage and hub | Yes | D-24, D-26 | NF100009 and NF-100009 are not unified (path PH6) |
| Integrate (profile section 5, item 8) | Client in a sale: SUNBELT.POLICY_SALE.CLIENT_NUMBER | Tabled | client_h, existing | As D-4 and D-8 | datavault4dbt hub, second source | Yes | D-32 | None |
| Integrate (profile section 5, item 8) | Insured: NORTHFIELD.POLICY.INSURED_ID | Tabled | insured_h, columns hk_insured_h, insured_number | Hash of the trimmed, uppercased insured number; a missing number takes the all-zeros key | datavault4dbt stage and hub | Yes | D-24, D-32 | None |
| Integrate (profile section 5, item 8) | Role in a sale: SUNBELT.POLICY_SALE.ROLE, with the client and policy keys | Tabled | client_policy_l, columns hk_client_policy_l, hk_client_h, hk_policy_h, role | Link hash over the client key, the policy key and the role | datavault4dbt link with a dependent child key | Yes | D-33 | None |
| Integrate (profile section 5, item 8) | Insured on a policy: NORTHFIELD.POLICY.INSURED_ID with the policy key | Tabled | insured_policy_l, columns hk_insured_policy_l, hk_insured_h, hk_policy_h | Link hash over the insured key and the policy key | datavault4dbt link | Yes | D-33 | None |
| Integrate (profile section 5, item 8) | Sale details: SUNBELT.POLICY_SALE.SOLD_ON, AGENT_NAME, PLAN_NAME | Tabled | client_policy_sunbelt_s | Parent key hk_client_policy_l | datavault4dbt satellite | Yes | D-34 | None |
| Integrate (profile section 5, item 8) | Insured details: NORTHFIELD.POLICY.INSURED_NAME, BIRTH_DATE, ADDRESS | Tabled | insured_northfield_s | Parent key hk_insured_h | datavault4dbt satellite | Yes | D-34 | Not merged with client_sunbelt_s (D-29) |
| Integrate (profile section 5, item 8) | Policy details: NORTHFIELD.POLICY.FACE_AMOUNT, STATUS, EFFECTIVE_DATE | Tabled | policy_northfield_s | Parent key hk_policy_h | datavault4dbt satellite | Yes | D-34 | None |
| Integrate (profile 6.1, the agent) | The agent: SUNBELT.POLICY_SALE.AGENT_NPN and NORTHFIELD.POLICY.WRITING_AGENT_NPN | Tabled | agent_h, columns hk_agent_h, agent_npn | Hash of the trimmed number; an empty number takes the all-zeros key | datavault4dbt stage and hub, two sources | Yes | D-24, D-35 | None |
| Integrate (profile 6.1, the agent) | The agent on a policy: either agent number with the policy key | Tabled | agent_policy_l, columns hk_agent_policy_l, hk_agent_h, hk_policy_h | Link hash over the agent key and the policy key | datavault4dbt link, two sources | Yes | D-36 | Disagreeing sources are kept as two rows (path LA2) |
| Integrate (profile section 5, item 8) | Arrival time and record source of both new tables: LOADED_AT; the codes SSI and NFL | Tabled; technical | ldts and rsrc of every new staging view, hub, link and satellite | Not identity columns | Staging hard rule | Yes | D-5, D-15, D-23 | None |
| Reject, as CRM_ROW_ID | SUNBELT.POLICY_SALE.SALE_ROW_ID | Absent | Absent | D-24 | A technical key that identifies a row, not a sale |
Completion check: every profiling Integrate row has a destination; every Defer and Reject is named as absent; every written UDM column traces to a source column or stated derivation.
6.2 Dictionary
| Term | Business meaning | UDM mapping | Mapping state | Decision | Verified example |
|---|---|---|---|---|---|
| Client | One instance of the client role: a person Sunbelt Senior’s client list keeps one row for | Tabled | Tabled | D-3, D-32 | Specified row: client number A-1001, first load 2026-10-01 02:00 UTC |
| Client number | Sunbelt Senior’s identifier for a client, designed as the letter A, a hyphen and four digits | Tabled | Tabled | D-3, D-4 | A-1001; variants a-1004 and A-1005 with a trailing space resolve to existing keys; A1007 does not |
| CRM row ID | The number Sunbelt Senior’s CRM gives a row; not a client identifier | None: rejected | Not applicable | Profile 6.3 | A-1003 arrives under a new CRM row ID in load 2 and stays the same client |
| Load timestamp | The time a row arrived from the source | Tabled; technical | Tabled | D-5 | 2026-10-01T02:00:00 for every load 1 row |
| Record source | The permanent code of the source system a row came from; its name lives in the source system seed | Tabled; technical | Tabled | D-15 | SSI on every client-file row |
| Source system seed | The table of every source system code with its current name, description and owner; its history is its git history | Tabled; technical | Tabled | D-21 | SSI: Sunbelt Senior client file |
| Role | What a party does in a context, never what it is: client and insured as roles held with a source; owner, insured and payor as roles on a policy | Tabled | Tabled | D-32, D-33 | Specified rows: A-1010 as owner and as insured on NF-100010 |
| Insured | One instance of the insured role: a person a carrier’s policy covers, as Northfield Life keeps them | Tabled | Tabled | D-32 | Specified row: NLI-55007, Garrity, Irene, on NF-100007 |
| Policy | An insurance contract issued by a carrier, identified by the carrier’s code and its policy number together | Tabled | Tabled | D-26 | Specified row: NFL with NF-100001 |
| Link | A vault table recording a relationship between hub keys, at the grain its source sends it | Tabled; technical | Tabled | D-33 | Specified row: client A-1007, policy NFL NF-100007, role insured |
| Dependent child key | A column added to a link’s key so that one source can record the same pair more than once, told apart | Tabled; technical | Tabled | D-33 | Specified rows: A-1010 as owner and as insured on NF-100010 |
| Agent | A licensed insurance producer who sells policies, identified by a national producer number | Tabled | Tabled | D-35 | Specified row: 17001001, on NF-100001 in both sources |
| National producer number | The number assigned through the National Insurance Producer Registry to a licensed producer, the same in every state and every source | Tabled | Tabled | D-35 | Specified rows: 17001001 in the sales data and the carrier data |
6.3 Domain content
The hash rule, step by step (datavault4dbt code, read 2026-10-22): the client number is cast to text, trimmed, and checked for NULL; text is escaped and quoted; the whole input is uppercased because business keys are case-insensitive by default (D-8); line breaks and tabs are removed; the result is hashed with MD5. Only a NULL input becomes the all-zeros key. D-4 moves empty and whitespace-only values into that NULL path.
Ghost records. The staging view adds an unknown row (all-zeros key, business key (unknown), load timestamp 0001-01-01 00:00:01, record source SYSTEM) and an error row (all-f key, business key (error), load timestamp 8888-12-31 23:59:59, record source ERROR). The hub keeps them like any other key, so after the build it holds 34 client keys and these two rows. No satellite stores a source row on the unknown key: each satellite reads a staging view that keeps only rows with their key (D-44), and an inspector raises every row without its key as a finding (D-45).
Keys declared from tests. Each relationships test in layer 1 names a column and the table it must match; each unique test names a table’s key. The macro declare_vault_keys reads those tests after every build and declares each as a key in Snowflake: a unique test becomes the table’s primary key, a relationships test a foreign key (D-43). Snowflake does not enforce them; they describe the vault to anything that reads its DDL, such as a modeling tool.
Keep the first sighting. For each hash key the hub keeps the earliest row across everything it reads, and on later builds inserts only keys it does not already hold. That is why a client present in both loads keeps load 1’s timestamp.
Keys with two parts. The policy key is the carrier’s code and the policy number, hashed together. Each part is standardized like a single key; a missing part is written as a placeholder (^^) before hashing, so a sale with no carrier code still gets a real key, separate from the same number under NFL. Only a key missing every part becomes the all-zeros key (datavault4dbt hash and hash_standardization macros, read at the fork’s pinned commit).
The role as part of a link’s key. client_policy_l hashes the client key, the policy key and the role together, so A-1010 as owner and A-1010 as insured on NF-100010 are two link rows. A role code the source never documented, such as payor, becomes one more row with that role; the link’s columns do not change.
6.4 Instance models
Verified in production’s vault, DV, read 2026-10-31 22:22Z: A-1010 holds one hub row, loaded 2026-10-01 02:00, so the lower-case a-1010 resolved to it; A-1005 holds one row; A1007 holds its own row, loaded 2026-10-02 02:00; no hub row holds an empty client number, and the unknown ghost record is the hub’s only all-zeros row.
flowchart LR
S1["load 1: A-1010"] ==> K1["hub key of A-1010"]
S2["load 1: a-1010"] ==> K1
S3["load 2: A-1005 with trailing space"] ==> K2["hub key of A-1005"]
S4["load 2: A1007"] ==> K3["hub key of A1007, separate"]
S5["load 1: NULL"] ==> G["unknown ghost record"]
S6["load 1: empty string"] ==> G
S7["load 2: three spaces"] ==> G
The source system seed, after the planted rename (as committed; the first version is in git, commit 29f7412):
| SOURCE_SYSTEM_CODE | SOURCE_NAME | OWNER |
|---|---|---|
| SSI | Sunbelt Senior client file | Digest Insurance Distributors |
| SYSTEM | The vault itself: unknown ghost record | The vault’s team |
| ERROR | The vault itself: error ghost record | The vault’s team |
Irene Garrity’s policy across both sources (profile P-16; verified in DV, read 2026-10-31 22:22Z: NF-100007 joins A-1007 as insured in client_policy_l, NLI-55007 in insured_policy_l and 17001001 in agent_policy_l, one row first named by SSI):
flowchart LR
S1["sales act 1: NF-100007, A-1007, insured"] ==> L1["client_policy_l: A-1007, NFL NF-100007, insured"]
C1["carrier act 1: NF-100007, NLI-55007, Garrity, Irene"] ==> L2["insured_policy_l: NLI-55007, NFL NF-100007"]
L1 ==> P["policy_h: NFL NF-100007"]
L2 ==> P
L1 ==> H1["client_h: A-1007"]
L2 ==> H2["insured_h: NLI-55007"]
S1 ==> AL["agent_policy_l: 17001001, NFL NF-100007"]
C1 ==> AL
AL ==> P
AL ==> A["agent_h: 17001001"]
6.5 Worked examples
Invented names, separate from the specified rows. Sunbelt Senior sends Rosa Diaz as A-2001 on Monday. Its Tuesday file has her again as a-2001 because a clerk typed lower case: same hash key, so the hub still shows Monday as her first arrival. Wednesday’s file carries A2001 without the hyphen: a different key, so the hub now holds two rows that both belong to Rosa Diaz, and only the form inspector says anything about it. Thursday’s file has a new client whose number field is blank: staging turns the blank into NULL, the row takes the all-zeros key, the hub gains nothing, the client details satellite stores nothing, and an inspector writes one finding naming the row.
A second story, also invented. Tomas buys a policy from Lakeview Life through an agency; he owns it, and his mother Lucia is the insured. The agency’s sales data sends two rows for policy LV-200001: Tomas as owner and Lucia as insured, each under the agency’s own client number. Lakeview Life’s data sends one row: the policy, with Lucia under Lakeview’s own insured number. The vault holds one policy, two of the agency’s clients and one Lakeview insured. Lucia appears twice, once per source, and nothing in the vault says the two are one person. The agent who sold the policy, Maria, is different: both sources send her national producer number, so the vault holds her once.
flowchart LR
A1["agency: Tomas, owner"] ==> P["policy LV-200001"]
A2["agency: Lucia, insured"] ==> P
B1["Lakeview Life: Lucia, insured"] ==> P
A1 ==> G["agent Maria, one national producer number"]
B1 ==> G
7. Deferred decisions
| ID | Deferred decision | Reason | Unblocking condition | Owner |
|---|---|---|---|---|
| DD-1 | Qualify a business key by its issuer | Resolved: the policy key carries the carrier’s code (D-26), and each person number stays in its own source’s role hub (D-32) | None | Ray |
| DD-2 | UDM taxonomy: party, roles, role names | UDM tabled until the vanilla Data Vault increments are done | Ray lifts the table | Ray |
| DD-3 | The descriptive columns FIRST_NAME, LAST_NAME, STATE | Resolved by D-34 | None | Ray |
| DD-4 | Pragmatic Data Platform history in LANDING.STORAGE | Comparison belongs to a later increment | The increment that builds both approaches side by side | Ray |
| DD-5 | Match the person behind client and insured role keys | Matching changes meaning: a soft rule, applied after the raw vault (D-29) | The course’s business vault part | Ray |
8. Testing plan
| Layer | Required proof or artifact | Applicability and reason |
|---|---|---|
| 1: built-in dbt tests | Source SUNBELT.CLIENT: not_null on CRM_ROW_ID and LOADED_AT. Hub client_h: unique and not_null on hk_client_h. Source system seed: unique and not_null on SOURCE_SYSTEM_CODE. Sources SUNBELT.POLICY_SALE and NORTHFIELD.POLICY: not_null on LOADED_AT. Source PINECREST.POLICY (Lab 1-2, D-39): not_null on LOADED_AT. Hubs policy_h, insured_h and agent_h, links client_policy_l, insured_policy_l and agent_policy_l: unique and not_null on their hash keys. Satellites client_sunbelt_s, client_policy_sunbelt_s, insured_northfield_s and policy_northfield_s: a relationships test from each hash key to its parent (D-40), four tests. Links client_policy_l, insured_policy_l and agent_policy_l: a relationships test from each hub key to its hub (D-42), six tests, beside the layer 4 assertion. Satellites: a unique-combination test on the parent hash key with the load time, and a not-null test on each (D-42), twelve tests. Severity warn, stated explicitly. | Applies: structural integrity of every source, hub, link and satellite |
| 2: dbt-expectations | Compound uniqueness of each source table’s grain: (LOADED_AT, CRM_ROW_ID) on SUNBELT.CLIENT, and the grains of profile 2.1.3 and 2.1.4; (LOADED_AT, POLICY_NUMBER) on PINECREST.POLICY (Lab 1-2, D-39); row counts checked as a source query against a target query, never against a fixed number. Severity warn. | Applies; the expectations package cannot be installed on the workstation, so these are dbt_utils or singular tests |
| 3: PCM unit tests | One unit test per path, P1 to P8, on the client list’s staging view and the client hub, the P7 test first as a characterization; one passing and one failing unit test each for the inspectors and the auditors. One unit test per path of profile P-12 to P-15 and P-19 on the two new staging models, the hubs, the links and the satellites, built from the planted source data. All written and run before the code they prove. Severity error. | Applies: the staging hard rules (D-4, D-24), the hubs’ keep-first behavior, the link’s dependent child key and the satellites’ change detection are logic; the source system seed has none Lab 1-2 adds no unit test: land_act is an operations macro, proved live, as for D-22: its dry run lists 8 and then 4 rows, a second load of an act is refused, and LANDING_QA is refused. |
| 4: logic assertions | No hub row has an empty or whitespace-only business key. Every source row with a usable client number has its hash key in client_h. Every record source value in the vault resolves to a code in the source system seed (D-17), NFL included. Every link row’s hub keys exist in their hubs. Severity warn. | Applies: modeled truths the vault claims |
| 5: detectors | inspector_sunbelt_client_number_form (D-9) and inspector_sunbelt_policy_number_form (D-30), each writing one finding per nonconforming value to LANDING.CONFIG.DATA_FINDING through merge_into. For the planted data: the client numbers of profile P-3 and the policy number of path PH6. The missing-key inspectors inspector_sunbelt_client_key_missing, inspector_sunbelt_policy_sale_key_missing and inspector_northfield_policy_key_missing (D-45), one finding per row and missing key. | Applies: P-3, PH6 and the rows without a business key are real conditions to disclose |
| 6: reconciliation | auditor_sunbelt_client_hub (D-10), and in its pattern one auditor per new source table, auditor_sunbelt_policy_hub and auditor_northfield_policy_hub: every distinct usable key in the source must exist in the hub it feeds, a source query against a target query, with a finding only when a key is missing | Applies: the gap between rows and keys is the lesson |
| 7: semantic surface | The release 1 mart’s metrics records_not_accepted and rows_missing_key (D-47), each with a saved query and a two-route verification; the conformed mart’s semantic layer is the mart design’s (, section 4) | Applies: the branch ships metrics (D-47) |
| Full-build guardrails, not a methodology layer | Full build clean after each load act, with capture_baseline before and verify_build after (D-12); every warning named and dispositioned | Applies |
Consumer-question probes
| Question | Runnable probe | Expected result |
|---|---|---|
| Q-1 | SELECT client_number, ldts, rsrc FROM the client hub WHERE rsrc = ‘SSI’, against the distinct usable client numbers of SUNBELT.CLIENT with their first load | The two lists agree; A-1031 and A-1032 first arrive in load 2 (profile P-1) |
| Q-2 | SELECT column_value, detail FROM LANDING.CONFIG.DATA_FINDING for the inspector inspector_sunbelt_client_number_form | Two findings, one naming A1007 and one naming A-01008 |
| Q-3 | The auditor’s findings in LANDING.CONFIG.DATA_FINDING | No finding: 34 keys and 43 rows agree |
| Q-4 | SELECT h.client_number, s.source_name FROM the client hub h JOIN the source system seed s ON s.source_system_code = h.rsrc | Every row names its source: client rows the Sunbelt Senior client file, ghost rows the vault itself |
| Q-5 | SELECT the client number, the policy key and the role FROM client_policy_l joined to client_h and policy_h | Each sale’s client appears with the role the sales data sends: owner, insured or payor; A-1010 holds two roles on NF-100010 (profile P-14) |
| Q-6 | SELECT the policy hub row of NF-100007 with every link row that reaches it, and the client and insured keys at their other ends | One policy row; a client_policy_l row to client A-1007 and an insured_policy_l row to insured NLI-55007; no row joins A-1007 to NLI-55007 (profile P-14, path L4) |
Profile check register
| Profile result | Check | Layer | Planned artifact |
|---|---|---|---|
| P-1 | The client list’s 43 rows give 34 client keys; with A-1033 from the sales data and the two ghost records, the hub holds 37 rows | 2 and 6 | row-count expectation; auditor_sunbelt_client_hub |
| P-2 | Variants unify or stay separate as specified | 3 | unit tests P4, P5, P8 |
| P-3 | Two findings, one per nonconforming client number | 5 | inspector_sunbelt_client_number_form |
| P-4 | No hub row with an empty business key | 3 and 4 | unit test P7; logic assertion |
| P-5 | NULL key lands on the unknown ghost record; no satellite stores the row, and an inspector raises it | 1, 3 and 5 | unit test P6; uq_client_sunbelt_s_pk; the missing-key inspectors and their unit tests |
| P-6 | Changed CRM row ID keeps the key | 3 | unit test P2 |
| P-7 | Parse count equals populated count | 1 | not_null after the cast, plus the build itself |
| P-8 | The seed’s git history holds both versions of SSI; every hub row still carries SSI | 4 | git log of the seed; logic assertion and probe Q-4 |
| P-9 | Every vault record source resolves to a code in the source system seed | 4 | logic assertion |
| P-10 | Every client-file hub row carries SSI | 4 | logic assertion and the Q-4 probe |
| P-11 to P-15, P-19 | Every planted path behaves as specified | 3 | one unit test per path |
| P-19, path AG1 | One agent sold through both sources is one hub row | 3 and 4 | the AG1 unit test; every link row’s hub keys exist in their hubs |
| P-12, path PH6 | The nonconforming policy number is reported | 5 | inspector_sunbelt_policy_number_form |
| P-14, path L4 | One person stays two role keys | 4 and probe Q-6 | probe Q-6 |
| P-16 | Every planted row lands in its act | 2 and 6 | grain uniqueness; the new auditors |
| P-17 | A missing number takes the unknown ghost record | 3 | the unit tests of paths CH3 and IH4 |
| P-18 | One agent hub keyed by the national producer number, fed by both tables, and a link from the agent to the policy | 3 and 4 | the unit tests of paths AG1 to AG5, LA2 and LA3; every link row’s hub keys exist in their hubs |
9. Handoff boundary
The implementation guide owns how: the committed script that creates LANDING (schemas, tables, roles) and lands the two loads; the ported macros; the source declaration; the staging model with the D-4 rule; the hub model; every test above with its exact file; the build sequence; and the verification queries. For increment 2 the guide also owns the two new landing tables and their acts, the NFL seed row, the two staging models, and the hubs (agent_h included), links (agent_policy_l included), satellites, inspector and auditors named above, with every test of section 8. For the fix to release 1 the guide also owns D-42’s layer 1 tests, the macro declare_vault_keys and its hook, the three known-key staging views, the three missing-key inspectors, the production correction script, and the regeneration of the vault’s DDL. This design owns what and why and changes only through an agreed amendment.
Finance workbook and initial UAT
For finance reporting branches, state the workbook’s consumer, scope, business questions, expected behavior, preliminary-review conditions and final acceptance criteria under K#12 I#27. Otherwise state NA with a reason.
Not applicable: this branch is not a finance reporting branch.
10. Compliance record
Built by the author against the design template and Standard: Branch Delivery Documents (, design document section and its amendments), at 2026-10-31 22:46Z, on the text above. Verdict: compliant.
10.1 Template and structure
| # | Requirement | Result |
|---|---|---|
| 1 | Header: every field present and filled | Yes. The author line reads “Ray Serrano; edited by Claude”, overriding the template’s fixed text (Ray, 2026-10-22) |
| 2 | Every section of the template | Yes, 22 of 22: 0 to 9 with 2.1 to 2.3, 6.1 to 6.5, the probes, the profile check list and the finance workbook section |
| 3 | Written through the governed procedures | Instantiated from the template; changes through the Pilot interface, ITEM_SPLICE, which records each body as a new version. One write of 2026-10-28 at 17:44Z, before the interface existed in this database, went by direct update (Linear DIG-595 installed the interface) |
| 4 | Section 0, the target ERD, drawn from the UDM map and the profile’s source ERD | Yes: present and agreeing with the UDM map; each solid line is checked: the links’ by D-42’s relationship tests and the layer 4 assertion, the satellites’ by D-40’s relationship tests |
10.2 Required content
| # | Requirement | Result |
|---|---|---|
| 1 | Consumer questions: id, asker, time, origin, words, answer, decisions, probe, and a state of answered, partially answered, later or not answered | Yes: Q-1 to Q-5 answered, each by its probe run against the developer vault on 2026-10-25; Q-6 partially answered, the lessons and diagram following the build; Q-9 to Q-11 and Q-15 answered by release 1; Q-13 and Q-14 later, in this release’s conformed mart; Q-12 deferred (mart design DD-1); Q-7, Q-8 and Q-16 later; Mary’s probes in the test plan’s section 2.3.4 |
| 2 | Data reality cites the profile results | Yes: section 4 cites P-1 to P-19 |
| 3 | Decisions numbered, each with rationale and evidence, no attribution inside | Yes: D-1 to D-52, 45 live and 7 superseded; D-13 states only its current rule; no decision row carries attribution |
| 4 | Domain specification: UDM map first, then dictionary, content, instance models and worked examples, each model and example with its diagram | Yes: 6.1 to 6.5 in that order; diagrams in the instance models and the worked examples |
| 5 | Deferred decisions with their unblocking conditions; testing plan with the seven layers, a probe per question, and the profile’s checks | Yes: DD-1 to DD-5 with condition and owner; layers 1 to 7; probes Q-1 to Q-6; a check row for every profile result, P-18 added and P-1 brought to the hub’s increment 2 count |
10.3 Each decision on its own
| # | Requirement | Result |
|---|---|---|
| 1 | Every live decision carried by the domain specification, the testing plan or the implementation guide | Yes for 44 of 45; D-31, a decision to build no separate object, has nothing to carry; D-47 to D-52 are carried by the implementation guide’s steps 46 to 51. D-37 to D-39 are carried by profile P-20 and section 8; D-40 by section 0, section 8’s layer 1 and the test plan’s layer 1; D-41 by the test plan’s layer 4; D-42 by section 0 and section 8’s layer 1; D-43 by section 6.3 and the implementation guide; D-44 by sections 4, 6.3 and 6.5; D-45 by section 8’s layer 5 and the P-5 check; D-46 by the implementation guide and the production runbook |
| 2 | Each decision confirmed, against its map row, instance model, worked example, dictionary term and tests, to say what the decision says | Yes, on 2026-10-25: all 29 live decisions read beside every line that cites them in sections 2 to 8 and in the implementation guide. Four were reworded to match what was built: D-1 (the NORTHFIELD schema), D-3 (no UDM role taxonomy, against D-32’s role hubs), D-10 (the auditor checks that each key exists) and D-24 (the agent’s number). D-42 to D-46 on 2026-10-28, each read beside the passages that carry it in sections 0, 4, 6.3, 6.5 and 8 |