Data portal architecture guide
How do you build a portal on top of a data warehouse?
Use the warehouse for governed history and analytical measures, then add a customer-aware serving and application layer for secure access, predictable performance, understandable data, freshness communication and customer workflow.
The direct approach
Start with a customer decision, not with every table in the warehouse. Identify the measures, history, dimensions and supporting records the customer needs. Build a server-side portal API that resolves the customer organisation, queries a governed semantic or serving model, enforces row and field access, shapes the result and communicates when the data was refreshed.
Use live operational APIs for current status and transactions where warehouse latency is not acceptable. A hybrid portal often combines warehouse-backed reporting with operational workflows from ERP, CRM or specialist systems.
01
Use a data warehouse when the customer need is analytical
| Need | Warehouse fit | Likely addition |
|---|---|---|
| Consolidated history | Strong | Customer-aware semantic model and period definitions |
| Measures across systems | Strong | Governed calculation ownership and reconciliation |
| Large filtered exports | Potentially strong | Queued generation, limits and secure file delivery |
| Current order status | Depends on ingestion latency | Live operational API or low-latency read model |
| Immediate customer transaction | Usually weak | Write to the operational system through an API or queue |
| Collaborative workflow | Provides context only | Portal-owned requests, approvals, comments and status |
Microsoft's data warehousing guidance separates ingestion, transformation, modelling and serving. That distinction is useful: the warehouse prepares trusted analytical data, while the portal adds the customer experience and operational controls. Compare a reporting database with a data warehouse when the required platform scope is still unclear.
02
Use a layered reference architecture
Source
Operational ownership
Systems of record own transactions, workflow states and correction processes.
Warehouse
Analytical truth
Pipelines integrate history, validate data and calculate governed measures.
Serving
Customer contract
An API or read model applies customer scope, limits, caching and response shaping.
Portal
Meaning and action
The interface explains measures, supports navigation and connects insights to workflow.
A browser should not receive warehouse credentials or construct unrestricted analytical queries. Keep the customer contract stable even when warehouse tables and pipelines evolve. If Snowflake is the selected warehouse, use the focused guide to building a customer portal on Snowflake.
03
Design the model around customer meaning
- Customer key: maintain a governed mapping from portal organisation to warehouse customer, branch, contract or account keys.
- Measure definition: state formula, grain, period, currency, timezone, exclusions and owner.
- Dimensions: expose customer-friendly labels and approved filtering options.
- History: define how corrections, late-arriving data and slowly changing relationships appear.
- Entitlement: distinguish a record belonging to a customer from a user being entitled to see it.
- Lineage: retain enough source and pipeline context to investigate disputes without exposing internal details.
A semantic model can centralise calculations, relationships and customer-friendly concepts. It does not replace portal-level identity, navigation, workflow or export policy. See how to centralise reporting across business systems when several operational sources contribute to the model.
04
Choose how the portal will serve warehouse data
| Pattern | Use when | Main responsibility |
|---|---|---|
| Controlled warehouse query | Volume is moderate and the platform supports predictable governed queries | Query templates, workload limits and access policy |
| Semantic model | Measures and relationships need central business meaning | Definitions, row-level security and model lifecycle |
| Customer-facing read model | Portal interactions need low latency and stable response shapes | Synchronisation, reconciliation and cache invalidation |
| Embedded analytics | Customers need interactive analytical exploration | Embedding identity, tenant isolation, capacity and experience |
| Pre-generated output | Reports are periodic or expensive to calculate | Schedule, secure storage, expiry and download audit |
Test realistic filters, date ranges, concurrent customers and exports. An efficient analyst query can still be unsuitable for a public application if users can vary it without bounds.
05
Make freshness and data trust visible
Customers need to know whether a number reflects this minute, last night or the previous closed period. For every view or report, define:
- source cut-off and last successful refresh time;
- expected latency and business calendar;
- status when a pipeline is late, partial or failed;
- how corrections and restatements appear;
- who owns a data question or dispute;
- whether the portal may serve the last known good result; and
- which operational data must be fetched live instead.
Do not show a fresh page timestamp next to stale business data. Timestamp the data itself and expose material limitations close to the measure.
06
Connect insight to a controlled customer action
A useful portal can turn a reporting exception into a structured query, evidence upload, acknowledgement or approval. Keep the warehouse read path separate from the operational write path:
Store a reference to the report period, measure and source identifiers so staff can understand what the customer saw. Show received, processing, completed and rejected states honestly. Read more about customer reporting portals and turning business data into a customer-facing application.
07
Deliver the portal in a measured sequence
- Choose one customer decision and define the success measure.
- Confirm customer-key quality, data ownership and the refresh commitment.
- Agree the visible measures, filters, periods and exclusions.
- Prototype tenant-scoped access and the most expensive representative query.
- Select the serving pattern and test realistic concurrency and export volume.
- Add customer explanation, support, audit and data-dispute handling.
- Pilot with real roles and compare the portal output to approved source reports.
Sources
Primary references
Questions
Frequently asked questions
Can a customer portal use a data warehouse?
Yes. A data warehouse can provide consolidated history, governed measures and cross-system reporting. Place a customer-aware application and serving layer in front of it rather than allowing the browser or customer to query warehouse tables directly.
Should the portal query the warehouse directly?
The portal backend may use a controlled warehouse interface for suitable read workloads, but the customer-facing API should own authentication, tenant scope, response shaping, limits and caching. A separate serving model is often safer for high-volume or predictable interactions.
Can a warehouse support real-time portal data?
It depends on ingestion and serving latency. Many warehouse pipelines are batch-oriented. Combine warehoused history with a live operational source when customers need current status or immediate transactions.
Where should customer actions be written?
Write customer actions to the system that owns the operational workflow, usually through a supported API or queue. A warehouse should not become the accidental transaction system unless it was explicitly designed for that role.
How do you isolate customer data in a warehouse-backed portal?
Resolve the customer organisation on the server, map it to governed warehouse keys, apply row and field policies in every query path, minimise the output, and test that filters, exports, caches and embedded analytics cannot cross customer boundaries.
Plan the warehouse-backed portal
Bring the data model, refresh cycle and customer reporting need.
LCR can help separate warehouse responsibilities from the customer-facing application and identify the smallest secure reporting workflow.