Schema Design & Data Modeling
Design optimized table structures (star/snowflake schemas, fact/dimension tables) for your analytics use cases based on your source data.
Prompt Template
I need help designing a [schema type, e.g. star schema / snowflake schema / dimensional model]for [analytics use case or business domain].
My source tables are:
- [table_name_1] — [brief description of what it contains]
- [table_name_2] — [brief description of what it contains]
- [table_name_3] — [brief description of what it contains]
Key requirements:
- [Primary analytics questions to answer, e.g. "track patient outcomes by provider and facility"]
- [Performance considerations, e.g. "optimized for daily dashboard refreshes"]
- [Any constraints, e.g. "must support slowly changing dimensions for provider data"]
Please recommend fact and dimension tables, define their relationships,and suggest the appropriate grain for each fact table.
Industry Example
I need help designing a star schema for patient care analytics.
My source tables are:
- PATIENT_ENCOUNTERS — visit records with diagnosis codes, timestamps, and provider IDs
- PROVIDER_DIRECTORY — physician details including specialty, facility, and credentials
- BILLING_CLAIMS — insurance claims with procedure codes, amounts, and approval status
Key requirements:
- Track patient outcomes and readmission rates by provider, specialty, and facility- Optimized for weekly executive dashboard refreshes
- Must support slowly changing dimensions for provider affiliations
Please recommend fact and dimension tables, define their relationships,and suggest the appropriate grain for each fact table.