Latency Requirement Versus a 300-Table Warehouse
Vantage Outfitters, a retailer, runs a 3NF enterprise data warehouse with 300 tables fed by 12 source systems in a nightly batch. The load finishes at 04:00, so inventory data is up to 18 hours old by mid-afternoon. Merchandising now wants near-real-time inventory visibility for fulfillment promises. Analysts have built 1,400 personal extracts because the warehouse is hard to query. Investigation shows roughly 60% of the lag comes from two source systems that post late, not from warehouse processing. The CMO wants streaming; the DBA team wants to renormalize the warehouse; finance wants stable reporting.
1. Merchandising wants near-real-time inventory. What should Vantage do first, and what architecture decision follows?
Read the worked answer
Diagnose the lag before choosing streaming. If about 60% of the 18-hour lag comes from two source systems posting late, a streaming platform will not fix the promise date; it will move the same late data faster. Start with a lag budget by source and process step. Then decide by use case: keep the warehouse for historical and financial reporting; add a bounded event-driven path, using change data capture or events from the two critical sources, into an operational data store that serves current inventory state with a defined freshness SLA.
Do not renormalize the whole warehouse to solve a latency problem; that is a high-risk change with little latency benefit. Publish freshness per data domain rather than one enterprise number.
2. Analysts have built 1,400 personal extracts. How does that affect your modeling and delivery approach?
Read the worked answer
The extracts are a signal that the warehouse access layer does not match how analysts think. Treat them as requirements, not disobedience. Inventory the top 20 extracts by use and reconstruct the questions they answer; then model curated dimensional marts or views for those questions, with conformed dimensions for customer, product, store, and time. Provide a semantic layer so metrics are defined once.
For the real-time inventory need, model current state and event history separately; do not force event data into the same star schema as daily snapshots without defining the grain. Retire extracts only after replacements are validated, with a published migration date.
3. Finance wants stable reporting, the CMO wants streaming, and the DBA team wants a rebuilt 3NF warehouse. How do you resolve the conflict?
Read the worked answer
Separate requirements by decision latency and stability, then assign each to a delivery tier. Finance needs reproducible, reconciled numbers on a controlled cadence; keep it on the warehouse with versioned data and change control. Merchandising needs current state within minutes; give it an operational data store with freshness SLAs and clear operational ownership. The CMO's streaming request should be tested against a specific decision and cost; streaming everything is not a strategy. The DBA's renormalization should be evaluated as a separate modernization case with its own benefits, not bundled with latency.
Document the tiers, the interfaces, and who owns each. Conflicts then become placement questions rather than architecture wars.