— Goal & Context

• Use case — e.g., Get customer acquisition count by channel
• Impact — Link to CPA = Cost / Acquired Customers
• End user — BI dashboards + automation pipelines
• KPI definition — Write formula, restate goal

— Assumptions & Clarifications

• Granularity — per order line × product × day
• PII handling — RBAC, Dynamic Data Masking (Snowflake), Unity Catalog ACLs
• Historical data — retention period, volume estimates for partitioning
• Conceptual model — 4–5 min explanation of product flow
• Schema — Star vs snowflake, denormalization trade-offs
• Entities list — Customer, Product, Campaign, Platform, Date, Order Line

— Dimension Modeling

• Start with date dimension
• Other dimensions — Customer (SCD2), Product, Campaign, Platform
• Fact table — transactions + measures (order qty, revenue), choose partition key to avoid skew
• Relationship mapping — 1:1, 1:n, m:n
• SCD handling — SCD2 in dimensions

— Fact Tables