Data Modeling Studio

Why this is hard — the design tension

The problem isn't a single trip — it's the group intent. Five friends going to the airport expect to be charged a single price, but Uber dispatches two vehicles. Operations sees two trips; Finance must charge the group once but pay two drivers fairly; Risk must verify the convoy actually moved together. The robust solution is a hierarchical Star Schema that decouples the physical movement of the car from the financial obligation of the rider.

Section 1 — Dimensional Model Overview

This model uses a Journey → Trip → Event hierarchy to handle everything from a single ride to complex shared convoys.

Fact Tables (the hierarchy)

• fct_journeys — The "Parent" grain. One row per group request. Captures group intent (split type, total group cost, status).
• fct_trips — The "Vehicle" grain. One row per driver / vehicle per journey. Source of truth for Finance (driver payouts).
• fct_trip_events — The "Telemetry" grain. One row per state transition (REQUEST, MATCH, PICKUP, etc.) per trip. Powers Ops (Live ETA) and Risk (GPS replay).
• fct_trip_feedback — The "Sentiment" grain. One row per (trip × rider) feedback event. Captures rating, category, free-text comment, sentiment. Powers Product (CSAT, NPS).
• brg_journey_riders — The "Financial" bridge. Links riders to journeys and to a specific assigned trip/vehicle — handles cost-splitting (journey-level) and capacity routing (trip-level).

Dimension Tables

• dim_users — Riders and drivers, typed via user_role. SCD2 on plan tier and rating.
• dim_vehicles — Vehicle specs (model, type), insurance status, owner. SCD2 on insurance status (Risk needs as-of-trip status).
• dim_geography — split by volatility: the H3 grid itself (dim_h3_cell, level 8 ≈ 0.7 km²) is SCD1 because the geometry is static, but the operational overlays riding on it — tax zones, airport/surge rings, road status — are SCD2 (dim_surge_zone and road-state rows), because those boundaries get re-drawn. See §11.3 for SCD2 geography in action.
• dim_payment_methods — Card / wallet on file. SCD2 — Finance audit needs the as-of-trip card.
• dim_fx_rate — SCD2 daily rates; locked at request_timestamp.

Section 10 — Working with a real Uber dataset (148K bookings · Delhi NCR · 2024)

To make the practical SQL drillable on real data, the model maps cleanly to a public Kaggle-derived dataset: github.com/hannahsutton1/uber.data — 148,770 booking rows from Delhi NCR for calendar year 2024. Repo includes raw + cleaned CSVs plus the cleaning Jupyter notebooks.

Source schema (single wide table, 21 columns)

Date, Time, booking_ID, Booking_Status, Customer_ID, Vehicle_Type,
Pickup_Location, Drop_Location, Avg_VTAT, Avg_CTAT,
Cancelled_Rides_by_Customer, Reason_for_cancelling_by_Customer,
Cancelled_Rides_by_Driver, Driver_Cancellation_Reason,
Incomplete_Rides, Incomplete_Rides_Reason,
Booking_Value, Ride_Distance, Driver_Ratings, Customer_Rating, Payment_Method

Glossary: VTAT = vehicle-to-pickup minutes (driver arrival time). CTAT = customer-to-completion minutes (in-trip duration). Booking_Value = fare in INR. Ride_Distance in km. Ratings on a 1–5 scale.

Source → Target mapping (ETL plan)

Enrichment: inferring the journey hierarchy from flat bookings

The dataset is one row per single-rider booking — there is no journey_id, so the convoy hierarchy can't be exercised until you derive one. A window function groups bookings that share an origin, destination, and request-minute into an inferred journey:

-- Inferred journey derivation (the enrichment layer)
WITH inferred AS (
  SELECT
    booking_ID  AS trip_id,
    Customer_ID AS rider_id,
    DENSE_RANK() OVER (
      ORDER BY Pickup_Location, Drop_Location,
               DATE_TRUNC('minute', CAST(Date || ' ' || Time AS TIMESTAMP))
    ) AS inferred_journey_id
  FROM raw_kaggle_uber_data
)
SELECT
  inferred_journey_id      AS journey_id,
  COUNT(*)                 AS convoy_vehicle_count,
  COUNT(DISTINCT rider_id) AS distinct_riders
FROM inferred
GROUP BY inferred_journey_id
HAVING COUNT(*) > 1;        -- multi-vehicle groups = candidate convoys

Caveat — this is a heuristic, not ground truth. Grouping on (origin, destination, minute) fuses independent strangers sharing a popular corridor at rush hour into a phantom "convoy." A real enrichment needs a genuine correlation signal — a shared booking/party token, a tighter spatial radius (H3 L10) with a few-second window, or the same payment instrument — before two bookings can be claimed as one journey. Use it to teach the window-function pattern, and name its false-positive rate out loud in the interview: spotting the limit is itself the senior signal.

Real sample rows (5 representative bookings from the dataset)

-- Loaded into fct_trips after ETL transformation:
INSERT INTO fct_trips (trip_id, customer_id, vehicle_type, pickup_loc, drop_loc,
                       request_ts, terminal_state, vtat_min, ctat_min,
                       booking_value_inr, ride_distance_km, payment_method)
VALUES
  ('CNR5884300','CID1982111','eBike',         'Palam Vihar',  'Jhilmil',
    '2024-03-23 12:29:38','no_driver_found',     NULL,  NULL,    NULL,   NULL,  NULL),
  ('CNR8494506','CID9202816','Auto',          'Khandsa',      'Malviya Nagar',
    '2024-08-23 08:56:10','completed',           13.4,  25.8,    627.00, 13.58, 'Debit Card'),
  ('CNR8906825','CID2610914','Premier Sedan', 'Central Secretariat','Inderlok',
    '2024-10-21 17:17:25','completed',           13.1,  28.5,    416.00, 34.02, 'UPI'),
  ('CNR1950162','CID9933542','Bike',          'Ghitorni Village','Khan Market',
    '2024-09-16 22:08:00','completed',            5.3,  19.6,    737.00, 48.21, 'UPI'),
  ('CNR1326809','CID4604802','Go Sedan',      'Shastri Nagar','Gurgaon Sector 56',
    '2024-11-29 18:01:39','incomplete',           4.9,  14.0,    237.00,  5.73, 'UPI');

Q17 — Vehicle type mix & cancellation rate (real-data analysis)

SELECT
  vehicle_type,
  COUNT(*)                                                                      AS bookings,
  COUNT(*) FILTER (WHERE terminal_state = 'completed')                          AS completed,
  COUNT(*) FILTER (WHERE terminal_state LIKE 'cancelled%')                      AS cancelled,
  COUNT(*) FILTER (WHERE terminal_state = 'no_driver_found')                    AS no_driver,
  ROUND(100.0 * COUNT(*) FILTER (WHERE terminal_state = 'completed')
        / COUNT(*), 1)                                                          AS completion_pct,
  ROUND(AVG(booking_value_inr) FILTER (WHERE terminal_state = 'completed'), 0)  AS avg_fare_inr,
  ROUND(AVG(ride_distance_km)  FILTER (WHERE terminal_state = 'completed'), 1)  AS avg_distance_km
FROM fct_trips
WHERE DATE_TRUNC('year', request_ts) = '2024-01-01'
GROUP BY vehicle_type
ORDER BY bookings DESC;

Typical result on the 148K dataset:

Insight: completion rate is ~62% across all vehicle types — the bottleneck isn't the vehicle, it's cancellation behavior (next query).

Q18 — Top 10 routes by booking volume (Pickup → Drop)

SELECT
  pickup_loc,
  drop_loc,
  COUNT(*)                                                                       AS trips,
  ROUND(AVG(booking_value_inr) FILTER (WHERE terminal_state = 'completed'), 0)   AS avg_fare,
  ROUND(AVG(ride_distance_km)  FILTER (WHERE terminal_state = 'completed'), 1)   AS avg_dist,
  ROUND(100.0 * COUNT(*) FILTER (WHERE terminal_state = 'completed')
        / COUNT(*), 1)                                                           AS completion_pct
FROM fct_trips
GROUP BY pickup_loc, drop_loc
ORDER BY trips DESC
LIMIT 10;

Q19 — Cancellation reason breakdown (driver vs customer)

WITH driver_cancels AS (
  SELECT 'driver' AS cancelled_by, driver_cancellation_reason AS reason, COUNT(*) AS n
  FROM fct_trips
  WHERE terminal_state = 'cancelled_driver' AND driver_cancellation_reason IS NOT NULL
  GROUP BY driver_cancellation_reason
),
customer_cancels AS (
  SELECT 'customer' AS cancelled_by, reason_for_cancelling_by_customer AS reason, COUNT(*) AS n
  FROM fct_trips
  WHERE terminal_state = 'cancelled_rider' AND reason_for_cancelling_by_customer IS NOT NULL
  GROUP BY reason_for_cancelling_by_customer
)
SELECT cancelled_by, reason, n,
  ROUND(100.0 * n / SUM(n) OVER (PARTITION BY cancelled_by), 1) AS pct_within_actor
FROM (SELECT * FROM driver_cancels UNION ALL SELECT * FROM customer_cancels) u
ORDER BY cancelled_by, n DESC;

Use case: if "Vehicle Breakdown" tops the driver list, fleet maintenance owns the fix; if "Driver took too long" tops the customer list, dispatch tuning owns it.

Q20 — Hourly demand pattern + completion rate (peak-hour stress)

SELECT
  EXTRACT(HOUR FROM request_ts) AS hour_of_day,
  COUNT(*) AS bookings,
  COUNT(*) FILTER (WHERE terminal_state = 'no_driver_found')          AS no_driver_found,
  ROUND(100.0 * COUNT(*) FILTER (WHERE terminal_state = 'no_driver_found')
        / COUNT(*), 2)                                                AS no_driver_pct,
  ROUND(100.0 * COUNT(*) FILTER (WHERE terminal_state = 'completed')
        / COUNT(*), 1)                                                AS completion_pct,
  ROUND(AVG(vtat_min) FILTER (WHERE terminal_state = 'completed'), 1) AS avg_vtat_min
FROM fct_trips
GROUP BY EXTRACT(HOUR FROM request_ts)
ORDER BY hour_of_day;
-- The hours where no_driver_pct spikes are the operational gaps where supply incentives matter most.

Q21 — Payment method mix by vehicle tier (premium vs economy)

SELECT
  CASE WHEN vehicle_type IN ('Premier Sedan','UberXL') THEN 'premium'
       WHEN vehicle_type IN ('Go Sedan','Go Mini')      THEN 'mid'
       ELSE                                                  'economy'
  END                                                  AS tier,
  payment_method,
  COUNT(*)                                             AS trips,
  ROUND(100.0 * COUNT(*) /
        SUM(COUNT(*)) OVER (PARTITION BY
          CASE WHEN vehicle_type IN ('Premier Sedan','UberXL') THEN 'premium'
               WHEN vehicle_type IN ('Go Sedan','Go Mini')      THEN 'mid'
               ELSE                                                  'economy' END), 1) AS pct_within_tier
FROM fct_trips
WHERE terminal_state = 'completed' AND payment_method IS NOT NULL
GROUP BY tier, payment_method
ORDER BY tier, trips DESC;
-- UPI dominates economy; cards skew premium. Drives partnership and incentive design.

What this dataset does not support (caveats — for honest interviews)

• No driver_id, no vehicle_id. Only Vehicle_Type as a categorical. Driver-side analytics (utilization, payouts) need a synthetic driver_id assignment or a different dataset.
• No surge multiplier, no fare breakdown. Only the final Booking_Value. Surge analysis (Scenario 2) needs synthetic data.
• No lat/lon, no H3 cells. Only Delhi NCR neighborhood names. Geocode at ETL using h3-py or Mapbox.
• No journey or multi-leg concept. One rider per booking — convoy / Pool / shared rides are not in scope. Use synthetic data for those examples.
• No GPS trace. fct_trip_events can be reverse-engineered from VTAT/CTAT into approximate REQUESTED, MATCHED, PICKUP, DROPOFF timestamps but the geographic events (location_h3 per state) are unavailable.

Section 11 — Mass-departure scale: 20K+ people, road blocks & growth accounting

An interview prompt sometimes raises the stakes: "Architect the system to handle a 20,000-person stadium event — accidents, road blocks, supply/demand volatility, all at once." The existing Journey → Trip → Event hierarchy holds, but three additional concerns must be modeled explicitly: infrastructure as a fact (SCD2 geography), growth accounting on both sides of the marketplace, and point-in-time financial integrity under chaos.

Section 12 — Entity ownership & boundaries (who owns what, what crosses the line)

The model so far names tables. A senior answer names archetypes and owners — because the boundary between two tables is a contract, not a foreign key. Four archetypes, four write disciplines, four systems of record.

The ownership rule that signals seniority:

fct_trips is a projection, not a source. The trip's life is the fct_trip_events log; fct_trips is that log folded into its current state. If the two ever disagree, the log wins and fct_trips is rebuilt.

That is event-sourcing-with-a-snapshot, and it earns the level. The interviewer's follow-up — "so can you rebuild fct_trips from fct_trip_events?" — has one correct answer: yes, and that is the whole point (Section 15).

Section 13 — Consistency contracts: one model, four consistency regimes

A model that does not state its consistency contracts looks architecturally correct but operationally fuzzy. The Uber model spans four subsystems, and each runs a different consistency regime. Name them explicitly, per subsystem.

The framing that lifts the answer a level: consistency is per-field, not per-system. On a single fct_trips row —

• surge_multiplier, fx_rate_to_usd — strong; locked at request, immutable forever.
• driver_id — strong at assignment (the optimistic lock), then immutable.
• the live ETA the rider sees (derived, not stored on the fact) — eventual; a stale value is a harmless UX hint.
• is_flagged_risk — eventual and slowly-changing; appended, never blocks anything.

Section 14 — State-machine rigor: legal transitions, idempotency, the races

Section 1 lists the states. Seniority is in the edges — which transitions are legal, what guards the illegal ones, and which concurrency races the model must already survive. Interviewers love breaking a state machine; bring the guards before they ask.

The legal transition table (event states; terminal states mirror fct_trips.terminal_state):

Anything outside this table is an invalid transition and must be rejected, not silently written: STARTED → MATCHED (time travel), DROPOFF → STARTED (a terminal state is absorbing), a second MATCHED on a trip already matched. The guard is a state-rank check plus event_timestamp ordering within trip_id — an event that would lower the rank is routed to a dead-letter queue, not appended.

Idempotency. fct_trip_events.event_id is the producer's idempotency key. At-least-once delivery (Kafka, retried RPCs) will redeliver; the append must be a no-op on a seen event_id.

-- Event-log append. At-least-once delivery WILL redeliver; event_id makes it safe.
INSERT INTO fct_trip_events (event_id, trip_id, state, event_timestamp, location_h3, actor)
SELECT :event_id, :trip_id, :state, :event_ts, :h3, :actor
 WHERE NOT EXISTS (SELECT 1 FROM fct_trip_events WHERE event_id = :event_id);
-- Equivalent: UNIQUE(event_id) + INSERT ... ON CONFLICT DO NOTHING.

The races a senior volunteers — before the interviewer reaches for them:

-- OLTP write path. Driver assignment: the optimistic lock that
-- resolves the duplicate-offer race. Exactly one racing driver wins.
UPDATE trips
   SET driver_id = :candidate_driver_id,
       state     = 'MATCHED'
 WHERE trip_id   = :trip_id
   AND state     = 'REQUESTED'     -- guard: trip still offerable
   AND driver_id IS NULL;          -- guard: only the first writer passes
-- rows_updated = 0  → another driver already won; release the offer, re-queue.
-- rows_updated = 1  → this driver is assigned; emit the MATCHED event.

Section 15 — Failure scenarios & the reconciliation engine

The clean star schema is the steady-state view. Production runs a reconciliation engine underneath it — and a senior candidate volunteers the ugliness rather than waiting to be cornered by it.

The reconciliation engine is three standing jobs, not a feature bolted on later:

1. Invariant checks — the two SUM reconciliations from Section 4, run continuously. A break pages the producing team.
2. Stuck-state sweeper — trips that miss a terminal state inside their SLA window are force-resolved on a known rule.
3. Ledger reconciliation — SUM(fct_trips.total_fare_usd) vs the payments ledger, per day. A drift is a real bug, never "rounding."

Section 16 — Partitioning & shard-key strategy

The shard key is the one decision you cannot migrate cheaply later — and the OLTP write path and the warehouse read path want different keys. Conflating them is a classic miss.

Hot-partition risk — where production actually fails:

Why trip_id and not city or driver_id: a shard key must be immutable and high-cardinality with uniform load. trip_id is both. City is low-cardinality and skewed (and a trip's city can even change cross-border); driver_id is skewed by super-drivers. Rebalancing: consistent hashing on trip_id moves only ~1/N of keys when a shard is added — and because trips are short-lived (hours, not years), a rebalance only drains in-flight trips, it never migrates history.

Section 2 — The Logical Data Model

Parent Fact: fct_journeys (the group intent)

Vehicle Fact: fct_trips (one row per driver/vehicle per journey)

Event Fact: fct_trip_events

Sentiment Fact: fct_trip_feedback (per-rider satisfaction)

Financial Bridge: brg_journey_riders

Append-only systems of record. Two things on fct_trips — money and the fraud verdict — keep changing after the trip reaches its terminal state. Mutating them in place would break the one-row-per-trip grain, so each gets its own append-only ledger; the columns on fct_trips are read-only projections of the latest state.

Ledger Fact: fct_trip_financial_entry (one row per double-entry posting — append-only)

Conservation invariant. Because every row moves value between two accounts, platform integrity reduces to one identity per account: SUM(amount_local) WHERE destination_account = 'X' must equal SUM(amount_local) WHERE source_account = 'X'. A pipeline bug that creates or loses money breaks the equality on the next run — the ledger audits itself, exactly like the two reconciliation invariants on brg_journey_riders.

Risk Fact: fct_trip_risk_decision (one row per evaluation version — append-only)

Mid-journey mutation — the "convoy jettison." Vehicle B breaks down ten minutes in; its two riders pile into Vehicle A or fork off into a fresh standalone trip. Nothing is mutated in place: the bridge appends new assignment_version rows re-seating those riders (the old rows stay, so "who was in which car at 12:09" is still answerable), and the billing divergence from the original group intent posts as a promo_adjustment / refund pair in fct_trip_financial_entry. The grain holds, the audit holds, and the reconciliation invariants still sum.

Promised vs driven — the mid-ride route recalculation. Rider A and Rider B share a pooled journey; mid-trip A cancels from inside the car and the driver re-routes to drop B. Two distances now diverge and the model keeps both: quoted_route_distance_km (frozen at accept — what the fares were quoted on) and trip_distance_km (actually driven, from telemetry). A's in-car cancel is a CANCELLED event after STARTED in fct_trip_events — the recalculation pivot — which stamps route_recalc_reason = rider_cancel_in_trip on the trip.

Who pays what. The driver is paid on trip_distance_km (they drove the detour) — a driver_payout posting. Rider A is charged only to the cancel point, bounded by effective_from→their cancel event, with the difference from quoted_leg_km posting as a refund / cancellation_fee pair. Rider B's pool discount assumed A's shared miles; once A leaves, B effectively rode partway solo, so a promo_adjustment closes the gap per policy. Every line is an append-only row in fct_trip_financial_entry, and the conservation invariant guarantees the re-route nets to zero across the rider, driver, and platform accounts.

Why the split matters for ML. Distance and ETA models train on features-at-request → realized-outcome: quoted_route_distance_km is the immutable prediction, trip_distance_km is the label. But here the two diverged for an exogenous reason — A cancelled — not because the model was wrong. Feed this trip to the base model unmarked and you teach it that a good prediction was an error. route_recalc_reason IS NOT NULL is the one-column filter that pulls these out of the training set (or routes them to a separate disruption model). Naming that confounder is the senior signal — the schema makes it a WHERE clause, not a forensic reconstruction.

Section 3 — How the model serves each stakeholder

Operations · live ETA & monitoring

By using fct_trip_events, Operations calculates lead-time metrics directly:

• Dispatch Latency = timestamp(MATCHED) − timestamp(REQUESTED)
• Pickup ETA accuracy = timestamp(STARTED) − timestamp(MATCHED) compared to the predicted ETA
• Spatial hot-spot detection via location_h3: aggregate REQUESTED events per cell per minute against MATCHED events to identify cells where demand exceeds supply in real time.

Finance · driver payouts & revenue

• fct_trip_financial_entry is the general ledger — every charge, fee, payout, refund, and adjustment is an append-only posting keyed to trip_id. fct_trips carries only the rolled-up total_fare_usd / driver_payout_usd projections for fast reads. Driver payouts settle in fct_payouts (one row per driver per payout run) and must reconcile back to the ledger.
• surge_multiplier is immutable once the request is made — ensures the rider's quoted price matches the driver's earning potential. Stored on the row, never derived.
• terminal_state dictates whether a cancellation fee triggers a partial driver payout (e.g. cancelled_rider after MATCHED triggers a fee; cancelled_system does not).
• FX is locked at request_timestamp — guarantees revenue restatement is impossible from rate drift.

Risk · fraud detection

• Location anomaly: compare origin_h3 on fct_trips with the first STARTED event's H3 in fct_trip_events — large divergence = potential ride-tampering.
• Trip-event GPS replay reveals "teleportation" — impossible speed between consecutive events implies spoofed location.
• Account farming: join fct_trips to dim_devices (one row per physical device — hardware fingerprint, first-seen, OS) through fct_session (one row per app session — device_id × rider_id × login window). A single device cycling through multiple rider_ids in a short window flags suspicious activity.
• Each re-scoring appends a new fct_trip_risk_decision version instead of overwriting the last, so Finance can replay any payout against the verdict that was current at the time — a later fraud re-evaluation can never retroactively rewrite a settled decision.
• Phantom convoy (collusion): two drivers and a fraudulent rider book a split-fare convoy on a stolen card; one car drives the route while the second sits at home spoofing its GPS to collect a "moving together" payout. Proximity isn't the tell — real convoys stay close too — jitter is: genuine telemetry carries GPS noise, a scripted loop doesn't. A streaming job scores each asset's positional entropy; near-zero jitter over a multi-minute window appends a confirmed_fraud verdict to fct_trip_risk_decision and freezes the driver payout before it settles.

Section 4 — Why the hierarchy: bridges + parent fact

Without the journey/trip split + bridge, you face three bad choices:

• Option A: One row per trip, rider IDs in an array column. Finance can't aggregate revenue per rider without exploding the array. Disqualifying.
• Option B: One row per rider in fct_trips. Pool with 3 riders becomes 3 rows — fare is triple-counted on every aggregation. Driver and dispatch counts also triple-count. Disqualifying.
• Option C: Single fact for everything (rider-grain + driver-grain merged). A 3-rider, 2-driver convoy = 6 rows. Now you can't tell drivers from riders without filtering by role, and convoy-cost reconciliation needs a self-join. Disqualifying.

The hierarchy with bridge is the only design that satisfies all three stakeholders:

• One row per group in fct_journeys — group-level KPIs (avg group size, split-type mix) are correct without filters.
• One row per driver/vehicle per journey in fct_trips — Operations and Finance per-driver counts are correct on simple aggregation. (driver_id, journey_id) uniqueness honored.
• Multiple rows per journey in brg_journey_riders — Finance computes per-rider charge with the journey's split logic; Risk can route a rider to a specific trip via the FK.
• Two enforceable invariants:
  • SUM(brg_journey_riders.individual_charge_usd) per journey_id = fct_journeys.total_group_fare_usd
  • SUM(fct_trips.trip_fare_share_usd) per journey_id = fct_journeys.total_group_fare_usd
  Two continuous data-quality checks. Either failing flags a producer bug.

Allocation rules:

• EQUAL_SPLIT: individual_charge = total_group_fare / num_riders
• DISTANCE_PRORATA: individual_charge = total_group_fare × (leg_distance_km / SUM(leg_distance_km))
• REQUESTER_PAYS: individual_charge = total_group_fare for requester, 0 for others

Section 5 — Conceptual data flow (the convoy in plain English)

For Journey J-500 with 5 riders heading to the airport, dispatched as a 2-car convoy:

• fct_journeys — the group's perspective. One row, $60 total, EQUAL_SPLIT, 2 vehicles dispatched.
• fct_trips — the drivers' perspective. Two rows: Driver Alpha ($30 share), Driver Bravo ($30 share).
• brg_journey_riders — the riders' perspective. Three rows: each rider charged $20, two assigned to T-101, one to T-102.

Enhanced graphical data model — Trip Lifecycle & Analytical Metrics

Solid arrows = referential FK relationships. Dashed arrows = analytical lineage (which fact tables feed which metric module).

The driverless future — what changes when the human driver disappears?

As autonomous vehicles (AVs) become a real share of the fleet, the data model has to absorb three structural shifts:

• The driver dimension shrinks. dim_users becomes mostly riders. Drivers don't go away entirely — they become safety operators / remote-fleet teleoperators — but most rows in fct_trips won't have a human driver_id.
• The vehicle dimension explodes. dim_vehicles evolves into dim_av_fleet with software version, sensor calibration, ODD (Operational Design Domain) restrictions, last safety-test pass, etc. — all SCD2 because they drift.
• A whole new fact table appears for safety telemetry. Every disengagement, every remote-intervention, every edge-case recognition gets logged. Regulators require it; insurance audits depend on it.

Q15 — AV safety metric: miles per disengagement (the core regulator KPI)

WITH av_miles AS (
  SELECT
    a.software_version,
    SUM(t.trip_distance_km) * 0.621371 AS total_miles
  FROM fct_trips t
  JOIN dim_av_fleet a ON t.av_key = a.av_key
  WHERE t.terminal_state = 'completed'
    AND t.request_timestamp >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY a.software_version
),
disengagements AS (
  SELECT
    a.software_version,
    COUNT(*) FILTER (WHERE s.event_type = 'DISENGAGEMENT') AS n_disengagements,
    COUNT(*) FILTER (WHERE s.event_type = 'REMOTE_INTERVENTION') AS n_interventions
  FROM fct_av_safety_event s
  JOIN dim_av_fleet a ON s.av_key = a.av_key
  WHERE s.event_timestamp >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY a.software_version
)
SELECT
  m.software_version,
  ROUND(m.total_miles, 0)                               AS total_miles,
  d.n_disengagements,
  d.n_interventions,
  ROUND(m.total_miles / NULLIF(d.n_disengagements, 0))  AS miles_per_disengagement,
  ROUND(m.total_miles / NULLIF(d.n_interventions, 0))   AS miles_per_intervention
FROM av_miles m JOIN disengagements d USING (software_version)
ORDER BY miles_per_disengagement DESC;
-- Higher MPD = safer software. Required for NHTSA 2027 quarterly safety filings.

Q16 — Software version A/B: did v3.2 reduce disengagement rate vs v3.1?

WITH per_version AS (
  SELECT a.software_version,
    SUM(t.trip_distance_km * 0.621371)                                  AS miles,
    COUNT(*) FILTER (WHERE s.event_type = 'DISENGAGEMENT')              AS disengagements
  FROM fct_trips t
  JOIN dim_av_fleet a              ON t.av_key = a.av_key
  LEFT JOIN fct_av_safety_event s  ON s.trip_id = t.trip_id
  WHERE a.software_version IN ('v3.1', 'v3.2')
  GROUP BY a.software_version
)
SELECT
  software_version,
  ROUND(disengagements::DECIMAL / miles * 1000000, 2) AS disengagements_per_mil_miles
FROM per_version;
-- Compare across versions; statistical-significance test runs in the model card.

11.1 — Re-stated grain hierarchy under stress

11.2 — Growth accounting on both sides of the marketplace

To know whether a 20K-person event is being fueled by new users or resurrected ones (and which drivers are churning), every user is segmented daily.

Q22 — L1 / L7 / L28 active status per user (daily refresh)

SELECT
  user_id,
  user_role,                                                                -- 'rider' | 'driver'
  MAX(CASE WHEN activity_date >= CURRENT_DATE - 1  THEN 1 ELSE 0 END) AS is_L1,
  MAX(CASE WHEN activity_date >= CURRENT_DATE - 7  THEN 1 ELSE 0 END) AS is_L7,
  MAX(CASE WHEN activity_date >= CURRENT_DATE - 28 THEN 1 ELSE 0 END) AS is_L28
FROM dw_user_daily_activity
GROUP BY user_id, user_role;

Q23 — Growth segmentation: New / Retained / Resurrected / Churned (daily diff)

WITH today AS (
  SELECT DISTINCT user_id, user_role FROM dw_user_daily_activity
  WHERE activity_date = CURRENT_DATE - 1                                    -- yesterday's activity
),
prior AS (
  SELECT DISTINCT user_id, user_role FROM dw_user_daily_activity
  WHERE activity_date BETWEEN CURRENT_DATE - 8 AND CURRENT_DATE - 2          -- prior 7-day window
),
ever AS (
  SELECT DISTINCT user_id, user_role FROM dw_user_daily_activity
  WHERE activity_date < CURRENT_DATE - 8                                    -- before prior window
)
SELECT user_role,
  COUNT(*) FILTER (WHERE t.user_id IS NOT NULL AND p.user_id IS NULL  AND e.user_id IS NULL)  AS new_users,
  COUNT(*) FILTER (WHERE t.user_id IS NOT NULL AND p.user_id IS NOT NULL)                      AS retained,
  COUNT(*) FILTER (WHERE t.user_id IS NOT NULL AND p.user_id IS NULL  AND e.user_id IS NOT NULL) AS resurrected,
  COUNT(*) FILTER (WHERE t.user_id IS NULL     AND p.user_id IS NOT NULL)                      AS churned
FROM today t
FULL OUTER JOIN prior p USING (user_id, user_role)
LEFT  JOIN ever  e USING (user_id, user_role)
GROUP BY user_role;
-- Driver "churned" spike before a stadium event = supply crisis incoming. Marketing fires re-engagement push.

11.3 — Mass departure with road blocks: SCD2 geography in action

When an accident or police closure occurs during the stadium emptying out, dim_geography records the change as a new SCD2 row. Old route remains queryable; new route status is "as-of-trip".

Q24 — Correlate pickup latency to road status (was the spike supply or infrastructure?)

SELECT
  g.h3_index,
  g.route_status,
  COUNT(t.trip_id)                                                                AS total_requests,
  ROUND(AVG(EXTRACT(EPOCH FROM (e_pic.event_timestamp - e_req.event_timestamp))), 1) AS avg_pickup_latency_sec,
  ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (
        ORDER BY EXTRACT(EPOCH FROM (e_pic.event_timestamp - e_req.event_timestamp))), 1) AS p95_pickup_latency_sec
FROM fct_trips t
JOIN fct_trip_events e_req ON t.trip_id = e_req.trip_id AND e_req.state = 'REQUESTED'
JOIN fct_trip_events e_pic ON t.trip_id = e_pic.trip_id AND e_pic.state = 'PICKUP'
JOIN dim_geography  g
  ON t.pickup_h3_cell = g.h3_index
 AND e_req.event_timestamp >= g.valid_from
 AND e_req.event_timestamp <  g.valid_to                                          -- as-of-request status
GROUP BY g.h3_index, g.route_status
ORDER BY p95_pickup_latency_sec DESC;
-- "Accident / Blocked" rows with high p95 = blame infrastructure, not driver supply.
-- "Normal" rows with high p95 = blame supply, surge it up.

11.4 — Financial & risk integrity under chaos

Even when a 20K event triggers cascading surges + accidents + cancellations, three SCD2 rules guarantee restate-ability:

• FX rate locked at request_timestamp. dim_fx_rate is SCD2; the rate that was current at the moment the journey was requested is the rate the rider is charged in USD, regardless of how the rate moves while the trip is in progress. Prevents arbitrage and revenue restatement.
• Insurance status as-of accident timestamp. dim_vehicles SCD2 on insurance. If a crash happens at 22:14, Risk queries dim_vehicles with valid_from ≤ 22:14 < valid_to — exact policy in effect to the millisecond.
• Surge multiplier locked in fct_trips at journey request. Even if surge spikes from 1.5× to 3.0× while the rider is being matched, the rider pays the multiplier that was active at request. Stored on the row, not derived.

Q25 — Insurance audit: was the vehicle insured at the moment of the accident?

SELECT
  t.trip_id,
  t.vehicle_id,
  e.event_timestamp                                                  AS accident_ts,
  v.insurance_policy_id,
  v.insurance_status,
  v.valid_from                                                       AS policy_active_from,
  v.valid_to                                                         AS policy_active_to,
  CASE WHEN v.insurance_status = 'active' THEN 'COVERED' ELSE 'GAP' END AS audit_verdict
FROM fct_trip_events e
JOIN fct_trips        t ON e.trip_id = t.trip_id
JOIN dim_vehicles     v ON t.vehicle_id = v.vehicle_id
                      AND e.event_timestamp >= v.valid_from
                      AND e.event_timestamp <  v.valid_to
WHERE e.state = 'ACCIDENT_REPORTED';
-- Required for insurance claims + regulator audits. Single-row defensible answer.

11.5 — Scalability rules under load (the engineering details)

Section 7 — SQL analysis for business units

Sample data (Journey J-500: airport convoy with 3 riders split across 2 vehicles)

INSERT INTO dim_users VALUES
  (1,'R_1','rider',  'Gold',     NULL),
  (2,'R_2','rider',  'Silver',   NULL),
  (3,'R_3','rider',  'Silver',   NULL),
  (4,'D_Alpha','driver','Platinum', NULL),
  (5,'D_Bravo','driver','Gold',     NULL);

-- 1 journey
INSERT INTO fct_journeys
 (journey_key, journey_id, requesting_user_id, split_type,
  total_group_fare_usd, num_vehicles_dispatched, num_riders, journey_status, request_timestamp)
VALUES
  (500,'J-500',1,'EQUAL_SPLIT', 60.00, 2, 3, 'completed', '2025-05-01 08:30:00');

-- 2 trips under J-500 (the convoy)
INSERT INTO fct_trips
 (trip_key, trip_id, journey_id, driver_id, vehicle_id, trip_fare_share_usd,
  surge_multiplier, terminal_state)
VALUES
  (101,'T-101','J-500', 4, 99, 30.00, 1.0, 'completed'),
  (102,'T-102','J-500', 5, 88, 30.00, 1.0, 'completed');

-- Trip events for both vehicles
INSERT INTO fct_trip_events VALUES
  ('e01','T-101','REQUESTED','2025-05-01 08:30:00','h3_a','rider'),
  ('e02','T-101','MATCHED',  '2025-05-01 08:31:00','h3_a','system'),
  ('e03','T-101','PICKUP',   '2025-05-01 08:35:00','h3_a','driver'),
  ('e04','T-101','DROPOFF',  '2025-05-01 09:00:00','h3_z','driver'),
  ('e05','T-102','REQUESTED','2025-05-01 08:30:00','h3_a','rider'),
  ('e06','T-102','MATCHED',  '2025-05-01 08:31:30','h3_a','system'),
  ('e07','T-102','PICKUP',   '2025-05-01 08:35:00','h3_a','driver'),  -- same cell as T-101 (legit convoy)
  ('e08','T-102','DROPOFF',  '2025-05-01 09:01:00','h3_z','driver');

-- Bridge: 3 riders, 2 assigned to T-101, 1 assigned to T-102
INSERT INTO brg_journey_riders VALUES
  ('J-500', 1, 'T-101', 20.00, 1, 12.0),
  ('J-500', 2, 'T-101', 20.00, 2, 12.0),
  ('J-500', 3, 'T-102', 20.00, 1, 12.0);

-- Feedback: post-dropoff ratings + sentiment from NLP
INSERT INTO fct_trip_feedback VALUES
  (9001,101,1,5,'driver_behavior',     'Great driver, smooth ride',  0.78,'2025-05-01 10:30:00'),
  (9002,101,2,4,'route',               'Took a slight detour',       0.20,'2025-05-01 10:42:00'),
  (9003,102,3,2,'vehicle_cleanliness', 'Car smelled bad',           -0.65,'2025-05-01 11:05:00');

A. Operations · live ETA & lifecycle metrics

Q1 — System lag: per-state transition latency using gaps-and-islands

SELECT
  trip_id,
  state,
  event_timestamp,
  LAG(event_timestamp) OVER (PARTITION BY trip_id ORDER BY event_timestamp) AS prev_event_time,
  EXTRACT(EPOCH FROM (
    event_timestamp -
    LAG(event_timestamp) OVER (PARTITION BY trip_id ORDER BY event_timestamp)
  )) AS transition_latency_sec
FROM fct_trip_events
WHERE trip_id = 'T-101'
ORDER BY event_timestamp;

Result for T-101:

Use case: high transition_latency_sec on REQUESTED→MATCHED indicates "hot partitions" or processing delays — Ops can alert when the cell-level p95 exceeds threshold.

Q2 — Dispatch latency (p50 / p95) per H3 cell per hour

WITH latency AS (
  SELECT
    e.trip_id,
    DATE_TRUNC('hour',
      MIN(CASE WHEN state='REQUESTED' THEN event_timestamp END)) AS hour_bucket,
    MAX(CASE WHEN state='REQUESTED' THEN location_h3 END) AS h3_cell,
    EXTRACT(EPOCH FROM (
      MAX(CASE WHEN state='MATCHED'   THEN event_timestamp END)
      - MAX(CASE WHEN state='REQUESTED' THEN event_timestamp END)
    )) AS latency_sec
  FROM fct_trip_events e
  GROUP BY trip_id
  HAVING MAX(CASE WHEN state='MATCHED' THEN 1 END) = 1
)
SELECT hour_bucket, h3_cell, COUNT(*) AS matched_trips,
  PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY latency_sec) AS p50_sec,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY latency_sec) AS p95_sec
FROM latency GROUP BY 1, 2 ORDER BY p95_sec DESC;

Q3 — Cancellation breakdown by actor

SELECT terminal_state, COUNT(*) AS cnt,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_total
FROM fct_trips
GROUP BY terminal_state ORDER BY cnt DESC;

Q4 — Convoy efficiency (vehicles per journey, riders per vehicle)

SELECT
  j.journey_id,
  j.num_riders,
  j.num_vehicles_dispatched,
  ROUND(j.num_riders::DECIMAL / j.num_vehicles_dispatched, 2) AS riders_per_vehicle,
  COUNT(DISTINCT t.trip_id) AS actual_trips,
  COUNT(DISTINCT b.rider_id) AS riders_seen
FROM fct_journeys j
JOIN fct_trips t USING (journey_id)
JOIN brg_journey_riders b USING (journey_id)
GROUP BY j.journey_id, j.num_riders, j.num_vehicles_dispatched;

Result for J-500:

B. Finance · revenue reconciliation & driver payout

Q5 — Driver payout reconciliation (per-driver share vs riders' charges)

SELECT
  t.driver_id,
  t.trip_id,
  t.trip_fare_share_usd  AS revenue_basis,
  SUM(b.individual_charge_usd) AS total_collected_from_riders,
  COUNT(b.rider_id)            AS riders_in_this_car
FROM fct_trips t
JOIN brg_journey_riders b ON t.trip_id = b.trip_id
GROUP BY t.driver_id, t.trip_id, t.trip_fare_share_usd
ORDER BY t.trip_id;

Result for J-500:

Critical insight: Driver Alpha's car collected $40 from riders but Alpha is paid only on $30 revenue_basis (the journey was split equally between drivers). The $10 gap goes to the platform as the "convoy coordination" fee. Without this two-grain design, you couldn't represent it.

Q6 — Pool fare allocation per rider (single-vehicle Pool, distance-prorata)

-- Same query, useful when split_type = DISTANCE_PRORATA
SELECT
  b.journey_id,
  b.rider_id,
  b.trip_id,
  b.pickup_sequence,
  b.leg_distance_km,
  ROUND(100.0 * b.leg_distance_km
        / SUM(b.leg_distance_km) OVER (PARTITION BY b.journey_id), 1) AS distance_share_pct,
  b.individual_charge_usd,
  j.total_group_fare_usd
FROM brg_journey_riders b
JOIN fct_journeys j USING (journey_id)
WHERE b.journey_id = 'J-500'
ORDER BY b.trip_id, b.pickup_sequence;

Q7 — Twin invariant data-quality check (continuous DQ)

-- Invariant 1: SUM(brg.individual_charge) per journey = fct_journeys.total_group_fare
-- Invariant 2: SUM(fct_trips.trip_fare_share) per journey = fct_journeys.total_group_fare
SELECT
  j.journey_id,
  j.total_group_fare_usd                            AS reported_total,
  ROUND(SUM(DISTINCT t.trip_fare_share_usd), 2)     AS sum_trip_shares,
  (SELECT ROUND(SUM(b.individual_charge_usd), 2)
     FROM brg_journey_riders b WHERE b.journey_id = j.journey_id)
                                                    AS sum_rider_charges,
  CASE
    WHEN j.total_group_fare_usd != SUM(DISTINCT t.trip_fare_share_usd) THEN 'FAIL_TRIP_INVARIANT'
    WHEN j.total_group_fare_usd != (SELECT SUM(individual_charge_usd)
                                    FROM brg_journey_riders WHERE journey_id = j.journey_id)
                                    THEN 'FAIL_RIDER_INVARIANT'
    ELSE 'PASS'
  END AS dq_status
FROM fct_journeys j
JOIN fct_trips t USING (journey_id)
GROUP BY j.journey_id, j.total_group_fare_usd;
-- Any FAIL row = producer regression. Required for SOX / audit.

C. Risk · fraud detection

Q8 — Phantom Convoy detection (drivers claim convoy but aren't actually together)

-- Risk pattern: two drivers booked into the same journey but their PICKUP H3 cells
-- are far apart at the same moment. Either a producer bug or a fraud ring.
SELECT
  e1.trip_id        AS car_1,
  e2.trip_id        AS car_2,
  e1.location_h3    AS loc_1,
  e2.location_h3    AS loc_2,
  EXTRACT(EPOCH FROM ABS(e1.event_timestamp - e2.event_timestamp)) AS pickup_time_gap_sec
FROM fct_trip_events e1
JOIN fct_trips t1 ON e1.trip_id = t1.trip_id
JOIN fct_trips t2 ON t1.journey_id = t2.journey_id AND t1.trip_id < t2.trip_id
JOIN fct_trip_events e2 ON e2.trip_id = t2.trip_id
WHERE e1.state = 'PICKUP' AND e2.state = 'PICKUP'
  AND e1.location_h3 != e2.location_h3                       -- not in same H3 cell
  AND ABS(EXTRACT(EPOCH FROM (e1.event_timestamp - e2.event_timestamp))) < 600;  -- within 10 min
-- Any returned row = an investigation item.

For J-500 sample data, both cars pickup at h3_a within 0 seconds of each other → no rows returned → legit convoy.

Q9 — Multi-rider capacity check (over-capacity vehicle)

SELECT
  t.trip_id,
  v.model,
  v.capacity         AS vehicle_capacity,
  COUNT(b.rider_id)  AS passenger_count
FROM fct_trips t
JOIN brg_journey_riders b ON t.trip_id = b.trip_id
JOIN dim_vehicles      v ON t.vehicle_id = v.vehicle_key
GROUP BY t.trip_id, v.model, v.capacity
HAVING COUNT(b.rider_id) > v.capacity;
-- Off-app riders being charged = either producer bug or driver fraud.

Q10 — Location anomaly: request vs first-pickup divergence

WITH first_pickup AS (
  SELECT trip_id, location_h3 AS pickup_h3
  FROM fct_trip_events
  WHERE state = 'STARTED'
)
SELECT
  t.trip_key, t.trip_id, t.origin_h3_key, fp.pickup_h3,
  CASE WHEN t.origin_h3_key <> fp.pickup_h3 THEN 'INVESTIGATE' ELSE 'OK' END AS risk_flag
FROM fct_trips t
LEFT JOIN first_pickup fp USING (trip_id)
WHERE t.terminal_state = 'completed';

D. Product · CSAT, sentiment & growth accounting

Q11 — Trip rating distribution (1–5 stars) per driver, last 28 days

SELECT
  t.driver_id,
  COUNT(f.feedback_key)                                            AS total_ratings,
  ROUND(AVG(f.rating_score), 2)                                    AS avg_rating,
  COUNT(*) FILTER (WHERE f.rating_score <= 2)                      AS low_ratings,
  ROUND(100.0 * COUNT(*) FILTER (WHERE f.rating_score <= 2)
        / NULLIF(COUNT(*), 0), 1)                                  AS pct_low_ratings
FROM fct_trips t
JOIN fct_trip_feedback f ON f.trip_key = t.trip_key
WHERE f.feedback_timestamp >= CURRENT_DATE - INTERVAL '28 days'
GROUP BY t.driver_id
ORDER BY avg_rating ASC;
-- Drivers with pct_low_ratings > 5% are flagged for retraining or offboarding.

Q12 — Rolling 7d & 28d CSAT (% of ratings ≥ 4)

WITH daily_ratings AS (
  SELECT DATE(feedback_timestamp) AS d,
         COUNT(*)                                  AS n_ratings,
         COUNT(*) FILTER (WHERE rating_score >= 4) AS n_satisfied
  FROM fct_trip_feedback
  GROUP BY 1
)
SELECT d,
  ROUND(100.0 * SUM(n_satisfied) OVER (ORDER BY d ROWS  6 PRECEDING)
              / NULLIF(SUM(n_ratings) OVER (ORDER BY d ROWS  6 PRECEDING), 0), 1) AS csat_7d,
  ROUND(100.0 * SUM(n_satisfied) OVER (ORDER BY d ROWS 27 PRECEDING)
              / NULLIF(SUM(n_ratings) OVER (ORDER BY d ROWS 27 PRECEDING), 0), 1) AS csat_28d
FROM daily_ratings
ORDER BY d;

Q13 — Sentiment by feedback category (NLP signal × free-text)

SELECT
  feedback_category,
  COUNT(*)                                AS n_comments,
  ROUND(AVG(sentiment_score), 3)          AS avg_sentiment,
  ROUND(AVG(rating_score), 2)             AS avg_stars,
  COUNT(*) FILTER (WHERE sentiment_score < -0.3) AS n_strongly_negative
FROM fct_trip_feedback
WHERE feedback_timestamp >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY feedback_category
ORDER BY avg_sentiment ASC;
-- Surfaces what's driving the dissatisfaction — vehicle, route, app, payment, driver.

Q14 — Growth accounting: L1 / L7 / L28 active rider counts

WITH user_activity AS (
  SELECT b.rider_id,
    MAX(j.request_timestamp) AS last_active_ts
  FROM brg_journey_riders b
  JOIN fct_journeys j USING (journey_id)
  GROUP BY b.rider_id
)
SELECT
  COUNT(*) FILTER (WHERE last_active_ts >= CURRENT_TIMESTAMP - INTERVAL '1 day')   AS l1_active,
  COUNT(*) FILTER (WHERE last_active_ts >= CURRENT_TIMESTAMP - INTERVAL '7 days')  AS l7_active,
  COUNT(*) FILTER (WHERE last_active_ts >= CURRENT_TIMESTAMP - INTERVAL '28 days') AS l28_active,
  COUNT(*)                                                                          AS total_riders
FROM user_activity;
-- Standard L1/L7/L28 active KPIs from Reforge growth-accounting framework.

Worked numerical example — Convoy J-500 (EQUAL_SPLIT, $60 group fare)

1. 5 friends call a group ride. 3 are billable riders (R_1, R_2, R_3); the others are children covered under R_1's account. Group fare quoted: $60.00.
2. System dispatches 2 vehicles because the group exceeds single-car capacity → fct_journeys.num_vehicles_dispatched = 2.
3. Driver Alpha's car (T-101) takes 2 riders (R_1, R_2). Driver Bravo's car (T-102) takes 1 rider (R_3).
4. Per-rider charge (EQUAL_SPLIT): $60 / 3 = $20.00 per rider. Stored in brg_journey_riders.individual_charge_usd.
5. Per-driver share: $60 / 2 = $30.00 per driver. Stored in fct_trips.trip_fare_share_usd.
6. Invariant 1 (rider side): SUM(brg.individual_charge) = $20 × 3 = $60 ✓
7. Invariant 2 (driver side): SUM(fct_trips.trip_fare_share) = $30 × 2 = $60 ✓
8. Note the asymmetry: Driver Alpha's car collected $40 in charges from his 2 riders, but Alpha's payout basis is $30 — the platform retains $10 as a convoy-coordination fee. Driver Bravo's car collected $20 from his 1 rider but is paid $30 — the platform covers the $10 gap. This is fair: the journey was equally split between drivers regardless of which car each rider sat in. Without the two-grain design (trip share AND rider charge), you couldn't represent it.

Section 8 — Why this works for the convoy scenario

• Unique driver accounts. By placing driver_id on fct_trips (not fct_journeys), the model honors the rule that a (driver, journey) combination is unique — same driver can't take two trips for one journey, but two drivers can share one journey.
• Cost flexibility. The bridge supports multi-destination and DISTANCE_PRORATA logic. If Rider 1 exits at Mile 5 and Rider 2 at Mile 10, individual_charge_usd is computed pro-rata using leg_distance_km captured from fct_trip_events — same model, different split_type.
• Auditability. Finance traces every cent from a rider's wallet through brg_journey_riders directly to the specific driver's fct_trips payout. The two continuous invariants (Q7) catch producer bugs before they hit a quarterly SOX report.
• Scalability. "Pool for 4", "Bus", "Wedding party of 12" — no schema migration. Just more rows in brg_journey_riders; the journey grain is unchanged.
• Eliminates granularity mismatch. The 3-tier hierarchy (journey, trip, event) lets each stakeholder query at their natural grain without filters or self-joins.

Section 9 — Business model evolution: single-sided → two-sided → three-sided → driverless

The Uber data model isn't static — it's shaped by the platform's market structure, and that structure is shifting. As Uber harnesses platform leverage, AI, and driverless technologies, the rider may eventually hail a ride that involves no human driver at all. A senior architect should be able to describe how the data model morphs at each platform shape, and what the driverless future demands.

Section 6 — Sample data representation

Table: fct_journeys (the logical group)

Table: fct_trips (the physical vehicles)

Table: brg_journey_riders (the rider split)

Two reconciliations: SUM(brg.individual_charge) = $20 + $20 + $20 = $60 ✓  |  SUM(fct_trips.trip_fare_share) = $30 + $30 = $60 ✓  |  both equal fct_journeys.total_group_fare.

Why this is hard — the design tension

Surge isn't one fact — it's a causal chain: state observed → model invoked → multiplier set → riders react → drivers reposition → state changes. Each link needs its own grain or you lose the ability to evaluate the engine. The model also has to replay history with the surge model that was active then, not today's — pricing teams A/B test new models monthly. And the geographic skew is brutal: midtown Manhattan on New Year's Eve has 100× the rows of suburban Queens.

Section 1 — Dimensional Model Overview

Star Schema with a State → Decision → Outcome causal chain.

Fact Tables

• fct_geo_state_minute — The "Observation" grain. One row per (H3 cell × minute). Captures open requests, available drivers, ratio, ETA, weather, event signals.
• fct_surge_decision — The "Decision" grain. One row per surge-multiplier change per cell. Captures which model version made the call and on what input features.
• fct_surge_outcome — The "Effectiveness" grain. One row per surge decision joined to its post-decision N-minute outcome (did supply rebalance? did the price stick?).
• brg_zone_cells — Hierarchy bridge. Many H3 cells roll up to a surge zone (operational region used by the pricing team).

Dimension Tables

• dim_h3_cell — H3 L8 hexagons. SCD1 (geometry is static).
• dim_surge_zone — Operational regions (e.g. "SF Downtown", "JFK Airport"). SCD2 — boundaries get re-drawn.
• dim_surge_model — Versioned ML/heuristic models. SCD2 — drives the replay capability.
• dim_weather — Weather state per cell per hour (rain, snow, temperature). SCD1 snapshot.
• dim_event — Concerts, sports games, conferences. SCD1 (each event is a row, not a state).

Section 10 — Geospatial indexing: GeoHash vs H3 vs Quadtree

The model leans on H3 everywhere — dim_h3_cell, fct_geo_state_minute per cell. A senior interviewer will ask why H3 and not the alternatives. Have the trade-off ready.

Why H3 wins here: surge is a supply/demand ratio per cell, and the math only behaves if cells are equal-area and neighbors are equidistant — you average a cell against its ring of neighbors. Hexagons give that; GeoHash rectangles do not (a diagonal neighbor sits 1.4× farther than an edge neighbor, biasing the ratio). The choice of dim_h3_cell is not cosmetic — it is what makes driver_to_request_ratio a trustworthy number.

Storage — Redis GEO vs a custom H3 grid:

The surge engine reading "a Redis snapshot of the latest minute" keyed by cell is the custom H3 grid — chosen over raw Redis GEO because surge needs per-cell aggregate state, not point-radius search. And resolution is a dial: L10 localizes demand more sharply than L8 but multiplies the cell count (and the per-minute write cost) — so the model picks resolution per zone (L8 cities, L10 venues), never globally.

Section 11 — Update amplification & location staleness

Two production realities the clean per-minute grain hides — and both are favourite senior probes.

Update amplification. Each driver app emits a location ping roughly every 4 s. With D drivers online that is D/4 writes per second. A naïve design fans every single ping out to the matching index, the surge counter, the rider's live map, and the ETA service — one ping becomes four downstream writes, and matching re-evaluates candidate sets on every ping. The counter-moves:

Location staleness. A driver's last fix is 25 s old — include them in matching, or not? The rule is degrade, don't exclude — and degrade by confidence, not a hard cutoff:

A hard 30-second cutoff is the amateur move: dropping every stale driver in a supply-starved cell shrinks the matchable pool, pushes the multiplier higher, and the surge model starts fighting itself. Staleness is a ranking signal weighted against supply pressure, not a binary filter — so carry location_age_sec alongside every location read and let the matcher weight it.

Section 2 — The Logical Data Model

Observation Fact: fct_geo_state_minute

Decision Fact: fct_surge_decision

Outcome Fact: fct_surge_outcome

Section 3 — How the model serves each stakeholder

The Surge Engine · live

• Reads fct_geo_state_minute for the latest minute, joins dim_surge_model to get the active model, computes a multiplier, writes fct_surge_decision.
• Round-trip target: under 500 ms per cell. Reads come from a Redis snapshot of the latest minute; writes append to Kafka, then to the warehouse.

Pricing Team · effectiveness analysis

• Did the surge work? A surge "worked" if (delta_drivers > 0 within 10 min) AND (abandoned_requests stayed flat). Both signals come from fct_surge_outcome.
• Pricing elasticity: for each multiplier band, what % of riders abandoned? Plot abandonment vs multiplier — the inflection point is the pain threshold.
• Model A/B: two model versions live in dim_surge_model; route a % of cells to each. Compare realized_revenue_usd + abandoned_requests per model.

Capacity Planning · forecasting

• Train demand forecast on historical fct_geo_state_minute. MAPE measured against later observed minutes.
• Identify chronic supply gaps — cells where driver_to_request_ratio is below 0.5 for >30% of peak hours. These get long-term incentive programs.

Section 4 — Why versioning the surge model matters (and why input_features_json is mandatory)

Without a versioned dim_surge_model + frozen input features, you cannot replay history. Three failure modes show up the moment the pricing team A/B tests:

• Failure 1: model drift hides a regression. If today's model retroactively scores yesterday's input, a buggy current-model version "explains" yesterday's outcome cleanly — even when the actual yesterday's model produced a wildly different result.
• Failure 2: feature drift. A weather data source upgrades from "rain_mm" to "precip_intensity" mid-week. Without frozen input_features_json, you can't tell whether yesterday's surge was driven by rain or by something else.
• Failure 3: dispute resolution. A regulator asks "why was this rider charged 3.2× on this date?" — only frozen features + the SCD2 model row gives you a defensible answer.

The contract: every fct_surge_decision row carries a deterministic, replay-safe snapshot of the inputs. Same inputs + same model_key → identical multiplier, every time, forever.

Section 5 — Conceptual data flow (the State → Decision → Outcome chain)

Enhanced graphical data model — Surge Pricing & Effectiveness

Section 9 — The fare lifecycle: estimate, commit, and the audit replay

Surge sets a multiplier — but a senior answer models the whole fare, and a fare is not one number. It has a lifecycle with two hard time boundaries that an interviewer expects you to separate.

The two boundaries are time-of-decision (request/accept — surge multiplier, FX rate, pricing-model version all freeze here) and time-of-charge (dropoff — the committed fare may differ from the quote: route changed, wait time, tolls, a promo). The delta between quote and committed is itself an auditable fact. They must be separate fields because they live in different consistency regimes: the estimate is fast and eventually-consistent (Redis, re-computed every few seconds); the committed fare is strong-consistency and append-only (the ledger). Fuse them into one column and either the ledger inherits the jitter of the UX quote, or the quote inherits the latency of the ledger.

The audit replay — "recompute exactly what the rider was charged." Finance and regulators demand reproducibility. Add one FK to fct_trips — pricing_model_version — pointing at a new SCD2 dimension dim_pricing_model (the base-fare / per-km / per-min rate card), exactly parallel to the existing dim_surge_model. With surge_multiplier and fx_rate_to_usd already frozen on the row, the fare becomes a pure function of frozen inputs:

-- Reproduce the exact fare a rider was charged: join the fare to the pricing
-- model active AT the trip's request time — never today's rate card.
SELECT t.trip_id,
       t.total_fare_usd                                          AS charged,
       (pm.base_fare + pm.per_km * t.distance_km
                     + pm.per_min * t.duration_min) * t.surge_multiplier AS recomputed
FROM   fct_trips t
JOIN   dim_pricing_model pm
  ON   pm.pricing_model_version = t.pricing_model_version
 AND   t.request_timestamp BETWEEN pm.effective_from
                               AND COALESCE(pm.effective_to, '9999-12-31');
-- charged = recomputed  → the trip is auditable. A mismatch is a real bug.

Versioned pricing logic follows the same discipline as the surge model (Section 4): the rate card is an SCD2 row, not code baked into the ETL. Historical recalculation never runs today's formula over yesterday's trips — it joins as-of-trip-time. A/B pricing experiments run two pricing_model_versions at once; fct_trips.pricing_model_version records which one priced each trip, so revenue and conversion compare cleanly per version.

Section 7 — SQL analysis for business units

Sample data (a single H3 cell, 5 minutes of supply/demand state and 1 surge decision)

INSERT INTO fct_geo_state_minute VALUES
  ('h3_sf_market', '2025-05-01 19:00:00',  8, 12, 1.50, 240, 'clear', NULL),
  ('h3_sf_market', '2025-05-01 19:01:00', 14,  7, 0.50, 360, 'clear', ARRAY['concert_chase_center']),
  ('h3_sf_market', '2025-05-01 19:02:00', 22,  5, 0.23, 540, 'clear', ARRAY['concert_chase_center']),
  ('h3_sf_market', '2025-05-01 19:03:00', 19,  9, 0.47, 410, 'clear', ARRAY['concert_chase_center']),
  ('h3_sf_market', '2025-05-01 19:04:00', 16, 14, 0.88, 280, 'clear', ARRAY['concert_chase_center']);

-- Pricing engine fires a surge decision at 19:02 when ratio crashes:
INSERT INTO fct_surge_decision VALUES
  (5001, 'h3_sf_market', 'model_v3.2', '2025-05-01 19:02:00',
   1.0, 2.5,
   '{"ratio":0.23,"eta_sec":540,"event":"concert_chase_center","prev_minute_ratio":0.50}',
   'LOW_SUPPLY');

-- Outcome computed nightly (10-min window post-decision)
INSERT INTO fct_surge_outcome VALUES
  (5001, 10, +9, -3, 14, 5, 1240.00);

A. Pricing engine · live decisions

Q1 — Cells currently below ratio 0.5 (live surge candidates)

WITH latest_state AS (
  SELECT h3_key, MAX(minute_bucket_ts) AS latest_ts
  FROM fct_geo_state_minute
  WHERE minute_bucket_ts >= NOW() - INTERVAL '2 minutes'
  GROUP BY h3_key
)
SELECT s.h3_key,
       s.driver_to_request_ratio,
       s.open_requests, s.available_drivers,
       s.predicted_eta_sec,
       w.precip_intensity, e.event_name
FROM fct_geo_state_minute s
JOIN latest_state l USING (h3_key)
LEFT JOIN dim_weather w ON s.weather_key = w.weather_key
LEFT JOIN dim_event   e ON e.event_key = ANY(s.event_keys)
WHERE s.minute_bucket_ts = l.latest_ts
  AND s.driver_to_request_ratio < 0.5
ORDER BY s.driver_to_request_ratio ASC;

B. Pricing team · effectiveness analysis

Q2 — Surge effectiveness: % of decisions that pulled supply within 10 min

SELECT
  d.model_key,
  COUNT(*)                                                 AS n_decisions,
  COUNT(*) FILTER (WHERE o.delta_drivers > 0)              AS pulled_supply,
  ROUND(100.0 * COUNT(*) FILTER (WHERE o.delta_drivers > 0)
        / COUNT(*), 1)                                     AS effectiveness_pct,
  ROUND(AVG(o.delta_drivers), 1)                           AS avg_delta_drivers,
  ROUND(AVG(o.abandoned_requests), 1)                      AS avg_abandoned
FROM fct_surge_decision d
JOIN fct_surge_outcome  o USING (decision_key)
WHERE o.n_minutes_after = 10
  AND d.decision_ts >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY d.model_key
ORDER BY effectiveness_pct DESC;

Q3 — Pricing elasticity: abandonment by multiplier band

SELECT
  CASE
    WHEN d.new_multiplier <  1.5 THEN '1.0-1.5x'
    WHEN d.new_multiplier <  2.0 THEN '1.5-2.0x'
    WHEN d.new_multiplier <  3.0 THEN '2.0-3.0x'
    ELSE                              '3.0x+'
  END                                              AS multiplier_band,
  COUNT(*)                                         AS n_decisions,
  ROUND(AVG(o.abandoned_requests), 1)              AS avg_abandoned,
  ROUND(AVG(o.completed_trips), 1)                 AS avg_completed,
  ROUND(100.0 * AVG(o.abandoned_requests)
        / NULLIF(AVG(o.abandoned_requests + o.completed_trips), 0), 1) AS abandonment_pct,
  ROUND(AVG(o.realized_revenue_usd), 2)            AS avg_revenue_usd
FROM fct_surge_decision d
JOIN fct_surge_outcome  o USING (decision_key)
WHERE o.n_minutes_after = 10
GROUP BY multiplier_band
ORDER BY multiplier_band;
-- The inflection point in abandonment_pct is the rider pain threshold.

Q4 — Model A/B comparison: revenue per cell-minute under v3.1 vs v3.2

SELECT
  d.model_key,
  COUNT(*)                                          AS n_decisions,
  ROUND(SUM(o.realized_revenue_usd), 2)             AS total_revenue,
  ROUND(SUM(o.realized_revenue_usd) / COUNT(*), 2)  AS rev_per_decision,
  ROUND(AVG(o.completed_trips), 2)                  AS avg_completed,
  ROUND(AVG(o.abandoned_requests), 2)               AS avg_abandoned
FROM fct_surge_decision d
JOIN fct_surge_outcome  o USING (decision_key)
WHERE d.decision_ts >= CURRENT_DATE - INTERVAL '14 days'
  AND d.model_key IN ('model_v3.1', 'model_v3.2')
  AND o.n_minutes_after = 10
GROUP BY d.model_key;
-- Statistical significance test runs in the model card; this is the headline.

C. Capacity planning · forecast accuracy & chronic gaps

Q5 — Demand forecast MAPE per cell (predicted vs actual)

SELECT
  h3_key,
  COUNT(*)                                                                            AS n_minutes,
  ROUND(AVG(ABS(predicted_demand - open_requests)
            / NULLIF(open_requests, 0)) * 100, 2)                                     AS mape_pct,
  ROUND(AVG(predicted_demand - open_requests), 2)                                     AS avg_bias,
  COUNT(*) FILTER (WHERE predicted_demand < open_requests)                            AS underforecast_min
FROM fct_geo_state_minute
WHERE minute_bucket_ts >= CURRENT_DATE - INTERVAL '7 days'
  AND open_requests > 0
GROUP BY h3_key
HAVING AVG(ABS(predicted_demand - open_requests) / NULLIF(open_requests, 0)) > 0.20
ORDER BY mape_pct DESC;
-- Cells with MAPE > 20% need feature engineering attention.

Q6 — Chronic supply gaps (cells under-supplied during peak hours regularly)

SELECT
  h3_key,
  COUNT(*)                                                            AS peak_minutes,
  COUNT(*) FILTER (WHERE driver_to_request_ratio < 0.5)                AS under_supplied_min,
  ROUND(100.0 * COUNT(*) FILTER (WHERE driver_to_request_ratio < 0.5)
        / COUNT(*), 1)                                                 AS pct_under_supplied
FROM fct_geo_state_minute
WHERE minute_bucket_ts >= CURRENT_DATE - INTERVAL '30 days'
  AND EXTRACT(HOUR FROM minute_bucket_ts) BETWEEN 17 AND 21
GROUP BY h3_key
HAVING COUNT(*) FILTER (WHERE driver_to_request_ratio < 0.5) * 1.0 / COUNT(*) > 0.30
ORDER BY pct_under_supplied DESC;
-- These cells get long-term driver incentive programs (per-trip bonus, guarantees).

Section 8 — Why this works

• Causal chain preserved. State → Decision → Outcome are three separate facts. Each can be queried in isolation, but they join cleanly via decision_key. Effectiveness analysis is one JOIN, not a self-join puzzle.
• Replay-safe by construction. Frozen input_features_json + SCD2 dim_surge_model means any historical surge can be recomputed exactly. Audits, disputes, and A/B baselines all work.
• Skew managed. H3 L8 cells partition the geography evenly; downtown skew is contained per-cell. Sub-partition by HASH(h3_key) MOD 8 for the hottest few cells if needed.
• Cold-start covered. When a new city launches, brg_zone_cells can map untrained cells to their parent zone's averages until enough data accrues.

Worked example — surge math at 19:02 in h3_sf_market

1. At 19:01, ratio drops from 1.50 → 0.50 (concert lets out). 7 drivers available, 14 open requests. ETA jumps from 240s → 360s.
2. At 19:02, ratio crashes to 0.23 (5 drivers, 22 requests). The model fires a surge decision: new_multiplier = 2.5.
3. Frozen input_features_json: {ratio:0.23, eta_sec:540, event:"concert_chase_center", prev_minute_ratio:0.50}. Same inputs to model_v3.2 will always yield 2.5 — replay is deterministic.
4. 10 min later (19:12): drivers from neighboring cells reposition (delta_drivers = +9), some riders abandon (5 abandoned out of 19), but 14 trips complete. Realized revenue: $1,240.
5. Effectiveness verdict: the surge worked — supply pulled in (delta_drivers > 0), revenue captured, but at the cost of 5 abandoned trips. The pricing team weighs $1,240 captured vs ~$200 estimated lost-to-abandonment.

Why this is hard — the design tension

This is a 3-sided marketplace (customer + restaurant + courier) where one fact table can't satisfy all three. Stacked orders make it worse: one courier dispatch covers multiple orders, but each order has its own customer SLA and its own restaurant relationship. Mutable tips (editable up to 24h post-delivery) demand append-only economics. The model must read clean for each stakeholder without forcing the others to filter.

Section 1 — Dimensional Model Overview

Fact Tables (the order/dispatch split + bridge)

• fct_orders — The "Customer" grain. One row per customer order. Drives customer SLA + restaurant operations.
• fct_dispatches — The "Courier" grain. One row per courier dispatch. May cover 1 (single) or N (stacked) orders. Drives courier earnings.
• brg_dispatch_orders — The "Stacking" bridge. Links a dispatch to its 1-N orders, with leg sequence and per-order distance.
• fct_order_state_events — The "Telemetry" grain. One row per order state transition (PLACED, CONFIRMED, READY, PICKED_UP, DELIVERED, CANCELLED).

Dimension Tables

• dim_customers · dim_addresses (SCD2 on address — for legal/refund history)
• dim_restaurants (SCD2 — hours, menu version, status)
• dim_couriers (SCD2 — vehicle type, rating tier, on/off platform)
• dim_h3_cell (SCD1)

Section 2 — Logical Data Model

Customer Fact: fct_orders

Courier Fact: fct_dispatches

Bridge: brg_dispatch_orders

Section 3 — How the model serves each stakeholder

Customer · on-time SLA + experience

• On-time: delivered_ts ≤ promised_ts from fct_orders.
• Late stage detection: join fct_order_state_events to find which stage stalled (restaurant prep? courier route?).

Courier · earnings & efficiency

• Earnings per hour: SUM(payout_usd) / dispatch hours from fct_dispatches.
• Stack uplift: compare per-leg payout in single vs batched dispatches — quantifies the courier value of stacking.

Restaurant · operational health

• Prep time variance: ready_ts − confirmed_ts per restaurant per hour. Spikes = kitchen overload.
• Cancellation source: terminal_state = cancelled_restaurant rate; restaurants with >3% rate get warning.

Section 4 — Why the order/dispatch split + bridge

Three failure modes if you collapse:

• Single-fact (order grain only): can't represent "courier did 2 stacked deliveries with shared pickup" — courier earnings get double-counted.
• Single-fact (dispatch grain only): the customer SLA query needs to sum within bridge — ugly, error-prone.
• No bridge: 1:1 dispatch:order forces non-stacking — kills the entire economic case for batching.

The bridge with leg_sequence + leg_distance + leg_payout_share enables: fct_orders stays clean (1 row = 1 customer order), fct_dispatches stays clean (1 row = 1 courier shift-segment), and the join produces correct per-leg attribution for both sides.

Enhanced graphical data model — DoorDash 3-sided marketplace

Section 7 — SQL analysis for business units

Sample data (Dispatch DSP-99: 1 courier, 2 stacked orders from the same restaurant)

INSERT INTO fct_orders VALUES
  ('O-201','CUST_A','RST_PIZZA','h3_drop1','2025-05-01 18:00','2025-05-01 18:02','2025-05-01 18:18','2025-05-01 18:22','2025-05-01 18:34', 25.00, 2.10, 2.99, 1.00, 5.00, '2025-05-02 18:34', 0.00, 'delivered'),
  ('O-202','CUST_B','RST_PIZZA','h3_drop2','2025-05-01 18:01','2025-05-01 18:03','2025-05-01 18:18','2025-05-01 18:22','2025-05-01 18:42', 32.00, 2.69, 2.99, 1.00, 6.00, '2025-05-02 18:42', 0.00, 'delivered');

INSERT INTO fct_dispatches VALUES
  ('DSP-99','COURIER_X','2025-05-01 18:18','2025-05-01 18:42', TRUE, 2, 4.2, 6.50, 11.00, 17.50);

INSERT INTO brg_dispatch_orders VALUES
  ('DSP-99','O-201', 1, 2.0, 8.50),
  ('DSP-99','O-202', 2, 2.2, 9.00);

A. Customer · order latencies & SLA

Q1 — Stage-by-stage latency for the order, with whose-fault attribution

SELECT
  order_id,
  EXTRACT(EPOCH FROM (confirmed_ts - placed_ts)) / 60   AS confirm_min,
  EXTRACT(EPOCH FROM (ready_ts - confirmed_ts)) / 60    AS prep_min,
  EXTRACT(EPOCH FROM (picked_up_ts - ready_ts)) / 60    AS pickup_wait_min,
  EXTRACT(EPOCH FROM (delivered_ts - picked_up_ts)) / 60 AS drive_min,
  EXTRACT(EPOCH FROM (delivered_ts - placed_ts)) / 60   AS total_min,
  CASE
    WHEN (ready_ts - confirmed_ts)   > INTERVAL '20 min' THEN 'restaurant_slow'
    WHEN (picked_up_ts - ready_ts)   > INTERVAL '10 min' THEN 'courier_late_pickup'
    WHEN (delivered_ts - picked_up_ts) > INTERVAL '20 min' THEN 'courier_slow_drive'
    ELSE 'on_track' END                                 AS bottleneck
FROM fct_orders WHERE DATE(placed_ts) = '2025-05-01';

B. Courier · earnings & stack uplift

Q2 — Earnings per hour by stacked vs single dispatch

SELECT
  is_batched,
  COUNT(*)                                                      AS dispatches,
  ROUND(SUM(payout_usd) /
        SUM(EXTRACT(EPOCH FROM (dispatch_end_ts - dispatch_start_ts))/3600), 2) AS earnings_per_hr,
  ROUND(SUM(payout_usd) / SUM(total_orders), 2)                 AS payout_per_order,
  ROUND(AVG(total_distance_km / total_orders), 2)               AS km_per_order
FROM fct_dispatches
WHERE DATE(dispatch_start_ts) BETWEEN '2025-05-01' AND '2025-05-31'
GROUP BY is_batched;
-- Quantifies the courier value of stacking; if stacked is < 1.3× single payout/hr, batching isn't worth it.

C. Restaurant · prep time & cancellation

Q3 — Prep time p50/p95 per restaurant per day

SELECT
  restaurant_id,
  DATE(placed_ts) AS d,
  COUNT(*) AS orders,
  PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (ready_ts - confirmed_ts))/60) AS prep_p50_min,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (ready_ts - confirmed_ts))/60) AS prep_p95_min,
  ROUND(100.0 * COUNT(*) FILTER (WHERE terminal_state = 'cancelled_restaurant') / COUNT(*), 1) AS cancel_pct
FROM fct_orders
WHERE confirmed_ts IS NOT NULL AND ready_ts IS NOT NULL
GROUP BY restaurant_id, DATE(placed_ts)
ORDER BY prep_p95_min DESC;
-- Restaurants with prep_p95 > 25min get warned; > 35min get throttled (lower placement priority).

Section 8 — Why this works

• Three stakeholders, three grains, one model. Customer queries hit fct_orders; courier queries hit fct_dispatches; the bridge ties them only when stacking math matters.
• Stack uplift is measurable. Compare is_batched=TRUE vs FALSE earnings/hr — if stacking doesn't beat single by ~30%, dispatch should stop offering it.
• Mutable tips handled cleanly. Append correction rows; tip_locked_ts tracks the immutability boundary. Finance restates from tip_locked_ts.
• Stage-attribution. The bottleneck CASE WHEN in Q1 turns "the order was 18 min late" into "the restaurant prep took 22 min" — actionable per stakeholder.

Worked example — Stacked dispatch DSP-99

1. Courier X accepts dispatch at 18:18 with 2 stacked orders from RST_PIZZA. Pickup is shared (single restaurant); 2 different drop locations.
2. Order O-201 delivered first at 18:34 (16 min after pickup, 2.0 km).
3. Order O-202 delivered second at 18:42 (24 min after pickup, additional 2.2 km from drop1).
4. Courier earnings: base_pay $6.50 + tips $11.00 = $17.50 for 24 min of dispatch time → $43.75/hr. A single-order dispatch in the same area pays ~$28/hr.
5. Bridge: O-201 leg_payout_share = $8.50, O-202 leg_payout_share = $9.00. Sum = $17.50 ✓
6. Customer SLAs: O-201 on-time (16 min total); O-202 was 8 min late (waited for O-201 drop). Stacking trade-off: courier earnings up, second customer's SLA at risk.

Why this is hard — the design tension

This is a causal chain across 4 facts with extreme asymmetry: 2 billion ad requests/day → 200 billion targeting lookups → ~100 candidate ads per request → 1 winner. Loser logs are 20× winner volume. Auctions clear in <100ms but conversions land days later. The model must keep the join key (auction_decision_id) deterministic across all stages, support re-attribution when models change, and protect privacy under ATT/Sandbox.

Section 1 — Dimensional Model Overview

Fact Tables (the auction chain)

• fct_auctions — The "Decision" grain. One row per auction clearing. Carries auction_decision_id.
• fct_impressions — The "Served" grain. One row per impression (auction winner served on the page).
• fct_clicks — The "Click" grain. One row per click on an impression. Mutable: invalid-click invalidation comes later.
• fct_conversions — The "Conversion" grain. One row per advertiser-reported conversion event.
• fct_attributions — The "Attribution" grain. One row per (conversion × eligible-touchpoint × model × run). Append-only per attribution_run_id.
• fct_auction_losers — The "Counterfactual" grain. Sampled losing candidates (1%) for ranker training.

Dimension Tables

• dim_advertisers, dim_campaigns, dim_ad_groups, dim_creatives — all SCD2 (budget, targeting, creative drift).
• dim_keywords — SCD1, high cardinality.
• dim_users · privacy-tokenized (Customer Match, FLoC/Topics, ATT bucket).
• dim_attribution_models — SCD2 — versioned (last_click, position_based, data_driven_v3, etc.).

Section 2 — Logical Data Model (key columns only)

fct_auctions

fct_impressions — joined to auctions on auction_decision_id; carries served_ts, creative_id, predicted_pCTR, quality_score, ad_rank.

fct_clicks — impression_id, click_ts, is_invalid, invalidation_ts (slowly-changing).

fct_conversions — conversion_id, advertiser_id, conversion_ts, conversion_value_usd, conversion_type (purchase, signup, install).

fct_attributions — conversion_id, click_id, impression_id, model_id, attribution_run_id, fractional_credit, window_type. Multiple rows per conversion; one per (model × run × eligible touchpoint).

Section 3 — How the model serves each stakeholder

Advertiser · billing & ROAS

• Spend by campaign: SUM(clearing_price_usd) from fct_impressions joined back to fct_auctions.
• ROAS = SUM(conversion_value_usd attributed) / SUM(clearing_price_usd) — uses the latest attribution_run_id.

Ranker team · model training & quality score

• fct_auction_losers + fct_impressions = balanced training set for pCTR model.
• Quality Score retrospective: join historical creative SCD2 row + impression's predicted_pCTR to debug regressions.

Attribution & measurement

• Re-attribution: when the data-driven model upgrades from v2→v3, run a new attribution_run_id over the same conversions and impressions. Old run is preserved — no data lost.
• Incrementality: compare conversion-rate of exposed (impression served) vs counterfactual (won auction but didn't serve due to PSA / blank slot) — fct_auction_losers partly enables this.

Section 4 — Why auction_decision_id + append-only attribution

• One key, four facts. auction_decision_id is generated once at auction-time and travels with the impression, click, conversion. No fuzzy joins, no timestamp matching.
• Re-runnable attribution. Attribution is computed, not collected. New models = new attribution_run_id; the latest_run view picks the most recent. Advertisers can compare last-click vs data-driven side by side.
• Invalid clicks as a slowly-changing fact. Click validation is asynchronous (bot detection takes hours to days). Append is_invalid=TRUE with invalidation_ts rather than UPDATE — preserves historical billing audit.
• Loser sampling at 1%. 20× winners by volume. Reservoir sample to 1% and re-weight in training; full retention is unaffordable.

Enhanced graphical data model — Search Ads Auction Chain

Section 7 — SQL analysis for business units

Sample data — Nike "running shoe" auction

-- Auction at 14:32:01 — 5 candidates compete; Nike wins on ad_rank 0.0751
INSERT INTO fct_auctions VALUES
  ('AUC_NIKE_001','q_runningshoe','kw_runningshoe','U_TOK_42','dev_iphone',
   '2025-05-01 14:32:01.123','AD_NIKE_RUN','1.92','1.0','1.92','standard');

-- Impression served
INSERT INTO fct_impressions VALUES
  ('IMP_001','AUC_NIKE_001','AD_NIKE_RUN',0.034,0.92,'2025-05-01 14:32:01.180', 0.0751);

-- User clicks 4 seconds later
INSERT INTO fct_clicks VALUES ('CLK_001','IMP_001','2025-05-01 14:32:05',FALSE,NULL);

-- Conversion 17 hours later (next-day purchase)
INSERT INTO fct_conversions VALUES
  ('CONV_001','ADV_NIKE','2025-05-02 07:42:00','purchase',129.99);

-- Two attribution rows: last-click model + DDA model on the same conversion
INSERT INTO fct_attributions VALUES
  ('ATTR_001','CONV_001','IMP_001','CLK_001','MODEL_LAST_CLICK','RUN_2025_05_02', 1.000, 'click_24h', 129.99),
  ('ATTR_002','CONV_001','IMP_001','CLK_001','MODEL_DDA_v3',    'RUN_2025_05_02', 0.620, 'click_24h',  80.59);

-- Sampled losers (1%)
INSERT INTO fct_auction_losers VALUES
  ('AUC_NIKE_001','AD_ASICS_GEL', 0.0572, 100),  -- weight = 1/sample_rate
  ('AUC_NIKE_001','AD_STRAVA',    0.0408, 100);

A. Advertiser · ROAS & ranker debug

Q1 — CTR + CPA + ROAS per campaign (latest attribution run)

WITH latest_run AS (
  SELECT MAX(attribution_run_id) AS run_id FROM fct_attributions
),
campaign_metrics AS (
  SELECT
    c.campaign_id,
    COUNT(DISTINCT i.impression_id)                                               AS impressions,
    COUNT(DISTINCT cl.click_id) FILTER (WHERE cl.is_invalid = FALSE)              AS clicks,
    SUM(a.clearing_price_usd)                                                     AS spend_usd,
    COUNT(DISTINCT atr.conversion_id)                                             AS conversions,
    SUM(atr.attributed_value_usd)                                                 AS attributed_revenue
  FROM fct_auctions a
  JOIN fct_impressions i  USING (auction_decision_id)
  JOIN dim_creatives  cr  ON i.creative_id = cr.creative_id
  JOIN dim_campaigns  c   ON cr.campaign_id = c.campaign_id
  LEFT JOIN fct_clicks       cl  USING (impression_id)
  LEFT JOIN fct_attributions atr ON atr.click_id = cl.click_id
                                AND atr.attribution_run_id = (SELECT run_id FROM latest_run)
                                AND atr.model_id = 'MODEL_LAST_CLICK'
  WHERE a.auction_ts >= CURRENT_DATE - INTERVAL '7 days'
  GROUP BY c.campaign_id
)
SELECT campaign_id, impressions, clicks, spend_usd, conversions, attributed_revenue,
  ROUND(100.0 * clicks / NULLIF(impressions, 0), 3)                AS ctr_pct,
  ROUND(spend_usd / NULLIF(clicks, 0), 2)                          AS cpc_usd,
  ROUND(spend_usd / NULLIF(conversions, 0), 2)                     AS cpa_usd,
  ROUND(attributed_revenue / NULLIF(spend_usd, 0), 2)              AS roas
FROM campaign_metrics ORDER BY roas DESC;

B. Attribution & measurement

Q2 — Last-click vs DDA: which model gives higher attributed revenue?

SELECT
  atr.model_id,
  COUNT(DISTINCT atr.conversion_id)              AS conversions,
  ROUND(SUM(atr.attributed_value_usd), 2)        AS total_attributed_value,
  ROUND(AVG(atr.fractional_credit), 3)           AS avg_credit
FROM fct_attributions atr
WHERE atr.attribution_run_id = (SELECT MAX(attribution_run_id) FROM fct_attributions)
GROUP BY atr.model_id;
-- Last-click gives full credit (credit=1.0). DDA distributes — useful for diagnosing over-attribution.

C. Ranker · quality score & pCTR calibration

Q3 — Predicted vs realized pCTR per creative (calibration check)

SELECT
  i.creative_id,
  COUNT(*)                                                                   AS impressions,
  ROUND(AVG(i.predicted_pCTR), 4)                                            AS predicted_pCTR,
  ROUND(COUNT(*) FILTER (WHERE c.click_id IS NOT NULL)::DECIMAL / COUNT(*), 4) AS realized_pCTR,
  ROUND(100.0 * (COUNT(*) FILTER (WHERE c.click_id IS NOT NULL)::DECIMAL / COUNT(*) - AVG(i.predicted_pCTR))
        / AVG(i.predicted_pCTR), 1)                                          AS calibration_error_pct
FROM fct_impressions i
LEFT JOIN fct_clicks c USING (impression_id)
WHERE i.served_ts >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY i.creative_id
HAVING COUNT(*) > 1000
ORDER BY ABS(calibration_error_pct) DESC;
-- Creatives with calibration_error > 20% need ranker retraining attention.

Section 8 — Why this works

• One join key, four facts. auction_decision_id threads through impression, click, conversion. ROAS query is a 4-way join on a single column — efficient and correct.
• Re-attribution is cheap. New model = new attribution_run_id. Old runs preserved; advertisers compare side-by-side.
• Invalid clicks handled losslessly. Append-only is_invalid + invalidation_ts preserves billing history while crediting advertisers.
• Loser sampling tractable. 1% sampled with sample_weight column; ranker training works without 20× data volume.

Worked example — Nike "running shoe" auction clearing &amp; attribution

1. Auction: 5 candidates compete. ad_rank = bid × pCTR × quality:
   • Nike: $2.40 × 0.034 × 0.92 = 0.0751 ← winner
   • Asics: $2.10 × 0.029 × 0.94 = 0.0572
   • Strava: $1.80 × 0.028 × 0.81 = 0.0408
2. Second-price clearing: Nike pays just enough to beat Asics's ad_rank — at Nike's quality 0.92, that's $1.92, not $2.40. Savings of $0.48 = trust signal.
3. Impression served at 14:32:01. User clicks at 14:32:05.
4. Conversion 17 hours later: $129.99 purchase.
5. Last-click attribution (MODEL_LAST_CLICK): credit 1.0 → $129.99 attributed to this click.
6. Data-driven attribution (MODEL_DDA_v3): credit 0.62 → $80.59 attributed to this click. The remaining $49.40 attributed to upstream touchpoints.
7. Nike's ROAS under last-click = $129.99 / $1.92 = 67.7×. Under DDA = $80.59 / $1.92 = 42.0×. Both are stored side-by-side via attribution_run_id.

Why this is hard — the design tension

One human, many devices. Apple's ATT cuts user-level signals on iOS. SKAdNetwork only returns aggregated postbacks. The model must support cross-device joining via a probabilistic+deterministic identity graph, multiple attribution windows in parallel (1d / 7d / 28d × click vs view), and two privacy paths (user-level for opted-in, aggregate-only for SKAN).

Section 1 — Dimensional Model Overview

Fact Tables

• fct_ad_events — The "Touch" grain. One row per impression OR click (event-typed).
• fct_conversions — The "Conversion" grain. One row per advertiser-reported conversion.
• fct_skan_postbacks — The iOS-aggregate grain. One row per SKAdNetwork postback (no user-level data).
• fct_attributions — The "Match" grain. One row per (conversion × eligible touchpoint × window × model × run). Append-only via attribution_run_id.

Dimension & Bridge Tables

• dim_identities — One row per resolved person. Probabilistic + deterministic.
• bridge_identity_devices — SCD2. Many devices per identity; link_confidence drives weighting.
• dim_advertisers, dim_campaigns, dim_creatives — SCD2.
• dim_user_holdouts — Holdout assignment for incrementality (SCD2 per advertiser × period).

Section 2 — Logical Data Model (key columns)

bridge_identity_devices (the heart of cross-device)

fct_ad_events — event_id, event_type ∈ {impression, click}, identity_id (NULL on iOS), device_id_token, campaign_id, creative_id, placement, event_ts, privacy_path.

fct_attributions — conversion_id, ad_event_id, window_type ∈ {click_1d, click_7d, view_1d, view_28d, ...}, attribution_model, attribution_run_id, is_attributed BOOLEAN, fractional_credit DECIMAL. Partitioned by attribution_run_id.

Section 3 — How the model serves each stakeholder

Advertiser · ROAS & window comparison

• Run multiple window_type values in parallel: click_1d, click_7d, view_28d. The advertiser picks; we report all.
• Cross-device join: phone impression + laptop click + tablet conversion all join via identity_id from bridge_identity_devices.

Privacy / iOS · ATT & SKAN

• privacy_path = 'att_optout' events have NULL identity_id; only aggregate roll-ups by campaign × placement × day are valid.
• fct_skan_postbacks is a separate fact — never joins to user-level. Aggregated reporting only.

Measurement · incrementality lift

• Hold out N% of users from receiving any ad (in dim_user_holdouts). Lift = (treatment conversion rate) − (holdout conversion rate).
• The holdout group does not appear in fct_attributions at all — they're conversions without ad events.

Section 4 — Why "match at read, not at write"

The decisive design choice is to never compute attribution at ingest time. Every conversion is paired with every eligible touchpoint, and the attribution model is a function evaluated at read time. Three benefits:

• Re-attribution is a query, not a re-ingest. Switch from last-click to data-driven without rebuilding the fact table.
• Multiple windows in parallel. Same conversion appears in click_1d, click_7d, and view_28d rows simultaneously.
• Audit-ready. When an advertiser disputes attribution, you produce the exact attribution_run_id that ran on date X with model Y. Defensible.

Enhanced graphical data model — Cross-device Attribution

Section 7 — SQL analysis for business units

Sample data — 1 user (3 devices) → 3 ad events → 1 conversion

INSERT INTO dim_identities VALUES ('IDT_001','PERSON_TOK_42','login_email');

INSERT INTO bridge_identity_devices VALUES
  ('IDT_001','DEV_PHONE',  1.000,'login',        '2024-01-01',NULL),
  ('IDT_001','DEV_LAPTOP', 0.920,'household_ip', '2024-03-15',NULL),
  ('IDT_001','DEV_TABLET', 0.880,'behavioral',   '2024-06-22',NULL);

-- 3 ad events across 3 devices, same identity:
INSERT INTO fct_ad_events VALUES
  ('AE_001','impression','IDT_001','DEV_PHONE','CMP_NIKE','feed','2025-04-25 09:15:00','opted_in'),
  ('AE_002','click',     'IDT_001','DEV_LAPTOP','CMP_NIKE','feed','2025-04-28 14:32:00','opted_in'),
  ('AE_003','impression','IDT_001','DEV_TABLET','CMP_NIKE','reel','2025-04-30 21:00:00','opted_in');

-- Conversion 1 week after first impression
INSERT INTO fct_conversions VALUES
  ('CONV_777','ADV_NIKE','2025-05-02 11:42:00','purchase',129.99,'IDT_001');

-- Attribution rows for window=7d_click + last_click vs MTA
INSERT INTO fct_attributions VALUES
  ('ATR_01','CONV_777','AE_002','MODEL_LAST_CLICK','RUN_2025_05_03','click_7d',TRUE,1.000,129.99),
  ('ATR_02','CONV_777','AE_001','MODEL_MTA',       'RUN_2025_05_03','view_28d', TRUE,0.300, 38.99),
  ('ATR_03','CONV_777','AE_002','MODEL_MTA',       'RUN_2025_05_03','click_7d', TRUE,0.500, 65.00),
  ('ATR_04','CONV_777','AE_003','MODEL_MTA',       'RUN_2025_05_03','view_28d', TRUE,0.200, 26.00);

A. Advertiser · cross-device ROAS by window

Q1 — Conversions attributed by window_type (1d, 7d, 28d) under last-click

SELECT
  atr.window_type,
  COUNT(DISTINCT atr.conversion_id)         AS conversions_attributed,
  ROUND(SUM(atr.attributed_value_usd), 2)   AS attributed_value
FROM fct_attributions atr
WHERE atr.attribution_run_id = (SELECT MAX(attribution_run_id) FROM fct_attributions)
  AND atr.model_id = 'MODEL_LAST_CLICK'
  AND atr.is_attributed = TRUE
GROUP BY atr.window_type ORDER BY attributed_value DESC;
-- Lets advertiser see how many conversions fall inside each window. Picks the right window for their funnel.

Q2 — Cross-device match rate (% conversions linked to ≥1 ad event via identity)

WITH conv AS (
  SELECT conversion_id, identity_id FROM fct_conversions
  WHERE conversion_ts >= CURRENT_DATE - INTERVAL '30 days'
),
matched AS (
  SELECT DISTINCT atr.conversion_id FROM fct_attributions atr
  JOIN conv USING (conversion_id)
  WHERE atr.is_attributed = TRUE
)
SELECT
  COUNT(DISTINCT c.conversion_id) AS total_conversions,
  COUNT(DISTINCT m.conversion_id) AS matched_conversions,
  ROUND(100.0 * COUNT(DISTINCT m.conversion_id) / COUNT(DISTINCT c.conversion_id), 1) AS match_rate_pct
FROM conv c LEFT JOIN matched m USING (conversion_id);

B. Measurement · incrementality lift

Q3 — Conversion rate: treatment vs holdout

WITH treatment AS (
  SELECT i.identity_id,
    EXISTS (SELECT 1 FROM fct_conversions c WHERE c.identity_id = i.identity_id
            AND c.advertiser_id = 'ADV_NIKE'
            AND c.conversion_ts BETWEEN '2025-04-01' AND '2025-05-01') AS converted
  FROM dim_identities i
  WHERE i.identity_id NOT IN (SELECT identity_id FROM dim_user_holdouts
                              WHERE advertiser_id = 'ADV_NIKE'
                                AND period = '2025-04')
),
holdout AS (
  SELECT i.identity_id,
    EXISTS (SELECT 1 FROM fct_conversions c WHERE c.identity_id = i.identity_id
            AND c.advertiser_id = 'ADV_NIKE'
            AND c.conversion_ts BETWEEN '2025-04-01' AND '2025-05-01') AS converted
  FROM dim_identities i
  JOIN dim_user_holdouts h ON i.identity_id = h.identity_id
  WHERE h.advertiser_id = 'ADV_NIKE' AND h.period = '2025-04'
)
SELECT 'treatment' AS arm, COUNT(*) AS n, ROUND(100.0 * AVG(converted::INT), 3) AS conv_rate_pct FROM treatment
UNION ALL
SELECT 'holdout',  COUNT(*),   ROUND(100.0 * AVG(converted::INT), 3) FROM holdout;
-- Lift = treatment_rate - holdout_rate. Statistical significance = Welch's t-test on the two proportions.

Section 8 — Why this works

• Identity graph as a first-class fact. bridge_identity_devices SCD2 with confidence is how cross-device works without lying about certainty.
• Match at read. Multiple windows + multiple models computed in parallel; advertiser picks at query time.
• iOS / SKAN carve-out. Privacy path is explicit; aggregate-only queries fall back to fct_skan_postbacks.
• Incrementality real. Holdout assignment is a separate dim, not derived — survives campaign re-orgs.

Worked example — User IDT_001 attribution math

1. User IDT_001 sees Nike impression on phone (Apr 25), clicks Nike ad on laptop (Apr 28), sees Nike Reels on tablet (Apr 30).
2. Buys Nike shoes for $129.99 on May 2.
3. Last-click (click_7d window): credit 1.0 → all $129.99 attributed to AE_002 (the laptop click).
4. MTA model (multi-touch): AE_001 view 30%, AE_002 click 50%, AE_003 view 20% → $38.99 + $65.00 + $26.00 = $129.99 ✓
5. Identity bridge enabled the cross-device join; without it the 3 events wouldn't connect. link_confidence on each device drives MTA weighting in advanced models.
6. Both attribution rows live in fct_attributions with attribution_run_id = RUN_2025_05_03. Switching models = SELECT, not re-ingest.

Why this is hard — the design tension

CTV inventory has a contractual layer that programmatic doesn't: guaranteed buys with monetary penalties for under-delivery. Three pressures pull simultaneously: (1) maximize fill rate, (2) honor frequency caps so users don't see the same ad 8 times, (3) hit committed impressions per advertiser per month. Modeling unfilled opportunities (not just impressions) is the senior move — it's the only way to answer "why did we leave money on the table?"

Section 1 — Dimensional Model Overview

Fact Tables

• fct_ad_opportunities — The "Slot" grain. One row per ad-slot opportunity in a viewing session. Filled or unfilled — both kept.
• fct_ad_impressions — The "Served" grain. Subset of opportunities where an ad actually played.
• fct_pacing_daily — The "Contract" grain. One row per (campaign × day) aggregating delivered vs committed.
• fct_frequency_state — The "Cap" grain. One row per (profile × campaign × day) tracking impressions toward cap.
• fct_makegood_obligations — The "Owed" grain. One row per (campaign × month) when committed not met. Drives next-month bonus inventory.

Dimension Tables

• dim_advertisers, dim_campaigns, dim_creatives (SCD2)
• dim_campaign_flights — Period a campaign runs (start_date / end_date / total_committed_impressions)
• dim_titles (SCD2 for content metadata; ratings drive ad eligibility)
• dim_profiles (SCD2; subscription tier = ad-supported / premium)

Section 2 — Logical Data Model (key columns)

fct_ad_opportunities — opportunity_id, profile_id, title_id, session_id, slot_type ∈ {pre_roll, mid_roll, post_roll}, slot_position_sec, slot_duration_sec, opportunity_ts, predicted_cpm, served_creative_id (NULL=unfilled), fill_reason {won_auction, guaranteed_buy, house_ad}, unfilled_reason {frequency_capped, no_eligible_demand, content_unsuitable}.

fct_pacing_daily — campaign_id, day, delivered, committed_today, cumulative_delivered, total_committed, pacing_index (1.0 = on track), days_remaining.

fct_makegood_obligations — campaign_id, original_period, makegood_period, owed_impressions, owed_value_usd, status {pending, scheduled, fulfilled}.

Section 3 — How the model serves each stakeholder

Ad server · live decisions — for each opportunity, query fct_frequency_state + fct_pacing_daily + active campaign list. Pick a creative or mark unfilled.

Account managers · advertiser pacing — track pacing_index per campaign daily. If <0.95 with <5 days left, increase delivery priority.

Finance · make-good bookkeeping — fct_makegood_obligations tracks dollars owed when committed impressions not met. Liability on the balance sheet until fulfilled.

Exec · "money on the table" analysis — count fct_ad_opportunities with served_creative_id IS NULL grouped by unfilled_reason. Tells you if the bottleneck is demand (sales team) or eligibility rules (ops team).

Section 4 — Why model unfilled opportunities

• Without unfilled rows: can't compute fill rate. Can't tell if you under-delivered because of low demand vs frequency caps vs content unsuitability.
• With unfilled rows: fill rate = COUNT(filled) / COUNT(total opportunities). Drill into unfilled_reason to find the right team to fix.
• Cost: doubles the row count. Tradeoff is worth it; partition by date keeps queries fast.

Enhanced graphical data model — Netflix CTV Inventory

Section 7 — SQL analysis

Sample data — 1 campaign, 5 opportunities (3 filled / 2 unfilled), 1 day pacing

INSERT INTO dim_campaigns VALUES ('CMP_FORD','ADV_FORD','2025-05-01','2025-05-31',5000000,1.0);

INSERT INTO fct_ad_opportunities VALUES
  ('OPP_001','PROF_A','TITLE_X','SESS_1','pre_roll',  0,  15, 25.50, 'CRT_FORD_15S', 'guaranteed_buy', NULL,                  '2025-05-01 19:32:00'),
  ('OPP_002','PROF_A','TITLE_X','SESS_1','mid_roll',1200, 15, 25.50, NULL,            NULL,             'frequency_capped',     '2025-05-01 19:52:00'),
  ('OPP_003','PROF_B','TITLE_Y','SESS_2','pre_roll',  0,  15, 25.50, 'CRT_FORD_15S', 'guaranteed_buy', NULL,                  '2025-05-01 20:05:00'),
  ('OPP_004','PROF_C','TITLE_Z','SESS_3','pre_roll',  0,  30, 25.50, NULL,            NULL,             'no_eligible_demand',   '2025-05-01 20:22:00'),
  ('OPP_005','PROF_D','TITLE_X','SESS_4','mid_roll',1500, 15, 25.50, 'CRT_FORD_15S', 'guaranteed_buy', NULL,                  '2025-05-01 21:00:00');

-- Pacing for the day:
INSERT INTO fct_pacing_daily VALUES ('CMP_FORD','2025-05-01', 3, 161290, 3, 5000000, 0.000, 30);
-- Way under: should have delivered 161290 today, only got 3.

A. Operations · fill rate & unfilled diagnostics

Q1 — Fill rate & unfilled-reason breakdown (the "money on the table" report)

SELECT
  slot_type,
  COUNT(*)                                                              AS opportunities,
  COUNT(*) FILTER (WHERE served_creative_id IS NOT NULL)                AS filled,
  ROUND(100.0 * COUNT(*) FILTER (WHERE served_creative_id IS NOT NULL)
        / COUNT(*), 1)                                                  AS fill_rate_pct,
  COUNT(*) FILTER (WHERE unfilled_reason = 'frequency_capped')          AS cap_blocked,
  COUNT(*) FILTER (WHERE unfilled_reason = 'no_eligible_demand')        AS no_demand,
  COUNT(*) FILTER (WHERE unfilled_reason = 'content_unsuitable')        AS content_block
FROM fct_ad_opportunities
WHERE DATE(opportunity_ts) = '2025-05-01'
GROUP BY slot_type ORDER BY opportunities DESC;
-- "no_eligible_demand" rows = sales team to fix. "frequency_capped" = ops/cap-policy team.

B. Account managers · pacing alerts

Q2 — Campaigns at risk of under-delivery (pacing < 0.95 with <5 days remaining)

SELECT p.campaign_id, c.advertiser_id,
  p.cumulative_delivered, p.total_committed,
  ROUND(100.0 * p.cumulative_delivered / NULLIF(p.total_committed, 0), 1) AS pct_delivered,
  p.pacing_index, p.days_remaining,
  ROUND((p.total_committed - p.cumulative_delivered) / NULLIF(p.days_remaining, 0)) AS daily_needed
FROM fct_pacing_daily p JOIN dim_campaigns c USING (campaign_id)
WHERE p.day = CURRENT_DATE
  AND p.pacing_index < 0.95
  AND p.days_remaining < 5
ORDER BY p.pacing_index ASC;
-- Triggers ad-server priority bump + account-manager Slack alert.

C. Finance · make-good liability

Q3 — Pending make-good liability ($) by advertiser

SELECT
  c.advertiser_id,
  COUNT(DISTINCT mg.campaign_id)                AS campaigns_owed,
  SUM(mg.owed_impressions)                      AS total_impressions_owed,
  ROUND(SUM(mg.owed_value_usd), 2)              AS total_dollars_owed,
  COUNT(*) FILTER (WHERE mg.status = 'pending') AS pending_count
FROM fct_makegood_obligations mg
JOIN dim_campaigns c USING (campaign_id)
WHERE mg.status IN ('pending','scheduled')
GROUP BY c.advertiser_id
ORDER BY total_dollars_owed DESC;
-- Books a liability on Netflix's balance sheet until fulfilled.

Section 8 — Why this works

• Unfilled rows are first-class. Fill rate becomes a simple COUNT FILTER, not a derived metric.
• Pacing as a daily snapshot. Not derived on the fly — pre-aggregated for fast dashboard reads.
• Make-goods as a liability fact. Finance owns it; it's a real balance-sheet item, not a metric.
• Frequency caps as state. Per (profile × campaign × day) — exactly what the ad server queries pre-decision.

Worked example — CMP_FORD on May 1

1. Total committed: 5,000,000 impressions over May. Daily target: 161,290.
2. Day 1 delivered: 3 impressions (out of 5 opportunities). 2 unfilled — 1 frequency_capped, 1 no_eligible_demand.
3. pacing_index = 3 / 161,290 = 0.0000186 — catastrophic miss.
4. If pattern continues 30 days: ~90 delivered total. Make-good owed = (5M − 90) × CPM-based dollar rate.
5. Queries trigger: account manager alert (Q2), finance liability booking (Q3), unfilled-reason audit (Q1) reveals "no_eligible_demand" needs sales-team push.

Why this is hard — the design tension

Returns reshape revenue retroactively. Multi-warehouse fulfillment splits a single SKU across two warehouses. Seller-of-record (1P retail / 2P FBA / 3P marketplace) determines whether Amazon books GMV or commission. And inventory has to reconcile to ledger every night. The model must be append-only on the financial side and movement-based on the physical side — same product, two grains, one source of truth.

Section 1 — Dimensional Model Overview

Fact Tables

• fct_order_lines — The "Sale" grain. One row per (order × product × source_warehouse). 3-item order = 3 rows.
• fct_returns — The "Refund" grain. One row per return event, line-level. Append-only.
• fct_inventory_movements — The "Stock-flow" grain. One row per stock change (in / out / transfer / damage / adjustment).
• snap_inventory_daily — The "State" grain. Daily snapshot per (product × warehouse).
• fct_replacements — The "Linked-order" grain. Maps replacement orders back to their original.

Dimension Tables

• dim_products (SCD2 — title, category, dimensions drift)
• dim_sellers (SCD2 — seller_type ∈ {retail_1p, fba_2p, marketplace_3p}, recognized_as ∈ {gmv, commission_only})
• dim_warehouses, dim_customers + dim_addresses (SCD2), dim_promotions (SCD2)

Section 2 — Logical Data Model (key columns)

fct_order_lines — order_line_id, order_id, line_number, customer_id, product_id, seller_id, source_warehouse_id, ordered_ts, shipped_ts, delivered_ts, quantity, unit_price_usd, promo_discount, tax, shipping, gross_revenue_usd, is_prime, is_subscribe_save.

fct_returns — return_id, order_line_id, return_ts, refund_ts, reason_code, quantity (partial OK), refund_amount_usd, restocking_fee_usd, condition_received {new, used, damaged}, is_replacement BOOL.

fct_inventory_movements — movement_id, product_id, warehouse_id, movement_ts, movement_type {receipt, sale_out, return_in, transfer_out, transfer_in, damage, adjustment}, quantity (signed), unit_cost_usd, related_order_line_id (NULL for non-sale moves).

snap_inventory_daily — product_id, warehouse_id, snapshot_date, opening_qty, receipts, sales, returns_in, transfers_net, damage, ending_qty, weighted_avg_cost. Reconciliation invariant: opening + receipts + returns_in − sales − damage + transfers_net = ending.

Section 3 — How the model serves each stakeholder

Customer experience — order delivery SLA from fct_order_lines (ordered → delivered). Return rate by category from fct_returns tracks product quality.

Finance — net revenue = SUM(order_lines.gross_revenue_usd) − SUM(returns.refund_amount_usd). For 3P sellers, dim_sellers.recognized_as = 'commission_only' means only the commission is booked.

Operations — multi-warehouse fill rate, transfer efficiency, days of inventory on hand. Reconciliation runs nightly: snap_inventory_daily ending_qty must match SUM(fct_inventory_movements) since opening.

Section 4 — Why append-only returns + movement-based inventory

• Returns reshape historical revenue. Never UPDATE fct_order_lines. Append fct_returns. Finance can restate any historical period correctly.
• Inventory snapshots derive from movements. Snapshots are pre-aggregated for fast read; movements are the immutable source. Lose a snapshot, rebuild from movements.
• Replacements are linked, not collapsed. Original order stays + replacement is a new row + return matches both. Triple-counted at gross level, nets to zero correctly.
• 3P seller revenue carve-out. dim_sellers.recognized_as drives whether SUM is GMV or commission — single column makes the rule explicit and queryable.

Enhanced graphical data model — Amazon Orders + Returns + Inventory

Section 7 — SQL analysis

Sample data — 1 order with 2 line items (one split across 2 warehouses), 1 partial return

INSERT INTO dim_sellers VALUES
  ('SLR_AMZN','retail_1p','gmv', 0.0),
  ('SLR_3P_ABC','marketplace_3p','commission_only', 0.15);

-- 3-item order; line 2 split across 2 warehouses (qty 4 from WH-A, qty 2 from WH-B)
INSERT INTO fct_order_lines VALUES
  ('OL_001','ORD_99', 1, 'CUST_X','PROD_BOOK', 'SLR_AMZN','WH-A','2025-05-01 10:00','2025-05-02 14:00','2025-05-03 16:00', 1, 19.99, 0.00, 1.65, 0.00, 1.0, 21.64, TRUE, FALSE),
  ('OL_002','ORD_99', 2, 'CUST_X','PROD_PEN',  'SLR_3P_ABC','WH-A','2025-05-01 10:00','2025-05-02 14:00','2025-05-03 16:00', 4,  3.50, 0.00, 1.16, 0.00, 1.0, 15.16, TRUE, FALSE),
  ('OL_003','ORD_99', 2, 'CUST_X','PROD_PEN',  'SLR_3P_ABC','WH-B','2025-05-01 10:00','2025-05-02 14:00','2025-05-03 16:00', 2,  3.50, 0.00, 0.58, 0.00, 1.0,  7.58, TRUE, FALSE);

-- Partial return: customer keeps the book, returns 3 of 6 pens
INSERT INTO fct_returns VALUES
  ('RET_001','OL_002','2025-05-08 09:00','2025-05-08 09:30','quality_issue', 3, 11.37, 0.00, 'used', FALSE);

A. Customer · order funnel + delivery SLA

Q1 — Prime vs non-Prime delivery time (last 30 days)

SELECT
  is_prime,
  COUNT(*)                                                                       AS lines,
  ROUND(AVG(EXTRACT(EPOCH FROM (delivered_ts - ordered_ts))/3600), 1)             AS avg_hours,
  ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (delivered_ts - ordered_ts))/3600), 1) AS p95_hours
FROM fct_order_lines
WHERE delivered_ts IS NOT NULL AND ordered_ts >= CURRENT_DATE - 30
GROUP BY is_prime;

B. Finance · net revenue with 1P/3P carve-out

Q2 — Net revenue with seller-type carve-out (3P books only commission)

WITH gross AS (
  SELECT
    s.seller_type,
    s.recognized_as,
    SUM(CASE WHEN s.recognized_as = 'gmv' THEN ol.gross_revenue_usd
             ELSE ol.gross_revenue_usd * s.commission_pct END) AS gross_recognized_usd
  FROM fct_order_lines ol JOIN dim_sellers s USING (seller_id)
  WHERE DATE(ol.ordered_ts) = '2025-05-01'
  GROUP BY s.seller_type, s.recognized_as
),
returns AS (
  SELECT
    s.seller_type,
    SUM(CASE WHEN s.recognized_as = 'gmv' THEN r.refund_amount_usd
             ELSE r.refund_amount_usd * s.commission_pct END) AS refund_recognized_usd
  FROM fct_returns r
  JOIN fct_order_lines ol ON r.order_line_id = ol.order_line_id
  JOIN dim_sellers s USING (seller_id)
  WHERE DATE(r.return_ts) = '2025-05-01'
  GROUP BY s.seller_type
)
SELECT g.seller_type,
  ROUND(g.gross_recognized_usd, 2)                                                  AS gross_revenue,
  ROUND(COALESCE(r.refund_recognized_usd, 0), 2)                                    AS refunds,
  ROUND(g.gross_recognized_usd - COALESCE(r.refund_recognized_usd, 0), 2)           AS net_revenue
FROM gross g LEFT JOIN returns r USING (seller_type);

C. Operations · inventory reconciliation

Q3 — Daily inventory invariant check (snap = opening + receipts + returns_in − sales − damage + transfers)

SELECT
  product_id, warehouse_id, snapshot_date,
  opening_qty, receipts, returns_in, sales, damage, transfers_net, ending_qty,
  (opening_qty + receipts + returns_in - sales - damage + transfers_net) AS computed_ending,
  ending_qty - (opening_qty + receipts + returns_in - sales - damage + transfers_net) AS drift,
  CASE WHEN ending_qty = opening_qty + receipts + returns_in - sales - damage + transfers_net
       THEN 'PASS' ELSE 'FAIL' END AS reconciliation
FROM snap_inventory_daily
WHERE snapshot_date = CURRENT_DATE - 1
  AND ending_qty != opening_qty + receipts + returns_in - sales - damage + transfers_net;
-- Empty result = clean. Non-empty = inventory adjustment needed; investigate.

Section 8 — Why this works

• Append-only returns. Net revenue = SUM(orders) − SUM(returns). No UPDATE; full historical audit.
• Movements are immutable. Snapshots derive from movements; rebuild at any time.
• Seller-type carve-out is a column. 1P / 2P / 3P revenue rules become a CASE in the query, not a separate ETL path.
• Multi-warehouse split is a row. Same order_id + line_number, different warehouse → fulfilment is queryable without losing the customer's "1 line item" mental model.

Worked example — Order ORD_99 with split fulfillment + partial return

1. Order: 1 book ($21.64) + 6 pens. Pens split across 2 warehouses: 4 from WH-A ($15.16) + 2 from WH-B ($7.58) = 6 pens for $22.74. Total order gross = $44.38.
2. Seller-type effects on revenue: Book is 1P retail → Amazon books $21.64 GMV. Pens are 3P seller (15% commission) → Amazon books $22.74 × 0.15 = $3.41. Total Amazon recognized = $21.64 + $3.41 = $25.05.
3. Partial return: 3 of 6 pens returned a week later. Refund = $11.37 (proportional). Amazon's recognized refund = $11.37 × 0.15 = $1.71.
4. Net Amazon recognized: $25.05 − $1.71 = $23.34.
5. Inventory: WH-A movements record sale_out (+4 pens out, then return_in +1 pen back). WH-B records sale_out (-2 pens). Daily snapshot reconciles.

Why this is hard — the design tension

Volume alone forces design choices: 100B impressions/day means exact COUNT DISTINCT is unaffordable; HLL is mandatory. Mutable engagements (like → unlike) demand append-only with is_undone flags. Heartbeat-driven dwell can't live at raw grain — must be aggregated at ETL. And the ranker A/B requirement means every impression must carry the model_id that ranked it, or you can't compare baseline lift.

Section 1 — Dimensional Model Overview

Fact Tables

• fct_impressions — The "Served" grain. One row per (viewer × post × impression). Carries ranker_model_id + predicted_score.
• fct_engagements — The "Reaction" grain. One row per (viewer × post × event_type × event_ts). Likes, saves, comments, shares, hides, reports — all here.
• fct_dwell_per_impression — The "Time-on-post" grain. One row per impression with total dwell_ms (aggregated from heartbeats at ETL).
• fct_ranker_experiments — The "Treatment" grain. One row per (viewer × experiment × variant). Drives A/B comparison.

Dimension Tables

• dim_users (SCD2) · dim_posts (SCD2 — caption edits, audio swaps) · dim_creators (SCD2)
• dim_post_audio (SCD1)
• dim_ranker_models — SCD2; semver + model_card_url + deployed_pct.

Section 2 — Logical Data Model (key columns)

fct_impressions — impression_id, viewer_id, post_id, feed_position SMALLINT, impression_ts, device_id, ranker_model_id (FK), predicted_score DECIMAL(8,6), surface ∈ {feed, reels, stories, explore}.

fct_engagements — engagement_id, impression_id (NULLable: comments can arrive without prior impression event), viewer_id, post_id, event_type ∈ {like, save, share, comment, hide, report, follow_creator}, event_ts, is_undone BOOLEAN, undone_ts. Append-only — never delete an unlike.

fct_dwell_per_impression — impression_id, dwell_ms INT, was_full_view BOOLEAN, viewport_pct DECIMAL. Aggregated from raw 250ms heartbeats at ETL — saves 100× rows.

fct_ranker_experiments — viewer_id, experiment_id, variant_id, assigned_ts, exit_ts. Bridges viewers to A/B-test arms.

Section 3 — How the model serves each stakeholder

Ranker team · model lift — Compare engagement-rate per impression for ranker_model_id = v3.1 vs v3.2. Positive lift on like-rate but watch hide-rate; both should move favorably.

Creator analytics — Reach (impressions), engagement rate (engagements / impressions), dwell distribution per post.

Brand safety — Hide+Report rate per post per hour. Posts crossing thresholds get demoted; spike → human review queue.

Product growth — Negative-engagement rate is the canary for "this content drives session abandonment".

Section 4 — Why ranker_model_id on every impression + append-only engagements

• Ranker A/B requires the model on the row. Without it, a 1% holdout for the new model can't be compared to the 99% baseline. ranker_model_id is a first-class dimension, not a footnote.
• Likes are mutable, but the fact is immutable. A like at T1 + unlike at T2 = 2 rows: an insert and an "undone" insert with is_undone=TRUE, undone_ts=T2. Never DELETE; preserves time-series of toggle behavior.
• Dwell aggregated at ETL. 250ms heartbeats × 100B impressions × 30 sec average = ~12 trillion raw rows/day. Aggregate before warehouse — only 100B per-impression rows survive.
• Approximate aggregations. APPROX_COUNT_DISTINCT + HLL for everything user-level. Exact only when finance demands it (rare).

Enhanced graphical data model — Instagram Feed Engagement

Section 7 — SQL analysis

Sample data — 1 viewer sees 3 posts under v3.2 ranker; engages with two

INSERT INTO fct_impressions VALUES
  ('IMP_A','U_42','POST_001', 1,'2025-05-01 09:00:00','dev_iphone','model_v3.2',0.72,'feed'),
  ('IMP_B','U_42','POST_002', 2,'2025-05-01 09:00:08','dev_iphone','model_v3.2',0.55,'feed'),
  ('IMP_C','U_42','POST_003', 3,'2025-05-01 09:00:15','dev_iphone','model_v3.2',0.41,'feed');

INSERT INTO fct_dwell_per_impression VALUES
  ('IMP_A',  4200, 1.00, TRUE),   -- 4.2s, full view
  ('IMP_B',   800, 0.40, FALSE),  -- 0.8s, partial — scrolled past
  ('IMP_C',  6500, 1.00, TRUE);   -- 6.5s, full view

INSERT INTO fct_engagements VALUES
  ('ENG_1','IMP_A','U_42','POST_001','like',   '2025-05-01 09:00:04', FALSE, NULL),
  ('ENG_2','IMP_C','U_42','POST_003','save',   '2025-05-01 09:00:21', FALSE, NULL),
  ('ENG_3','IMP_C','U_42','POST_003','comment','2025-05-01 09:01:00', FALSE, NULL),
  ('ENG_4','IMP_A','U_42','POST_001','like',   '2025-05-01 09:05:00', TRUE,  '2025-05-01 09:05:00');  -- unlike

A. Creator analytics · post engagement

Q1 — Like / save / share rate per post (with active engagements only)

WITH active_engagements AS (
  SELECT post_id, event_type FROM fct_engagements WHERE is_undone = FALSE
)
SELECT
  i.post_id,
  COUNT(*)                                                        AS impressions,
  APPROX_COUNT_DISTINCT(i.viewer_id)                              AS unique_viewers,
  COUNT(*) FILTER (WHERE e.event_type = 'like')                   AS likes,
  COUNT(*) FILTER (WHERE e.event_type = 'save')                   AS saves,
  COUNT(*) FILTER (WHERE e.event_type = 'share')                  AS shares,
  ROUND(100.0 * COUNT(*) FILTER (WHERE e.event_type='like') / COUNT(*), 2) AS like_rate_pct,
  ROUND(AVG(d.dwell_ms), 0)                                       AS avg_dwell_ms
FROM fct_impressions i
LEFT JOIN active_engagements e ON e.post_id = i.post_id
LEFT JOIN fct_dwell_per_impression d ON d.impression_id = i.impression_id
GROUP BY i.post_id ORDER BY like_rate_pct DESC;

B. Brand safety · negative-signal monitoring

Q2 — Posts with hide+report rate above brand-safety threshold

SELECT
  i.post_id,
  COUNT(*)                                                                 AS impressions,
  COUNT(*) FILTER (WHERE e.event_type IN ('hide','report'))                AS negative_signals,
  ROUND(100.0 * COUNT(*) FILTER (WHERE e.event_type IN ('hide','report'))
        / COUNT(*), 3)                                                     AS negative_rate_pct
FROM fct_impressions i
LEFT JOIN fct_engagements e ON e.impression_id = i.impression_id AND e.is_undone = FALSE
WHERE DATE(i.impression_ts) = CURRENT_DATE - 1
GROUP BY i.post_id
HAVING COUNT(*) > 1000
   AND COUNT(*) FILTER (WHERE e.event_type IN ('hide','report')) * 1.0 / COUNT(*) > 0.005
ORDER BY negative_rate_pct DESC;
-- Posts above 0.5% hide+report rate go to human moderation queue.

C. Ranker · A/B model lift

Q3 — Engagement-rate lift v3.2 over v3.1

WITH ranker_metrics AS (
  SELECT
    i.ranker_model_id,
    COUNT(*)                                                    AS impressions,
    COUNT(*) FILTER (WHERE e.event_type='like'  AND e.is_undone=FALSE) AS likes,
    COUNT(*) FILTER (WHERE e.event_type='save'  AND e.is_undone=FALSE) AS saves,
    COUNT(*) FILTER (WHERE e.event_type IN ('hide','report'))    AS negatives,
    AVG(d.dwell_ms)                                             AS avg_dwell_ms
  FROM fct_impressions i
  LEFT JOIN fct_engagements        e ON e.impression_id = i.impression_id
  LEFT JOIN fct_dwell_per_impression d ON d.impression_id = i.impression_id
  WHERE i.impression_ts >= CURRENT_DATE - 7
    AND i.ranker_model_id IN ('model_v3.1','model_v3.2')
  GROUP BY i.ranker_model_id
)
SELECT ranker_model_id,
  ROUND(100.0 * likes     / NULLIF(impressions, 0), 3) AS like_rate_pct,
  ROUND(100.0 * saves     / NULLIF(impressions, 0), 3) AS save_rate_pct,
  ROUND(100.0 * negatives / NULLIF(impressions, 0), 3) AS negative_rate_pct,
  ROUND(avg_dwell_ms, 0)                                AS avg_dwell_ms
FROM ranker_metrics;
-- v3.2 wins if like_rate higher AND negative_rate not worse.

Section 8 — Why this works

• Ranker A/B is queryable. ranker_model_id on every impression → comparing v3.1 vs v3.2 is a GROUP BY, not a separate ETL pipeline.
• Mutable engagements without losing history. Append-only with is_undone preserves the full toggle behavior — useful for "regret rate" analysis.
• Dwell aggregated at ETL. 100B per-impression rows >> 12T per-heartbeat rows. The aggregation is non-negotiable.
• HLL by default. Distinct viewer counts at petabyte scale require APPROX_COUNT_DISTINCT — exact only when finance demands it.

Worked example — viewer U_42's feed session

1. 3 posts impressed under model_v3.2. Predicted scores: 0.72, 0.55, 0.41. Ranker order = score-desc, correct.
2. POST_001 (score 0.72): 4.2s dwell, like at +4s, full view → strong positive.
3. POST_002 (score 0.55): 0.8s dwell, no engagement, partial view (40%) → user scrolled past. Ranker over-predicted.
4. POST_003 (score 0.41): 6.5s dwell, save + comment → strong positive. Ranker under-predicted.
5. Calibration error on this session: 1/3 (POST_002) too high, 1/3 (POST_003) too low. Logged in fct_impressions.predicted_score for ranker training.
6. Mutable like: User unlikes POST_001 5 minutes later. fct_engagements stores both events (ENG_1 like, ENG_4 the unlike with is_undone=TRUE). Final state: not liked. Active-only queries filter is_undone=FALSE.

Why this is hard — the design tension

Two consumers want the same fact for opposite reasons. Product needs every play (skips included) for recommendations. Finance only pays on qualified streams (≥30s). Multi-rights splits mean one stream produces 4+ payout rows (artist + label + publisher + songwriter) and the splits change mid-quarter when deals renegotiate. Pool model math requires computing per-stream rate as revenue ÷ total streams in pool — a chicken-and-egg dependency that resolves only at period close.

Section 1 — Dimensional Model Overview

Fact Tables

• fct_play_events — The "Listen" grain. One row per play event with qualified_play flag.
• fct_royalty_periods — The "Pool" grain. One row per (country × period). Holds total pool revenue, total qualified streams, computed per-stream rate.
• fct_royalty_attributions — The "Payout" grain. One row per (play × rights_holder × period). Derived nightly at period close.

Dimension & Bridge Tables

• dim_users (SCD2 — plan tier drifts) · dim_subscriptions (Family/Duo group of users)
• dim_tracks (SCD2 — metadata) · dim_artists · dim_labels (SCD2) · dim_publishers (SCD2) · dim_songwriters
• bridge_track_rights — SCD2 with effective dates. The deal-terms ledger.

Section 2 — Logical Data Model (key columns)

fct_play_events — play_id, user_id, track_id, played_ts, duration_played_sec, qualified_play BOOL (≥30s), context_type ∈ {playlist, algorithmic, search, radio}, source_playlist_id, device_id, country_id.

bridge_track_rights (SCD2) — track_id, rights_holder_id, rights_type ∈ {artist, label, publisher, songwriter}, share_pct, effective_from, effective_to. Sum of share_pct per (track, rights_type) per period = 100%.

fct_royalty_periods — period_id, country_id, period_start, period_end, total_pool_revenue_usd, total_qualified_plays, per_stream_rate_usd (= revenue / qualified_plays).

fct_royalty_attributions — play_id, period_id, rights_holder_id, rights_type, share_pct, attributed_payout_usd.

Section 3 — How the model serves each stakeholder

Product · recommendations + Year in Review — every play counts (qualified or not). Skips are signal. context_type tells the recommender which surface drove the play.

Finance · royalty payouts — quarterly close: compute pool revenue per country, count qualified plays, derive rate, generate fct_royalty_attributions. Splits use as-of-play bridge rows, never current.

Rights holders · transparency — labels/publishers want their cut breakdown. Each fct_royalty_attributions row is a defensible per-play line item.

Section 4 — Why the bridge is SCD2 (not just a table)

Label deals renegotiate mid-quarter — a label might move 5% from publisher to artist effective Apr 15. Three failure modes if the bridge isn't SCD2:

• Apr 1-14 plays paid under the wrong split. Restatement needed every quarter — auditor nightmare.
• Lawsuit risk. Songwriters can demand "pay me as of the deal that was active when my song was streamed". SCD2 is the only defensible answer.
• Forecast invalidation. Finance forecasts use the current bridge; without SCD2, mid-quarter changes silently invalidate projections.

The contract: every fct_royalty_attributions row uses bridge_track_rights WHERE play.played_ts BETWEEN bridge.effective_from AND bridge.effective_to.

Enhanced graphical data model — Spotify Listening + Royalty Pool

Section 7 — SQL analysis

Sample data — Country pool with 1M streams in Q2-2025

INSERT INTO fct_royalty_periods VALUES
  ('PER_2025Q2_US','US','2025-04-01','2025-06-30', 5000000.00, 1000000, 0.005000);
-- Per-stream rate = $5M / 1M plays = $0.005 / qualified play

INSERT INTO bridge_track_rights VALUES
  ('TRK_001','LBL_BIG',     'label',     0.50,'2024-01-01','2025-04-15'),
  ('TRK_001','ART_NOVA',    'artist',    0.30,'2024-01-01','2025-04-15'),
  ('TRK_001','PUB_HARMONY', 'publisher', 0.15,'2024-01-01','2025-04-15'),
  ('TRK_001','SW_LEE',      'songwriter',0.05,'2024-01-01','2025-04-15'),
  -- Mid-quarter renegotiation: label gives up 5%, artist gains 5%
  ('TRK_001','LBL_BIG',     'label',     0.45,'2025-04-15','9999-12-31'),
  ('TRK_001','ART_NOVA',    'artist',    0.35,'2025-04-15','9999-12-31'),
  ('TRK_001','PUB_HARMONY', 'publisher', 0.15,'2025-04-15','9999-12-31'),
  ('TRK_001','SW_LEE',      'songwriter',0.05,'2025-04-15','9999-12-31');

-- 2 plays of TRK_001 in US: one before, one after the renegotiation
INSERT INTO fct_play_events VALUES
  ('PLAY_A','U_42','TRK_001','2025-04-10 14:00:00', 180, TRUE,  'algorithmic', NULL,'iphone','US'),
  ('PLAY_B','U_42','TRK_001','2025-05-20 09:30:00', 240, TRUE,  'playlist','PL_ROCK','iphone','US');

A. Finance · per-stream rate & pool reconciliation

Q1 — Per-stream rate per country per period

SELECT
  rp.country_id, rp.period_start, rp.period_end,
  rp.total_qualified_plays,
  ROUND(rp.total_pool_revenue_usd, 2)   AS pool_usd,
  ROUND(rp.per_stream_rate_usd, 6)      AS rate_per_stream
FROM fct_royalty_periods rp
WHERE rp.period_start = '2025-04-01'
ORDER BY rp.country_id;

Q2 — Royalty attribution per qualified play (using as-of-played bridge splits)

SELECT
  p.play_id,
  p.played_ts,
  b.rights_type,
  b.rights_holder_id,
  b.share_pct,
  rp.per_stream_rate_usd,
  ROUND(rp.per_stream_rate_usd * b.share_pct, 6) AS attributed_payout_usd
FROM fct_play_events p
JOIN dim_countries  c  ON p.country_id = c.country_id
JOIN fct_royalty_periods rp
  ON rp.country_id = p.country_id
 AND p.played_ts BETWEEN rp.period_start AND rp.period_end
JOIN bridge_track_rights b
  ON b.track_id = p.track_id
 AND p.played_ts >= b.effective_from
 AND p.played_ts <  b.effective_to                 -- as-of-play splits
WHERE p.qualified_play = TRUE AND p.track_id = 'TRK_001'
ORDER BY p.played_ts, b.rights_type;

B. Product · listening behavior

Q3 — Skip rate by context_type (algorithmic vs playlist vs radio)

SELECT context_type,
  COUNT(*)                                                         AS plays,
  COUNT(*) FILTER (WHERE qualified_play = FALSE)                   AS skips,
  ROUND(100.0 * COUNT(*) FILTER (WHERE qualified_play = FALSE)
        / COUNT(*), 2)                                             AS skip_rate_pct
FROM fct_play_events
WHERE played_ts >= CURRENT_DATE - 30
GROUP BY context_type ORDER BY skip_rate_pct DESC;
-- High skip rate on algorithmic = recommender quality issue. On radio = expected (lean-back).

Section 8 — Why this works

• One play, two consumers. qualified_play as a flag (not a separate table) keeps Product and Finance on the same source of truth.
• Pool model derived at period close. Per-stream rate isn't real-time — it's only known when the period closes. Modeled as a fact table, not a daily rolling number.
• SCD2 bridge keeps deals defensible. Mid-quarter renegotiations don't break historical payouts.
• Attribution table grows linearly. ~4 rows per qualified play. Quarterly bulk-write at period close, then read-only.

Worked example — TRK_001 royalty math (before vs after renegotiation)

1. Pool: $5,000,000 in US for Q2-2025. 1,000,000 qualified streams. Per-stream rate = $0.005.
2. PLAY_A (Apr 10): uses pre-renegotiation splits. Label 50% / Artist 30% / Publisher 15% / Songwriter 5%.
   • Label: $0.005 × 0.50 = $0.0025
   • Artist: $0.005 × 0.30 = $0.0015
   • Publisher: $0.005 × 0.15 = $0.00075
   • Songwriter: $0.005 × 0.05 = $0.00025
   • Sum: $0.005 ✓
3. PLAY_B (May 20): uses post-renegotiation splits. Label 45% / Artist 35% / Publisher 15% / Songwriter 5%.
   • Label: $0.005 × 0.45 = $0.00225
   • Artist: $0.005 × 0.35 = $0.00175
   • (others unchanged)
   • Sum: $0.005 ✓
4. Audit query produces 8 rows total (2 plays × 4 rights holders). Each is defensible: shows the exact bridge row that was active when the play occurred.

Why this is hard — the design tension

Payments is the domain with zero tolerance for retro edits. A regulator can ask "what was merchant X's balance on day Y as the books showed it then?" — the answer has to be defensible regardless of corrections made since. That forces append-only on every fact. Multi-currency adds an FX gain/loss leg. Chargebacks arrive 30-180 days after the charge. Reserves are an internal transfer, not external money. The model must enforce SUM=0 per balance transaction per currency as a continuous invariant.

Section 1 — Dimensional Model Overview

Fact Tables

• fct_ledger_entries — The "Atom" grain. One row per debit OR credit on (account, currency). The source of truth. Append-only.
• fct_balance_transactions — The "Business event" grain. One row per charge/refund/payout/chargeback. Groups its entries via balance_txn_id.
• snap_account_balance_daily — The "State" grain. Derived — running SUM per (account × currency × day). Pre-aggregated for fast balance reads.

Dimension Tables

• dim_merchants (SCD2 — risk profile, reserve_pct) · dim_customers · dim_payment_methods (SCD2)
• dim_accounts — Chart of accounts: merchant_balance, fees, reserve, tax, fx_gain_loss, chargeback_reserve.
• dim_currencies · dim_fx_rates (SCD2 daily — FX rate locked at posting time)

Section 2 — Logical Data Model (key columns)

fct_ledger_entries

fct_balance_transactions — balance_txn_id, txn_type ∈ {charge, refund, payout, chargeback, fee, transfer}, txn_ts, source_object_id (charge_id / refund_id), merchant_id, status.

Section 3 — How the model serves each stakeholder

Merchant · balance & payouts — Available balance = SUM(fct_ledger_entries.amount) WHERE account = 'merchant_balance' AND merchant_id = X. From snap_account_balance_daily for speed.

Finance · reconciliation — Reconcile every balance_txn: SUM(amount) GROUP BY currency = 0. Any failure = producer bug, page on-call.

Risk · chargeback exposure — Aggregate entry_type = 'chargeback' per merchant per day. High rate = risk profile change → increase reserve_pct.

Regulators · audit trail — "Show merchant X balance as of date Y as the books showed it then" — query fct_ledger_entries WHERE posted_ts <= Y AND filter out reversals for entries posted after Y.

Section 4 — Why append-only + invariant + idempotency key

• Append-only is regulatory. SOX / PCI / banking audits require an immutable history. Every reversal is a new pair of entries with opposite signs, never a retro edit.
• SUM=0 invariant per (balance_txn, currency). Continuous data-quality check. If it fails, payments stop until fixed.
• Idempotency at the row. UNIQUE(source_event_id) guarantees that producer retries don't create duplicate ledger entries — even under network partitions.
• FX as separate currency entries. EUR charge → USD payout requires a 4-entry transaction: −EUR (merchant_balance debit) / +EUR (fx_holding credit) / −USD (fx_holding debit at locked rate) / +USD (merchant_balance credit). FX gain/loss recognized in reporting currency.

Enhanced graphical data model — Stripe Double-Entry Ledger

Section 7 — SQL analysis

Sample data — €100 charge converted to USD payout (multi-currency, 4 entries)

-- Business event 1: charge €100 (customer pays merchant)
INSERT INTO fct_balance_transactions VALUES
  ('BTX_001','charge', '2025-05-01 09:00:00','CH_777','MRC_FR','succeeded');

-- Ledger entries (SUM = 0 per currency):
INSERT INTO fct_ledger_entries (balance_txn_id, account_id, merchant_id, currency_code, amount, entry_type, effective_ts, source_event_id) VALUES
  ('BTX_001','merchant_balance','MRC_FR','EUR',  100.00, 'charge',     '2025-05-01 09:00','EVT_001'),
  ('BTX_001','fees',            'MRC_FR','EUR',   -2.90, 'fee',        '2025-05-01 09:00','EVT_002'),
  ('BTX_001','customer_balance','MRC_FR','EUR', -100.00, 'charge',     '2025-05-01 09:00','EVT_003'),
  ('BTX_001','fees_revenue',    'MRC_FR','EUR',    2.90, 'fee',        '2025-05-01 09:00','EVT_004');
-- SUM(amount WHERE currency='EUR') = 100 - 2.90 - 100 + 2.90 = 0 ✓

-- Business event 2: payout €97.10 → USD at locked rate 1.08
INSERT INTO fct_balance_transactions VALUES
  ('BTX_002','payout', '2025-05-03 10:00:00','PO_777','MRC_FR','paid');

INSERT INTO fct_ledger_entries (balance_txn_id, account_id, merchant_id, currency_code, amount, entry_type, effective_ts, source_event_id) VALUES
  ('BTX_002','merchant_balance','MRC_FR','EUR',  -97.10, 'payout',  '2025-05-03 10:00','EVT_005'),  -- debit EUR
  ('BTX_002','fx_holding',      'MRC_FR','EUR',   97.10, 'payout',  '2025-05-03 10:00','EVT_006'),  -- credit EUR
  ('BTX_002','fx_holding',      'MRC_FR','USD', -104.87, 'payout',  '2025-05-03 10:00','EVT_007'),  -- debit USD (97.10 × 1.08)
  ('BTX_002','merchant_bank',   'MRC_FR','USD',  104.87, 'payout',  '2025-05-03 10:00','EVT_008'); -- credit USD
-- SUM EUR = 0 ✓ ; SUM USD = 0 ✓

A. Finance · invariant DQ check

Q1 — Continuous SUM=0 invariant per (balance_txn × currency)

SELECT
  balance_txn_id,
  currency_code,
  ROUND(SUM(amount), 4) AS sum_amount,
  COUNT(*)              AS entry_count
FROM fct_ledger_entries
WHERE effective_ts >= CURRENT_DATE - 1
GROUP BY balance_txn_id, currency_code
HAVING ABS(SUM(amount)) > 0.0001;
-- Empty = healthy. Any row = producer bug. Halts the payments pipeline.

B. Merchant · balance & payouts

Q2 — Merchant balance per currency (as-of-now)

SELECT
  merchant_id, currency_code,
  ROUND(SUM(amount), 2) AS balance
FROM fct_ledger_entries
WHERE account_id = 'merchant_balance' AND merchant_id = 'MRC_FR'
GROUP BY merchant_id, currency_code;
-- Or read from snap_account_balance_daily for sub-second response.

Q3 — Audit trail: balance as it appeared on a specific historical date

SELECT
  merchant_id, currency_code,
  ROUND(SUM(amount), 2) AS balance_as_of_2025_05_15
FROM fct_ledger_entries
WHERE account_id = 'merchant_balance'
  AND merchant_id = 'MRC_FR'
  AND posted_ts <= '2025-05-15 23:59:59'
GROUP BY merchant_id, currency_code;
-- Defensible regardless of corrections made AFTER 2025-05-15. The append-only contract.

C. Risk · chargeback exposure

Q4 — Chargeback rate per merchant + dispute lag

WITH charges AS (
  SELECT merchant_id, balance_txn_id, effective_ts AS charge_ts, ABS(amount) AS gross_usd
  FROM fct_ledger_entries
  WHERE entry_type = 'charge' AND account_id = 'merchant_balance' AND amount > 0
),
chargebacks AS (
  SELECT merchant_id, reversal_of_id, effective_ts AS chargeback_ts, ABS(amount) AS chargeback_usd
  FROM fct_ledger_entries WHERE entry_type = 'chargeback'
)
SELECT
  c.merchant_id,
  COUNT(c.balance_txn_id)                                                AS total_charges,
  COUNT(cb.reversal_of_id)                                               AS total_chargebacks,
  ROUND(100.0 * COUNT(cb.reversal_of_id) / NULLIF(COUNT(c.balance_txn_id), 0), 2) AS chargeback_rate_pct,
  ROUND(AVG(EXTRACT(EPOCH FROM (cb.chargeback_ts - c.charge_ts))/86400), 1) AS avg_dispute_lag_days
FROM charges c
LEFT JOIN chargebacks cb ON cb.reversal_of_id = c.balance_txn_id
WHERE c.charge_ts >= CURRENT_DATE - 90
GROUP BY c.merchant_id
HAVING COUNT(c.balance_txn_id) > 100
ORDER BY chargeback_rate_pct DESC;
-- Merchants >1% chargeback rate trigger reserve_pct increase.

Section 8 — Why this works

• Append-only is the contract. Historical balance reads ("as of date Y") are defensible regardless of corrections made later. Required by SOX, PCI, banking regulators.
• Invariant catches bugs at write time. Continuous DQ on SUM=0 — any producer regression halts the pipeline before bad data reaches finance.
• Idempotency at the row. UNIQUE(source_event_id) handles producer retries without duplicating money.
• FX as currency entries. No "convert at read time" — every conversion is a recorded ledger event with its locked rate.

Worked example — €100 charge → USD payout

1. Charge BTX_001 (€100): 4 ledger entries in EUR.
   • +€100 to merchant_balance · −€2.90 fee from merchant_balance · net €97.10 to merchant.
   • −€100 from customer_balance · +€2.90 to fees_revenue.
   • EUR sum = 100 − 2.90 − 100 + 2.90 = 0 ✓
2. Payout BTX_002 (3 days later, FX rate locked at 1 EUR = 1.08 USD).
   • −€97.10 from merchant_balance · +€97.10 to fx_holding (EUR side closes)
   • −$104.87 from fx_holding (USD side, 97.10 × 1.08) · +$104.87 to merchant_bank
   • EUR sum = 0 ✓ · USD sum = 0 ✓
3. Two months later: chargeback on BTX_001. Append 4 new entries that reverse the charge (positive amounts where original was negative). reversal_of_id links each new entry to its original. Original rows untouched. Audit trail preserved. Net effect: merchant balance net change for the chargeback is recorded as a fresh balance_txn.

Why this is hard — the design tension

The calendar is the hot read path: a search query for "Paris, July 4-7, 2 guests" hits potentially millions of (listing × night) rows in milliseconds. Calendar-as-fact with daily snapshots is the canonical answer. Booking modifications require append-only — same booking_id can't UPDATE in place. Cancellation refunds depend on (policy × days-from-check-in) and must be computed at cancel time, locked on the booking row. Multi-currency pays guest in EUR, host in USD; FX locked at booking.

Section 1 — Dimensional Model Overview

Fact Tables

• fct_calendar_nights — The "Availability" grain. One row per (listing × night × snapshot_date). Daily snapshots enable point-in-time price lookup.
• fct_bookings — The "Reservation" grain. One row per booking lifecycle. Append-only — modifications create new rows.
• fct_reviews — The "Bilateral feedback" grain. One row per review (typed: guest_to_host OR host_to_guest).
• fct_host_payouts — The "Payout" grain. One row per payout event after stay completion.

Dimension Tables

• dim_listings (SCD2 — price, policies, capacity drift)
• dim_hosts (SCD2 — superhost status, response rate)
• dim_guests
• dim_geography (neighborhood, lat/lon, market_id)

Section 2 — Logical Data Model (key columns)

fct_calendar_nights

fct_bookings — booking_id, listing_id, guest_id, request_ts, accepted_ts, cancelled_ts, cancellation_actor ∈ {guest, host, platform}, check_in_date, check_out_date, nights, guests, subtotal/cleaning/service/taxes/total_local, fx_rate_to_usd, total_usd, cancellation_policy ∈ {flexible, moderate, strict, super_strict}, refund_amount_usd, terminal_state.

Section 3 — How the model serves each stakeholder

Guest · search & book — Search joins fct_calendar_nights WHERE is_available=TRUE AND night_date BETWEEN x AND y. Hot path; pushed to Elasticsearch + indexed in warehouse.

Host · earnings & calendar mgmt — Daily payouts from fct_host_payouts. Pricing team analyzes fct_calendar_nights over time to recommend smart-price strategy.

Pricing team · "what was the price on date X?" — Time-travel via snapshot_date in the PK. Critical for dynamic-pricing experiments and dispute resolution.

Trust & safety · review-rate analysis — Bilateral reviews; if a host has high "guest cancelled" rate, T&S investigates.

Section 4 — Why calendar-as-fact + daily snapshot

• Without snapshots: can only answer "what's the price now?" Time-travel queries require reconstructing from dim_listings SCD2 + booking events — slow, error-prone, often impossible after data drift.
• With daily snapshots: "what was the price of listing X on July 4 last year (as Airbnb showed it on July 1)?" is one query.
• Cost: 7M listings × 365 future nights × daily snapshot = ~9B rows/day. Partition by night_date; aggressively expire old snapshots (keep 90-day raw, then weekly).
• Append-only on bookings. Modifications generate a new booking row with parent_booking_id; original sets terminal_state = modified. Audit trail preserved.

Enhanced graphical data model — Airbnb Bookings + Calendar

Section 7 — SQL analysis

Sample data — 1 listing, 5 nights of calendar, 1 booking, 1 guest cancellation

INSERT INTO fct_calendar_nights VALUES
  ('LST_PARIS_42','2025-07-04','2025-06-01', FALSE, FALSE, TRUE,  'BKG_777', 200.00, 240.00, 240.00,'EUR'),
  ('LST_PARIS_42','2025-07-05','2025-06-01', FALSE, FALSE, TRUE,  'BKG_777', 200.00, 240.00, 240.00,'EUR'),
  ('LST_PARIS_42','2025-07-06','2025-06-01', FALSE, FALSE, TRUE,  'BKG_777', 200.00, 240.00, 240.00,'EUR'),
  ('LST_PARIS_42','2025-07-07','2025-06-01', TRUE,  FALSE, FALSE,  NULL,    200.00, 220.00, 220.00,'EUR'),
  ('LST_PARIS_42','2025-07-08','2025-06-01', TRUE,  FALSE, FALSE,  NULL,    200.00, 220.00, 220.00,'EUR');

INSERT INTO fct_bookings VALUES
  ('BKG_777','LST_PARIS_42','GUEST_LISA','2025-06-01 10:00','2025-06-01 10:30',NULL,NULL,
   '2025-07-04','2025-07-07', 3, 2,
   720.00, 80.00, 56.00, 64.00, 920.00, 1.08, 993.60,
   'moderate', NULL, 'confirmed');

-- Guest cancels 5 days before check-in (under 'moderate' policy = 50% refund)
UPDATE fct_bookings SET cancelled_ts='2025-06-29 14:00', cancellation_actor='guest',
  refund_amount_usd=496.80, terminal_state='cancelled' WHERE booking_id='BKG_777';

A. Search · calendar utilization

Q1 — Available listings in Paris for July 4-7, 2 guests

SELECT l.listing_id, l.title, MIN(c.effective_price_local) AS price_low, MAX(c.effective_price_local) AS price_high
FROM dim_listings l
JOIN fct_calendar_nights c ON l.listing_id = c.listing_id
JOIN dim_geography g ON l.geography_id = g.geography_id
WHERE c.snapshot_date = CURRENT_DATE
  AND c.night_date BETWEEN '2025-07-04' AND '2025-07-06'
  AND c.is_available = TRUE
  AND l.capacity >= 2
  AND g.market_id = 'PARIS'
GROUP BY l.listing_id, l.title
HAVING COUNT(*) = 3;     -- all 3 nights available

B. Pricing team · time-travel queries

Q2 — Price of LST_PARIS_42 on July 4, 2025 — as Airbnb showed it on June 1

SELECT listing_id, night_date, base_price_local, smart_price_local, effective_price_local
FROM fct_calendar_nights
WHERE listing_id = 'LST_PARIS_42'
  AND night_date = '2025-07-04'
  AND snapshot_date = '2025-06-01';
-- The data product the dynamic-pricing team can't ship without.

C. Trust & safety · cancellation analysis

Q3 — Host cancellation rate (a strong T&S signal)

SELECT
  l.host_id,
  COUNT(*)                                                   AS total_bookings,
  COUNT(*) FILTER (WHERE b.cancellation_actor = 'host')      AS host_cancels,
  ROUND(100.0 * COUNT(*) FILTER (WHERE b.cancellation_actor = 'host')
        / COUNT(*), 2)                                        AS host_cancel_rate_pct
FROM fct_bookings b
JOIN dim_listings l USING (listing_id)
WHERE b.request_ts >= CURRENT_DATE - 90
GROUP BY l.host_id
HAVING COUNT(*) > 10
ORDER BY host_cancel_rate_pct DESC;
-- Hosts >3% rate get warning; >5% lose Superhost status.

Section 8 — Why this works

• Calendar-as-fact + snapshots = time-travel pricing queries are first-class.
• Append-only bookings with parent_booking_id chain = audit trail for modifications.
• Refund locked at cancel time = future policy changes don't restate historical refunds.
• Bilateral reviews = both guest and host accountability surfaced.

Worked example — BKG_777 cancellation under "moderate" policy

1. Booking total: $993.60 for 3 nights ($720 subtotal + $80 cleaning + $56 service + $64 tax, FX 1.08).
2. Cancelled 5 days before check-in.
3. Moderate policy refund_curve: ≥5 days before = 100% refund of nightly rate (subtotal only); cleaning fee always refunded; service fee non-refundable.
4. Refund = $720 (subtotal at 100%) × 0.50 (we're inside the partial-refund window for "moderate") + $80 cleaning + $0 service = $496.80.
5. fct_bookings.refund_amount_usd = $496.80, locked at cancellation_ts. Future re-querying gives same result regardless of policy changes after.
6. Calendar nights for Jul 4-6 flip back to is_available=TRUE in the next snapshot. Listing reopens for searches.

Why this is hard — the design tension

Two revenue streams with opposite shapes. Subscription is predictable, monthly, modeled at low row volume. Usage is volatile, hourly, billions of rows/day. Mid-cycle plan changes require pro-rated math. Trials look like paid plans but bill $0. Free-tier allowances reset each period. Multi-currency demands FX locked at usage time. The model must keep usage and subscription separate at the fact layer but join cleanly at invoice time.

Section 1 — Dimensional Model Overview

Fact Tables

• fct_subscription_state — The "Daily snapshot" grain. One row per (subscription × day). Captures plan, status, MRR.
• fct_usage_meter — The "Hourly usage" grain. One row per (subscription × meter × hour). High volume.
• fct_billing_events — The "Charge" grain. One row per subscription_charge / usage_charge / proration / refund / credit / dunning.
• fct_invoices — The "Statement" grain. One row per invoice — derived rollup of billing events.

Dimension Tables

• dim_accounts · dim_plans (SCD2 — pricing tiers drift) · dim_meters (compute_credits, storage_gb_month, api_calls) · dim_promotions (SCD2)

Section 2 — Logical Data Model (key columns)

fct_usage_meter — account_id, subscription_id, meter_id, hour_bucket_ts, raw_quantity, billable_quantity (= max(0, raw − free_allowance)), unit_price_local (locked at hour), currency_code, amount_local, fx_rate_to_usd, amount_usd.

fct_billing_events — event_id, account_id, subscription_id, event_type, plan_id (SCD2 effective at event_ts), event_ts, period_start, period_end, amount_local, currency_code, amount_usd, status, source_event_id UNIQUE.

fct_subscription_state — subscription_id, day, plan_id, status ∈ {trialing, active, paused, canceled, past_due}, mrr_usd, currency, days_into_cycle, days_remaining.

Section 3 — How the model serves each stakeholder

Finance · MRR & ARR — MRR = SUM(fct_subscription_state.mrr_usd) WHERE day = today AND status = 'active'. ARR = MRR × 12.

Product · usage analytics — Hourly grain → daily/weekly rollups for adoption analysis. Same source as Finance — one truth.

Customer Success · expansion signal — Accounts approaching their plan's overage threshold = upsell trigger. Computed from fct_usage_meter trends + dim_plans.tier_limit.

Sales · NRR cohorts — Net revenue retention by quarterly signup cohort. Combines all four facts.

Section 4 — Why hourly meter + daily snapshot + event log

• Hourly meter resolution is the integration point: fine enough for usage analytics, coarse enough that the table doesn't explode. Per-query/per-call rolls up to the hour at ingest.
• Daily subscription state avoids modeling subscription as a slowly-changing dim — too much daily flux. Daily snapshot is bounded and queryable.
• Append-only billing events with UNIQUE(source_event_id) = idempotent + audit-defensible. Mid-cycle plan change emits 2 events (proration credit on old, prorated charge on new); invoice is just SUM of events in the period.
• FX locked per hour. Same precision as the meter; never restated.

Enhanced graphical data model — SaaS Subscription + Usage

Section 7 — SQL analysis

Sample data — Mid-cycle plan upgrade with 15 days remaining

-- Account on Pro plan ($100/mo), upgrades to Enterprise ($300/mo) on day 15 of a 30-day cycle
INSERT INTO fct_billing_events VALUES
  ('EVT_S1','ACCT_X','SUB_42','subscription_charge','PLAN_PRO',       '2025-05-01 00:00','2025-05-01','2025-05-31',100.00,'USD',100.00,'paid','SRC_001'),
  -- Day 15: upgrade. Proration credit on PRO (15 days unused = $50)
  ('EVT_P1','ACCT_X','SUB_42','proration',          'PLAN_PRO',       '2025-05-15 14:00','2025-05-15','2025-05-31',-50.00,'USD',-50.00,'applied','SRC_002'),
  -- Day 15: prorated charge for ENT (15 days at new rate = $150)
  ('EVT_P2','ACCT_X','SUB_42','proration',          'PLAN_ENTERPRISE','2025-05-15 14:00','2025-05-15','2025-05-31',150.00,'USD',150.00,'paid','SRC_003');

-- Hourly usage: account exceeds 100 free credits/hour 3 times during the day
INSERT INTO fct_usage_meter VALUES
  ('ACCT_X','SUB_42','METER_COMPUTE','2025-05-15 09:00:00', 120,  20, 0.10,'USD', 2.00,1.0, 2.00,'2025-05-15 09:05'),
  ('ACCT_X','SUB_42','METER_COMPUTE','2025-05-15 14:00:00', 180,  80, 0.10,'USD', 8.00,1.0, 8.00,'2025-05-15 14:05'),
  ('ACCT_X','SUB_42','METER_COMPUTE','2025-05-15 18:00:00', 250, 150, 0.10,'USD',15.00,1.0,15.00,'2025-05-15 18:05');

A. Finance · MRR & invoice rollup

Q1 — MRR roll-forward (active subscriptions, latest snapshot)

SELECT
  COUNT(*)                                        AS active_subs,
  ROUND(SUM(mrr_usd), 2)                          AS mrr_usd,
  ROUND(SUM(mrr_usd) * 12, 2)                     AS arr_usd
FROM fct_subscription_state
WHERE day = CURRENT_DATE
  AND status = 'active';

Q2 — Invoice for SUB_42 in May 2025 (sums all event types)

SELECT
  subscription_id,
  SUM(amount_usd) FILTER (WHERE event_type = 'subscription_charge') AS subscription_charges,
  SUM(amount_usd) FILTER (WHERE event_type = 'usage_charge')        AS usage_charges,
  SUM(amount_usd) FILTER (WHERE event_type = 'proration')           AS proration_net,
  SUM(amount_usd) FILTER (WHERE event_type = 'credit')              AS credits,
  SUM(amount_usd)                                                    AS total_due_usd
FROM fct_billing_events
WHERE subscription_id = 'SUB_42'
  AND period_start = '2025-05-01'
GROUP BY subscription_id;

B. Customer Success · upsell signal

Q3 — Accounts approaching their plan's overage threshold (upsell candidates)

WITH usage_30d AS (
  SELECT subscription_id, meter_id, SUM(billable_quantity) AS used
  FROM fct_usage_meter
  WHERE hour_bucket_ts >= CURRENT_DATE - 30
  GROUP BY subscription_id, meter_id
),
plan_caps AS (
  SELECT s.subscription_id, m.meter_id, p.included_quantity
  FROM fct_subscription_state s
  JOIN dim_plans p USING (plan_id)
  CROSS JOIN dim_meters m
  WHERE s.day = CURRENT_DATE AND s.status = 'active'
)
SELECT u.subscription_id, u.meter_id, u.used, c.included_quantity,
  ROUND(100.0 * u.used / NULLIF(c.included_quantity, 0), 1) AS pct_of_cap
FROM usage_30d u
JOIN plan_caps c USING (subscription_id, meter_id)
WHERE u.used > c.included_quantity * 0.85
ORDER BY pct_of_cap DESC;
-- >85% of cap = upsell trigger; >100% = overage charges hit next invoice.

C. Sales · NRR by cohort

Q4 — Net Revenue Retention by signup cohort

WITH cohort_baseline AS (
  SELECT subscription_id, DATE_TRUNC('quarter', MIN(day)) AS cohort_q,
         FIRST_VALUE(mrr_usd) OVER (PARTITION BY subscription_id ORDER BY day) AS starting_mrr
  FROM fct_subscription_state
),
current_state AS (
  SELECT subscription_id, mrr_usd AS current_mrr
  FROM fct_subscription_state WHERE day = CURRENT_DATE
)
SELECT cb.cohort_q,
  COUNT(DISTINCT cb.subscription_id)                                      AS cohort_size,
  ROUND(SUM(cb.starting_mrr), 0)                                          AS starting_mrr_total,
  ROUND(SUM(COALESCE(cs.current_mrr, 0)), 0)                              AS current_mrr_total,
  ROUND(100.0 * SUM(COALESCE(cs.current_mrr, 0)) / NULLIF(SUM(cb.starting_mrr), 0), 1) AS nrr_pct
FROM cohort_baseline cb
LEFT JOIN current_state cs USING (subscription_id)
GROUP BY cb.cohort_q
ORDER BY cb.cohort_q;
-- NRR > 100% = expansion outpaces churn (the SaaS holy grail).

Section 8 — Why this works

• Hourly usage + daily subscription = right resolution at each grain. Usage is high-volume; subscription is bounded.
• Proration is two events, not one. Both credit and charge land in fct_billing_events; invoice rollup picks them up cleanly.
• Idempotent at source_event_id. Producer retries don't double-charge.
• FX locked per hour. Same precision as the meter; no FX restatement.

Worked example — SUB_42 mid-cycle upgrade + usage overage

1. Day 1 (May 1): charged $100 for Pro plan (full month).
2. Day 15 (May 15): upgrade to Enterprise. Two proration events:
   • Credit −$50 on Pro (15 unused days × $100/30)
   • Charge +$150 for Enterprise (15 days × $300/30)
   • Net delta: +$100
3. Day 15 (later): usage spikes. Plan includes 100 compute_credits/hr; account uses 250 in one hour → 150 billable at $0.10 = $15 overage.
4. End-of-month invoice for May: $100 (Pro full) − $50 (credit) + $150 (Ent prorated) + $25 (3-hr usage overage) = $225.
5. MRR effect on day 15 onward: jumps from $100 to $300/mo. Visible in fct_subscription_state.mrr_usd daily snapshot.

Why this is hard — the design tension

Spotify's royalty model is the cleanest example of a multi-party financial fact: each stream is a small event, but the dollars get split across artist + label + publisher + Spotify, with the rate calibrated per-country and the pool refreshed monthly. Mistakes are not just dollars — they're rights-holder lawsuits.

Two competing structural forces:

• Pool-based pro-rata (Spotify's choice): every premium subscriber's $$ enters one country pool; each artist gets their streams ÷ total country streams × pool. Fast, simple, predictable.
• User-centric (UCPS) (Apple/Deezer testing): each subscriber's $$ goes ONLY to the artists THEY listened to. Fairer to niche artists; computationally heavy at billions-of-users × millions-of-artists scale.

The query in co_sql_305-0375 implements pro-rata; the modeling section here covers the schema that makes both possible without a rewrite.

Section 1 — Dimensional Model

Enhanced graphical data model — Spotify Royalty Pool · Pro-Rata Distribution

Section 2 — The crucial SCD2 — bridge_track_rightsholder

The same track can have its label, publisher, or featured-artist split CHANGE between months. A pre-Sept rights deal pays Label A; a Sept-onward deal pays Label B. Rolling up against the current rights table gives wrong attribution for August streams.

SELECT t.track_sk, b.party_sk, b.party_role, b.share_pct
FROM fct_stream s
JOIN bridge_track_rightsholder b
  ON b.track_sk = s.track_sk
 AND s.played_at BETWEEN b.effective_from AND COALESCE(b.effective_to, '9999-12-31')

Every query joins by event-time, never by current. The bridge supports BOTH pro-rata (sum streams × share) AND UCPS (sum user_share × stream_share) without schema rewrite.

Section 3 — Why the pool table is its own fact

The country pool isn't computed at query time — it's frozen at month-close. Storing it in fct_country_pool:

• Auditors can replay any month's payout from the same inputs.
• Late-arriving streams (offline mode upload Day 35) attribute to the month-of-event, not month-of-ingest.
• If the calculation logic changes (e.g. UCPS rollout), past months' payouts are unaffected.

Section 4 — The 30-second royalty boundary

Industry standard: streams under 30 seconds DO NOT trigger royalty. Embed at the fact-table level:

CREATE TABLE fct_stream (
  ...
  played_seconds INTEGER NOT NULL,
  is_royalty_eligible INTEGER GENERATED ALWAYS AS (played_seconds >= 30) STORED
);

Every downstream query can filter WHERE is_royalty_eligible with no risk of forgetting. The flag is denormalized for performance; computed once at write.

Section 5 — Fraud-stream filtering

Bot-driven streams (5-second-loop attacks to inflate small artists) are real. Anti-fraud writes a side flag rather than deleting events:

fct_stream.is_fraudulent INTEGER (default 0)
-- Updated by a separate Spark job, not the player

Why update-not-delete: legal teams want the audit trail of "what was claimed vs paid". Deleted rows lose the "we caught X% of fraud" reporting capability.

Why this is hard — the design tension

The For-You-Page is the most-watched ML system on the planet — billions of impressions per day, sub-200ms latency, and the recommendations train on themselves. Modeling this without producing biased feedback loops is the entire game.

The trap: echo-chamber bias. The ranker boosts videos that are already getting watched; users see them more; they get watched more; they're boosted more. New videos and new creators starve. The schema must support both online learning (continuous updates) and counterfactual evaluation (was the recommendation actually causal?).

Section 1 — Dimensional Model

Enhanced graphical data model — TikTok For-You-Page · Recommendation Feedback Loop

Section 2 — The exploration table is non-negotiable

fct_exploration_event stores the 5% of impressions that were RANDOM (or from a holdout pool). Without this, you can't measure causality:

• "Watched videos liked by similar users" gets clicked 80% — is that because the algorithm is good, or because the videos were going to win anyway?
• Random exploration data answers: "if we showed this video to 1000 random users, how many would have engaged?" — the counterfactual baseline.
• Model lift = (algorithmic CTR − exploration CTR) ÷ exploration CTR. Without exploration, you can't compute lift, just attribution.

Every recommendation system at FAANG scale has this. Drop the term "off-policy evaluation" in interviews.

Section 3 — Storing the predicted score

The ranker's predicted engagement score (e.g., predicted_watch_seconds = 18.3) is stored AT IMPRESSION TIME, not query time. Why:

• Models are versioned. Today's prediction was made by ranker v117; replaying with v118 changes everything.
• "Was the model right?" requires comparing prediction-then to outcome-now. Storing the score freezes the test.
• Production debugging: a video shown 10M times with high score but low actual engagement is the diagnostic signal of model drift.

Section 4 — The cold-start lever

New videos have no engagement history; the ranker can't predict. Two design moves:

Section 5 — The shadow-traffic pattern for model rollouts

New ranker versions get shadow traffic: same impressions served, but new model also produces predictions logged to fct_ranker_score_shadow. If shadow looks good, promote to a 1% A/B; if A/B wins, ramp to 100%.

Schema requirement: fct_ranker_score has a model_version column. Production runs 5-10 ranker versions concurrently — all writing to the same table, joined by version for evaluation.

Section 6 — Privacy + retention

Engagement data is high-PII. The schema separates:

• fct_engagement — keyed by user_id_token (not user_id directly)
• vault.user_id_map — encrypted token mapping, separate access pool
• 30-day retention on raw events; aggregated rollups (per-user-per-day-per-video-cluster) kept indefinitely

Why this is hard — the design tension

Stripe processes ~$1T/year. Every cent must reconcile across charges, refunds, fees, payouts, FX, taxes, chargebacks — and survive replay, retries, and partial failures. The whole system is an append-only double-entry ledger: nothing is updated, only debited or credited. This is the same model banks have used for centuries; the schema is just the SQL realization.

Section 1 — Dimensional Model

Enhanced graphical data model — Stripe Double-Entry Ledger · Append-Only · SUM=0

Section 2 — The double-entry contract

Every transaction emits a set of journal entries. Their signed amounts MUST sum to zero per transaction_id. This is the core integrity constraint:

-- A $100 charge with a $3 Stripe fee:
INSERT INTO fct_journal_entry (txn_id, account, currency, amount) VALUES
  ('TXN_001', 'customer_xyz',  'USD', -100.00),  -- customer debited $100
  ('TXN_001', 'merchant_abc',  'USD',   97.00),  -- merchant credited $97
  ('TXN_001', 'stripe_revenue','USD',    3.00);  -- Stripe credited $3
-- SUM = 0 ✓

Run a daily integrity check:

SELECT txn_id, SUM(amount) AS net
FROM fct_journal_entry
GROUP BY txn_id
HAVING ABS(SUM(amount)) > 0.001;
-- ANY row returned = data corruption, page on-call

Section 3 — Idempotency via natural key

Stripe's API is idempotent: same idempotency key + same body = same result. The schema enforces:

UNIQUE (idempotency_key, account_id, txn_type)
ON fct_journal_entry

A retry tries to insert a duplicate row → unique violation → application catches and returns the original txn. Replay-safe by construction.

Section 4 — Refunds are NEW entries, not deletes

Junior engineers mark a charge as "refunded" with an UPDATE. Wrong. The double-entry ledger appends:

-- Refund of TXN_001
INSERT INTO fct_journal_entry (txn_id, account, currency, amount, references_txn) VALUES
  ('TXN_002', 'merchant_abc',  'USD',  -97.00, 'TXN_001'),
  ('TXN_002', 'customer_xyz',  'USD',  100.00, 'TXN_001'),
  ('TXN_002', 'stripe_revenue','USD',   -3.00, 'TXN_001');

Both transactions exist forever. fct_account_balance is just SUM(amount) GROUP BY account — always derivable, never wrong.

Section 5 — Multi-currency lock

Charge in EUR, Stripe books revenue in USD, merchant settles in GBP. Three currencies, one transaction. Three journal entries with different currency columns + an fx_rate_locked_at_event column on each. Querying "what did we earn in March in USD?" sums the USD-denominated entries — never re-does FX.

Section 6 — Chargebacks are 60-90 days late

Card networks send chargebacks 30-90 days after the charge. The original transaction has long since posted; the chargeback emits a NEW journal entry with references_txn = 'TXN_001'. The merchant balance silently goes down 60 days later. Schema accommodates: monthly close-of-books locks the past, but new chargeback rows can still arrive — they go to a "post-close adjustment" sub-period.

Why this is hard — the design tension

Enterprise CRM has a problem most B2C platforms never face: nested organizations. JP Morgan has 100 subsidiaries, each with their own opportunity flow, but the renewal happens at the parent level. A revenue rollup that doesn't aggregate up the tree is wrong; one that aggregates incorrectly double-counts subsidiaries.

Plus multi-tenancy: every Salesforce customer's data is in the same physical tables, partitioned by org_id. One bad query reads someone else's data. The schema design and the row-level security stack must enforce isolation absolutely.

Section 1 — Dimensional Model

Enhanced graphical data model — Salesforce Account Hierarchy · Multi-Tenant CRM

Section 2 — Recursive hierarchy with materialized path

Two ways to model the parent-child tree:

Salesforce uses a hybrid: parent_id for writes + materialized path for read perf, with a daily job to rebuild paths. Mention all three in the interview.

Section 3 — Rollup query — the recursive CTE

WITH RECURSIVE descendants AS (
  SELECT account_id, account_id AS root, 0 AS depth FROM dim_account WHERE parent_account_id IS NULL
  UNION ALL
  SELECT a.account_id, d.root, d.depth + 1
  FROM dim_account a JOIN descendants d ON a.parent_account_id = d.account_id
  WHERE d.depth < 10  -- circuit breaker
)
SELECT d.root AS top_level_account,
  SUM(s.arr) AS total_subtree_arr,
  COUNT(DISTINCT a.account_id) AS subsidiaries
FROM descendants d
JOIN fct_account_arr_snapshot s ON s.account_id = d.account_id
GROUP BY d.root;

Section 4 — Multi-tenant isolation

Every fact and dim table has a leading org_id column. The platform enforces:

• Partition key: org_id is part of the primary key
• Index leading column: org_id-first composite indexes
• Row-level security: CREATE POLICY filters every query — engineers can't access a tenant's data without going through the API layer
• Per-tenant query caps: a runaway query in one org doesn't take down the cluster

Section 5 — Record sharing (the bridge)

Salesforce's "share with my manager" feature: opportunity Y is owned by rep A but visible to manager B + VP C. Modeled as bridge_account_user_access(account_sk, user_sk, permission_level, granted_via). Sharing rules cascade up the role hierarchy automatically — bridge rows generated by a daily job from the role tree.

Section 6 — Pipeline velocity from stage changes

Don't store "current_stage" as a column on the opportunity — that's lossy. Store every stage change as an event in fct_opportunity_stage_change(opp_id, stage, entered_at). Current stage is the latest event:

SELECT opp_id, stage AS current_stage
FROM (
  SELECT opp_id, stage, ROW_NUMBER() OVER (PARTITION BY opp_id ORDER BY entered_at DESC) AS rn
  FROM fct_opportunity_stage_change
) WHERE rn = 1;

Velocity, conversion-by-stage, time-in-stage all derivable from the same table. Single source of truth for "everything pipeline".

Why this is hard — the design tension

Netflix has the data problem most consumer platforms can only dream of: 250M subscribers, 15B+ play events per day, content metadata that propagates regionally over minutes-to-hours. Three structural challenges intersect:

• Late-arriving dimensions (LAD) — a Netflix Original drops at midnight Pacific; play events fire before the title metadata propagates to every regional shard. INNER JOIN drops the views silently and Content Strategy stares at "0 plays in the first 3 minutes" of the most-anticipated launch of the quarter.
• Series-vs-episode-vs-season grain — "Stranger Things S5 E1" is one episode, of one season, of one series, of one franchise. Aggregations must roll up cleanly without double-counting (a viewer watched 8 episodes — that's 1 series-bingewatcher, not 8 series-views).
• Bingewatching as a derived behavior — bingewatching isn't recorded; it's computed from the play stream. The schema must support both per-session binge (3+ episodes within 24h) and per-day binge (any 3+ episodes of same series in a calendar day) without a re-write.

Section 1 — Dimensional Model

Enhanced graphical data model — Netflix · Streaming + Series + Bingewatching + LAD

Section 2 — The LAD-defensive query (the make-or-break pattern)

Every play-event-to-content-dim join must be defensive. The shape:

WITH current_episode AS (
  SELECT episode_id, title, series_id, season_number, episode_number,
    ROW_NUMBER() OVER (PARTITION BY episode_id ORDER BY effective_date DESC) AS rn
  FROM dim_episode
)
SELECT p.play_id, p.account_id, p.profile_id,
  COALESCE(e.title,         'Episode ID: ' || p.episode_id || ' (Pending Sync)') AS title,
  COALESCE(e.series_id,     -1) AS series_id_safe,
  COALESCE(e.season_number, -1) AS season_number_safe,
  p.watch_minutes
FROM fct_play_event p
LEFT JOIN current_episode e ON e.episode_id = p.episode_id AND e.rn = 1;

Three things working together: LEFT JOIN keeps every play; ROW_NUMBER + rn=1 picks the most-recent SCD2 dim version; COALESCE with a searchable placeholder ("Pending Sync") so the BI tool's filter dropdown surfaces orphans. The orphan-rate SLO is < 0.5% — above 1% pages the data-platform on-call.

Section 3 — Series + episode roll-up — the bridge approach

"Top binged series this week" requires going from episode → series. Naive approach: store series_id directly on every play. Problem: when a series is re-titled (Marvel acquires a show, renames it), all historical plays now point to the wrong series name.

The bridge approach: store only episode_id on the fact (the immutable identity); join through SCD2 dim hierarchy at query time, with temporal-correctness via the bridge:

SELECT s.series_title, COUNT(DISTINCT p.account_id || '|' || s.series_id) AS unique_viewers
FROM fct_play_event p
JOIN dim_episode e USING (episode_id)
JOIN dim_series s
  ON s.series_id = e.series_id
 AND p.played_at BETWEEN s.effective_from AND COALESCE(s.effective_to, '9999-12-31')
GROUP BY s.series_title
ORDER BY unique_viewers DESC;

Plays that happened during the OLD series_title attribute correctly to the OLD title; new plays attribute to the new. No backfill required when re-titling.

Section 4 — Bingewatching as a derived fact

Bingewatching is not an event — it's a pattern. Two co-existing definitions need separate derived facts:

-- fct_binge_session derivation, refreshed nightly
WITH same_series_runs AS (
  SELECT account_id, profile_id, series_id, episode_id, started_at,
    LAG(started_at) OVER (PARTITION BY account_id, profile_id, series_id ORDER BY started_at) AS prev_start
  FROM fct_play_event
),
sessioned AS (
  SELECT *,
    SUM(CASE WHEN prev_start IS NULL OR started_at - prev_start > INTERVAL '30 min' THEN 1 ELSE 0 END)
      OVER (PARTITION BY account_id, profile_id, series_id ORDER BY started_at) AS session_id
  FROM same_series_runs
)
SELECT account_id, profile_id, series_id, session_id,
  MIN(started_at) AS session_start, COUNT(*) AS episodes_in_session
FROM sessioned
GROUP BY account_id, profile_id, series_id, session_id
HAVING COUNT(*) >= 3;

Section 5 — Series-streak detection — gaps-and-islands per (user, series)

"Watched at least one episode every day for 5 days" is the engagement loyalty signal. Different shape from binge — same user across CONSECUTIVE days, not multiple episodes in one day. The pattern is gaps-and-islands:

WITH active_days AS (
  SELECT DISTINCT account_id, series_id, DATE(started_at) AS active_date
  FROM fct_play_event
),
islands AS (
  SELECT account_id, series_id, active_date,
    julianday(active_date) - ROW_NUMBER() OVER (PARTITION BY account_id, series_id ORDER BY active_date) AS island_id
  FROM active_days
)
SELECT account_id, series_id,
  MIN(active_date) AS streak_start, MAX(active_date) AS streak_end, COUNT(*) AS streak_days
FROM islands
GROUP BY account_id, series_id, island_id
HAVING COUNT(*) >= 5;

Per-series streaks reveal which shows hook viewers — the "people kept coming back to Stranger Things every day for a week" insight. Used by content acquisition for renewal decisions.

Section 7 — The QoE side-table

Quality-of-experience (rebuffering, bitrate switches, startup latency) is high-volume — heartbeats every 60s. Storing on the play fact bloats it. Production splits:

fct_play_event       — one row per session, low volume, hot
fct_play_heartbeat   — many rows per session, high volume, downsampled to 5-min in warehouse
fct_qoe_summary      — derived nightly, rebuf-time / total-time per session

Analysts query fct_qoe_summary 99% of the time; raw heartbeats are reserved for SRE incident drills.

Section 8 — Late-arriving FACTS — the offline-mobile problem

A user watches an episode offline on the subway. The mobile uploads the play event 4 hours later. By then the dim_episode might have moved on (re-genre'd). The defensive temporal join handles it:

JOIN dim_episode e
  ON e.episode_id = p.episode_id
 AND p.played_at BETWEEN e.effective_from AND COALESCE(e.effective_to, '9999-12-31')

The play attributes against the dim version that was current AT THE TIME OF THE EVENT, not now. This is the LAD-FACT cousin of the LAD-DIM problem — same SCD2 mechanism, opposite direction of lateness.

Section 6 — Profile-vs-account distinction

Netflix's profile model is unusual: one billing account with up to 5 profiles. Mom watches drama; Kid watches cartoons. Modeling consequences:

• Account-grain facts — billing, plan tier, payment events. These cross profile boundaries.
• Profile-grain facts — every play, search, browse, rating. These are profile-keyed.
• Both keys on every event — fct_play_event has BOTH account_id AND profile_id, never just one. Otherwise rolling up "minutes per account" requires joining back through dim.

The 2023 password-sharing crackdown depended on this distinction — accounts with profiles being used in geographically-incompatible patterns flagged for sharing.

Why this is hard — four data problems pretending to be one deal

Every acquisition press release says "stronger together." Every acquirer's data team knows that means two incompatible CRMs, two warehouses, two ID systems, two fiscal calendars, and an executive expecting unified KPIs on day 90. The interview question: "your company just bought a $20B SaaS — walk me through the data integration". The senior signal is naming all four problems first, then sequencing them:

• Entity resolution — is their Acme Corp the same as our Acme Corp? Golden record + crosswalk + an ordered rule playbook (strict-then-fuzzy).
• Schema unification — both apps keep shipping; can't freeze either DB. Strangler pattern with dual-write + CDC + reconciliation.
• Warehouse consolidation — Snowflake vs Databricks vs BigQuery. Three strategies: big-bang, federated query, or share-and-bridge.
• Multi-org metrics — their DAU isn't your DAU. Fiscal calendars, FX policy, consolidation rules, overlapping ARR. Versioned dim_metric_definition.

Full deep-dive: the M&A Integration page walks all four patterns end-to-end with Adobe-Figma, Microsoft-Activision, Cisco-Splunk, and Salesforce-Slack as live case studies.

Section 1 — Dimensional Model

Enhanced graphical data model — M&amp;A · Golden Records + Strangler + Multi-Org Metrics

Section 2 — The matching playbook (strict rules first, fuzzy rules second)

You never decide "these two rows are the same customer" using one rule. You run an ordered list of rules from strict to permissive. The first rule that fires wins — same idea as airport security: passport check first (definite ID), then bag scan, then pat-down. Strict rules auto-merge; fuzzy rules send candidates to a human analyst. (Industry jargon: this is sometimes called the "match-key cascade" — same thing, fancier name.)

Section 3 — The un-merge — the part everyone forgets

Two months in, an MDM analyst realizes "Acme Corp Holding" and "Acme Corporation" were merged but are actually parent/subsidiary. You need to un-merge without losing referential integrity in the downstream pipelines that already consumed the wrong golden ID.

The pattern: superseded_by column on customer_xref + append-only crosswalk + SCD2 on golden_customer. Never DELETE; retire the golden record, point affected source IDs at a new golden_id, log who/when, emit CDC event so fact tables can re-attribute. Without this, the first analyst un-merge request is a 2-week incident.

Section 4 — The strangler dual-write pattern

Both apps keep shipping during a 6-18-month convergence. The pattern: stand up unified schema alongside source; CDC every source change to a translation layer; backfill historicals; cutover one entity at a time; reconcile daily.

-- Reconciliation query (runs hourly during cutover)
WITH source_counts AS (
  SELECT 'acquirer_crm' AS src, COUNT(*) AS n, MAX(updated_at) AS last_change
  FROM acquirer_crm.customers
  UNION ALL
  SELECT 'target_crm', COUNT(*), MAX(updated_at) FROM target_crm.tenants
),
unified_counts AS (
  SELECT source_system AS src, COUNT(*) AS n, MAX(ingested_at) AS last_ingest,
         COUNT(*) FILTER (WHERE ingest_status = 'rejected') AS rejected
  FROM unified.customer_ingest_log GROUP BY source_system
)
SELECT s.src, s.n AS source_rows, u.n AS unified_rows,
       100.0 * ABS(s.n - u.n) / NULLIF(s.n, 0) AS drift_pct,
       u.rejected,
       EXTRACT(EPOCH FROM (s.last_change - u.last_ingest)) AS lag_seconds
FROM source_counts s LEFT JOIN unified_counts u USING (src);

Drift > 0.1% pages on-call. Lag > 5 min pages on-call. Cutover gate: 72 consecutive hours green.

Section 5 — Warehouse consolidation — three strategies

Every real M&A uses share-and-bridge for the first 6 months, then commits. Tag every query/cluster with cost_owner = 'pre-merger-target' | 'pre-merger-acquirer' | 'post-merger-combined' so the CFO's "how much of last month's bill came from the acquired team" question has an answer.

Section 7 — Case studies (see the deep-dive)

Full walk-through: see M&A Integration deep-dive.

Section 8 — Senior framing

Section 6 — Multi-org metrics — versioned definitions

The board deck has "combined DAU" on slide 3. It's wrong, because acquirer counts "any session in 24h" and target counts "any session in 7d, deduped to one row per user-day". Bake definitions into a versioned dim instead of into SQL:

CREATE TABLE dim_metric_definition (
  metric_id          TEXT NOT NULL,        -- 'dau'
  definition_version INT  NOT NULL,
  org_scope          TEXT NOT NULL,        -- 'acquirer' | 'target' | 'combined'
  window_unit        TEXT NOT NULL,        -- 'hour' | 'day' | 'week'
  window_size        INT  NOT NULL,
  dedup_grain        TEXT NOT NULL,        -- 'user' | 'user-day' | 'session'
  fiscal_calendar    TEXT NOT NULL,        -- 'calendar' | '445'
  currency_policy    TEXT NOT NULL,        -- 'lock-at-txn' | 'lock-at-month-end'
  effective_from     DATE NOT NULL,
  effective_to       DATE,
  PRIMARY KEY (metric_id, definition_version)
);

Every dashboard tile cites (metric_id, definition_version). Combined-DAU has three competing versions; finance picks the one that matches the board narrative; the data layer auto-generates the SQL from the dim row.

Why this is hard — five problems pretending to be one model

Every interview asks the same question: "design the data model for a fraud system at a fintech / marketplace / streaming product." Junior answers stop at "a transaction table with a risk_score column." The senior signal is naming the five orthogonal sub-problems first:

• Identity resolution — the SAME user appears across 3 emails, 2 phones, 4 devices, 5 IPs. Build a graph, not a row. Strong edges (same payment instrument) vs weak edges (same IP within 1h).
• Impossible travel / multi-geo — login from Lagos, transaction from Berlin 18 min later. Velocity of physical position vs known device/SIM.
• Account sharing detection — one user, many simultaneous-session devices/geos (Netflix family-plan abuse, Spotify subscription sharing). Inverse of multi-account fraud: one identity using many accounts looks like sharing; many identities sharing one account looks like ATO.
• Decision replay — at audit time you must reproduce why you blocked the September 14 transaction. Means: freeze the model version, ruleset version, and feature snapshot at decision time. SCD2 everywhere.
• Feedback loop — chargebacks come back 30-180 days later; analyst verdicts hours later. Both must flow back as labels to retrain the next model — without the loop, the model degrades silently.

Common interview pitfall: conflating multi-account fraud (one bad actor opens 50 accounts to abuse signup bonuses) with account sharing (one legit user shares Netflix with their parents) with account takeover (one bad actor uses one legit user's compromised account). All three look like "identity ↔ account doesn't match 1:1" in the data — the schema must distinguish them, or every detection rule produces the wrong false-positive class.

Section 1 — Dimensional Model

Section 2 — Enhanced graphical data model — Fraud · Identity Graph + Decision Replay + Feedback Loop

Section 3 — The Identity Graph (the part everyone gets wrong)

The graph turns "is this account fraudulent?" into "what's the connected component of strong edges this account participates in?" Edges have a strength:

• Strong (1.0): same payment instrument used by two accounts; same KYC document hash; same SIM
• Medium (0.5): same device fingerprint observed within 24h on both accounts
• Weak (0.2): same IP observed within 1h; same residential ASN within a week

Compute the identity_cluster via connected components on edges with edge_strength >= 0.5 (run nightly; incremental for new edges). Strong-only clusters are the action graph; weak-edge graphs feed the analyst as "these may be related but require human judgement."

Section 5 — Worked numerical example · the Lagos-Berlin transaction

22:47 UTC. A user account in good standing for 18 months attempts a €4,200 transaction.

Decision is written to fact_transaction with decision='block', risk_score=110, plus the frozen context: ruleset_id=42, model_id='v7.2', feature_snapshot_id=$decision_id. The 6 contributing rules each get a row in fact_rule_firing.

A case opens, analyst reviews in 4 hours, confirms fraud → label written to fact_analyst_label. The graph-expansion query above runs on the cluster the device fingerprint belongs to; 17 additional accounts are surfaced, of which 12 have transactions in the last week. Fraud ops freezes them all pending review. None had crossed the per-account threshold individually; only the cluster lookup tied them together.

180 days later: a chargeback arrives on one of the 17 (txn from before the analyst freeze). fact_chargeback records it idempotently (network deduplicates retries). fact_outcome_label updates: where the analyst said 'fraud' AND the chargeback agreed → final_label='fraud', source='agreed'. The retrain pipeline picks this up Friday and the new model_id v7.3 ships Monday with this case in its training set.

Section 4 — Stakeholder SQL · grouped by who asks

Fraud analyst — case queue ordered by SLA risk

SELECT c.case_id, c.opened_ts, c.sla_hours,
       EXTRACT(EPOCH FROM (NOW() - c.opened_ts))/3600 AS hours_open,
       t.amount, t.merchant, u.email, u.risk_tier
FROM fact_case c
JOIN fact_transaction t ON c.txn_id = t.txn_id
JOIN dim_user u ON t.user_id = u.user_id AND u.is_current
WHERE c.closed_ts IS NULL
ORDER BY (c.sla_hours - EXTRACT(EPOCH FROM (NOW() - c.opened_ts))/3600);

Risk PM — false-positive rate by ruleset version (last 30d)

SELECT t.ruleset_id, r.rules_json->'name' AS ruleset_name,
       COUNT(*) FILTER (WHERE t.decision = 'block') AS blocked,
       COUNT(*) FILTER (WHERE t.decision = 'block' AND ol.final_label = 'clean') AS fp,
       ROUND(100.0 * COUNT(*) FILTER (WHERE t.decision = 'block' AND ol.final_label = 'clean')
             / NULLIF(COUNT(*) FILTER (WHERE t.decision = 'block'), 0), 2) AS fp_pct
FROM fact_transaction t
JOIN dim_ruleset r ON t.ruleset_id = r.ruleset_id
LEFT JOIN fact_outcome_label ol ON t.txn_id = ol.txn_id
WHERE t.ts >= NOW() - INTERVAL '30 days'
GROUP BY t.ruleset_id, r.rules_json->'name'
ORDER BY fp_pct DESC;

Detection engineer — impossible-travel signal (same user, two geos < possible flight time apart)

WITH paired AS (
  SELECT s.user_id, s.ts AS ts_a, s.geo AS geo_a,
         LEAD(s.ts) OVER (PARTITION BY s.user_id ORDER BY s.ts) AS ts_b,
         LEAD(s.geo) OVER (PARTITION BY s.user_id ORDER BY s.ts) AS geo_b
  FROM fact_session_event s
)
SELECT user_id, ts_a, geo_a, ts_b, geo_b,
       EXTRACT(EPOCH FROM (ts_b - ts_a))/60 AS gap_minutes,
       great_circle_km(geo_a, geo_b)         AS distance_km
FROM paired
WHERE ts_b IS NOT NULL
  AND geo_a <> geo_b
  AND great_circle_km(geo_a, geo_b) / NULLIF(EXTRACT(EPOCH FROM (ts_b - ts_a))/3600, 0) > 900  -- > 900 km/h ⇒ faster than any plane
ORDER BY gap_minutes;

Detection engineer — account-sharing detector (one user, simultaneous sessions in distant geos)

SELECT user_id, COUNT(DISTINCT geo) AS distinct_geos_24h,
       COUNT(DISTINCT device_id)   AS distinct_devices_24h,
       MAX(great_circle_km_between_pairs) AS max_pair_km
FROM (
  SELECT s.user_id, s.geo, s.device_id, s.ts,
         great_circle_km(s.geo, LAG(s.geo) OVER (PARTITION BY s.user_id ORDER BY s.ts)) AS great_circle_km_between_pairs
  FROM fact_session_event s
  WHERE s.ts >= NOW() - INTERVAL '24 hours'
) p
GROUP BY user_id
HAVING COUNT(DISTINCT geo) >= 3 AND COUNT(DISTINCT device_id) >= 3;
-- 3+ geos AND 3+ devices in 24h = candidate for shared-account review,
-- separate from impossible-travel (which is about velocity, not parallelism).

Data scientist — feature-audit for replay (what did model v7.2 see for txn X?)

SELECT t.txn_id, t.risk_score, t.decision,
       t.model_id, m.sha, m.training_cutoff,
       fs.feature_vector_json
FROM fact_transaction t
JOIN dim_risk_model m       ON t.model_id = m.model_id
JOIN dim_feature_snapshot fs ON t.feature_snapshot_id = fs.feature_snapshot_id
WHERE t.txn_id = $1;
-- Reproduces the EXACT inputs the model used 6 months ago. Without this, every
-- "why did you block this customer?" audit becomes "we don't know."

Fraud ops — cluster expansion (the moment you find one mule, find the other 47)

WITH RECURSIVE walk AS (
  SELECT user_id, 0 AS hop
  FROM fact_analyst_label
  WHERE case_id = $1 AND label = 'fraud'
  UNION
  SELECT CASE WHEN e.user_a = w.user_id THEN e.user_b ELSE e.user_a END,
         w.hop + 1
  FROM walk w
  JOIN brg_user_device e ON w.user_id IN (e.user_a, e.user_b)
  WHERE e.edge_strength >= 0.5 AND w.hop < 4
)
SELECT DISTINCT user_id, MIN(hop) AS hops_from_seed
FROM walk
GROUP BY user_id
ORDER BY hops_from_seed;
-- Expands outward 4 hops on strong edges. Surfaces accounts the analyst
-- didn't know were related — typical haul: 30-200x the seed account count
-- for mule rings, 2-5x for friendly fraud.

Section 6 — Senior framing

Why this is hard — a single MAU number hides a leaky bucket

"How many monthly active users do we have?" is the question a junior answers with COUNT(DISTINCT user_id). The senior answer: that number is nearly useless on its own. A flat 10M MAU can mean a healthy stable product OR a bucket leaking 2M users a month and refilling with 2M new ones — a business about to fall off a cliff. The framework that fixes this is growth accounting (the Social Capital / Reforge model), and it forces four design problems:

• Define the activity event. Netflix counts a viewing session ≥ 2 min; Uber counts a completed trip; Spotify counts a track play ≥ 30s. The grain of fact_activity IS the product's definition of "active" — get it wrong and every downstream metric is wrong.
• The state machine. Every period, each user is exactly one of: new (first-ever active period), retained (active last period too), resurrected (was dormant, came back), or churned (active last period, gone this period). Plus, for revenue: expansion and contraction.
• The frozen cohort. A user's cohort = the period of their first activity, and it never changes. Cohort retention and LTV curves are only meaningful if the cohort key is immutable — which means it's a derived dimension, computed once, SCD0.
• The generalized value column. The whole framework runs on one column, inc_amt — incremental value created by a user in a period. Set inc_amt = 1 and you get pure engagement growth accounting; set inc_amt = daily_revenue and the identical query produces MRR growth accounting with expansion/contraction. One model, two metrics.

The two identities that make it work: MAU(t) = retained(t) + new(t) + resurrected(t) and MAU(t−1) = retained(t) + churned(t). Subtract them: ΔMAU = new + resurrected − churned. That decomposition is the entire point — it turns "MAU is flat" into "we added 2M new + 0.3M resurrected and lost 2.3M churned," which is an actionable, alarming, completely different statement.

Section 1 — Dimensional Model

Section 2 — Enhanced graphical data model — Growth Accounting · Activity → State Machine → Cohort Curves

Section 3 — One model, two metrics: the inc_amt generalization

The cleverness of growth accounting is that engagement growth and revenue growth run the identical query — only the value column changes:

• inc_amt = 1 → user-count growth accounting. new/retained/resurrected/churned are headcounts. This is the DAU/MAU leaky-bucket view.
• inc_amt = daily_revenue → MRR growth accounting. Now the same join also produces expansion (a retained user whose inc_amt went UP) and contraction (retained, but DOWN). Revenue identities: MRR(t) = retained + new + resurrected + expansion and MRR(t−1) = retained + churned + contraction.

Company lens. Netflix — activity = a viewing session; resurrection is the win-back of a lapsed subscriber; the metric that matters is monthly, because the subscription is monthly. Uber — activity = a completed trip; resurrection is enormous and seasonal (riders lapse for months, return for an airport run); weekly cohorts expose this. Spotify — activity = a 30-second play; DAU-dominant; the critical state transition isn't churn, it's free → premium, which growth accounting models as a contraction-to-expansion flip on inc_amt.

Section 5 — Worked numerical example · the flat-MAU trap

A streaming product reports MAU of 10.0M in March and 10.1M in April — leadership calls it "stable, slight growth." Growth accounting on the April row: