DIG-574 Mart Design: Mary’s questions in CONFORMED_DM

Mart Design Document

Template version: 2 | Updated: 2026-10-11 UTC

Branch Delivery Document - Level 3 - Design Document

How to complete this template

This template specializes the Branch Delivery Design Document template (knowledge 12, item 175) for mart design. The design owns WHAT and WHY, including all source-to-column mappings. The implementation guide owns HOW; the test plan owns the detailed runnable proof. Neither adds an unresolved design decision.

Complete the workflow in order: consumer questions and metric definitions; required grains and existing-fact search; missing measures and dimensions; MetricFlow definitions and consumer views; validation and approval. Read the existing branch code and warehouse objects before declaring an object reusable, insufficient, or absent. Use current evidence with its environment and clock. Do not propose new physical objects before completing the metric, grain, and existing-fact analysis. Record not applicable with a reason instead of omitting a required field. Replace every placeholder before instantiation.

A measure describes one row at a declared fact grain. A metric answers a consumer’s question over a population. A calculated value can still be a measure at the row grain. A qualifier describes, filters, or groups the rows and belongs in a dimension. For example, an amount applied by one payment to one invoice is a measure; its total across the selected population is a metric; payment status is a qualifier.

0. Target ERD

The mart this design builds, drawn from section 2’s grains and section 3’s maps (Standard: Branch Delivery Documents v6.2, section 0): four facts, one per grain (client_policy_role_fact, agent_policy_fact, policy_terms_fact, finding_fact), each carrying its keys, its event dates and, for policy_terms_fact, the one measure; the qualifiers live in the dimensions. People and organizations follow the party model (D-10, D-11): two base dimensions, person_dim and organization_dim, with role views over them, client_dim, insured_dim and agent_dim over person_dim and carrier_dim over organization_dim, each drawn joined to its base; policy_dim, source_dim and day_dim complete the dimensions. Each event date is on its fact as a date that is also its key to day_dim in its role (sold, first known, valid from, valid to, effective, detected), drawn as a note rather than lines; agent_policy_fact, person_dim, organization_dim and policy_dim also refer to source_dim, left undrawn for the same reason. The insured’s birth date and address are shown under the masking mock of D-9. Every line holds by construction from the vault’s keys and is proved by section 5.3’s dimension integrity tests.

1. Consumer questions and metrics

1.1 Purpose, scope, and concept

The consumer is Mary Mink, Director of Data Architecture and Governance, asking her predicted questions from her own Claude through the DV Digest connector, which serves governed metrics only (list-metrics, get-dimensions, query-metrics: a metric and its group-by, no filter). The surface supports her first look at what the acquired agency’s and the carrier’s data say about clients, policies and agents, and how much of it arrives in a form the vault rejects.

Scope: the six questions the raw vault answers today, Q-9, Q-10, Q-11, Q-13, Q-14 and Q-15. Out of scope: Q-7, Q-8 and Q-16, which need the data integration layer and a second acquired agency; Q-12 is deferred until its rule is decided (DD-1). The concepts: a client (a client number Sunbelt Senior sends), a policy (a carrier’s policy number), an agent (a national producer number), a role (what a client is on a policy: owner, insured, payor), a version of a policy’s terms (what the carrier said about it from one load until the next), and a finding (a record an inspector or auditor flagged).

This design replaces the five MART views built on 2026-10-26 and 27 without a design (client_policy_fact, agent_policy_fact, policy_fact, finding_fact, and time_spine_day), which break the datamart form: facts carrying qualifiers, no dimensions, timestamps in place of day keys, and the sunsetting MART schema. Their numbers were right; their shape is retired here (D-6).

Name the consumer, the decision the surface supports, and the business concepts in plain language. State the design’s boundary and cite the profile results that establish it.

1.2 Questions from consumers

Use Q-n identifiers. A predicted initial question is identified as a prediction, rather than attributed to a consumer who has not asked it.

1.3 Questions to consumers and source owners

Use C-n for questions to consumers and S-n for questions to source owners. This design is their live home; other branch documents cite these entries. An unanswered question carries its interim treatment and disclosed consequences.

1.4 Metric definitions

Define each displayed number before deciding its physical model. State filters, numerator and denominator where applicable, period boundaries, and whether partial periods are allowed. Distinguish reporting grain from the supporting fact grain identified in Section 2. A comparison names both sides and their interpretation. Do not silently supply a business judgment that requires a consumer answer.

2. Grains and fact reuse

Grain is foundational. Declare exactly what one row represents in business terms and the keys that prove its uniqueness before selecting fact columns, joins, or physical objects. Distinguish the consumer’s reporting grain from each required supporting fact grain. Population restrictions are separate from grain; a restricted population does not by itself justify another fact at the same grain. Establish the actual grain from existing code and warehouse rows, not from selected columns, grouping, or an assumed model name.

2.1 Data reality

Cite the profile’s result identifiers. Measurements are dated evidence, not fixed expected values for future tests.

2.2 Metric-to-fact map

One fact serves each group of metrics at the same required supporting grain. Reuse an existing fact at that grain; extend it when measures or necessary population support are missing. Preserve existing consumers’ declared populations through explicit semantic filters and regression proof. A dashboard, comparison purpose, new consumer, or different reporting grain does not justify another shared fact by itself.

Before proposing a new fact, show whether the existing facts and dimensions can supply the required output through semantic calculations and consumer views. A new fact requires evidence that a distinct underlying business grain is necessary and that no existing fact serves it; the same requirement applies to a factless relationship fact. State the actual row identity, not merely a list of selected columns. For any upstream computation required, specify whether an existing source supplies it or a separately designed change is necessary.

Complete the review in this order: existing facts and actual grains; required supporting grain; reuse or extension; consumer-view necessity; object classification and schema-specific naming. A naming or suffix correction rechecks object necessity and classification; it must not preserve an unvalidated object proposal. For example, a deal/month output does not by itself justify another deal fact: assess the existing deal fact and attendance relationships before designing its consumer projection.

2.3 Decisions

Use D-n identifiers in sequence. Proposed mappings are shown to the reviewer and resolved through numbered decisions. Changed mappings are re-proposed, never silently substituted.

3. Measures and dimensions

3.1 Upstream and UDM map

Consume every column in the profile’s Integrate list; name Defer and Reject as absences. If this mart design requires no integration change, explicitly state that conclusion and map the existing upstream inputs it consumes. A missing consumer view does not by itself establish a missing integration capability.

3.2 Fact measures and keys

Each event date is carried on its fact as the date itself, which is also its key to day_dim in that role ( as corrected 2026-10-31). People and organizations are reached through the role views of section 3.3 (D-10, D-11).

Every written column traces to a source column or an explicit derivation. Facts carry measures and keys; qualifiers belong in dimensions. Date keys refer to the shared day dimension in their named roles. Explain any judicious degenerate identifier. Population metrics do not become precomputed fact columns.

3.3 Dimensions and qualifiers

Release 1 uses the party model (D-10, D-11): two base dimensions, person_dim and organization_dim, with role views over them. Dates that describe the entity itself, such as a birth date, live in its dimension; event dates live in the facts.

Reuse dimensions where they supply the required qualifiers. Qualifiers include codes, names, states, flags, and descriptive attributes.

3.4 Joins and cardinality

Show how each join preserves row identity and measures. A total over a view must first establish how often each measure-bearing key occurs.

3.5 Dictionary and domain rules

Ghost members: the unknown and error rows of each hub are dimension members, never fact rows. A sale with a blank policy number is joined to the unknown policy, whose carrier reads (unknown).

Teach terms in dependency order. Newly designed dictionary mappings begin PROPOSED and become VERIFIED through built-data evidence.

4. MetricFlow and consumer views

4.1 Semantic definitions

Every published metric is defined in semantic YAML. A consumer view preserves that shared definition; it does not become a second independent definition.

4.1a The agent’s guide: descriptions and saved queries

The semantic layer is the agent’s guide to this data (Data Mart Design Principles, , “The agent’s guide”). Every semantic model, metric and column of sections 3.2 and 3.3 carries a description in the semantic YAML: a model states what one row is and which questions it serves; a metric states what it counts, how to read it, what it leaves out, what to pair it with, and the decision that defined it; a column states what it holds, including when it is empty or unknown. A saved query stands for each question release 1 answers:

Proof: for each saved query, the test plan runs it and checks its answer against the figures on Mary’s release page.

4.2 SQL exceptions

Use none with a reason if no exception is required. SQL exceptions are limited to computations allowed by Data Mart Design Principles (knowledge 12, item 104); dashboard convenience is not a justification.

4.3 Consumer-view and card map

One measures view serves a report’s measures at the report grain. Finer-grain child views serve separate detail cards. The measures view never joins its children; child records are not flattened into numbered columns.

4.4 Physical placement and materialization

Warehouse schemas and repository folders are distinct. Workgroup marts read CONFORMED_DM alone. A needed MART object moves to CONFORMED_DM once, with its MART copy retired and readers repointed; no duplicate copy is introduced. Datamart tables are facts or dimensions with the corresponding _fact or _dim suffix. Apply the Object Naming standard (knowledge 12, item 147) before agreement.

4.5 Consumer interpretation

Apply the Data Consumption Design standard (knowledge 17, item 72). Show what was measured, over which population and period, with which exclusions and as-of clocks. An unexplained discrepancy or unresolved interpretation remains visible rather than receiving an invented answer.

5. Validation and approval

5.1 Instance models

Policy NF-100003 in DV_QA (read 2026-10-27 01:10 UTC): policy_dim holds it once (carrier NFL). policy_terms_fact holds two versions: valid from 2026-10-01 02:00 to 2026-10-02 01:59:59.999 UTC with status IF and face amount 15,000.00, and valid from 2026-10-02 02:00, current, with status LP and the same amount. face_amount_current reads 15,000.00 from the second; policy_versions grouped by policy reads 2; grouped by status, the change from IF to LP on 2026-10-02 is the answer to what changed. Figure M-1:

flowchart LR
  S1["policy_northfield_s1: NF-100003, IF, 15,000.00, loaded 2026-10-01 02:00"] ==> V1["policy_terms_fact: valid 2026-10-01 to 2026-10-02, IF, 15,000.00"]
  S2["policy_northfield_s1: NF-100003, LP, 15,000.00, loaded 2026-10-02 02:00"] ==> V2["policy_terms_fact: valid from 2026-10-02, current, LP, 15,000.00"]
  V1 ==> P["policy_dim: NFL NF-100003"]
  V2 ==> P
  V2 ==> C["face_amount_current: 15,000.00"]
  V1 ==> N["policy_versions for NF-100003: 2"]
  V2 ==> N

Show verified real records, source read clocks, and their rows through the proposed model to the consumer projection. Include a diagram for each trace. An instance model is evidence.

5.2 Worked examples

A client Ada is on policy P-1 as owner and insured: two rows in client_policy_role_fact, one policy. policies_held for Ada reads 1; client_policy_roles reads 2. Figure M-2:

flowchart LR
  R1["client_policy_role_fact: Ada, P-1, owner"] ==> P["policy_dim: P-1"]
  R2["client_policy_role_fact: Ada, P-1, insured"] ==> P
  R1 ==> A["client_dim: Ada"]
  R2 ==> A
  A ==> H["policies_held for Ada: 1"]
  A ==> K["client_policy_roles for Ada: 2"]

Use small invented names and values to explain the design, with a diagram. A worked example teaches the shape and does not replace real-data evidence.

5.3 Required proof and test-plan references

Walk the seven layers of the current Testing Methodology (knowledge 2) in order in the paired test plan; state not applicable with a reason. Do not copy an older template’s layer numbering. Include grain uniqueness, dimension integrity, join cardinality, measure conservation, unknown treatment, metric reconciliation through two independent routes, existing-consumer regression, and consumer-question probes as applicable. Tests precede implementation under the Test-Driven Development standard (knowledge 23). Fixed observed counts or amounts are dated examples, not enduring pass conditions.

5.4 Deferred decisions

5.5 Compliance record and completion checklist

Before agreement, verify every template section and field is filled; every applicable standard has evidence; every consumer question maps to a metric or evidence answer; every fact declares what one row represents and the keys that prove its actual grain; reporting grain and supporting fact grains are distinguished; every metric maps to a required supporting grain and fact; every new fact proves a distinct underlying business grain is necessary and no existing fact serves it; every missing measure or necessary population extension reuses an existing fact when its grain matches; consumer views are assessed before proposing another shared fact; object necessity and classification are checked before schema-specific naming, including after a suffix correction; every written column has a source or derivation; dimensions carry qualifiers; joins preserve measures; semantic definitions and SQL exceptions comply; physical placement meets schema boundaries; and every decision has corresponding proof in the test plan. Resolve every internal section and item reference. A requirement without evidence is not met.

5.6 Handoff boundary

The implementation guide adds the mart steps: the conformed_dm folder and schema entry, the thirteen models (four facts, five dimensions and four role views), the semantic YAML, the tests of 5.3 written first, the MART retirement, and the QA run. Agreement of this design does not authorize code; the build follows Ray’s answer on it.

The implementation guide implements the agreed decisions and maps, naming exact files and patterns. It adds no model, column, metric definition, consumer interpretation, or mapping decision absent from this design. Agreement of this document does not authorize code or database execution; execution follows the separately approved numbered decisions or agreed execution goal.