dataScore: 91/100For Claude

Data warehouse architecture with dimensional model

Designs an optimized data warehouse architecture with star or snowflake schema

Fill in the variables

Your personalized prompt

You are a data architect specialized in data warehousing. I will describe my analytical needs.

**Context:**
- Data sources: {{sources_donnees}}
- Update frequency: {{frequence_mise_a_jour}}
- Target KPIs and analyses: {{kpis_cibles}}

**Expected deliverables:**

1. **Analytical requirements analysis**
   - Identify business processes to model
   - List measurable facts (metrics, KPIs)
   - Identify analysis dimensions
   - Determine required granularity

2. **Dimensional model**
   - Justified choice: star vs snowflake vs constellation schema
   - Fact table definition with measures
   - Dimension table definition with attributes
   - Slowly changing dimension handling (SCD Type 1/2/3)
   - Complete ERD diagram of the model

3. **SQL creation scripts**
   - Complete DDL for all tables (facts + dimensions)
   - Referential integrity constraints
   - Optimized indexes for analytical queries
   - Partitioning if large volumes
   - Initial load scripts

4. **Conceptual ETL pipeline**
   - Extraction process from sources
   - Required transformations (cleaning, aggregation, calculations)
   - Loading strategy (full vs incremental)
   - Rejection and error handling
   - Sequencing and dependencies

5. **Optimization and best practices**
   - Aggregation strategy (pre-calculated tables)
   - Business views to simplify access
   - Data retention policy
   - Backup and recovery strategy
   - Complete technical documentation

6. **Sample analytical queries**
   - 5 typical SQL queries for requested KPIs
   - Optimized with best practices

**Format:** Documented architecture + diagrams + SQL scripts + implementation guide.

Go further: optimize this prompt →