# Data & Analytics Architecture

## Purpose
This skill provides architectural knowledge for designing and evaluating data platforms,
data lakehouses, and analytical solutions. Apply it when designing the structure of a
data solution or evaluating an existing one.

## Medallion Architecture

### Bronze Layer (Raw)
- Stores data exactly as received from source systems — no transformation, no cleaning.
- Preserves the full history of source data for reprocessing.
- Tables: `Store_{SourceSchema}_{SourceTable}` (append-only or full snapshot depending on source).
- Access views: `Access_{SourceSchema}_{SourceTable}` (current state or delta).
- **Rule**: Never modify Bronze data. If the source changes, capture the new state as a new version.

### Silver Layer (Integrated)
- Stores cleaned, integrated, historized data using the **Lean Data Vault (LDV)** pattern.
- Each source entity maps to: Key table + Hist1 table + Access view.
  - `Store_{Schema}_{Table}_Key` — Surrogate key + business key. Insert-only.
  - `Store_{Schema}_{Table}_Hist1` — All attributes + ValidFrom / ValidTo. Full history.
  - `Access_{Schema}_{Table}_Actual` — Current state view over Hist1.
- **Rule**: Silver is the single source of truth for integrated data.

### Gold Layer (Consumption)
- Stores domain-optimized structures for reporting and analytics: **Star Schema**.
  - `Store_Dim{Entity}` — Dimension: surrogate key, business key, and attributes.
  - `Store_Fact{Subject}` — Fact: measure columns + FK to all relevant dimensions.
- **Rule**: Fact tables contain only measures and foreign keys — no descriptive attributes.

## Key Design Principles

### Metadata-driven development
Define data structures as metadata rather than hand-written DDL.
Enables: automated deployment, impact analysis, lineage tracking, and change management.

### Insert-only history (Hist1 pattern)
Never update or delete historical records. Closed records get a ValidTo timestamp;
new records are inserted with ValidFrom = now.

### Surrogate keys
All dimension and key tables use system-generated surrogate keys.
Business keys are stored alongside but never used as join keys in Gold.

### Naming conventions
- Bronze: `Store_{SourceSchema}_{SourceTable}` / `Access_{SourceSchema}_{SourceTable}`
- Silver Key: `Store_{Schema}_{Table}_Key`
- Silver Hist: `Store_{Schema}_{Table}_Hist1`
- Silver View: `Access_{Schema}_{Table}_Actual`
- Gold Dim: `Store_Dim{Entity}`
- Gold Fact: `Store_Fact{Subject}`

## Common Anti-Patterns
- **Skipping Silver**: Joining Bronze directly to Gold creates fragile, unmaintainable pipelines.
- **Attributes in Fact tables**: Violates Star Schema and breaks BI tool optimizations.
- **UPDATE/DELETE in history tables**: Breaks the auditability guarantee of the LDV pattern.
- **Mixed layer responsibilities**: A Bronze table that transforms data defeats layering.
- **Inconsistent naming**: Even one exception breaks automated tooling.
