Analytics engineer interview questions: SQL, dbt and modelling

Fifteen common analytics engineer interview questions

1. Write a query using window functions to find each user's first and latest order.
2. How do you deduplicate records reliably?
3. Explain the grain of a fact table and why it matters.
4. Design a model for subscription revenue, including churn and upgrades.
5. How do you structure a dbt project?
6. What tests do you add to models?
7. Incremental models — when and how?
8. How do you handle late-arriving data?
9. How do you define a metric such as active users so everyone agrees?
10. How do you document models for business users?
11. A dashboard number doesn't match finance's. What do you do?
12. How do you make a slow transformation faster?
13. How do you handle slowly changing dimensions?
14. How do you review a teammate's model?
15. Tell me about a dataset you rebuilt and why.

SQL that comes up

• Window functions: ROW_NUMBER, RANK, LAG, LEAD and running totals with SUM() OVER (PARTITION BY … ORDER BY …).
• Deduplication: ROW_NUMBER() partitioned by the natural key, ordered by the most reliable timestamp, keeping row 1.
• Gaps and islands: consecutive-day streaks using the difference between a date and a row number.
• Cohorts and retention: first activity date per user joined to later activity by period.
• Joins and fan-out: joining facts at different grains multiplies rows; aggregate before joining.

Modelling and dbt practice

In dbt, use sources with freshness checks, ref() for dependencies, tests for uniqueness, not-null, accepted values and relationships, plus custom tests for business rules. Use incremental models for large, append-heavy tables with a lookback window to catch late-arriving records. See dbt interview questions.

Metrics and mismatched numbers

Most analytics conflicts are definitional: does “active” mean logged in, or performed a key action? Over 7 days or 28? Including trial users? Resolve them by writing the definition down with its owner, encoding it once in a semantic or metrics layer, and linking dashboards to it. When a number doesn't match finance, trace both to their sources: filters, time zones, refunds, currency conversion and timing of revenue recognition explain most differences. Document the reconciliation so it doesn't recur.

Performance

• Filter and aggregate early; select only needed columns.
• Use partitioning and clustering keys that match common filters.
• Replace repeated heavy logic with an intermediate model.
• Make large models incremental; check query plans for full scans and exploding joins.

The live SQL round

Live SQL exercises usually escalate: a simple aggregation, then a join that introduces duplicates, then a window function, then an edge case such as users with no orders or ties in a ranking. Talk through the grain of each intermediate result before writing the next step, and check row counts as you go. Interviewers reward candidates who notice fan-out from a join before it corrupts a total. If you're unsure of a dialect's syntax, say what you want to do and write the closest standard SQL; logic matters more than memorised function names.

Many loops also include a take-home modelling task with dbt. Keep it small, tested and documented, and explain trade-offs in a README rather than building every possible mart.

Practising

Interviewers follow up on the most specific thing you say, so rehearse the second question as well as the first. CoPilot Interview, a free AI interview assistant, runs analytics engineering mock interviews with follow-ups and produces a report of likely next-round questions. Do one SQL window-function problem and one modelling exercise a day for two weeks. See also SQL interview questions and data modeling interview questions.

Disclosure: CoPilot Interview is our product, so weigh what we say about it accordingly. Everything else on this page applies whichever tool you use, or none.

Frequently asked questions

What does an analytics engineer interview include?

Advanced SQL, dimensional modelling, dbt project structure and testing, incremental models, metric definitions, reconciling mismatched numbers, performance tuning and stakeholder communication.

How is an analytics engineer different from a data analyst?

Analytics engineers build and maintain the modelled, tested datasets analysts use; analysts focus more on answering business questions and communicating insights from that data.

What dbt tests should every model have?

At minimum, unique and not-null tests on primary keys, relationship tests on foreign keys, accepted-values tests on categorical fields and source freshness checks, plus custom tests for key business rules.

How do you resolve conflicting metric definitions?

Agree a written definition with a named owner, encode it once in a shared metrics or semantic layer, point dashboards at it, and document how it reconciles with other reports.