Business requirements for the enterprise data warehouse serving the combined entity — statutory and quality reporting, network analytics, medical economics and finance. Issued February 20, 2024. Of twenty-two systems on the disposition matrix, this is the only one being built rather than absorbed, preserved or retired, which makes it the only work package on the program with genuinely greenfield requirements.
For a warehouse, durability matters more than availability, and getting that the wrong way round is the mistake this document exists to prevent. A seventy-two hour outage is survivable: no claim goes unpaid, no member is denied care, and the reports arrive late. But the warehouse is the system of record for quality measurement and statutory reporting, and losing a measurement year cannot be recovered at any recovery time objective — because by the time anyone notices, the source transactions that would rebuild it have aged out of the systems that produced them. ⚠ Every recovery requirement in §8 is written against that asymmetry, not against downtime.
Table of Contents
Part I — Context
Part II — Requirements
- Conformed Dimensions
- Historical Continuity
- Statutory and Quality Reporting
- Data Quality and Reconciliation
- Durability and Recovery
- Non-Functional Requirements
Part III — Proving It
1. Business Case and Objectives
| Objective | Measure | Target |
|---|---|---|
| Statutory and quality reporting for the combined entity | Filings submitted on the regulator's calendar | ⚠ On time, every cycle, including the cycle spanning the cutover |
| One number per question | Reports disagreeing on the same metric | Zero. See §4. |
| Continuous history across the transaction | Trend continuity through the merge point | ⚠ No discontinuity that is an artifact of the merge rather than of the business |
| Retire two reporting estates | Reporting platforms in production | One |
| Medical economics and network analytics | Availability of combined-population analysis | Enables the network rationalization decisions |
2. Why Build Rather Than Absorb
| Option | Consideration | Outcome |
|---|---|---|
| Absorb into ACME's warehouse | Existing model would need substantial extension for the target's products and network | ⚠ Rejected — the extension approaches a rebuild, without the benefit of a clean model |
| Preserve the target's warehouse | Smaller, and its measurement definitions differ | Rejected — cannot carry the combined volume or ACME's reporting obligations |
| Run both | Two answers to every question | ⚠ Rejected — defeats the purpose of a warehouse |
| Build once, migrate both | Higher one-time cost, single conformed model | Selected |
The honest framing of this decision, and the one to use if challenged on cost: the alternative was never "keep what we have." Both existing warehouses would have required substantial work — ACME's to model products and a network it was not built for, the target's to carry four times its volume and a different set of regulatory obligations. Once the realistic comparison is "rebuild one of them under a merge" against "build one clean," the second is defensible on cost as well as on outcome. ⚠ It is also why this is the only greenfield item on the program: everything else had a credible survivor.
3. Current State and Consumers
| Consumer | Uses it for | Consequence of a gap |
|---|---|---|
| Quality and accreditation | Measure calculation and submission | ⚠ A missing measurement year cannot be reconstructed after the source ages out |
| Statutory reporting | Regulatory filings | Late or incorrect filing is a regulatory event |
| Medical economics | Cost and utilization trend | Network and benefit decisions made on incomplete evidence |
| Network management | Provider performance, rate analysis | ⚠ Gates the network rationalization synergy |
| Finance | Reserving, medical loss ratio | Financial statement inputs |
| Actuarial | Pricing and trend | Rate filings depend on it |
4. Conformed Dimensions
| Ref | Priority | Requirement | Detail |
|---|---|---|---|
| FR-01 | Must | One definition per measure, signed by both entities | ⚠ Member month, covered life, paid claim, incurred date, allowed amount. Agreed and signed before the model is built. |
| FR-02 | Must | Conformed member dimension | Keyed on the enterprise member identifier, with both source identifiers retained |
| FR-03 | Must | Conformed provider dimension | ⚠ A provider contracted with both entities is one provider with two contracts, not two providers |
| FR-04 | Must | Conformed date dimension | Service, incurred, paid and processed dates distinguished. ⚠ Reports must state which they use. |
| FR-05 | Must | Slowly changing dimensions | Member, provider and benefit attributes carry effective dating. A report as at March uses March's attributes. |
| FR-06 | Must | Source system lineage | Every fact traceable to its source system and load |
| FR-07 | Should | Business glossary published | Definitions visible to consumers, not held in the model only |
FR-01 is a governance requirement disguised as a technical one, and it is the requirement most likely to be treated as a formality. Two health plans do not define "member month" identically — one may count a member enrolled on the first of the month, another a member enrolled at any point in it, and both are defensible. Build a warehouse before settling that and it will faithfully produce two different answers to the same question, each traceable, each correct under its own definition, and the organization will spend a year arguing about which report is right instead of about what the number means. ⚠ The signature on this requirement is from the Chief Actuary and the target's Chief Actuary jointly, because a definition that only one side accepts has not been agreed.
5. Historical Continuity
| Ref | Priority | Requirement | Detail |
|---|---|---|---|
| FR-08 | Must | Both histories loaded | Claims and enrollment history from both entities for the statutory retention period |
| FR-09 | Must | History restated to conformed definitions | ⚠ Prior periods recomputed on the agreed definitions, so a trend line crossing the merge is comparable |
| FR-10 | Must | Pre-merge and post-merge distinguishable | A consumer can always separate the combined view from either legacy view |
| FR-11 | Must | Restatement is documented and dated | ⚠ Any restated figure carries the basis and the date of restatement |
| FR-12 | Must | Archive retrievable after source retirement | ⚠ The retiring platform's history survives the platform. It is loaded, not referenced. |
FR-09 and FR-10 pull in opposite directions on purpose, and both are needed. Restating history on conformed definitions is what makes a trend line meaningful across the merge — without it, every metric appears to jump in October for reasons that are definitional rather than real. But a restated history is not what was filed at the time, and a regulator, an auditor or a rate filing may need the number exactly as originally reported. ⚠ The warehouse therefore has to hold both and label them, which is more work than either alone and is the only honest answer to *"has this number changed?"*
6. Statutory and Quality Reporting
| Ref | Priority | Requirement | Detail |
|---|---|---|---|
| FR-13 | Must | Measure calculation to specification | Quality measures computed to the accrediting body's published specification, not to a local interpretation |
| FR-14 | Must | Measurement year integrity | ⚠ A measurement year is not switched mid-cycle. Cutting over mid-year invalidates the rates for that year. |
| FR-15 | Must | Submission-ready extracts | In the regulator's required format, with the audit trail supporting each figure |
| FR-16 | Must | Reproducibility | ⚠ A filed figure can be reproduced later from retained data and the calculation version that produced it |
| FR-17 | Must | Combined-entity reporting from the first full cycle | No cycle reported on a partial population without disclosure |
7. Data Quality and Reconciliation
| Ref | Priority | Requirement | Detail |
|---|---|---|---|
| FR-18 | Must | Load reconciliation | Record counts and financial totals reconciled to source on every load |
| FR-19 | Must | A failed load does not partially publish | ⚠ Either the load completes and reconciles, or the prior state stands. A half-loaded warehouse produces confident wrong answers. |
| FR-20 | Must | Late-arriving data handled | Claims received after a period closes update the period and are flagged as restating it |
| FR-21 | Must | Completeness monitoring | Expected versus received volume per source per day, alerting on absence rather than only on error |
| FR-22 | Should | Data quality dashboard | Visible to consumers, so a report's reliability is knowable without asking |
FR-21 alerts on absence, which is different from alerting on error and is the harder of the two. A failed load raises an exception and somebody investigates. A source that simply stops sending raises nothing at all — the pipeline is healthy, the job succeeded, the warehouse is up, and the only symptom is a number that is quietly too low. ⚠ On this program that risk is concrete: during coexistence there are two sources, and one going quiet while the other continues produces a report that looks plausible and understates the combined population.
8. Durability and Recovery
The warehouse is Tier 2 — important: recovery time objective 72 hours, recovery point objective 24 hours. It inherits no backup regime, no rehearsed runbook and no restore history, so its recovery position is constructed and demonstrated rather than assumed.
| Ref | Priority | Condition — all seven evidenced before go-live |
|---|---|---|
| FR-23 | Must | Documented RTO and RPO agreed with the business owner and recorded in the Quality Plan |
| FR-24 | Must | Geo-redundant backup configured to the Azure paired region |
| FR-25 | Must | Immutable or soft-delete protection against ransomware and accidental deletion |
| FR-26 | Must | ⚠ Business associate agreement confirmed to cover the backup location and secondary region |
| FR-27 | Must | Full restore tested end to end, with evidence retained for audit |
| FR-28 | Must | DR runbook with named roles and a named accountable owner |
| FR-29 | Must | Recovery obligations carried in the risk register with a residual rating |
| FR-30 | Must | Retention of seven years, above the HIPAA floor, driven by state insurance record rules. Restore re-tested annually. |
FR-27 is the condition that gets deferred and the only one that proves anything. The other six can be satisfied by configuration, and configuration can be reviewed on a screen in an afternoon — backup enabled, region paired, retention set, runbook written, owner named. None of that demonstrates that the data comes back. ⚠ A backup that has never been restored is a hypothesis: the job may be succeeding against an empty target, the retention may be shorter than believed, the restore may take a week rather than the seventy-two hours committed, and referential integrity may not survive it. The requirement is a completed restore with evidence, not a configured backup, and it is written this way because "backup is configured" is the answer that is always available and never sufficient.
9. Non-Functional Requirements
| Ref | Requirement | Target |
|---|---|---|
| NFR-01 | Load window | Daily load completes and reconciles before the reporting day begins |
| NFR-02 | Query performance | Standard measure calculations complete within the reporting cycle |
| NFR-03 | Availability | Tier 2 — ⚠ 72 hour RTO. Deliberately not Tier 0; see the opening callout. |
| NFR-04 | Durability | ⚠ 24 hour RPO with geo-redundancy. This, not availability, is the binding requirement. |
| NFR-05 | Data residency | United States only, enforced by platform policy |
| NFR-06 | Access control | Role-based; ⚠ identified member data restricted to roles with a documented need |
| NFR-07 | Audit logging | Write-once. Query access to identified data logged and reviewed. |
10. Testing and Validation
| Test | Owner | Criterion |
|---|---|---|
| Definition conformance | E. Wetherby | Both actuaries compute the same measure from the same data and agree |
| Load reconciliation | Y. Abegunde | Counts and totals to source, every load, including a deliberately failed one |
| Historical restatement | M. Thibodeaux | Restated and as-filed figures both retrievable and labelled |
| Measure calculation | Dr. M. Ellsworth | Against the accrediting body's specification and prior-year results |
| Restore test | H. Sandifer | ⚠ Full restore to a clean environment, referential integrity verified, timed against the 72 hour objective |
| Completeness alerting | P. Ramaswamy | ⚠ A source deliberately stopped — the absence alerts |
11. Acceptance Criteria
- Every Must requirement demonstrated.
- Measure definitions signed by both entities' actuarial functions before the model was built.
- ⚠ All seven recovery conditions evidenced, including a completed restore with referential integrity verified and timing recorded.
- Load reconciliation passing, and a deliberately failed load shown not to publish partially.
- ⚠ A deliberately silenced source shown to alert on absence.
- Restated and as-filed history both retrievable and labelled.
- One full statutory reporting cycle produced and reconciled before the source platform retires.
- Retiring platform's history loaded and retrievable independently of that platform.
12. Constraints, Assumptions and Dependencies
| Type | Item | Consequence |
|---|---|---|
| Constraint | Measurement year calendar | ⚠ Externally governed. Cutover cannot fall mid-measurement-year without invalidating that year's rates. |
| Constraint | Statutory filing dates | Fixed. A load problem in filing week is not reschedulable. |
| Constraint | Seven-year retention | Storage and cost implication over the platform's life |
| Assumption | Archive transfer completes on the wave plan | ⚠ Historical load depends on it. Physical appliance rotation, SOW-08. |
| Dependency | Enterprise member identifier | ⚠ The conformed member dimension has no key without it. BRD-01 gates this. |
| Dependency | IF-13 warehouse ingestion | ⚠ Permanent interface — not retired with the integration layer. BRD-03. |
| Dependency | Landing zone accepted | Met January 15, 2024 |
13. Traceability
| Requirement | Design artifact | Verified by | Evidence |
|---|---|---|---|
| FR-01 to FR-07 | 20 — Application Disposition Matrix (AD-20) | Definition conformance | Signed measure definitions; two actuaries agreeing |
| FR-08 to FR-12 | Warehouse model specification | Historical restatement test | Restated and as-filed both retrievable |
| FR-13 to FR-17 | Reporting specification | Measure calculation test | Results against the published specification |
| FR-18 to FR-22 | 26 — Quality Plan §5 | Load reconciliation, absence alerting | Failed load and silenced source results |
| FR-23 to FR-30 | 34 — Cloud Landing Zone, 26 — Quality Plan §6.1 | Restore test | ⚠ Completed restore with evidence and timing — not configuration |
| NFR-01 to NFR-07 | 34 — Cloud Landing Zone | Performance and security testing | Load window, access logs |
14. Sign-Off and Approval
Business owner
Dr. A. Ravindran
Director, Enterprise Data Management, ACME
Date: _______________
Accountable executive
S. Achebe
Chief Information Officer, ACME Health
Date: _______________
Measure definitions — ACME
E. Wetherby
VP Actuarial Services, ACME · owns FR-01
Date: _______________
Measure definitions — Cumberland Valley
C. Adeyemi
Chief Actuary, Cumberland Valley · owns FR-01
Date: _______________
Two actuarial signatures rather than one, and that is the whole point of FR-01. A measure definition accepted by the acquirer alone is not a conformed definition — it is the acquirer's definition applied to somebody else's data, and the first time the target's numbers look wrong under it, the argument starts. Requiring both to sign forces the disagreement to happen now, in a room, over a document, rather than in eighteen months over a filed figure. ⚠ It is also the requirement most likely to be deferred as "we can align definitions later," which is true only in the sense that the warehouse will have been built twice by then.
Related artifacts: BRD-01 · BRD-03 · 20 — Application Disposition Matrix · 26 — Quality Plan · 34 — Cloud Landing Zone · 35 — Wave Plan · 28 — Risk Register