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.