A hotel-booking marketplace in the mould of Booking.com tracks hotel attributes (star rating, location, chain, commission tier) for 3 million properties. Analysts want to report bookings by the attributes in force at booking time. The team can store daily snapshots, an SCD Type 2 dimension, or an event log of attribute changes. What does each choice buy and cost?

What is gained, and what is paid

Daily snapshots. One full copy of the dimension per day. Trivial to understand: join on (property_id, date). Cost: 3 million rows x 365 days is about 1.1 billion rows a year of mostly unchanged data. At 200 bytes a row that is roughly 220 GB a year before compression, and columnar compression of repeated values brings it down heavily, so storage is rarely the real cost; the cost is every query scanning a daily copy.

SCD Type 2. A row per version with valid_from and valid_to, so a property that changed twice has three rows. If 2% of properties change in a typical month, the table grows by tens of thousands of rows a month. Reads are cheap and storage is minimal. Cost: the join is a range join (booking_date >= valid_from AND booking_date < valid_to), which engines handle worse than equality joins, and a half-open versus closed-interval mistake duplicates or drops rows.

Event log. One row per change with the new value. Smallest and most faithful (you can answer what changed and why). Cost: reconstructing state at a date needs a window function over the log, so nobody can use it directly, and you end up building an SCD2 table from it anyway.

When the cost becomes visible

Snapshots hurt when the dimension is wide and queries span years. SCD2 hurts when analysts write the range join by hand: the first duplicated-revenue incident shows it. The event log hurts the moment a non-engineer asks "what was the star rating on 3 March?".

What I would choose

SCD2 for the serving model, built from an event log as the source of truth. Analysts get cheap, correct as-of joins, and the log keeps the ability to rebuild or correct history. Add a surrogate key per version, and have the fact table store the surrogate key at load time, so as-of joins are equality joins on the key and the range logic is paid once, in the pipeline.

How to keep the option to reverse

Keep the event log or daily raw extracts. From either you can regenerate SCD2 or snapshots. The inverse is lossy: snapshots cannot recover changes between days.

When the simpler choice wins

If only the current attribute matters (Type 1) for most reports, store that and add history for the two or three attributes that actually need it, such as commission tier. Tracking history on every column multiplies versions for no consumer.

Common weak answers

• "Snapshots are wasteful." They are wasteful in storage but dominant in simplicity for small dimensions.
• "Use Type 2 everywhere." Every tracked column creates versions that fan out joins.