dv-discover

name	dv-discover
description	Analyze source tables or schema descriptions and propose a Pragmatic Data Vault model (hubs, links, satellites)
enabled	true

/dv-discover — Source Discovery & Vault Model Proposal

Analyze one or more source systems and propose a Pragmatic Data Vault model. Works from table names, DDL, CSV headers, or plain-language descriptions.

Input

The user may provide:

• DDL (CREATE TABLE ...)
• A list of table names with column names
• A CSV header row
• A plain-language description ("we have a CRM with customers, orders, and products")
• A path to a SQL or schema file

If the user provides nothing, ask:

"What source tables or systems are you modeling? You can paste DDL, column names, or just describe what you have."

Steps

1 — Spawn the Source Profiler subagent

Read agents/source-profiler.md for the full system prompt.

Pass the user's source description as input. Ask the Profiler to return a structured report with:

• For each table: candidate business keys (with uniqueness rationale), relationship signals, row-level behaviour (insert-only vs. updates), PII indicators
• Cross-table relationships detected
• DDD context mapping for each source (see step 1.5 below)

1.5 — Classify each source against DDD context mappings

For each source system identified, determine which Domain-Driven Design (DDD) context mapping applies. This shapes how the source is modelled and loaded:

Pattern	When it applies	Implication for vault design
Customer-supplier	In-house software; the source team accepts change requests and honours data contracts	Push fixes upstream. Standard staging and load patterns apply. Data contracts are enforceable. First prize.
Anti-corruption layer (ACL)	Legacy or vendor software that can change, but downstream must be shielded from upstream churn	Implement pre-staging views or tables that absorb schema drift. The vault sees a stable interface regardless of upstream changes. Second prize.
Conformist	Vendor software that cannot be changed (global SaaS, third-party APIs)	Accept the source model as-is. The vault must conform to the vendor's structure. Business Vault artefacts are likely needed to manage the resulting technical debt and reshape data into business terms. Last prize.

Include the DDD classification in the vault model proposal output:

SOURCE CLASSIFICATION
=====================
  <Source>    pattern: customer-supplier | acl | conformist
              implication: <one-line note>

If the pattern is conformist, flag it prominently: "Expect BV artefacts to reshape this source's data into business terms. Raw vault satellites will carry source-system structure as-is."

2 — Spawn the Pattern Recommender subagent (once per entity)

Read agents/pattern-recommender.md for the full system prompt.

For each entity identified by the Profiler, ask the Recommender to choose:

• Hub or Link (is this a business entity or a relationship between entities?)
• Which satellite variant fits (standard, ma, ef, dp, nh, st, rt, xt) — see /dv-explain satellite variants
• Whether a PII naming suffix is needed (any type can have _pii suffix for access segregation)
• Any overlays needed (same-as link, PIT table)

3 — Assemble the vault model proposal

Present the proposed model to the user as a structured summary:

PROPOSED VAULT MODEL
====================
Source: <system name>

SOURCE CLASSIFICATION
=====================
  <Source>    pattern: customer-supplier | acl | conformist
              implication: <one-line note>

HUBs
  HUB_<NAME>     business key: <column>     from: <source table>
  ...

LINKs
  LNK_<NAME>     connects: HUB_A ↔ HUB_B   from: <source table>
  ...

SATELLITEs
  SAT_<HUB>_<CONTEXT>   variant: <type>   tracks: <column list>
  ...

OPEN QUESTIONS
  ? <anything ambiguous that the user needs to confirm>

4 — Ask for confirmation before proceeding

Do not generate code or a manifest automatically. Ask:

"Does this look right? Any hubs or links missing? Once you confirm, use /dv-model to define each construct in detail, or /dv-generate to produce SQL."

4.1 — Data Profiling Questionnaire (pre-mob homework)

Before a mob modelling session, assign source-system SMEs this structured questionnaire as homework. Answers feed directly into the source profiler and pattern recommender.

Section A — Business Key Analysis (16 questions)

#	Question
A1	What column(s) uniquely identify a single record in this table?
A2	Is this key assigned by the business or generated by the system (surrogate)?
A3	Can the key value change over time? (If yes → not a valid BK)
A4	Is the key reused or recycled? (e.g. account numbers reassigned after closure)
A5	Does the key contain embedded meaning (smart key)? What do the parts mean?
A6	Is the same key used in other systems? Which ones?
A7	Are there multiple key formats for the same entity? (e.g. with/without leading zeros)
A8	Is case significant? (e.g. ABC123 vs abc123 — same or different?)
A9	What is the key's data type and max length in the source?
A10	Are there known data quality issues with this key? (nulls, duplicates, blanks)
A11	Is there a natural hierarchy? (e.g. branch → account → sub-account)
A12	Does the key depend on a parent key to be unique? (dep-child signal)
A13	What is the business name for this identifier? (what does the business call it?)
A14	Is this key visible to end users / customers?
A15	Is there an alternate key or cross-reference to another system?
A16	What is the expected key population growth rate? (records per day/week/month)

Section B — Unit of Work Identification (7 questions)

#	Question
B1	What business event or transaction does this table record?
B2	How many entities participate in one event? (list them)
B3	Can the same combination of participants appear more than once?
B4	Is there a natural timestamp that marks when the event occurred?
B5	Is this a point-in-time event or a relationship with a lifespan?
B6	Can the relationship end and restart? (effectivity signal)
B7	If one record were deleted, what business meaning would be lost?

Section C — Descriptive Content Classification (10 questions)

#	Question
C1	Which columns describe the entity vs. the relationship?
C2	Which columns change frequently? Which rarely change?
C3	Are there columns that are always updated together? (split candidate)
C4	Are there PII / sensitive columns? (name, email, phone, DOB, SSN, etc.)
C5	Are there columns that can have multiple simultaneous values? (multi-active signal)
C6	Are there columns that are overwritten without history? (non-historized signal)
C7	Are any columns derived or calculated from other columns?
C8	What is the source of truth for each column group?
C9	Are there columns that only apply to certain record sub-types?
C10	What is the expected volume of change? (% of records that change per load cycle)

Section D — Peripheral & Ad-hoc (8 questions)

#	Question
D1	Who is the business owner of this data?
D2	Is there an existing data dictionary or glossary?
D3	Are there known upstream dependencies or feeds into this table?
D4	Are there regulatory retention requirements?
D5	Is this data replicated to other systems? Which ones consume it?
D6	Are there known timing dependencies? (e.g. must load after table X)
D7	Is late-arriving data expected? How late can records arrive?
D8	Are there soft deletes? How are deleted records indicated?

Usage: Assign sections A-C as mandatory pre-session homework. Section D is collected during the mob session by the Guardian role. Incomplete answers become open questions in the vault model proposal.

4.5 — Mob modelling guidance

Mob modelling is a collaborative session where business stakeholders, data modellers, and engineers jointly identify vault constructs. It is the recommended approach for step 5 of the steel thread (see /dv-when) and for any discovery exercise where the business case isn't fully defined upfront.

Establish the ubiquitous language first — polysemy and false cognates

Before any modelling begins, the team must align on business vocabulary. Two named risks to watch for:

• Polysemy — a single word or phrase carries multiple related meanings depending on context. Example: "ticket" means a support request in IT, a travel document in airlines, a political slate in elections, and a price label in retail. In a vault that spans multiple domains, "ticket" as a hub name is ambiguous unless the context is explicit.
• False cognates — two or more people use the same term thinking they mean the same thing, but they don't. This is the most common source of modelling errors: a business analyst says "account" meaning a contractual agreement; a developer hears "account" and models a login credential. The vault will be built correctly to the wrong concept.

Resolution: each hub name must be validated against the business domain. If the same term is used across domains with different meanings, prefix or qualify it (e.g. HUB_SUPPORT_TICKET vs HUB_TRAVEL_TICKET). Never let the engineering team name hub tables without domain expert sign-off.

Polysemes — shared entities across domains (distinct from polysemy)

A polyseme is a business concept that exists across multiple domains, pointing to the same real-world entity but modelled with domain-specific attributes and vocabulary. For example, "customer" in the CRM domain and "customer" in the billing domain both refer to the same real-world person or organisation — but the CRM domain tracks marketing attributes, and the billing domain tracks financial attributes. They are the same entity with different contextual lenses.

This is distinct from polysemy (same word, ambiguous meaning):

• Polysemy → two teams use "account" to mean different things → they are NOT the same entity → separate hubs
• Polyseme → two teams use "customer" for the same real-world entity → they ARE the same entity → one hub, multiple satellites per domain

Vault mapping of polysemes:

• One hub table (HUB_CUSTOMER) with the canonical business key — the integration point across all domains
• Domain-specific satellite tables hang off the same hub (SAT_RV_HUB_CUSTOMER_CRM, SAT_RV_HUB_CUSTOMER_BILLING)
• Passive integration via the shared business key harmonises the domains automatically — this is the "shared kernel" in DDD terms

Polysemes are the reason hub tables are the integration layer: every domain that cares about the same business entity contributes its attributes to satellites on the same hub row. The hub key is the universal identifier that bridges bounded contexts.

Complementary collaborative workshop techniques

Mob modelling is the primary discovery technique, but two complementary DDD workshops produce vault-ready outputs and can be run before or alongside mob modelling:

• Domain Storytelling — participants narrate how they do their work using a pictographic language (actors, work objects, activities). A facilitator draws the story on a shared canvas and the group corrects it in real time. This rapidly surfaces business roles, the objects they work with, and the activities between them — which map directly to hubs, satellites, and links.
• Event Storming — participants write business events on sticky notes and place them on a timeline (orange = domain event, blue = command, yellow = aggregate). The group clusters events into bounded contexts and identifies the entities involved. A vault modeller can read an event storm output directly into hub/link proposals.

All three techniques produce the same vault-ready outputs: agreed entity list with business keys, relationship map, and unit-of-work definition. Choose based on the audience: mob modelling works well when a data model sketch already exists; domain storytelling works well when starting from blank; event storming works well when the domain is process-heavy.

Run mob modelling when:

• The use case is new and business entities haven't been formally identified yet
• Multiple source-system SMEs need to align on key definitions
• Business stakeholders need to validate the proposed vault model before any SQL is generated

Mob modelling outputs (minimum):

• Agreed business entity list with confirmed business keys
• Relationship map between entities
• Unit of work definition (what constitutes one "event" in the source)
• Scope boundary: which tables are in scope for this use case (never the entire platform)

Mob modelling participant roles

Role	Responsibility
Subject Matter Expert (SME)	Domain knowledge — ensures business logic, relationships, and attributes are modelled correctly
Technical Business Analyst	Bridges business rules to source system representation; identifies technical debt, reporting requirements, glossary updates
Data Modeler (driver)	Keeps the session on track and within its time block; captures decisions, action items, and tasks; controls the keyboard/canvas
Guardian	Action-taker — owns follow-up items that cannot be resolved during the session (information gathering, decisions requiring escalation, tasks with tangible outcomes). Assigned explicitly so that shared responsibility does not become nobody's responsibility

Session facilitation rules

• One conversation at a time — the driver manages the floor
• Rotate the driver periodically (typically every 15 minutes) to maintain engagement
• Time-box discussions: if a decision cannot be reached quickly, apply "good enough for now, safe enough to try" — trial a solution and learn from it rather than debating toward the theoretically perfect answer
• Park discussions that are not relevant to the current session scope — assign a Guardian to follow up
• Visualise: whiteboard or shared canvas keeps the whole mob oriented and reduces misunderstanding

Source-system SME engagement rule — when working with source-system subject matter experts, set scope expectations upfront:

"We are interested in the business keys, their relationships, and the grain of the source data. We will never model your entire platform — only the tables and entities that are part of the current business case."

This prevents scope creep and keeps discovery sessions focused. Source-system SMEs should not be asked to explain columns or tables outside the agreed use case scope.

Rules

Hub naming is an information mapping responsibility — not a data modelling one

Hub table names and definitions are determined by business architecture and information mapping — business data architects, domain experts, and business stakeholders who own the business ontology. Hub names must be signed off by business domain experts before data modellers begin designing links, satellites, and physical structures.

Responsibility	Who owns it	Output
Hub table names and definitions	Business domain experts, business data architects	Business object names aligned to the business ontology (e.g. HUB_CUSTOMER, HUB_LOAN_ACCOUNT)
Link table design	Data modellers + source SME	Link tables that map the unit of work as the source system records it
Satellite design + column mapping	Data modellers + source SME	Satellite DDL, hashdiff computation, satellite splitting decisions

Mixing these responsibilities — letting engineers name hubs without business input — is the root cause of WARN-HUB-GENERIC (too vague), WARN-HUB-WEAK (no business recognises the key), and WARN-HUB-SMART (smart keys embedded in hub names). All three indicate that hub naming happened without business architecture validation.

• Never invent a business key — if it's unclear, flag it as an open question
• A table with a composite natural key should be a Link, not a Hub, unless the user says otherwise
• Reference/lookup tables (< ~10k rows, rarely updated) should be flagged as candidates for non-historized satellites
• Tables with multiple rows per entity key are multi-active satellite candidates
• If PII columns are detected (email, SSN, DOB, phone, name patterns), flag for PII satellite segregation
• Always classify each source against the three DDD context mappings (customer-supplier, ACL, conformist) — this determines the staging approach and whether BV artefacts will be needed
• Flag overloaded BK columns — if a single column contains multiple entity types (e.g. a type_code + id combination, or mixed entity IDs with no discriminator), it cannot be loaded directly to a hub. Raise as an open question and recommend the preferred resolution order:
  1. First prize: request the source to split the content into independent files/tables per entity type. The integration debt stays with the source team that owns it.
  2. Second prize: pre-staging split with error traps to detect when the splitting rule changes. This requires additional code and maintenance overhead. An overloaded column that requires a type_code to give the key meaning is a dependent-child key, not a hub business key. A hub named HUB_ENTITY with a type discriminator is a Fake Vault pattern (Weak Hub / Bag of Keys).

---

CDM / LDM / PDM in the data vault context

Traditional data modelling follows a CDM → LDM → PDM waterfall. Data vault accelerates this by leveraging what source systems already provide. The relative importance of each layer differs from classic data warehouse practice:

Layer	Traditional waterfall	Data Vault approach	Priority
CDM (Conceptual)	Design from scratch; define all entities and relationships	Already derivable from business architecture and capabilities. Domain storytelling, event storming, and mob modelling surface the CDM rapidly. Hub tables ARE the conceptual model — each hub = one business object.	Vital — cannot skip
LDM (Logical)	Full relational model with cardinality, constraints, relationships	Source-system SME engagement surfaces the CDEs, unit of work, and relationships. You don't need to understand all source internals — only the critical data elements for the current use case. Link tables = logical relationships.	Very important
PDM (Physical)	Define data types, indexes, partitioning	Source systems have already done the physical modelling. Pulling source data reveals column types, nullability, and grain automatically. Staging inherits the source's PDM.	Important but not vital

Key insight: Data vault's insert-only, append-only physical model means you don't need to design the PDM — you inherit it from source. The valuable modelling effort is in CDM (what are the business objects?) and LDM (how do they relate, and what CDEs matter?). The modelling bottleneck is human, not technical: stream-aligned teams can model their own CDEs in parallel using shared standards without waiting for a centralised waterfall.

---

Subagent files

• Source Profiler: agents/source-profiler.md
• Pattern Recommender: agents/pattern-recommender.md