# The Rate That Was Canonical URL: Domain: Data Modeling · Difficulty: medium · Seniority: mid ## 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 ### Why this problem exists in real interviews This is a drifting-measure problem wearing a consulting costume. The rate a consultant bills is not a stable attribute of the person; it moves with promotions and client-negotiated discounts, and the whole ask hinges on one question: can you reconstruct a past invoice at the rate that was true when the work happened? Anyone can draw consultants, engagements, and time entries. The trap is where you put the rate. > **Trick to Solving** > > The moment you hear 'reissue a past invoice and it must match,' stop drawing dimensions and ask where the rate lives. If it lives on dim_consultants or a role table, every promotion silently rewrites history. It has to be snapshotted onto the time-entry row at the instant the entry is logged. > > 1. Recognize consultant-to-engagement is many-to-many > 2. Model staffing as a bridge with its own key > 3. Declare the time-entry grain > 4. Snapshot rate_applied onto the fact --- ### Break down the requirements #### Step 1: Spot the many-to-many A consultant is on several engagements in one week, and an engagement staffs many consultants. That is a many-to-many, and it cannot be expressed with a foreign key on either dimension. It needs a bridge table, staffing_assignments, with its own surrogate key. #### Step 2: Give the bridge attributes The role a person plays and their allocation percentage differ per engagement, so those belong on the assignment row, not the consultant. Effective dates on the assignment let a mid-engagement role change be a new row rather than a destructive update. #### Step 3: Declare the fact grain fact_time_entries is one row per consultant per engagement per work date. Hours and billed amount are additive at that grain, which is what lets finance roll up to engagement, client, and month without double counting. #### Step 4: Snapshot the rate At entry time, resolve the rate for this consultant, role, and date, and write rate_applied and billed_amount onto the fact row. From then on the row is self-describing: reissuing the invoice is a plain SUM, and no future promotion can touch it. --- ### The reference model Below is one defensible design. The anchor is that fact_time_entries carries its own rate_applied, so the invoice is reconstructed from the fact alone and never from a current-state join. > **Why this works** > > Snapshotting the rate makes the fact row the system of record for what the work was worth. Reissuing a past invoice becomes SUM(billed_amount) filtered to an engagement and month, immune to any later promotion or discount. The bridge keeps staffing many-to-many without exploding either dimension. > **Interviewers watch for** > > A strong candidate asks the reissue question before drawing anything and reaches for a snapshot column the moment they hear the answer. They also name the staffing many-to-many out loud and refuse to hang a rate off dim_consultants. > **Common pitfall** > > Storing a single current_rate on dim_consultants (or a role table) and computing billed_amount with a join at query time. The first promotion silently changes every historical invoice for that person, and finance discovers it only when a reissued invoice no longer matches the original. --- ### Trade-offs and alternatives **Snapshot rate on the fact** rate_applied and billed_amount written at entry time. * Invoice reconstruction is a plain SUM * History is immutable by construction * Slightly more storage per fact row **Join to a versioned rate table** Rate held in a Type 2 rate table keyed by consultant, role, and validity window. * Single source of truth for the rate * Every billing query becomes a temporal range join * One join slip re-attributes the whole invoice --- ## Common follow-up questions - A client negotiates a 10 percent discount retroactive to the start of the quarter. How do you apply it without corrupting already-issued invoices? _(Tests whether corrections are new adjustment rows versus destructive updates to snapshotted amounts.)_ - How would you support a consultant whose role changes mid-engagement from analyst to manager? _(Tests effective-dated assignment rows and whether the new rate flows into subsequent snapshots only.)_ - At tens of millions of time entries a year, how would you partition fact_time_entries for monthly invoice runs? _(Tests partitioning by work_date for pruning on period-scoped billing queries.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the-rate-that-was) - [Data Modeling Interview Questions](https://datadriven.io/data-modeling-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.