The Territory That Keeps Moving

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

Domain
Data Modeling
Difficulty
hard
Seniority
L5
Asked in
Bayer

Problem

We are a pharmaceutical company that employs a field sales force selling prescription medications to healthcare providers. We need a data warehouse that tracks sales performance by rep, product, and territory, supports quota attainment reporting, and provides an auditable record of all sales interactions for compliance purposes. Design the data model.

Reps get reassigned. The receipts have to survive.

Worked solution and explanation

Why this problem exists in real interviews

This probes whether you can model territories that realign mid-year without corrupting historical quota credit. The signal is Type 2 SCD on both dim_territories and dim_reps so every historical interaction resolves to the rep and territory that owned it at that moment, with an auditable trail for compliance.

Break down the requirements

Step 1: Declare the interaction grain

fact_sales_interactions sits at one row per rep-provider touch. That is also the grain the Sunshine Act expects for transfer-of-value reporting.

Step 2: Apply Type 2 SCD to dim_reps and dim_territories

Territory realignments happen at least once a year. Type 2 on both dimensions preserves the as-of assignment so past credit does not retroactively move.

Step 3: Model quota as a separate fact

fact_quota_attainment at rep-product-period grain is the cleanest place to store targets and actuals. Keeping it separate from interactions avoids conflating activity with outcomes.

Step 4: Preserve provider identity for compliance

provider_id on the interaction fact is the compliance hook. Every transfer of value is traceable to an NPI without a join hop.

The solution

Below is one defensible model. The conceptual anchor is Type 2 SCD on territories and reps, which preserves attribution across realignments.

Type 2 SCD on `dim_territories` and `dim_reps` preserves historical rep-to-territory attribution across realignments
  • dim_reps: rep_sk BIGINT PK, rep_nk TEXT, territory_sk BIGINT FK, effective_from TIMESTAMP, effective_to TIMESTAMP, is_current BOOLEAN
  • dim_products: product_sk BIGINT PK, product_nk TEXT, therapeutic_area TEXT, is_controlled BOOLEAN
  • dim_territories: territory_sk BIGINT PK, territory_nk TEXT, region TEXT, effective_from TIMESTAMP, effective_to TIMESTAMP, is_current BOOLEAN
  • fact_sales_interactions: interaction_id BIGINT PK, rep_sk BIGINT FK, product_sk BIGINT FK, territory_sk BIGINT FK, provider_id TEXT, interaction_ts TIMESTAMP, transfer_of_value DECIMAL
  • fact_quota_attainment: attainment_id BIGINT PK, rep_sk BIGINT FK, product_sk BIGINT FK, period_start DATE, quota_target DECIMAL, actual_units DECIMAL
  • dim_territories references dim_reps
  • dim_reps references fact_sales_interactions
  • dim_products references fact_sales_interactions
  • dim_territories references fact_sales_interactions
  • dim_reps references fact_quota_attainment
  • dim_products references fact_quota_attainment

The analysis pattern

Rep attainment by therapeutic area with compliance transfer-of-value
SELECT
    r.rep_nk,
    p.therapeutic_area,
    SUM(q.actual_units) / NULLIF(SUM(q.quota_target), 0) AS attainment_ratio,
    SUM(i.transfer_of_value) AS compliance_tov
FROM fact_quota_attainment q
JOIN dim_reps r ON r.rep_sk = q.rep_sk
JOIN dim_products p ON p.product_sk = q.product_sk
LEFT JOIN fact_sales_interactions i
  ON i.rep_sk = q.rep_sk
 AND i.product_sk = q.product_sk
 AND i.interaction_ts >= q.period_start
WHERE q.period_start = DATE '2025-01-01'
GROUP BY r.rep_nk, p.therapeutic_area

Trade-offs and alternatives

Type 2 SCD on territories and reps
Historical attribution is stable under realignment. Auditable by construction. Join complexity is higher and surrogate key management must be disciplined.
Append-only assignment fact
Rep-territory assignments live on their own event fact. Dimensions stay Type 1. Flexible for overlapping assignments. Every reporting query has to reconstruct the effective assignment via an as-of join.

Common follow-up questions