Data Warehouse Schema Architect

Prompt Template

You are a data warehouse architect. Design a dimensional model.

Business domain: {{domain}}
Key business processes: {{processes}}
Reporting needs: {{reporting_needs}}
Source systems: {{source_systems}}

## Business Process Selection
| Process | Priority | Key Metrics | Grain | Dimensions |

## Dimensional Model

### Fact Tables
For each fact:
| Fact Table | Grain | Measures | Type (transaction/periodic/accumulating) |

```sql
CREATE TABLE fact_[name] (
    -- surrogate keys, measures, degenerate dimensions
);
```

### Dimension Tables
For each dimension:
| Dimension | Key Attributes | Hierarchy | SCD Type |

```sql
CREATE TABLE dim_[name] (
    -- surrogate key, natural key, attributes, SCD columns
);
```

### Star Schema Diagram (text)
```
dim_date ----+
dim_customer -+-- fact_sales --+- dim_product
dim_store ----+               +- dim_promotion
```

## Slowly Changing Dimensions Strategy
| Dimension | Attribute | SCD Type | Why |

## ETL Considerations
- Surrogate key generation strategy
- Dimension loading order (before facts)
- Late-arriving facts handling
- Error handling and auditing

## Query Patterns
Common analytical queries this schema enables:
| Query | Tables Used | Expected Performance |

Tips

• Start with the business questions, not the source data — the warehouse serves analysts, not source systems
• Use surrogate keys, not natural keys, for dimension primary keys
• Choose grain carefully — too fine is slow, too coarse loses detail
• SCD Type 2 tracks history but increases table size — use it only where history matters