Analytics for Data Engineers FAQ

Scope — the analytics knowledge a data engineering interview expects, and how to talk about analytics experience: what “analytics experience” questions probe, defining metrics precisely, ratio metrics and the averaging trap, the common product metrics, building metric tables people trust (additive vs non-additive measures, sketches, one definition for every consumer), A/B testing from the data engineer’s side (assignment, exposure logging, sample ratio mismatch, reading a result), root-causing a metric change, and serving dashboards fast. The SQL for cohorts, funnels and sessions is sql_for_data.md’s; star schemas and the semantic layer are data_modeling.md’s. See also: ./sql_for_data.md — retention, funnel, sessionisation and percentile queries; ./data_modeling.md — grain, facts and metric definitions in the semantic layer; ./data_performance.md — making the dashboard queries fast; ./batch_pipeline.md — data quality checks on the tables behind the metrics.

A data engineer is rarely asked to run a regression in the interview, but is almost always asked whether the numbers their pipelines produce can be trusted, what a metric means, and what they would do when one moves. This file is the vocabulary and the reasoning behind those answers.

0) The Short Answer ★★★★★Priority 5 of 5 — Must know — expect it in almost every loop

“Analytics, for me, is turning events into numbers people make decisions on. That means a precise definition for every metric — numerator, denominator, unit, time window and filters — tables built at the right grain so the number is computed one way everywhere, data quality checks so a pipeline bug never looks like a business change, and, when a metric moves, a fixed order of checks: is the data right, is it external, is it a release or an experiment, and which segment moved.”

1) What “Analytics Experience” Means in a DE Interview ★★★★★Priority 5 of 5 — Must know — expect it in almost every loop

1-1) What the interviewer is probing

Behind “tell me about your analytics experience” are four questions: do you understand the business numbers, can you define them precisely, can you build tables that compute them correctly, and can you reason about a change without guessing.

1-2) Telling an analytics story

Use STAR, but make the Result a number and make the Action about the data: which definition you fixed, which table you built, which check you added.

Situation  Marketing and finance reported different "new customer" counts for the same month
           (12.4k vs 10.9k), and both numbers were in board slides.
Task       Own one definition and make every dashboard use it.
Action     - Traced both: marketing counted sign-ups; finance counted first paid orders,
             and excluded refunds within 14 days.
           - Agreed a written definition with both teams: first paid order, net of refunds.
           - Built fct_first_order at one row per customer, incremental by order date,
             and moved both dashboards onto it.
           - Added a daily reconciliation check against the billing system (alert at > 0.5%).
Result     One number in every report; the monthly reconciliation meeting was cancelled;
           the check caught a duplicated payment-webhook load two months later.

What makes it land:

• The conflict was in the definition, not the SQL — that is the usual root cause.
• There is a number in the Situation and in the Result.
• The Action names a table, a grain and a check — data engineering, not slide-making.

2) Metrics ★★★★★Priority 5 of 5 — Must know — expect it in almost every loop

2-1) Kinds of metric

Every metric belongs to a role: a north-star metric says whether the product is winning, driver (input) metrics are the levers that move it, and guardrail metrics make sure a win on one does not quietly break something else.

And by shape, which decides how it is aggregated:

2-2) Defining a metric precisely

A metric is not defined until someone else could compute exactly the same number from the definition alone; most “the numbers don’t match” incidents are two definitions, not a bug.

The checklist every definition answers:

-- DAU, written so the definition is visible in the query
SELECT
  DATE(event_ts)              AS activity_date,      -- event_ts stored in UTC
  COUNT(DISTINCT user_id)     AS dau
FROM   events
WHERE  is_foreground = TRUE                                     -- foreground app events only
  AND  user_id IS NOT NULL                                       -- logged-in users only
  AND  NOT EXISTS (SELECT 1 FROM internal_users i                -- staff and test accounts
                   WHERE i.user_id = events.user_id)             -- (NOT IN breaks on a NULL id)
  AND  NOT EXISTS (SELECT 1 FROM known_bots b                    -- known bots
                   WHERE b.user_id = events.user_id)
GROUP  BY DATE(event_ts);

2-3) Ratio metrics and the averaging trap

A ratio across groups is the sum of numerators over the sum of denominators; the average of each group’s ratio weights a tiny group the same as a huge one and gives a different, usually wrong, answer.

                orders   visits   conversion
Mobile             900   30,000       3.0%
Desktop            100      500      20.0%

Average of ratios:  (3.0% + 20.0%) / 2      = 11.5%   ← wrong
Ratio of sums:      (900 + 100) / 30,500    =  3.3%   ← the site's conversion rate

-- Store the numerator and denominator; divide only at the end
SELECT
  SUM(orders) * 1.0 / NULLIF(SUM(visits), 0) AS conversion_rate
FROM   daily_platform_stats
WHERE  stat_date BETWEEN DATE '2026-11-01' AND DATE '2026-11-07';

• Tables should store numerators and denominators, not precomputed rates, so any rollup stays correct — as long as both are additive across the rollup. A ratio of distinct counts (stickiness, users who converted ÷ active users over a week) needs each count recomputed at the target grain first (see §3-2).
• Simpson’s paradox: a rate can rise in every segment while falling overall, because the mix shifted towards a lower-rate segment — always check the mix when a ratio moves (see §5-2).

2-4) The common product metrics

The SQL for retention cohorts and funnels is ./sql_for_data.md §7-8 and §7-9.

3) Building Metric Tables People Trust ★★★★★Priority 5 of 5 — Must know — expect it in almost every loop

3-1) The layers from events to metrics

Metrics are computed from facts at a declared grain, and rolled up only along paths that are mathematically safe — so the same number comes out whichever dashboard asks.

raw events ──► cleaned events ──► facts at a grain ──► daily aggregates ──► metrics layer ──► dashboards
 (bronze)        (silver)          fct_orders           agg_daily_country    one definition     BI, notebooks
                                   one row per order    one row per          per metric
                                                        day × country

• Declare each table’s grain in its name or docs: one row per order, per user per day.
• Store additive measures (counts, sums) and the parts of ratios in aggregates.
• Build incrementally by date partition, so late data reprocesses only the affected days.

3-2) Non-additive metrics: distinct users across days

Distinct counts do not add up: the users active this week is not the sum of each day’s DAU, because the same user is counted once per day. Recompute from detail, or store a mergeable sketch per day.

-- Wrong: sums daily distinct counts — a user active on 5 days counts 5 times
SELECT SUM(dau) AS wau_wrong
FROM   agg_daily_active
WHERE  activity_date BETWEEN DATE '2026-11-01' AND DATE '2026-11-07';

-- Exact: recompute from the detail table
SELECT COUNT(DISTINCT user_id) AS wau
FROM   fct_user_activity_daily
WHERE  activity_date BETWEEN DATE '2026-11-01' AND DATE '2026-11-07';

-- Approximate and cheap (BigQuery): store a HyperLogLog++ sketch per day, merge any range
CREATE TABLE agg_daily_active_sketch AS
SELECT activity_date, HLL_COUNT.INIT(user_id) AS users_sketch
FROM   fct_user_activity_daily
GROUP  BY activity_date;

SELECT HLL_COUNT.MERGE(users_sketch) AS wau_approx
FROM   agg_daily_active_sketch
WHERE  activity_date BETWEEN DATE '2026-11-01' AND DATE '2026-11-07';

The same holds for medians and percentiles: a median of daily medians is not the week’s median. Keep the detail, or a mergeable quantile sketch (t-digest, KLL).

3-3) One definition, many consumers

When every dashboard writes its own SQL for “revenue”, the numbers drift; define each metric once — in a semantic layer, dbt metrics or a governed view — and make dashboards read it.

• Version metric definitions; a definition change is announced and dated, and history is either restated or annotated.
• Name the owner of each metric — the person who decides an edge case.
• Keep the definition next to the code (YAML in the metrics layer, a doc string on the view), not in a slide.

The modelling side of this — conformed dimensions and the semantic layer — is ./data_modeling.md §9-3.

3-4) Data quality checks for metrics

A pipeline bug must never be reported as a business change, so the tables behind a metric carry checks that fail or warn before the dashboard updates.

The patterns for writing these checks as SQL are ./sql_for_data.md §7-16.

4) A/B Testing for Data Engineers ★★★★☆Priority 4 of 5 — High value — a gap here costs you rounds

4-1) The data path of an experiment

An experiment is a data pipeline: assignment decides the variant, exposure logging records who actually saw it, the metric tables compute outcomes per user, and the analysis compares the groups — and most broken experiments are broken in the first two steps, not the statistics.

assignment ──► exposure log ──► join to metric facts ──► per-user outcomes ──► analysis
 hash(user,     user saw the      by user_id, from        one row per user      lift, CI,
 experiment)    variant at ts     exposure time onward    per variant           SRM check

4-2) What the data engineer owns

Deterministic assignment, a reliable exposure log, and metrics computed per randomisation unit from the moment of exposure.

• Assignment is a hash of the user and the experiment, so it is stable across sessions and needs no lookup table:

import hashlib

def assign_variant(user_id: str, experiment: str, treatment_share: float = 0.5) -> str:
    """Same user + same experiment always gives the same variant."""
    key = f"{experiment}:{user_id}".encode("utf-8")
    bucket = int(hashlib.sha256(key).hexdigest()[:8], 16) % 10_000   # 0..9999
    return "treatment" if bucket < treatment_share * 10_000 else "control"

print(assign_variant("user_42", "checkout_v2"))   # stable on every call

• Including the experiment name in the hash keeps different experiments independent — otherwise the same users are always in treatment together.
• Assignment first, exposure with care: the safe overall comparison is everyone assigned. Restricting to users who reached the changed screen removes the dilution from users who never saw it, but is unbiased only if the trigger is the same in both groups — log the trigger in control too (counterfactual logging) and check that its rate matches across variants. If the variant changes who reaches the screen, analyse by assignment.
• Randomisation unit = analysis unit: randomise by user, then compute one value per user. Treating each page view as independent when users were randomised overstates significance.
• Metrics start at a common start time for everyone in the analysis: assignment time for an assignment analysis — every assigned user, including those never exposed, who count with zero activity — or the trigger time (logged in both groups) for a triggered one. Events before that cannot have been caused by the variant.

4-3) Sample ratio mismatch

If the split was 50/50 and the groups are 50.8/49.2 on a large sample, something is broken — assignment, logging, a redirect or a bot filter — and the result cannot be trusted until it is explained.

def srm_chi_square(control: int, treatment: int, expected_treatment_share: float = 0.5) -> float:
    """Chi-square statistic for observed vs expected group sizes (1 degree of freedom)."""
    total = control + treatment
    expected_treatment = total * expected_treatment_share
    expected_control = total - expected_treatment
    return ((control - expected_control) ** 2 / expected_control
            + (treatment - expected_treatment) ** 2 / expected_treatment)

stat = srm_chi_square(control=50_800, treatment=49_200)
print(round(stat, 1))   # 25.6
# Critical values at 1 df: 3.84 (p = 0.05), 10.83 (p = 0.001).
# Teams flag SRM at a strict threshold such as p < 0.001 — 25.6 is far past it.

Common causes: one variant crashes or loads slower so fewer exposures are logged; a bot filter removes more users from one side; exposure logged only on one variant’s code path.

4-4) Reading the result

Report the effect with a confidence interval, not only a p-value; decide the sample size before starting; and do not stop the test the first day it looks significant.

from math import erf, sqrt

def two_proportion_z_test(conv_a: int, n_a: int, conv_b: int, n_b: int) -> tuple:
    """Return (absolute lift, z, two-sided p-value) for conversion B vs A."""
    p_a, p_b = conv_a / n_a, conv_b / n_b
    pooled = (conv_a + conv_b) / (n_a + n_b)
    standard_error = sqrt(pooled * (1 - pooled) * (1 / n_a + 1 / n_b))
    z = (p_b - p_a) / standard_error
    p_value = 2 * (1 - 0.5 * (1 + erf(abs(z) / sqrt(2))))
    return p_b - p_a, z, p_value

lift, z, p = two_proportion_z_test(conv_a=1_000, n_a=20_000, conv_b=1_100, n_b=20_000)
print(f"lift={lift:.4f} z={z:.2f} p={p:.3f}")   # lift=0.0050 z=2.24 p=0.025

5) Root-Causing a Metric Change ★★★★★Priority 5 of 5 — Must know — expect it in almost every loop

5-1) The order to check

“Revenue dropped 10% yesterday”: first prove the data is right, then rule out the calendar, then look for something you shipped, then find which segment moved — and only then form a hypothesis about users.

The trap interviewers wait for: jumping straight to step 6 with a story about user behaviour when the cause was a half-loaded partition.

5-2) Decomposing the change: rate vs mix

A total moves because a segment’s rate changed, or because the mix of segments changed — split the change into both before blaming any one segment.

-- Conversion by platform, this week vs last week: did a rate move, or the mix?
WITH by_platform AS (
  SELECT
    platform,
    SUM(CASE WHEN week = 'this' THEN visits ELSE 0 END) AS visits_this,  -- 0, not NULL, when a
    SUM(CASE WHEN week = 'last' THEN visits ELSE 0 END) AS visits_last,  -- platform is new or gone
    SUM(CASE WHEN week = 'this' THEN orders ELSE 0 END) AS orders_this,
    SUM(CASE WHEN week = 'last' THEN orders ELSE 0 END) AS orders_last
  FROM weekly_platform_stats
  GROUP BY platform
)
SELECT
  platform,
  orders_last * 1.0 / NULLIF(visits_last, 0)                       AS rate_last,
  orders_this * 1.0 / NULLIF(visits_this, 0)                       AS rate_this,
  visits_last * 1.0 / NULLIF(SUM(visits_last) OVER (), 0)          AS mix_last,
  visits_this * 1.0 / NULLIF(SUM(visits_this) OVER (), 0)          AS mix_this
FROM by_platform
ORDER BY platform;

Reading it:

• Rates flat, mix shifted (say, a campaign brought many low-converting mobile visitors) — the total fell with no segment getting worse. Nothing is broken.
• One rate fell, mix flat — look at that segment: a release on that platform, a broken payment method in that country.

6) Dashboards and the Serving Layer ★★★★☆Priority 4 of 5 — High value — a gap here costs you rounds

6-1) Where dashboards read from

Match the store to the freshness and concurrency the dashboard needs: a warehouse for internal daily reporting, an extract or cache for many viewers, and a real-time OLAP store for sub-second, user-facing analytics.

6-2) Making dashboards fast and cheap

• Point each dashboard at a pre-aggregated table at the grain it displays — daily × country, not raw events.
• Partition by date and make the dashboard’s date filter required, so every query prunes.
• Refresh incrementally — today’s partition plus the days late data can still change (three days, for the DAU definition in §2-2), not the whole history.
• Use approximate distinct counts or sketches for user counts across arbitrary ranges.
• Watch the most expensive dashboard queries (warehouse query history) — a few tiles usually account for most of the cost.

The engine-side levers are ./data_performance.md §3.

7) The Analytical SQL Toolkit ★★★★☆Priority 4 of 5 — High value — a gap here costs you rounds

The analytics questions in a DE interview are usually a handful of SQL patterns; each is worked out in full in sql_for_data.md.

8) Quick Q → A ★★★★☆Priority 4 of 5 — High value — a gap here costs you rounds