This set is for data engineers preparing for interviews where the first technical screen is not a coding puzzle but a conversation about concepts. It is written for candidates applying to product companies, large technology firms, banks, retailers, consultancies and global capability centres in the United States, India and remote-first teams, from early-career engineers with one to three years of experience through to senior candidates who are expected to defend design choices in front of architects and hiring managers. If you build pipelines, model warehouse tables, tune queries that business teams depend on, or explain to a stakeholder why a dashboard number changed overnight, the ten questions in this set will sound familiar, because they are the questions interviewers return to again and again when they want to know whether a candidate understands the systems behind the tools.
Last checked 20 November 2026
Concept questions decide data-engineer interviews more often than candidates expect. A candidate can solve a medium-difficulty string problem, write a workable SQL join and still be rejected because the panel could not tell whether that person would make sound decisions when the data volume grows tenfold, when a source system changes its schema without warning, or when a finance team needs a number that is both fast and auditable. Tools change quickly. A team may move from one orchestrator to another, from one warehouse to another, or from batch files to event streams within a single year. The underlying distinctions — between transactional and analytical workloads, between storage that enforces structure on arrival and storage that defers it, between loading data before or after transformation, between processing records in bulk and processing them as they arrive — do not change at the same pace. Interviewers know this, so they use concept questions as a proxy for judgement. They are listening for whether you can place a problem in the right category, name the trade-off that category creates, and choose a design that fits the workload in front of you rather than reciting the design you used in your last job.
There is a second reason these questions carry so much weight. Data engineering failures are rarely dramatic in the moment they are introduced. A pipeline that writes duplicate rows, a dimension table that silently overwrites history, a warehouse table partitioned on the wrong column — none of these raises an error on day one. They surface weeks later as a reconciliation break, a dashboard that no longer matches the source, or a query bill that has quietly tripled. Interviewers have usually lived through at least one of those incidents, and their questions are shaped by that experience. When they ask about slowly changing dimensions or about the difference between ETL and ELT, they are not testing vocabulary. They are testing whether you would preserve history when history matters, whether you would keep raw data when an audit might need it, and whether you understand the cost of the choices you recommend. A strong answer signals that you have thought about what happens after the pipeline runs successfully the first time.
This first set covers the foundations on which the rest of data engineering rests. It begins with the split between OLTP and OLAP systems and with the three storage patterns — warehouse, lake and lakehouse — that dominate modern platform discussions. Later questions in the set move to star schemas and slowly changing dimensions, to ETL compared with ELT, and to batch compared with streaming, before closing with the supporting ideas that tie them together in a real platform. Each question is answered in full, with the reasoning an interviewer is actually scoring, a worked variant of the kind panels use to check that an answer was understood rather than memorised, and the follow-up question that most often comes next.
How to use this set
Use this set as a practice document, not as a script to memorise. For each question, read the question first and try to answer it out loud for two minutes before you read the worked answer. Note where your answer stopped: most candidates discover that they can define a term clearly but run out of material when they need an example, a number or a failure mode. That gap is exactly what the worked answers are designed to close. After reading an answer, close the document and give the answer again, this time including one concrete example from your own project experience — a table you modelled, a pipeline you debugged, a query you optimised — even if the example is small. Interviewers remember candidates who can ground an abstract distinction in a specific table, a specific volume and a specific decision. Finish by reading the variant and the follow-up, because panels rarely stop at the first question; they probe sideways to see whether the understanding holds when the scenario changes slightly.
Work through the set in order the first time. The questions build on each other deliberately: the OLTP and OLAP distinction explains why warehouses exist, the warehouse, lake and lakehouse discussion explains where analytical data lives, and those two together make star schemas, dimension handling and load patterns much easier to reason about. On later passes, practise out of order and under mild time pressure. In a real interview you will not be warned which concept is coming next, and you will often be asked to connect two ideas — for example, why a lakehouse still needs careful dimensional modelling, or why a streaming pipeline still needs batch reconciliation. If you can make those connections without prompting, you are answering at the level panels associate with senior engineers.
How interviewers score concept answers
Interviewers rarely score concept answers with a formal rubric on paper, but experienced panels apply a remarkably consistent internal standard, and understanding it changes how you should answer. A definition alone fails. If the entire answer to a question about OLTP and OLAP is that one is for transactions and the other is for analytics, the panel has learned only that you have read a glossary. That answer earns no credit for judgement, because it contains no evidence that you could choose between the two, size either one, or recognise when a workload is in the wrong place. Definitions are the entry ticket, not the performance.
What earns credit, in ascending order, is a recognisable pattern. First, a precise definition in one or two sentences, stated without hedging. Second, the mechanism: why the two things differ, in terms of data layout, access pattern or processing order, not just what they are used for. Third, the trade-off: every design choice in data engineering buys something by giving something up, and naming both sides of that exchange is the single clearest signal of practical experience. Fourth, a concrete example with numbers — a row count, a latency target, a data volume, a query pattern — because numbers force specificity and are very hard to fake convincingly. Fifth, a failure mode: what goes wrong when this concept is applied badly or ignored, and how you would detect and recover from it. An answer that contains all five elements, delivered calmly and in a sensible order, will outscore a longer answer that wanders, and it will outscore a technically correct answer that never leaves the abstract level.
There are also consistent ways candidates lose credit. The most common is answering the question they wished they had been asked — describing a favourite tool when the question was about a concept, or narrating an entire project when the question was narrow. Panels also mark down answers that present one option as universally better: saying ELT is simply modern and ETL is outdated, or that streaming is always preferable to batch, tells an interviewer that the candidate has absorbed marketing rather than engineering. Hedging everything is equally damaging; if every sentence begins with "it depends" and none of them go on to say what it depends on, the panel hears uncertainty rather than nuance. The strongest formulation is conditional and specific: name the condition, name the choice it implies, and name the reason. Finally, candidates lose credit by inventing precision they do not have. If you do not know a product's internal detail, say what you do know about the class of system and reason from first principles. Interviewers forgive a bounded gap readily; they do not forgive confident invention, because they cannot trust the rest of the answer afterwards.
Question 1: OLTP vs OLAP
Question
What is the difference between OLTP and OLAP systems? Why can you not simply run all of a company's reporting directly against the production application database, and how would you explain the distinction to an interviewer with a concrete example?
What they’re really testing
The interviewer is testing whether you understand that database systems are optimised for a workload, not for data in the abstract. Behind this apparently elementary question sit several deeper checks. Can you describe access patterns — many small reads and writes touching a few rows each, versus fewer large reads scanning millions or billions of rows? Can you connect those patterns to storage layout, indexing and concurrency? Can you explain, in operational terms, what actually goes wrong when analytical queries compete with live transactions for the same resources? And can you propose the standard resolution — a separate analytical system fed by replication or pipelines — without presenting it as mere convention? Panels also listen for whether you know that the boundary is about workload rather than about a brand name: the same vendor may sell both kinds of system, and a single product can sometimes serve both at small scale, but the optimisation conflict remains real as volume grows.
Full worked answer
OLTP stands for online transaction processing. An OLTP system supports the day-to-day operations of a business as they happen: placing an order, updating a customer's address, reserving a seat, recording a payment, decrementing inventory. Its workload is characterised by a very large number of short, concurrent operations, each touching a small number of rows identified by a key. A typical OLTP operation reads or writes one order, one account or one user profile. Latency expectations are strict — users and calling applications usually expect a response in single-digit to low tens of milliseconds — and correctness expectations are stricter still: transactions must be atomic and durable, so that a payment is never half-recorded and two customers cannot buy the last unit of stock. OLTP schemas are therefore highly normalised, often to third normal form, so that a fact such as a customer's address is stored once and updated once, with no risk of two copies disagreeing. Indexes are plentiful and selective, supporting point lookups by primary key and by common access paths such as email address or order identifier. Concurrency control, locking and write-ahead logging are central concerns, because thousands of users may be writing at once.
OLAP stands for online analytical processing. An OLAP system supports analysis and reporting: revenue by region over the last twelve quarters, conversion rates by acquisition channel, inventory ageing across warehouses, churn by cohort. Its workload is almost the mirror image of OLTP. The number of queries is far smaller — perhaps hundreds or thousands a day rather than millions — but each query touches an enormous amount of data, scanning large portions of one or more tables, filtering, grouping and aggregating. A single dashboard query might scan 400 million order rows to produce twelve numbers. Latency expectations are measured in seconds rather than milliseconds, and a well-tuned analytical query that returns in two to five seconds is usually acceptable for interactive use, while heavier jobs may run for minutes. Writes arrive in bulk — a nightly or hourly load of millions of rows — rather than as a continuous stream of single-row inserts. Schemas are deliberately denormalised, often into star schemas, so that a query joins a large fact table to a handful of dimension tables instead of traversing a long chain of normalised tables. Storage is typically columnar, so a query that needs three columns from a table of eighty reads only those three, and compression is far more effective because values in a single column are of the same type and often highly repetitive.
The reason reporting cannot simply run against the production OLTP database follows directly from those opposing patterns, and it is worth stating as cause and effect rather than as a rule. An analytical query that scans hundreds of millions of rows consumes CPU, memory and storage bandwidth for seconds or minutes at a time. While it runs, it competes with live transactions that are expected to complete in milliseconds. The consequences are predictable: transaction latency rises, locks or buffer-pool pressure increase, and the customer-facing operation — checkout, login, payment — slows or times out because a reporting query is reading the same data. There is also a structural problem. A normalised OLTP schema is a poor shape for analysis: answering a simple question such as revenue by product category and month may require joining eight or ten tables, and the joins that are cheap for a single-row lookup become expensive at analytical scale. Finally, an OLTP database holds current state, not history. When a customer's address or a product's category is updated, the old value is replaced. Analysis very often needs the value as it stood at the time of the transaction, and that history simply does not exist in the operational system unless it was deliberately captured elsewhere.
A concrete example makes the contrast tangible. Consider a retailer taking around 50,000 orders a day through its website and stores — roughly 18 million orders a year, with perhaps 45 million order-line rows. The order database is OLTP: when a customer checks out, the system inserts one order row and a few line rows, updates inventory, and confirms within milliseconds, and it does this for hundreds of concurrent shoppers. Now consider the merchandising team's Monday report, which compares sales by category, region and week for the last two years. That query must scan close to 90 million line rows, join product and store dimensions, and aggregate. Run against the production database at 10 a.m., it would hold resources through the morning peak and visibly slow checkout. Run against an OLAP warehouse loaded overnight, stored columnar and modelled as a star schema, the same question returns in a few seconds and can be re-run all day at no risk to trading. Same underlying business facts, two entirely different workloads, two systems designed accordingly.
The storage contrast can be summarised without a table, as a structured list of the dimensions interviewers expect you to name:
Primary purpose: OLTP records current business operations as they occur; OLAP analyses accumulated business data to support decisions.
Typical operation: OLTP performs short inserts, updates and key-based reads of one or a few rows; OLAP performs large scans with filters, joins, groupings and aggregations over millions or billions of rows.
Concurrency and latency: OLTP serves thousands of concurrent users with responses expected in milliseconds; OLAP serves far fewer concurrent queries, with responses expected in seconds.
Schema shape: OLTP schemas are normalised to remove redundancy and protect write integrity; OLAP schemas are denormalised, commonly star schemas, to reduce joins and speed reads.
Storage layout: OLTP storage is usually row-oriented, retrieving whole records efficiently; OLAP storage is usually columnar, reading only the columns a query needs and compressing them heavily.
Data held: OLTP holds current, detailed, constantly changing data; OLAP holds historical, often aggregated data spanning years, loaded in bulk.
Integrity focus: OLTP prioritises transactional guarantees for individual operations; OLAP prioritises query correctness, reproducibility and scan performance across very large volumes.
A short SQL contrast shows the same point at the level of the queries themselves. An OLTP query is a point lookup, returning one customer's current orders:
SELECT order_id, status, total_amount FROM orders WHERE customer_id = 84217 AND order_id = 99120034;
SELECT d.month_name, p.category, SUM(f.net_amount) AS revenue FROM fact_order_line f JOIN dim_date d ON f.date_key = d.date_key JOIN dim_product p ON f.product_key = p.product_key WHERE d.year = 2026 GROUP BY d.month_name, p.category ORDER BY revenue DESC;
The first query should return in milliseconds and touch an index, not a table scan. The second may scan hundreds of millions of fact rows and is exactly the kind of work a columnar analytical engine is built for. Noticing that difference — and being able to say which system each query belongs on, and why — is the heart of a full-marks answer.
Two refinements lift an answer from good to strong. First, mention how data moves between the two worlds: change data capture or scheduled extracts take committed changes from the OLTP system, pipelines transform and model them, and the OLAP system is loaded in bulk, usually with a freshness lag of minutes to hours that the business has explicitly accepted. Naming that lag matters, because it shows you understand the separation has a cost and that the cost is negotiated, not hidden. Second, acknowledge the boundary cases honestly. At very small scale, a single database with a read replica can carry both workloads for a while, and some modern systems narrow the gap. The interviewer is not looking for a claim that separation is a law of nature; they are looking for the point at which separation becomes necessary — when analytical scans begin to endanger transactional latency, when history must be preserved, or when analytical concurrency grows — and for evidence that you could recognise that point in a real system.
Common wrong answer
The common wrong answer defines the acronyms correctly and then stops, or reduces the distinction to "OLTP is for applications and OLAP is for reports" without any mechanism. A weaker variant claims that the difference is simply size — that OLAP databases are bigger — which misses that a small analytical workload still wants columnar storage and denormalised models, and a large transactional system still wants normalisation and row storage. Another frequent error is to claim that reporting on the production database is merely impolite or against best practice, rather than explaining the resource contention, the schema mismatch and the absence of history that make it fail in practice. Panels also penalise the claim that a read replica fully solves the problem: a replica removes some contention but preserves the normalised schema and the lack of historical modelling, so it is at best a partial answer. If your answer contains no numbers, no access pattern and no consequence, expect the interviewer to probe until one of those appears.
Worked variant
A common variant changes the setting: "Our application database is coping fine today, but the business wants dashboards refreshed every fifteen minutes instead of overnight. What breaks first, and what would you change?" The strong response reasons from the workload rather than reaching for a product name. Increasing refresh frequency does not change the nature of the analytical queries; it increases how often they run and how fresh the data must be, so contention on the source and on any shared analytical store arrives sooner and the acceptable lag shrinks. The candidate should talk about capturing changes incrementally from the source — using change data capture or updated-at watermarks rather than full extracts — loading into a separate analytical store in small batches, and modelling the data once so that fifteen-minute refreshes do not mean fifteen-minute re-computation of all history. They should also raise the cost and complexity trade-off explicitly: near-real-time freshness is achievable, but it is bought with more moving parts, more failure modes and more spend, so the business should confirm that decisions are genuinely taken every fifteen minutes before the platform pays for that freshness. An answer that simply names a streaming tool, without discussing incremental capture, separate analytical storage or the cost of freshness, misses what the variant is testing.
Follow-up an interviewer asks next
The follow-up that most often follows is: "If analytical queries are the problem, why not just add more indexes and more hardware to the production database?" The expected reasoning is that indexes help selective lookups, not full scans — an aggregation over two years of orders still has to read most of the data — and that hardware scaling raises the ceiling without removing the conflict, because a bigger shared system still runs two opposing workloads and still holds no history. A good answer concedes that vertical scaling and a read replica can buy time at modest scale, then explains why the durable solution is a purpose-built analytical system fed by pipelines, and names the freshness and cost trade-offs that come with it. Candidates who can handle this follow-up cleanly have usually answered Question 1 at a strong-hire level.
Question 2: Data warehouse vs data lake vs lakehouse
Question
What is the difference between a data warehouse, a data lake and a lakehouse? How do you decide which one a company needs?
What they’re really testing
Whether you choose storage by workload and governance need rather than by fashion. The interviewer is checking that you understand schema enforcement, data types supported, cost structure and who each system serves — and that you can see the lakehouse as an engineering compromise, not magic.
Full worked answer
A data warehouse stores structured, modelled data for analytics. Data is cleaned, conformed and given a schema before or as it is loaded — schema-on-write. That upfront work is expensive, but it buys fast, reliable querying: business users get consistent definitions, strong access control and predictable performance on SQL workloads such as finance reporting and KPI dashboards. Warehouses historically coupled storage and compute and were costly to scale, although modern cloud warehouses separate the two. Their weakness is rigidity and scope: semi-structured logs, raw events, images or free text fit poorly, and every new source needs modelling before anyone can use it.
A data lake stores data in its raw form, in inexpensive object storage, at almost any scale — structured tables alongside JSON events, log files, images and audio. Structure is applied when data is read — schema-on-read — so ingestion is fast and cheap and nothing is discarded. That flexibility serves data science and engineering teams who want to explore raw data or train models on it. The cost is discipline. Without cataloguing, quality checks, access control and lifecycle rules, a lake degrades into what practitioners call a data swamp: files nobody can find, trust or legally delete, duplicate datasets with different definitions, and queries that scan far more data than they should. Lakes also historically lacked reliable update, delete and transaction guarantees, making corrections and regulatory deletions hard.
A lakehouse tries to combine the two patterns on one storage layer. Data remains in open file formats in object storage, as in a lake, while a table layer adds the warehouse properties lakes lacked: transactional consistency, update and delete support, schema enforcement where wanted, versioning and time travel, and governance through a central catalogue. In principle one copy of the data can then serve both business-intelligence SQL and data-science workloads, instead of maintaining a lake and a warehouse with pipelines copying data between them. The trade-off is complexity and maturity: the team must still design modelling, partitioning and governance well, performance on every workload may not match a specialist system, and a lakehouse label on a product does not by itself create data quality.
The medallion architecture is the organisational pattern most often used inside a lake or lakehouse, and interviewers expect you to know it briefly. It separates data into three logical layers:
Bronze: raw data landed exactly as received from the source, retained for replay, audit and reprocessing.
Silver: cleaned, deduplicated, validated and conformed data, joined where useful, suitable as a trusted enterprise view.
Gold: business-level, modelled aggregates and dimensional tables optimised for a specific reporting or product use case.
Medallion layering is not a product and does not replace modelling; it is a discipline for promoting data from raw to trusted to consumed, with quality checks at each promotion.
A structured comparison, without a table, covers the points panels score:
Schema: warehouse enforces schema-on-write; lake defaults to schema-on-read; lakehouse supports enforcement selectively by layer or table.
Data types: warehouse is strongest on structured data; lake holds any format; lakehouse holds any format with structured table guarantees on top.
Governance: warehouse governance is built in but narrow in scope; lake governance must be deliberately added; lakehouse aims for unified governance across all data, if the catalogue and access rules are actually maintained.
Cost: warehouse storage and compute are typically the most expensive per terabyte for raw retention; lake object storage is cheapest; lakehouse keeps lake storage costs but adds compute and management costs for its table layer.
Best fit: warehouse for trusted, repeated business reporting; lake for cheap raw retention and exploration; lakehouse where one platform must serve both reporting and science without duplicating data.
The decision follows from the workload. A company whose need is governed finance reporting on well-understood sources should start with a warehouse. A company landing huge volumes of raw event and media data for exploration needs lake storage economics. A company maintaining both, and paying to copy and reconcile data between them, is the natural lakehouse candidate — provided it has the engineering capacity to run the more complex platform well.
Common wrong answer
The common wrong answer says a lake is simply a bigger, cheaper warehouse, or that a lakehouse makes warehouses and modelling obsolete. Both miss the point. Size and price are consequences, not definitions; the defining differences are when schema is enforced, what guarantees the system provides and who bears the cost of making data trustworthy. Claiming raw storage removes the need for modelling is a particular red flag: gold-layer tables in a lakehouse need exactly the same dimensional care as warehouse tables, or reporting becomes inconsistent. Equally weak is presenting the swamp risk as a reason lakes are a mistake, rather than as a governance failure that good cataloguing, ownership and quality checks prevent.
Worked variant
The variant interviewers use is: "We already have a lake, and the business still complains that reports are slow and numbers disagree. Do we need a warehouse as well?" A strong answer diagnoses before prescribing. Slow reports usually mean gold data is not modelled and aggregated for the questions being asked; disagreeing numbers usually mean there is no single conformed silver layer and each team defines metrics privately. Both problems can often be solved inside a lakehouse pattern — enforce a governed silver layer, publish modelled gold tables, and restrict report authors to them — without buying a separate warehouse. A separate warehouse becomes justified when its performance, workload isolation or governance tooling is demonstrably needed, not as a reflex. The answer should end with a measurement: prototype the modelled gold layer, benchmark the top reports, and decide on evidence.
Follow-up an interviewer asks next
The follow-up is usually: "If a lakehouse does everything, why do warehouses still exist?" The expected answer is that installed platforms, specialist performance, mature governance features, team skills and migration cost all count. A warehouse that already serves finance reliably has real value, replatforming carries risk, and no architecture wins on a diagram alone. Candidates who concede that point, while explaining clearly what a lakehouse changes, sound like engineers who have worked on real platforms rather than read product announcements.
Question 3: Star Schema vs Snowflake Schema
Question
You are designing the analytical model for a retailer selling through stores and online. Explain star schema versus snowflake schema. Which would you choose, and why? Walk through how you would model daily sales.
What they’re really testing
This question is not really asking for two definitions. Any candidate can recite that a star has denormalised dimensions and a snowflake normalises them further. The interviewer is testing whether you can design from the grain outward, why dimensional modelling exists at all, and whether you can make a trade-off and defend it.
Four judgements hide inside the question. Can you declare the grain before drawing a single table? Do you understand the different jobs fact and dimension tables do, and why mixing them produces slow queries and wrong numbers? Do you understand keys — why a warehouse invents surrogate keys instead of trusting operational identifiers? And can you talk about conformed dimensions, where modelling becomes an enterprise discipline: the same customer, product, and calendar meaning the same thing in every subject area?
A strong candidate also names the default — a star, in almost every modern warehouse — then describes the narrower conditions where snowflaking part of a dimension genuinely wins. "Always star, never snowflake" is a slogan; "it depends," with nothing after it, avoids the question. The interviewer wants a default, a reason, and an exception with conditions attached.
Full worked answer
Start with the grain, because everything else follows from it. The grain is the precise statement of what one row in the fact table represents. For this business, a defensible grain is: one row per product, per store or sales channel, per day, per transaction line. If the business only needs daily aggregates and never needs to drill to a basket, the grain could instead be one row per product, per store, per day. Those are different models, with different storage costs, load logic, and questions they can answer. You cannot aggregate your way back to detail you threw away, so grain is the one decision that is expensive to reverse. Say it out loud, in one sentence, before naming any table — that habit separates candidates who have built warehouses from candidates who have read about them.
With the grain declared, the model separates into two kinds of table that do opposite jobs.
A fact table holds the measurements of a business process, recorded at the declared grain: units sold, gross amount, discount amount, net amount, cost amount, and the margin derived from them. Facts share several properties worth naming:
They are numeric and additive, or at least partly additive. Units sold and net amount can be summed across all stores, products, and days, and the total still means something.
They are numerous — orders of magnitude more rows than any dimension — which is why the fact is kept narrow: mostly foreign keys and measures, very little text.
They record events or snapshots of a process — a sale, a shipment, a payment, a click — rather than describing the things involved.
They should avoid values that can be derived inconsistently. Storing both unit price and extended amount invites two reports to disagree about which to multiply; storing the extended amount and quantity, and deriving unit price at query time, keeps the arithmetic in one place.
A dimension table holds the descriptive context: who, what, where, and when. Product name, brand, category, and department describe the what; store name, city, region, and format the where; customer segment the who; calendar attributes — month, quarter, fiscal period, holiday flag — the when. Dimensions have the opposite profile:
They are wide and comparatively small, with many text attributes and few rows.
They are the entry points for filtering, grouping, and labelling in almost every report.
Their attributes are the vocabulary of the business. If two departments argue about what “active customer” or “net sales” means, that argument is settled in dimension and fact definitions, not in a dashboard.
Keeping those jobs separate is the whole point. Copy descriptive attributes into the fact and it bloats, updates become painful, and the same product ends up spelled three ways in three reports. Push measurements into dimensions and aggregation becomes awkward and slow. A clean dimensional model is a refusal to confuse measurements with descriptions.
Now the star and the snowflake. In a star schema, each dimension is a single table. The product dimension carries product, brand, category, and department attributes side by side in one row per product, even though department repeats across many products and category repeats across many brands. The fact table joins directly to each dimension, so a typical query touches the fact and a handful of dimension tables, and the join path is short and obvious. Drawn on a whiteboard, the fact sits in the middle with dimensions radiating out, which is where the name comes from.
In a snowflake schema, those hierarchies are normalised into separate tables. Product joins to subcategory, subcategory to category, category to department; store joins to city, city to region. The dimension has branched, like a snowflake, into a chain. Storage is saved because repeated values are stored once, and a category name can be updated in exactly one row. The cost is paid at query and comprehension time: every department-level report must traverse the whole chain, the SQL is longer, the optimiser has more joins to plan, and a business user must understand the hierarchy before asking their first question.
On a modern columnar warehouse or lakehouse engine, that saving is usually trivial while the costs are real. Columnar storage compresses repeated low-cardinality values extremely well, so storing a category name on ten thousand product rows costs far less than intuition suggests, while the extra joins multiply across every query, dashboard, and analyst learning the model. That is why the professional default is a star: optimise for questions the business will ask thousands of times, not for storage paid for once.
Keys are the next layer, and interviewers listen carefully here. A natural key is the source system's identifier: the product code in the point-of-sale system, the customer number in the CRM. Natural keys carry business meaning, which is exactly their weakness. They can be reused after a product is retired, change format when a source is replaced, collide when two acquired companies both have a product code 10042, and cannot represent a member that has not arrived yet. A surrogate key is a meaningless integer generated by the warehouse itself, assigned to one dimension row and never reused or changed. The dimension keeps the natural key as an attribute — often called the business key — for traceability, but every fact references the surrogate.
Surrogates buy four things. They insulate the warehouse from source change, because a new source maps onto the existing key space. They make joins fast and compact. They allow the warehouse to hold versions of the same member — essential for the slowly changing dimensions in the next question — each with its own surrogate, sharing one natural key. And they give late or unknown members a legitimate home, through reserved rows such as minus one for unknown and minus two for not applicable, so a fact is never dropped or left with a null foreign key because its context was incomplete.
Conformed dimensions complete the picture. A conformed dimension means exactly the same thing wherever it is used: one calendar, one product dimension, one customer dimension, shared by the sales fact, the returns fact, the inventory snapshot, and the web-traffic fact. Conformance makes drill-across queries possible — comparing units sold against units returned by the same category and month, from two different fact tables, without a reconciliation project. It is also the hardest part in practice, because it is an organisational agreement disguised as a technical artefact: two teams must agree on one definition of region, one handling of closed stores, one calendar.
The worked design for daily retail sales, at the grain of one row per transaction line, looks like this in outline. Note the deliberate choices: surrogate primary keys on dimensions, the natural key retained as a business key, foreign keys on the fact, measures kept additive, and no descriptive text on the fact at all.
CREATE TABLE dim_date (
date_key INT PRIMARY KEY, -- surrogate, smart format YYYYMMDD
full_date DATE NOT NULL,
day_name VARCHAR(12) NOT NULL,
month_name VARCHAR(12) NOT NULL,
month_number INT NOT NULL,
quarter_number INT NOT NULL,
year_number INT NOT NULL,
fiscal_period VARCHAR(7) NOT NULL,
is_holiday BOOLEAN NOT NULL,
is_weekend BOOLEAN NOT NULL
);
CREATE TABLE dim_product (
product_key INT PRIMARY KEY, -- surrogate, warehouse-generated
product_code VARCHAR(20) NOT NULL, -- natural / business key from source
product_name VARCHAR(200) NOT NULL,
brand_name VARCHAR(100) NOT NULL,
category_name VARCHAR(100) NOT NULL,
department_name VARCHAR(100) NOT NULL
);
CREATE TABLE dim_store (
store_key INT PRIMARY KEY,
store_code VARCHAR(20) NOT NULL,
store_name VARCHAR(200) NOT NULL,
city_name VARCHAR(100) NOT NULL,
region_name VARCHAR(100) NOT NULL,
store_format VARCHAR(50) NOT NULL
);
CREATE TABLE fact_sales (
date_key INT NOT NULL REFERENCES dim_date (date_key),
product_key INT NOT NULL REFERENCES dim_product (product_key),
store_key INT NOT NULL REFERENCES dim_store (store_key),
transaction_id VARCHAR(30) NOT NULL, -- degenerate dimension, kept on the fact
units_sold INT NOT NULL,
gross_amount DECIMAL(12, 2) NOT NULL,
discount_amount DECIMAL(12, 2) NOT NULL,
net_amount DECIMAL(12, 2) NOT NULL,
cost_amount DECIMAL(12, 2) NOT NULL
);
Two details reward a sentence each. The transaction identifier is a degenerate dimension: it lives on the fact because it identifies the event itself, has no attributes worth a table, and is needed for basket-level analysis and reconciliation to source. And the date key uses the year-month-day integer format, a rare case where a smart key is acceptable because calendar values are stable, never reused, and never versioned — unlike product or customer keys, which should stay meaningless.
So which to choose here? A star. The hierarchies are shallow and stable, the dimensions are modest, analysts and query tools benefit from short join paths, and columnar compression makes denormalised attributes cheap to store.
Then name when snowflaking genuinely wins, because it does sometimes:
When a dimension is very large and a branch of its hierarchy is shared, volatile, or governed separately. A geography hierarchy used by sales, logistics, and compliance, where region boundaries are redrawn by a central team, can justify a separate geography structure maintained once rather than copied into several wide dimensions.
When a subset of attributes has a genuinely different update cadence or security boundary. Product pricing attributes that change hourly and are restricted to a finance audience may belong in their own table rather than in the general product dimension every analyst can read.
When a hierarchy is deep, ragged, or recursive — an organisational chart or a chart of accounts where levels vary by branch — a flattened star dimension fights the data, and a bridge or parent-child structure serves the queries better.
When the consuming tool or semantic layer performs measurably better with the normalised shape on the actual platform in use, demonstrated by a test rather than assumed from theory.
Notice the pattern: each exception is about governance, security, shape, or measured performance — never storage savings alone, and never normalisation being tidier in the abstract. That framing is the difference between a textbook answer and an engineering answer.
Common wrong answer
The weak answer defines both schemas, says the snowflake "saves space and avoids redundancy, so it is better designed," and stops. It treats normalisation — a discipline invented to make transactional updates safe — as a universal virtue, and imports it into a system whose workload is almost entirely reads and aggregations. It never mentions grain. It uses natural keys throughout because "the source already has IDs," leaving the model exposed to key reuse, source replacement, and the impossibility of holding history. And it puts attributes wherever they fit on the diagram — a region name on the fact here, a measure in a dimension there — telling the interviewer the candidate has drawn schemas but not yet kept one correct under daily load. The reflexive opposite also fails: declare snowflakes always wrong, and a shared-geography scenario collapses the rule.
Worked variant
The interviewer now changes the constraints: the same retailer acquires a second chain, and the combined business has forty million products, a geography hierarchy maintained by a central team and shared with logistics, and a requirement to report sales for both chains against one category structure even though the two chains classify products differently.
A good response adapts rather than restarts. The grain does not change — acquisition does not alter what one row of sales means — and saying so first shows steadiness. The product dimension keeps one row per product per chain, with the natural key now a composite of source system and product code, because codes will collide; surrogate keys absorb that collision without touching a fact row. Category conformance becomes the central work: a mapping from each chain's native categories to the shared reporting category, maintained as governed reference data and carried on the product dimension, so both chains report on one vocabulary while native classification is preserved for chain-specific analysis. Geography is the legitimate snowflake candidate here — large, shared, centrally owned — so carving it into its own maintained structure, referenced by store and customer, is defensible in a way it was not originally. Close by naming the risk honestly: historical facts need no reloading, because they point at surrogates, but the dimension mapping must be validated before the first combined report, since a wrong category map produces confident, wrong totals.
Follow-up question
Your star schema is live and the business is happy with it, but the product team now asks why last quarter's category totals changed after this quarter's product reclassification, when no sales data was restated. What has happened in your model, and what design choices would you discuss with them before changing anything?
Question 4: Slowly Changing Dimensions
Question
A customer dimension stores each customer's segment, region, and account manager. Those attributes change over time. Explain slowly changing dimensions: what the main types are, how each behaves, and how you would decide which type to apply. Show how you would implement the most important type.
What they’re really testing
This question tests whether you understand that a warehouse is a record of history, not a mirror of today's source system. Operational systems overwrite: when a customer moves region, the CRM updates the row, and the old region is gone because the business only needs the current truth to operate. A warehouse exists partly to answer questions the operational system cannot — what did sales by region look like as they were understood at the time, and what do they look like under today's structure? Which of those questions a given attribute must answer determines its slowly changing dimension treatment, and that is a business decision the engineer has to draw out, not a default to apply silently.
The interviewer also tests implementation, not just taxonomy. Type 2 is the workhorse, and many candidates can describe it while being unable to write the merge that maintains it: closing the current row, inserting the new version, allocating a fresh surrogate key, and guaranteeing exactly one current row per member. Finally, the question tests downstream awareness: the type chosen for an attribute quietly rewrites every report that uses it, and a candidate who has watched a finance team discover that will say so unprompted.
Full worked answer
A slowly changing dimension is the set of techniques for handling dimension attributes that change over time. Slowly is historical — these attributes change occasionally, like an address, a segment, or a manager, as opposed to measurements that arrive constantly. In practice the techniques are applied attribute by attribute, not table by table: within one customer dimension, a corrected spelling might be overwritten, a segment change fully historised, and an original sign-up channel preserved forever. Real dimensions are almost always a deliberate mixture.
Type 0 means retain the original and never change it. The attribute is loaded once and is immune to later source updates. This suits attributes that are facts of origin rather than descriptions of the present: the channel a customer first signed up through, the date and terms of original acquisition, an original credit score at application. Applying Type 0 is an active decision, not laziness — you are asserting that the first value is the analytically meaningful one and that later values, however accurate, would corrupt cohort analysis built on the original.
Type 1 means overwrite. The old value is replaced, no history is kept, and every fact that points at that member — including facts from years ago — now reports under the new value. Type 1 is correct in three situations. First, corrections: a misspelled name or a miscoded city was never true, and keeping it would preserve an error, not a history. Second, attributes where history has no analytical value and the business explicitly wants a current-only view. Third, restatement by design: some organisations deliberately report all history under the current organisational structure, so a region reorganisation overwrites region on the dimension and last year's numbers roll up into this year's regions. That is a legitimate reporting policy, provided it is chosen openly and everyone understands the totals for a closed period can move. The danger of Type 1 is accidental use: applied by default to an attribute whose history mattered, it destroys information that cannot be recovered, because the source system has overwritten it too.
Type 2 means add a new row. Each version of the member becomes a separate dimension row with its own surrogate key, the same natural key, and metadata describing its period of validity. Facts loaded at any moment join to the version that was current when the event occurred, so history is preserved exactly as it was known at the time. A Type 2 dimension row typically carries:
A surrogate primary key, unique per version, which is what facts reference.
The natural or business key, shared by all versions of the same member.
The tracked attributes themselves, such as segment and region.
An effective start date or timestamp, from which this version is valid.
An effective end date or timestamp, after which it is no longer valid, often left open-ended or set to a far-future sentinel on the current row.
A current-row flag, so queries for the present-day view can find the live version without date arithmetic.
Optionally, a version number and the reason or source of the change, which prove invaluable when a business user asks why a member appears twice.
The mechanics matter. When a tracked attribute changes, the load must close the existing current row by setting its end date to just before the change takes effect and clearing its current flag, then insert the new version with a new surrogate key, the change date as its start, an open end date, and the current flag set. Facts already loaded are untouched — they keep pointing at the version they were loaded against, which is precisely how history stays frozen. New facts pick up the new surrogate. Two invariants protect the whole design: there is exactly one current row per natural key at all times, and the validity intervals for a member never overlap and never leave gaps. A load that breaks either invariant produces double-counted or vanished members in reports, so both belong in automated data-quality checks, not in hope.
Here is a worked merge for a customer segment change, written as plain SQL so the sequence is visible. Assume a staging row holds the incoming customer state and the change date.
-- Step 1: close the current version for customers whose tracked attributes changed
UPDATE dim_customer AS d
SET effective_end_date = s.change_date - INTERVAL '1 day',
is_current = FALSE
FROM stg_customer AS s
WHERE d.customer_id = s.customer_id
AND d.is_current = TRUE
AND (d.segment_name <> s.segment_name
OR d.region_name <> s.region_name
OR d.account_manager <> s.account_manager);
-- Step 2: insert the new version, with a fresh surrogate key
INSERT INTO dim_customer (
customer_key, customer_id, customer_name,
segment_name, region_name, account_manager,
effective_start_date, effective_end_date, is_current
)
SELECT
next_customer_key(), -- warehouse-generated surrogate
s.customer_id,
s.customer_name,
s.segment_name,
s.region_name,
s.account_manager,
s.change_date,
DATE '9999-12-31', -- open-ended sentinel for the current row
TRUE
FROM stg_customer AS s
WHERE NOT EXISTS (
SELECT 1
FROM dim_customer AS d
WHERE d.customer_id = s.customer_id
AND d.is_current = TRUE
AND d.segment_name = s.segment_name
AND d.region_name = s.region_name
AND d.account_manager = s.account_manager
);
Brand-new customers flow through the same insert, since they have no current row to match; unchanged customers are ignored by both steps. In production this is usually expressed with the platform's merge or slowly-changing-dimension transformation, but the interview point stands: state the order of operations — detect change, close old, insert new, allocate key — and the edge cases you would test, including a change arriving with a past effective date, which must be spliced into the middle of the version chain rather than appended at the end.
Type 3 means add a new column. The dimension keeps one row per member and adds a prior-value column alongside the current one — current region and previous region — so facts can be reported under either structure. Type 3 suits a single, anticipated transition, such as a one-off reorganisation where the business wants one period compared under both structures, and it is deliberately limited: it holds exactly one previous state, so a second change overwrites the first history, it does not scale to repeated change, and each tracked attribute doubles the column sprawl. Mention it accurately, including its ceiling.
Type 6 is the hybrid worth naming briefly: Type 1, Type 2, and Type 3 working together on the same attribute. The dimension keeps full Type 2 versioned rows and additionally carries a current-value column, overwritten on every version row whenever the attribute changes. Analysts get both views without a self-join — group by the versioned attribute for history as it was, or by the current column for all history under today's value. The cost is a wider update on each change, since every historical row for that member must be touched. Recommending Type 6 for one or two high-value attributes, such as sales territory, while keeping the rest simpler, shows proportionate judgement.
Two harder realities separate experienced answers from recitations. The first is the late-arriving dimension member. A fact arrives — a sale, a claim, a shipment — for a customer or product the dimension has never seen, because extracts run in different orders or the member was created moments before the transaction. Dropping the fact or blocking the load is unacceptable; measurements must not be lost because context is late. The standard handling is an inferred member: a placeholder row carrying the natural key from the fact, the surrogate the fact will reference, and unknown values for the descriptive attributes, flagged as inferred. When the real data arrives, that row is updated in place — a Type 1 style completion keeping the same surrogate key — so every fact already loaded against it gains its correct context without reloading. If the arriving data reveals the member genuinely had earlier history, a fuller Type 2 treatment may be needed, but the inferred-member pattern resolves the common case cleanly and is often the detail the interviewer is fishing for.
The second reality is downstream effect. The type applied to an attribute decides what every report using it will silently do. Under Type 1, a closed quarter's totals can change after a reorganisation, and a finance team reconciling to a frozen extract finds a variance nobody can explain from the data alone. Under Type 2, period totals stay fixed, but a naive count of dimension rows overstates the customer base, because one customer with three versions is three rows; headcount queries must filter to distinct natural keys or to current rows, depending on the question, and a dashboard that joins facts to "the customer" without a point-in-time or current-row decision has ambiguity baked in. Under Type 3, comparisons are easy for exactly one transition and impossible beyond it. Choosing types is therefore a contract with every future consumer of the data, and each significant decision should be recorded, with its business reason, where the next engineer can find it.
A practical decision habit closes the answer. For each attribute, ask: Was the old value ever true, or was it an error? Does anyone need the past as it was known then, as understood now, or both? How often does this change, for how many members? Errors point to Type 1, origin facts to Type 0, a single anticipated restructure to Type 3, repeated change with as-was reporting to Type 2, and both-views demand on a key attribute to Type 6. Do not historise attributes nobody can attach a reporting need to: every Type 2 attribute adds load complexity and query ambiguity someone will maintain for years.
Common wrong answer
The weak answer lists the types as a memorised chant — "Type 1 overwrite, Type 2 new row, Type 3 new column" — and then applies Type 2 to everything, on the grounds that keeping all history is always safest. It is not safest; it is most expensive, and it makes simple current-state questions harder for every consumer. The answer usually cannot describe the merge mechanics: no mention of closing the current row, no effective dates, no current flag, and crucially no grasp that each version needs its own surrogate key — without which Type 2 is impossible, because facts cannot distinguish one version from another. It also treats the choice as a property of the whole table rather than of each attribute, and it never mentions what happens to reports afterwards. A tell-tale closing line is "the source system keeps the history anyway," which reveals the candidate has not yet worked with a source that overwrites — which is to say, almost every source there is.
Worked variant
The interviewer narrows the scenario: account manager changes constantly — some customers change manager four times a year — while segment changes rarely and is the basis of regulatory reporting that must reproduce, years later, exactly what was reported at the time.
A proportionate design treats the two attributes differently and says why. Segment gets full Type 2 handling, because the regulatory requirement is the clearest possible case for as-was history: the version chain, effective dates, and an auditable load are non-negotiable, and its low change rate keeps the row growth modest. Account manager is the judgement call. Full Type 2 on it would multiply customer versions roughly fourfold and make every customer query carry version logic for an attribute most reports only need in its current form. A defensible choice is Type 1 overwrite for the manager on the dimension — current manager only, openly documented — combined with manager-at-time-of-sale captured as a separate, smaller relationship or fact attribute where a specific sales-attribution report genuinely needs it historically. Alternatively, if manager history is needed broadly, Type 6 on that one attribute gives current and as-was views while segment's Type 2 chain carries the regulatory weight. Either design passes, provided the candidate names the trade-off — version growth and query complexity against attribution accuracy — and states which reports each choice serves and which it knowingly does not.
Follow-up question
Six months after go-live, a business user shows you two reports for the same quarter: one says the enterprise segment had 4,100 customers, the other says 4,860, and both were built on your customer dimension. Walk through what you would check, in order, and what you would change in the model or the reporting layer so the disagreement cannot recur unexplained.
Question 5: ETL vs ELT — When Do You Transform Before You Load, and When After?
Question
Explain the difference between ETL and ELT. Why has ELT become the default pattern for most cloud data platforms, and in what situations would you still deliberately choose classic ETL, transforming data before it ever reaches the warehouse?
What they’re really testing
This sounds like a vocabulary question, and weak candidates treat it that way. Strong interviewers are using it to test whether you understand where compute lives in a modern platform, what that compute costs, and who bears the risk when raw data contains something sensitive.
Three judgements sit underneath the acronyms.
First, architectural judgement. Can you explain why the order of two letters changed — not as fashion, but as a direct consequence of storage and compute being decoupled in cloud warehouses? If you cannot explain the economics, you do not yet understand the pattern you use every day.
Second, governance judgement. ELT means raw data lands somewhere before it is cleaned. If that raw data includes email addresses, payment tokens, health information, or free-text fields where customers type anything they like, landing it first creates obligations. The interviewer wants to hear that you have thought about access, retention, and masking before the data becomes queryable, not after an incident.
Full worked answer
Start with clean definitions, stated in one breath each.
ETL — Extract, Transform, Load: Data is pulled from a source, transformed on a separate processing engine or in a dedicated pipeline layer, and only the cleaned, modelled result is loaded into the warehouse or serving store.
ELT — Extract, Load, Transform: Data is pulled from a source and loaded, largely as-is, into a powerful target platform. Transformations then run inside that platform, using its compute, usually expressed in SQL.
Classic ETL was not a mistake the industry later corrected. It was the rational response to expensive, tightly coupled warehouses. In the on-premises era the warehouse was a scarce appliance: storage was costly, compute was fixed, and loading unmodelled data wasted the most expensive resource you owned. So you transformed upstream, with tools such as Informatica or hand-written Spark jobs acting as a bouncer at the door — only cleansed, conformed data was allowed in.
Cloud warehouses inverted the economics, and ELT followed directly from that inversion.
Storage and compute separated. On platforms such as Snowflake, BigQuery, and Databricks, you can store large volumes of raw data cheaply and spin up compute only when you transform or query. Keeping the raw copy no longer punishes you as it once did.
The warehouse became the best transformation engine you already pay for. Modern warehouses execute SQL transformations at a scale that would require you to build and operate a separate distributed tier to match. Pushing transformation into that engine removes a whole system to provision, patch, and scale.
Raw retention preserves optionality. When the raw order, click, and event are preserved, a new definition of a metric does not require re-extracting from source. You re-run a different transformation over history you already hold. Churn defined one way in January can be restated in June under a new definition, reproducibly, because nothing was thrown away at the door.
Late decisions beat early guesses. Business logic changes faster than source schemas. ELT lets you defer modelling until the moment of use, and maintain several modelled views over the same raw base for finance, product, and machine learning without three separate ingestion pipelines.
Tooling matured around SQL in place. Orchestrated SQL transformation frameworks made in-warehouse transformation reviewable and testable in a way older graphical tools rarely were. Transformations became code in a repository, with pull requests, automated tests, and documentation generated from the models.
Staging models rename, cast, and standardise one source at a time — no joins across systems yet, no business logic beyond making each source legible on its own terms.
Intermediate models join and reconcile across sources: matching a web user to a customer record, resolving duplicate entities, applying conforming logic.
Mart models express business concepts directly — one row per order, one row per customer per month — with definitions the company has agreed, in plain column names.
The medallion pattern, popularised in lakehouse environments, expresses the same discipline with different names, and you should be able to translate between the vocabularies without hesitation.
Bronze — raw, faithful, append-oriented. Data as it arrived, with ingestion metadata. Duplicates may exist; malformed records are quarantined or flagged, not silently repaired. Access is tightly restricted, because bronze is where sensitive fields still live in original form, and retention is a deliberate policy decision.
Silver — cleaned, conformed, trustworthy. Types enforced, duplicates removed, keys standardised, quality rules applied, personally identifiable fields tokenised or masked according to policy. Silver is the layer engineers certify and others build on, because its contract is stable.
Gold — business-shaped, consumption-ready. Aggregates and wide, denormalised tables built for specific decisions: daily revenue by region, activation cohorts, feature adoption by account. Gold is optimised for the reader, not for fidelity to source, and can be rebuilt from silver whenever definitions change.
On cost and governance, be honest about the trade-off: ELT moves compute bills into the warehouse rather than eliminating them, and incremental models matter more than the acronym. Raw retention also creates governance debt — every raw table needs an owner, retention period, access list, and deletion path — in exchange for far better lineage when transformations are managed as code.
Now the part that separates a memorised answer from a professional one. There are at least four situations where transforming before load is correct engineering, not nostalgia.
Sensitive data must be masked before it is stored anywhere queryable. If a source emits card numbers, government identifiers, or health details, and your warehouse gives broad analyst access, landing raw values first — even briefly — may breach policy or regulation. A pre-load step that tokenises, hashes, or drops those fields means the warehouse never holds them at all. “We transform it right after loading” is inadequate when the rule is that the value must never persist.
Regulated data carries residency, retention, or consent constraints. In healthcare, financial services, and European consumer data, you may have to prove what you hold, where, and that you deleted it on request across every copy. Every raw replica multiplies that burden. Minimising before load — keeping only fields with a lawful purpose — reduces the surface you must govern, audit, and erase from.
The target cannot do the transformation well. Not every destination is a cloud warehouse. Loading into an operational store, a search index, a feature store with strict latency needs, or a third-party system with a fixed schema may require data to arrive already shaped. ELT presumes a powerful general-purpose target; where that presumption fails, ETL returns.
Streaming transforms must happen in flight. When data moves continuously and a decision must be made in seconds — blocking a fraudulent transaction, suppressing a duplicate event, enriching a click before routing it — there may be no meaningful “after load” moment. The transformation is part of delivery itself.
Ground all of this in one pipeline you can narrate end to end. Consider an online retailer processing orders from a transactional database, click events from its website, and a nightly file from a fulfilment partner. Under an ELT design, a change-data-capture stream lands order inserts and updates, source-shaped, into a raw orders table every few minutes, preserving the source commit timestamp. Click events arrive into object storage and load into a raw events table hourly. The fulfilment file is dropped into a bucket and loaded untouched, file name and load time recorded.
A scheduled transformation then promotes bronze to silver: it rejects a fulfilment delivery whose row count falls outside an expected band rather than treating a truncated file as truth, deduplicates orders on their natural key keeping the latest update, standardises currency with the original amount and rate retained, and tokenises customer email into a stable hashed key. The raw email remains only in restricted bronze, visible to a small platform group, and is purged after a defined window. Silver to gold models build one row per order with fulfilment status, one row per customer with lifetime history, and a daily revenue table by region and channel that finance reconciles against the payment processor. When marketing redefines "active customer" mid-year, only the gold model changes; the restated history is produced the same afternoon.
Note the deliberate ETL exception inside the same system. The payment provider's webhook payload contains a field the retailer has no lawful need to store in analytics at all. That field is stripped by the ingestion service before the payload is written anywhere — a small, classic transform-before-load step, chosen because the correct place for that data is nowhere. Real platforms are hybrids, and the engineer's job is to place each transformation where its risk and cost belong, not to pledge allegiance to one ordering of three letters.
Common wrong answer
The common wrong answer defines both terms correctly and then declares ELT the modern winner, full stop: "ETL is old, ELT is what everyone does now because cloud warehouses are cheap, so you just load everything raw and transform with SQL."
It fails on four counts. It gives no reason rooted in economics, so it cannot explain when the economics change. It ignores sensitive data entirely, which alarms any interviewer who has worked under regulation. It treats raw loading as costless and governance as someone else's problem. And it offers no example, so there is no evidence the candidate has operated either pattern. A definition earns partial credit. A trade-off analysis with a defended exception earns the role.
Worked variant
Your interviewer follows up: "We ingest support tickets from a SaaS helpdesk using ELT. Each ticket has a free-text description. Legal says customers sometimes paste passwords and personal health details into those descriptions. What do you change?"
Work it in steps, out loud. First, name the exposure precisely: raw ticket text already lands in bronze and persists there, so anyone with bronze access — and anyone downstream if the field propagates — can read secrets the company never asked for, and an erasure request would have to hunt through free text. Second, move one transformation upstream: introduce a pre-load redaction step for this source only, using pattern detection for credential-like strings and identifier formats plus a classifier pass for health-related content, replacing matched spans with a redaction marker before the record is written. This is textbook ETL applied surgically to one field of one source, inside an otherwise ELT platform. Third, quarantine uncertain cases rather than silently altering or dropping them: low-confidence flags route to a restricted area for review, because dropping a customer's ticket silently is its own harm. Fourth, fix downstream copies: redaction at ingestion does nothing for tickets already stored, so include a backfill pass over historical bronze and a check that no silver or gold model has copied the raw description anywhere. Finally, reduce the chance at source by suggesting a warning in the helpdesk form when pasted text looks like a credential. Pipelines should not be the only line of defence, and saying so shows systems thinking.
Follow-up question
If your raw layer holds three years of unmasked source data because "storage is cheap," who in the company can query it today, how would you find out, and what would a defensible retention and access policy for that layer look like?
A strong response describes discovering actual access from warehouse grant logs rather than from intention, separating human from service-account access, applying a short default retention to raw tables with documented exceptions, requiring a named owner per source, and scheduling access reviews — and concedes that the honest first step is an audit, because most platforms cannot answer "who can query it" from memory.
Question 6: Batch vs Streaming — How Do You Decide How Fresh Data Really Needs to Be?
Question
What is the difference between batch and streaming processing? Walk through how you would choose between them for a real system, and explain how concepts like event time, watermarks, and exactly-once delivery affect that choice.
What they’re really testing
Interviewers ask this to find out whether you reach for streaming because it is impressive or because the problem demands it. Streaming systems are harder to build, test, and operate than batch systems in almost every dimension. Choosing one without a latency requirement that justifies it is not ambition; it is unpriced risk.
Beneath the definitions, three things are being assessed. First, whether you can translate a business need into a freshness requirement in plain units. "Real time" is not a requirement. "The fraud team must block a card within thirty seconds of a suspicious transaction" is a requirement. "Finance closes the books at month end and needs complete, reconciled figures by the morning of the third working day" is also a requirement — and it points firmly at batch.
Third, whether your claims about correctness survive scrutiny. "Exactly-once" is the most casually promised and least casually delivered phrase in data engineering. The interviewer is listening for someone who can explain what is actually guaranteed, by which component, and what the application must still do — usually idempotent writes and deduplication on stable keys — to make the end-to-end result correct.
Full worked answer
Define both patterns by how data moves and when results appear.
Batch processing collects data over an interval — an hour, a day, a full file delivery — and processes the whole set at once on a schedule or trigger. Inputs are bounded: you can count them, checksum them, and re-run the job over exactly the same set. Latency is the interval plus processing time, and in exchange you get simplicity, completeness, and easy recovery: if a run fails, fix it and run it again.
Streaming processing treats data as an unbounded flow of events and processes each event, or each small window, as it arrives. Results update continuously with latency in seconds or less. In exchange you accept continuous operation, state that must be managed and checkpointed, and correctness questions batch never asks because its input politely sits still.
Work through the trade-off as structured considerations, with a consequence attached to each.
Latency. Batch latency is dominated by the schedule: a nightly job cannot produce an answer fresher than yesterday, however fast it runs. Streaming latency is dominated by arrival and window design: a five-minute aggregation window cannot emit a complete result before the window closes, however fast the engine is. Ask which part of the budget the business actually feels.
Throughput. Batch engines are extraordinarily efficient at bulk work — scanning and joining billions of rows in one optimised pass, with no per-event overhead. Streaming engines sustain high event rates, but per-event state lookups, serialisation, and checkpointing tax every record. For pure volume moved per unit of money, a well-tuned batch job is hard to beat.
Cost shape. Batch cost is spiky and schedulable: rent a large cluster for forty minutes at 2 a.m. and release it. Streaming cost is continuous: the system must be provisioned and monitored at peak and at trough, at 3 p.m. and 3 a.m., because events do not respect office hours. A streaming system serving a question nobody asks overnight is still awake and billing.
Correctness and completeness. Batch sees the whole input before answering, so totals reconcile naturally and late files are handled by including them in the next run or re-running the period. Streaming must decide, for every result, whether to wait for stragglers, emit early and correct later, or discard what arrives too late. Those are product decisions disguised as configuration.
Operational burden. A failed batch job is a bounded problem with a bounded input and a re-run button. A failed streaming job may involve state recovery, replay offsets, poison events blocking a partition, and downstream consumers that have already acted on partial output. The on-call reality belongs in the design conversation.
Development and testing. Batch logic can be tested against a fixed snapshot with deterministic expected output. Streaming logic must be tested against sequences — out-of-order arrivals, duplicates, gaps — which multiplies the test space and the time needed to gain confidence in a change.
When a system needs fresh answers and trustworthy answers, two named architectures recur. Know both, and the criticism of each.
Lambda architecture runs two paths over the same events. A speed layer processes the stream and serves approximate, up-to-the-second results. A batch layer processes the complete history more slowly and produces authoritative figures. A serving layer merges them, with batch results progressively replacing speed-layer estimates for the same period. Its strength is pragmatic: freshness now, correctness shortly after. Its weakness is that every business rule must be implemented, tested, and kept consistent twice, often in two codebases. Divergence between the paths is not a hypothetical failure mode; it is the default outcome without relentless discipline.
Kappa architecture removes the batch path and treats everything as a stream. When logic changes or history must be recomputed, you replay the retained event log — from a durable system such as Kafka, where events persist for days, months, or indefinitely — through the single streaming job at high speed to rebuild state and outputs. One codebase, one set of semantics. Its preconditions are the catch: long-retention, high-throughput event storage, replay capacity well above live traffic so reprocessing does not take longer than the history it covers, and consumers who tolerate outputs being revised as a replay passes over them.
This next distinction is the conceptual heart of the question, and it deserves slow, precise treatment.
Event time is when the thing actually happened, according to the source: the moment a customer clicked, a sensor read, a payment was authorised. It is usually carried as a timestamp inside the event itself.
Processing time is when your system happens to process the event, according to your servers’ clocks. It depends on network delays, retries, queue backlogs, and whether the source was a phone in airplane mode for six hours.
Ingestion time, the useful middle term, is when the event first entered your platform.
A watermark is the mechanism streaming engines use to make that judgement explicit. It is a moving threshold in event time — a statement of the form "we believe we have now seen essentially all events with an event time earlier than this point." When the watermark passes the end of a window, the engine emits the window's result and, depending on configuration, finalises it.
Everything important follows from "essentially." A watermark is a heuristic, not a guarantee, typically derived from the maximum event time observed minus an allowed delay. Set the delay short and results arrive quickly but late events fall behind the watermark and must be handled as exceptions. Set it long and results are more complete but freshness — the entire reason for streaming — erodes. Late data therefore needs an explicit policy, chosen per use case: drop late events and accept the undercount; allow a late-arrival period during which windows re-emit corrected results, and design consumers to accept revisions; or route late events to a side output for separate reconciliation, which is often where a small batch correction job re-enters the design. And out-of-order arrival is normal, not exceptional: events from different devices, regions, and partitions interleave arbitrarily, so any design that quietly assumes arrival order equals event order will produce confident, wrong aggregates.
Handle exactly-once with visible care; precision here is a strong senior signal. No distributed system delivers each event's effect exactly once by magic. What real systems provide is a combination of narrower guarantees. At-least-once delivery is the common transport guarantee: events may be redelivered after failures, so duplicates are expected and must be survivable. Checkpointed state in engines such as Flink or Spark Structured Streaming records, atomically with source offsets, how far processing has progressed, so a restart resumes without skipping or starting over. Transactional or idempotent sinks complete the chain: the output write either participates in the same commit as the checkpoint, or applying it twice has the same effect as applying it once — an upsert on a stable event key, a merge into a table keyed by event identifier, deduplication on a unique ID assigned at source.
The end-to-end result engineers call exactly-once is therefore more honestly described as effectively-once processing: at-least-once delivery, plus deduplication, plus idempotent writes, arranged so duplicates never become visible in the final data. Say it that way. Then add the corollary: the guarantee ends at your sink. If a downstream system consumes your output and sends emails or moves money, that system needs its own idempotency, because your checkpoint cannot roll back an email. Batch, for contrast, achieves correctness more bluntly: bounded input, deterministic re-runs, and overwrite-or-merge writes per period make a batch job naturally idempotent with even modest care. That bluntness is a feature, and one reason regulated reporting stays batch long after dashboards go streaming.
Say the next part without embarrassment: recommending batch where it fits is a mark of judgement, not a lack of ambition. Batch is honestly better when the decision cadence is slow — monthly reconciliation, quarterly regulatory submissions, payroll, and board reporting gain nothing from second-level freshness and lose auditability if results keep revising. It is better when completeness outranks freshness, because a bounded batch that waits for the file arriving six hours late beats a stream that must guess when to close. It is better when the computation is inherently global — training features over a year of history or large joins across entire datasets are bulk operations. It is better when the team is small, because a streaming platform is a permanent operational commitment and a three-person team serving reports read each morning is better served by excellent batch pipelines and sleep. And it is better when the source is a batch anyway: many enterprise sources deliver only files or periodic extracts, and wrapping a nightly file in streaming clothing produces streaming costs for batch freshness — the worst cell in the matrix.
Close by designing one system that uses both patterns, because that is what the interviewer will ask next anyway. A retailer wants two things from the same events: the growth team wants a live view of sessions and checkout progress during a major sale, and the payments team wants to intercept fraudulent checkouts while fulfilment can still be stopped.
Capture once, durably: clicks and checkout events are published to a durable event log with a unique event identifier and the client's event timestamp attached at source, retained long enough to support replay. For fraud, a streaming job consumes checkout events, maintains account state — attempts per card, velocity across accounts, mismatch between billing and delivery geography — and scores each checkout within seconds. Its windows run on event time with a watermark of a couple of minutes, because a fraud decision cannot wait ten. Scoring is idempotent: a redelivered event re-scores the same checkout and overwrites the same decision record rather than double-counting velocity, and a hold decision is written to a serving store the checkout system reads synchronously. Late events arriving after a decision are not ignored; they update the account's risk profile for the next attempt, the correct destination for information that missed its moment.
For the live dashboard, a second streaming view updates session counts and funnel progress continuously, labelled in the interface as live estimates. Nobody reconciles money against this path, and saying so in the design is what makes approximation acceptable. Overnight, a batch job processes the complete event log for the day — including every late, duplicated, and out-of-order event, deduplicated on event identifier — and produces the reconciled sessions, conversion, and revenue figures finance relies on. Where batch figures differ from live estimates, batch wins, and the size of the difference is itself monitored as a health signal for the streaming path. The honest summary: streaming was bought, at real operational cost, for exactly one decision that expires in seconds — stopping a fraudulent checkout. Everything that merely wanted to feel live got estimates, and everything that had to be right got batch. Freshness purchased only where a decision's value decays is the answer this question was built to elicit.
Common wrong answer
The common wrong answer is a slogan: "Batch is legacy, streaming is real-time, so streaming is better — and our platform gives us exactly-once delivery, so correctness is handled."
Worked variant
The interviewer changes the facts: "Our nightly batch job loads yesterday's transactions and builds the risk team's report. It works. Now the risk team says a new fraud pattern costs us money within hours, and they want detection 'as soon as possible.' What do you do?"
Work it as an investigation before it becomes a build. First, quantify "as soon as possible": sit with the risk team and price the delay. How much is lost per hour of undetected activity, and at what point does intervention stop working — before settlement, before fulfilment, before withdrawal? If the true deadline is two hours, a well-built hourly micro-batch may satisfy it at a fraction of streaming's cost. Do not build a streaming platform to meet a requirement that was never measured. Second, check the source before the engine: can transactions even be obtained continuously? If the payment processor exposes only an hourly file, the constraint is upstream and the first project is an ingestion negotiation — a webhook or change-data-capture feed — not a streaming purchase. Third, scope the streaming build to the detection, not the estate: stand up one job for the new fraud signals, writing alerts and holds, while the nightly batch continues to produce the reconciled risk report. Resist re-platforming trusted reports onto the stream. Fourth, plan reconciliation between the two from day one: the stream will miss late events and the batch will find them, so define in advance that batch findings retroactively flag accounts the stream cleared, and measure the stream's miss rate from that comparison. Without this loop you cannot know whether the expensive new system works. Finally, revisit after a proving period: if the streaming path demonstrates value, consider widening it; if losses concentrate in patterns an hourly job catches, say so and shrink the design. The variant rewards engineers who treat architecture as a reversible sequence of bets, not a single leap.
Follow-up question
Your streaming job aggregates purchases into ten-minute windows using event time, with a watermark that allows five minutes of lateness. A customer's phone, offline on a long flight, reconnects and delivers two hundred purchase-related events nine hours after they occurred. Trace exactly what happens to those events, what the already-published window totals do, and what — if anything — the business sees change.
A strong response walks the events to the watermark, finds them far behind it, and then follows the system's stated late-data policy rather than improvising one: dropped from the live aggregates with a metric recording the drop, routed to a side output, or absorbed by overnight batch reconciliation — and it names which published figures were always labelled estimates, so the correction arriving through batch is an expected event with an owner, not a surprise discovered by finance.
Question 7: Partitioning, Clustering and Sorting for Analytical Queries
Question
You have a fact table with three years of event data, roughly two billion rows. Most analyst queries filter on event date, then on one or two other columns such as country or product category, yet full scans are common and the bill keeps rising. How would you use partitioning, clustering and sorting to make those queries fast and cheap, and how do you choose the keys?
What they’re really testing
This question separates engineers who have merely created a partitioned table from engineers who understand why partitioning works and what it costs when you get it wrong. The interviewer is listening for four things. First, the physical mechanism: a partition is not an index — partitioning divides a table into storage segments so the engine can skip entire segments when a filter matches, which is partition pruning, and it only happens when the filter is written in a usable form. Second, cardinality: a key with too many distinct values creates thousands of tiny fragments; a key with too few creates enormous fragments that cannot be pruned usefully. Third, the finer-grained ordering inside a partition — clustering and sort keys — which helps with the second and third filters. Fourth, files, not just logical tables: in lakehouse systems the number and size of underlying files determine how much metadata the engine must list, open and schedule, so a logically perfect design on ten thousand tiny files will still be slow.
Full worked answer
Start from the workload, not from the table. I would examine ninety days of query history: which columns appear in filters, which appear together, and what date ranges do queries touch? In most event workloads almost every query filters on a date — usually the last seven, thirty or ninety days — and a smaller set of columns (country, channel, product category) appears as the second filter. Almost nothing filters first on a high-cardinality identifier such as a user ID, because analysts rarely know those values in advance. So: partition on the date, cluster or sort on the next most common filters.
Partitioning on event date, usually at daily grain, gives the engine its biggest win:
SELECT country, COUNT(*) AS events
FROM analytics.events
WHERE event_date >= DATE '2026-11-01'
AND event_date < DATE '2026-11-08'
GROUP BY country;Without partitioning, the engine reads the whole table to find one week, because those rows are scattered through three years of storage. With daily partitioning on event_date, the query prunes to seven partitions out of more than a thousand — roughly one one-hundred-and-fiftieth of the data — and scan cost, billed by bytes read on most cloud warehouses, falls by the same proportion. Partition pruning is the first-order effect; everything else is refinement.
Pruning, however, has conditions, and a good candidate names them unprompted:
The filter must be on the partition column itself, or on an expression the engine can resolve to a range on it.
WHERE DATE(event_timestamp) = DATE ‘2026-11-05’may defeat pruning if the partition column is the timestamp, because the engine cannot always prove which partitions the function touches. Store a clean date column and filter on it directly.Literal or easily resolved ranges prune best; a filter built from a subquery, a join, or a volatile function may be evaluated too late for the planner to skip partitions.
Pruning helps in proportion to selectivity. A query touching eleven months of a twelve-month table gains almost nothing. Partitioning rewards recent, bounded analysis; it does not make full-history scans cheap.
Choosing the partition key is a trade-off between pruning power and partition count, and this is where the cardinality traps live:
Too high cardinality. Partitioning by
user_id,order_idor minute-level timestamp explodes the partition count: each partition carries metadata and may hold only a few rows, the planner spends its time listing fragments instead of reading data, and some engines impose hard partition limits the design can hit as data grows.Too low cardinality. Partitioning by year, a boolean flag, or a three-value status creates partitions so large that pruning barely reduces the scan — a query for last week still reads a full year.
Skew. Partitioning by an uneven column — country, when ninety percent of traffic comes from one market — creates one giant partition and many trivial ones; queries for the dominant value gain nothing.
The wrong column entirely. Partitioning by ingestion date when queries filter on event date is a classic quiet failure: the dates agree most of the time, but late-arriving data lands in the wrong partitions and queries must scan extra days to be safe.
Daily partitioning survives these traps for event data because it is bounded and predictable: about 365 partitions per year, each of a workable size, aligned with how analysts ask questions. Monthly suits slower, larger historical tables; hourly is justified only at very large scale, where a single day is already many gigabytes.
Once the partition is chosen, clustering and sorting handle the filters inside it. Engines use different words — clustering keys, sort keys, Z-ordering, liquid clustering — but the idea is the same: co-locate rows with similar values so a filter can skip blocks within the partition. If analysts most often filter on country, then product category, I would cluster on country first and category second. Order matters, as in a composite index: a filter on country alone benefits fully, country and category benefits more, and category alone benefits least, because category values are interleaved across countries in the stored order.
Clustering is not a weaker version of a row-store index, either. An index excels at finding a handful of rows by exact key; a columnar analytical table is read in large blocks and maintained in bulk, so a per-row index would punish every write. Clustering serves a different pattern: not finding one row, but skipping most blocks inside the partitions already selected.
The final layer is file sizing, the layer most often missed in interviews. In object storage, data lives in files, commonly Parquet. Two failure modes dominate. The small-files problem: micro-batch or streaming writes, aggressive partitioning or repeated partial updates leave thousands of files of a few megabytes or less, and every query must list, open and schedule tasks for each one, so planning overhead dominates and modest queries become slow. And files that are too large, which reduce parallelism and make partial reads wasteful. The practical target is files in the broad range of tens to a few hundreds of megabytes. You hold it operationally: batch small writes before committing, run periodic compaction into fewer right-sized files, avoid partitioning so finely that each partition holds only a sliver, and watch update patterns that fragment files.
To validate, I would run the ten most expensive queries before and after, comparing bytes scanned as well as elapsed time, check partition sizes for skew and file counts per partition, and confirm common queries write date filters in a prunable form.
Common wrong answer
The weak answer says: “Partition by user ID, add indexes on every column people filter on, and if queries are still slow, add more partitions.” Almost every clause is wrong. Partitioning by a high-cardinality identifier explodes the partition count and helps almost no real query, because analysts filter by date first. Indexes on every column misunderstand how columnar engines read data and punish every write. And past the useful point, “more partitions” is a dial for metadata overhead and small files, not speed. A related weak answer partitions on several columns at once — date, country and category — ignoring the multiplicative explosion of mostly empty partition combinations.
Worked variant
The interviewer changes the setting: “This is not a warehouse table. It is a lakehouse table on object storage, written by a streaming job every minute. Queries are still mostly last-thirty-days by date and country. What changes?”
The principles do not change; the emphasis does. I would still partition by event date at daily grain, resist hour or country as a first-level partition, and let clustering handle country within each day. The streaming writer is the real problem: committing every minute produces roughly 1,440 files per day — a small-files problem manufactured on a schedule. Buffer and commit on a size or time threshold that produces right-sized files, and schedule regular compaction with a retention policy for old file versions so time travel does not quietly double the storage bill.
Follow-up
A likely follow-up is: “Your most important dashboard does not filter on date at all. It looks up the last ninety days for a single customer account, thousands of times a day. Does your design serve it?”
The honest answer is: not well, and it should not try to. A date-partitioned analytical table is optimised for scanning a bounded slice of history across many accounts, not for repeated single-account lookups, and repartitioning on account would wreck it for every other query. Serve the two workloads separately: keep the analytical table as designed, and publish the account-level view into a store built for point lookups, or a separate clustered aggregate keyed by account. Naming that boundary is usually the answer the follow-up was designed to draw out.
Question 8: Schema Evolution and Data Contracts
Question
Your pipeline consumes events produced by another team. One morning a field you depend on has been renamed, its type has changed, and half your jobs have failed. How do you design for schema evolution, what is a data contract, and how do you stop an upstream change breaking downstream consumers without warning?
What they’re really testing
The interviewer is testing whether you understand that most data breakage is organisational before it is technical. Schemas change because products change, because producers do not know who consumes their data, and because nothing made the cost of a breaking change visible to the person making it. A strong answer works on three levels: it classifies changes correctly; it describes machinery — registries, compatibility checks, contract tests — that catches a dangerous change before production, not after; and it describes a human process of ownership, notice, versioning and a calm incident routine for the night the machinery is bypassed anyway. Candidates who only list tools, or only talk about process, both fall short.
Full worked answer
Start by classifying changes, because the response depends on which kind a change is.
Additive changes are usually safe. Adding a new nullable field, a new optional column, or a new value to an extensible category list does not disturb consumers reading the fields they already know — a well-built consumer ignores fields it does not recognise. State that tolerance in the contract itself: producers may add, consumers must not break on additions.
Breaking changes are the dangerous set, and they deserve to be named precisely:
Renaming a field or column, which to a consumer is indistinguishable from deleting the old one and adding an unrelated new one.
Removing a field that any consumer still reads.
Changing a type — an integer becoming a string, a timestamp becoming a date, an amount moving from minor units to major units while keeping the same name.
Changing meaning without changing shape — the most treacherous change of all. The column is still
revenue, still a decimal, but now excludes refunds, or is in a different currency. No schema check catches this automatically, which is why contracts must document meaning, units and examples, not just names and types.Loosening a guarantee consumers rely on — a field that was always populated becoming frequently null, or a unique identifier becoming duplicated.
Between those poles sit changes safe only in one direction — widening an integer to a long is typically safe for readers, unsafe for writers on the older shape; reordering columns is harmless in a format that reads by name, destructive in one that reads by position. Compatibility modes formalise exactly this direction-dependence.
A schema registry makes the classification enforceable. Producers register the schema for a topic or dataset centrally; every message or file carries a reference to the schema version it was written with, so consumers resolve exactly how to read it. Before a new version can be used, the registry checks it against a configured compatibility mode:
Backward compatibility means a consumer using the new schema can read data written with the previous schema. This lets you upgrade consumers first, and it is the most common requirement for event streams.
Forward compatibility means a consumer using the previous schema can read data written with the new schema. This protects you during rollbacks and when consumers upgrade slowly.
Full compatibility requires both directions — the strictest and safest setting for long-lived, widely consumed datasets.
No compatibility check exists as an option, and choosing it for a shared production dataset is a decision an interviewer should hear you refuse to make casually.
When a proposed schema fails the check, registration is rejected: the breaking change is stopped at the producer’s desk, in daylight, instead of in your pipeline at 2 AM. That shift — from detection in production to prevention at publication — is the entire point.
A data contract is the agreement above the schema: the schema says what the data looks like; the contract says what it means and what consumers may rely on. A useful contract covers:
Ownership. A named team and contact route that owns the dataset and approves changes.
Schema and semantics. Field names, types, units, time zones, nullability, and the meaning of each important field, with examples.
Guarantees. Freshness, completeness, uniqueness of keys, and valid ranges or enumerations.
Compatibility and versioning policy. Which changes are allowed without notice, which require a new version, and how long an old version is supported during migration.
Change process. How a breaking change is proposed, who approves it, how much notice consumers receive, and how dual-running works.
Contract enforcement is where many programmes quietly fail, because a contract stored as a wiki page enforces nothing. Enforcement means expressing it in a form the pipeline can check: schema validation on write and on read, automated tests in the producer’s build, data quality checks on arrival — null rates, uniqueness, volume within an expected band — and quarantine with an alert when checks fail, rather than dropping bad records without trace or letting them poison the main table. The goal is a loud, early, specific failure at the boundary, instead of a wrong number in a report three days later.
For managed evolution, the pattern is expand, migrate, contract — over time, not in one release. Expand: add the new field alongside the old one, with the producer populating both. Migrate: move consumers to the new field one by one, verifying each. Contract: only when no consumer reads the old field, remove it in a separate, announced change. The pattern applies to renames, type changes and field splits alike. It is deliberately slow, because it converts a flag-day breakage into steps each of which can be verified and reversed.
Now the 2 AM scenario, which interviewers ask directly, judging temperament as much as technique. An upstream team has broken you anyway. The calm process is:
Contain first. Pause the affected jobs, protect the last known good partitions, and keep downstream tables serving the last correct state rather than a half-loaded one. A stale but correct dashboard beats a fresh and wrong one.
Identify the exact change. Diff the incoming schema and a sample of records against the last good batch: which field, old shape, new shape, from which batch.
Decide: adapt, hold, or roll back. If the change is additive or easily adapted, apply the smallest safe mapping, noted as temporary. If it is semantic — a meaning change you cannot verify at 2 AM — hold the data rather than guess; numbers built on a guessed meaning are worse than late numbers.
Communicate once, clearly. Tell consumers what is affected, what they are currently seeing, and when the next update will come. Silence erodes trust faster than the outage itself.
Fix forward in the morning. Backfill from the point of change once correct handling is agreed, add the missing registry check or contract test that would have caught this class of change, and record the incident against the contract so the gap is closed rather than merely survived.
Common wrong answer
The weak answer is: “Make everything a string, store the raw JSON, and figure out the schema when you read it.” Keeping an untouched raw copy is good practice at the landing layer, but as a total strategy schema-on-read does not prevent breakage; it postpones and privatises it — every consumer discovers the rename independently, in their own query, at their own 2 AM. The mirror-image weak answer, “freeze the schema forever,” is no better: products change, so freezing drives change underground into overloaded fields and shadow datasets no contract governs.
Worked variant
The interviewer narrows the case: “A producer needs to split one field, address, into street, city and postcode. Several teams consume address. Walk me through the change.”
I would treat it as textbook expand, migrate, contract. The producer adds the three new fields while continuing to populate address, and the contract marks address deprecated with a stated removal date; the registry check passes because the change is purely additive. Consumers migrate at their own pace — I would track who still reads address from access logs, not by asking around — with a consistency check comparing the old field against a reconstruction from the new ones. Only when the logs show no remaining reads and the notice period has elapsed is address removed, as its own small, announced change. No consumer was broken, and at every point the change could have been paused.
Follow-up
A likely follow-up is: “How do you handle a consumer you do not know about?”
Honestly: you cannot fully prevent breakage for consumers who are invisible to you, and pretending otherwise is a red flag. You can shrink the invisible set: publish datasets through governed access rather than raw storage paths, so consumers register and become known; use access logs to discover readers before planning a removal; keep deprecation windows long and retain old versions during migration, so a surprised consumer finds a working, if dated, dataset instead of an error. Then fold every discovered consumer into the contract’s list, so the unknown set shrinks with every change.
Question 9: Idempotency, Backfills and Reprocessing
Question
Your nightly pipeline loads yesterday’s orders into the warehouse. Last night it ran twice, and this morning the revenue numbers are doubled. Separately, a pricing error affected the last forty days and those days must be reloaded. Explain idempotency, how you design a load that can safely run more than once, and how you approach a backfill of that size.
What they’re really testing
This is the most operational question in the set. Almost anyone can describe a pipeline that works when everything runs exactly once, in order, on good data; production does not behave like that. Jobs are retried, restarted and replayed after outages. The interviewer probes three ideas: idempotency as a property of the result, not the run; the concrete mechanisms — partition overwrite, merging on a key, deduplicating on a stable identifier — and when each is safe; and whether you treat a backfill as a controlled operation, rather than “just run the job forty times and hope.”
Full worked answer
Idempotency means applying the same operation repeatedly produces the same final state as applying it once. For a data load: if the load for 19 November runs once, twice, or five times, the table afterwards contains each 19 November order exactly once, with the same values, every time.
The doubled-revenue failure happens with a blind append:
INSERT INTO analytics.orders
SELECT * FROM staging.orders
WHERE order_date = DATE '2026-11-19';
Nothing in that statement cares that those rows are already present; run it twice and every row exists twice. Appends are correct for truly append-only data with guaranteed once-only delivery — but a retriable nightly batch load is not that situation, and designing it as an append is designing in the double-count.
Two patterns cover most batch loads, with deduplication underneath both.
The first is partition overwrite. If the load is bounded to a clean partition — one day, in this design — replacing the whole partition with the freshly computed result is naturally idempotent: overwrite only the target partition, insert its complete freshly derived contents, and running it again replaces the partition with the same contents. The state does not accumulate.
This pattern is simple and easy to verify, because the unit of replacement matches the unit of work. It works best when the source for that day can be fully re-derived and the partition is a manageable size; it works badly when a day’s data is corrected across many later days, because then no single run holds the day’s complete truth.
The second pattern is merge, or upsert, on a stable business key: reconcile row by row, updating rows whose keys exist and inserting new keys. It is the right pattern when corrections arrive late, when the load is not cleanly bounded by date, or when the target accumulates state — a customer dimension, or an order record moving through statuses. A worked example:
MERGE INTO analytics.orders AS target
USING (
SELECT order_id, order_date, customer_id, status, amount
FROM staging.orders
WHERE order_date = DATE '2026-11-19'
) AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN UPDATE SET
target.order_date = source.order_date,
target.customer_id = source.customer_id,
target.status = source.status,
target.amount = source.amount
WHEN NOT MATCHED THEN INSERT (
order_id, order_date, customer_id, status, amount
)
VALUES (
source.order_id, source.order_date, source.customer_id,
source.status, source.amount
);
Run that merge once and the day’s orders are present and correct. Run it again and every row matches and is updated to the same values — nothing is duplicated, because identity is decided by order_id, not by how many times the job executed. That is replay safety: replaying an input, in whole or in part, without corrupting the target.
The merge pattern carries two conditions worth stating. The key must be stable and unique in the source — if staging contains the same order_id twice, many engines fail the merge with a multiple-match error, so deduplicate the source first. And deletions need an explicit decision: a plain merge never removes rows that vanished upstream, so if the source can retract records, the contract must say how — a delete flag handled in the merge, or periodic full reconciliation of a bounded partition — rather than letting ghost rows accumulate.
Deduplication supports both patterns. Every record needs a dedup key — the identifier by which “the same record again” is recognised. The strongest keys are stable source-assigned identifiers, such as an order ID or event ID; a key constructed from a combination of columns is weaker, because a legitimate correction to one column makes the corrected record look new. The rule for which copy wins must be deterministic — latest by a trustworthy update timestamp, or the authoritative source — never “whichever the query encounters first.”
With idempotent loads in place, a backfill becomes manageable instead of a gamble. Forty days of pricing corrections is significant but routine if approached in order:
Define the scope precisely. Which dates, tables, downstream aggregates and reports are affected? A pricing change may touch the orders fact, every rollup built from it, and exported or cached copies. Listing the blast radius first prevents the classic outcome where the fact table is fixed and the dashboard still shows old numbers.
Confirm the load is idempotent before you start. A backfill is reprocessing at scale: if the daily load is a blind append, backfilling forty days manufactures forty days of duplicates. If it is not yet idempotent, fixing that is step one, not a separate project.
Choose the order deliberately. Chronological order is the safe default, because later calculations often depend on earlier state — running balances, slowly changing dimensions, period-to-date aggregates. Where each day is provably independent, days can run in parallel, but independence should be established, not assumed.
Bound and checkpoint the work. Run in chunks — a day or a few days at a time — recording which have completed and verifying each before moving on. A backfill that fails on day thirty-one should resume there, not restart from day one.
Control concurrency with the live pipeline. Tonight’s normal load and the backfill must not write the same partitions at once: pause the regular load for the affected range, sequence the backfill around it, or partition the work so the two never overlap.
Verify against independent checks. After each chunk, compare row counts and key totals for the reprocessed dates against source expectations, confirming counts have not grown in a way that signals duplication. After the full range, reconcile the corrected revenue with the business, because they own the number.
Communicate the moving numbers. While the backfill runs, tell consumers which dates are already corrected and which are not, and mark the work complete only when the whole range and its aggregates are rebuilt and verified.
One further habit separates a senior answer: design for reprocessing from the start. Keep raw, untouched input for a generous retention window and make transformations deterministic functions of it, so any historical day can be rebuilt by re-running the same code over the same raw data. If the raw input was discarded, an honest backfill may be impossible — and fixing a transformation bug changes nothing in data already written; the affected range must still be reprocessed.
Common wrong answer
The weak answer is: “Add a step that checks whether the job already ran, and skip it if it has.” That guard fails in every interesting way. After a partial failure the job “ran” but loaded half the day, and the guard now prevents the completing re-run. Two concurrent executions can both pass the check before either records it. And the guard does nothing for the legitimate re-runs you need — the correction, the late data, the backfill. The related weak answer, deleting duplicates afterwards with a cleanup query, treats duplication as an accident to tidy rather than a property to guarantee, and the cleanup must solve the same identity problem under worse conditions, after the damage is already visible in reports.
Worked variant
The interviewer varies the setting: “This is a streaming pipeline, not a nightly batch. Events arrive continuously, delivery is at-least-once, and the same event can be delivered twice minutes or hours apart. Where does idempotency live now?”
It lives in the same place conceptually — identity and reconciliation — with streaming mechanics. Each event must carry a stable source-assigned identifier, because without one no downstream system can recognise a redelivery. The sink must tolerate repeats: merge or upsert on that identifier, or use a table format that deduplicates on it at commit time, rather than appending every delivery. I would use a platform exactly-once mode if offered but still keep the key-based design, because platform guarantees cover delivery within the pipeline, not a producer that genuinely submits the same event twice. Deduplication windows must outlive the latest plausible redelivery. The test is the same as in batch: replay yesterday’s stream and prove the result is identical to processing it once.
Follow-up
A likely follow-up is: “The backfill will take nine hours and the business wants corrected numbers by morning. What do you do?”
I would not answer with heroics. First, narrow what “corrected numbers” must mean by morning: often the business needs the headline revenue figure and the most-used reports corrected, not every downstream table. Sequence the backfill so the highest-value targets are reprocessed and verified first, parallelise across days where independence is proven, and give a specific, honest position — these reports by this time, the remainder by that later time. If even the narrowed scope cannot be done safely, say so early, while the business can still choose between a later correct number and a clearly flagged provisional one. Never shorten verification: a backfill that finishes on time and quietly duplicates two of its forty days has not met the deadline; it has scheduled a second, worse incident.
Question 10: Data quality, testing and observability in pipelines
Question
Your pipeline runs green every morning, yet the business team says the revenue dashboard is wrong again. How do you design data quality checks, testing, and observability so that bad data is caught before it reaches a dashboard, and so that when something does break, you can find the cause quickly?
What they’re really testing
This question separates engineers who can move data from engineers who can be trusted with data other people make decisions on. The interviewer is not looking for a list of tool names. They are testing whether you think in layers: prevention at the point of ingestion, detection while the pipeline runs, containment before bad rows reach consumers, and investigation after an incident. They also want to hear judgement about trade-offs. A check that blocks every load on the slightest deviation will protect a dashboard and paralyse a business. A pipeline with no blocking checks will stay available and quietly become untrustworthy. Strong candidates explain which failures should stop a publish, which should warn, which should quarantine a subset of records, and how that decision depends on the consumer and the contract the data is meant to satisfy.
There is a second, quieter test underneath. Observability is about the next engineer at 2 a.m., who may be you in three months with no memory of this design. Can that person tell, from signals alone, what was expected, what arrived, what changed, and where in the lineage the divergence began? If your answer covers checks but not lineage, freshness signals, alert quality, and run history, it will sound incomplete to an experienced panel, because detection without diagnosis leaves the hardest part of the incident untouched.
Full worked answer
Start by defining what “good” means for a specific dataset, not in the abstract. Every important table should have an owner, a stated purpose, a freshness expectation, a set of key columns that must be present and unique, and ranges or categories that are plausible for its measures. That definition is sometimes called a data contract when it is agreed with a producer, and a set of expectations when it is enforced inside the pipeline. The name matters less than the discipline: expectations are written down, versioned, and tested, rather than living in one engineer’s head.
Then organise checks into families, because each family catches a different failure mode.
Freshness checks ask whether data arrived when it should. A sales table that updates hourly should raise an alert if its maximum event timestamp or its load timestamp falls behind the agreed window. Freshness is the earliest warning for many upstream outages, because an empty or stale load often still completes successfully. Report freshness in business terms — “orders are current to 06:00, expected 07:00” — rather than only as a job status, so a consumer can judge whether to use the dashboard at all.
Volume checks ask whether the amount of data is plausible. Compare today’s row count, distinct key count, and null rates against a rolling baseline for the same weekday, because Monday and Saturday rarely look alike. A drop of 40 per cent in transactions, a sudden doubling after a retry, or a key column that is null for a whole partition are all volume signals. Absolute thresholds work for stable feeds; relative thresholds and seasonal baselines work better for feeds that grow or swing. Either way, a volume check should name the expected range and the observed value in its alert, so triage does not start from zero.
Schema checks ask whether the shape still matches the agreement. Has a column been renamed, dropped, or retyped? Has a new category appeared in a status field that downstream logic treats as closed? Has a decimal arrived as a string, truncating or failing silently in one engine and loudly in another? Schema checks belong as early as possible, ideally at ingestion against a registered contract, because a shape change caught at the boundary costs minutes, while the same change discovered after three transformations costs a backfill and an apology.
Content and validity checks ask whether values make sense inside the shape. Examples include: order totals are not negative where refunds are modelled separately; an end date is not before a start date; a country code belongs to the accepted list; referential integrity holds between fact and dimension keys; duplicate business keys do not appear within a partition. These checks encode business rules, so they need an owner who can say whether a violation is a bug, a legitimate exception, or a rule that has aged out.
Anomaly detection sits on top of these families rather than replacing them. Simple statistical methods — rolling averages, standard-deviation bands, day-over-day and week-over-week comparisons on core metrics — catch slow drift that fixed thresholds miss: a gradual fall in match rate between two systems, a creeping rise in late-arriving records, a source that begins sending a growing share of records with a default value. More advanced methods can help on high-volume metrics, but a candidate should be candid that anomaly detection produces candidates for investigation, not verdicts. Every anomaly system needs a feedback loop: which alerts were real, which were noise, and what threshold or baseline changed as a result. Without that loop, alert fatigue sets in, and alert fatigue is itself a data quality failure because real incidents start being ignored.
Next, explain where checks run. Unit-style tests cover transformation logic on small, controlled inputs: given these five input rows, including an edge case and a duplicate, the model should produce these four output rows. Contract and ingestion checks run on arrival, before transformation. Post-load checks run on the published table, because they validate what the consumer will actually see, including joins and aggregations that earlier checks cannot see. Testing only at one stage leaves blind spots. A transformation can be logically correct and still publish stale data; a load can be fresh and still be wrong after a faulty join.
Containment is the part many answers skip. When a check fails, what happens to the data? Three patterns cover most situations:
Fail and hold: the publish step stops, the previous good version remains live, and an alert fires. Use this for contract-breaking schema changes, missing partitions, and violations in regulated or financial reporting tables where wrong data is worse than old data.
Quarantine and continue: rows that fail row-level rules are written to a quarantine table with the rule name, the failure reason, and the raw payload, while passing rows continue to the main table. Use this when failures are localised — a single source region sending malformed records, for example — and when losing the whole load would do more harm than isolating a subset. Quarantine only works if someone owns reviewing it; an unreviewed quarantine table is a landfill with good intentions.
Warn and publish: the load completes, a visible flag is attached to the dataset or dashboard, and a ticket is raised. Use this for soft deviations, such as a plausible but unusual volume movement during a known promotion, where blocking would hide real business activity.
The key sentence in an interview is that the choice is made in advance, per dataset and per check, not improvised during an incident. Severity, action, and owner should be part of the check definition itself.
Observability then ties the system together. At minimum, a mature pipeline emits structured logs for each run, metrics for rows read, rows written, rows quarantined, duration, and check outcomes, and a run identifier that follows data through every stage. Lineage — which upstream tables and which code version produced this output, and which dashboards and models consume it — turns an alert into a map. When a source column changes, lineage answers the two questions an incident actually turns on: what else did this feed, and who is affected downstream? Keep lineage practical. Even a maintained dependency graph generated from your orchestration and transformation definitions, plus a catalogue entry naming the owner and consumers, is far better than tribal knowledge.
Service-level agreements give observability a promise to measure against. Define a small number of service-level indicators for important datasets: freshness within a stated window, completeness above a stated proportion of expected records, and validity above a stated pass rate on critical checks. Attach an error budget in spirit, even if you never use the term formally: if a table misses its freshness target repeatedly in a month, the next work is reliability work, not new features for that pipeline. In an interview, naming one or two concrete indicators for a named dataset sounds far stronger than claiming you would “monitor everything.”
Testing deserves one more distinction, because candidates often conflate two different environments. Transformation logic should be tested before deployment, in the same continuous integration step that checks code: run the models against small fixture datasets that deliberately include duplicates, late records, null keys, boundary dates, and a source row that violates a business rule, and assert not only that valid output appears but that invalid input is rejected or quarantined in the expected way. Production data should then be checked after deployment, because fixtures can never reproduce the full variety of a live source — a new region, a partner feed with a different null convention, a currency that appears twice a year. Pre-deployment tests prove the logic does what its author intended; post-deployment checks prove the intention still matches reality. A pipeline that has only the first will fail confidently when the source drifts. A pipeline that has only the second will catch errors it could have prevented. Naming both, and giving one concrete fixture case, signals that you have actually maintained a test suite rather than merely enabled one.
Finally, close the loop with incident practice. When bad data does reach consumers — and a credible candidate admits it eventually will — the response is to mark or roll back the affected partition, notify consumers through the same channel they actually read, publish the affected date range, backfill from the corrected source once fixed, and add the check that would have caught this failure to the suite. That last step is the difference between an incident and a recurring incident.
Common wrong answer
The weak answer is a tool list with no reasoning: “I would add Great Expectations, set up dbt tests, and put alerts in Slack.” It names products instead of decisions. It usually fails in four predictable places. First, it treats every check as blocking, which in practice means teams disable checks under delivery pressure. Second, it tests only transformations and never freshness or volume, so the most common real failure — data that never arrived — passes green. Third, it has no containment story, so a handful of bad rows either poison a whole table or force a full reload. Fourth, it confuses monitoring a job with observing data: a green job badge says the code ran, not that the output was right. Interviewers probe exactly here, asking “the job succeeded — now what?” A candidate who has only the tool list has nowhere to go.
Another common miss is promising 100 per cent data quality. Experienced interviewers hear that as inexperience. Sources change without notice, late data is a property of real systems, and some business rules have legitimate exceptions. The credible promise is narrower and stronger: critical failures are caught before publish, localised failures are isolated, everything important is observable, and every escape improves the checks.
Worked variant
The interviewer adapts the question: “Our clickstream volume dropped 30 per cent overnight, but every job is green. Walk me through your first thirty minutes.”
A strong response works the families in order. Confirm the drop is real by checking the raw ingestion counts, not only the modelled table, and compare against the same weekday baseline to rule out a calendar effect. Check freshness and partition completeness per source and per region, because an aggregate drop often hides one missing feed. Check schema and contract logs for a silent change, such as a new application version sending events under a renamed field that now lands as null and gets filtered. Check the quarantine and dead-letter counts, in case a rule began rejecting a whole class of events. Use lineage to see whether the drop exists at the source or appears after a specific transformation deployed yesterday — and check the deployment history for that window before theorising about user behaviour. Communicate early: post the affected range and the current hypothesis, even before the cause is confirmed, so analysts do not build on the number. If the cause is a missing regional feed, hold or flag the affected partitions rather than republishing a partial total as if it were complete. The variant rewards the same structure as the main answer, applied under time pressure: verify the signal, localise it in the lineage, contain it, communicate it.
Follow-up
Expect: “How do you stop quality checks from becoming so noisy that nobody reads the alerts?”
Answer by separating signal design from volume. Tier alerts so that only publish-blocking failures page a human immediately; warnings aggregate into a daily digest owned by the dataset owner. Tune thresholds from history, and require every alert to carry the expected value, the observed value, the affected partition, and a link to the run and lineage. Retire or fix checks that fire without action — a check that has produced twenty alerts and zero interventions is either miscalibrated or measuring something nobody owns. Review alert outcomes in the same cadence as incident reviews. The principle to state aloud is that an alert is a request for a decision; if no decision follows, the alert, not the engineer ignoring it, is the defect.
What candidates are reporting and debating
The themes below are reported discussion themes from candidate communities, presented as themes only. They are useful because they show what loops currently feel like from the candidate’s side of the table — but each theme needs to be weighed against what the structured content of this set actually demands, because discussion can amplify whatever is most frustrating rather than whatever is most decisive.
The first debate is concept depth versus SQL weight. Many candidates report loops in which SQL rounds carry the visible scoring — window functions, aggregation logic, query debugging under time pressure — while concept questions arrive late, sometimes in a single hiring-manager conversation. The frustration is understandable: a candidate can prepare distributed-systems reasoning for weeks and be eliminated by a query they misread in minute twelve. Cross-checked against this set, the theme is partly right and partly overstated. It is right that SQL is a gate: no panel advances a data engineer who cannot reason in the language the work is written in. It is overstated if it is taken to mean concepts are decorative. The questions in this set — idempotency, partitioning, schema evolution, slowly changing dimensions — are precisely the ones that tend to decide senior and staff-level differentiation once the SQL gate is passed, because they predict whether a candidate’s pipelines will survive retries, restatements, and source changes. The practical reading is sequencing, not rivalry: SQL gets you considered; concepts get you chosen at level.
The second debate is take-home assignments versus live design rounds. Candidates report take-homes expanding in scope — a full ingestion, transformation, and testing exercise presented as a four-hour task — and debate whether refusing or time-boxing them protects them or removes them from contention. Live rounds attract the opposite complaint: performance under observation rewards fluency of narration as much as quality of design. Weighed against this set, both formats test the same underlying material from different angles. A take-home exposes whether your idempotency, partitioning, and quality checks survive contact with real files and awkward edge cases. A live round exposes whether you can state assumptions, order your decisions, and revise when the interviewer changes a constraint. Neither is a pure measure, and candidates who prepare only for one format routinely underperform in the other. The overstated version of this debate treats format as destiny; the version that holds up is narrower — know which format a company uses, prepare the same concepts in both written and spoken form, and time-box a take-home explicitly in your submission notes so the panel can see judgement about scope, not just output.
The third debate is whether lakehouse and medallion vocabulary is becoming a screening filter. Candidates report being asked to define bronze, silver, and gold layers, or to position warehouse, lake, and lakehouse, in first-round screens — and some conclude that fluency in current terminology matters more than engineering substance. There is a real signal inside this one. Vocabulary is a cheap screening proxy, and proxies get used. But cross-checked against Question 2 and Question 3 in this set, the filter only opens the door; it does not carry the round. A candidate who recites the medallion layers and then cannot explain how schema evolution is handled in the silver layer, or how a slowly changing dimension is modelled in the gold layer, is exposed within two follow-up questions. Conversely, a candidate who reasons clearly about storage, compute, table formats, and modelling but uses different layer names can usually be recognised by a strong panel once they make their reasoning explicit. What holds up: learn the common vocabulary, because screens are real. What is overstated: the idea that vocabulary substitutes for the mechanics underneath it.
A closing observation ties the three debates together. Each one is, at root, an argument about what a loop can measure in a few hours. Candidates do best when they stop treating that argument as something to win in discussion and instead cover both sides of each divide: fluent SQL and deep concepts, written and spoken preparation, current vocabulary and the mechanics beneath it.
Three real-world interview rooms
The three scenarios below are composite illustrative examples, written to show how a single concept question can decide a loop. They are not reports of specific candidates or companies.
Room one: the backfill that decided a senior loop. Consider a candidate who handled the early rounds of a senior data engineering loop comfortably — clean SQL, a sensible warehouse design — and then reached the hiring manager’s concept round. The question was idempotency: a payments pipeline had double-counted a day after a retry, and the candidate was asked how they would redesign the load and repair the damage. The weaker version of the answer, given by many candidates in this position, promises to “be careful with retries” and to delete the duplicates by hand. The strong answer in this room was structural. The candidate described deterministic loads keyed by business identifiers, a merge on the natural key rather than an append, partition-level replacement for restated days, and a backfill procedure that could be run twice with the same result. They then named the repair order: quarantine the affected partition, recompute from source, reconcile totals against the payments ledger before republishing. The panel’s feedback centred on that one answer, because it demonstrated the property the role existed for — pipelines that recover predictably instead of heroically.
Room two: the schema change nobody announced. Consider a candidate in a mid-level loop at a company whose analysts kept finding broken dashboards. The deciding question was schema evolution: an upstream team renames a column and adds a new event type next quarter; what breaks, and what would you put in place now? The average answer lists breakages. The strong answer in this room organised them. The candidate separated additive changes, which should flow through safely, from renaming and retyping, which are contract breaks, and proposed versioned contracts at ingestion, compatibility checks in the deployment pipeline, and deprecation windows in which both the old and new fields are published. They also described how downstream models would pin to explicit column lists rather than selecting everything, so an additive change could not silently alter a production table. What persuaded the panel was not the catalogue of failures but the prevention posture: this candidate had clearly lived through an unannounced change and had converted the experience into a system, which is exactly the conversion the question is designed to detect.
Room three: the freshness alert that was not a pipeline failure. Consider a candidate for a platform-leaning data role who was given the observability question from Question 10 in scenario form: dashboards are stale, jobs are green, stakeholders are escalating. Several candidates in such rooms begin by proposing more retries or a faster schedule. The strong answer began by questioning the signal. The candidate asked what the freshness indicator actually measured, discovered in the scenario that it tracked job completion rather than the maximum event timestamp in the table, and rebuilt the answer around that distinction: measure data freshness at the table, alert on the gap between expected and observed event time, use lineage to localise whether the delay sat with the source, the ingestion step, or a blocked downstream partition, and give consumers a visible “current as of” marker so trust did not depend on rumour. The loop outcome turned on that reframing, because the role’s real problem was not processing speed but the team’s inability to see its own system — and only one candidate treated observability as a design problem rather than a tooling purchase.
Across all three rooms the pattern is the same. The deciding moment was rarely the hardest question in the loop. It was a concept question answered with structure — layers, containment, recovery, measurement — instead of a list. Candidates preparing from this set should notice that each strong answer also stated its trade-offs unprompted, which is the habit panels consistently describe as senior.
Preparation for questions like these compounds when it is kept in one place. Working through a set such as this one produces fragments — a cleaner way to explain idempotency, a checklist for schema changes, a phrasing for quarantine trade-offs — and those fragments are only useful in the next loop if they can be found, revised, and built on rather than scattered across browser tabs, chat threads, and half-remembered documents. Building a personal question-and-answer notebook, set by set, turns each interview cycle into an asset for the next one: the answer you drafted for partitioning can be sharpened after a real interviewer’s follow-up, and the follow-up itself becomes part of the entry. This is the kind of private, structured preparation vault that VaultBook (https://vaultbook.net) is built for, and candidates who maintain one arrive at later loops revising their own best answers instead of starting again from a blank page.
A 7-day study plan for this set
Work the set in the order that builds understanding fastest: storage and modelling first, movement and reliability second, quality and performance last, with spoken practice throughout. Keep each day to one main block and one short review block.
Day 1 — Storage foundations: Re-read Questions 1 and 2 (OLTP versus OLAP; warehouse, lake, and lakehouse). Write a half-page comparison from memory, then check it against the answers. End by listing, for one system you know, which parts are optimised for transactions and which for analysis.
Day 2 — Modelling: Work Questions 3 and 4 (star and snowflake schemas; slowly changing dimensions). Sketch a small star schema for orders and customers on paper, then decide, dimension by dimension, how history is kept and why.
Day 3 — Movement patterns: Work Questions 5 and 6 (ETL versus ELT; batch versus streaming). For one pipeline you have built or studied, write where transformation happens, what the latency requirement actually is, and what a streaming design would cost in complexity.
Day 4 — Scale and change: Work Questions 7 and 8 (partitioning; schema evolution). Practise the follow-ups aloud: a partition key that creates skew, and an additive change versus a contract break, each explained in under two minutes.
Day 5 — Reliability: Work Question 9 (idempotency and backfills). Design a backfill for a restated month: partition replacement, reconciliation checks, and the order in which consumers are told. Write the runbook steps as a numbered list.
Day 6 — Quality and observability: Work Question 10 in full, including the variant and follow-up. Draft the check families for one table you care about, assign each check a severity and an action — hold, quarantine, or warn — and write the alert text each would send.
Day 7 — Mock loop: Run the whole set aloud under time: two minutes to structure each answer, then the follow-up. Record yourself if you can. Note the two weakest answers, rewrite them using the four-part format from this set, and file both versions in your notebook so the improvement is visible next cycle.
FAQs
How deep should a concept answer go in a first-round screen?
Deep enough to show structure, short enough to leave room for follow-ups. State the layers or trade-offs in the first minute, then let the interviewer pull you into the area they care about. A screen is testing whether depth exists, not exhausting it.
Are concept questions more common at senior levels?
Yes, in weight rather than in number. Junior loops use concepts to check foundations; senior loops use the same questions to check judgement — containment choices, recovery design, and what you would measure. The follow-ups, not the opening question, are where level is decided.
What if I have never used the tool named in a question?
Answer from the mechanism, and say so plainly. Interviewers asking about a specific platform usually accept a clear explanation of how the equivalent works in a system you know, provided you name the difference honestly rather than bluffing familiarity.
How should I prepare for follow-up questions?
For every answer in this set, prepare one failure story-shaped extension: what breaks, how you would notice, and what you would change afterwards. Most follow-ups are a variation on one of those three, including the variant and follow-up built into Question 10.
Is it acceptable to disagree with a premise in the question?
Yes, when the disagreement is reasoned and brief. If a scenario assumes streaming is required, it is legitimate to ask what latency the business decision actually needs before designing for it. Panels reward examined assumptions; they penalise reflexive agreement and reflexive argument in roughly equal measure.
How many sets like this should I work through before an interview cycle?
Fewer, worked properly, beat more skimmed. One set worked to the standard of the 7-day plan above — written comparisons, spoken answers, rewritten weak spots — prepares you better than three sets read passively, because the loop tests retrieval under pressure, not recognition on a page.
— Pinnacle Report · Layoffs verified. Ladders decoded. Interview questions answered.
Coverage note: Sources checked for this set include the structured question material developed for this Interview Practice set, candidate discussion themes reviewed in aggregate, and the data quality, testing, and observability practices described in current data engineering literature and platform documentation, cross-checked against the verification standards in the Pinnacle Report roadmap. No individual posts or usernames are reproduced. Last checked 20 November 2026.


