Skip to content
Skills Library
Data & analytics · Planning

modeling-dimensional-data

/modeling-dimensional-data

Design analytics data models using dimensional modeling — star and snowflake schemas, fact and dimension tables, grain declaration, surrogate keys, and slowly changing dimensions (SCD Type 1/2/3). Use when designing a warehouse schema, building marts, choosing a table grain, tracking history, or deciding fact vs dimension.

Licence
Apache-2.0
Last repository change
30.08.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
2
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 30.08.2026, without references to retired models or APIs.

Licence

Apache-2.0, otwarta: można używać i zmieniać, z zachowaniem licencji i informacji o zmianach.

What it does and when to use it

Design analytics data models using dimensional modeling — star and snowflake schemas, fact and dimension tables, grain declaration, surrogate keys, and slowly changing dimensions (SCD Type 1/2/3). Use when designing a warehouse schema, building marts, choosing a table grain, tracking history, or deciding fact vs dimension.

Original author description

Design analytics data models using dimensional modeling — star and snowflake schemas, fact and dimension tables, grain declaration, surrogate keys, and slowly changing dimensions (SCD Type 1/2/3). Use when designing a warehouse schema, building marts, choosing a table grain, tracking history, or deciding fact vs dimension.

How it works

  1. When to use
  2. Workflow
  3. Patterns
  4. Common pitfalls
  5. References
Original SKILL.md headings · 81 lines, 506 words
SKILL.md preview
---
name: modeling-dimensional-data
description: Design analytics data models using dimensional modeling — star and snowflake schemas, fact and dimension tables, grain declaration, surrogate keys, and slowly changing dimensions (SCD Type 1/2/3). Use when designing a warehouse schema, building marts, choosing a table grain, tracking history, or deciding fact vs dimension.
---

# Modeling Dimensional Data

## When to use

- Designing warehouse/mart tables for analytics or BI.
- Deciding a table's grain, or whether something is a fact or a dimension.
- Tracking attribute history over time (customer moved, product re-priced).
- Do NOT use for OLTP/application schema design (normalize instead).

## Workflow

```
- [ ] Pick the business process to model
- [ ] Declare the grain (one row = ...)
- [ ] Identify the dimensions (context: who/what/where/when)
- [ ] Identify the facts (numeric measures at that grain)
- [ ] Choose SCD behavior per dimension attribute
- [ ] Add surrogate keys and relationships
```

1. **Choose the process** (orders, sessions, payments) — one star per process.
2. **Declare the grain first** and write it down: "one row per order line." Every
   fact column must be true at that grain. Never mix grains in one fact table.
3. **Dimensions** carry descriptive context and are the columns users filter/group
   by. **Facts** are additive numeric measures.
4. **Pick SCD type per attribute** (see below) based on whether history matters.
5. **Use surrogate keys** (warehouse-generated) as primary/foreign keys; keep the
   source natural key as a separate column.

## Patterns

**Star schema** — one central fact table with foreign keys to denormalized
dimensions. Prefer this default: fewer joins, faster BI, easier to understand.
**Snowflake schema** normalizes dimensions into sub-tables; use only when a
dimension is huge and shared, accepting more joins.

**Fact table types:**

- _Transaction_ — one row per event (most common).
- _Periodic snapshot_ — one row per entity per period (daily balances).
- _Accumulating snapshot_ — one row per process instance, updated as it progresses.

**SCD types (per attribute):**

- _Type 1_ — overwrite; no history. Use for corrections.
- _Type 2_ — add a new row with `valid_from`/`valid_to` + `is_current`; preserves
  full history. The default when history matters.
- _Type 3_ — add a `previous_value` column; keeps only the prior value.

```sql
-- SCD Type 2 dimension row shape
customer_key      BIGINT      -- surrogate key (unique per version)
customer_id       VARCHAR     -- natural/business key (stable across versions)
name              VARCHAR
region            VARCHAR
valid_from        TIMESTAMP
valid_to          TIMESTAMP   -- NULL or 9999-12-31 for the current version
is_current        BOOLEAN
```

Join facts to the dimension version that was current at the event time using the
surrogate key captured at load time, not the natural key.

## Common pitfalls

- **Undeclared or mixed grain** — the root cause of double-counting. Declare it
  and enforce it with a uniqueness test.
- **Joining facts on natural keys** — breaks under SCD Type 2; join on the
  surrogate key resolved at event time.
- **Non-additive measures stored as additive** (ratios, percentages) — store the
  numerator and denominator, compute the ratio at query time.
- **Overusing snowflaking** — normalizing every dimension adds joins for little
  benefit in a columnar warehouse.
- **Nulls in dimension foreign keys** — use a dedicated "unknown" dimension row
  (key = -1) instead of NULL so joins stay inner and counts stay correct.

## References

- [SCD implementation patterns](references/SCD.md)
Files in this package (2)
  • SKILL.md 4 KB
  • references/SCD.md 2 KB

Versions

The database has no other skills named modeling-dimensional-data.

Database snapshot: 13.11.2026 · package version 86b401c251fb (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 90data-dimensional-modeling
    Use this skill when designing dimensional data models — Kimball star schemas, Data Vault 2.0, slowly changing dimensions, fact table design, or bus matrix development. This skill enforces: Kimball 4-step methodology (business process, grain, dimensions, facts), star schema…
  • 90powerbi-data-modeling
    Dimensional data modeling for Power BI and Fabric: star schema design, choice of grain, fact tables and dimensions, surrogate keys, slowly changing dimensions (SCD), role-playing dimensions, many-to-many bridge tables, date table, storage mode choice (Import / DirectQuery /…
  • 95data-mart
    Use when designing, building, or optimizing data marts — dimensional modeling (star/snowflake schemas), fact and dimension table design, conformed dimensions, slowly changing dimensions (SCD Types 1-6), aggregation strategies, ETL/ELT loading patterns, data mart bus…
  • 100star-snowflake-schema-dimensional-modeling
    Use this skill when designing analytical data models with centralized fact tables and denormalized dimension tables for BI reporting.
  • 95schema-design-interviewer
    A Data Warehouse and Lakehouse Schema Design Expert interviewer focused on dimensional modeling, star/snowflake schemas, analytics optimization, and modern lakehouse architectures. Use this agent when you need to practice designing fact and dimension tables, handling SCD types,…
  • 90data-model-design
    Dimensional modeling and schema design for data products. Star schema patterns, slowly changing dimensions, denormalization decisions, and architecture decision records. Use when designing data models, reviewing schema designs, choosing between normalization strategies, or when…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.