---
name: "data-warehouse-modeling"
category: "data"
maturity: "stable"
version: "1"
description: "Design analytical warehouse schemas — star / snowflake / OBT — with dimension, fact, and bridge tables; partitioning; indexing; and query optimization."
license: "MIT"
capabilities: ""
outputs: ""
sideEffects: []
dependencies: []
stopCondition: "Schema design saved; dimensions and facts defined; optimisation notes present."
risk: "low"
trustTier: "1"
maxIterations: "5"
promptVersion: "2.0"
artifactType: "data"
modelTier: "reasoning"
evaluators: ["behavioral", "regression"]
fixturesPath: ""
diataxis: "how-to"
tags: ["data"]
compatibility: []
approvalRequired: "false"
approvalFor: []
---

## Contract

- Input: source data descriptions, analytical questions, query patterns.
- Output: warehouse schema design + query optimisation notes.
- Scope: designs schema; does not create warehouse resources.
- Rule: designs schema; does not create warehouse resources.
- Rule: documented standards override defaults; explicit project rules take precedence.
- Rule: if blocked by missing context or dependencies, surface the blocker before proceeding.

## Provenance

| Question | Answer |
|---|---|
| What is the source of truth? | The user's request, originating spec/issue, and the skill's declared outputs |
| What is in scope? | Work covered by the skill's acceptance criteria and completion rules |
| What is explicitly out of scope? | Files, behaviors, and decisions outside the skill's declared boundary |
| Who or what consumes this artifact afterward? | The next skill in the workflow or the user |
| What evidence proves it is done? | Completion criteria met, artifact saved, validation passed |
| What risk remains? | Subjective judgment calls, missing context, or external dependency failures


## Artifact

Emit `DataWarehouseModelingArtifact` as both:
- JSON: `.agents/skills/platform/artifacts/data-warehouse-modeling/{identifier}.json`
- Markdown view: same filename with `.md` extension


# 
# 
# 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.

## Completion

- the skill's completion criteria are explicitly checked
- the artifact is saved and validated
- any blockers or skipped validations are documented
- the next consumer is identified or the work is handed off
---
@include .agents/skills/platform/contract-base.xml