Practice — Data Modeling: Dimensional & Normalized (5 questions)

You're the first data engineer at a B2B SaaS company. The production (OLTP) database has normalized tables roughly like:

accounts(account_id, name, industry, signup_date, plan_id)
plans(plan_id, plan_name, monthly_price, tier)
subscriptions(subscription_id, account_id, plan_id, start_date, end_date, status)
invoices(invoice_id, subscription_id, invoice_date, amount, paid_date)

Finance and the CS team both want a self-serve BI dashboard answering questions like: monthly recurring revenue by industry and plan tier, how many accounts churned each month, and average revenue per account by signup cohort.

1. Design a dimensional (star schema) model for this: declare the grain of the primary fact table explicitly, and write the DDL for the fact table and its dimension tables.
2. plan_id on an account can change over time (upgrades/downgrades). Which SCD type would you use for plan on the account/subscription dimension, and why? Show a worked before/after example.
3. Would you also build a periodic snapshot fact table here? If so, what would its grain be and what question does it answer that the transaction-grain fact table can't answer well?