Contract
- Input: source data descriptions, analytical questions, query patterns.
- Output: warehouse schema design + query optimisation notes.
- Side effects: none.
- Dependencies: none.
- Stop condition: schema saved; dimensions/facts defined.
- Risk: low.
- Boundary: designs schema; does not create warehouse resources.
Data Warehouse Modeling
Design an analytical warehouse schema — star / snowflake / OBT — with dimensions, facts, and query optimisation.
Process
1. Identify analytical questions
- What questions must be answered? (revenue by region/month/product, customer lifetime value, conversion funnel, inventory turnover)
- What metrics? (sum, count, average, rate, ratio)
- What filter dimensions? (date, region, product category, customer segment)
Completion criterion: questions saved.
2. Design dimensions
- Time: date, month, quarter, year, fiscal period; hierarchies (year → quarter → month → day).
- Geography: country, state, city, zip; hierarchies.
- Product: category, subcategory, brand, SKU; hierarchies.
- Customer: segment, tier, acquisition channel, lifetime value band.
- Other: promotion, channel, device, experiment group.
Completion criterion: dimensions with hierarchies saved.
3. Design facts
- Transactional: order events, transaction records, attendance, usage logs.
- Snapshot: daily balances, inventory levels, account states.
- Aggregate: pre-computed summaries for fast queries (if needed).
Completion criterion: facts with grain (one row per event / per day / per customer per day) saved.
4. Schema layout
- Star: dimensions directly connect to fact; simple; fast for common queries.
- Snowflake: dimensions normalised (e.g. product category → subcategory → brand → SKU as separate tables); saves storage; more joins.
- OBT (One Big Table): flatten everything into one wide table; fastest queries; may require more storage and maintenance.
Completion criterion: layout selected with justification.
5. Indexing and partitioning
- Partitioning: by date (most common), region, category; use time-based for rolling windows.
- Clustering: by frequently-filtered columns (e.g. region + product).
- Materialised views: for slow aggregates (monthly revenue, quarterly trends).
- Indexes: primary keys, foreign keys, frequently queried columns.
Completion criterion: optimisation notes saved.
1---2name: data-warehouse-modeling-23description: Design analytical warehouse schemas — star / snowflake / OBT — with dimension, fact, and bridge tables; partitioning; indexing; and query optimization.4---56## Contract78- **Input:** source data descriptions, analytical questions, query patterns.9- **Output:** warehouse schema design + query optimisation notes.10- **Side effects:** none.11- **Dependencies:** none.12- **Stop condition:** schema saved; dimensions/facts defined.13- **Risk:** low.14- **Boundary:** designs schema; does not create warehouse resources.1516# Data Warehouse Modeling1718Design an **analytical warehouse schema** — star / snowflake / OBT — with dimensions, facts, and query optimisation.1920## Process2122### 1. Identify analytical questions23- What questions must be answered? (revenue by region/month/product, customer lifetime value, conversion funnel, inventory turnover)24- What metrics? (sum, count, average, rate, ratio)25- What filter dimensions? (date, region, product category, customer segment)2627**Completion criterion:** questions saved.2829### 2. Design dimensions30- **Time:** date, month, quarter, year, fiscal period; hierarchies (year → quarter → month → day).31- **Geography:** country, state, city, zip; hierarchies.32- **Product:** category, subcategory, brand, SKU; hierarchies.33- **Customer:** segment, tier, acquisition channel, lifetime value band.34- **Other:** promotion, channel, device, experiment group.3536**Completion criterion:** dimensions with hierarchies saved.3738### 3. Design facts39- **Transactional:** order events, transaction records, attendance, usage logs.40- **Snapshot:** daily balances, inventory levels, account states.41- **Aggregate:** pre-computed summaries for fast queries (if needed).4243**Completion criterion:** facts with grain (one row per event / per day / per customer per day) saved.4445### 4. Schema layout46- **Star:** dimensions directly connect to fact; simple; fast for common queries.47- **Snowflake:** dimensions normalised (e.g. product category → subcategory → brand → SKU as separate tables); saves storage; more joins.48- **OBT (One Big Table):** flatten everything into one wide table; fastest queries; may require more storage and maintenance.4950**Completion criterion:** layout selected with justification.5152### 5. Indexing and partitioning53- **Partitioning:** by date (most common), region, category; use time-based for rolling windows.54- **Clustering:** by frequently-filtered columns (e.g. region + product).55- **Materialised views:** for slow aggregates (monthly revenue, quarterly trends).56- **Indexes:** primary keys, foreign keys, frequently queried columns.5758**Completion criterion:** optimisation notes saved.
quantumquirkxyz