| license | Apache-2.0 |
| name | dimensional-modeler |
| description | Star schema, snowflake schema, SCD types, and Kimball methodology for analytical data modeling. Activate on: dimensional model, star schema, snowflake schema, SCD, fact table, dimension table, Kimball, grain, surrogate key. NOT for: dbt implementation (use dbt-analytics-engineer), warehouse tuning (use data-warehouse-optimizer). |
| allowed-tools | Read,Write,Edit,Bash(npm:*,npx:*,python:*) |
| category | Data & Analytics |
| tags | ["dimensional-modeling","star-schema","kimball","fact-table","scd"] |
| pairs-with | [{"skill":"dbt-analytics-engineer","reason":"dbt implements dimensional models as SQL transformations"},{"skill":"data-warehouse-optimizer","reason":"Physical model design affects warehouse query performance"},{"skill":"data-quality-guardian","reason":"Dimensional models need referential integrity tests"}] |
Dimensional Modeler
Design analytical data models using Kimball methodology with star schemas, slowly changing dimensions, and proper grain definition.
Activation Triggers
Activate on: "dimensional model", "star schema", "snowflake schema", "SCD", "fact table", "dimension table", "Kimball", "grain", "surrogate key", "conformed dimension", "bridge table"
NOT for: dbt SQL implementation → dbt-analytics-engineer | Warehouse performance tuning → data-warehouse-optimizer | OLTP schema design → relevant backend skill
Quick Start
- Identify the business process — what is being measured? (orders, sessions, payments)
- Declare the grain — one row equals what? (one order line item, one daily snapshot, one event)
- Choose dimensions — who, what, where, when, how (customer, product, store, date, channel)
- Define facts — measurable quantities at the grain (amount, quantity, duration, count)
- Handle change — SCD Type 1 (overwrite), Type 2 (versioned rows), Type 3 (previous column)
Core Capabilities
| Domain | Technologies |
|---|
| Methodology | Kimball, Inmon (Data Vault for staging) |
| Schema Types | Star schema, snowflake schema, galaxy schema |
| SCD | Type 0 (fixed), Type 1 (overwrite), Type 2 (versioned), Type 3 (column) |
| Fact Types | Transaction, periodic snapshot, accumulating snapshot, factless |
| Implementation | dbt, SQL DDL, modeling tools (dbtERD, dbdiagram.io) |
Architecture Patterns
Star Schema Design
┌──────────────┐
│ dim_date │
│──────────────│
│ date_key (PK)│
│ full_date │
│ year, quarter│
│ month, week │
│ is_holiday │
└──────┬───────┘
│
┌──────────────┐ ┌──────┴───────┐ ┌──────────────┐
│ dim_customer │ │ fct_orders │ │ dim_product │
│──────────────│ │──────────────│ │──────────────│
│ customer_key │←───│ customer_key │───→│ product_key │
│ customer_id │ │ product_key │ │ product_id │
│ name │ │ date_key │ │ name │
│ segment │ │ store_key │ │ category │
│ region │ │──────────────│ │ brand │
└──────────────┘ │ quantity │ └──────────────┘
│ unit_price │
│ discount_amt │ ┌──────────────┐
│ total_amount │ │ dim_store │
└──────┬───────┘ │──────────────│
│ │ store_key │
└───────────→│ store_name │
│ city, state │
└──────────────┘
Grain: one row per order line item
Facts: quantity, unit_price, discount_amt, total_amount
