The Slow Yes

A hard 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
hard
Seniority
mid
Asked in
Google

Problem

A digital advertising platform needs an analytics warehouse for campaign reporting, where analysts slice impressions, clicks, and conversions by campaign, ad, device, geography, and day. Conversions can land days after the impression that drove them and must trace back to that exact impression so credit is never reassigned or double-counted, while every dashboard query scans wide date ranges on a columnar engine billed by bytes read. Design the schema.

Worked solution and explanation

The trap

This is grain separation with a late-arriving twist, dressed up as campaign reporting. Impressions, clicks and conversions all feel like 'ad events', so most candidates draw one wide ad_events table with nullable click and conversion columns. Anyone can list the five dimensions. What separates candidates is three facts at three grains, with impression_id carried down to fact_clicks and fact_conversions. Miss that and a conversion landing twelve days late has two bad homes. It can rewrite a closed impression partition, or it can be credited by fuzzy match to whichever impression looks close. Either way your conversion rate counts some yeses twice and some never.

Building it

Step 1: Declare three grains before drawing a table

One row per impression served, one per click, one per conversion. cost_micros sums at impression grain and conversion_value sums at conversion grain. Put both on one row and every sum needs a filter to undo the conflation, and an impression with two conversions fans out.

Step 2: Carry impression_id down to both downstream facts

It is the spine. A click and a conversion each hold the FK to the exact impression that earned them, so attribution is a key join, never a match on campaign plus time. Every other attribute (campaign, ad, device, geo) is reachable through that one hop.

Step 3: Give every fact its own date_key

fact_conversions.date_key is the day the conversion landed, not the day of the impression. Partition on it and late yeses append into fresh partitions; October's impression partition stays immutable after the month closes.

Step 4: Denormalize one key, not the dimensions

campaign_key sits on fact_impressions even though dim_ad already implies it. That single redundant key lets the most common dashboard group by campaign without touching dim_ad. The dimensions stay narrow and conformed; flattening their text columns onto billions of fact rows is what the bytes-read bill punishes.

Three facts at three grains sharing `dim_date`; clicks and conversions link home through `impression_id`.
  • dim_date: date_key INT PK, full_date DATE, week INT, month INT
  • dim_campaign: campaign_key INT PK, campaign_id BIGINT, advertiser TEXT, objective TEXT
  • dim_ad: ad_key INT PK, ad_id BIGINT, campaign_key INT FK, format TEXT
  • dim_device: device_key INT PK, device_type TEXT, os TEXT
  • dim_geo: geo_key INT PK, country TEXT, region TEXT
  • fact_impressions: impression_id BIGINT PK, date_key INT FK, campaign_key INT FK, ad_key INT FK, device_key INT FK, geo_key INT FK, served_at TIMESTAMP, cost_micros BIGINT
  • fact_clicks: click_id BIGINT PK, impression_id BIGINT FK, date_key INT FK, clicked_at TIMESTAMP
  • fact_conversions: conversion_id BIGINT PK, impression_id BIGINT FK, date_key INT FK, converted_at TIMESTAMP, conversion_value DECIMAL
  • dim_ad.campaign_key references dim_campaign.campaign_key
  • fact_impressions.date_key references dim_date.date_key
  • fact_impressions.campaign_key references dim_campaign.campaign_key
  • fact_impressions.ad_key references dim_ad.ad_key
  • fact_impressions.device_key references dim_device.device_key
  • fact_impressions.geo_key references dim_geo.geo_key
  • fact_clicks.impression_id references fact_impressions.impression_id
  • fact_clicks.date_key references dim_date.date_key
  • fact_conversions.impression_id references fact_impressions.impression_id
  • fact_conversions.date_key references dim_date.date_key

Reading it back

Campaign conversion rate over a window
SELECT
    c.advertiser,
    SUM(i.imps)        AS impressions,
    SUM(cv.conversions) AS conversions,
    SUM(cv.conversions) * 1.0 / NULLIF(SUM(i.imps), 0) AS cvr
FROM (
    SELECT campaign_key, COUNT(*) AS imps
    FROM fact_impressions
    WHERE date_key BETWEEN 20251001 AND 20251031
    GROUP BY campaign_key
) i
LEFT JOIN (
    SELECT f.campaign_key, COUNT(*) AS conversions
    FROM fact_conversions cv
    JOIN fact_impressions f ON f.impression_id = cv.impression_id
    WHERE cv.date_key BETWEEN 20251001 AND 20251130
    GROUP BY f.campaign_key
) cv ON cv.campaign_key = i.campaign_key
JOIN dim_campaign c ON c.campaign_key = i.campaign_key
ORDER BY cvr DESC

Conversions scan a wider date_key window than impressions, then attribute back through impression_id.

Three facts, conformed dimensions
Each measure additive at its own grain. Late conversions append to their own date_key partition. Cost: one join on impression_id to attribute.
One wide event table
Zero joins for the simplest chart. But a late conversion updates a closed impression row, a second conversion fans the row out, and most clicked_at and conversion_value cells are null bytes you still pay to scan.

Common follow-up questions