Data Warehouse Architect — Build a Reliable Analytics

Warehouse architecture proposal — illustrative, platform-neutral

Decision scope: daily net revenue by order date and region. Required unknowns: authoritative order/customer keys, refund handling, currency and tax semantics, region history, source delivery, volume, query concurrency, retention and recovery objectives. Do not calculate net revenue before definitions are approved.

Proposed layers: immutable/replayable source landing where policy permits; staging preserving source fields and load metadata; integrated authoritative identities and approved history; order-level analytical model at a confirmed grain; reporting mart/semantic metric for net revenue. These are design proposals, not discovered source schemas.

Grain gate: confirm whether each source row is an order, line item or event. Model facts and joins accordingly to prevent double-counting. Define refund grain and attribution to order date separately.

History gate: decide whether region reporting uses purchase-time region or current customer region. Preserve change history if the chosen business question requires it; do not silently rewrite historical truth.

Loading: deterministic keys/version rules, idempotent incremental processing, quarantine of invalid/conflicting records and a documented replay/backfill path. Platform-specific syntax deferred.

Quality/reconciliation: confirm source totals, accepted/rejected records, refunds and currency control totals; test join fanout and metric definitions against authoritative finance evidence.

Operations: assign proposed data/metric owners; define access and masking, freshness observability, workload isolation, recovery testing and cost monitoring after real requirements are known.

Execution notes: no warehouse created, data loaded, benchmark run or savings measured. Reference design is not proof of production performance.