The Rate That Was

A medium 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
medium
Seniority
mid
Asked in
PwC

Problem

We run a professional services firm where consultants are staffed onto client engagements, often several at once, and log billable hours against each one. Rates change as consultants get promoted and as clients renegotiate terms, so any invoice we reissue for a past period has to bill at the rate that was in effect when the work was actually done. Design the schema that supports staffing, time logging, and invoice reconstruction.

Worked solution and explanation

What this problem really is

This is a slowly changing measure in a consulting costume. The rate is not a fact about a person. It moves when someone is promoted and when a client renegotiates, and the only question that matters is whether last March's invoice can be reissued to the cent. Anyone can draw consultants, engagements and time entries. The trap is where the rate lives. Put a current_rate on dim_consultants, price hours with a join at query time, and the first promotion quietly reprices every invoice that person ever appeared on.

Step 1: Bridge the many-to-many with staffing_assignments

A consultant sits on several engagements in one week, and each engagement staffs many people. No foreign key on either dimension can hold that. The bridge gets its own surrogate assignment_key because the pair alone is not unique: the same person can be staffed twice under different roles.

Step 2: Effective-date the role, never overwrite it

role_on_engagement and allocation_pct belong to the assignment, not the person. A mid-engagement promotion closes the row with assigned_to and opens a new one. That is Type 2 on the bridge: the old role survives for the hours logged under it.

Step 3: Version the rate card for renegotiations

rate_cards holds one row per engagement, role and validity window. A renegotiated rate is a new row with a fresh valid_from, never an UPDATE to hourly_rate.

Step 4: Snapshot rate_applied onto the fact

Each fact_time_entries row is one logged entry against an assignment on a work_date. At entry time, resolve the card in effect and write rate_applied and billed_amount onto the row, keeping rate_card_key for lineage. A reissue becomes a plain SUM(billed_amount) with no temporal join to get wrong.

`rate_cards` versions the rate, `staffing_assignments` bridges people to engagements, and `fact_time_entries` freezes `rate_applied` and `billed_amount` per entry.
  • dim_clients: client_key INT PK, client_name TEXT, industry TEXT, region TEXT
  • dim_consultants: consultant_key INT PK, full_name TEXT, home_office TEXT, hire_date DATE
  • dim_engagements: engagement_key INT PK, client_key INT FK, engagement_name TEXT, engagement_type TEXT, start_date DATE, end_date DATE
  • staffing_assignments: assignment_key BIGINT PK, engagement_key INT FK, consultant_key INT FK, role_on_engagement TEXT, allocation_pct DECIMAL, assigned_from DATE, assigned_to DATE
  • rate_cards: rate_card_key BIGINT PK, engagement_key INT FK, role_on_engagement TEXT, hourly_rate DECIMAL, currency TEXT, valid_from DATE, valid_to DATE, is_current BOOLEAN
  • fact_time_entries: time_entry_key BIGINT PK, assignment_key BIGINT FK, consultant_key INT FK, engagement_key INT FK, rate_card_key BIGINT FK, work_date DATE, hours DECIMAL, is_billable BOOLEAN, rate_applied DECIMAL, billed_amount DECIMAL, currency TEXT
  • dim_engagements.client_key references dim_clients.client_key
  • staffing_assignments.engagement_key references dim_engagements.engagement_key
  • staffing_assignments.consultant_key references dim_consultants.consultant_key
  • rate_cards.engagement_key references dim_engagements.engagement_key
  • fact_time_entries.assignment_key references staffing_assignments.assignment_key
  • fact_time_entries.consultant_key references dim_consultants.consultant_key
  • fact_time_entries.engagement_key references dim_engagements.engagement_key
  • fact_time_entries.rate_card_key references rate_cards.rate_card_key
Snapshot on the fact
rate_applied and billed_amount written once at entry. Reissue is a SUM. Immutable by construction, at the cost of a few bytes per row.
Resolve from effective-dated history
Join on work_date BETWEEN valid_from AND valid_to at billing time. One source of truth, but every invoice depends on non-overlapping windows staying correct.

Common follow-up questions