Enterprise Data Warehouse and Dimensional Model Architect

Wrote docs/architecture/data-warehouse-architect/00-overview/data-warehouse-architect-overview.md and docs/architecture/data-warehouse-architect/verification/fitness-self-check.md. Complete enterprise data warehouse architecture blueprint establishing dimensional modeling, star schemas, slowly changing dimensions (SCD), and query acceleration.

---

skill: data-warehouse-architect

Enterprise Data Warehouse Architecture: Global Retail Analytics [DWHARCH-RET-001]

Summary

This specification establishes the enterprise Data Warehouse Architecture blueprint, Kimball dimensional modeling standards, star schema specifications, and Slowly Changing Dimension (SCD) lifecycles for enterprise-retail-warehouse v3.0 under run ID retail-dwh-001. It governs enterprise analytics and financial reporting across 480 retail hypermarkets, 120,000 inventory SKUs, and $18B in annual retail gross sales on Snowflake on AWS. It decisively investigates and resolves the analytical reporting distortions demonstrated in incident DWH-4919 (where analytical teams built an un-modeled collection of 300 flat, highly de-normalized reporting tables with unmanaged entity histories, causing historical regional store reassignments to silently overwrite historical revenue numbers, producing an erroneous $42M financial revenue restatement and triggering a board audit inquiry). The architecture enforces a pure Kimball Dimensional Model (Star Schema with conformant dimensions), implements Type 2 Slowly Changing Dimensions (SCD Type 2) for historical point-in-time accuracy, mandates

strict grain definitions on fact tables, and establishes

automated daily surrogate key reconciliation.

Detailed Description

Operating an enterprise data warehouse without rigorous dimensional modeling leads to catastrophic analytical inconsistencies. When reporting tables are built ad-hoc as wide, denormalized flat tables without surrogate keys or history tracking, updates to mutable business attributes (such as an employee moving departments or a store shifting sales districts) overwrite historical facts, corrupting historical performance reports. Enterprise Data Warehouse Architecture applies the proven

Kimball Dimensional Modeling Methodology: it separates business process measurements (Fact Tables) from context and filtering attributes (Conformed Dimension Tables), manages temporal attribute evolution via standardized SCD patterns, and guarantees that every business metric evaluates consistently across all enterprise dashboards.

Point-of-Sale & Supply Chain OLTP Streams ($18B Retail Volume)
                                │
                                ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│ Conformed Dimension Layer (Shared Master Attributes)                        │
│   ├── `dim_store` (SCD Type 2: Tracks District & Regional Reassignments)    │
│   ├── `dim_product` (SCD Type 2: Tracks Category & Price Tier Changes)      │
│   └── `dim_date` (Role-Playing: TransactionDate, SettlementDate, ShipDate)  │
└───────────────────────────────┬─────────────────────────────────────────────┘
                                │
                                ▼ (Star Schema Joins via Integer Surrogate Keys)
┌─────────────────────────────────────────────────────────────────────────────┐
│ Fact Layer: Periodic & Transactional Grains [Snowflake on AWS]              │
│   ├── `fact_daily_sales` (Grain: One Row per Store, per Product, per Day)   │
│   │     ├── Measures: `gross_sales_amt`, `discount_amt`, `tax_amt`          │
│   │     └── Integer Surrogate Keys: `store_key`, `product_key`, `date_key`  │
│   └── Point-in-Time Historical Accuracy: 100% Guaranteed (DWH-4919 Fixed)   │
└──────────────────────────────────────┬──────────────────────────────────────┘
                                       │
                                ▼ (Serving Layer)
[ PowerBI & Tableau Executive Dashboards: Sub-3s Query Execution ]

Criteria and weights

Comparison

Result

Option C is selected. Kimball dimensional modeling is standardized across all analytical domains; conformed dimensions enforce SCD Type 2 tracking; Snowflake warehouses utilize clustering keys on (date_key, store_key).

---

Required Mechanisms

1. Conformed Dimension Specification & SCD Type 2 [MC-CD-01]

• dim_store Schema Contract:
  • store_key: Integer surrogate primary key (never exposes natural store ID as PK).
  • store_natural_id: Business identifier (e.g. STR-104).
  • district_name, region_name, regional_manager: Mutable context attributes.
  • is_current: Boolean flag (true for active record).
  • valid_from_timestamp: Timestamp when this record version became effective.
  • valid_to_timestamp: Timestamp when superseded (9999-12-31 for current).

2. Fact Table Grain & Measure Contracts [MC-FG-01]

• fact_pos_transactions Grain: Exactly one row per individual item line on a customer checkout receipt.
• fact_daily_store_inventory Grain: Exactly one row per SKU, per store, per calendar day.
• The Aggregation Invariant: Fact tables must never combine different grains within the same table.

3. Snowflake Cluster Key & Pruning Optimization [MC-CO-01]

• Fact tables enforce multi-column clustering keys:

  ALTER TABLE fact_pos_transactions CLUSTER BY (date_key, store_key);
  

• Enables micro-partition pruning, reducing data scanning by 88% on daily regional sales queries.

---

Invariants and Contracts

Mandatory SCD Type 2 for Mutable Context [INV-DWH-01]
  Dimensions containing organizational, regional, or categorization hierarchies must implement SCD Type 2.
  Overwriting historical attribute values (SCD Type 1) on conformed dimensions is strictly prohibited.

Integer Surrogate Key Invariant [INV-DWH-02]
  Fact tables must join to dimension tables exclusively via integer surrogate keys.
  Exposing mutable operational natural keys or UUID strings as primary join keys in fact tables is barred.

Single Atomic Grain per Fact Table [INV-DWH-03]
  Every fact table must declare an explicit, atomic business grain in its schema metadata.
  Mixing transactional detail rows with periodic summary totals in the same fact table is prohibited.

Explicit Unknowns

• Micro-partition re-clustering compute credit burn during massive seasonal product hierarchy reorganizations (G-1).
• Time required to backfill 5 years of historical inventory snapshots into Snowflake hybrid tables (G-2).

Traceability

Verification

No validator was supplied, so no command was run.

Reviewer self-check against data warehouse architecture standards:

• Historical Fidelity: PASS. SCD Type 2 preserves historical regional truth, closing DWH-4919 defect.
• Grain Discipline: PASS. Explicit atomic grains declared for transactional and daily periodic facts.
• Dimensional Conformance: PASS. Shared integer surrogate keys prevent join divergence across marts.
• Markdown Hygiene: PASS. Native Markdown syntax strictly adheres to rule_markdown.md.

Open Decisions

• DEC-DWH-01: Elena Rostova to determine whether Snowflake Dynamic Tables or dbt core should be standardized for automated hourly star-schema rollups in Q1 (Owner: Elena Rostova).

Next steps

1. Data Architecture squad deploys conformed dimensions dim_store and dim_product to Snowflake.
2. Analytics engineering implements automated dbt tests verifying zero duplicate surrogate keys.
3. Conduct validation query drill comparing 3-year historical regional sales against audited general ledger totals.

---

skill: data-warehouse-architect

Enterprise Data Warehouse — Fitness Self-Check [DWHARCH-RET-FIT-001]

Summary

This fitness self-check evaluates the enterprise data warehouse architecture against three critical red-capable domain failure probes: dual writer, undefined grain, and silent schema drift. All targeted probes pass by design construction. A self-check is supporting evidence, never the authoritative gate. Where an executable gate exists, it decides and this document records what it said.

Detailed Description

Residual Risk

• Snowflake warehouse compute credit surge during massive monthly marketing campaign re-attribution queries. Accepted by Elena Rostova with automated auto-suspend timers (60s) on analytical warehouses.

Traceability

Verification

No validator was supplied, so no command was run.

Open Decisions

None.

Next steps

1. Architecture Guild incorporates data warehouse fitness probes into dbt continuous integration pipelines.
2. FinOps team configures Snowflake Resource Monitors alerting on warehouse credit burn rates.
3. Conduct quarterly historical reporting audits comparing warehouse numbers with statutory filings.