Analytics engineer at xAI

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, recognized 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

Walk me through the difference between ref() and source() in dbt. When would you use one over the other, and what does dbt actually do under the hood when it resolves a ref() call?

Sample answer outline

source() points to raw tables in the warehouse that are managed outside dbt (ingested by Fivetran, Airbyte, etc.), while ref() points to dbt models within the same project or a dbt package. Under the hood, ref() resolves to the fully qualified table or view name for the current target schema and injects a dependency edge into the DAG so dbt orders execution correctly. A strong answer also mentions that source() enables freshness checks via dbt source freshness and that ref() enables cross-project references in dbt Mesh.

Expect these follow-ups

• What happens if you use a hardcoded table name instead of ref() in a model?
• How do source freshness checks work, and what do you configure to make them meaningful?