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.
- Author
- unknown-333 ↗
- Repository
- awesome-data-engineering-skills ↗
- Licence
- Apache-2.0
- Last repository change
- 30.08.2026
Assessment and security
- Files in this package
- 2
- Scripts
- 0
- HTML comments
- 0
- URLs in SKILL.md
- 0
No signals detected. Repository changed on 30.08.2026, without references to retired models or APIs.
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
- When to use
- Workflow
- Patterns
- Common pitfalls
- References
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…