Analytics engineer interview questions

As asked

Tell me about a data quality incident where a downstream consumer noticed before you did. What happened and what changed?

Sample answer outline

Pick a real example with measurable impact (wrong financial number in a dashboard, broken ML training set, misallocated marketing spend). Walk through detection (and the embarrassment of being told by a stakeholder), diagnosis, and remediation. The strong answer ends with structural fixes: tests at the contract boundary, freshness alerts, schema change detection, a data SLA with the consuming team. Show that you treat data quality as a system property, not a 'be more careful' exhortation.

Expect these follow-ups

• What test would have caught this earlier?
• How did you communicate the impact to downstream teams?
• How do you decide what to monitor vs what to alert on?

As asked

You inherit a dbt project with 200 models and no consistent structure. How would you reorganise it?

Sample answer outline

Standard layering: staging (one-to-one with source tables, light renaming and casting), intermediate (joins and reshaping, no business logic), marts (business-facing, one row per business concept). Group marts by domain (finance, marketing, product) not by source. Name models predictably: stg_, int_, fct_, dim_. Tag models for selection in CI and prod. Document at the model and column level so the warehouse is browsable. Enforce structure with project linters (dbt-project-evaluator). Migrate the existing 200 models in waves, not all at once.

Expect these follow-ups

• How do you handle a one-off analytics question that does not fit any mart?
• What is your testing strategy: every model or just marts?
• When is a model worth materialising as a table vs a view?

As asked

Write SQL to find each user's first and last purchase date and the days between them. Use window functions.

Sample answer outline

Use FIRST_VALUE and LAST_VALUE partitioned by user_id, ordered by purchase_date. Be careful with the LAST_VALUE default frame, which is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; you need ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the actual last. Alternative: MIN/MAX in an aggregation, then join back. Window function version is more flexible if you need other per-user attributes alongside.

Reference implementation (sql)

select distinct
  user_id,
  first_value(purchase_date) over (
    partition by user_id order by purchase_date
    rows between unbounded preceding and unbounded following
  ) as first_purchase,
  last_value(purchase_date) over (
    partition by user_id order by purchase_date
    rows between unbounded preceding and unbounded following
  ) as last_purchase,
  date_diff(
    last_value(purchase_date) over (
      partition by user_id order by purchase_date
      rows between unbounded preceding and unbounded following
    ),
    first_value(purchase_date) over (
      partition by user_id order by purchase_date
      rows between unbounded preceding and unbounded following
    ),
    day
  ) as days_between
from purchases;

Expect these follow-ups

• What is the difference between RANK, DENSE_RANK, and ROW_NUMBER?
• How would you compute a 7-day rolling sum of revenue per user?
• When is a self-join cleaner than a window function?

As asked

The CFO asks: 'What was our revenue last month by product, and how does that compare to the same month last year?' Write the SQL.

Sample answer outline

Aggregate revenue by product and month from the orders fact table. Compute current month and same-month-last-year in one query with conditional aggregation, then derive the year-over-year delta. Watch for: time zone handling, refunds and chargebacks (do they count), product hierarchy (do we group at SKU or category), and the difference between booking date and recognition date. The strong answer asks one or two clarifying questions before writing the query.

Reference implementation (sql)

with monthly_revenue as (
  select
    date_trunc(order_date, month) as month,
    product_id,
    sum(net_revenue) as revenue
  from {{ ref('fct_orders') }}
  where order_date >= date_sub(current_date(), interval 13 month)
  group by 1, 2
)
select
  curr.product_id,
  curr.revenue as current_month_revenue,
  prev.revenue as same_month_last_year,
  safe_divide(curr.revenue - prev.revenue, prev.revenue) as yoy_change
from monthly_revenue curr
left join monthly_revenue prev
  on prev.product_id = curr.product_id
 and prev.month = date_sub(curr.month, interval 1 year)
where curr.month = date_trunc(date_sub(current_date(), interval 1 month), month)
order by current_month_revenue desc;

Expect these follow-ups

• How do you handle a product that did not exist a year ago?
• What if revenue recognition is on a multi-month schedule (subscriptions)?
• What sanity check do you run on this number before sending it to the CFO?

As asked

Sales, finance, and product all have different revenue numbers in dashboards. How would you build a semantic layer that people trust?

Sample answer outline

Start by reconciling definitions with stakeholders: booked revenue, recognised revenue, net revenue, refunds, tax, and currency conversion are different metrics. Encode canonical definitions in a semantic layer or governed BI model rather than in dashboard-specific SQL. Add ownership, documentation, tests, certification status, and a change process so metric changes are deliberate. Keep enough flexibility for exploratory analysis without letting every team redefine company metrics. Strong BI engineers know this is partly technical and partly governance, because trust is lost through unclear ownership as much as bad SQL.

Expect these follow-ups

• How do you handle a board dashboard that uses an old definition?
• When should a metric be certified?
• How do you stop the semantic layer becoming a bottleneck?

As asked

Implement a Type 2 slowly changing dimension for a customer table where the customer address can change over time. Write the SQL.

Sample answer outline

Type 2 SCD keeps history: for each customer, multiple rows with a valid_from and valid_to, and one row with valid_to NULL or a sentinel far-future date marking 'current'. On an incoming change, close the current row by setting valid_to = today and insert a new row with valid_from = today. Add a surrogate key (customer_sk) so fact tables can join to the right point-in-time version. Mind the boundary cases: same-day changes, late-arriving updates, and the difference between the system clock and the business effective date.

Reference implementation (sql)

MERGE INTO dim_customer AS tgt
USING staging_customer AS src
  ON tgt.customer_id = src.customer_id
 AND tgt.valid_to IS NULL
WHEN MATCHED AND tgt.address <> src.address THEN
  UPDATE SET valid_to = CURRENT_DATE
WHEN NOT MATCHED BY TARGET THEN
  INSERT (customer_id, address, valid_from, valid_to)
  VALUES (src.customer_id, src.address, CURRENT_DATE, NULL);

-- Insert the new "current" row for changed customers
INSERT INTO dim_customer (customer_id, address, valid_from, valid_to)
SELECT src.customer_id, src.address, CURRENT_DATE, NULL
FROM staging_customer src
JOIN dim_customer tgt
  ON tgt.customer_id = src.customer_id
 AND tgt.valid_to = CURRENT_DATE
WHERE tgt.address <> src.address;

Expect these follow-ups

• How would you do this in dbt with snapshots?
• What if an upstream system back-dates a change by 30 days?
• When is Type 2 overkill and Type 1 the right answer?