Alabama Staffing Data Scientist Interview Questions & Guide 2026

The 60-second brief

As a Data Scientist at Alabama Staffing, you will play a foundational role in how we connect top talent with the right opportunities. In the highly competitive staffing industry, data is our most valuable asset. Your work directly influences how we source candidates, predict market trends, and optimize our matching algorithms to ensure long-term success for both job seekers and employers.

How much statistics you need depends on the flavour of the seat. Experiment-facing work wants you deep enough to notice that repeated looks at accumulating data inflate the false positive rate of a fixed-sample test; modelling-facing work wants estimation and honest uncertainty intervals.

Focus areas

• Correct late-entered timesheets before trending recent weeks
• Separate bookings, recognised revenue and collected cash
• Cluster inference at the account, not the engagement

Overview

This position offers a unique blend of technical execution and strategic business impact. You will not just be crunching numbers; you will be building predictive models that reduce time-to-hire and improve the quality of placements. The scale of our candidate databases and the complexity of human career trajectories make this an incredibly rich problem space for a data professional.

Expect a role where your insights actively shape the daily operations of our recruiting teams. You will work closely with engineering, product, and operations to translate raw data into actionable intelligence. If you are passionate about foundational data science and want to see your models directly impact people's livelihoods, this role provides the perfect platform.

The Alabama Staffing interview process

Alabama Staffing candidates report 3 rounds · ≈ 3-5 weeks. The stages below are what candidates describe, not a published process.

Initial Recruiter Screen

A screening call is a matching exercise run by someone who will not evaluate your statistics. They are checking that the work described on your resume is work you personally did, and that its scope matches the level the role is written for. Logistics get settled in the same half hour so nobody spends an interviewer's afternoon on a mismatch. The answer that fails is the one narrated in the plural. If every sentence is 'we built' and 'the team decided', there is nothing specific to write down about you. Name the piece that was yours, the decision you made inside it, and what changed after.

What to demonstrate

• Whether the ownership implied by your resume survives one round of follow-up about who actually did which part
• Whether your described scope (data size, stakeholders, what shipped) matches the seniority the role is written at
• Whether timeline, location and compensation expectations make the rest of the loop worth scheduling

How to prepare

• Rewrite your top three resume bullets in the first person singular, each with the decision you made and what moved afterwards, then say them out loud once so the 'we' does not return under pressure
• Attach one number to each project: the baseline, the change, and the window it was measured over. Where impact was never measured, say that plainly rather than inventing a figure
• Settle your compensation range before the call and give it as a range with a reason behind it, such as current total comp or a competing timeline, instead of deflecting the question twice

Technical Round

This round decides whether someone can hand you a schema and a question and trust the number that comes back. Correctness under a clock is the bar, not clever syntax. The habit that separates strong from weak answers is checking the grain: after every join, know how many rows you expect and whether the count moved. Most wrong answers in this format are not wrong logic, they are a fan-out from a key that turned out not to be unique, or a filter applied before an aggregate when it belonged after. Say what you expect before you run it.

What to demonstrate

• Whether your row counts survive each join, and whether you notice on your own when they do not
• Deliberate handling of rows that fail to match, including whether the question needs an inner join or a left join with the non-matches kept and counted
• Whether NULLs are treated on purpose, given that a NULL compares equal to nothing and that COUNT of a column skips it
• Reaching a defensible answer inside the window instead of a refined one after it

How to prepare

• Take a two-table schema, write a join that fans out on purpose, then fix it by collapsing the many-side to one row per key before joining. Repeat until the fix is reflex rather than recall.
• Write a funnel as one query and print the distinct user count at each stage, then confirm each stage is a subset of the one above it rather than assuming it
• Do a few timed runs in a plain text box with no autocomplete and no formatter, since assessment editors often have neither

Behavioral Round

Rounds of this kind usually include one question about work that did not go well, and it is the part that carries the most information. Anyone can narrate a shipped win. What the interviewer learns from a project that stalled is how you behave without a result to hide behind: whether you noticed the problem yourself, how long it took, and who you told. Answers that route the failure onto a data pipeline or a reorganisation close the topic without answering it, and the follow-up comes back to your own part.

What to demonstrate

• Whether you found the error yourself or someone else found it, and how long it sat before anyone knew
• What you changed afterwards, stated as a check you now run rather than a lesson you now believe
• Whether the mistake you choose has real cost attached, such as a quarter of misdirected roadmap or a metric that was reported upward, instead of one that flatters you

How to prepare

• Choose a failure you caught yourself and be ready to say what tipped you off. A story where someone else caught it is still usable, but you will be asked why you missed it.
• Write down the check you added afterwards and where it lives now, so the correction is a concrete artefact rather than a resolution.
• Rehearse saying the cost out loud. Candidates shrink the number by instinct once the interviewer is in the room.

Where candidates lose points

Pooling margin, realisation or overrun across pricing models

Fixed-fee margin falls with hours worked; uncapped time-and-materials margin rises with hours worked; retainer margin depends on neither. A quarter in which the firm sells more fixed-fee work will show a margin change caused entirely by mix, not by delivery performance, and the aggregate can move in the opposite direction to every individual pricing model. Always stratify by fct_engagement.pricing_model before comparing periods, and report the mix shift alongside the within-stratum change.

Modelling win rate on proposals with a recorded outcome, using fields written after the decision

Two failures compound here. First, stage IN ('withdrawn','no_decision') is not missing at random: those are disproportionately deals that were going to be lost, so training on won-plus-lost only inflates apparent win rate and distorts the coefficients. Second, fields like engagement_id, final scope and revised pricing are populated after the outcome is known, so including them leaks the label and produces a model with excellent backtest accuracy and no forward value. Restrict features to values knowable at submitted_at.

Over-explaining the method and under-explaining the implication

Lead with the answer and what you would do about it, then give the approach when asked. Roughly one sentence of method per three of implication is the right ratio for a stakeholder-facing answer; the interviewer already knows what a regression is.

Building features from data that postdates the prediction time

Check every feature against the timestamp at which the model would actually score, and drop anything computed from a window that includes or follows the label event. For a forecasting use case, split train and test by time rather than at random, and split by entity when the same entity recurs.

Questions & practice

Explain the concept of p-value to someone with no statistical background.

Medium · statistics and probability

Approach

1. Write down the assumption the method needs before you use the method.
2. Translate the result into the decision it informs, in one plain sentence.
3. Quantify uncertainty explicitly rather than reporting a point estimate alone.

Follow-up

• Which assumption here is most likely to be violated in practice?
• What sample size would you need to detect an effect half this size?

How would you approach building a model to predict candidate retention?

Medium · machine learning and modelling

Approach

1. Check what information would not exist at prediction time, and exclude it.
2. Pick an evaluation metric that matches the cost of each error type, not a default.
3. Set a baseline first, so any model has something honest to beat.

Follow-up

• How would you choose the decision threshold, and who owns that choice?
• Where could label leakage enter this setup?

How do you check if a linear regression model is a good fit for your data?

Medium · machine learning and modelling

Approach

1. Check what information would not exist at prediction time, and exclude it.
2. Pick an evaluation metric that matches the cost of each error type, not a default.
3. Frame the prediction: the label, the moment of prediction, and the action it triggers.

Follow-up

• Where could label leakage enter this setup?
• What would you monitor after launch to know the model is still valid?

Attach effective-dated cost rates without merge_asof

Medium · type-2 scd · interval join · searchsorted

time_entries (consultant_id, work_date, hours) has roughly two million rows. dim_consultant is a type-2 dimension (consultant_sk, consultant_id, standard_cost_rate_usd, effective_from, effective_to nullable, is_current) with a handful of rows per consultant. Attach the cost rate in force on each entry's work_date. You may not use pandas.merge_asof and you may not materialise the consultant_id cartesian product. Treat intervals as half-open, meaning [effective_from, effective_to), with a NULL effective_to open-ended. Return unmatched entries separately with a reason rather than dropping them.

Approach

1. Fix the semantics before writing code: half-open intervals are what make the join single-valued on the day a rate changes. Compare work_date at start-of-day in the reporting timezone against effective_from in the same zone, and write that comparison down as a stated assumption.
2. Sort dim_consultant by (consultant_id, effective_from) and assert non-overlap inside each consultant, since searchsorted only returns the right answer if the intervals are ordered and disjoint.
3. Build the composite sort key so it cannot overflow: dense-code the consultant ids through one shared sorted category mapping, express time as whole days since a floor date earlier than every date in both frames, and take SCALE as a power of two above the largest day offset, giving key = code * SCALE + offset. Assert code.max() * SCALE + off.max() < np.iinfo(np.int64).max before using it. Multiplying a raw consultant_id by a scale larger than a nanosecond timestamp (about 1.75e18 for present-day dates) leaves an id budget of only int64_max / SCALE, roughly five consultants, before the product passes 9.22e18; beyond that numpy wraps silently, and it does not even wrap consistently negative (with SCALE = 2e18 an id of 12 lands back on a large positive key), so the order breaks without a sign flip to catch it.
4. With a key that fits, np.searchsorted(dim_key, entry_key, side='right') - 1 is the index of the last interval starting at or before work_date, in O((n + k) log k) and with no cartesian product. SCALE only has to exceed the span of the offsets, never an absolute epoch value, so if a rate can change intraday, switch the offset unit to seconds (a 2^31 scale covers 68 years) and re-run the same assertion.
5. Validate the candidate rather than trust it: the match holds only if work_date < effective_to or effective_to is NULL. Everything else is an exception, either work logged before the first dimension row or a genuine gap in the SCD.
6. Return matched rows carrying consultant_sk and the rate, plus an exceptions frame with counts by reason, so a gap in the dimension surfaces as a number rather than as quietly missing cost.

Worked solution (30 min)

1. dim = dim.sort_values(['consultant_id','effective_from']); assert within each consultant that effective_from is strictly increasing and effective_to.fillna(max) >= the next effective_from.
2. cats = np.union1d(time_entries.consultant_id.unique(), dim.consultant_id.unique()), which is sorted, so code = pd.Categorical(ids, categories=cats).codes.astype('int64') preserves consultant_id order and the sort above still holds; an id absent from the dimension codes to -1 instead of matching something else.
3. floor = min(work_date.min(), effective_from.min()).normalize(); off = (ts.dt.normalize() - floor).dt.days.to_numpy() in both frames; SCALE = 1 << 20 (1,048,576 days, about 2871 years); assert off.min() >= 0, off.max() < SCALE and code.max() * SCALE + off.max() < np.iinfo(np.int64).max; key = code * SCALE + off.
4. idx = np.searchsorted(dim_key, entry_key, side='right') - 1; entries whose code is -1 are exceptions with reason 'consultant_not_in_dimension'; rows with idx < 0, or whose matched dimension code differs from the entry's code, are exceptions with reason 'no_interval_before_work_date'.
5. Check the upper bound: keep rows where effective_to is NaT or work_date < effective_to; the rest are exceptions with reason 'gap_in_scd'.
6. Brute-force 200 random entries with a straight boolean filter over that consultant's rows and diff the rates against the vectorised answer.

Expected result: Matched rows equal to the input count minus exceptions, each carrying exactly one consultant_sk and standard_cost_rate_usd, plus an exceptions frame grouped by reason. No input row is duplicated and none is silently dropped.

Verify it yourself

• len(matched) + len(exceptions) == len(time_entries), which is the property an inner join would break.
• The 200-row brute-force sample agrees on every rate.
• An entry whose work_date equals an effective_from takes the new rate, not the old one, exactly once.
• Per-consultant total cost changes by (new_rate - old_rate) * hours after the change date, and by nothing before it.
• np.diff(dim_key) is strictly positive, asserted rather than assumed: this is the only cheap guard against a key that has wrapped, since an overflowed product can land back on a positive value and a non-negativity check alone would pass it. Renumbering the consultant_ids (say to 4000001..4000050) must not change a single attached rate, which is the fixture a raw-id-times-nanoseconds key fails once ids run past int64_max / SCALE.

Follow-up

• Rates were backdated a quarter after the fact. How do you keep last quarter's published cost from moving when someone reruns the report?
• How does the implementation change if effective_to is inclusive rather than exclusive, and what breaks on the boundary day if you get that wrong?

These questions evaluate your hands-on ability to extract and clean data.

Medium · sql and data manipulation

Approach

1. Say which table is the grain you start from, and join outward from it.
2. Check whether any join is one-to-many before aggregating, or the sums inflate.
3. Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.

Follow-up

• How does the query change if the join becomes one-to-many?
• How would you verify this result without re-running the same query?

What is the difference between WHERE and HAVING in SQL?

Medium · sql and data manipulation

Approach

1. Say which table is the grain you start from, and join outward from it.
2. State the window function and its partition and ordering out loud before writing it.
3. Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.

Follow-up

• How would you verify this result without re-running the same query?
• How does the query change if the join becomes one-to-many?

Write a query to find the second highest salary from an employee table.

Medium · sql and data manipulation

Approach

1. State the window function and its partition and ordering out loud before writing it.
2. Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
3. Compute rates by summing numerator and denominator separately, never by averaging rates.

Follow-up

• How does the query change if the join becomes one-to-many?
• How would you verify this result without re-running the same query?

Build a client cohort revenue table from invoice lines

Easy · cohorts · date truncation · revenue recognition

dim_client holds client_id and first_engagement_date. fct_engagement maps engagement_id to client_id. fct_invoice_line holds engagement_id, line_type, period_start_date, amount_usd and status; recognised fees are lines with line_type IN ('fees','milestone','credit_note') and status <> 'draft', and credit notes carry negative amount_usd. Return one row per (cohort_month, months_since_first) giving distinct clients billed and net recognised fees. cohort_month is the month of first_engagement_date; months_since_first is the whole-month gap between period_start_date and first_engagement_date. Long format, no pivot. Decide explicitly what to do with clients whose first_engagement_date is NULL.

Approach

1. Filter fct_invoice_line to recognised lines first (line_type, status <> 'draft'), and key recognition on period_start_date rather than issued_at or paid_at. Billing period, issue date and payment date are three different clocks, and mixing them produces a cohort table nobody can reconcile against finance.
2. Join invoice lines to fct_engagement to reach client_id, since invoice lines carry engagement_id only, then to dim_client for first_engagement_date.
3. Truncate both dates to month, then compute months_since_first as 12 * (year(bill_month) - year(cohort_month)) + (month(bill_month) - month(cohort_month)). Doing it on truncated months is what keeps a client billed on day 2 of the third month in bucket 2 instead of bucket 1.
4. Aggregate to (cohort_month, months_since_first) with SUM(amount_usd) and COUNT(DISTINCT client_id), keeping credit_note lines inside the sum so the figure is net rather than gross.
5. Handle NULL first_engagement_date as an explicit exclusion and report how many clients and how much revenue that removes, rather than letting the join drop them silently. That exclusion is why the output's total is smaller than the filtered line set's total, so carry the excluded figure as a named number rather than discovering the gap later.

Worked solution (25 min)

1. Write the recognised-lines CTE with the line_type and status predicates, and check its row count against the unfiltered table so you know how much you excluded.
2. Join through fct_engagement to dim_client and confirm the join does not multiply rows: a line has exactly one engagement, an engagement exactly one client.
3. Add the two date_trunc expressions and the integer month arithmetic, then eyeball ten rows against their source dates.
4. Group and aggregate, then sort by cohort_month and months_since_first.
5. Run a separate count of clients with NULL first_engagement_date, and a separate sum of their recognised fees, and note both beside the output.

Expected result: A long-format table of (cohort_month, months_since_first, active_clients, net_fees_usd), plus two scalars reported beside it: the count of clients with a NULL first_engagement_date and the recognised fees attached to them. months_since_first is a non-negative integer on every row; a negative value means an invoice period precedes the recorded first_engagement_date, which is a data-quality finding to report, not something to clamp to zero.

Verify it yourself

• SUM(net_fees_usd) over the whole result equals SUM(amount_usd) over the filtered lines whose engagement resolves to a client with a non-NULL first_engagement_date. It does not equal SUM(amount_usd) over the full filtered line set, which is larger by exactly the excluded NULL-cohort revenue: add that separately reported figure back and the two agree. Any remainder after that is a line whose engagement_id has no matching row in fct_engagement, which is a referential finding to name rather than absorb.
• COUNT(DISTINCT client_id) at months_since_first = 0 within a cohort_month is less than or equal to the number of clients whose first_engagement_date falls in that month.
• Zero rows have a negative months_since_first; if any appear, name the clients rather than filtering them out.
• Re-running with period_end_date in place of period_start_date moves only lines whose billing period spans a month boundary.

Follow-up

• How would you turn this table into a 12-month net revenue retention ratio, and what exactly becomes the denominator?
• One account contributes 40 percent of a cohort's month-13 fees. How do you present the cohort so a reader sees that before they act on the ratio?
• If subsidiaries roll up to parent_client_id, which first_engagement_date defines the cohort, and does the rollup change the cohort a subsidiary lands in?

Diagnose a sample ratio mismatch before reading the result

Easy · sample ratio mismatch · randomisation integrity · chi-square test

A staffing-recommendation tool was randomised one to one across 840 billable-role consultants, with assignment drawn from a hash of consultant_id, so 420 sit in each arm and the assigned arm is recoverable for every consultant. The analysis table, built from exposure logs, holds 300 treatment and 420 control consultants: 720 rows against 840 assignments. Treatment shows a 1.9-point utilisation lift at p = 0.01, and the sponsor wants it in the deck tomorrow. Run the right test on the allocation, quantify how far the split sits from 1:1, decide whether the lift can be reported, and name the two most likely mechanisms given that the table came from exposure logs rather than the assignment table.

Approach

1. Test the split before touching the outcome. Chi-square goodness of fit on the 720 retained rows against an expected 360/360 gives chi-square = 2 * (60^2 / 360) = 20.0 on 1 degree of freedom, p around 7.8e-6. The equivalent binomial z is 60 / sqrt(720 * 0.25) = 4.47, and z^2 = 20.0 reproduces the chi-square.
2. Then run the sharper test that a recoverable assignment makes available: per-arm retention against the known 420. Control retains 420/420 = 100%, treatment retains 300/420 = 71.4%. That 28.6-point gap says more than the split test does, because it names which arm lost rows and how many, rather than only that the ratio is off.
3. Use a much stricter alpha for the allocation test than for the outcome, commonly 0.0005. The split test protects no multiplicity budget and should fire only on real breakage; 7.8e-6 clears even that bar by a factor of about sixty.
4. Treat the outcome as unreportable rather than as interesting-with-a-caveat. Under a sample ratio mismatch the analysis population is no longer the randomised population, so the comparison is observational and the lift carries no protection from confounding.
5. Trace the mechanism from the table's provenance, starting from the fact that the deficit is one-sided. A symmetric filter, such as an inner join to dim_consultant on is_current = TRUE, drops rows from both arms at similar rates: it shrinks n without moving the ratio, so it cannot be the cause here and should be ruled out rather than listed. Two one-sided candidates fit. First, the exposure log records a treatment consultant only once they open the tool, so non-openers vanish from treatment and the arm is silently conditioned on engagement, which is a post-assignment filter. Second, the arms are populated by different loggers: treatment exposure is written by the tool's own client-side beacon while control exposure is written server-side by the staffing screen, so the arms differ in logging coverage rather than in behaviour.
6. Rebuild on intention to treat from the assignment table, so all 840 assigned consultants sit in their assigned arm whether or not they opened the tool, then re-run the split test before re-running the outcome.

Worked solution (15 min)

1. Reconcile row counts first: the assignment table puts 420 in each arm, the analysis table holds 300 and 420, so 120 rows are missing and every one of them is on the treatment side.
2. Expected under 1:1 on the 720 retained rows: 360 per arm, observed deviation 60 in each direction.
3. chi-square = (300 - 360)^2 / 360 + (420 - 360)^2 / 360 = 10 + 10 = 20.0 on 1 degree of freedom.
4. Convert: z = sqrt(20.0) = 4.47, two-sided p = 2 * (1 - Phi(4.47)) = 7.8e-6.
5. Observed treatment share 300/720 = 0.4167; 95% interval 0.4167 +/- 1.96 * sqrt(0.4167 * 0.5833 / 720) = 0.4167 +/- 0.0360, so [0.381, 0.453], excluding 0.5.
6. Retention by arm against assignment: control 420/420 = 1.000, treatment 300/420 = 0.714. The loss is entirely one-sided, which is the signature of a treatment-side filter rather than of noise.

Expected result: chi-square = 20.0 on 1 degree of freedom, p around 7.8e-6, treatment share 0.4167 with a 95% interval of roughly [0.381, 0.453] that excludes 0.5, and a one-sided retention gap of 100% control against 71.4% treatment. The allocation is broken and the utilisation result is not reportable until the analysis table is rebuilt from assignment rather than exposure.

Verify it yourself

• The chi-square and the binomial z must agree: z^2 = 4.47^2 = 20.0 = chi-square.
• Arm counts must be reconciled against the 840 randomised consultants, not only against each other. They sum to 720, so 120 rows were dropped, and the drop sitting entirely on one arm is what makes it a filter.
• A candidate mechanism must be capable of producing a one-sided deficit. Any filter applying equally to both arms shrinks n while leaving the ratio near 1:1, so it fails this test and is not the explanation.
• Re-running the split test on the assignment table rather than the exposure table should come back non-significant if the hash itself is sound.

Follow-up

• Suppose the analysis table had held all 840 consultants, split 402 to 438. Run the same test and tell me whether you would report the result.
• If non-openers are themselves a treatment effect, because the tool is bad enough that people ignore it, what does intention to treat estimate, and what would you need to estimate the effect on compliers?
• How would you monitor for sample ratio mismatch continuously without creating a peeking problem on the outcome?

Size a rate-card pilot when accounts are the cluster

Easy · power analysis · minimum detectable effect · cluster randomisation · intraclass correlation

The firm wants to pilot a revised rate card. Assignment happens at the client account: one rate card per account, every engagement on it inherits that card. The outcome is realisation rate per engagement, fees billed over the standard-rate value of approved billable hours. You have 120 eligible accounts averaging three engagements each inside the pilot window, a per-engagement standard deviation of 0.12, and an intraclass correlation of 0.35 within account. Leadership will change the card only if realisation moves by at least 2 percentage points. Two-sided alpha 0.05, 80% power. Say whether this pilot can answer the question.

Approach

1. Write the minimum detectable effect for a two-sample difference in means with equal allocation: MDE = (z_0.975 + z_0.80) * sigma * sqrt(4/N), coefficient 2.802, and be explicit that N counts engagements while randomisation happens on accounts.
2. Convert engagements into independent information with the design effect DEFF = 1 + (m - 1) * rho. At m = 3 and rho = 0.35 that is 1.70, so 360 engagements carry the information of roughly 212.
3. Compute the MDE on the effective sample, compare it to the 2-point decision threshold, and express the shortfall as a sample-size ratio rather than a vague 'underpowered'.
4. Test whether a longer window rescues the design. Effective observations per cluster are capped at 1/rho, so total effective sample asymptotes at 120/0.35 = 343 no matter how long the pilot runs. Duration cannot fix an ICC problem; only more clusters can.
5. Price the real options: more accounts, variance reduction from a pre-period covariate, a larger accepted effect size, or a design that uses within-account variation instead of between-account randomisation.

Follow-up

• Revenue is concentrated in a handful of accounts. How does a value-weighted estimate change the power calculation, and which estimand does leadership actually want?
• The ICC of 0.35 came from last year's data on the same accounts. What would make that estimate optimistic, and how would you stress it?
• What breaks if you randomise at the engagement instead of the account?

Two-year-old engagement margins moved after a dimension join change

Hard · type-2 scd · restatement · effective dating

After a warehouse release, gross margin on engagements closed two years ago changed by up to six points, upward on some and downward on others. The cost model now joins dim_consultant on consultant_id AND is_current = TRUE to read standard_cost_rate_usd, instead of using the cost_rate_usd already snapshotted on each fct_time_entry row. Available columns include consultant_sk, consultant_id, level, standard_cost_rate_usd, effective_from, effective_to, is_current, termination_date. Explain the restatement, quantify who it favours, and specify the join you would ship.

Approach

1. State the mechanism in one line: joining a type-2 dimension on is_current pastes today's cost rate onto historical hours, so every consultant promoted or re-rated since restates their own history in the direction their rate moved. Because promotions differ per person, engagement margins move in both directions, which is exactly the observed pattern.
2. Quantify with three parallel cost computations per engagement: (a) the snapshotted fct_time_entry.cost_rate_usd, (b) an effective-dated join using work_date BETWEEN effective_from AND COALESCE(effective_to, 'infinity'), and (c) the shipped is_current join. Report the distribution of (c) minus (a) and correlate it with the share of hours delivered by staff who have since been promoted.
3. Check cardinality as a separate defect: an is_current join can drop consultants whose current row is missing or whose termination retired the row, and can fan out if consultant_id carries more than one current row. Compare hour totals before and after the join; they must be identical.
4. Decide the canonical source and justify it: the snapshotted rate on the time entry is what was booked and is authoritative for financial restatement. The effective-dated join is the reconciliation, and a systematic disagreement between (a) and (b) is its own defect worth raising rather than papering over.
5. Reissue the affected periods with an explicit changeover note, and pin the rule in a test that fails if is_current appears in any join from a fact table to a type-2 dimension on a historical grain.

Follow-up

• Some entries have cost_rate_usd NULL or zero. Which source wins then, and what does that do to your reconciliation?
• The same pattern exists for bill_rate_usd. What does it do to realisation, and in which direction?
• How would you sweep every model in the warehouse for this class of bug without reading each one?

Suggested practice checklist

Roughly 90 minutes a night on weekdays with one longer weekend block. The plan deliberately cuts scope rather than compressing everything, on the assumption that finishing one thing a night beats half-starting four.

Day 1 — Fix the scope and set a baseline

• Read the role description and write the three things the loop will almost certainly test, then write an explicit not-doing list for everything else and keep it visible all week.
• Take one 20-minute SQL prompt and one 10-minute metric question cold, and write the single sentence that says what blocked each attempt, since that sentence is what decides which two topics get the most evenings.
• Set the week's one rule: one problem finished to completion every night, including the night you only have 40 minutes.

Deliverable: A one-page scope with an explicit not-doing list and two cold attempts, each carrying one sentence on what blocked it.

Day 2 — One query pattern, written three times

• Choose the single pattern most likely to appear (a cohort retention grid, or a funnel counted by user) and write it three times from a blank file rather than editing the previous attempt.
• On the third attempt, write the grain of every CTE as a comment before writing its body.
• Stop at 90 minutes even if the third version is imperfect, and write the one thing you would fix with another hour.

Deliverable: Three independent versions of the same query plus a note on what changed between them.

Day 3 — Only the statistics you will be asked to defend

• Write, in under 200 words, how you would decide whether a difference between two groups is real: the test, its assumptions, and what you would switch to when an assumption fails.
• Compute a 95 percent confidence interval for a difference in proportions by hand on realistic numbers, then write in one sentence what changes if the two samples are paired rather than independent.
• Write your answer to "what does a p-value mean", check it against a definition, and delete the version that describes it as the probability the hypothesis is true.

Deliverable: A 200-word written answer and one hand-computed interval you can reproduce under pressure.

Day 4 — One case, and the assumptions holding it up

• Answer one product case aloud in 20 minutes with a recording running, then listen back with a pen and mark every claim you asserted without saying what it rested on: an assumed user behaviour, an assumed data source, an assumed baseline rate, an assumed grain.
• Pick the three assumptions the recommendation actually depends on, write how you would check each one against data, and say which one being wrong would flip the recommendation rather than merely weaken it.
• Write the four-step structure you used onto a card small enough to hold in working memory when you are nervous.

Deliverable: One recording, three load-bearing assumptions each with a written check, and a four-step structure card.

Day 5 — Your own work, timed

• Write a 90-second version and a four-minute version of your main project, and time both out loud rather than reading them.
• Prepare answers to the two follow-ups that always come: what you would do differently, and how you knew it worked.
• Put one number in the first sentence and be able to say exactly where that number came from and what it excludes.

Deliverable: Two timed narratives with one defensible number in the opening line.

Day 6 — The one full rehearsal, in a longer weekend block

• Run a 60-minute mock covering query work, a case and a behavioural question in a single sitting with no breaks, because sustained attention is the thing evenings have not trained.
• Immediately afterwards, and before hearing any feedback, write the three moments you lost the thread.
• Spend the rest of the block only on those three moments, and on nothing you merely feel shaky about.

Deliverable: Mock notes naming three failure moments with a specific fix written under each.

Day 7 — Taper

• Write the 20-minute warm-up you will actually do on the morning of the interview: one query you can already write from a blank file, one metric you can define out loud, and nothing you have never seen before.
• Re-read only your own notes from this week, and open no new material.
• Write down the logistics: the tool you will be asked to work in, whether lookups are allowed, and the sentence you will use when you do not know something.

Deliverable: A one-page card holding the case structure, the project numbers, and the logistics.

Getting an analysis acted on

Work that nobody used is a common and unflattering pattern in data careers, and interviewers probe for it. Have a story about an analysis that changed a decision, and be specific about how you got it in front of the person who could act. Also have one about work that went nowhere, with your reading of why.

How do you handle outliers in a dataset before feeding it into a model?

Medium · behavioural and stakeholder questions

Approach

1. Name the disagreement or constraint, and how you resolved it with evidence.
2. Pick a story where you drove the decision, not one where you observed it.
3. Quantify the outcome, including what you would not claim credit for.

Follow-up

• How did you know the outcome was caused by your change?
• What did you decide not to do, and why?

These assess your problem-solving approach and culture fit.

Medium · behavioural and stakeholder questions

Approach

1. Close with what you would do differently, concretely.
2. Quantify the outcome, including what you would not claim credit for.
3. Pick a story where you drove the decision, not one where you observed it.

Follow-up

• How did you know the outcome was caused by your change?
• What would you do differently if you ran that project again?

Disagreeing with a proposed utilisation target using realisation evidence

Medium · realisation · guardrail metrics · observational evidence

A delivery lead proposes raising the billable utilisation target for analyst through senior_consultant from 72 to 85 percent. You have fct_time_entry, including is_billable, bill_rate_usd and written_off_hours, and fct_invoice_line. You believe the target will raise reported utilisation and lower fees. Prepare the disagreement: the evidence you pull, the mechanism you name, the metric pair you propose instead, and the condition under which you would concede that the target is correct.

Approach

1. Name the probe: whether you disagree with a mechanism and a measurement, or with an opinion about a metric being bad.
2. State the substitution precisely. Utilisation counts approved hours with is_billable = TRUE. An hour that is charged to the client and later written off stays in that numerator, so utilisation is unaffected while realisation, fees divided by hours times bill_rate_usd, falls and margin falls with it. That is the exact channel by which a higher target can raise the reported number and lower revenue.
3. Pull the evidence at consultant-month grain: plot realisation and the write-off share, written_off_hours over billable hours, against utilisation decile. If the current top decile already shows lower realisation, the proposed target moves a large share of the staff into that regime.
4. Stratify before concluding. Fixed_fee teams can show high utilisation and high realisation for reasons that have nothing to do with the proposal, so run the comparison within pricing_model and report the mix.
5. Propose the pair rather than the veto: utilisation published with realisation and write-off rate as standing guardrails, with the threshold at which the combination is net positive stated in advance. Then name your concession condition: if the top utilisation decile shows no realisation penalty and bench hours are the binding constraint, the target is right and you will say so.

Follow-up

• Utilisation and realisation are computed from overlapping hours. Does that make the relationship you found mechanical rather than behavioural?
• How many consultant-months would you need to detect a three-point realisation move, and does the firm have them?