data-model

# Data Model Analytics models succeed when **grain** is explicit, **keys** are stable, and **slowly changing dimensions** are chosen deliberately—not “star schema by default.” ## When to Offer This Workflow **Trigger conditions:** - Designing a warehouse, lakehouse, or BI layer - Confusion on **one row per what**; duplicate counts in reports - Refactoring dimensional models for performance or clarity **Initial offer:** Use **six stages**: (1) business questions & grain, (2) conformed dimensions, (3) facts & measures, (4) dimensions & SCD types, (5) keys & integrity, (6) performance & evolution). Confirm **tooling** (dbt, dimensional DW, BigQuery, etc.). --- ## Stage 1: Business Questions & Grain **Goal:** **Grain** = the atomic row: e.g., “one line item per order per day” not “sort of per order.” ### Practices - List **questions** the model must answer; **derive** grain from **smallest** needed detail **Exit condition:** One sentence grain per fact table. --- ## Stage 2: Conformed Dimensions **Goal:** **Same** customer/product definitions across facts—**shared** dimension tables or **SCD** policy aligned. --- ## Stage 3: Facts & Measures **Goal:** **Additive** vs **semi-additive** vs **non-additive** measures documented (balances, distinct counts). ### Practices - **Degenerate** dimensions vs junk dimensions—**avoid** **wide** **fact** **sprawl** **without** **reason** --- ## Stage 4: Dimensions & SCD Types **Goal:** **SCD1** overwrite vs **SCD2** history with `valid_from`/`valid_to` vs **SCD3** limited history—**match** **compliance** **and** **reporting** **needs**. --- ## Stage 5: Keys & Integrity **Goal:** **Surrogate** keys in facts; **natural** keys preserved as attributes; **referential** integrity strategy in the warehouse layer. --- ## Stage 6: Performance & Evolution **Goal:** **Partition** and **cluster** keys for large facts; **late-arriving** facts policy; **version** **dims** when schema evolves. --- ## Final Review Checklist - [ ] Grain explicit per fact table - [ ] Conformed dimensions planned - [ ] Measure additivity documented - [ ] SCD strategy per critical dimension - [ ] Keys and late-arriving data handled ## Tips for Effective Guidance - **Fan traps** and **chasm traps** in BI—flag when joining across facts incorrectly. - **Snapshot** fact tables for **point-in-time** balances vs **transaction** facts. ## Handling Deviations - **Event**-only pipelines: still model **curated** **dimensions** **for** **analysis**, not only raw JSON.