Data analyst / BI interview questions

A stakeholder asks for “orders per customer.” What do you pin down first?

The strongest answer: The grain — one row per order, per customer-day, or per customer-lifetime — then the join, not a chart that silently mixes them

Why it works: Exactly. Grain is the question. A pretty mix of grains is how two people get two numbers.

• Name the grain first.
• Then join.
• Do not mix grains in one number.
• Do not average averages to paper over it.

Why do we split data into train, validation, and test instead of one pile?

The strongest answer: Train fits the model, validation guides choices I still control, test is a last look I do not tune against — two piles collapse those jobs

Why it works: Exactly. Three roles: fit, choose, report. Mixing them is how scores lie to you.

• Train fits; val chooses; test reports.
• Do not tune on test.
• Two piles merge choice and report.
• More data is not a substitute for a held-out look.

def add(item, bag=[]): bag.append(item); return bag — you call add(1) then add(2). What comes back the second time?

The strongest answer: [1, 2] — the default list is created once when the function is defined, so every call without a bag shares it; use bag=None and build a new list inside

Why it works: Exactly. One default object, shared for the life of the function. None plus a fresh list inside is the fix.

• Default arguments are evaluated once, at definition.
• A mutable default is shared by every call.
• Use None and create the value inside the function.
• The bug is silent, not an error.

What is the difference between an INNER JOIN and a LEFT JOIN?

The strongest answer: INNER returns only rows that match on both sides; LEFT returns every row from the left table and fills the right side with NULLs where there is no match — so LEFT is how you keep the customers with no orders

Why it works: Right. The question to ask is: do I want rows with no match to survive?

• INNER: matches on both sides only.
• LEFT: all left rows, NULLs for missing right.
• Use LEFT to keep the ones without a match.
• A WHERE on the right table can silently turn LEFT into INNER.

When do you use LEFT JOIN instead of INNER?

The strongest answer: When you must keep the left grain even if the right side is missing — customers without orders still exist

Why it works: Exactly. INNER drops; LEFT keeps. Choose from the grain, not from a fear of NULL.

• LEFT keeps the left grain.
• INNER drops unmatched.
• Choose from the question.
• Do not hide a drop.

You drag a formula down a column and every row after the first one is wrong. What is going on?

The strongest answer: The reference to the rate cell moved down with the formula — I lock it as $B$1 so it stays put, and leave the row reference relative so it walks down the column

Why it works: Exactly — the $ locks what must not move. Knowing which half to lock is the whole trick.

• Lock what must not move.
• $ before the column, the row, or both.
• Leave the walking reference relative.
• Drag, then check the last row.

Where should a “revenue” definition live — in the ingest or in a model?

The strongest answer: In a modelled layer on top of raw — ingest lands what the source said; definitions are a second, owned step

Why it works: Exactly. Raw is archaeology. Definitions are products. Dashboard dialects are the problem.

• Land raw as the source said.
• Define revenue in a model.
• Do not bake the metric into ingest.
• Do not leave it to each chart.

A teammate keeps rerunning the test set after each hyperparameter tweak. What do you say?

The strongest answer: Stop — that set is now another validation fold. Freeze a true holdout and only open it when the recipe is locked

Why it works: Exactly. Repeated peeking turns test into val. A holdout you keep opening is not a holdout.

• Repeated test peeks burn the holdout.
• Lock the recipe, then open test once.
• Do not average your peeks.
• Process beats “I will not look.”

What is the difference between supervised and unsupervised learning, and how do you tell which one a problem needs?

The strongest answer: Supervised learns from examples with a known target and predicts it for new rows; unsupervised finds structure — clusters, low dimensions, anomalies — with no target; the question is whether a trustworthy label exists

Why it works: Exactly — the presence of a target you trust is the deciding fact; if you want to predict a thing, you need examples of it.

• Supervised: a known target exists.
• Unsupervised: find structure, no target.
• Ask: do I trust the labels?
• Clusters are not predictions.

When would you choose a tuple over a list?

The strongest answer: When the collection is fixed and its position means something — a coordinate, a record, a dict key — since tuples are immutable and therefore hashable

Why it works: Right. Immutability is the point, and hashability is what it buys you.

• Tuple = fixed, positional, hashable.
• List = a sequence you intend to change.
• Only a tuple can key a dict or join a set.
• Choose on meaning, not on length.

Why does WHERE status != 'closed' not return rows where status is NULL?

The strongest answer: Because NULL is unknown, and any comparison with NULL is neither true nor false — the row is filtered out; use IS NULL or COALESCE to include them deliberately

Why it works: Exactly. Three-valued logic: comparisons with NULL are unknown and WHERE keeps only true.

• NULL means unknown, not empty.
• Comparisons with NULL are unknown, so WHERE drops them.
• IS NULL / IS NOT NULL to test.
• COALESCE to give NULL a stand-in value.

You need last month’s paid orders joined to customers. Where does the date filter go?

The strongest answer: On the orders side, before or in the join logic, so you do not multiply then “filter the mess” — and you say whether cancelled rows count

Why it works: Exactly. Filter the fact at the right grain. Chart-only filters are how extracts lie.

• Filter the fact at its grain.
• Say who is excluded.
• Do not leave it to the chart.
• Caches are not a where clause.

You need each order and the customer’s lifetime total on the same row. What do you use?

The strongest answer: A window sum partitioned by customer — I do not join the table to an aggregate of itself if a window is clearer, and I do not GROUP BY and lose the order grain

Why it works: Exactly. Windows keep the grain and add a measure. GROUP BY collapses it.

• Window to keep the row grain.
• GROUP BY collapses.
• Do not hope dialects keep extras.
• Spreadsheets are not the join.

Users appear across months. How do you validate a churn model?

The strongest answer: Hold out later months and whole users — I do not let last month’s behaviour of the same person sit in train while this month is test

Why it works: Exactly. Time and identity. Both can leak. A later window of new (or held-out) people is the adult cut.

• Hold out later time and users.
• Do not shuffle calendar blocks.
• Do not test the past with the future.
• One month is a snapshot, not a study.

You read a 40 GB log file to count matching lines. What shape does the code take?

The strongest answer: Iterate the file object line by line, or write a generator that yields matches — one line is in memory at a time, so the file size stops mattering

Why it works: Exactly. Streaming turns memory cost into a constant.

• Iterating a file yields lines lazily.
• Generators keep memory constant.
• readlines and read materialise everything.
• yield lets you compose stages without buffering.

A query is slow. What is the first thing you run, and what are you looking for?

The strongest answer: EXPLAIN (with ANALYZE where safe) to see the plan: a sequential scan on a big table, an estimated row count far from the actual, a nested loop over many rows, or a sort spilling to disk

Why it works: Right. The plan tells you where the time goes before you change anything.

• EXPLAIN first, change second.
• Scans, bad estimates, nested loops, disk sorts.
• ANALYZE shows real times and counts.
• Fix the biggest node.

A dashboard opens with one huge number and nothing else: 48,210 active users.

What is missing from that tile?

The strongest answer: Everything that makes it mean something — the period it covers, a comparison against the previous period or the target, and a small trend line, so the reader can tell whether it is good news

Why it works: Exactly — a number alone cannot be read. Period, comparison and trend turn it into something someone can act on.

• A number needs a comparison.
• State the period covered.
• Show it against target or last period.
• Add the trend, not more digits.

You join orders to refunds (many refunds per order) then average order value. What bites you?

The strongest answer: The average is now refund-weighted unless you pre-aggregate refunds to order grain — fan-out changes the denominator

Why it works: Exactly. Pre-agg to the grain you will average. DISTINCT on amount is a different lie.

• Pre-aggregate to the measure grain.
• Fan-out changes weights.
• More rows ≠ more units.
• Do not DISTINCT the measure.

A column is named `will_renew_next_quarter` in a “features” table. Your move?

The strongest answer: Treat it as a label until proven otherwise — check lineage and when it is written — then remove it from features if it is the outcome

Why it works: Exactly. Names lie. Lineage and write-time tell you if it is the target.

• Lineage over column names.
• Outcomes are not features.
• Warehouse ≠ allowed.
• Do not train on the label in costume.

What is a decorator, really?

The strongest answer: A function that takes a function and returns a replacement — @log above def f is just f = log(f), which is why the wrapper should carry functools.wraps

Why it works: Right, and wraps is the part people leave out until a name or docstring goes missing.

• @d over f means f = d(f).
• The decorator runs at definition time.
• functools.wraps keeps __name__ and __doc__.
• A decorator with arguments is one more layer.

An index on (customer_id, created_at) — which queries can use it?

The strongest answer: Ones that filter on customer_id, or on customer_id and created_at; a query filtering only on created_at cannot use it, because the leftmost column comes first in the index order

Why it works: Right. Leftmost prefix — column order is the design decision.

• Composite index serves leftmost prefixes.
• Column order matters.
• Equality columns first, then range.
• One index per access pattern, not per column.

A VLOOKUP returns a value for an id that is not in the lookup table at all. How is that possible?

The strongest answer: The last argument was left out, so it did an approximate match and returned the nearest lower key; I pass FALSE for an exact match, or use XLOOKUP, which is exact by default

Why it works: Exactly — the fourth argument defaults to approximate, and an unsorted column then hands back a silent wrong answer.

• Always ask for an exact match.
• Pass FALSE, or use XLOOKUP.
• Approximate match needs a sorted column.
• Decide what a missing key shows.

A customer dimension has two rows per person after an address change. How do you join a fact?

The strongest answer: On the key and the date range that was true when the fact happened — or a current flag if the question is “now” — I do not blindly join on id

Why it works: Exactly. SCD is a time join. Blind id joins double or mis-attribute.

• Join key + valid time, or say “current.”
• Blind id joins fan out.
• Latest ≠ as-was.
• Do not sum people.

You need setup and guaranteed teardown around a block. What do you write?

The strongest answer: A context manager — @contextlib.contextmanager with a yield between setup and cleanup in a finally, or a class with __enter__ and __exit__

Why it works: Exactly, and the finally around the yield is what makes it hold on the error path.

• with guarantees cleanup on every exit.
• @contextmanager: setup, yield, cleanup in finally.
• Or __enter__ / __exit__ on a class.
• __exit__ can suppress an exception — usually it should not.

A north-star metric keeps getting “improved” in the SQL. What do you lock?

The strongest answer: A versioned definition, a change log, and a dual-run when it must change — silent SQL edits are a new product

Why it works: Exactly. Metrics are APIs. Dual-run changes. Secret board numbers are two books.

• Version the definition.
• Dual-run changes.
• Log who changed what.
• No second board number.

A CPU-bound function is too slow. You add threads and nothing improves. Why?

The strongest answer: The GIL lets only one thread execute Python bytecode at a time, so threads help blocking I/O, not computation — use processes, a native library that releases the GIL, or move the work out of Python

Why it works: Exactly. Threads for waiting, processes for computing.

• The GIL serialises bytecode execution.
• Threads still win on I/O waits.
• multiprocessing for CPU-bound work.
• NumPy and friends release the GIL in C.

Two requests read a balance of 100, each subtracts 30, and the balance ends at 70. What happened and what fixes it?

The strongest answer: A lost update: both read then wrote; fix with a single UPDATE that does the arithmetic in the database (SET balance = balance - 30 WHERE balance >= 30), optimistic locking with a version column, or SELECT ... FOR UPDATE

Why it works: Right. Let the database do the arithmetic, or lock or version the row.

• Read-modify-write races.
• Atomic UPDATE with the arithmetic.
• Version column for optimistic locking.
• SELECT FOR UPDATE for pessimistic.

How do you keep an experimentation program honest when people want wins?

The strongest answer: Pre-registration, a primary, guardrails, and a place that publishes flats — I do not fund only the tests that “worked”

Why it works: Exactly. Flats are data. Trophy walls and must-win rules are how you p-hack a culture.

• Pre-register the primary.
• Publish flats.
• Guardrail metrics.
• No must-win rule.

How do you write a percent-of-total measure in DAX, and what changes when the user has a slicer on?

The strongest answer: Divide the measure in the current filter context by itself with the category filter removed — CALCULATE with ALL for the grand total, ALLSELECTED to respect the slicer — and DIVIDE so an empty denominator is safe

Why it works: Exactly — the question is always which filters the denominator keeps. ALL ignores the slicer; ALLSELECTED honours it.

• Choose what the denominator keeps.
• CALCULATE plus ALL: grand total.
• ALLSELECTED: respects the slicer.
• DIVIDE handles the empty denominator.

One synchronous database call inside an async handler makes the whole service slow. Why?

The strongest answer: A blocking call stops the event loop, so no other coroutine can run until it returns — use an async driver, or push it to a thread with asyncio.to_thread

Why it works: Right. One blocking call in an async service is an outage waiting for load.

• Blocking code freezes the whole event loop.
• Use async drivers end to end.
• asyncio.to_thread for unavoidable blocking calls.
• One offender starves every other coroutine.

Optimistic or pessimistic locking for editing a document that several people open?

The strongest answer: Optimistic — a version number checked on save — because conflicts are rare and locks held across a human's editing time would block everyone; pessimistic locking suits short, contended, automated updates

Why it works: Right. Humans hold edits for minutes; a version check on save handles the rare collision.

• Optimistic: version check on save.
• Pessimistic: lock while editing.
• Human edits: optimistic.
• Short automated updates: locks are fine.

Three independent API calls run one after another in an async function. How do you overlap them?

The strongest answer: await asyncio.gather(a(), b(), c()) so all three are in flight at once — and decide what return_exceptions should do, because by default the first failure propagates

Why it works: Exactly, and the exception policy is the part usually left unconsidered.

• gather runs awaitables concurrently.
• Sequential awaits are sequential.
• return_exceptions decides fail-fast or collect.
• TaskGroup gives structured cancellation.

The events table has two billion rows and queries always filter by month. What do you do?

The strongest answer: Partition by time range so each query touches only its months' partitions and old data drops by detaching a partition; keep the partition key in every query's WHERE and index within partitions

Why it works: Right. Partition pruning and cheap retention are what range partitioning buys.

• Range-partition by time.
• Queries prune to relevant partitions.
• Retention = detach a partition.
• The partition key must be in the WHERE.

Twelve dashboards compute “active” differently. What do you build?

The strongest answer: A semantic layer or certified model those twelve read — I do not add a thirteenth SQL dialect

Why it works: Exactly. One certified computation. Meetings and sheets are how the thirteenth appears.

• One certified definition.
• Dashboards consume it.
• No thirteenth dialect.
• Meetings are not a layer.

A margin percent field in the BI tool reads correctly on a row of detail but is wrong on every subtotal.

What happened, and how do you write the field?

The strongest answer: It is computed per row and then aggregated — usually averaged — by the tool; I write it as an aggregate, sum of margin over sum of revenue, so it recomputes at whatever grain the view shows

Why it works: Right — where the division happens decides the answer. Divide the sums; never average the ratios.

• Aggregate, then divide.
• Row-level calculations get averaged.
• Write ratios over the sums.
• Check the field at every grain.

How do contracts survive 40 producers?

The strongest answer: A registry, producer gates, and a small platform owner — not a volunteer who pings Slack on Fridays

Why it works: Exactly. Organisation. Sheets, never-change, and gists are folklore.

• Registry + producer gates.
• A platform owner.
• Versioned breaks.
• No gist dialects.

You are asked for a two-year analytics strategy. What are the first three pages?

The strongest answer: The decisions you owe, the certified metrics, and the quality gates — not a tool bake-off and not a headcount chart without outcomes

Why it works: Exactly. Decisions, metrics, gates. Tools and empire are appendices.

• Name the decisions you owe.
• Name the certified metrics.
• Write the quality gates.
• Put tools in the back.

You are designing a Python service that fans out to many slow downstreams. What shapes the design?

The strongest answer: Async end to end so waiting costs a task rather than a thread, with bounded concurrency per downstream, timeouts on every call and a budget for the whole request

Why it works: Right, and the bound per downstream is what stops you from becoming their outage.

• Async suits many concurrent waits.
• Bound concurrency per dependency.
• Timeout every call, budget the request.
• Measure with real downstream latency.

Product wants dashboards over the transactional database. When do you say no and build a separate analytical store?

The strongest answer: When the analytical queries scan large fractions of tables, need historical snapshots or joins across services, and start competing with the product for the working set; then a warehouse fed by replication or CDC with its own model is the honest answer

Why it works: Right. Row stores serve transactions; scans and history want a columnar, separately modelled store.

• OLTP: many small transactions.
• OLAP: scans, history, cross-source joins.
• Replica for light reporting.
• Warehouse when they compete.

A director points at a four percent week-on-week rise and asks whether it is real.

How do you answer without statistics jargon?

The strongest answer: Say how much this measure normally moves week to week, whether four percent sits inside that, what could produce it on its own — one large account, a short week — and the one check that would settle it

Why it works: Exactly — plain words, a range taken from the measure’s own history, and a named next step. That is what uncertainty sounds like to a non-specialist.

• Compare against normal movement.
• Name what else could explain it.
• Give a range, not a verdict.
• Say what would settle it.

What do you forbid in every readout, as a house rule?

The strongest answer: Invented percentiles, hidden n, causal verbs without a design, and appendix-only caveats — the title must survive the footnote dying

Why it works: Exactly. The honesty contract is the product. Forbidding uncertainty and flats is how you become a marketing desk.

• No invented ranks.
• n visible.
• Verbs match design.
• Caveats travel with the title.

Your worker cannot keep up with the producer and memory climbs. What is the right answer?

The strongest answer: Backpressure — a bounded queue that makes the producer wait or shed load, so the system degrades predictably instead of consuming memory until it dies

Why it works: Exactly. A bounded queue turns an outage into a slowdown you can see.

• Bounded queues create backpressure.
• Unbounded queues move the failure to memory.
• Decide explicitly: wait, shed or drop.
• Make the decision observable.

How do you model a warehouse so analysts can answer new questions without you?

The strongest answer: Facts and dimensions — a star schema: a fact table of measurable events keyed to conformed dimensions like date, customer and product — so any question is a join of the fact to dimensions the analysts already know

Why it works: Right. Conformed dimensions are what make questions composable.

• Fact table: events with measures.
• Dimensions: date, customer, product.
• Conformed dimensions across facts.
• Grain declared per fact table.

Who is allowed to change a board metric?

The strongest answer: A named owner, a dual-run, and a note to the people who already used it — not whoever has the warehouse password

Why it works: Exactly. Change control. Passwords, Slack, and surprises are how numbers become politics.

• Named owner.
• Dual-run + notify.
• Not a password.
• Not a surprise rewrite.

How do you treat certified gold like an API?

The strongest answer: Consumers, SLOs, versions, and an on-call — a table without those is a file

Why it works: Exactly. If it has users, it has an SLO. Hope, logos, and yearly rebuilds are not operations.

• Consumers + SLO + versions.
• On-call for gold.
• Hope is not an SLO.
• Do not annual-rebuild as ops.

A Python service does heavy numeric work per request and latency is unacceptable. What options do you weigh?

The strongest answer: Push the loop into a library that releases the GIL, move the work to a process pool or a separate service, precompute or cache, or accept that this part does not belong in Python

Why it works: Right — and knowing when to leave Python for one component is part of the answer.

• Vectorised or native code releases the GIL.
• Process pools give real parallelism.
• Precompute or cache where the input repeats.
• Some components should leave Python.

A service writes an order and publishes an event, and sometimes the event never arrives. What is the fix?

The strongest answer: The transactional outbox: write the event into an outbox table in the same transaction as the order, and a relay publishes from the outbox and marks rows sent — no dual write, no lost events, at-least-once delivery the consumers already handle

Why it works: Right. One transaction, one source of truth, a relay for the rest.

• Dual writes lose events.
• Outbox row in the same transaction.
• Relay publishes and marks sent.
• Consumers stay idempotent.

Overall conversion fell last quarter, yet every single channel’s conversion rate went up.

How do you explain that to the exec team?

The strongest answer: The mix moved — more traffic came from the weakest-converting channel, so the total fell while every part rose; I show the rates beside the volumes and split the change into a rate effect and a mix effect

Why it works: Exactly — naming it is not enough; the decomposition tells them which half to act on.

• Rates and weights move separately.
• Show volumes next to rates.
• Split rate effect from mix effect.
• Say which half to act on.