Data Warehouse Requirements: A Practical Checklist

Write a decision-to-metric contract first

Name the decision owner, action, review cadence, threshold, and consequence of a wrong or late number. Then define the metric in plain language and as a reproducible calculation. State the lowest grain, allowed dimensions, business keys, event time, reporting cutoff, timezone, currency, source records, formula, exclusions, corrections, owner, and version.

Attach source and service requirements to that contract: source authority and extraction, schema change, deletion, late arrival, retained evidence, transformation tests, lineage, restatement, reconciliation, freshness, access, performance, and recovery. This creates a testable vertical slice instead of an abstract “centralize all data” program.

The dashboard data-readiness checklist helps define metric ownership before visualization. This guide continues the work into the analytical storage, transformation, and operating contracts needed when several consumers or historical questions depend on the same result.

Cover the requirement lanes that create trusted reuse

Define scale with the units that drive behavior and cost: source count, tables or streams, daily changed rows, event rate, raw and modeled storage growth, history retained, transformation runs, backfill window, concurrent queries, scanned data, export volume, users, service accounts, and recovery targets. “Cloud scale” is not an acceptance criterion.

Make grain, time, and historical truth explicit

Every fact should state what one row represents. An order, order line, payment attempt, shipment event, daily account balance, and monthly target are different grains. Mixing them creates double counting and ambiguous joins. Define which business key identifies the event and which dimensions were true when it occurred.

Microsoft's star-schema guidance distinguishes dimension tables used for filtering and grouping from fact tables used for summarization, and notes that facts should load at a consistent grain. Use that as a modeling check, not a command to force every workload into one pattern.

Decide separately how corrections and changing descriptive attributes affect history. The same guidance explains that a Type 1 slowly changing dimension overwrites the old value while Type 2 creates a time-bounded version. Choose per attribute based on the business question. If a customer changes region, should last year's revenue stay in the former region or move to the current one? The requirement is the answer and its effective-time rule, not the acronym.

Keep event time, source update time, extraction time, warehouse receipt time, transformation time, and reporting cutoff distinct where they change decisions. Define late-arriving behavior and whether a closed report is restated, adjusted in the next period, or preserved with an explicit revision.

Design the whole operating path, not only storage

Microsoft's current modern data warehouse playbook describes ingest, transform, model, and serve stages, with security, governance, and operability as cross-cutting concerns. That is a useful completeness check. It does not decide whether a particular team needs a warehouse, lakehouse, operational store, or simpler managed reporting path.

For each stage, name the owner, code or configuration source, deployment method, input and output contract, dependency, service target, alert, retry, replay, correction, cost control, run metadata, and support procedure. Define environments and promotion rules so a dashboard does not depend on an unreviewed analyst query that exists only in one workspace.

If data crosses product or vendor boundaries, use the API integration requirements guide to specify delivery and correction behavior. If the result is embedded inside a customer-facing product, the embedded analytics checklist covers tenancy, authorization, product UX, and operational support at the serving boundary.

Turn quality, lineage, and reconciliation into operations

A quality rule needs a scope, threshold, severity, owner, affected outputs, alert route, response target, and resolution evidence. Separate technical checks—such as nulls, duplicates, schema, freshness, and referential integrity—from business controls such as order-to-invoice completeness, settled payment totals, inventory movement balance, or finance sign-off.

Reconcile at stable control points. Compare source extracts to landed data, landed data to transformed facts, facts to approved control totals, and semantic outputs to published reports. Store counts, amounts, run identifiers, cutoff times, differences, explanations, and approvals. “The dashboard looks right” is not a control.

The OpenLineage object model represents lineage around jobs, runs, and datasets. Whether or not that standard is adopted, require enough run and version context to trace a field from a consumer-facing result back through models and transformations to its source evidence—and to identify which downstream assets a proposed change could affect.

Choose the smallest architecture that earns trust

Compare build and operating cost across ingestion, storage, compute, orchestration, transformation, catalog, observability, environments, networking, security, support, egress, migration, and exit. A focused dashboard and analytics engagement can establish the first governed metric path without pretending every organization needs an enterprise data platform.

The BI tool versus custom dashboard guide helps choose the presentation and workflow layer after the underlying decision, data, and ownership boundaries are clear.

Run a one-metric warehouse pilot

1. Choose a consequential metric. Name the decision, owner, definition, grain, dimensions, source records, cutoff, and expected result.
2. Freeze source evidence. Retain the extracts, schemas, timestamps, row counts, control totals, and known data issues for one period.
3. Build one vertical path. Ingest, retain, transform, test, model, secure, and serve only what the decision needs.
4. Break history deliberately. Add an update, deletion, duplicate, late event, changing attribute, schema change, failed load, and corrected source record.
5. Reconcile every layer. Compare control counts and amounts, explain differences, and retain run and approval evidence.
6. Test operation. Exercise alerting, rerun, backfill, access change, cost threshold, backup, recovery, and downstream impact analysis.
7. Write the decision brief. Choose direct reporting, a managed analytical store, warehouse, lakehouse, or staged hybrid with exclusions, owners, service targets, rollout, and exit.

Data warehouse requirements FAQ