Taking an assessment rather than buying one? This page is written for employers. Here is the page for candidates.
Data engineering and analytics engineering · Mid level
How to assess a Analytics Engineer
A transformation take-home asks for code whose correctness is judged against a supplied expected output, which makes it both fully solvable by a model and irrelevant to the actual risk in this seat. The analytics engineer's characteristic failure is not broken SQL — it is correct SQL that encodes the wrong definition. A model built at customer grain when the business means account, an active flag that includes trialists, a revenue measure that nets refunds where finance does not: every one of those runs green, passes every generic uniqueness and not-null test, and produces a dashboard that disagrees with the ledger. Screening never asks the candidate to choose a definition under ambiguity and then defend it, so the one competency that matters most is the one nobody observes.
The analytics engineer sits between the pipeline and the dashboard and owns the thing neither of the seats on either side wants to own: what the columns mean. The data engineer guarantees the rows arrived. The analyst interprets what the rows say. In between, someone has to decide that a customer is the billing account and not the login, that revenue is recognised at invoice and not at payment, that a churned subscription that reactivated within thirty days was never churned, and then encode those decisions in a model that everyone downstream consumes without re-deriving them. That is the job, and it is much more a job of adjudication than of engineering.
The consequence is that the failure mode here is uniquely quiet. A data engineer's mistakes involve absent or duplicated rows and can be caught by counting. An analytics engineer's mistakes involve rows that are all present, all unique, and all wrong in the same consistent direction. Every test passes. The model is elegant. Finance's number and the growth team's number differ by four percent, nobody can explain why, and eventually one of them stops being used — which is how organisations end up with a warehouse that is technically excellent and commercially untrusted. dbt Labs' respondents named poor data quality their most frequent challenge, at 56 percent; a large share of what gets filed under data quality is definitional divergence of exactly this kind.
Grain is the technical expression of the same skill. Most catastrophic downstream errors in a transformation layer trace to a model whose grain was never explicitly stated: a join that was one-to-one in the sample and one-to-many in production, a dimension with an unclosed effective-dated history, a fact table that silently duplicates when a second address row appears. A strong candidate names the grain of every model before writing it and asserts it in a test. A weak candidate infers the grain from whether the numbers looked right the first time.
The third differentiator is refactoring under obligation. Analytics engineers inherit models that other people's dashboards depend on, and changing a definition is a social act as much as a technical one. The behaviour worth hiring is versioning the change, identifying who consumes it, and communicating what will move and by how much — rather than either shipping the correction silently or refusing to correct it.
Assessment for this role should therefore look different from the data engineer's, not be a variant of it. The data engineer is given a broken pipeline and asked to make it reliable. The analytics engineer should be given a brief containing a genuine definitional conflict — two stakeholders, two reasonable readings of the same word, and data that supports both — and be asked to model it, test it, and then explain in a spoken follow-up which reading they chose, what they would have had to know to be sure, and who they would go and ask. A candidate who assembled their SQL can produce the models. They cannot produce that conversation, because the conflict was never in the prompt they wrote.
What the job actually needs
- metric and dimension definition
- modelling for grain
- writing tests that assert business invariants
- refactoring without breaking downstream consumers
- negotiating a single definition between teams that each have one
How people fail in this seat
- builds a model at the wrong grain and fans out every downstream aggregate
- defines a metric that silently diverges from the finance definition
- tests for nulls but never for the business rule that actually matters
- leaves an untested model that becomes load-bearing
- accepts two stakeholders' conflicting definitions and ships both
What most employers do instead
CV screen for dbt and warehouse keywords, a SQL exercise on window functions or slowly changing dimensions, and an unmonitored take-home asking for a small set of transformation models.
The assessment
About 78 minutes end to end.
The systems it runs in
A dbt project on Snowflake, version-controlled, with a semantic layer that is part of the deliverable rather than scenery. `active_customers` has to be declared as a metric in MetricFlow, which is the tooling decision the whole design rests on: a definition that lives only inside a select statement cannot be shown to conflict with anybody else's, and the conflict is the assessment. The two rival readings are present in the forms they take in a real estate — finance's is a committed dbt model that feeds the month-end close, growth's is a LookML measure of the same name in the connected Looker project — so the disagreement is between two declared definitions rather than between two opinions. The tests are dbt's: the generic unique, not_null, relationships and accepted_values that every candidate reaches for, and the singular tests the top anchor requires, which is where a business invariant has to be written by hand.
- dbt Core
- dbt generic tests
- dbt singular tests
- dbt Semantic Layer with MetricFlow
- Snowflake
- Looker LookML
SQLMesh in place of dbt; BigQuery, Databricks SQL or Amazon Redshift in place of Snowflake. The semantic layer is the least standardised component in this stack and this file will not pretend otherwise — MetricFlow, Looker LookML, Cube and a Power BI semantic model are all in live use and none of them is dominant. Any of them can carry the fixture, because what the rubric reads is whether the candidate declared a definition, asserted it, and told the people downstream. What cannot be swapped away is having a semantic layer at all, and having somebody else's definition already inside it.
What the candidate actually does
| Task | What happens |
|---|---|
| Two definitions already exist and nobody says so coding_sandbox · 40 min | A small Snowflake warehouse — accounts, users, subscriptions, invoices, payments, addresses — and a dbt project with a brief that asks for a customer dimension, a monthly revenue fact, and an `active_customers` metric that the business can trust. The metric has to be declared in the semantic layer rather than left implicit in a select statement, which is what makes the choice a committed artefact somebody could disagree with. The repository already contains two things the brief does not mention. One is the finance team's month-end close model, in which revenue is recognised at invoice issue and a subscription that lapsed and reactivated inside thirty days was never churned. The other is a measure of the same name in the growth team's connected LookML project, in which revenue is recognised at payment and anyone in a trial counts as active. Both are committed, both are in use, and both are reasonable readings of the same words. The data supports either. Separately, the `addresses` table is effective-dated and one account has two open rows with no end date, so a naive join to build the customer dimension fans out the revenue fact — correctly, silently, and through every generic test. The fork. Choosing a definition without looking is faster, produces an elegant model, and passes uniqueness and not-null on every column. The tests are green. The numbers are internally consistent. Nothing in the brief suggests there is anything to find, and an assistant given this brief writes clean, correct SQL encoding whichever definition the prompt implied. The failure surfaces only when someone compares the output to the close pack. This is the analytics engineer's signature failure, and the fixture reproduces it exactly. |
| Lock your definition written_artifact · 10 min | Before the stakeholder call opens, the candidate writes and locks a short statement, timestamped — the grain of each model, the definition of revenue and of an active customer they have chosen, and the note they would send to whoever consumes the model. It is graded against what is actually in the repository and against what they actually built, not against a model answer. The fork. The comfortable version is a description of the model. The useful version names the choice as a choice, says which existing number will move and in which direction, and identifies who has to be told. Writing the second one commits the candidate to a position four minutes before somebody senior challenges it. |
| The controller who is four percent apart live_call · 20 min | A live spoken call with an AI finance controller who has run the candidate's model against the month-end close and found the two are about four percent apart. They are not hostile and they are not wrong. They want to know which number is correct and they would like it to be theirs, because theirs is the one that has been reported. They will accept a well-reasoned answer either way, including "yours is right and here is what I would change", and they will also accept a capitulation and thank the candidate for it. The fork. Agreeing with the controller ends the call in ninety seconds, is socially rewarded, and is sometimes correct. What separates candidates is whether the agreement or the disagreement is reasoned from the locked definition and the data, or produced by whoever spoke more confidently. The concession is scored against the locked statement and reported with its offset from the moment that caused it. |
| Changing a model four teams depend on judgment_scenario · 8 min | Four short cases about altering a definition already in use. A metric you own is wrong and the correction lowers a number the sales team is compensated on. A downstream dashboard depends on a column you want to drop. Two teams ask for opposite changes to the same model in the same week. A definition you inherited is defensible but not what the finance pack assumes, and nobody has complained. For each, the candidate says what they do first. The fork. Two of the four have a fast correct-sounding answer — fix it, and refuse it — and the discriminating behaviour in both is identifying who consumes the thing before touching it. The fourth case has no complaint attached to it, which makes doing nothing the path of least resistance. |
The mark scheme
Each criterion is scored 1 to 5 against written anchors, and every score is reported with the excerpt that earned it. A criterion marked floored is reported as a finding rather than averaged into the total. The first is open; open any other to read its anchors in full.
Notices and names the definitional conflictweight 0.3Goes looking before modelling, finds both, states that the two disagree and on exactly which cases — trialists, and the thirty-day reactivation — choo…
States the grain and asserts itweight 0.2Names the grain of every model before writing it, handles the unclosed effective-dated rows explicitly and says what they chose to do with the ambiguo…
Tests encode business rules, not shapesweight 0.15At least one test that would fail if the chosen definition were violated — active customers never exceeding total accounts, monthly revenue reconcilin…
Tells the people who depend on it flooredweight 0.2Names the choice, names at least one number that will change and the direction, names who reads it, and proposes how the change is introduced — versio…
Holds or concedes on evidenceweight 0.15Reconstructs the four percent from their own model — which cases account for it — says which of the two readings they think the business should adopt …
How it is scored
Weighted mean of five criteria, 1-5 against the anchors, each reported with the code excerpt, written line or transcript quotation that earned it. The fourth criterion is floored — a score of 1 on it is reported as a finding on the face of the report and never averaged away, because silently redefining a number other people are already using is the specific harm this seat exists to prevent, and a candidate who models beautifully and does that must not surface as a strong candidate. Reported raw and outside the mean: the timestamp of any concession in the call, given as an offset from the challenge that produced it. Also reported and explicitly annotated as not a positive signal: whether the candidate's revenue figure matches the close pack. In this fixture either reading can be defended, so agreement with finance is not the mark scheme, and a buyer who reads that column as the result has misread the design.
Integrity
- Monitored sandbox with statement log in execution order and file-open order, including whether the finance close model and the growth team's LookML measure were ever opened
- Edit timeline and paste-versus-typed provenance on the models and the tests
- Definition statement locked and timestamped before the call opens, and not editable afterwards
- AI assistance permitted and logged; the fixture is designed so that assistance produces clean SQL encoding an unexamined definition
- Same-day spoken call and follow-up on the candidate's own models
- No automated cheating verdict is produced. What is recorded is which files were opened and when, and what was said afterwards about work the candidate signed.
The log describes what happened. It does not produce a cheating verdict — the follow-up conversation is the control, because a statistical accusation is not something we would ask a reviewer to defend.
What you receive
- Models and tests as submitted, with edit timeline and file-open order
- The locked definition statement with its timestamp
- Call recording and transcript with the concession offset marked
- The four judgment responses as written
- Per-criterion score with the excerpt or quotation behind it
Who decides
Required rather than recommended. The central criterion asks whether a definitional choice was made deliberately, and the difference between a deliberate choice and a lucky one is a judgment about a written statement that no automated scorer should make. There is also a legitimate disagreement available in the call — a reviewer may reasonably think the candidate should have conceded — and that disagreement is useful information about the hiring team's own definitions rather than a defect in the score.
What this does not measure
This design does not measure work at estate scale. It does not observe someone untangling a warehouse of four hundred models, negotiating a definition across an organisation over months, or holding a semantic layer stable through a reorganisation, which is a large part of what a senior analytics engineer is hired for. It does not measure performance or cost engineering on real volumes; the fixture is small by design so that the definitional problem is the binding constraint rather than query runtime. It is dbt-shaped in its idea of a tested, version-controlled transformation layer, and a strong candidate from a stored-procedure estate may be slower for reasons unrelated to judgment — the anchors are written about grain, definitions and consumers rather than about any tool's syntax, and reviewers should hold to that. The call is spoken, often in a second language, and is scored on the content of the argument and never on fluency, accent, register or assertiveness: a quiet, exact disagreement scores at the top. The concession criterion also carries a cultural risk, because contradicting a finance controller is not equally safe in every workplace a candidate has come from; reviewers should look for the reasoning being surfaced at all, in any form, rather than for a confrontational style. Offer captions, extra time, or the call in written form on request.
The data engineer's mistakes involve rows that are missing or duplicated, and they can be caught by counting. The analytics engineer's mistakes involve rows that are all present, all unique, and all wrong in the same consistent direction. Every test passes. The model is elegant. Finance's number and the growth team's number differ by four percent, nobody can reconstruct why, and eventually one of them stops being used. That is why this assessment is not a variant of the data engineering one, and why building it as a variant would have been the obvious mistake. The two seats fail differently, so they have to be observed differently.
The whole design turns on one decision: the brief does not ask the candidate to find a definitional conflict. It asks for a metric the business can trust, which is what a real ticket says. The conflict is in the repository, in two files that are committed, in use, and never pointed at. If the brief said "identify any ambiguities in the definitions", the assessment would measure compliance with an instruction, and every candidate would comply, and the scores would be indistinguishable. Leaving it silent means what gets observed is whether the candidate goes looking — which is the actual competency and the one thing the standard take-home can never see, because the take-home's expected output contains the definition.
The grain trap runs underneath it as a second, technical expression of the same habit. One account has two open effective-dated address rows, so the natural join for a customer dimension duplicates that account through the revenue fact. It is not a trick: unclosed dimension history is the single most common cause of a silently doubling fact table in a real warehouse. A candidate who names the grain of a model before writing it and asserts it in a test never has the problem. A candidate who infers the grain from whether the totals looked right has it permanently, and the fixture is sized so that the fan-out moves the total by an amount that looks like ordinary variance rather than like a bug.
The call is where the design earns the rest of its weight, and the reason the definition is locked and timestamped beforehand is that without it, folding is unfalsifiable. A candidate who abandons their model's definition thirty seconds into a challenge from a controller can always say afterwards that agreement was the plan. With a locked statement, the concession is an event with a time on it, and it can be reported as one — conceded forty seconds after the close pack was mentioned — rather than dissolving into an average. What is being scored is not firmness. A candidate who concedes because the controller's argument is better scores at the top of that criterion; a candidate who holds because they have re-derived the difference and can name the cases it comes from also scores at the top. What scores at the bottom is either move made without re-examining anything.
One criterion is floored, and it is not the technical one. A candidate can build the model at the right grain, test it properly, choose a defensible definition, and still ship a number that silently differs from one the organisation is already using — no note, no version, no idea who reads it. That is the failure that produces a warehouse which is technically excellent and commercially untrusted, and it is precisely the failure a weighted mean would launder, because everything around it scores well. So a 1 on that criterion is reported as a finding on the face of the report, in the same way the regulated designs in this corpus report a withheld material term.
Sources
Every figure on this page is traceable. Where a claim could not be sourced it is stated qualitatively instead.
- dbt Labs, 2025 State of Analytics Engineering Report, 459 data practitioners and leaders surveyed 8 October to 27 December 2024, https://www.getdbt.com/resources/state-of-analytics-engineering-2025
- US Bureau of Labor Statistics, Occupational Outlook Handbook, Database Administrators and Architects, 2025, https://www.bls.gov/ooh/computer-and-information-technology/database-administrators.htm
See what the employer actually receives. A full report for one role, with every score shown beside the excerpt that earned it, conduct findings reported rather than averaged, and a reviewer sign-off required before any decision. No form.
Read a sample reportOr talk to us about this role