Data engineer 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.

When do you load first and transform in the warehouse (ELT) instead of a heavy ETL box?

The strongest answer: When the warehouse can do the transforms cheaper and we want raw landings we can replay — I do not ETL by habit because “that is what the old tool did”

Why it works: Exactly. ELT is a replay and cost story. Free is not a reason; skipping checks is a red flag.

• ELT when you want replayable raw.
• Use the warehouse if it is the cheaper computer.
• Not because it is “free.”
• Do not skip checks.

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 a region and an availability zone?

The strongest answer: A region is a geographic area with several availability zones; each zone is one or more separate data centres with its own power and network, so spreading across zones survives a data-centre failure

Why it works: Right. The zone is the fault boundary you design around; the region is where your data lives.

• Region: a geography with multiple zones.
• Zone: isolated power, cooling and network.
• Spread across zones for availability.
• Spread across regions for disaster recovery.

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.

An interviewer says: “Design a URL shortener.” What do you do first?

The strongest answer: Ask what it must do and at what scale — reads vs writes, expiry, custom aliases, analytics — and state the assumptions you will design against before drawing anything

Why it works: Right. Requirements and numbers first; the boxes follow from them.

• Clarify functional and non-functional requirements first.
• State assumed scale in numbers.
• Only then draw.
• Check assumptions with the interviewer.

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.

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.

When does partitioning a table actually help?

The strongest answer: When queries filter on the partition key so whole partitions are skipped, or when we need to drop or archive old data quickly; it does not make a table faster by itself

Why it works: Exactly — pruning and lifecycle management are the wins; size alone is not a reason.

• Partition for pruning.
• Partition for cheap archiving.
• Size alone is not a reason.
• Key must match the queries.

You have to load tens of millions of rows into a table tonight. How?

The strongest answer: Use the engine’s bulk-load path, load in committed batches, consider dropping and recreating non-critical indexes, and refresh statistics after — while watching log and disk space

Why it works: Exactly — bulk path, batches, index strategy, statistics and space are the checklist.

• Use the bulk-load path.
• Commit in batches.
• Handle indexes deliberately.
• Refresh statistics after.

An events table holds six years of data and only the last three months are queried. Options?

The strongest answer: Agree a retention rule with the owners, move older rows to an archive table or cheaper storage in batches, and partition by time so future archiving is a partition drop

Why it works: Exactly — retention is a business decision; batches and partitions make it routine.

• Agree retention with owners.
• Archive in batches.
• Partition by time for the future.
• One giant delete is an outage.

Finance says the end-of-day report, which reads from a replica, is missing the last few minutes of orders.

How do you respond?

The strongest answer: Measure the lag and its cause — long transactions, heavy reporting queries, or network — then either route that report to the primary, add lag-aware routing, or fix the workload that is holding replay back

Why it works: Exactly — lag has a cause; find it and decide where that particular read belongs.

• Measure lag and find the cause.
• Route lag-sensitive reads deliberately.
• Fix the workload blocking replay.
• Restarts are not diagnosis.

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.

A date filter is wrapped in DATE(ts). Why did the scan stay huge?

The strongest answer: The function hid the partition column from prune — filter on the partition key as stored, or generate a matching key

Why it works: Exactly. Sargable partition filters. Slots and * fund a function on the key.

• Keep partition filters naked.
• Or add a stored key you can filter.
• Functions on the key kill prune.
• Slots last.

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 user has an allow policy for an action and their group has a deny for it. What happens?

The strongest answer: The deny wins — policy evaluation starts from implicit deny, an explicit allow grants, and an explicit deny anywhere overrides every allow

Why it works: Right. Deny beats allow, always; that is what makes guardrail denies trustworthy.

• Default: implicit deny.
• Explicit allow grants.
• Explicit deny overrides everything.
• Guardrails are written as denies for that reason.

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.

URL shortener: how do you generate the short code?

The strongest answer: Either hash the long URL and take the first characters, handling collisions by retry, or hand out pre-generated unique codes from a counter or key service; say which, and why — a counter gives guaranteed uniqueness but predictable codes, a hash is stateless but needs collision checks

Why it works: Right. Name the scheme and its trade-off.

• Hash-and-truncate or pre-generated keys.
• Handle collisions explicitly.
• Predictability vs statelessness.
• Length from the target count.

Snowflake, BigQuery and Databricks all separate storage from compute. What does that actually buy a data team?

The strongest answer: One shared copy in cheap object storage, with compute started, sized and billed per workload — ETL, BI and ad-hoc users each scale or stop without copying data or fighting each other

Why it works: Right — one copy of the data, many independent compute pools; isolation without duplication is the whole pitch.

• One copy, many compute pools.
• Storage is cheap and shared.
• Size and bill compute per workload.
• Do not copy data for isolation.

Bronze / silver / gold is on the slide. What makes it real?

The strongest answer: Contracts and tests at each hop, and a ban on gold reading bronze “just this once” — names without gates are folders

Why it works: Exactly. Gates, not colours. Late raw-as-gold and extra metals are fashion.

• Tests at each hop.
• Gold does not read bronze.
• Names ≠ gates.
• Do not invent metals.

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.

The business asks for a disaster recovery plan. What two numbers do you ask for first, and why?

The strongest answer: RPO — how much data loss is acceptable — and RTO — how long the service may be down; together they decide whether backups, a warm standby or an active second region is the right answer, and what it costs

Why it works: Right. Every DR design is a point on the RPO/RTO/cost triangle; get the two numbers before the architecture.

• RPO: acceptable data loss.
• RTO: acceptable downtime.
• They pick backup, warm standby or active-active.
• Cost rises steeply as both approach zero.

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.

Your queue delivers at least once. What does that force on every consumer?

The strongest answer: Idempotency: each message carries a key and the consumer records what it has processed, so a redelivery after a crash or a slow ack is a no-op; you design the side effects to tolerate repeats rather than hoping for exactly-once

Why it works: Right. Repeats are certain; make them harmless.

• At-least-once means duplicates.
• Consumer keeps processed keys.
• Side effects tolerate repeats.
• Do not rely on exactly-once.

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 treat the orchestrator like a product?

The strongest answer: SLOs on the critical path, ownership of the paved DAG, and a queue that is not “ask in Slack” — I do not let every squad invent a runner

Why it works: Exactly. Path, paved DAG, one runner. Pools, yearly rewrites, and slides are not a product.

• SLO the critical path.
• One paved runner.
• No Slack-as-queue.
• Do not yearly-rewrite.

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.

Active-passive versus active-active across regions — what does active-active cost you beyond money?

The strongest answer: Data consistency: two regions taking writes means conflict resolution, replication lag and a data model that tolerates both, so active-active is a design constraint on the application, not only a bigger bill

Why it works: Right. The hard part is the data; the compute is the easy part.

• Active-active means writes in two places.
• Conflict resolution and lag are yours.
• Design the data model for it.
• Active-passive keeps one truth.

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.

A hot cache key expires and 5,000 requests miss at once. Design against it.

The strongest answer: Coalesce: the first miss takes a lock or a single-flight slot and rebuilds while the others wait or serve the stale value; add early refresh before expiry with jittered TTLs so keys do not expire together; the database sees one read, not thousands

Why it works: Right. Single flight, stale-while-revalidate, jitter.

• One rebuild, others wait or serve stale.
• Refresh before expiry.
• Jitter TTLs.
• DB sees one read.

A load finishes green every night. How do you know it is complete — that every source row arrived?

The strongest answer: Reconcile against the source: row counts and a checksum or sum of a key measure per partition, compared with the same figures from the source, with a tolerance and an alert — plus a check that expected partitions exist

Why it works: Exactly — completeness is a comparison to the source, not a look at the destination alone.

• Compare to the source, per partition.
• Counts and checksums, with tolerance.
• Expected partitions exist.
• Green is not complete.

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

The strongest answer: The products you owe (certified tables, SLOs, contracts), the paved road (ingest, orchestrate, quality), and the cost model — not a tool catalogue

Why it works: Exactly. Products, road, cost. Tools and empire are appendices.

• Name the data products.
• Describe the paved road.
• Show how you will pay for scans.
• 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.

You are asked to make a payments service active in two regions. What is the first design question?

The strongest answer: Which data must be strongly consistent and which can be eventually consistent — balances need one writer or a consensus store, receipts can replicate later; that split decides the architecture and most of the cost

Why it works: Right. Sort the data by consistency need first; everything follows from that.

• Classify data by consistency requirement.
• One writer or consensus for the strongly consistent part.
• Eventual replication for the rest.
• Cost follows the split.

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.

Design multi-tenancy for a product where some tenants are regulated.

The strongest answer: Tiered isolation: shared schema with a tenant key and row-level enforcement for most, and a separate database or cluster for tenants whose contract or regulation demands it — one code path, an isolation level chosen per tenant, with noisy-neighbour limits and per-tenant keys for encryption

Why it works: Right. One product, several isolation tiers.

• Isolation as a per-tenant tier.
• Shared with row-level enforcement.
• Dedicated where required.
• Same code path.

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.

A director wants “data mesh” on a slide. What do you require in the room?

The strongest answer: Domain owners, contracts, and a platform that makes the paved path easier than a shadow lake — the word is not a reorg

Why it works: Exactly. Ownership plus a road. Renames, anarchy, and no-platform are how you get 40 swamps.

• Owners + contracts + paved path.
• The word is not a reorg.
• No anarchy Spark.
• Do not ban the platform.

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.

What is a cell-based architecture and when is it worth the complexity?

The strongest answer: Running many small, independent copies of the whole stack, each serving a slice of customers, so a failure or a bad deploy hurts one cell rather than everyone; worth it once a single shared stack's blast radius is the biggest risk you have

Why it works: Right. Blast radius is the product; complexity is the price.

• Many independent full-stack copies.
• Customers routed to a cell.
• Failure contained to one cell.
• Adopt when blast radius is the top risk.

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.

How do you limit the blast radius of a failure in a system serving millions?

The strongest answer: Cells: independent copies of the whole stack, each serving a slice of customers, with a thin routing layer; a bad deploy or a poison request takes out one cell, deploys roll cell by cell, and capacity grows by adding cells — at the cost of cross-cell features being hard

Why it works: Right. Isolation by cell; the cost is anything cross-cell.

• Independent full-stack cells.
• Customers pinned to a cell.
• Deploy cell by cell.
• Cross-cell features are the cost.