The Person They Were Then

An easy Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
easy
Seniority
mid
Asked in
Snap

Problem

We run a messaging app where a user's subscription tier and country both drift over time, and the ad-revenue team needs every past impression to reflect the tier and country the user actually had the moment it fired. Design the schema that lets analysts slice historical ad revenue by the profile state as of each event.

Worked solution and explanation

Why this problem exists in real interviews

Beneath the profile-history costume this is a Type 2 slowly changing dimension with point-in-time attribution. The skill being probed: can you bind each ad impression to the version of the user that existed when it fired, not the version that exists today? Anyone can add a valid_from column. The trap is the join key. FK the fact to the natural user_id and every historical impression silently re-attributes to the user's current profile, so someone who upgraded to paid last week makes all of last year's free-tier impressions look like paid revenue.

Break down the requirements

Step 1: Separate attributes by change semantics

Ask what each column means over time. A user's tier and country define who they were when an event happened, so they need history (Type 2). A cosmetic label like a display name is just the current tag on the account; nobody reports revenue by a name that has since changed, so it overwrites in place (Type 1). This is exactly the kind of scoping question a good candidate raises before drawing tables, because versioning everything bloats the dimension and hands you no analytical value.

Step 2: Give the dimension a surrogate key and a validity span

user_id is the natural key and repeats across versions, so it cannot be the primary key. Mint a surrogate user_sk per version and stamp valid_from/valid_to (plus an is_current flag for convenience). Exactly one open version exists per user at any instant, and spans are contiguous and non-overlapping so any timestamp resolves to one row.

Step 3: Attribute the fact to the version, not the person

At load time, resolve the impression's user_id and event timestamp to the user_sk whose validity span contains that timestamp, and store that user_sk on fact_ad_impression. Now the fact points at a frozen snapshot of tier and country. History cannot rewrite itself when the user changes tomorrow, because tomorrow gets a brand new user_sk.

Step 4: Keep the fact additive and the dimensions conformed

Revenue is additive at the impression grain. Conform dim_ad (advertiser, campaign, format) and dim_date so analysts can slice historical revenue by tier, country, campaign, and calendar without touching the natural key again.

The reference model

dim_user is a Type 2 dimension keyed by user_sk; fact_ad_impression carries that surrogate so each impression is welded to the profile state in effect when it fired. A cosmetic label such as display_name can live on the dimension but is overwritten across versions, so it is not a basis for historical slicing.

`dim_user` is Type 2 (surrogate `user_sk` plus validity span); `fact_ad_impression` FKs to `user_sk` so revenue stays pinned to the profile state at event time.
  • dim_user: user_sk BIGINT PK, user_id BIGINT, subscription_tier TEXT, country TEXT, display_name TEXT, valid_from TIMESTAMP, valid_to TIMESTAMP, is_current BOOLEAN
  • dim_ad: ad_sk INT PK, advertiser TEXT, campaign TEXT, format TEXT
  • dim_date: date_key INT PK, full_date DATE, day_of_week INT, month INT
  • fact_ad_impression: impression_id BIGINT PK, user_sk BIGINT FK, ad_sk INT FK, date_key INT FK, event_at TIMESTAMP, revenue_usd DECIMAL
  • dim_user references fact_ad_impression
  • dim_ad references fact_ad_impression
  • dim_date references fact_ad_impression

The analysis pattern

Historical revenue by tier and country
SELECT
    u.subscription_tier,
    u.country,
    d.month,
    SUM(f.revenue_usd) AS revenue
FROM fact_ad_impression f
JOIN dim_user u ON u.user_sk = f.user_sk
JOIN dim_date d ON d.date_key = f.date_key
WHERE d.full_date >= DATE '2025-01-01'
GROUP BY u.subscription_tier, u.country, d.month
ORDER BY revenue DESC

The join is on user_sk, so tier and country reflect the version in effect at each impression, no timestamp range predicate needed.

Trade-offs and alternatives

Surrogate key on the fact (Type 2)
Impression stores user_sk resolved at load time. * History is frozen and correct forever * Reads are a plain equi-join, no range scan * Load must resolve the version at ingest
Natural `user_id` on the fact
Impression stores user_id, joins to current dim row. * Simple to load * Every tier change silently rewrites past revenue * Point-in-time reporting is impossible without a separate history table

Common follow-up questions