data-modeling

Stars0

Forks0

UpdatedJune 10, 2026 at 12:49

Design warehouse and semantic data models. Use for grain definition, fact/dimension design, star schema, Kimball, Data Vault, SCD, bridge tables, surrogate keys, semantic metrics, and model review.

Installation

Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.

Source

datacoolie

datacoolie/dekit

View GitHub Repository View Creator Repositories

Download

Related occupationsSOC

Based on SOC occupation classification

Database ArchitectsComputer and Mathematical Occupations·SOC 15-1243

SKILL.md

readonly

name	data-modeling
description	Design warehouse and semantic data models. Use for grain definition, fact/dimension design, star schema, Kimball, Data Vault, SCD, bridge tables, surrogate keys, semantic metrics, and model review.

Data Modeling

Use this skill when data shape, grain, history, or business meaning is the main design problem.

Required Decisions

Grain: one row represents exactly what.
Model style: star schema, wide mart, Data Vault, hybrid, or operational table.
Entity keys: natural key, surrogate key, hash key, composite key.
Fact type: transaction, periodic snapshot, accumulating snapshot, factless.
Dimension behavior: Type 0/1/2/3/6, role-playing, conformed, junk, mini-dimension.
Relationship handling: one-to-many, many-to-many, bridge, hierarchy.
Metric contract: numerator, denominator, filters, time grain, late-arriving behavior.

Design Rules

Define grain before columns.
Facts contain measures and foreign keys; dimensions contain descriptive context.
Use surrogate keys when business keys can change or SCD history matters.
Use conformed dimensions for cross-domain analysis.
Use bridges only when many-to-many cannot be simplified without losing meaning.
Keep business logic centralized; duplicate metric logic is a defect.

Anti-Patterns

Table with no stated grain.
Fact table that mixes grains.
Surrogate key equals mutable business key.
SCD2 without valid-from, valid-to, current flag, and change detection.
Snowflaking by habit instead of measured need.
Metrics defined only in dashboard code.

Verification

Before finalizing a model:

State grain for every table.
Prove keys are unique at the declared grain.
Check referential integrity and unknown-member behavior.
Validate SCD history: no gaps, overlaps, or duplicate current rows.
Reconcile core measures to source or prior layer.
Document metric definitions and lineage.

More from this repository

same repository

wiki

datacoolie/dekit

Build and maintain an internal technical LLM wiki when the user requests wiki work or a verified major or architectural change qualifies for automatic wiki review. Use for project memory, architecture notes, contracts, runbooks, decisions, glossary, incremental ingest, delta status, and wiki health. Do not use merely because a routine task or plan completed.

2026-07-180

brainstorm

datacoolie/dekit

Explore ambiguous ideas and solution directions before evidence-backed research or implementation planning. Use for ideation, framing, assumption checks, option generation, product or architecture trade-offs, and deciding whether research or a plan is needed.

2026-07-030

research

datacoolie/dekit

Research technical options with source-backed recommendations. Use for technology evaluation, architecture trade-offs, library/framework choices, best practices, scalability, security, maintainability, or when current external facts matter.

2026-07-030

plan

datacoolie/dekit

Create implementation plans, architecture decisions, and phased roadmaps. Use for Standard or Complex changes, new pipelines, schema changes, migrations, multi-file features, technology choices, or when acceptance criteria and verification strategy are unclear.

2026-06-170

code-review

datacoolie/dekit

Review code and data pipeline changes for correctness, security, performance, maintainability, contract breaks, and missing verification. Use before merge, after implementation, for PRs, commits, pending diffs, or codebase risk scans.

2026-06-100

data-ingestion

datacoolie/dekit

Design data ingestion into landing/bronze layers. Use for source onboarding, transfer pattern selection, full/incremental/CDC scope, change detection, landing zones, schema drift, idempotent file or batch processing, and ingestion quality gates.

2026-06-100

name	data-modeling
description	Design warehouse and semantic data models. Use for grain definition, fact/dimension design, star schema, Kimball, Data Vault, SCD, bridge tables, surrogate keys, semantic metrics, and model review.

Data Modeling

Use this skill when data shape, grain, history, or business meaning is the main design problem.

Required Decisions

Grain: one row represents exactly what.
Model style: star schema, wide mart, Data Vault, hybrid, or operational table.
Entity keys: natural key, surrogate key, hash key, composite key.
Fact type: transaction, periodic snapshot, accumulating snapshot, factless.
Dimension behavior: Type 0/1/2/3/6, role-playing, conformed, junk, mini-dimension.
Relationship handling: one-to-many, many-to-many, bridge, hierarchy.
Metric contract: numerator, denominator, filters, time grain, late-arriving behavior.

Design Rules

Define grain before columns.
Facts contain measures and foreign keys; dimensions contain descriptive context.
Use surrogate keys when business keys can change or SCD history matters.
Use conformed dimensions for cross-domain analysis.
Use bridges only when many-to-many cannot be simplified without losing meaning.
Keep business logic centralized; duplicate metric logic is a defect.

Anti-Patterns

Table with no stated grain.
Fact table that mixes grains.
Surrogate key equals mutable business key.
SCD2 without valid-from, valid-to, current flag, and change detection.
Snowflaking by habit instead of measured need.
Metrics defined only in dashboard code.

Verification

Before finalizing a model:

State grain for every table.
Prove keys are unique at the declared grain.
Check referential integrity and unknown-member behavior.
Validate SCD history: no gaps, overlaps, or duplicate current rows.
Reconcile core measures to source or prior layer.
Document metric definitions and lineage.