BRD-04 — Enterprise Data Warehouse

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.

1. Business Case and Objectives

2. Why Build Rather Than Absorb

Of the twenty-two systems on the Application Disposition Matrix, twenty-one are absorbed, preserved or retired. This one is built. That makes it the only greenfield item in the program, and the exception needs a reason better than preference.

The reason is that neither existing warehouse can answer the questions the combined entity will be asked. Each was built to report on its own book, against its own definitions, for its own regulator filings. Absorbing one into the other does not produce a combined view — it produces the acquirer's view with additional rows in it, which is a different and misleading thing. The measures that matter after integration (medical loss ratio, network utilization, risk adjustment accuracy) are all ratios, and a ratio computed over two populations defined differently is not a measure of anything.

Building also has a cost that should be stated rather than glossed. A greenfield warehouse has no users on day one and no history until it is loaded, so it delivers nothing for the first several months while consuming budget and the scarcest people on the program. Every other workstream can show progress against a system somebody is already using. This one cannot, which makes it the easiest item to defer when the schedule tightens — and §12 records why deferring it is more expensive than it looks.

⚠ What is deliberately not in scope. This is a reporting and analytics warehouse, not an operational data store. It is not a system of record for anything, does not serve transactional lookups, and no operational process may take a dependency on its availability. That boundary is what keeps its recovery objectives honest — a warehouse nobody transacts against can afford hours of downtime, and §8 spends that latitude deliberately in exchange for durability.

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

4. Conformed Dimensions

A conformed dimension is a definition both organizations agree to use: what counts as a member month, when a claim is considered incurred, which provider identifier is authoritative, how a product is categorized. These sound like technical decisions and they are not. Each one silently determines what the combined entity's numbers say.

The example that makes it concrete is the member month. If one organization counts a member enrolled on any day of the month and the other counts only members enrolled on the first, the denominators differ by a few per cent — and every per-member ratio built on them differs correspondingly. Neither definition is wrong. They are answers to slightly different questions, and both organizations have years of reported history behind their own answer.

FR-01 therefore requires two actuarial signatures on every conformed definition, one from each organization, and the requirement is about authority rather than diligence. A definition accepted by the acquirer alone is the acquirer's definition applied to somebody else's data. It will produce numbers that look wrong to everyone who knows the target's book, and the disagreement will surface later as a dispute about the warehouse's accuracy rather than as what it actually is — a disagreement about meaning that was never settled.

The requirement also forces the harder conversation early, while both actuaries are still employed and the definitions can still be changed cheaply. Restating a definition after the warehouse is loaded means reprocessing history and re-issuing anything already reported from it.

⚠ Where the two definitions cannot be reconciled, the requirement is to keep both and label them, not to choose. Some measures genuinely mean different things in the two books because the underlying products differ. Forcing a single definition on those would produce a number that is consistent, comparable and false — the worst of the three available outcomes.

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

The warehouse must carry history from before the transaction, and that history was produced by two organizations that did not know they would be compared. Neither kept its data with the other's questions in mind.

The practical limit is that history cannot be improved retrospectively. Where the target recorded a field the acquirer did not, the combined series has that field for one book and not the other, for every period before integration. No amount of transformation fixes that; it can only be labeled. FR-13 therefore requires the warehouse to record, per measure and per period, which book contributed and on what basis, so that a series which changes composition mid-way says so rather than appearing to change level.

⚠ The failure this prevents is a trend that is an artifact. A combined measure that begins including the target's book in a given month will step at that month, and the step is composition rather than performance. Without the labeling, somebody will eventually explain that step as a program outcome — favorably or unfavorably, depending on which direction it went and who is presenting.

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

7. Data Quality and Reconciliation

Warehouse quality checks conventionally look for bad data: values out of range, referential breaks, duplicate keys. Those matter and FR-18 through FR-20 cover them. FR-21 covers the harder case, which is data that never arrived.

The asymmetry is structural. An error arrives carrying its own evidence — a malformed record is present in the load, it fails validation, something logs it, someone is notified. An absence arrives as nothing at all. A feed that stops sending produces no error, no failed record and no alert, because every mechanism that would raise one is triggered by the arrival of something. The load completes successfully. The reports run. They are simply built on less data than they should be, and everything about them looks normal.

So FR-21 requires alerting on expected arrival rather than on failure: each feed declares its cadence and expected volume range, and the absence of a load within its window is itself the alert. Volume matters as much as presence, because a feed that delivers a fraction of its usual records is the same failure in a form that passes a presence check.

⚠⚠ This is the requirement most likely to be quietly dropped during build, and the one whose absence surfaces latest. It produces no visible function, adds configuration to every feed, and generates alerts during normal operational variation until it is tuned. The consequence of skipping it is not an outage — it is a quarter of reporting built on a partial dataset, discovered when someone external to the program asks why a number moved, by which point the reports have already been filed.

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

Most systems on this program are judged on availability: how quickly service resumes after a failure, expressed as a recovery time objective. That framing does not fit here, and applying it is the mistake this section exists to prevent.

A warehouse can be unavailable for a day without much consequence — reports run late, an analyst waits. What it cannot survive is losing data, because unlike an operational system it has no second copy anywhere. The transactions that produced its content live in the source systems, and those systems purge on their own schedules for their own reasons. Losing a measurement year cannot be recovered at any RTO, however fast the restore, because by the time anyone notices, the source transactions have aged out of the systems that produced them.

That gap between failure and detection is what makes this different. An outage announces itself within minutes. Silent corruption or partial loss in a warehouse can sit undetected for months, because the only thing that would reveal it is somebody querying a period nobody has queried recently. So the requirement is not "can we restore" but "can we restore to a point before the damage, at a distance we cannot currently see." That drives retention of restore points far beyond what an availability objective would justify, and it drives the requirement that restores be proven rather than assumed.

FR-27 carries OB-01 for that reason: a restore must be demonstrated, on a schedule, to a working state that is verified against known values. The verification is the part that matters. A restore that completes without error proves the mechanism ran; it does not prove the restored data is correct, complete or usable, and those are three separate questions.

⚠ "Backup is configured" is the answer that is always available and never sufficient. It is true in almost every organization that has ever lost data. It describes an intention rather than a demonstrated capability, and it is accepted because it is unfalsifiable until the day it matters. OB-01 exists to convert it into an observation with a date on it — which is also why it is the obligation most likely to slip when the schedule tightens, since it consumes environment time and produces nothing a stakeholder can see.

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.

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

10. Testing and Validation

11. Acceptance Criteria

1. Every Must requirement demonstrated.
2. Measure definitions signed by both entities' actuarial functions before the model was built.
3. ⚠ All seven recovery conditions evidenced, including a completed restore with referential integrity verified and timing recorded.
4. Load reconciliation passing, and a deliberately failed load shown not to publish partially.
5. ⚠ A deliberately silenced source shown to alert on absence.
6. Restated and as-filed history both retrievable and labeled.
7. One full statutory reporting cycle produced and reconciled before the source platform retires.
8. Retiring platform's history loaded and retrievable independently of that platform.

12. Constraints, Assumptions and Dependencies

This workstream's dependencies run the wrong way round compared with the rest of the program. Everything else depends on migrations completing; the warehouse depends on migrations having happened long enough ago to have produced history. It cannot be finished early, and its value accrues only with elapsed time.

That creates the pressure §2 anticipated. The warehouse is the easiest item on the program to defer, because deferring it breaks nothing visible this quarter. The cost of deferral is not schedule but coverage: every month it is not loading is a month of combined history that has to be reconstructed later from sources that are themselves being retired, or accepted as permanently missing.

⚠ The dependency nobody schedules. Both actuaries must be available to agree conformed definitions (§4) during a period when both are also supporting statutory filings and one of them works for an organization being absorbed. The requirement set assumes their availability and cannot enforce it — which is why the definitions are sequenced first, while retention still covers the roles that hold the knowledge.

13. Traceability

14. Sign-Off and Approval

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