Articles
Analytics Engineer Interview Guide 2026: SQL, dbt, Data Modeling, and Stakeholders
Prepare for analytics engineer interviews with tested SQL, dbt model reasoning, incremental updates, clear data contracts, and stakeholder explanations.
A query can run, a dbt job can turn green, and a business dashboard can still tell the wrong story. Analytics engineer interview preparation should connect those three layers: SQL correctness, dependable models, and definitions that stakeholders understand.
Prepare one defensible path from a business question to a tested data product. Explain the row grain, show a small counterexample, identify what each test proves, and describe how a definition change reaches consumers safely.
This 2026 guide uses official role and dbt documentation, checked on September 30, 2026. Its exercises and preparation recommendations are original. No candidate reports establish a universal interview sequence, question frequency or employer scoring rule; the year identifies the current research check, not a standardized hiring format.
Use the worked example below to practice explaining both the SQL and its business meaning.
What does the role evidence tell you to prepare?
Official role example: GitLab's published Analytics Engineering job family describes work connecting business requirements and technical models, including dbt development, dimensional modeling, documentation and collaboration. It is one organization's role definition, not evidence of every company's interview process or an open vacancy. GitLab Analytics Engineering job family
Preparation inference: organize your evidence around four capabilities: write correct SQL, build reusable transformations, define model semantics, and resolve questions about the resulting data. A portfolio containing only isolated queries demonstrates less of that path than one small model with tests and a clear consumer.
Read the actual job description before deciding how much warehouse performance, Python, orchestration or BI practice to add. Ask the recruiter about exercise format, SQL dialect, available environment and permitted assistance. A tool in the job description does not prove it will appear in a timed assessment.
For a project discussion, separate your contribution from the team's work. If your experience is academic or personal, say so. You can demonstrate sound engineering without inventing production users or measured revenue impact.
How do you prove SQL preserves the intended grain?
Use this original cash-movement fixture. The desired model has one row per order. Amounts are integer minor units in a single currency, and the staging inputs contain deduplicated successful captures and completed refunds.
Order O1 has two captures, 30 and 70, and two refunds, 5 and 15. Its net captured amount is 80. Joining both raw child tables to orders produces four combinations: 30/5, 30/15, 70/5 and 70/15. Summed captures become 200; refunds become 40; the incorrectly computed net is 160.
Aggregate each child to the intended join grain first:
WITH captured AS (
SELECT order_id, currency, SUM(amount_minor) AS captured_minor
FROM captures
GROUP BY order_id, currency
), refunded AS (
SELECT order_id, currency, SUM(amount_minor) AS refunded_minor
FROM refunds
GROUP BY order_id, currency
)
SELECT o.order_id, o.currency,
COALESCE(c.captured_minor, 0)
- COALESCE(r.refunded_minor, 0) AS net_captured_minor
FROM orders AS o
LEFT JOIN captured AS c
ON o.order_id = c.order_id AND o.currency = c.currency
LEFT JOIN refunded AS r
ON o.order_id = r.order_id AND o.currency = r.currency;
The local SQLite check returns O1 = 80, O2 = 40 and O3 = 0. O2 has a capture of 50 and a refund of 10; O3 has neither child record. This is an executed SQL fixture, not a dbt or cloud-warehouse run.
Explain the zero assumption: the input snapshot is complete, so no child means no recorded movement. A missing extraction is unknown data, not automatically zero. Validate that each child's currency matches its order rather than silently dropping mismatches at the join.
SUM(DISTINCT amount_minor) is not a general repair. Two legitimate captures may have equal amounts. Deduplicate event identity upstream; preserve both distinct events. Then inspect row counts and key uniqueness after every join that changes cardinality.
How would you turn the query into a dbt model?
Official fact: dbt's ref() resolves a referenced model relation and establishes dependencies used in the model graph. dbt ref documentation
Design inference: build source-specific staging models that normalize identifiers, types and valid event states. Intermediate models can aggregate captures and refunds to order grain. A final order model exposes the net amount with documented currency and date fields. Use meaningful names that describe the contract rather than hiding every transformation in a numbered sequence.
In dbt SQL, reference modeled inputs with ref() rather than hard-coding another deployment's schema. Explain the dependency edge and where the model materializes. The appropriate view, table or incremental strategy depends on query cost, source size, freshness and adapter behavior.
Keep a shared rule in one reviewed model or macro when that improves consistency. Avoid abstraction that makes a simple business calculation difficult to inspect. Show the compiled SQL when debugging generated logic, and separate a compilation problem from a query that runs with the wrong result.
For deployment, describe the changed models, affected consumers, selected validation and rollback approach. “We run dbt” is a tool statement; “this change preserves one row per order and migrates the dashboard's date definition” is an engineering claim that can be checked.
Which tests prove logic, data integrity and freshness?
Official facts: dbt unit tests validate SQL-model logic with controlled static inputs. Data tests assert properties of models and other resources, commonly through queries returning failing records. These are different evidence boundaries. Unit tests and data tests
For this model, a small fixture should include two captures and two refunds on one order, equal-valued legitimate captures, a missing child and a late refund. Expected rows make the join error visible before large-scale data distracts you.
Data tests then check actual resources: nonnull and unique order IDs, unique capture/refund IDs, valid states, currency agreement and relevant source relationships. A relationship test catches an orphan; it does not decide whether an event should be eligible for the metric. A unique output key also cannot prove its amounts are correct.
Official fact: dbt build does not include source freshness checks; those are configured and executed separately. dbt source freshness
Interview inference: a recent maximum load timestamp does not prove every expected partition arrived. Add completeness or reconciliation evidence where the source contract requires it. State what ran in CI, what ran against deployed data, and what remains unchecked.
Our fixture executes ordinary SQL and assertions in SQLite. To present native dbt test results, also run the project with the actual engine, adapter and supported test configuration, then retain the output. Do not describe a proposed YAML file as a successful dbt run.
What changes when the model becomes incremental?
Official fact: dbt incremental models process selected inputs on subsequent builds; a configured unique_key can identify existing rows for supported update strategies. Filtering and strategy behavior depend on the model and adapter. Incremental model documentation
Design inference: the output key and the change-discovery filter solve different problems. If an old order receives a new refund, filtering only orders created since the previous build misses the update, even with unique_key='order_id'.
Collect affected order IDs from changed orders, captures and refunds using the available source change fields. Recompute their complete child totals, then update the final rows under the chosen strategy. Explain deletion handling and changes arriving outside any lookback window.
Our local check adds a refund of 5 to O1. Recomputing its full state changes net captured amount from 80 to 75. An order-created-date-only selection misses that correction. The test demonstrates the discovery requirement; it does not validate a particular warehouse's merge behavior.
Do not treat unique_key as an automatically enforced uniqueness constraint. Test source and target keys, review duplicate-match behavior, and compare incremental output with a controlled full rebuild. A schema-change setting likewise does not automatically recalculate all historical values under new business logic.
Which model contract makes the metric reusable?
Say what one row means before choosing a fact or dimension table. The order model contains order-level amounts; capture and refund event facts retain their own event grains. Customer or product attributes may need current-state joins or historical versions depending on the question.
An order's creation date, a capture's event date and a refund's event date are different clocks. Aggregating all movement onto order date answers an order-cohort question. Grouping each movement by its own date answers a cash-event question. Neither should be silently labeled as recognized revenue.
Use this original handoff contract:
| Contract field | Example decision |
|---|---|
| Grain and key | One order row, keyed by order ID |
| Measure | Successful captures minus completed refunds, in integer minor units |
| Currency | Preserve currency; combine currencies only under a separate conversion policy |
| Time interpretation | Explicit order-cohort or movement-event date, with timezone and cutoff |
| Missing and late data | Separate absent events from incomplete loads; disclose correction policy |
| Ownership and consumers | Name the definition owner and dashboards using it |
This is a business and modeling agreement, not a claim that dbt column contracts enforce every field. Types and column names cannot establish the intended date attribution or consumer acceptance.
How do you handle stakeholders who disagree about a number?
Consider O1 alone: 100 is captured on January 31 and 20 refunded on February 2. As of February 3, its January order-cohort net is 80. Movement by event date is January +100 and February −20. The local date check reproduces both views.
A stakeholder asking why January changed may be comparing different definitions rather than finding a SQL bug. Ask which decision the report supports, which clock it uses and whether past periods are restated for later refunds. Show one order across both views before discussing the total.
Suggested explanation: “This view assigns later refunds to the order's cohort, so January can change. The movement-date view records the refund in February. We need to choose and name the view that supports your decision, then document its cutoff.” This is an original practice response, not a reported interview answer.
If a definition changes, identify dependent reports, compare old and new results on known cases, agree on an owner and rollout date, and provide a transition path. Avoid silently renaming a measure while keeping the dashboard unchanged. When agreement is unresolved, record the competing definitions and decision needed rather than presenting an arbitrary choice as settled truth.
What should a reviewable preparation project contain?
Use a small, honest repository: source fixtures, model SQL, expected results, failing cases, a README explaining grain and limitations, and a short consumer-facing definition. Add native dbt artifacts when you have actually run them. Keep credentials and confidential employer data out of the repository.
Show one failed approach and why it failed. The raw-child join is useful evidence because its result is plausible but wrong. Show the correction, the regression case and the remaining boundary, such as incomplete extraction or untested adapter behavior.
Rehearse the same project at two depths. First explain the business question and trusted output. Then expand into joins, dependency graph, incremental discovery and test evidence when asked. If using AI assistance, follow the exercise's stated rules and independently verify the generated SQL against your fixtures.