Skip to content

hollandkevint/data-product-operator / skills/data-model-design

data-model-design

Verified

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 someone asks "how should I model this data?" or "should I denormalize?" For OMOP CDM patterns specifically, see healthcare-data-domain.

FileKindSize
SKILL.mdskill md3.7 KB
schema-patterns.mdmarkdown3.3 KB

schema-patterns.md

View on GitHub
# Schema Patterns Reference

Common dimensional modeling patterns for data products. Reference this when designing or reviewing data models.

## Pattern: Event Fact Table

The most common pattern for data products. One row per event.

```
fact_patient_visit
├── visit_id (PK)
├── patient_id (FK -> dim_patient)
├── provider_id (FK -> dim_provider)
├── facility_id (FK -> dim_facility)
├── visit_date_id (FK -> dim_date)
├── diagnosis_id (FK -> dim_diagnosis)
├── visit_type (inpatient, outpatient, ER, telehealth)
├── length_of_stay_days
├── total_charge_amount
└── claim_paid_amount
```

Grain: one row per visit. If a visit has multiple diagnoses, the primary diagnosis goes here. Secondary diagnoses go in a bridge table.

## Pattern: Periodic Snapshot

Use when you need "state at a point in time" rather than individual events.

```
fact_patient_monthly_snapshot
├── patient_id (FK)
├── snapshot_month_id (FK -> dim_date)
├── active_medication_count
├── open_condition_count
├── days_since_last_visit
├── risk_score
└── care_gap_count
```

Grain: one row per patient per month. Useful for trend analysis and cohort tracking.

## Pattern: Accumulating Snapshot

Use for processes with defined stages (claims processing, referral management).

```
fact_claim_lifecycle
├── claim_id (PK)
├── submitted_date_id (FK)
├── adjudicated_date_id (FK)
├── paid_date_id (FK)
├── denied_date_id (FK, nullable)
├── days_to_adjudicate
├── days_to_pay
├── claim_amount
└── paid_amount
```

Grain: one row per claim, updated as it progresses through stages.

## Pattern: Bridge Table for Many-to-Many

When a fact has multiple values for a dimension (patient with multiple diagnoses per visit):

```
bridge_visit_diagnosis
├── visit_id (FK -> fact_patient_visit)
├── diagnosis_id (FK -> dim_diagnosis)
├── diagnosis_rank (primary=1, secondary=2, ...)
└── present_on_admission_flag
```

Never flatten many-to-many into the fact table. Use a bridge.

## Pattern: Junk Dimension

Collect low-cardinality flags and indicators into a single dimension rather than cluttering the fact table:

```
dim_visit_profile
├── visit_profile_id (PK)
├── is_emergency (Y/N)
├── is_readmission (Y/N)
├── is_telehealth (Y/N)
├── insurance_type (commercial, medicare, medicaid, self-pay)
└── discharge_disposition (home, SNF, AMA, expired)
```

## Pattern: Conformed Dimensions

Dimensions shared across multiple fact tables must use identical keys and attributes:

- `dim_patient` used by visit facts, claim facts, and medication facts
- `dim_date` used by every fact table
- `dim_provider` used by visit and claim facts

Build conformed dimensions first. They are the backbone of a data product that needs to answer questions across domains.

## Naming Conventions

- Fact tables: `fact_<subject>` or `fact_<subject>_<grain>`
- Dimension tables: `dim_<subject>`
- Bridge tables: `bridge_<left>_<right>`
- Columns: snake_case, descriptive, include unit where ambiguous (`length_of_stay_days`, not `los`)
- Keys: `<table>_id` for surrogate keys, `<source>_<table>_id` for natural keys
- Dates: `<event>_date` for actual dates, `<event>_date_id` for foreign keys to dim_date
brainstormingobra/superpowersYou MUST use this before any creative work - creating features, building components, adding functionality, modifying behavior, or planning anything new, in software or out of it (a talk, a business, a renovation).Stars: 322kUpdated Claude CodeCodexVerifiedInstall
brand-guidelinesanthropics/skillsApplies Anthropic's official brand colors and typography to any sort of artifact that may benefit from having Anthropic's look-and-feel. Use it when brand colors or style guidelines, visual formatting, or company design standards apply.Stars: 198kUpdated Claude CodeCodexVerifiedInstall
frontend-designanthropics/skillsGuidance for distinctive, intentional visual design when building new UI or reshaping an existing one. Helps with aesthetic direction, typography, and making choices that don't read as templated defaults.Stars: 198kUpdated Claude CodeCodexVerifiedInstall
mcp-builderanthropics/skillsGuide for creating high-quality MCP (Model Context Protocol) servers that enable LLMs to interact with external services through well-designed tools. Use when building MCP servers to integrate external APIs or services, whether in Python (FastMCP) or Node/TypeScript (MCP SDK).Stars: 198kUpdated Claude CodeCodexVerifiedInstall