Course guide: Digest Insurance Distributors, and how this course works

Part A: the business

A1. Insurance in five terms

Each term is defined before it is used again. The insurance lexicon holds them all, with more detail.

• Carrier: an insurance company, also called the insurer. It issues the insurance contract, charges the premium for it (the price of the insurance, paid to keep the policy in force) [1], and handles and pays claims [1][2].
• Policy: the insurance contract, which sets out when and how the carrier will compensate the person it covers [1].
• Client: the person who buys a policy, called the insured or policyholder in insurance [1]. (The lexicon separates the insured, the owner and the beneficiary; this course says client until it needs those distinctions.)
• Agent: a person licensed to solicit applications for, or negotiate, insurance policies [3]. To sell a carrier’s policies, an agent needs that carrier’s official authorization, called an appointment [4].
• Insurance agency: a business that represents carriers and sells their insurance products [5]. The policies it sells remain the carrier’s contracts: an agent acts on the carrier’s behalf, and some agents, when a carrier authorizes it, may even issue or countersign its policies [6].

Notes

1. NAIC definitions of insurer, insured, policy and premium, as published in the Dictionary of Insurance: https://itsallwidgets.com/dictionary-of-insurance, read 2026-10-23.
2. AgentSync glossary (carriers hire underwriters, handle claims and issue payments): https://agentsync.io/glossary, read 2026-10-23.
3. North Carolina General Statutes, section 58-33-10 (definition of agent and producer): https://library.ncleg.gov/EnactedLegislation/Statutes/HTML/BySection/Chapter_58/GS_58-33-10.html, read 2026-10-23.
4. New Mexico Administrative Code 13.4.2 (definition of appointment): https://www.srca.nm.gov/nmac/nmregister/xxxv/13.4.2.pdf, read 2026-10-23.
5. Vertafore glossary of insurance terms (agency and carrier relationship): https://vertafore.com/glossary, read 2026-10-23.
6. IRMI, insurance definitions, agent: an agent solicits, negotiates or instigates policies on behalf of one or more insurers, as their legal representative: https://irmi.com/term/insurance-definitions/agent ; and Achievable, insurance licensing course, producer authority: a managing general agent may countersign insurance contracts when the insurer authorizes it: https://app.achievable.me/study/insurance-property-casualty/learn/general-insurance-concepts-marketing-systems-and-producer-authority. Read 2026-10-26.

A2. The company: Digest Insurance Distributors

The course follows one fictional company, Digest Insurance Distributors, called the distributor from here on. It is modeled on a real insurance distributor in the United States, simplified to the parts the course uses.

The distributor is not a carrier: it does not write policies. It works with many carriers and sells their policies through a large network of agents and insurance agencies, and it handles contracting and back-office work for them. It earns a share of the commission on each policy sold, the commission being what the carrier pays for a sale: for life insurance, a percentage of the premium, largest in the policy’s first year [6]; for Medicare plans, a fixed amount per enrollment that Medicare caps every year [7]. Section A3 explains each of these, with its sources. Figure A2-1 shows the business as a whole.

The policies it sells, in the parts of the course that use them:

Figure A2-1. The distributor’s business model. Each carrier contracts once with the distributor; the distributor brings in an acquired agency, which arrives with its own client records, and contracted independent agents, who do not; both sell policies, which are between the client and the carrier.

Notes

1. University Health, Medicare overview: https://www.universityhealth.com/patient-visitor-resources/patients/financial-assistance-health-coverage/medicare, read 2026-10-23.
2. Blue Cross and Blue Shield of Oklahoma, how you qualify for Medicare: https://bcbsok.com/medicare/basics/how-you-qualify, read 2026-10-23.
3. NAIC Life Insurance Buyer’s Guide: https://content.naic.org/sites/default/files/publication-lig-lp-consumer-life.pdf, read 2026-10-23.
4. New Mexico Office of Superintendent of Insurance, life insurance: https://www.osi.state.nm.us/en/consumer-assistance/insurance-types/life/, read 2026-10-23.
5. NAIC Buyer’s Guide for Fixed Deferred Annuities: https://www.nj.gov/dobi/division_insurance/pdfs/naicdeferredannuities.pdf, read 2026-10-23.
6. Sonant, life insurance commission rates explained: a percentage of premium, front-loaded into a large first-year commission followed by smaller renewal commissions: https://www.sonant.ai/blog/life-insurance-commission-rates, read 2026-10-26.
7. CMS, contract year 2026 agent and broker compensation memo: fixed maximum amounts per enrollment, initial and renewal (renewal at most 50 percent of fair market value), as published by Ritter: https://medicareful.ritterim.com/documents/cms-memos/memo-agent-broker-compensation-and-training-and-testing-requirements-cy2026.pdf, read 2026-10-26.

A3. Why a distributor exists

Picture the industry without one. Every carrier would have to recruit, contract, license-check, train and pay thousands of independent agents one by one, each selling a modest number of policies. Every agent would have to contract separately with each carrier whose policies they want to sell, and would rarely sell enough of any one carrier’s policies to earn its better commission levels. In practice, carriers contracting with independent agents one by one is unusual [1].

A distributor sits in the middle. It holds contracts with many carriers and brings agents in under those contracts; it does not employ the agents and does not write policies [2]. The industry calls such a firm a marketing organization, under several names that mean much the same thing; this course says distributor.

It earns an override: a share of each sale’s commission, paid by the carrier to the distributor on top of the agent’s commission. In one common arrangement the carrier splits the payment about 7 to 3 or 8 to 2, the agent keeping the larger share [3].

So the chain runs carrier, distributor, agency, agent, client, and each step in it exists because it is cheaper than the alternative (Figure A3-1).

Figure A3-1. The chain, and where the money goes. The policy is sold along the chain (solid arrows); the money takes a different route (dashed, marked $): the client pays the premium to the carrier, and the carrier pays the agent’s commission and the distributor’s override. This is the simplest common arrangement; depending on the contracts, commissions are sometimes paid through the distributor or the agency instead.

The same pattern in data. Without a middle, every carrier deals with every agency: N carriers times M agencies relationships, each with its own terms. With the distributor, each connects once: N plus M (Figure A3-2). Three carriers and three agencies show the shape: 9 contracts against 6. The gap grows with the network: 50 carriers and 2,000 agencies would need 100,000 direct contracts, against 2,050 through the middle. Data has the same problem and the same answer: a data integration layer that every source is mapped into once, and every report reads from once. This is a known pattern in data integration, the Canonical Data Model, from Hohpe and Woolf’s Enterprise Integration Patterns: a common model independent of any one application, so that adding a participant needs only one new translation, into the common model [4]. Their arithmetic counts translations among peers, every application talking to every other: six applications need 30 translations without the common model and 12 with it [4]. The distributor applies the same idea between two sides, carriers and agencies, and shows it is a business pattern as much as a data one. It is why, later in the course, we apply the Canonical Data Model to the vault: the vault keeps each agency’s records faithfully apart, and one common model in the middle integrates them, as the distributor does for the business.

Figure A3-2. Every-to-every against once-to-the-middle. The same three carriers and three agencies, connected directly (left) and through the distributor (right).

Notes

1. AgentSync, “Insurance 101: FMOs vs IMOs vs NMOs”, https://agentsync.io/blog/distribution-channels/insurance-101-fmos-vs-imos-vs-nmos, read 2026-10-23.
2. “What is an IMO in insurance?”, https://advisor-calculators.lovable.app/blog/what-is-an-imo-in-insurance, read 2026-10-23.
3. AgentSync, the same article as note 1.
4. Gregor Hohpe and Bobby Woolf, Enterprise Integration Patterns, the Canonical Data Model pattern: https://www.enterpriseintegrationpatterns.com/patterns/messaging/CanonicalDataModel.html, read 2026-10-23.
5. Centers for Medicare & Medicaid Services, Agent and Broker Training and Testing Guidelines: https://www.cms.gov/files/document/cy2026agentbrokertrainingtestingguidelinespdf.pdf, read 2026-10-23.

A4. How it grows, and why that is a data problem

The distributor grows in two ways. Most of its agents are independent: they sell under contract with the distributor and remain their own businesses, and their sales reach the distributor as policies placed through it, recorded in its own systems as they happen. It also buys businesses outright, insurance agencies and smaller distributors, and that is what creates the data problem: each acquired business arrives with its own client records, kept in its own system, with its own way of numbering clients and years of history behind them. One agency numbers its clients A-1001, A-1002 and onward; another uses entirely different numbers; a person who was a client of two agencies appears in both, under two unrelated numbers.

The company needs every acquired agency’s records in one place, with nothing lost and every record traceable to where it came from. That is the job of the data vault this course builds: a way of storing data that keeps every source’s records, unchanged, with when and where each arrived. Lesson 1 explains what it does and what it does not do. Data Vault has two layers: the raw vault, which keeps the data sources send, and the business vault, which applies business rules on top of it [1][2]. Until the course introduces the business vault, the vault means the raw vault.

The first acquired agency in the course is Sunbelt Senior Insurance. Its nightly client list is the first data the vault receives.

Notes

1. “Data Vault 2.0 on Azure” (the raw data vault and the business vault as the two sub-layers between raw data and information marts): https://thewindowsupdate.com/2023/06/30/data-vault-2-0-on-azure/, read 2026-10-23.
2. N-iX, Data Vault 2.0 overview (the business vault sits on top of the raw vault and applies business rules): https://www.n-ix.com/data-vault-2-0/, read 2026-10-23.

A5. The ten questions the vault must answer

The course has a consumer: the distributor’s director of data architecture and governance. Everything the course builds is meant, in the end, to answer her questions through an assistant she can ask in plain words. Here are the ten the course expects her to ask first, written before she has seen anything; her own questions, once she uses what is built, will replace or extend them. A policy can name several people in different roles: the owner, who holds the contract; the insured, whose life or health it covers; and the payor, who pays its premium.

1. How many distinct people are our clients, across every agency we have acquired?
2. Which clients came to us through more than one agency?
3. Which policies does each client hold, with which carriers, and in which roles: owner, insured or payor?
4. How much business does each carrier write through us?
5. Which agent wrote each policy, and where do our sources disagree about it?
6. Which independent agents sell through us without an agency?
7. Where did this client’s record come from, and when did we first know it?
8. What did we know about this policy on a given date, and what has changed since?
9. How many records arrive in a form we do not accept, and from which source?
10. When we acquire the next agency, how much of its book already overlaps ours?

They split in two. Questions about where a record came from, when it arrived and what changed (7, 8 and 9), and about who holds which policy with which agent (3 and 5), are what the vault keeps by design, from the first lessons on. Questions that need one person recognized across different numbers (1, 2 and 10) cannot be answered by the vault alone: they need the data integration layer of section A3, built later in the course. Questions 4 and 6 need a measure or a rule the sources do not carry yet. Each lesson moves at least one of the ten forward.

A6. What the course builds

Before the lessons take it apart piece by piece, here is the whole of what the course builds, in two pictures: the source data as it arrives, and the vault built from it. Both are entity relationship diagrams (ERDs). In an ERD each table is a box listing its columns, and each relationship is a line between two boxes. The marks at a line’s ends say how many rows on one side can meet one row on the other: a bar means exactly one, a circle means none at all is allowed, and the three-pronged foot, called a crow’s foot, means many. A line is dashed when nothing declares the relationship and it holds only where the values happen to match.

Figure A6-1 shows the source data as it lands [1]. Two sources come first: Sunbelt Senior, the acquired agency, sends its client list and its policy sales, and Northfield Life, a carrier, sends its policy data. A third, Pinecrest Mutual, another carrier, is the source of your labs. No source says how its rows relate to another table’s, so every line is dashed. A sale’s client number is found in the client list for 10 of the 11 client numbers the sales carry. A sale’s carrier code and policy number are found in Northfield Life’s data for 3 of the 9 Northfield policy numbers the sales name. The agent appears in every source, but only as a number, the national producer number. In insurance law, a person licensed to sell, solicit or negotiate insurance is called a producer, a word that covers agents [2]; the national producer number is the unique identifier the National Association of Insurance Commissioners (NAIC) assigns to a producer through the licensing process, to track producers nationally [3]. No source sends a list of agents, so the agent is drawn dashed too.

Figure A6-1. The source data as it lands: four tables from three sources, each with its real columns. Every relationship is dashed, because no source declares it.

Figure A6-2 shows the vault the course builds from that data [1]. It holds three types of table, and each type gets its own lesson; in one line each, a hub keeps business keys, such as client numbers; a link records which keys belong together, such as a client and a policy; and a satellite keeps one source’s details about a hub or a link, with their history. In the figure each table carries a plain name and its type in parentheses. The colors show which source fed each satellite, and the dashed satellite is what your labs add.

Figure A6-2. The vault the course builds: four hubs, three links and four satellites, named in plain words with each table’s type in parentheses. Simplified: the dated views the build adds beside each satellite are left out.

Notes

1. Verified: read from the course’s warehouse on 2026-10-26, the source tables in LANDING and the vault’s tables in the development tier; the matches counted the same day.
2. North Carolina General Statutes, section 58-33-10 (an insurance producer is a person required to be licensed to sell, solicit or negotiate insurance, and includes an agent and a broker): https://library.ncleg.gov/EnactedLegislation/Statutes/HTML/BySection/Chapter_58/GS_58-33-10.html, read 2026-10-26.
3. National Insurance Producer Registry, NPN lookup (the National Producer Number is a unique NAIC identifier assigned through the licensing application process and used to track producers on a national basis): https://pdb.nipr.com/html/PacNpnSearch.html, read 2026-10-26.

Part B: how this course works

B1. Four terms from software teams

The course is built the way a software team builds things, so four of its terms come from that world. Each is used only after this point.

• Repository: the place that holds all of a project’s files and stores each file’s revision history [1]. The course’s vault is built from one repository.
• Commit: one recorded change to one or more files, with its author and a short message describing the change [1].
• Branch: a parallel version of a repository, so work in progress stays apart until it is merged [1]. Each branch drifts from the other as commits land on it; a merge joins one branch’s commits into another. Finished work merges into a branch named qa, where it is tested, and is then promoted to the branch named main, which production builds from.
• GitHub: the website that hosts the course’s repository [1].

Notes

1. GitHub glossary (repository, commit, branch, pull request): https://docs.github.com/enterprise/2.0/user/articles/github-glossary, with the repository definition as adapted in https://pychron.readthedocs.io/en/latest/user_guide/glossary.html, read 2026-10-23.

B2. The pieces

B3. How a part runs

1. The teacher builds the part’s piece of the vault in the course’s repository, under the same delivery process a professional team follows: documents first, tests before code, then the build.
2. The textbook teaches it, with a question after each section that the learner answers to the teacher before opening its hidden answer; using rows from the tables that were actually built, so every example can be checked in the warehouse.
3. The learner reviews it. The work arrives as a pull request (lesson 8 of part 1 explains what that is). Reviewing it is the part’s final exam, graded by a published rubric: the grading rubric for pull-request reviews. Its five criteria and three grades are known before the review starts, and the teacher’s answer key is sealed before the review so it cannot change afterwards.

After each lesson from lesson 2 on, a lab: a small increment the learner builds in their own branch and environment, exercising that lesson, with the teacher’s code as template and the teacher’s tests written first. A lab passes when the teacher’s review approves its pull request and it merges. The labs carry a second carrier, Pinecrest Mutual, into the same vault, so the learner’s increments and the teacher’s form one vault. From time to time a defect drill: the teacher introduces a defect into the shared branch qa and reports only its symptom, and the learner finds it, writes the test that catches it, and fixes it. Each lesson is read one at a time; the learner’s questions while reading become edits, applied together at the lesson’s end, before the next lesson is written.

B4. The five delivery documents

“Documents first” means five documents for each branch of work, written in this order and each agreed by the learner before the next begins. The course’s delivery process defines them, and lesson 8’s review reads them first.

The profile opens with an entity relationship diagram of the sources as they land, and the design with one of what it builds, each drawn before any analysis or decision (A6 shows both kinds).

The order is iterative, not one-way: when a later document finds a gap in an earlier one, the earlier one is amended with a decision that resolves it, and agreed again before anything builds on it.

B5. Three engineers, each with an environment

The course runs the way a professional team does, with three engineers: the teacher, who is the lead developer; the learner; and Kimi, a reviewer and second developer for business vault work. Each has their own environment: their own copy of the source data and their own vault database, separated from the others and from production (Kimi’s is being set up). The learner’s labs are increments of the same vault that complement the teacher’s: each adds what the other does not have, and both merge into qa. The learner’s environment builds the whole vault, the teacher’s increments and the learner’s own, so a review can check both against real rows. The engineers talk in the comments of Linear sub-issues, each comment signed. To start sooner, the learner’s branch began as a copy of the teacher’s, a one-time exception; once the teacher’s work is merged into qa, it is rebased onto qa.

B6. Every stall improves the course

If a sentence stops the learner, whether a term used before it was explained, a passage that needs rereading, or a step carrying too much at once, that is a defect in the course, not in the reader. The learner says so in their own words; the teacher records it in the course’s acceptance log, fixes it where it lives, and checks the rest of the course for the same problem. By the end, the course has been tested by its first learner, line by line.

Part 1, lesson 1 of 8: what the vault does and does not do

1. One real instance

Digest Insurance Distributors, the company the course guide introduces, has acquired a small insurance agency, Sunbelt Senior Insurance. Every night Sunbelt Senior sends its client list as source data, one row per client. Two nights of it have arrived, on October 1 and on October 2, and the course’s vault has been built from them. Here are three of the rows Sunbelt Senior sent [1]:

Irene Garrity appears twice. On October 1 her client number was typed A-1007. On October 2 it was typed A1007, without the hyphen. Pia Esposito arrived with no client number at all.

Here is what the vault kept for Irene [2]:

The vault kept two client numbers for Irene, each with the moment it first arrived and the source it came from. For Pia it kept no client number, because none was sent. Across both nights Sunbelt Senior’s client list sent 43 rows, and from them the vault kept 34 distinct client numbers [3]. A separate check built alongside the vault reported A1007 as a client number not written in Sunbelt Senior’s usual form [4]. The check reported it; it did not change it.

2. What the vault does

Three terms describe what happened, each defined here before it is used again.

• Source: the system a row comes from. Here there is one source, Sunbelt Senior’s system, which the vault identifies by a short permanent code, SSI.
• History: the record of when each thing arrived. In this part of the course, history means the moment a client number was first seen; later parts add how a client’s details change over time.
• Lineage: the record of where each thing came from. Every client number the vault keeps carries the code of its source.

So the instance shows the vault’s whole job, stated generally: the vault keeps every client number each source sends, with its history and its lineage, and it never changes or removes what it has kept [5]. A-1007 and A1007 are both kept, exactly as written, each with the time it arrived and the source that sent it.

3. What the vault does not do

The vault does not decide that A-1007 and A1007 belong to the same person. It cannot: the two numbers are written differently, and nothing in the source data says they refer to one client. A person looking at the rows sees the same name and suspects a typo; the vault sees two client numbers.

The same holds more widely. The vault does not combine Sunbelt Senior’s clients with another agency’s clients, and it does not correct values it believes are wrong. All three are decisions about what the data means, and the people who wrote the Data Vault method place every such decision after the vault, not inside it [6].

This is the question the course keeps returning to. It is tempting to think of the vault as the place where data from many sources comes together. What it actually does is keep each source’s data apart, faithfully and with its history and lineage. Deciding that two kept records describe one client, and combining them, is separate work that comes after.

4. A worked example

The names here are invented. An agency sends Rosa Diaz as client A-2001 on Monday. On Wednesday its source data lists her as A2001. The vault keeps both client numbers, A-2001 first seen Monday and A2001 first seen Wednesday, both from the agency’s client list. If an analyst counts the vault’s clients, Rosa is counted twice. Nothing is lost and nothing is wrong in what the vault kept; what is missing is the decision that A-2001 and A2001 are one person, and that decision is not the vault’s to make.

5. What to keep from this lesson

The vault keeps every client number each source sends, exactly as sent, with history (when it arrived) and lineage (where it came from). It does not decide what belongs together. When the same client arrives under two numbers, the vault keeps two.

Notes

1. Verified: read from the course’s warehouse on 2026-10-23 (the source table SUNBELT.CLIENT and the vault’s client hub). Re-read on 2026-10-23 at 18:41 UTC after record sources became codes, and on 2026-10-25.
2. Verified: the same query as note 1.
3. Verified: counted in the course’s warehouse on 2026-10-23, and again on 2026-10-25. By then the client hub held 35 client numbers under SSI: these 34, and A-1033, which reached the vault from Sunbelt Senior’s policy sales data in a later part of the course.
4. Verified: read from the course’s warehouse on 2026-10-23 (the findings the check recorded).
5. N-iX, Data Vault 2.0 overview (a hub loads by one rule, inserting a business key only if it does not already exist, and once a key exists its row never changes): https://www.n-ix.com/data-vault-2-0/, read 2026-10-23; and the course’s client hub, verified as in note 1.
6. VaultSpeed, Data Vault 2.0 concepts (the business vault applies soft business rules, transformations and logic): https://docs.vaultspeed.com/space/VPD/3011870744.md; “Data Vault 2.0 on Azure” (a business vault entity exists only if a business rule needs to be applied): https://thewindowsupdate.com/2023/06/30/data-vault-2-0-on-azure/; waitingforcode, Data Vault 2 in big data (the same-as link, which links a concept written differently to its correct definition, is a business vault construct): https://www.waitingforcode.com/general-big-data/data-vault-2-big-data/read. All read 2026-10-23.

Part 1, lesson 2 of 8: where data comes from

1. One real instance

Sunbelt Senior Insurance’s client A-1001 is Rosa Alvarez. She was in both nights’ client lists, so the warehouse holds two rows for her [1]:

The two rows are identical except for one column: LOADED_AT, the time Sunbelt Senior’s system stamped on each night’s rows. It arrives as text. Altogether October 1 brought 33 rows and October 2 brought 10 [2].

The vault’s first step, which lesson 3 names and explains, reads these rows and adds two columns to every one of them [3]:

LDTS holds the same moment as LOADED_AT, now stored as a real timestamp instead of text. RSRC holds the same short code on every row, SSI: a permanent code for the source the row came from. Looking SSI up in the source system seed gives its name today, as the seed spells it: Sunbelt Senior client file [4].

2. Three terms

• Source table: a source’s data as it landed in the warehouse, unchanged. Here it is Sunbelt Senior’s client list, landed as the table SUNBELT.CLIENT in the landing database, one landed row for every row sent each night. Nothing in it is corrected or combined.
• Load timestamp: the moment a row arrived, carried on the row itself. In the vault it is the column LDTS [5]. It is lesson 1’s history made concrete: the vault knows when something first arrived only because every row carries the moment it was loaded. In this course the load timestamp is the time Sunbelt Senior’s system stamped each night’s rows, converted from text to a timestamp in UTC, so every row from the same night carries the same moment.
• Record source: a permanent code for the source a row came from, carried on the row itself. In the vault it is the column RSRC [6]. It is lesson 1’s lineage made concrete: every row carries the code of the source it came from. The code never changes, even when the source’s name does; the name lives in one place, the source system seed, which lesson 7 explains. That way a renamed source changes one row of the seed, and no row in the vault.

3. Why every row carries both

Later, rows from different nights, and in later parts from different sources, end up side by side in the same vault tables. Once rows from many deliveries sit in the same table, there is nothing else to look at: the only way to know where and when a row came from is to read it from the row itself. So each row carries its own load timestamp and record source from the moment it enters. Rosa’s two rows show it: the same client, the same details, told apart only by their load timestamps, and both traceable to Sunbelt Senior by their record source, SSI.

4. A worked example

The names here are invented. A dental plan sends its member list every Monday and Thursday. Member M-77, Ana Ruiz, is in both deliveries of one week. The source table holds two rows for her. After the vault’s first step, both rows carry the record source DPM, the dental plan’s code; one carries Monday’s load timestamp and the other Thursday’s. Six months later, an analyst who finds Ana’s Monday row in a vault table can still say exactly which source it came from and when it arrived, without asking anyone. If the dental plan is renamed in the meantime, the rows still say DPM, and the source system seed gives the new name.

5. What to keep from this lesson

A source’s data lands unchanged in a source table. On the way into the vault, every row gains two columns: its load timestamp (LDTS, when it arrived) and its record source (RSRC, a permanent code for where it came from, named in the source system seed). They are history and lineage, written on each row.

Your lab. Lesson 2 has a lab: Lab 1-2 in the workbook, declaring a second carrier’s landed table as a source of the vault. Open it here: https://claude.ai/artifact/ELCnMhz2GPtV6AiuzW5rzB#lab-1-2

Notes

1. Verified: read from the course’s warehouse on 2026-10-23, and again on 2026-10-25.
2. Verified: counted in the course’s warehouse on 2026-10-23.
3. Verified: the same query as note 1, re-read on 2026-10-23 at 18:39 UTC after record sources became codes.
4. Verified: the source system seed, read on 2026-10-23.
5. datavault4dbt documentation, staging macro: the ldts column holds the load date timestamp (https://github.com/rayserrano2735/datavault4dbt/blob/694df249/docs/01_macro-instructions/02_staging/02_staging.md); Red Gate, Data Vault modeling basics: the load date tells the first time the data warehouse knew about a business key (https://www.red-gate.com/blog/data-vault-series-data-vault-2-0-modeling-basics/). Read 2026-10-23.
6. datavault4dbt documentation: the record source column gives information about where the source data is coming from (https://github.com/rayserrano2735/datavault4dbt/blob/694df249/docs/01_macro-instructions/01_documentation/01_documentation.md). Read 2026-10-23.

Part 1, lesson 3 of 8: the vault’s first step

1. One real instance

Lesson 2 showed Rosa Alvarez’s two rows gaining a load timestamp and a record source. Rows with other client numbers show what else happens on the way into the vault. Here are five rows of Sunbelt Senior’s client list as they landed in the source table SUNBELT.CLIENT, each client number shown between brackets so that a space after it is visible [1]:

Row 37 is A-1004 typed in lower case. Row 38 is A-1005 with a space after it. Row 32 sent an empty value, and one more row, row 41 on October 2, sent three spaces and nothing else.

Here are the same rows after the vault’s first step [2]:

Three things happened. The space after A-1005 is gone. The empty value became no value at all, and row 41’s three spaces did the same. And every row gained a new column, the hash key: rows 4 and 37 share one, rows 5 and 38 share another, although each pair was written two ways.

2. Four terms

• Business key: the identifier a source’s business uses for a thing, the way its people use it. Here it is the client number: the vault’s whole job, in lesson 1’s words, is to keep every client number each source sends. The toolkit that builds this course’s vault takes business keys as the input of everything that follows [3].
• Staging: the vault’s first step, one per source table, which reads the landed rows and prepares them for the vault without changing what they mean [4]. Here it is the view stg_sunbelt_client, over SUNBELT.CLIENT. It holds 45 rows: the 43 rows Sunbelt Senior sent, and two rows the vault adds itself, which lesson 5 explains [2].
• Hard rule: a change to how a value is stored that leaves its meaning alone: casting text to a timestamp, trimming spaces, turning an empty value into no value. The raw vault applies hard rules and nothing else; changes of meaning, called soft rules, belong to the business vault after it [5]. Lesson 2’s load timestamp, converted from text to a timestamp, was a hard rule.
• Hash key: a code of fixed length computed from a business key, which the vault then uses to identify that key [3]. Here it is 32 characters computed from the client number by a standard algorithm, MD5 [6].

3. How the hash key is made, and what it ignores

The toolkit trims the spaces around the client number, writes it in capitals, puts it between quotation marks and computes its MD5 [6][7]. For A-1001, Rosa’s number, that is the MD5 of “A-1001”, which is 2eec4d22ff4ec7df061cc883ac8d1b04; Rosa’s row in the vault carries exactly that key [8]. Any reader can repeat the calculation.

Because the number is written in capitals before it is hashed, a-1004 and A-1004 give one key; because spaces around it are trimmed, A-1005 with a space and A-1005 without give one key. Both are hard rules: letter case and surrounding spaces do not change which client a number names, and the toolkit is set to treat business keys that way [6]. The hash key does not remove the hyphen, though. A1007 and A-1007 give two keys, as lesson 1 showed, because deciding that they name one client is a decision about meaning, and that is not the raw vault’s to make.

The empty values needed a rule of their own. Without it, an empty client number would be hashed like any other value and become a key that looks real but names no client. When the course first built staging without the rule, a test showed exactly that: the empty value hashed to a key beginning 9d4568c0 [9]. With the rule, staging turns an empty or space-only client number into no value first, and every row with no client number receives the same reserved key, 32 zeros. What the vault does with that key is lesson 5’s subject.

4. A worked example

The names here are invented. A dental plan sends member M-77, Ana Ruiz, on Monday. On Thursday its member list writes her number as m-77, and on Friday as M-77 with a space before it. Staging gives all three rows one hash key, because letter case and surrounding spaces are ignored. A week later the plan sends M77, without the hyphen: staging gives that row a different key, and the vault keeps it as a second member number until someone decides, outside the raw vault, that the two are one member. A row with an empty member number receives the key of 32 zeros.

5. What to keep from this lesson

Staging is the vault’s first step: one per source table, it applies hard rules only, which change how values are stored and never what they mean. It computes each row’s hash key from its business key, ignoring letter case and the spaces around it, and nothing else. Two spellings that differ in more than that stay two keys.

Notes

1. Verified: read from the course’s warehouse on 2026-10-25 (the source table SUNBELT.CLIENT in the developer landing database).
2. Verified: the staging view stg_sunbelt_client, read on 2026-10-25 and joined to the source table by row and load time.
3. datavault4dbt documentation, standard hub: the hash key is the hub’s primary key, and the business keys are the input of the hash key: https://github.com/rayserrano2735/datavault4dbt/blob/694df249/docs/01_macro-instructions/06_hubs/06_standard-hub.md, read 2026-10-25.
4. datavault4dbt documentation, staging: the staging layer is mainly for hashing, one stage is created per source table, and it is not there to harmonize data: https://github.com/rayserrano2735/datavault4dbt/blob/694df249/docs/01_macro-instructions/02_staging/02_staging.md, read 2026-10-25.
5. VaultSpeed, Data Vault 2.0 concepts (hard business rules are type casting, formatting and null handling; the raw vault applies no transformations beyond hard rules; the business vault applies soft business rules): https://docs.vaultspeed.com/space/VPD/3011870744.md, read 2026-10-25.
6. datavault4dbt project settings, at the version the course pins: the hash algorithm is MD5, and hash key input is not case-sensitive: https://github.com/rayserrano2735/datavault4dbt/blob/694df249/dbt_project.yml, read 2026-10-25.
7. The branch design (Pilot knowledge 35 item 270), section 6.3, the hash rule step by step, read from the toolkit’s code on 2026-10-22.
8. Verified: MD5 of “A-1001” computed in the course’s warehouse on 2026-10-25 and compared with the client hub’s row for A-1001.
9. The branch post-implementation guide (Pilot knowledge 35 item 273), section 3: unit test P7, red on 2026-10-23, before the rule was added.

Part 1, lesson 4 of 8: the hub

1. One real instance

Lesson 3 ended with every row of Sunbelt Senior’s client list carrying a hash key. Here are six of those rows, with their client numbers as sent [1]:

And here is what the vault’s client table holds for those numbers [2]:

Six rows became four. A-1001 arrived twice and has one row, dated October 1. A-1004 arrived again on October 2, spelled a-1004, and nothing changed: the row still shows October 1 and the spelling first sent. A-1031 arrived only on October 2 and has a row dated October 2. A-1010 arrived twice in the same night, spelled two ways, and has one row.

2. Two terms

• Hub: the vault table that holds one row for each distinct business key a source has sent, and nothing else about it: the hash key, the business key as first seen, the load timestamp of its first arrival and the record source that first sent it [3][4]. The table above is the client hub, client_h. Its four columns are exactly those four things.
• Insert-only: a hub adds a row only for a key it does not already hold; once a row is in, it is never changed or removed [5]. A key’s second arrival adds nothing, so the load timestamp a hub row carries is the moment the vault first knew that key [6].

This is lesson 1’s job, built: the hub keeps every client number Sunbelt Senior sends, with its history (first arrival) and its lineage (record source), and never changes what it has kept.

3. What “first” means, and when the data cannot say

On October 1, Sunbelt Senior’s list carried A-1010 in row 10 and a-1010 in row 33. Both spellings have one hash key, both arrived in the same night with the same load timestamp, and the hub must keep one row. Both are first. The hub keeps one of the two spellings, and nothing in the data decides which: in the course’s first build the hub held a-1010; rebuilt from the same rows on 2026-10-25, it holds A-1010 [7]. Either way the key is the same, and so is everything the vault will later attach to it.

The spelling a hub keeps can therefore be a typo, whenever two spellings of one key arrive together. That is a known property of the course’s vault, disclosed in its delivery documents, not a fault in either build [7].

4. Counting the hub

Across both nights, Sunbelt Senior’s client list sent 43 rows. They give 34 hub rows: 30 client numbers first seen on October 1, and four first seen on October 2, A-1031, A-1032, A1007 and A-01008 [8]. The other nine rows were second arrivals of numbers the hub already held, spellings that hash to a key it already held, or rows with no client number, which lesson 5 explains. The hub holds a few more rows than these 34: two the vault adds itself, which lesson 5 also explains, and in part 2, numbers arriving from another source.

5. A worked example

The names here are invented. The dental plan sends member M-77, Ana Ruiz, on Monday; the member hub gains one row: M-77, its hash key, Monday’s load timestamp and the code DPM. On Thursday Ana is sent again and nothing changes. On Friday the plan sends M77, without the hyphen: its hash key is different, so the hub gains a second row, dated Friday. The hub now holds two member numbers for one person, each with its first arrival, and will hold them both for as long as the vault exists.

6. What to keep from this lesson

A hub holds one row per distinct business key: its hash key, the key as first seen, when it first arrived and which source first sent it. It is insert-only, so later arrivals add nothing and nothing kept ever changes. When two spellings of one key arrive together, the hub keeps one, and the data does not decide which.

Notes

1. Verified: read from the course’s warehouse on 2026-10-25 (the source table SUNBELT.CLIENT).
2. Verified: the client hub client_h, read on 2026-10-25.
3. datavault4dbt documentation, standard hub: a hub is built from stage models, its hash key is its primary key, and its business keys are loaded into it: https://github.com/rayserrano2735/datavault4dbt/blob/694df249/docs/01_macro-instructions/06_hubs/06_standard-hub.md, read 2026-10-25.
4. Verified: the columns of client_h, read from the warehouse’s information schema on 2026-10-25.
5. N-iX, Data Vault 2.0 overview (a hub loads by one rule, inserting a business key only if it does not already exist, and once a key exists its row never changes): https://www.n-ix.com/data-vault-2-0/, read 2026-10-23.
6. Red Gate, Data Vault modeling basics (the load date tells the first time the data warehouse knew about a business key): https://www.red-gate.com/blog/data-vault-series-data-vault-2-0-modeling-basics/, read 2026-10-23.
7. The branch post-implementation guide (Pilot knowledge 35 item 273), section 5, known characteristics, corrected on 2026-10-25 when the rebuilt hub held A-1010; and the branch design (Pilot knowledge 35 item 270), section 6.3: the hub keeps the first sighting.
8. Verified: counted in the client hub on 2026-10-25, among the rows whose record source is SSI.

Part 1, lesson 8 of 8: reviewing a pull request

1. One real instance

Everything built for part 1 lives on one branch of the course repository, claude_ws/dig-574-arrival-and-identity. On 2026-10-23 a request was opened on GitHub, numbered 1, asking to add that branch’s work to the branch qa, where the course’s finished work collects. When it was opened, the request carried 29 commits touching 36 files [1]. The course’s rule is that none of that work reaches qa until the reviewer approves it. GitHub does not enforce that rule here: the qa branch has no protection rule, and a repository’s owners can merge a pull request even without an approving review [2][3]. The person who decides is the reviewer, and for part 1 the reviewer is the learner. Request 1 was later closed and reopened as request 2 from the teacher’s own GitHub account, because GitHub does not let the author of a request approve it [3], and request 1 had been opened under the learner’s account. Request 2 has since grown: by 2026-10-25 it carried 58 commits touching 70 files, because the same branch also holds the work of later parts [6]. Then the course met a real-life pressure. The product’s first consumer could ask at any moment, so its first release was expedited for time to market: on 2026-10-26 the owner, who is also the learner, merged request 2 into qa without the review [7]. Owners can do that, and the rule exists to keep it rare and deliberate. The second release returns to the planned process: its request is reviewed before it merges, and that review is part 1’s exam.

2. Two terms

• Pull request: a request to add one branch’s changes to another branch, shown on a page where the changes can be read, discussed and approved or refused [4]. Request 1 above is a pull request from the part 1 branch into qa.
• Diff: the line-by-line difference between the branch and the branch it would join: every line added, removed or changed, file by file [4]. Request 1’s diff covered its 36 files when it opened; request 2’s covers 70.

Approving a pull request means: I have read this diff, I understand what it does and why, I have checked it against real data, and I accept it into qa.

3. How to read a pull request

The delivery process’s code review standard sets the order, and the order matters:

Step 3 is why the course gave the learner a separate developer environment: a reviewer builds the branch and queries the results, instead of trusting the author’s report.

4. How the course grades a review

• Rubric: a published list of the criteria a piece of work is graded against, known before the work is done.

The course’s rubric for pull-request reviews has five criteria:

It has three grades. Pass with distinction: all five criteria, plus a valid finding the teacher did not expect. Pass: all five criteria, with most expected findings caught. Not yet: a criterion missed; the grading names the lessons to reread and the learner reviews again, a normal outcome.

5. Why the answer key is sealed

Criterion 4 needs a reference: the teacher’s list of findings a strong reviewer should catch, called the answer key. The teacher writes it before the review and posts a fingerprint of its exact text (a SHA-256 hash) on the course’s master issue. At grading the key is opened and fingerprinted again. If even one character changed, the fingerprints would, with very high probability, differ [5]. So the teacher cannot add findings after seeing the review, and the learner knows the grading was fixed in advance.

6. A worked example

The names here are invented. A pull request adds a second agency’s client list. The documents say every client number is kept with its arrival time. In the diff, the reviewer sees a test that requires exactly 52 rows. In the data, the reviewer’s own build shows 52 rows, so the test passes. A strong review still reports the test: it will fail the day the agency sends a third night’s rows, because the expected count is written into the test instead of read from the data. That finding is criterion 4 at work: the build passed, and the reviewer found a defect anyway.

7. What to keep from this lesson

Approve a pull request only after reading the documents, then the diff, then your own data. The course grades that review by a rubric known in advance, against an answer key sealed before you start.

Notes

1. Verified: GitHub’s reply when the request was opened, 2026-10-23 at 12:35 UTC.
2. Verified: GitHub’s API for this repository, read 2026-10-23 (no branch protection and no rulesets on qa).
3. GitHub documentation, approving a pull request with required reviews (authors cannot approve their own pull requests; owners and administrators can merge without an approving review): https://docs.github.com/en/pull-requests/collaborating-with-pull-requests/reviewing-changes-in-pull-requests/approving-a-pull-request-with-required-reviews, read 2026-10-23.
4. GitHub glossary (pull requests are proposed changes accepted or rejected by a repository’s collaborators; the diff shows what was added or removed): https://docs.github.com/enterprise/2.0/user/articles/github-glossary, read 2026-10-23.
5. NIST, FIPS 180-4, Secure Hash Standard (any change to a message will, with a very high probability, result in a different message digest): https://csrc.nist.gov/files/pubs/fips/180-4/final/docs/fips180-4.pdf, read 2026-10-23.
6. Verified: GitHub’s API for this repository, read 2026-10-25 (request 1 closed unmerged on 2026-10-23 at 13:37 UTC; request 2 open, 58 commits, 70 files).
7. Verified: GitHub’s API for this repository, read 2026-10-27 (request 2 merged into qa by rayserrano2735 on 2026-10-26 at 20:03 UTC, with no review). The decision to expedite: Ray, 2026-10-26, for time to market; the second release follows the full process, Ray, 2026-10-27.

Appendix 1: Questions for a Data Vault architecture conversation

The course’s conclusions, gathered in one place: how to frame the course’s argument, the questions it leads to, the terms they use, and how far the course’s own vault has been built.

The framing, in two sentences

Data Vault keeps every source’s history faithfully, with full lineage; on its own it does not decide what a client, a policy or a status is across sources. This course finishes an approach John Giles began, the Universal Data Vault: hubs built from universal data model patterns (appendix 2), completed with the patterns Giles set aside (party, party role, party relationship and role within a transaction), as Silverston’s universal data model patterns define them. It is the Canonical Data Model applied inside the vault: one common model for what things are, which every source is translated into once.

Why “canonical” matters: the Canonical Data Model is Hohpe and Woolf’s pattern from Enterprise Integration Patterns. With a common model, adding a participant needs only one new translation; by their count, six applications need 30 translations without one and 12 with it.

The ten questions

Ask them as an architect learning how a team handles each case, not as objections.

The likely objection: performance

The answer: query speed is the marts’ job, in this design as in Data Vault. The data integration layer’s cost is the build, and the expensive joins are built in stages as materialized steps, incremental with change data capture, run in parallel.

Notes

1. Navicat, Designing better databases with Data Vault 2.0: https://www.navicat.com/company/aboutus/blog/3970-designing-better-databases-with-data-vault-2-0-and-navicat-data-modeler-4, read 2026-10-23.

Areas for further study

Four directions this course is exploring, offered as open questions rather than settled positions.

Terms, checked against the method’s sources

How far the course’s vault is built

• Built and verified: Sunbelt Senior’s client file, staged, with a client hub of 34 keys, and every row carrying its load timestamp and record source.
• Being designed now: Northfield Life’s carrier file, client and insured role hubs, a policy hub keyed by the carrier’s number, the client-policy link and one satellite per source.
• Read so far: the course guide and lessons 1 and 2.

Prior art: the Universal Data Vault

John Giles published a way to combine Silverston’s universal data model patterns with Data Vault and named it the Universal Data Vault [1]. It puts the patterns into the vault itself: generalized hubs such as Asset, Activity and Agreement hold every subtype, each subtype a row in a hand-made type table, with the type and the business code together making a key unique. He set party and party role aside as a possibly contentious topic, adding that the UDV structures could accommodate the alternative modeling choices [2].

This course builds on that design rather than departing from it. Its hubs are universal too, and its contribution is the part Giles left aside: party, party role, party relationship and role within a transaction, taken from Silverston’s universal data model patterns. Its first example is one hub for people, keyed by issuer and identifier, with a person-policy link that carries the role.

Notes

1. John Giles, Universal Data Vault, Part 1, TDAN: https://tdan.com/?p=19072, read 2026-10-24.
2. John Giles, Universal Data Vault, Part 2, TDAN: https://tdan.com/?p=19245, read 2026-10-24.

Appendix 2: Study of John Giles’s Universal Data Vault

1. Who he is and what he set out to do

Giles is an independent consultant in information architecture, working in IT since the late 1960s, a Fellow of the Australian Computer Society, and the author of The Nimble Elephant: Agile Delivery of Data Models Using a Pattern-based Approach (Part 1, author note). He asks whether combining universal data model patterns with Data Vault keeps the benefits of both or cancels them out (Part 1, opening).

2. The case that produced it

• A government order merged 83 related Australian organizations into one on July 1, each with about 5 core systems of about 20 central entities: an estimated 8,000 core tables (Part 1).
• He first built a pattern-based enterprise logical data model (ELDM), after a one-day patterns course for business and IT people; the business shaped and owned it, as specializations of generic patterns (Part 1).
• The data warehouse had to consolidate the history of all 83 organizations, serve reporting and operational queries, and give one consistent view of a client who appeared in several of them (Part 1).
• Earlier Kimball-style marts had cross-domain problems, and an Inmon-style warehouse was under way; he proposed Data Vault for auditability and adaptability, and a skeptical senior developer later found it lived up to its claims (Part 1).
• His earlier experience: he built a telecommunications ELDM in two weeks by starting from one of Silverston’s industry patterns (Part 1).

3. The dilemma

• Universal patterns are deliberately generalized, and Silverston and Agnew’s third volume notes that the generalized forms are what is usually implemented (Part 1).
• Data Vault practitioners usually recommend hubs that avoid high generalization, at the level the business speaks (Part 1).
• People speak at different levels: his wildfire example runs from “deployable resources” through “fire trucks” and “aircraft” to “water tankers”, “slip-ons”, “helicopters” and “fixed-wing planes” (Part 1).
• With one agreed level, standard hubs work. In the merged organization there was no single level; each group wanted its own (Part 1).
• Two mandates followed: the vault must accept back-loaded history from all 83 organizations without hundreds of hubs, and it must align with the one generalized ELDM shared by the service bus, master data, reference data and business rules work (Part 1).
• He notes the standard alternative, a hub per level joined by same-as links recording inheritance, and says it should be considered, but that here it risked hundreds of hubs (Part 1).

4. The design

5. What he set aside and what he found hard

• Party and party role: a possibly contentious topic he leaves aside, saying other modeling choices fit the UDV structures (Part 2).
• Business keys: hard to find in regular Data Vault, harder for generalized hubs (Part 2).
• Visibility: structure in type tables is less familiar to many modelers (Part 2).
• A cautionary story via Graham Witt about bottom-up modeling, a school database that assumed parents share a student’s address, with serious consequences; he argues starting from generalized patterns would have exposed it (Part 2).

6. His results and guidance

• He reports that over 90 percent of a colleague’s most complex sample data fitted the patterns, and that the first project’s success led a former client employee to ask him to repeat it elsewhere (Part 2).
• His guidance: if the enterprise has one sweet spot on the generalization continuum, use standard Data Vault; if a few focal points, use hubs for each with same-as links; if it values a multi-level hierarchy, especially pattern-based, consider the UDV (Part 2).

7. The course’s reading (the course’s reasoning, not Giles’s)

1. The course’s position is universal hubs inside the vault, as Giles’s are; it finishes his approach rather than offering an alternative. Provisional: this rests on the two articles; his later Data Vault book (section 9) includes a Party pattern, and until it is read the course does not claim he left party unfinished. Increment 2’s design shows it: one hub for people keyed by issuer and identifier (type-plus-code uniqueness, in Giles’s terms), the policy hub qualified by carrier, and the person-policy link carrying the role, close to his link types that name each participant’s role.
2. What it adds: party, party role, party relationship and role within a transaction, from Silverston’s universal data model patterns, the area Giles set aside. By his own words the UDV structures can hold those choices, so adding them completes rather than corrects him.
3. What he solves and the course keeps: specialization as rows, not new hubs, which partly answers the course’s contradictions 4 and 9.
4. His hub type tables have no source or load date; the course’s source system seed makes the same move.
5. Open: contradictions 1, 5 and 8 say entity decisions made at load are costly when wrong. Universal hubs reduce how often a new hub is needed, but still decide at load which pattern and type a row belongs to. How the course answers that is not settled.

Dated note: an earlier reading (2026-10-24) held that the universal model should stay outside the raw vault as a separate canonical layer. Ray reopened it the same day (“what we’re exploring is universal hubs”), and the course’s position became finishing Giles’s approach.

8. Not settled by the articles

• The diagrams: both articles include sample models as images, which this study could not read; their exact columns and keys are unknown here.
• How his generalized hubs choose a business key across subtypes, beyond type plus code.
• Whether he later revised or extended the UDV: his 2019 Data Vault book, section 9, is the place to look; not yet read.
• Whether the UDV handles source history before the vault (storage), which the articles do not discuss.
• How his design behaves when a source’s entities drift, which the articles do not discuss.

9. His books, and what to read first

Giles has three books: The Nimble Elephant (2012), on data model patterns in agile delivery; The Elephant in the Fridge (2019, 290 pages), on Data Vault built from business-centered models; and The Data Elephant in the Board Room, on an enterprise-wide conceptual model he calls the Data Town Plan [3][4]. The Elephant in the Fridge is the one that matters here: its four tasks are defining how the business sees its data, designing the Data Vault from that view, mapping sources to the vault bottom-up, and defining the business rules; it closes with summaries of controversial Data Vault topics and an appendix of patterns that can be added to a Data Vault, such as Party and Agreement [4][5].

Reading list, in order of value for this course:

Read on the Kindle; highlights export from the reader’s notebook, and each finding is added here cited to chapter.

Notes for section 9

3. Technics Publications, The Data Elephant in the Board Room: https://technicspub.com/data-elephant/, read 2026-10-24.
4. Technics Publications, The Elephant in the Fridge, contents: https://technicspub.com/fridge/, read 2026-10-24.
5. Steve Hoberman, The Book Look: The Elephant in the Fridge, TDAN: https://tdan.com/?p=25041, read 2026-10-24.

10. First finding from The Elephant in the Fridge

In chapter 2, “Untangling ‘raw’ and ‘business’ Data Vault,” Giles separates two questions: whether a hub, link or satellite’s structure reflects the business view or a raw source view, and whether each instance, by its record source, came from a raw source feed or from a business rule. His example is an Employee hub holding instances from payroll and HR feeds alongside contractors added by a business rule: neither a “raw” nor a “business” hub. He suggests avoiding both phrases, and noting structure and sourcing instead. For integrating satellites, he describes one source-shaped satellite per feed plus a business-centric “conformed” satellite.

For further study: the course’s appendix 1 asks whether that conformed satellite could take the UDM’s typed rows instead of columns.