---
description: Data Modeling
alwaysApply: false
---

# Data Modeling

Patterns for scalable, maintainable data models.

## Medallion Architecture

- **Bronze/Raw** — exact source copy, append-only, no transforms
- **Silver/Curated** — validated, typed, deduplicated, business keys applied
- **Gold/Marts** — aggregated, business-ready, optimized for consumption

## Dimensional Modeling (Star Schema)

- **Fact tables** — immutable events with measures and FK references to dimensions; partition by date
- **Dimension tables** — descriptive attributes with surrogate keys for SCD support

```sql
-- Fact: measures + foreign keys, partitioned by date
CREATE TABLE facts.orders (
    order_id STRING, customer_key BIGINT, product_key BIGINT,
    quantity INT, total_amount DECIMAL(12,2), _loaded_at TIMESTAMP
) PARTITIONED BY (order_date);

-- Dimension: surrogate key + SCD Type 2 tracking
CREATE TABLE dims.customers (
    customer_key BIGINT GENERATED ALWAYS AS IDENTITY,
    customer_id STRING, name STRING, segment STRING,
    effective_from DATE, effective_to DATE, is_current BOOLEAN
);
```

## Slowly Changing Dimensions

- **Type 1** — overwrite in place; no history
- **Type 2** — close old row (`is_current=false`, set `effective_to`), insert new row; full history
- **Type 3** — add `previous_*` column; limited history (one prior value)

## Schema Design Rules

- **Types**: IDs as STRING, money as DECIMAL, dates as DATE/TIMESTAMP, flags as BOOLEAN
- **Naming**: tables plural snake_case, columns singular snake_case, FKs as `*_key`, booleans as `is_*`/`has_*`, timestamps as `*_at`, dates as `*_date`
- **Avoid wide tables** — normalize into fact + dimension; join at query time

```sql
-- Bad: float for money, string for dates
unit_price FLOAT, order_date STRING

-- Good: precise types
unit_price DECIMAL(10,2), order_date DATE
```

## Partitioning Strategies

- **Time-based** — most common; partition by date for time-series data
- **Categorical** — partition by high-cardinality enum (e.g., event_type)
- Avoid over-partitioning; use clustering/bucketing for secondary access patterns

## Alternative Approaches

- **Data Vault** — Hub (business keys) + Link (relationships) + Satellite (attributes with history); for enterprise DWH
- **One Big Table (OBT)** — fully denormalized for BI tools; fast reads but harder updates

## Anti-Patterns

- Floating-point types for monetary values
- Storing dates as strings (breaks date math, inconsistent formats)
- Over-partitioning on high-cardinality columns (millions of tiny files)