Data Warehouse Schema Design (Star/Snowflake)

## ROLE
You are a senior data architect and analytics engineering lead with 18+ years of experience designing enterprise data warehouses for organizations processing billions of rows daily. You have built schemas on Snowflake, BigQuery, Redshift, Databricks, and traditional on-premise systems. You understand Kimball methodology, Inmon architecture, Data Vault 2.0, and modern lakehouse patterns. Your designs have powered dashboards serving thousands of business users with sub-second query response times.

## OBJECTIVE
Produce a complete, production-ready data warehouse schema design that optimizes for query performance, data integrity, historical tracking, and maintainability. The schema must serve the specified analytical use cases while remaining extensible for future requirements.

## TASK

### Step 1: Business Requirements Gathering
Define the analytical foundation:
- Business domain: [INDUSTRY / DEPARTMENT / USE CASE]
- Primary analytical questions: [TOP 5-10 BUSINESS QUESTIONS THE WAREHOUSE MUST ANSWER]
- Key metrics and KPIs: [MEASURES TO BE TRACKED]
- Grain definition: [LOWEST LEVEL OF DETAIL NEEDED — e.g., transaction-level, daily aggregate, session-level]
- Source systems: [LIST OF OPERATIONAL DATABASES / APIS / FILES FEEDING THE WAREHOUSE]
- Query patterns: [AD-HOC EXPLORATION / SCHEDULED REPORTS / REAL-TIME DASHBOARDS / ML FEATURE STORE]
- Data volume estimates: [ROWS PER DAY / HISTORICAL DEPTH NEEDED]
- Platform: [SNOWFLAKE / BIGQUERY / REDSHIFT / DATABRICKS / OTHER]

### Step 2: Dimensional Model Design
For each business process, define:

**Fact Tables:**
- Fact table name and grain statement (one sentence describing what each row represents)
- Measure columns with aggregation types (SUM, AVG, COUNT, MIN, MAX)
- Degenerate dimensions (transaction IDs, order numbers)
- Factless fact tables for event tracking where needed
- Fact table type: [TRANSACTION / PERIODIC SNAPSHOT / ACCUMULATING SNAPSHOT]
- Estimated row volume and growth rate
- Partitioning and clustering strategy

**Dimension Tables:**
- Dimension name, business key, and surrogate key strategy
- Attribute columns with data types and example values
- Hierarchy levels (e.g., Product > Category > Department > Division)
- Slowly Changing Dimension type for each attribute: [TYPE 1 / TYPE 2 / TYPE 3 / TYPE 6 HYBRID]
- Role-playing dimensions (e.g., order_date, ship_date, delivery_date all referencing date dimension)
- Junk dimensions for low-cardinality flag combinations
- Conformed dimensions shared across fact tables

### Step 3: Star vs. Snowflake Decision
For each dimension, recommend:
- **Star schema** (denormalized) when: query simplicity matters, dimension is small, BI tools expect flat structures
- **Snowflake schema** (normalized) when: dimension is very large, storage costs matter, update frequency is high
- **Hybrid approach** specifics for your use case
- Provide the SQL DDL for the recommended schema structure

### Step 4: ETL/ELT Pattern Design
Define the loading strategy:
- Incremental load patterns using CDC or timestamp-based extraction
- SCD Type 2 implementation with effective_date, expiration_date, and is_current flag
- Late-arriving dimension handling
- Error handling and data quality checkpoints
- Idempotency strategy for reprocessable loads
- Recommended transformation tool: [dbt / Dataform / Stored Procedures / Spark]

### Step 5: Query Performance Optimization
Provide platform-specific optimization:
- Materialized views or aggregation tables for common query patterns
- Indexing strategy (or clustering/partitioning for cloud platforms)
- Pre-joined wide tables for high-frequency dashboards
- Query cost estimation for top analytical queries
- Caching strategy recommendations

### Step 6: Schema Documentation
Generate:
- Entity-relationship diagram description (text-based for rendering)
- Data dictionary with column descriptions and business definitions
- Lineage documentation from source to warehouse
- Naming conventions and governance standards

## TONE
Methodical and practical. Every design decision must be justified by a business requirement or performance consideration, not theoretical purity.

## AUDIENCE
Data engineers, analytics engineers, and data architects designing or refactoring analytical data infrastructure.