1. Deduplicate rows keeping the most recent record per user.
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) = 1.
Analytics Engineer interview questions
Analytics Engineer interviews live between data engineering and analytics: SQL and dimensional modeling, dbt and the transformation layer, warehouse design, and the data-quality tests that keep a mart trustworthy. Expect questions about incremental models, slowly changing dimensions, and defining a metric once so two dashboards agree. Real questions with worked answers below.
How a Analytics Engineer interview runs
- SQL & data modeling
- dbt / transformation layer
- Warehouse & schema design
- Data quality & testing
- Behavioral
How to prepare for a Analytics Engineer interview
What the loop actually tests
The loop sits between data engineering and analytics and tests the transformation layer: SQL and dimensional modeling, dbt or its equivalent, warehouse and schema design, and the data-quality tests that keep a mart trustworthy. The question underneath every round is whether you can define a metric once so that two dashboards agree, and keep it that way as the source data changes.
The rounds, in order
Recruiter screen, then a SQL screen with modeling flavor: build a fact table from raw events, deduplicate with a rule, compute a metric at the right grain. The onsite adds a dbt or transformation round (incremental models, sources and refs, tests, how a DAG is structured), a warehouse design round (marts, dimensions, slowly changing attributes), a data-quality discussion (which tests, what breaks, how you would know), and a behavioral session with a stakeholder-management flavor.
What interviewers score
Modeling: the grain stated per table, dimensions conformed across marts, and a defensible line between what belongs in the warehouse and what stays in the BI tool. dbt: incremental logic that reruns safely, tests on the keys and the business rules, and a sense of what makes a model slow. Quality: named tests for uniqueness, null rate, freshness and accepted values, and a story about a bad number that reached a dashboard and how it was caught.
How to prepare, and what to do first
Rehearse the three modeling questions that recur: design a star schema for this business, handle an attribute that changes over time, and reconcile two teams with different definitions of revenue. Then dbt specifics: incremental strategies, snapshots, the tests you add by default, and how you would structure staging, intermediate and mart layers. Keep SQL sharp with window functions and deduplication patterns.
Bring one story about a metric that meant different things to different teams and what you did about it. It answers a question asked in almost every loop.
Where candidates lose the offer
Putting business logic in the dashboard because it was faster. A fact table with mixed grains. An incremental model that misses late-arriving rows. And having no answer to how you would know if the number was wrong before a stakeholder did.
12 Analytics Engineer interview questions
2. Group a DataFrame by category and compute multiple aggregations at once.
df.groupby('cat').agg({'x':'sum','y':'mean'}).
3. Tell me about a conflict with a stakeholder and how you resolved it.
Demonstrate empathy, data-backed communication, and a win-win outcome.
4. Design a data warehouse schema for an e-commerce company.
Star schema: fact_orders plus dimensions (user, product, date, geo), with slowly changing dimensions where attributes drift.
5. How do you ensure data quality in a pipeline?
Schema tests, freshness/volume checks, anomaly detection, contracts, alerting (e.g., dbt tests).
6. Design an incremental dbt model for a table where events can arrive late. What do you use as the incremental filter?
Filter on event time with a lookback window (in is_incremental(), where event_time >= (select max(event_time) from this model) minus a few days) and set a unique_key so late rows update instead of duplicating. Use merge or delete+insert on the partition, not append. State the tradeoff: a wider lookback is more correct but reprocesses more each run.
7. How do you model a Type 2 slowly changing dimension, and how do you test it?
One row per version of the entity with valid_from / valid_to and a current flag, and a surrogate key per version so facts join to the state as of the event. Test that exactly one version is current per natural key, that the validity windows never overlap, and that valid_from < valid_to, dbt unique/not_null plus a custom overlap test.
8. Two dashboards report different numbers for active users. How do you fix it for good?
The cause is two definitions, not a data bug. Define the metric once in a shared model or a semantic/metrics layer and have both dashboards read it, instead of each re-deriving it in its own SQL. Encode the grain and filters (which events, what window, the dedup rule) in one tested model. The fix is governance (a single source for the definition) not another patch on one dashboard.
9. What is the difference between a staging model and a mart in dbt, and why keep them separate?
Staging is one-to-one with a source: rename, cast, and clean only, no business logic, no joins. Marts are the joined, business-shaped tables analysts query. Separating them means a source change is absorbed in one place, logic is reusable across marts, and each layer's contract can be tested on its own.
10. Write a query to find gaps in a daily sequence: dates with no rows in a metrics table.
Generate a full date spine (a calendar table or generate_series) and LEFT JOIN the metrics table onto it. The days where the metric side is NULL are the missing ones. Never infer no-data from absent rows without a spine, you cannot otherwise tell zero from missing.
11. A model that ran in 2 minutes now takes 40. How do you diagnose and fix it?
Check the query plan and the volume first: usually a full refresh where an incremental would do, a fan-out join from a non-unique key, or a missing partition/cluster filter. Fix by making it incremental, deduping the join key, or partitioning on the filter column. Measure before and after, it feels faster is not a diagnosis.
12. How do you decide what belongs in the transformation layer versus the BI tool?
Put anything reused, tested, or definition-critical (metrics, joins, business logic) in the warehouse via dbt so it is version-controlled and consistent, and leave presentation, ad-hoc filtering, and last-mile formatting to the BI tool. The rule of thumb: if two dashboards would compute it, it belongs upstream.