Analytics engineer interview questions and answers

Two people walked into the Monday meeting with revenue for the same quarter. The numbers differed by about four percent. Both were computed correctly. One excluded refunds issued in the following month, the other did not, and neither definition was written down anywhere, so the meeting became a ninety-minute forensic exercise instead of a decision.

Preventing that meeting is the actual job, and it is why analytics engineer interview questions look different from both data engineering and analyst interviews. You are asked about SQL and modelling, yes — but the differentiating round is whether you can define a metric so precisely that two teams cannot disagree about it, and then encode that definition somewhere it is enforced. This guide covers the loop.

The analytics engineer interview loop

• SQL round — window functions, CTEs, complex joins, and explaining a query rather than only writing it.
• Data modelling round — layered transformation design, dimensional modelling, grain and slowly changing dimensions.
• dbt or transformation-tool round — materialisations, incremental models, tests, sources, documentation and lineage.
• Data quality round — what you test, and what happens when a test fails at 6am.
• Stakeholder round — metric definitions, conflicting numbers, and communicating with non-technical users.

Our data engineer interview questions guide covers the upstream role and data analyst interview questions covers the downstream one.

What an analytics engineer is

Answered early and often. The framing that works: data engineers move and land data reliably; analysts answer business questions; the analytics engineer owns the transformation layer in between — turning raw landed data into clean, tested, documented, well-modelled tables that analysts and dashboards can trust.

The role exists because the modern warehouse made ELT cheap, which moved transformation into SQL inside the warehouse and created a job that is software engineering practice applied to analytics: version control, testing, code review, CI and documentation.

SQL depth

Expect harder SQL than a general analyst round:

• Window functions, in depth. Running totals, ranking, LAG and LEAD, and frame clauses. Our SQL window functions interview questions guide covers them fully.
• CTEs versus subqueries, readability, and whether your warehouse materialises CTEs.
• Join fan-out. The most common real bug: joining to a table with multiple matching rows silently multiplies your measures. Be able to explain how you detect it — row counts before and after, and a uniqueness test on the join key.
• Deduplication. ROW_NUMBER partitioned by the business key ordered by a recency column, then filter to 1. Know why DISTINCT is usually the wrong fix.
• Date logic. Fiscal calendars, timezone conversion, and generating a date spine so days with no events still appear.
• Incremental logic. Late-arriving data, and why filtering on an event timestamp misses records that landed after your last run.

Data modelling

This is the distinguishing round.

• The layered approach. Staging (one model per source, light renaming and casting only), intermediate (joins and business logic), and marts (facts and dimensions the business consumes). Be able to say what belongs in each and why staging should never contain business logic.
• Star schema. Facts hold measures and foreign keys at a declared grain; dimensions hold descriptive attributes. State the grain of every fact table out loud — "one row per order line" — because an undeclared grain is the source of most double-counting.
• Slowly changing dimensions. Type 1 overwrites, type 2 keeps history with validity dates. Know when you actually need type 2, which is less often than people assume, and what it costs in query complexity.
• Wide tables versus strict star. On a columnar warehouse, a wide denormalised table often outperforms a textbook star. Have a view and acknowledge the tradeoff — this is a genuinely current debate and interviewers like hearing both sides.
• Surrogate keys and why you generate them rather than relying on source system IDs that can change.
• Semantic layer. Where a metric definition lives so that dashboards, notebooks and ad-hoc queries all compute revenue the same way. Our data modeling interview questions guide covers the fundamentals.

dbt and the transformation layer

• Materialisations. View, table, incremental, ephemeral — and the cost and freshness tradeoff of each.
• Incremental models. The is_incremental pattern, choosing a unique key, and handling late-arriving records with a lookback window rather than a strict watermark.
• Tests. Built-in tests for uniqueness, not-null, accepted values and relationships, plus custom singular tests for business rules like a total that must equal the sum of its parts.
• Sources and freshness. Declaring sources so lineage is complete, and freshness checks that catch an upstream pipeline that silently stopped.
• Refs and DAG. Why you always use ref rather than a hardcoded table name, and what that buys you in lineage and environment isolation.
• Documentation and exposures. Column-level descriptions, and knowing which dashboards break if a model changes.
• CI. Running modified models and their downstream dependents on every pull request against a sample of production data.

Our dbt interview questions guide covers the tool in detail.

Data quality and the 6am question

"A freshness test fails at 6am. What do you do?" The expected answer: determine the blast radius first — which marts and dashboards depend on it — communicate to affected stakeholders before they find out from a broken dashboard, then diagnose upstream, and afterwards decide whether stale data should be served with a warning or hidden entirely.

The senior addition: distinguish tests that should block a deployment from tests that should raise a warning, because a suite that fails constantly gets ignored, exactly like a flaky test suite in software.

The stakeholder round

• "Two teams have different revenue numbers. What do you do?" Find where the definitions diverge, get the business to decide which is correct, encode the agreed definition in one model, document it, and deprecate the other. The organisational half of that answer matters as much as the technical half.
• "How do you define a metric?" Numerator, denominator, grain, filters, timezone, and the edge cases — refunds, cancellations, test accounts, internal users. Naming those four edge cases unprompted is a strong signal.
• "An analyst wants a new column tomorrow." Understand the underlying question first; often the existing model already answers it and the request is for a shortcut that adds long-term maintenance cost.
• "How do you handle a breaking change?" Lineage to find consumers, notice, a deprecation window, and a migration path.

Where each prep option actually helps

• The dbt documentation and the dbt Labs blog — the closest thing to a canonical description of this role's practices.
• The Kimball dimensional modelling toolkit — still the reference for grain, facts, dimensions and SCDs, even where modern warehouses have softened the rules.
• Build a small project end to end — a source, staging, marts, tests, docs and a dashboard. It answers most of the loop for you.
• StrataScratch and DataLemur — for the SQL round specifically.
• ChatGPT — good at writing the model. It will not ask you what the grain of your fact table is, which is the question that actually separates candidates.
• Greenroom — the spoken layer. Ari, the AI interviewer runs the modelling and stakeholder rounds out loud, where the answer is a spoken definition rather than a query. Honest tradeoff: Ari will not run your dbt project, so build one.

Frequently asked questions

What does an analytics engineer do?

An analytics engineer owns the transformation layer between raw landed data and the dashboards and analyses built on top of it. Data engineers move and land data reliably, analysts answer business questions, and the analytics engineer turns raw tables into clean, tested, documented and well-modelled ones that both can trust. In practice it is software engineering practice applied to analytics — version control, testing, code review, CI and documentation, mostly expressed in SQL.

What SQL questions come up in an analytics engineer interview?

Harder SQL than a general analyst round: window functions including running totals, ranking and frame clauses; CTEs and readability; join fan-out and how you detect that a join silently multiplied your measures; deduplication with ROW_NUMBER partitioned by a business key rather than DISTINCT; date logic including fiscal calendars, timezones and generating a date spine; and incremental logic that correctly handles late-arriving data.

What data modelling questions are asked in analytics engineering interviews?

Expect the layered approach of staging, intermediate and mart models and what belongs in each, star schema design with an explicitly declared grain for every fact table, slowly changing dimensions and when type 2 history is genuinely needed, surrogate keys versus source system IDs, and the current debate between a strict star schema and wide denormalised tables on a columnar warehouse. Stating the grain out loud — for example one row per order line — is what prevents most double-counting bugs.

What dbt questions come up in interviews?

Materialisations and the cost and freshness tradeoff between view, table, incremental and ephemeral; the incremental model pattern including choosing a unique key and using a lookback window for late-arriving records; built-in tests for uniqueness, not-null, accepted values and relationships plus custom singular tests for business rules; sources and freshness checks; why you always use ref rather than a hardcoded table name; documentation and exposures; and running modified models plus downstream dependents in CI.

How do you handle two teams reporting different numbers for the same metric?

Find exactly where the definitions diverge — usually a filter, a timezone, or the treatment of refunds, cancellations, test accounts or internal users — then get the business to decide which definition is correct rather than deciding yourself. Encode the agreed definition in a single model, document it, and deprecate the alternative. The organisational half of that answer carries as much weight in the interview as the technical half.

What do you do when a data freshness test fails?

Establish blast radius first by checking which marts and dashboards depend on the stale source, then communicate to affected stakeholders before they discover it through a broken dashboard, and diagnose upstream in parallel. Decide deliberately whether stale data is served with a visible warning or hidden entirely. It is also worth distinguishing tests that block a deployment from tests that only warn, because a suite that fails constantly gets ignored.