Star Schema

1. Source systems (OLTP databases, CRM, ERP) emit transactional records in a highly normalized form optimized for writes, not analytical reads.
2. The ETL/ELT pipeline extracts these records, conforms keys, and resolves natural business keys into surrogate integer keys for consistent joins.
3. The dimension loader populates descriptive Dimension tables (Customer, Product, Date, Store), applying Slowly Changing Dimension logic to preserve historical attribute versions.
4. The fact loader writes quantitative measures (sales amount, quantity, discount) into a central Fact table, referencing each dimension only by its surrogate key.
5. The data warehouse stores the star: one narrow, tall Fact table surrounded by wide, short Dimension tables in a single-join-hop layout.
6. BI tools and analysts query the model, joining facts to dimensions across predictable, indexed key paths and aggregating measures by descriptive attributes.
7. The query engine exploits the flat structure with star-join optimization and bitmap indexes to return aggregates fast.

• Fast, simple analytical queries vs. denormalization introduces controlled data redundancy in dimensions.
• Intuitive business-friendly model vs. ETL complexity grows to conform keys and manage SCD logic.
• Predictable single-hop joins vs. write performance is sacrificed, making it unsuitable for operational updates.
• Efficient storage of measures vs. schema changes to add dimensions can require rebuilding facts.

A national grocery retailer consolidates point-of-sale data from 1,200 stores into a Sales Fact table holding billions of line-item rows, each keyed to Date, Store, Product, and Promotion dimensions. Analysts pivot weekly revenue by region, category, and promotion type in seconds, because every query resolves through a single join hop rather than traversing a dozen normalized tables. When a product is re-categorized, SCD Type 2 preserves the prior category so historical trend reports remain accurate.

• Declare the grain first: Fix the exact meaning of one fact row (e.g., one line item per receipt) before loading; mixing grains silently double-counts measures when aggregated. Store only additive or semi-additive measures, and flag non-additive ratios so BI tools never SUM them.
• Late-arriving rows: When a fact references a dimension member not yet loaded, insert an inferred placeholder row and backfill its attributes later, rather than dropping or orphaning the fact.
• SCD Type 2 hygiene: Enforce exactly one current row per natural key using effective-date ranges plus a current flag; overlapping or gapped validity windows corrupt point-in-time joins. Reloads must be idempotent to avoid duplicate versions.
• Partition and prune: Partition the fact table on the date key so queries scan only relevant ranges; without this the narrow-tall table drives most of your scan cost and bill.
• Schema evolution: Adding a dimension means backfilling its surrogate key across historical facts; ship nullable/default keys first, then reprocess, to avoid a full rebuild blocking loads.
• Observability: Track fact row counts per load, orphaned-key rejection rates, SCD version growth, and per-dimension cardinality; sudden dimension bloat usually signals a conforming or key-resolution bug upstream.