# What the Clock Remembers Canonical URL: Domain: Data Modeling · Difficulty: medium · Seniority: mid ## Problem AFRY's finance team bills clients each month for the hours consultants log against engineering projects, and a consultant's hourly rate gets renegotiated every few quarters. Model the time logs, projects, clients, and consultants so an invoice from last spring still reflects the rate that was in effect when the work was done, even after that rate has since changed. The same model should let finance total billed revenue per project per month. ## Worked solution and explanation ### Why this problem exists in real interviews This is a point-in-time rate lookup wearing a consulting-billing costume. Every candidate models consultants, projects, and time logs without much trouble. What separates the offer from the rejection is where the hourly rate lives: stamp it onto the time log when the work is recorded, or look it up later by joining the entry to the consultant's rate. Choose the join, and the first time finance renegotiates a rate, every invoice that consultant ever generated silently changes and last spring's closed books stop reconciling. > **Trick to solving** > > Recognize you have two shapes of the same number. The rate is a temporal attribute that drifts (consultant_rates with valid_from and valid_to), and the rate that was actually charged is a measure frozen on the fact. Stamp billed_rate and billed_amount onto fact_time_logs at entry time, and a closed month can never move no matter how rates change afterward. --- ### Break down the requirements #### Step 1: Declare the grain of the time log One row in fact_time_logs equals one logged entry: a consultant, a project, a date, and hours. Sub-day timing is dropped because finance never needs it. Hours stay additive, which is what lets revenue roll up cleanly to project and month. #### Step 2: Make rates temporal consultant_rates holds one row per rate period: consultant, billed role, hourly_rate, valid_from, valid_to. At most one rate is in effect for a consultant and role at any instant. This table answers what rate applies on a given date, and it is the source the entry pipeline reads when stamping a new log. #### Step 3: Snapshot the charged rate on the fact When an entry is recorded, look up the rate in effect on that date and write billed_rate and billed_amount onto the fact row. From then on, revenue is a sum over a frozen column, never a re-derivation. A later renegotiation inserts a new consultant_rates row and touches nothing historical. #### Step 4: Separate clients, projects, consultants Clients own many projects, so projects get their own dimension with a client foreign key. Consultants are their own dimension. The billed role lives on the rate row because the same consultant can bill different rates as lead versus reviewer, so role is not a static consultant attribute. --- ### The reference model Below is one defensible design. The anchor is that consultant_rates is the system of record for what a rate is on any date, while fact_time_logs carries a frozen snapshot of what was actually charged. That split is how you reconcile drifting rates with immutable invoices. > **Interviewers watch for** > > A strong candidate raises the rate-change question before drawing a single table and reaches for a snapshot column without being pushed. They also state that storing only an hourly_rate join key and multiplying at read time is a silent-corruption bug, and they explain that consultant_rates still exists as the lookup the entry pipeline reads. Naming 'effective dating' and 'immutable closed period' out loud signals seniority. > **Common pitfall** > > Computing revenue as hours times the consultant's current rate by joining fact_time_logs straight to a single-rate consultants table. It looks clean and it passes the demo, but the first renegotiation rewrites every historical invoice for that person and breaks reconciliation against months that finance already closed. A second pitfall is folding role onto dim_consultants, which collapses the lead-versus-reviewer rate split. > **How it scales** > > At roughly 50,000 entries a day over seven years the fact approaches 100M+ rows. Because billed_amount is frozen, monthly revenue is a partition-pruned SUM with no rate join at all. Partition fact_time_logs by date_key so a month-bounded query touches one partition, and old years archive to cold storage without affecting current reads. consultant_rates stays tiny, a few rows per consultant. --- ### The analysis pattern **Billed revenue per project per month** ```sql SELECT p.project_name, d.year, d.month, SUM(t.billed_amount) AS revenue, SUM(t.hours) AS hours FROM fact_time_logs t JOIN dim_projects p ON p.project_key = t.project_key JOIN dim_date d ON d.date_key = t.date_key GROUP BY p.project_name, d.year, d.month ORDER BY d.year, d.month, revenue DESC ``` *No rate join: revenue is a pure additive roll-up over the snapshotted measure.* --- ### Trade-offs and alternatives **Snapshot on the fact** billed_rate and billed_amount frozen on fact_time_logs. * Closed months are immutable by construction * Revenue is a simple additive SUM, no rate join * Costs a little extra fact storage **Join to a current rate at read time** fact_time_logs joins dim_consultants for hourly_rate. * Smaller fact rows on day one * Every rate renegotiation silently rewrites all history * Closed-period totals drift and fail audit --- ## Common follow-up questions - A client disputes an invoice and a consultant's rate is corrected retroactively for one project last quarter. How does your model record that without editing the closed month? _(Tests whether corrections are signed adjustment rows rather than destructive updates to a closed fact.)_ - AFRY bills clients in their local currency while the consultant's rate is set in SEK. Where does the exchange rate live so historical invoices stay reproducible? _(Tests decomposing the measure into base amount plus a snapshotted conversion rate, the same point-in-time discipline applied to currency.)_ - How would you let finance see the rate that was in effect on any arbitrary past date for a given consultant and role? _(Tests effective-dated lookups with a BETWEEN on valid_from and valid_to and handling of the open-ended current row.)_ - At seven years of history, how do you partition and archive fact_time_logs so current-month dashboards stay fast? _(Tests date partitioning, pruning, and a hot/cold tiering strategy for old data.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/what-the-clock-remembers) - [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.