Problem
Retail reporting often starts with operational tables: orders, order lines, products, stores, customers, and stock movements. Analysts join them differently, decide independently whether a return reduces sales, and pick whichever product description happens to be current. The resulting numbers can be individually plausible and collectively contradictory.
A warehouse should make the common questions easy to answer consistently:
- What did we sell by product, store, and day?
- Which category and price band applied when a sale occurred?
- How did inventory compare across stores at a particular time?
- How much was returned, and how does that relate to the original sale?
- Can sales, inventory, and returns use the same definitions for product, store, and date?
The central design problem is not choosing a database. It is establishing stable fact grain, preserving the versions of descriptive data that explain historical events, and giving separate facts shared dimensions with the same meaning. Those choices determine whether reports remain comparable after source data changes or a new subject area is added.
This design uses a star schema with atomic facts, Type 2 product history, and conformed product, store, and date dimensions. It assumes a medium system: one or two engineering teams, roughly 10,000–100,000 daily users across the retail application and its reporting surfaces, and a handful of deployable components rather than a large data platform.
Scope and requirements
Functional requirements
The initial warehouse supports three subject areas:
- Sales: one row per source order line, including quantity, gross amount, discount, net amount, and tax.
- Inventory: one row per product, store, and business date, representing an end-of-day snapshot.
- Returns: one row per returned source order line or partial return event, with quantity and amount returned.
The shared dimensions are:
- Product: source product identifier, SKU, name, brand, category, and other reporting attributes.
- Store: source store identifier, name, region, and store type.
- Date: one row per calendar date with fiscal and calendar attributes.
A fact joins to the dimension versions that applied at its event time. A product moving from one category to another creates a new product dimension row; it does not rewrite earlier sales. The model also needs an explicit process for late-arriving facts, source corrections, replay, and reconciliation.
Non-functional requirements
The following figures are planning assumptions, not measured capacity claims:
- Assume up to 5 million sales lines per day at peak business scale and a comparable order of magnitude for inventory snapshots. Revisit partitioning and storage after profiling representative data.
- Keep dashboards usable during routine loads. Separate ingestion and reporting workloads where the chosen database supports it.
- Make each load repeatable: reprocessing the same source range must not duplicate facts or create repeated dimension versions.
- Retain enough lineage to trace warehouse rows to source records and load runs.
- Deploy through CI/CD with a staging environment. The primary database must tolerate an availability-zone failure through a managed multi-AZ configuration.
- Provide reporting freshness and load health as explicit operational signals, not informal expectations.
The first release does not promise real-time analytics, arbitrary ad hoc SQL through the application API, or a universal customer identity graph. Those require separate requirements and governance.
High-level design
Use a modular ingestion service, a durable object-store landing area, a queue for load notifications, and a managed PostgreSQL warehouse. The warehouse uses a primary instance for controlled writes and a read replica for reporting reads where the deployment and freshness requirements justify it. Both remain within the same managed multi-AZ database setup; the replica is not a substitute for backups or recovery testing.
The worker is responsible for validating source extracts, applying dimension changes, loading facts, recording lineage, and publishing a completed-load watermark. A queue message identifies work; it is not the authoritative copy of source data. Raw files in object storage and load metadata in the warehouse make retries and audits possible. A small API exposes governed metrics and freshness information; it does not accept caller-supplied SQL.
flowchart LR
src["Retail source systems"] --> ingest["Extract and validation service"]
ingest --> raw["Raw object storage"]
ingest --> queue["Load notification queue"]
queue --> worker["Warehouse load worker"]
raw --> worker
worker --> wh["PostgreSQL warehouse primary"]
wh --> replica["Reporting read replica"]
replica --> api["Metrics API"]
api --> cache["Short-lived response cache"]
cache --> clients["BI and application clients"]
worker --> meta["Load metadata and watermark"]
meta --> apiThe queue wakes the worker; immutable raw extracts and warehouse metadata support replay and audit.
Keep the first implementation as a modular monolith for extraction coordination, validation, and warehouse loading, with a separate worker process for queue-driven execution. This gives a small team clear module boundaries without multiplying services. Object storage, queue, managed database, cache, and deployment platform are managed infrastructure choices; the diagram describes responsibilities rather than requiring a particular cloud.
The warehouse schema should be versioned in source control and promoted through staging. Production changes that alter fact grain or dimension semantics require a migration plan and report-owner review, not just a successful database migration.
Data model
Star-schema rules
A fact table represents one declared business event at one declared grain. Grain is part of the model contract:
| Fact | Grain | Example measures |
|---|---|---|
fact_sales_line | One source order line | Quantity, gross, discount, net, tax |
fact_inventory_daily | One product-store-business date | On-hand quantity, inventory cost |
fact_return_line | One source return line | Returned quantity, returned amount |
Sales and returns are transaction facts. Inventory is a periodic snapshot; summing its quantity across dates usually answers the wrong question. Keep units explicit and measures additive only where appropriate. For example, sales amount can be summed across products and days, while a unit price is generally derived from amounts and quantities rather than summed.
A product dimension row has a warehouse-generated surrogate key, a source natural key, validity bounds, and a current-row indicator. Facts store the surrogate key for the version that was valid at the event time. This supports historical “as was” reporting without making analysts reconstruct a product’s old category from an audit log.
SCD Type 2 and conformed dimensions
When a tracked product attribute changes, close the current row at the change’s effective timestamp and insert a new current row. Use half-open validity intervals: valid_from <= event_time < valid_to. An open-ended valid_to represents the current version. This avoids ambiguous boundary matches.
Not every attribute should trigger a new version. Category and brand typically affect historical analysis; a corrected spelling or source-system bookkeeping field may not. Agree on the tracked-attribute list with report owners. When a source sends a correction effective in the past, do not silently overwrite history: route it through a controlled correction or restatement policy, because affected facts may need new foreign keys.
Conformed dimensions are shared structures with shared semantics, not merely similarly named tables. Sales and inventory should use the same product key and the same category definition. If one subject area needs a different hierarchy or role, model that distinction explicitly rather than quietly changing the meaning of the shared dimension. A date dimension can serve multiple roles—order date, ship date, snapshot date—through separate foreign keys to the same date table.
erDiagram
DIM_PRODUCT ||--o{ FACT_SALES_LINE : describes
DIM_STORE ||--o{ FACT_SALES_LINE : occurs_at
DIM_DATE ||--o{ FACT_SALES_LINE : ordered_on
DIM_PRODUCT ||--o{ FACT_INVENTORY_DAILY : describes
DIM_STORE ||--o{ FACT_INVENTORY_DAILY : counted_at
DIM_DATE ||--o{ FACT_INVENTORY_DAILY : snapshot_on
DIM_PRODUCT {
bigint product_sk PK
text source_product_id
text sku
text category
timestamptz valid_from
timestamptz valid_to
boolean is_current
}
DIM_STORE {
bigint store_sk PK
text source_store_id
text region
text store_type
}
DIM_DATE {
integer date_sk PK
date calendar_date
integer fiscal_period
integer calendar_year
}
FACT_SALES_LINE {
bigint sales_line_sk PK
text source_line_id
bigint product_sk FK
bigint store_sk FK
integer order_date_sk FK
numeric net_amount
numeric quantity
}
FACT_INVENTORY_DAILY {
bigint product_sk FK
bigint store_sk FK
integer snapshot_date_sk FK
numeric on_hand_quantity
}Sales and inventory reference the same product, store, and date structures while retaining different grains.
The following PostgreSQL DDL shows the key history and fact constraints. Production schemas should also define the full date and store dimensions, load metadata, indexes based on actual query plans, and the selected retention and partitioning strategy.
CREATE TABLE dim_product (
product_sk bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
source_product_id text NOT NULL,
sku text NOT NULL,
product_name text NOT NULL,
brand text,
category text NOT NULL,
valid_from timestamptz NOT NULL,
valid_to timestamptz NOT NULL DEFAULT 'infinity',
is_current boolean NOT NULL DEFAULT true,
CONSTRAINT dim_product_valid_range CHECK (valid_from < valid_to)
);
CREATE UNIQUE INDEX dim_product_one_current
ON dim_product (source_product_id)
WHERE is_current;
CREATE TABLE fact_sales_line (
sales_line_sk bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
source_line_id text NOT NULL UNIQUE,
product_sk bigint NOT NULL REFERENCES dim_product(product_sk),
store_sk bigint NOT NULL REFERENCES dim_store(store_sk),
order_date_sk integer NOT NULL REFERENCES dim_date(date_sk),
ordered_at timestamptz NOT NULL,
quantity numeric(14, 3) NOT NULL,
gross_amount numeric(14, 2) NOT NULL,
discount_amount numeric(14, 2) NOT NULL,
net_amount numeric(14, 2) NOT NULL,
load_id uuid NOT NULL
);
CREATE TABLE fact_inventory_daily (
product_sk bigint NOT NULL REFERENCES dim_product(product_sk),
store_sk bigint NOT NULL REFERENCES dim_store(store_sk),
snapshot_date_sk integer NOT NULL REFERENCES dim_date(date_sk),
on_hand_quantity numeric(14, 3) NOT NULL,
inventory_cost numeric(14, 2),
load_id uuid NOT NULL,
PRIMARY KEY (product_sk, store_sk, snapshot_date_sk)
);
The partial unique index enforces one current product version; fact uniqueness makes sales-line retries idempotent.
Late facts and correction policy
If a sales line arrives after a product change, resolve the product version using the event timestamp, not the current-row flag. If no version covers that timestamp, quarantine the fact and raise a data-quality signal; assigning the current key would make the historical report look valid while being wrong.
For source corrections, retain the source record identifier, source update/version metadata, and load identifier. Decide per source whether corrections update the existing fact, create an adjustment fact, or trigger a bounded restatement. A financial report may need a different correction policy from an operational dashboard. The policy belongs in the subject-area contract and should be tested with realistic replay cases.
API contract
The API serves a small set of approved metrics and dimensions. BI tools can use the warehouse’s governed access path when appropriate; the application API is not a SQL proxy. Require bounded date ranges, pagination or aggregation limits where relevant, and authorization scoped to the caller’s permitted stores or regions.
| Endpoint | Request | Response | Contract notes |
|---|---|---|---|
GET /v1/retail/sales | from, to, group_by, optional store_id | Grouped sales rows and totals | Date range is inclusive at the API boundary; server converts it to a half-open warehouse interval. |
GET /v1/retail/inventory | as_of, optional store_id, category | Inventory quantities and valuation | Returns the latest completed snapshot on or before as_of, with its actual snapshot date. |
GET /v1/retail/freshness | None | Subject, latest completed load, watermark, status | Does not expose source credentials or internal object paths. |
For every response, include a request identifier and the data watermark used to produce it. The sales endpoint should return metric definitions alongside the values or link to versioned documentation: “net sales” must not be left to client interpretation. Cache only responses whose authorization scope is included in the cache key. Invalidate or expire cached data when the relevant subject-area watermark advances.
Low-level design
Separate the code into small modules with explicit responsibilities:
- Source adapters fetch or read source extracts and attach source identity and version metadata.
- Validation checks required fields, key uniqueness, accepted values, and referential completeness before a batch is promoted.
- Dimension service compares tracked attributes and applies Type 2 changes.
- Fact loader resolves dimension versions at event time and inserts facts idempotently.
- Load coordinator records a run, retries transient work, and advances a watermark only after the subject-area transaction commits.
- Metrics API reads approved aggregate queries and applies authorization and query bounds.
The worker should process source batches in deterministic order where effective timestamps matter. Divide large batches into bounded transactions, record their source ranges, and make retries safe. A batch that partially fails must not publish a “complete” watermark. The simplest reliable operational unit is a load run with a status, input range, row counts, error counts, and start and completion timestamps.
Core SCD Type 2 operation
The minimal example below shows the temporal lookup expected when resolving a fact to a product version. It assumes the dimension has already been loaded.
SELECT product_sk
FROM dim_product
WHERE source_product_id = $1
AND valid_from <= $2
AND $2 < valid_to;
The half-open interval makes an event exactly at a version boundary belong to one row, not two.
The realistic example uses psycopg 3 and assumes source changes arrive in effective-time order per product. A transaction-scoped advisory lock serializes changes for the same natural key; the database uniqueness constraint remains a final guard. Production code should add structured error handling, input validation, and a policy for out-of-order corrections.
from datetime import datetime
import psycopg
def apply_product_change(
conn: psycopg.Connection,
source_product_id: str,
sku: str,
product_name: str,
brand: str | None,
category: str,
effective_at: datetime,
) -> int:
with conn.transaction():
conn.execute(
"SELECT pg_advisory_xact_lock(hashtextextended(%s, 0))",
(source_product_id,),
)
current = conn.execute(
"""
SELECT product_sk, sku, product_name, brand, category, valid_from
FROM dim_product
WHERE source_product_id = %s AND is_current
FOR UPDATE
""",
(source_product_id,),
).fetchone()
attributes = (sku, product_name, brand, category)
if current:
current_attributes = current[1:5]
if attributes == current_attributes:
return current[0]
if effective_at <= current[5]:
raise ValueError("Correction requires the restatement workflow")
conn.execute(
"""
UPDATE dim_product
SET valid_to = %s, is_current = false
WHERE product_sk = %s
""",
(effective_at, current[0]),
)
row = conn.execute(
"""
INSERT INTO dim_product
(source_product_id, sku, product_name, brand, category,
valid_from, valid_to, is_current)
VALUES (%s, %s, %s, %s, %s, %s, 'infinity', true)
RETURNING product_sk
""",
(
source_product_id, sku, product_name, brand,
category, effective_at,
),
).fetchone()
return row[0]
The close-and-insert is one transaction, so a failed insert cannot leave the product without a current version.
This operation assumes one active history per source product identifier. If identifiers are only unique within a source system, include that system in both the natural key and the advisory-lock key. The source adapter must also deduplicate repeated changes before calling this function; identical attributes are a no-op, but conflicting changes at the same effective timestamp need an explicit source-specific rule.
Important flows
The first flow turns a source extract into a committed, visible subject-area load. A watermark advances only after the data transaction and validation succeed.
sequenceDiagram
participant Source
participant Ingest
participant ObjectStore
participant Queue
participant Worker
participant Warehouse
Source->>Ingest: Provide source range
Ingest->>ObjectStore: Write immutable extract
Ingest->>Queue: Publish object key and load id
Queue->>Worker: Deliver notification
Worker->>ObjectStore: Read extract
Worker->>Warehouse: Validate and load transaction
Worker->>Warehouse: Record counts and watermark
Worker->>Queue: Acknowledge messageAcknowledging a notification follows the durable warehouse commit, allowing safe redelivery after worker failure.
The second flow resolves a fact using the product version valid at the sale time. Unknown or uncovered keys are quarantined rather than silently mapped to a current version.
sequenceDiagram
participant Worker
participant Warehouse
participant Quarantine
Worker->>Warehouse: Find product version at event time
alt Version exists
Warehouse-->>Worker: Return product surrogate key
Worker->>Warehouse: Insert fact with source id
else No version covers event time
Warehouse-->>Worker: No matching version
Worker->>Quarantine: Store row and validation reason
endTemporal resolution is part of fact loading, so dimension history directly determines historical fact meaning.
Critical lifecycle: applying a product version
Treat the dimension update as a small state transition. For each natural key, compare incoming tracked attributes with the current version. A no-op does not create history. A forward-effective change closes the current version and inserts its successor atomically. A change that predates the current version is a correction workflow, not a normal update.
flowchart TD
start["Receive product change"] --> lock["Lock natural key"]
lock --> current["Read current version"]
current --> compare{"Tracked attributes changed"}
compare -->|No| noop["Keep current version"]
compare -->|Yes| time{"Effective time is forward"}
time -->|No| correction["Quarantine for correction workflow"]
time -->|Yes| close["Close current validity interval"]
close --> insert["Insert new current version"]
insert --> commit["Commit transaction"]
noop --> commitOnly a forward-effective attribute change creates a new version in the normal load path.
Failure modes, security, observability, scaling and trade-offs
Failure modes and recovery
| Failure | Detection and response |
|---|---|
| Duplicate queue delivery | Use the load ID and source keys for idempotent processing; acknowledge only after commit. |
| Worker stops during a batch | Let the message retry, or resume from recorded batch boundaries. Do not advance the watermark for partial work. |
| Unknown product, store, or date | Quarantine with source identifiers and reason; alert on rate and age, not on every row. |
| Out-of-order dimension change | Reject from the normal path and route to controlled correction/restatement. |
| Database failover or transient connection loss | Retry bounded transactions with backoff; do not assume a transaction committed unless its result is confirmed. |
| Reporting replica lag | Return the replica’s watermark, or route freshness-sensitive reads to the primary under a controlled policy. |
| Bad source batch | Stop promotion, preserve the raw extract, and correct or explicitly reject the range before replay. |
Keep raw inputs long enough to support the agreed recovery and audit window. A replay should use the original source range and transformation version where possible. If transformation logic has changed, label the run accordingly and compare its counts and key metrics with the prior result before replacing published data.
Security
Use separate identities for ingestion, warehouse loading, and reporting. The loader can write staging and modeled schemas; the API can read only approved views or functions. Avoid granting report users direct access to raw source data by default.
- Store source credentials in a managed secret store and rotate them using the platform’s normal identity controls.
- Encrypt transport and managed storage, and restrict database network access to required workloads.
- Apply row-level authorization to store or region scope where required. Enforce it in the server-side query path; do not rely on UI filters.
- Treat raw extracts as sensitive data. Minimize copied personal data, define retention, and ensure logs never contain credentials or unnecessary customer attributes.
- Audit administrative access and schema changes. Test authorization boundaries with both allowed and denied cases.
Observability
Instrument ingestion, transformations, database operations, and API requests with a shared trace context and load_id. Logs should be structured and useful for replay without duplicating source payloads. For example:
{
"timestamp": "2026-11-17T12:15:00Z",
"level": "INFO",
"event": "warehouse_load_completed",
"load_id": "9c7789a5-9b5b-4bf5-9f3d-a17f0bb89a5a",
"subject": "sales",
"source_range": "2026-11-16",
"rows_read": 120450,
"rows_loaded": 120450,
"rows_quarantined": 0,
"duration_ms": 18400,
"watermark": "2026-11-16T23:59:59Z"
}
Log identifiers and counts needed for diagnosis, not the full source record.
For Python workers, OpenTelemetry spans can connect queue receipt, object retrieval, validation, and warehouse loading. Export through the organization’s configured OpenTelemetry SDK and collector; avoid recording sensitive attributes.
from opentelemetry import trace
tracer = trace.get_tracer("retail-warehouse")
def load_subject(load_id: str, subject: str, object_key: str) -> None:
with tracer.start_as_current_span("warehouse.load") as span:
span.set_attribute("warehouse.load_id", load_id)
span.set_attribute("warehouse.subject", subject)
span.set_attribute("warehouse.object_key", object_key)
validate_and_load(object_key, load_id)
Attach stable operational identifiers to the span; keep credentials and row-level payloads out of telemetry.
Track service RED signals—request rate, errors, and duration—for the API and worker-facing endpoints. Track resource USE signals—utilization, saturation, and errors—for database storage and compute, worker capacity, queue depth, and cache. Also track domain signals: source-to-warehouse row reconciliation, quarantine volume, SCD version counts, and watermark age. Do not use product IDs or load IDs as metric labels; their cardinality belongs in logs and traces.
| SLI | Initial SLO assumption | Measurement |
|---|---|---|
| Sales data freshness | 99% of daily loads published within 2 hours of the agreed source cutoff | Source cutoff to completed sales watermark |
| API availability | 99.9% of monthly requests to approved metrics endpoints succeed | Successful authorized requests divided by eligible requests |
| API latency | 95% of bounded aggregate requests complete within 2 seconds | Server-side latency for successful requests |
| Load correctness | 100% of published loads pass required key and reconciliation checks | Published runs passing validation |
These are starting targets for planning, not guarantees; set final objectives from business reporting windows and measured staging results. Page on sustained API availability or latency SLO burn, a load past its freshness deadline, a growing queue with no worker progress, database saturation that threatens recovery, or a reconciliation failure that blocks publication. Route individual quarantined rows and non-urgent quality trends to an owner queue or dashboard unless they indicate a systemic issue.
Scaling and trade-offs
This design intentionally favors understandable history and governed queries over real-time freshness or unrestricted flexibility. PostgreSQL is a practical starting point when the assumed data volume and concurrency fit a measured, managed deployment. It is not a universal analytical engine. Validate representative fact loads and dashboard queries in staging before committing to a long-term capacity plan.
Scale in this order:
- Bound extract sizes and batch transactions; tune indexes using observed query plans.
- Partition the largest fact tables when measured maintenance, retention, or query patterns justify it. Choose partition keys that match pruning and lifecycle needs.
- Keep reporting reads off the write path where replica freshness is acceptable; expose the replica watermark.
- Add worker concurrency only where independent subject areas or source ranges can be processed safely. Serialize changes per dimension natural key.
- If the warehouse’s write or scan workload no longer fits the measured PostgreSQL operating envelope, migrate the analytical storage layer deliberately. Preserve the star schema’s grain, keys, and metric semantics across the change.
Caching helps repeated aggregate requests but makes freshness and authorization part of the cache contract. A short TTL is not a correctness policy by itself. Key entries by all scope-affecting filters, and surface the underlying watermark to consumers.
The primary trade-off in SCD Type 2 is more rows and more involved fact loading in exchange for reliable historical attribution. Type 1 overwrite is simpler and may be appropriate for attributes that should always reflect corrected current truth, but it must not be applied indiscriminately to attributes used to interpret past results. The trade-off in conformed dimensions is shared consistency versus coordinated change: adding a shared attribute or redefining a hierarchy affects multiple subject areas, so assign clear ownership and version the semantic contract.
Phased implementation plan
- Agree on semantics. Document each fact grain, metric formula, tracked SCD attributes, source natural keys, late-arrival behavior, and correction policy. Have analysts and source owners review concrete examples.
- Build a vertical slice. Load one sales source into raw storage, create the product/store/date dimensions and sales fact, and expose one governed sales query. Include load IDs, validation, and a replay test from the beginning.
- Add history and recovery. Implement Type 2 changes, event-time dimension resolution, quarantine handling, and controlled correction procedures. Test duplicate delivery, worker interruption, and out-of-order changes.
- Add subject areas. Introduce inventory and returns with their own grains, then verify they use the conformed dimensions and shared definitions. Add reconciliation and freshness reporting per subject.
- Harden and promote. Run staging load tests against representative volumes, validate query plans and replica lag, configure access controls and backups, and exercise recovery. Ship schema and worker changes through CI/CD with a migration and rollback plan.
- Review against measured use. After real workloads are observable, revisit partitions, indexes, cache behavior, replica placement, retention, and SLO targets. Change the architecture in response to measured bottlenecks rather than assumed future scale.
This article was generated with AI. #AIGenerated