Analytics engineer databases interview questions

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

Walk me through exactly how NULL values behave in SUM(), COUNT(), AVG(), and a WHERE filter. Then tell me about a real data modeling scenario where ignoring this caused a bug in a metric.

Sample answer outline

SUM() ignores NULLs, COUNT(*) counts all rows including NULLs, COUNT(column) skips NULLs, and AVG() divides the sum of non-NULL values by the count of non-NULL values which can be misleading. In a WHERE filter, comparing a column to NULL with = always evaluates to UNKNOWN, so rows with NULL are excluded even when you intend to include them. A real scenario is a revenue metric that uses SUM(order_value) where cancelled orders have NULL order_value rather than 0, causing revenue to be overstated because the denominator for average order value is wrong. The fix is explicit COALESCE or NULLIF handling.

Expect these follow-ups

• What does NULLIF(x, 0) do and when would you use it in a metric calculation?
• How does NULL behavior differ in a LEFT JOIN versus an INNER JOIN when the right-side row is absent?

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?

As asked

Your fact_events table has 5B rows and grows by 50M per day. A full refresh takes 6 hours. Walk me through how you would convert it to incremental.

Sample answer outline

Pick an incremental key: usually event_id plus event_date. Set materialized='incremental' and define the is_incremental block to filter the source to only new rows since the last run, with a small lookback window to catch late-arriving data (e.g. last 3 days). Choose the right strategy: merge for late-arriving updates, append for immutable events, insert_overwrite by partition for BigQuery. Be explicit about how late-arriving data is handled. Test the incremental and the full-refresh paths in CI. Document when a full refresh is needed (schema change, logic change).

Reference implementation (sql)

{{ config(
    materialized='incremental',
    unique_key='event_id',
    incremental_strategy='merge',
    partition_by={'field': 'event_date', 'data_type': 'date'}
) }}

select
  event_id,
  user_id,
  event_type,
  event_timestamp,
  cast(event_timestamp as date) as event_date
from {{ source('raw', 'events') }}

{% if is_incremental() %}
where event_timestamp >= (
  select max(event_timestamp) - interval '3 days' from {{ this }}
)
{% endif %}

Expect these follow-ups

• What is the failure mode of incremental models?
• How do you handle a 14-day late-arriving record on a 3-day lookback?
• When is incremental not worth the complexity?

As asked

An executive dashboard takes 45 seconds to load every Monday morning. How do you make it fast and reliable?

Sample answer outline

Profile the dashboard queries first and identify whether the bottleneck is warehouse compute, BI tool rendering, filters, joins, or too many tiles refreshing at once. Pre-aggregate stable metrics into marts or summary tables, partition and cluster large fact tables, and cache results where freshness requirements allow it. Reduce unnecessary cross-filtering and dashboard-level queries that scan the same table repeatedly. Add freshness indicators so executives understand the tradeoff between live data and fast data. Candidates often only increase warehouse size, which can hide modelling problems and raise cost.

Expect these follow-ups

• Which dashboard tiles should not be live queries?
• How do you decide between materialised views and scheduled summary tables?
• What freshness SLA would you set for an executive revenue dashboard?

As asked

An upstream team changes a JSON field from a string to an object without warning. Your ETL job starts failing before the finance dashboard refresh. What do you do?

Sample answer outline

Start by preserving raw data so the pipeline can be replayed after the parser is fixed. Triage whether the change is additive, compatible, or breaking, then decide whether to quarantine bad records, roll back the upstream producer, or ship a parser that supports both versions. Protect downstream tables with contracts and tests so finance does not receive silently coerced values. Communicate impact and expected refresh time to stakeholders rather than hiding behind the batch job. Weak answers focus only on patching the transform and ignore lineage, replay, and stakeholder trust.

Expect these follow-ups

• When would you fail closed versus quarantine records?
• How do you detect this class of change before a dashboard breaks?
• What contract would you ask the upstream team to adopt?

As asked

Can you explain the difference between ROWS BETWEEN and RANGE BETWEEN in a SQL window function frame clause? Give me a case where they would produce different results.

Sample answer outline

ROWS BETWEEN operates on physical row positions, so ROWS BETWEEN 1 PRECEDING AND CURRENT ROW always includes exactly the prior row and the current row regardless of ties. RANGE BETWEEN operates on logical values, so RANGE BETWEEN 1 PRECEDING AND CURRENT ROW includes all rows where the ORDER BY column value falls within 1 unit of the current row's value, which can pull in multiple rows during ties. This matters for running totals when duplicate order key values exist. A strong answer gives a concrete example with duplicate dates in a time series.

Expect these follow-ups

• What is the default frame if you omit ROWS BETWEEN or RANGE BETWEEN after ORDER BY?
• How does UNBOUNDED PRECEDING differ from just PRECEDING?

As asked

When would you add a clustering key to a Snowflake table, and how do you choose which columns to cluster on? What does Snowflake actually do with that key, and what are the ongoing maintenance costs?

Sample answer outline

Clustering keys tell Snowflake how to co-locate micro-partitions so that selective filters on the clustered column can skip large portions of the table. They are most valuable on very large tables where queries consistently filter on the same high-cardinality column like event_date or customer_id. Snowflake automatically re-clusters in the background, which consumes credits. Columns should be chosen based on how queries actually filter the table, not on join keys. A strong answer covers the average depth clustering statistic, how to evaluate whether clustering is working, and the cost-benefit tradeoff versus just relying on micro-partition pruning on smaller tables.

Expect these follow-ups

• How do you inspect whether a clustering key is actually being used by your queries?
• What is the difference between automatic clustering and manual clustering in Snowflake?

As asked

You have a BigQuery table partitioned on event_date. A stakeholder runs a query that scans the entire table every time even though they filter by date. What causes that and how do you fix it?

Sample answer outline

Partition pruning fails when the filter expression is not partition-prunable, typically because the date column is wrapped in a function like DATE(event_timestamp) instead of referencing the partition column directly, or because the filter is on a non-deterministic expression. The fix is to ensure the filter directly references the partition column without any wrapping function. A strong answer also mentions using INFORMATION_SCHEMA.PARTITIONS to inspect partition metadata, setting require_partition_filter to enforce filtered queries, and the difference between ingestion-time partitioning and column-based partitioning.

Expect these follow-ups

• What is the cost difference between scanning a partitioned table with pruning versus without?
• How does BigQuery clustering interact with partitioning?