# Lean Data Vault (LDV)

## Purpose
Lean Data Vault is a pragmatic adaptation of Data Vault for modern lakehouse environments.
It embraces Data Vault's strengths — auditability, historization, insert-only reliability —
while reducing the number of table types for faster, simpler implementation.
Apply this skill when designing or implementing the Silver (integrated) layer.

## Core Principle: Two Tables + One View

Every entity in the Silver layer maps to exactly three objects:

| Object | Purpose |
|---|---|
| Key table | Identity: surrogate key + business key |
| Hist table | History: all descriptive attributes, fully historized |
| Access view | Current state: exposes the latest record for consumers |

## Key Table

**Purpose**: Stores the surrogate key and business key(s) for one entity. Insert-only.

**Rules**:
- Insert only — records are never updated or deleted.
- One record per unique business key combination.
- Surrogate key is system-generated.
- Business keys are preserved exactly as received from the source.

**Naming**: `Store_{Schema}_{Entity}_Key`
Example: `Store_WWI_Sales_Customers_Key`

**Typical columns**:
- `{Entity}Key` — surrogate key (system-generated integer)
- Business key column(s)
- `ValidFrom` — load timestamp of first appearance
- `SourceSystem` — identifier of the originating source

## Hist Table

**Purpose**: Stores all descriptive attributes with full history. Insert-only.

**Rules**:
- Insert only — records are never updated or deleted.
- A new record is inserted whenever any tracked attribute changes.
- The previous record's `ValidTo` is set to the new record's `ValidFrom`.
- The current record always has `ValidTo = NULL`.
- References the Key table via surrogate key — never via business key.
- Use Hist2, Hist3 etc. to split out fast-changing attribute groups that would
  excessively inflate the main Hist table.

**Naming**: `Store_{Schema}_{Entity}_Hist1`
Example: `Store_WWI_Sales_Customers_Hist1`

**Typical columns**:
- `{Entity}Key` — FK to Key table
- All descriptive attributes
- `ValidFrom` — timestamp when this version became current
- `ValidTo` — timestamp when superseded (NULL = currently active)
- `SourceSystem`

## Access View (LoadAccess)

**Purpose**: Exposes only the current state of an entity for downstream consumers.
Separates structure definition from transformation and business logic.

**Rules**:
- Outer SELECT defines the target structure and data types.
- Inner SELECT contains transformation logic and business rules.
- Returns only the current record per entity (`ValidTo IS NULL`).
- Business logic lives here — not in tables.

**Naming**: `Access_{Schema}_{Entity}_Actual`
Example: `Access_WWI_Sales_Customers_Actual`

## Referential Integrity

- All joins between Silver objects use surrogate keys — never business keys.
- If a referenced entity has not yet arrived (early-arriving fact):
  - Option A: point to a default dummy surrogate key record.
  - Option B: create a placeholder (inferred member) marked for later enrichment.
- Broken references surface as measurable data quality issues — this is intentional.

## Anti-Patterns
- **Business keys as join keys**: Breaks when business keys change; use surrogate keys.
- **Updates or deletes in Hist tables**: Destroys the auditability and historization guarantee.
- **Business logic in tables**: Belongs in Access views, not embedded in Hist columns.
- **Merging Key and Hist into one table**: Loses the clean insert-only identity guarantee.
- **One Hist table for multiple entities**: Produces unmaintainable wide tables.
- **Reading Silver Key/Hist tables from Gold**: Gold must use Silver Access views only.

## Naming Conventions

| Object | Pattern | Example |
|---|---|---|
| Key table | `Store_{Schema}_{Entity}_Key` | `Store_WWI_Sales_Customers_Key` |
| Hist table | `Store_{Schema}_{Entity}_Hist1` | `Store_WWI_Sales_Customers_Hist1` |
| Access view | `Access_{Schema}_{Entity}_Actual` | `Access_WWI_Sales_Customers_Actual` |
