Design a Dimensional Model for Transactional Analytics (Concrete Example Included)

You are building a star-schema in a cloud data warehouse for near real-time transactional analytics. Assume a high-volume ads platform with three key event types: impressions (delivery), clicks, and conversions (downstream actions). Stakeholders need accurate, drillable reporting by user, device, campaign, and placement.

Tasks

1. Core Fact Table
   • Define the core fact table for delivery events.
   • Specify the grain, surrogate/natural keys, degenerate keys, and measures.
   • Outline 4–5 key dimension tables (names, purpose, and example attributes).
2. Slowly Changing Dimensions (SCD Type 2)
   • Describe how you would track attribute changes over time (e.g., a user’s country, a campaign’s status) using SCD Type 2.
3. Relationships and Cardinalities
   • Pick two example tables and describe their relationship (one-to-many vs. many-to-many).
   • Explain how these cardinalities impact schema design and query behavior.
4. Handling Many-to-Many
   • Show how you would handle a many-to-many relationship (e.g., impression-to-conversion attribution) using a bridge table or an additional fact table.
   • Discuss trade-offs in performance, storage, and correctness.
5. Schema Evolution
   • Propose evolutions you would consider for new requirements (e.g., add a column, add a dimension, normalize a table, add a fact).
   • Justify scalability impacts and how you would preserve a single authoritative fact table when possible.