82 ETL testing interview questions with answers

What they are assessing

Whether you understand the pipeline rather than the acronym.

Model answer

ETL is extract, transform, load: pulling data from source systems, applying business rules and conversions to it, and writing it into a target such as a data warehouse. ETL testing verifies that the data arriving in the target is complete, accurate and correctly transformed, and that nothing was lost, duplicated or silently altered along the way. It is data centric rather than interface centric: most of the work is comparing what is in the source against what is in the target, usually with SQL, rather than clicking anything.

Likely follow-up

What is the difference between ETL testing and database testing?

What they are assessing

Awareness of the modern architecture shift.

Model answer

In ETL the transformation happens in a dedicated engine between source and target, so only transformed data lands. In ELT the raw data is loaded into the target first and transformed there, using the warehouse's own compute. ELT has become common because cloud warehouses such as Snowflake, BigQuery and Redshift have cheap elastic compute, and keeping the raw layer means transformations can be re-run and corrected without re-extracting. For testing, ELT is often easier because the raw data is still available to compare against, which ETL pipelines frequently discard.

What they are assessing

Whether you know why a warehouse exists at all.

Model answer

OLTP systems handle day to day transactions: many small reads and writes, heavily normalised to avoid update anomalies, optimised for insert and update speed. OLAP systems handle analysis: fewer, much larger reads scanning millions of rows, denormalised into star schemas so queries need fewer joins, optimised for aggregation. You do not run analytics on OLTP because the queries would lock tables and cripple the application, and the normalised model makes analytical queries slow and complex. That difference is the reason ETL exists.

What they are assessing

Understanding of pipeline structure.

Model answer

A staging area is an intermediate store where extracted data lands before transformation. It decouples extraction from transformation, so the source system is released quickly and its availability window is respected. It allows reprocessing without re-extracting if a transformation fails, which matters when the source is a system you can only query at night. It is where data profiling, cleansing and deduplication usually happen. And it gives testers something to compare against, because staging is the closest thing to the source that you can query freely.

Likely follow-up

Should staging tables be truncated between runs, and what does that cost you?

What they are assessing

Vocabulary and scope awareness.

Model answer

A data warehouse is the enterprise wide integrated store covering many subject areas. A data mart is a subset focused on one business function such as sales, finance or marketing, designed for that audience's queries. Marts can be dependent, built from the warehouse, or independent, built directly from sources, and independent marts tend to produce the inconsistent numbers that drive people back to a warehouse in the first place. Testing a mart adds the question of whether its figures reconcile with the warehouse they came from.

What they are assessing

Whether your knowledge extends past classic warehousing.

Model answer

A data lake stores raw data in its native format at scale, schema on read rather than schema on write, so structure is applied when the data is queried rather than when it is stored. Testing differs because there is often no enforced schema to validate against on load, so the emphasis shifts to profiling, schema drift detection and validating the consumption layer. The common failure is a lake becoming a swamp: data arrives, nothing is catalogued, lineage is unknown, and nobody can say whether a figure is correct because nobody knows where it came from.

What they are assessing

Whether you can trace a number back to its origin.

Model answer

Lineage is the documented path a data element takes from source through every transformation to its final use in a report. A tester cares because when a figure looks wrong, lineage is what lets you work backwards to find where it diverged, rather than guessing. It is also what answers the audit question of where a regulatory number came from. On a mature platform lineage is captured by tooling; on most projects it lives in the mapping document, which is why keeping that document accurate is a testing concern rather than a documentation nicety.

What they are assessing

Whether you have a structured view of the work.

Model answer

Metadata testing, checking data types, lengths, constraints and index definitions match the specification. Data completeness, confirming record counts and no truncation. Data accuracy or transformation testing, confirming business rules were applied correctly. Data quality testing, covering nulls, duplicates, formats and referential integrity. Incremental load testing. Regression testing after pipeline changes. Performance testing of the load window. And report or BI testing at the consumption end, because a correct warehouse feeding a wrong report is still a wrong number to the business.

Likely follow-up

Which of those do you do first on a new pipeline, and why?

What they are assessing

Core dimensional modelling knowledge.

Model answer

A central fact table holding measures and foreign keys, surrounded by denormalised dimension tables holding descriptive attributes. It is called a star because of the shape. The denormalisation is deliberate: it trades storage and some update complexity for query simplicity and speed, since most analytical queries then need a single join per dimension rather than traversing a normalised hierarchy. It is the default model for a warehouse because it is both fast and comprehensible to business users building their own queries.

What they are assessing

Whether you understand the trade-off rather than the shape.

Model answer

In a snowflake schema the dimensions are normalised into further tables, so a product dimension might split into product, category and department. It saves storage and avoids update anomalies in the dimension, but it costs more joins per query and is harder for business users to navigate. Star is usually preferred in a warehouse because query performance and comprehensibility matter more than the storage saved, and dimension tables are small relative to facts anyway. Snowflaking is defensible when a dimension is genuinely large or shared across marts.

Likely follow-up

Which would you expect to perform better on a large aggregation, and why?

What they are assessing

The most basic modelling distinction.

Model answer

A fact table holds the measurements of a business process: quantities, amounts, counts, plus foreign keys to dimensions and usually a date key. It is long and narrow, with many rows and few columns, and grows continuously. A dimension table holds the descriptive context that gives those measures meaning: who, what, where, when. It is short and wide, with fewer rows and many attributes. In a query, facts are what you aggregate and dimensions are what you group and filter by.

What they are assessing

A concept that separates people who have modelled from people who have read.

Model answer

The grain is what a single row represents: one line item on an order, one daily balance per account, one click. Defining it first is the most important step in designing a fact table, because everything else follows from it: which dimensions apply, which measures are valid, and whether a given aggregation is meaningful. Getting it wrong produces double counting. From a testing perspective the first question about any fact table is what one row means, and the first check is whether the natural key combination is actually unique at that grain.

Trap to avoid

Skipping past the grain question. An interviewer asking about a fact table is often checking whether you ask what one row represents before writing any validation.

What they are assessing

Understanding of a design choice that drives much of ETL logic.

Model answer

A surrogate key is a meaningless integer generated by the warehouse to identify a dimension row. You avoid the natural key because source keys can be reused, can change, can collide when integrating several source systems, and can be non-numeric which slows joins. Critically, surrogate keys are what make slowly changing dimensions possible: you need several rows for the same business entity across time, which a natural primary key would forbid. Testing them means checking they are unique, never reused, and correctly resolved when the fact is loaded.

Likely follow-up

How would you test that a fact row picked up the correct surrogate key?

What they are assessing

Whether you know which aggregations are legitimate.

Model answer

Additive facts can be summed across every dimension, such as sales amount. Semi-additive facts can be summed across some dimensions but not time, the classic example being an account balance: summing balances across accounts is meaningful, summing the same account's balance across days is not. Non-additive facts cannot be summed at all, such as ratios, percentages and unit prices, which must be recalculated from their components after aggregation. This matters for testing because a report summing a non-additive fact produces a number that looks plausible and is meaningless.

What they are assessing

Depth of dimensional modelling knowledge.

Model answer

A fact table with no numeric measures, only the foreign keys recording that an event or a relationship occurred. Two common uses: event tracking, such as a student attending a class or a customer viewing a promotion, where the fact is the occurrence itself and you count rows; and coverage, such as which products were on promotion in which stores, which lets you answer what did not happen by comparing against the sales fact. Testing them focuses on the key combination being unique and on the absence of unexpected duplicates, since there is no measure to reconcile.

What they are assessing

Understanding of enterprise consistency.

Model answer

A dimension used identically across several fact tables or data marts, with the same keys and the same meaning, so measures from different processes can be compared side by side. A conformed date or customer dimension is what allows a sales figure and a support cost to be analysed by the same customer. Without conformance, each mart defines customer differently and the numbers cannot be combined, which is exactly how organisations end up with three versions of revenue. Testing conformance means checking the same key resolves to the same entity across marts.

Likely follow-up

How would you test that two marts agree on what a customer is?

What they are assessing

Detail knowledge that indicates real modelling exposure.

Model answer

A degenerate dimension is a dimension attribute stored directly in the fact table because it has no other attributes worth a separate table, typically a transaction or invoice number used for grouping line items. A junk dimension collects several low cardinality flags and indicators into one small dimension rather than adding many narrow columns to the fact or creating a table per flag. Both are pragmatic space and join optimisations, and both show up in mapping documents in ways that confuse testers who have not met them.

What they are assessing

Basic load strategy knowledge.

Model answer

A full load truncates the target and reloads everything from source, which is simple and self correcting but expensive and impractical at volume. An incremental or delta load moves only what has changed since the last run, identified by a timestamp, a sequence, a change data capture feed or a comparison. Incremental is far faster but carries risk: missed records if the change detection is wrong, duplicates if the watermark is not managed, and drift that accumulates silently. Most warehouses do an initial full load and then incrementals, with periodic full reconciliation.

Likely follow-up

How would you detect that an incremental load has been silently missing records for a month?

What they are assessing

Awareness of how deltas are actually identified.

Model answer

CDC is identifying which source rows have changed since the last extraction. Approaches vary in cost and reliability: a modified timestamp column is simplest but misses hard deletes and depends on the application maintaining it; database triggers are reliable but add write overhead; log based CDC reads the database transaction log, which is the most complete and least intrusive and captures deletes, and is what tools such as Debezium do; and full comparison is the fallback when nothing else exists. The approach determines what your tests must cover, particularly around deletes.

What they are assessing

A case that is very often missed in testing.

Model answer

Usually as soft deletes rather than physical ones, because a warehouse is a historical record and physically removing a row destroys history and breaks facts that reference it. So the dimension row gets a deleted or inactive flag and an end date, and reports filter accordingly. The testing risk is that many CDC mechanisms based on modified timestamps never see a delete at all, so records remain active in the warehouse indefinitely. Explicitly testing that a source deletion produces the expected flag change is one of the most valuable ETL test cases.

Trap to avoid

Assuming deletes flow through automatically. With timestamp based CDC they usually do not, and nobody notices until a report includes a customer who left two years ago.

What they are assessing

Whether you treat the mapping as the specification.

Model answer

It is the specification for the pipeline: for every target column, the source column or expression, the data type and length, the transformation rule, default and null handling, and the key relationships. It is the primary artefact an ETL tester works from, so reviewing it is a testing activity in itself. I check for missing rules, contradictory ones, source columns that are shorter or longer than the target, transformations with undefined behaviour on null, and target columns with no stated source, which usually means someone will populate them with something undocumented.

What they are assessing

Practical pipeline mechanics.

Model answer

A join combines two data sets inside the source query or the transformation engine, typically between comparable volumes. A lookup enriches a flowing row by fetching matching values from a reference set, usually cached in memory, which is how surrogate keys are resolved when loading a fact. The testing concern with lookups is the unmatched case: what happens when no match is found, whether it is rejected, defaulted to an unknown member, or silently nulled. That behaviour must be specified and tested, because defaulting quietly is how orphan facts appear.

Likely follow-up

What is an unknown member row in a dimension, and why does it exist?

What they are assessing

Operational maturity.

Model answer

An idempotent job produces the same result whether it runs once or several times with the same input. It matters because jobs fail halfway and get restarted, and a non-idempotent load produces duplicates when rerun, which is worse than the original failure because it is harder to detect. Achieving it usually means deleting or overwriting the target partition for the processing window before inserting, or using merge or upsert logic keyed properly, rather than blind inserts. I would always test a job by running it twice and confirming the target is unchanged.

What they are assessing

Restartability thinking.

Model answer

It should leave the target in a consistent state and be safely restartable. In practice that means transactional boundaries so a partial load is rolled back rather than committed, a watermark that only advances on successful completion, and staging so the extracted data does not need re-fetching. The failure mode to avoid is a watermark advanced before the load completed, which silently skips a window forever. I would test this deliberately by killing a job mid-run and verifying both that the target is clean and that the next run picks up the missed data.

Likely follow-up

How would you test a mid-run failure without breaking a shared environment?

What they are assessing

The most fundamental ETL check.

Model answer

Start with record counts on both sides for the same window, accounting for any documented filtering, because a count mismatch is the fastest signal something is wrong. Then check sums and averages of numeric columns, which catches cases where the count matches but values were altered. Then distinct counts of key columns to catch duplication or collapse. Then a minus or except query in both directions to find rows present on one side only. Counts alone are not sufficient: a pipeline that loses one row and duplicates another gives a perfect count.

Trap to avoid

Relying on row counts alone. Interviewers ask this specifically because it is the check that feels complete and is not.

What they are assessing

The core comparison technique.

Model answer

It returns rows present in the first result set and absent from the second. You run it in both directions: source minus target finds records that failed to load, target minus source finds records that should not be there, typically duplicates or leftovers from a previous run. Both being empty is the strongest simple assurance that the two sets are identical. The practical caveats are that it is expensive on large volumes, so it is often run on a sample or a partition, and that column ordering and data types must align or it reports false differences.

Likely follow-up

Why is running it in only one direction insufficient?

What they are assessing

Practical cross-platform comparison.

Model answer

The comparison cannot be a single SQL statement, so you need a common ground. Options are extracting both sides to files and comparing with a script or a data comparison tool, loading the source extract into the target platform as a temporary table and comparing there, or comparing aggregates and checksums rather than full row sets. The things that bite are data type differences, particularly date and timestamp precision, numeric scale and rounding, character set and collation differences affecting sort order and case sensitivity, and differing null handling in aggregate functions.

What they are assessing

A specific, common defect class.

Model answer

Compare the defined length of each target column against the maximum actual length in the source for that field, which catches the problem before a load rather than after. After loading, query for rows where the length of the target value equals the column maximum, since those are the candidates for silent truncation. The reason this matters is that some platforms and some tool configurations truncate without raising an error, so the load succeeds and the data is quietly wrong. Unicode makes it worse, because character and byte length differ.

What they are assessing

Whether you test structure as well as content.

Model answer

Column names, data types, lengths and precision against the mapping document. Nullability constraints. Primary and foreign key definitions, and whether they are enforced or merely documented, because many warehouses disable constraints for load performance and then rely on the ETL to maintain integrity. Default values. Index existence, since a missing index is a performance defect. And comparing the deployed structure against the specification after any release, because schema drift between environments is a frequent cause of a job working in test and failing in production.

What they are assessing

Pragmatism at real volume.

Model answer

Layer the checks by cost. Cheap aggregate reconciliation across the whole set first: counts, sums, minimums, maximums and distinct counts by partition, which catches most gross errors for very little cost. Then checksums or hash totals per partition to detect any difference without transferring data. Then a targeted full comparison on the partitions that disagree, plus a statistically meaningful random sample and a deliberate sample of edge cases: nulls, boundaries, the largest and smallest values, and the newest records. Full comparison is reserved for investigation, not routine validation.

Likely follow-up

How would you choose the sample so it is actually representative?

What they are assessing

Whether you test the rule or just eyeball the output.

Model answer

By reimplementing the rule independently and comparing, rather than reading the ETL code and confirming it matches itself. So for a rule stating that discount equals list price minus net price, I would write a query computing that from the source and compare it to the target column row by row. Then test the edge cases the rule does not mention: nulls on either side, zero, negative values, and the boundaries of any banding. The most valuable cases are almost always the ones the specification was silent about.

What they are assessing

Understanding of grain change.

Model answer

The key point is that the grain changes, so a one to one comparison does not apply. I would recompute the aggregate from source with an independent query grouped by the same key, then compare against the target, checking not only totals but the group count, since a missing group is invisible in a grand total. Null handling matters especially here, because most SQL aggregates ignore nulls while a count of all rows does not, which produces averages that differ from expectation in ways that look like a defect and are actually a specification gap.

Trap to avoid

Comparing only the grand total. It can match while individual groups are wrong in offsetting directions.

What they are assessing

Attention to a quiet source of corruption.

Model answer

Focus on where precision can be lost or meaning changed. Numeric to numeric: scale reduction causing rounding, and range overflow. String to numeric: how non numeric values are handled, leading zeros, and thousands separators. String to date: format assumptions, ambiguous formats such as day and month order, and two digit years. Timestamps: timezone handling and whether the conversion is to UTC or local, which is the single most common source of off by one day errors in reporting. And null versus empty string, which many conversions conflate.

Likely follow-up

A daily report is consistently one day out for some rows. Where do you look?

What they are assessing

Whether you test which row survives, not just that one does.

Model answer

First confirm that duplicates are actually removed, by checking the target has no repeated key at the stated grain. But the more important test is which record survived, because deduplication rules normally say keep the most recent or keep the one with the highest completeness, and that choice changes the data. So I would construct source cases with several duplicates differing in the deciding attribute and verify the correct one persists. I would also test ties, where the rule is usually undefined and the behaviour is therefore whatever the engine happens to do.

What they are assessing

Systematic coverage of combinational logic.

Model answer

With a decision table. I list the conditions, enumerate the combinations, and confirm the expected outcome for each, which immediately exposes combinations the specification never addressed. That gap is usually the finding. Then I construct test data covering each rule, including the combinations the business insists cannot occur, because source systems produce them anyway. Where the combination count is large, pairwise reduction keeps it tractable. The output is both a set of test cases and a list of questions for the business analyst.

What they are assessing

Modern ELT tooling awareness.

Model answer

dbt gives you testing hooks in the project itself, so part of the answer is using them: schema tests for uniqueness, not null, accepted values and referential integrity, plus custom singular tests written as SQL that must return zero rows. Beyond that I would treat the models as code: review them, check the lineage graph for models nobody consumes, and validate the marts against independently computed expectations rather than against the models that produced them. Snapshots need particular attention because they implement slowly changing dimension logic.

What they are assessing

A guaranteed ETL interview topic.

Model answer

A dimension whose attributes change over time, such as a customer moving address or a product changing category, where the business has to decide whether history matters. The handling types are the answer: Type 0 keeps the original and never updates, Type 1 overwrites so only the current value exists, Type 2 creates a new row with effective dates so full history is preserved, Type 3 keeps a previous value column so only the prior state is available, and hybrids combine these. Type 2 is the most common and the most tested.

What they are assessing

The highest value ETL test scenario there is.

Model answer

Change an attribute in the source and verify the whole set of consequences, not just the new row. The previous row should be closed: end date set to the change point, current flag cleared. A new row should exist with a new surrogate key, the same business key, the new value, the correct effective start date and an open end date or a high date sentinel. There should be exactly one current row per business key. Effective periods must not overlap and must not leave gaps. And the natural key plus effective date should be unique.

Likely follow-up

How would you find overlapping effective periods with a single query?

Trap to avoid

Checking only that a new row appeared. The defects are almost always in closing the old row: end dates left null, two current flags, or an off by one day gap.

What they are assessing

Whether you understand why Type 2 exists at all.

Model answer

Nothing: they keep pointing at the surrogate key that was current when they were loaded, which is exactly the point. A sale made while the customer lived in one region remains attributed to that region, so historical reports do not change retrospectively. New facts pick up the new surrogate key. This is the difference from Type 1, where overwriting the attribute silently rewrites history and last year's regional sales report produces a different answer than it did last year. Testing this means loading facts before and after a dimension change and verifying the attribution.

What they are assessing

A real world complication.

Model answer

A fact arrives referencing a dimension member that does not exist yet, which happens when systems load out of order or a transaction is recorded before the master data syncs. The usual handling is an inferred member: create a placeholder dimension row with the business key and unknown attributes, point the fact at it, and update the attributes when the real record arrives. The alternative is rejecting the fact to an error table for reprocessing. Testing means confirming the placeholder is created once rather than repeatedly, and that the later update fills it in without creating a duplicate.

What they are assessing

Depth beyond the standard SCD answer.

Model answer

A fact that arrives after the period it belongs to, for example a transaction backdated by a week. It is harder because with a Type 2 dimension you must resolve the surrogate key that was effective at the transaction date, not the one current now, which means joining on the business key with the transaction date between the effective start and end dates rather than simply taking the current row. Getting this wrong attributes the fact to the wrong dimension version and the error is nearly invisible. It also means any aggregate already built for that period needs rebuilding.

Likely follow-up

Write the join condition you would use to resolve that key.

What they are assessing

Basic validation SQL.

Model answer

Group by the columns that should be unique at the defined grain and return groups with a count greater than one: select key_cols, count(*) from target group by key_cols having count(*) > 1. The important part is choosing the right key. Duplicates in the business key are a load defect, while duplicates in the surrogate key indicate something more serious in key generation. And on a Type 2 dimension the correct uniqueness test is the business key plus effective date, not the business key alone, because multiple rows per key are expected there.

What they are assessing

Whether you can find orphan facts.

Model answer

A left join from the fact to the dimension on the surrogate key, filtered to rows where the dimension key is null, which returns facts referencing a dimension member that does not exist. The reason this needs testing rather than relying on the database is that warehouses frequently disable foreign key constraints for load performance, so nothing enforces it. I would also check the opposite direction for dimension rows never referenced by any fact, which is not necessarily an error but is worth understanding.

Likely follow-up

Constraints are disabled for performance. What else would you check as a result?

What they are assessing

Precision about what empty means.

Model answer

select * from target where col is null or trim(col) = ''. The point of the question is that null and empty string are different things and a check for one misses the other. On some platforms an empty string is stored as null, on others it is not, and a source system feeding whitespace produces a value that passes a not null constraint while being useless. I would also check for placeholder values that mean missing, such as N/A, unknown, or a sentinel date like 1900-01-01, since those pass every technical check.

What they are assessing

Problem solving under awkward constraints.

Model answer

Sorting does not help without a key, so I would build one. Compute a hash of the concatenated significant columns for each row, with consistent null handling and consistent formatting of numbers and dates, then compare the multisets of hashes on both sides. Grouping by the hash and counting also handles duplicates correctly, which a simple set comparison does not. The failure mode to guard against is inconsistent formatting producing different hashes for identical data, so the normalisation step matters more than the hash function.

What they are assessing

A small distinction with real consequences.

Model answer

Union removes duplicate rows and therefore sorts, while union all returns everything including duplicates and is significantly faster. In validation it matters twice over: using union when combining result sets can silently hide duplicate rows that are exactly what you were looking for, and on large volumes the deduplication cost can make a check impractically slow. For reconciliation I default to union all and handle duplicates explicitly, so nothing is removed without my deciding it should be.

What they are assessing

SQL beyond the basics.

Model answer

They are the cleanest way to validate sequential and historical logic. row_number partitioned by business key ordered by effective date finds the current row and identifies duplicates at a grain. lag and lead compare a row against the previous or next one, which is how you detect gaps and overlaps in Type 2 effective dates in a single pass. Running totals verify cumulative measures. rank identifies ties in deduplication logic. Before window functions these checks needed self joins that were slow and easy to get subtly wrong.

Likely follow-up

Show me how you would detect an overlapping effective period using lead.

What they are assessing

The mechanism most incremental loads depend on.

Model answer

A watermark is the stored marker of how far the last successful load reached, usually a maximum timestamp or sequence value, used to select the next batch. Testing it means checking it advances only on success, that the selection is inclusive or exclusive consistently so records on the boundary are neither skipped nor duplicated, and that a failed run leaves it unchanged. The boundary is where the defects are: greater than versus greater than or equal, combined with records sharing a timestamp, produces either loss or duplication.

Trap to avoid

Not testing records with identical timestamps at the boundary. That is precisely where the off by one occurs.

What they are assessing

Whether you can detect silent data loss.

Model answer

Periodic full reconciliation is the only reliable answer, comparing counts and key sets across the entire history rather than the current window, because incremental drift accumulates invisibly. Between reconciliations, I would check counts per source period against target counts per period, which localises when the loss occurred. I would also test the specific scenarios that cause it: records modified during the extraction window, records with clock skew from another server, transactions committed after their timestamp was assigned, and source rows updated without their modified date changing.

What they are assessing

Understanding of the dominant load pattern.

Model answer

Merge inserts rows that do not exist and updates those that do, matched on a key. What I test is each branch separately and then their interaction: a genuinely new record inserts, an existing record updates without duplicating, a record that has not changed is either untouched or updated harmlessly, and a source batch containing the same key twice behaves predictably rather than raising an error or applying an arbitrary one. That last case is the most common production failure, because merge on most platforms errors if the source has duplicate matches.

Likely follow-up

Your merge fails intermittently with a duplicate match error. What is happening?

What they are assessing

Partition-aware reasoning.

Model answer

Confirm each partition is loaded exactly once and that reprocessing a partition replaces rather than appends, which is the idempotency requirement. Test the boundaries: the first and last record of a partition, records exactly at midnight, and timezone handling, since a job partitioning by local date against UTC timestamps will misplace records at the edges. Test a gap, where one day produced no data, and confirm downstream does not interpret the absence as an error or skip forward. And test backfilling an old partition without disturbing later ones.

What they are assessing

A framework for thinking about quality rather than ad hoc checks.

Model answer

Completeness, meaning no missing values or records. Accuracy, meaning values reflect reality. Consistency, meaning the same fact agrees across systems. Validity, meaning values conform to defined formats and ranges. Uniqueness, meaning no unintended duplicates. Timeliness, meaning data is current enough for its purpose. Integrity, meaning relationships hold. Framing checks against these prevents the common pattern of testing only completeness and accuracy and being surprised when a report fails on timeliness.

What they are assessing

Whether you look at the data before writing tests.

Model answer

Profiling is examining the actual data to understand its shape before designing anything: value distributions, null rates per column, distinct counts, minimum and maximum values, pattern analysis on strings, and outliers. You do it at the start, because it reveals what the specification does not: that a supposedly mandatory field is null in twelve per cent of rows, that a date column contains 1900 placeholders, that a two character country code column contains full country names for older records. Every one of those becomes a test case or a question for the business.

Likely follow-up

Profiling shows a mandatory column is 12 per cent null. What do you do?

What they are assessing

Whether you verify the cleansing did not destroy information.

Model answer

Test both directions: that the dirty input produces the expected clean output, and that valid input is left unchanged, which is the case that gets skipped. Standardisation rules such as uppercasing, trimming or reformatting phone numbers can corrupt legitimate values, so I would include values that look dirty and are not. I would also check that rejected or corrected records are logged rather than silently dropped, because a cleansing step that quietly discards five per cent of rows is a completeness failure disguised as a quality improvement.

What they are assessing

Whether quality is continuous or a one-off validation.

Model answer

By making the checks part of the job rather than an external activity: assertions that run after each stage and fail or quarantine the batch when thresholds are breached. Row count variance against the historical average catches a partial extract. Null rate variance catches a source change. Referential integrity checks catch broken lookups. Distribution checks catch a transformation applied to the wrong column. Tools such as dbt tests, Great Expectations or Deequ formalise this. The important design decision is which failures halt the pipeline and which only alert.

What they are assessing

Awareness of operational reality.

Model answer

The load window is the time available to complete the load, usually overnight between the source system becoming quiet and the business needing reports in the morning. It constrains everything: a pipeline that produces perfect data in nine hours is a failure if the window is six. So performance is a functional requirement here, not a separate concern, and testing includes measuring the load duration at production-like volume and confirming it fits with margin for growth and for the occasional re-run after a failure.

Likely follow-up

The load takes five hours in a six hour window. Is that acceptable?

What they are assessing

Diagnostic knowledge.

Model answer

Row by row processing where a set based operation would do, which is the single biggest cause and often comes from a cursor or a lookup configured per row. Missing indexes on join and filter columns in the target. Indexes left enabled during a bulk insert, where dropping and rebuilding is faster. Unnecessary sorting. Lookups caching an entire large table into memory. Transformations that force the whole data set to be materialised. Network transfer of columns nobody uses. And statistics being stale so the optimiser chooses a poor plan.

What they are assessing

Method rather than opinion.

Model answer

With production-like volume, because behaviour is non linear: a job that handles a million rows comfortably can fall over at fifty million when a hash join spills to disk. I would measure total duration and per stage duration to find the bottleneck, watch the target database for lock waits and log growth, and test the realistic worst case such as month end or a backfill running alongside a normal load. I would also test the re-run scenario, since a failed load plus a re-run must still fit the window.

What they are assessing

Forward looking capacity thinking.

Model answer

Run at several volumes, for example one times, two times and five times current, and plot duration against volume. Linear growth is expected and acceptable; superlinear growth indicates a problem such as a nested loop join, an unindexed lookup or memory spilling to disk, and it means the pipeline will fail at a predictable future date. Identifying that date is the useful output. I would also check that the growth pattern of the target is sustainable in storage terms, and that any full comparison validation still completes within the window.

What they are assessing

Whether rejected data is visible or lost.

Model answer

They should be written to a reject or error table with the reason and enough context to reprocess them, not silently dropped and not allowed to fail the entire batch unless the failure rate indicates something systemic. Someone must own reviewing that table, because a reject table nobody reads is the same as discarding the records, and it is a very common finding. I would test that a deliberately malformed record lands there with the correct reason, that it does not appear in the target, and that reprocessing it after correction works.

Trap to avoid

Answering only that the job should fail. Failing the whole load because one row in a million is malformed is usually the wrong design.

What they are assessing

Whether you exploit the metadata the pipeline writes.

Model answer

Columns added by the pipeline rather than sourced from the data: insert timestamp, update timestamp, the job or batch identifier that loaded the row, the source system, and sometimes a record hash. They are extremely useful for testing, because they let you isolate exactly which rows a given run touched, reconcile a batch against its source extract, and investigate when something appeared. I would check they are populated consistently, that the batch identifier is genuinely unique per run, and that updates change the update timestamp rather than leaving it stale.

What they are assessing

Whether operational readiness is in scope.

Model answer

By causing the conditions and confirming someone is actually told. Fail the job and check the alert fires with enough detail to act on. Produce a zero row load and confirm it alerts rather than succeeding quietly, because a successful load of nothing is the failure that goes unnoticed longest. Breach a data quality threshold and confirm the warning. Run past the expected duration and confirm a long running alert. And check the alert reaches a person or rota rather than a mailbox nobody reads, which is the most common gap.

Likely follow-up

Why is a successful zero row load more dangerous than a hard failure?

What they are assessing

Breadth, and honesty about depth.

Model answer

Answer with what you have genuinely used and be specific about what you did with it. The common families are traditional enterprise tools such as Informatica PowerCenter, IBM DataStage, SSIS and Talend; orchestration frameworks such as Apache Airflow and Prefect where the logic is code; cloud native services such as AWS Glue, Azure Data Factory and Google Dataflow; and transformation layer tools such as dbt in an ELT stack. What matters more than the tool is whether you can read a mapping, write validation SQL and reason about grain.

Trap to avoid

Listing every tool you have heard of. A single follow-up question about a tool you named but have not used ends the credibility of everything else.

What they are assessing

Whether you follow the data to its destination.

Model answer

That the numbers reconcile to the warehouse, which is the core check, since a correct warehouse and a wrong report is still a wrong number. Then filters and their interaction, drill downs summing to the parent level, default date ranges, and totals and subtotals, especially where a non-additive measure is involved. Then access control, since row level security in a report is a common defect. Then formatting, rounding and currency, which cause reconciliation arguments. And refresh timing, so a user knows how current the figures are.

What they are assessing

Awareness of precomputation risk.

Model answer

A precomputed roll up stored to make common queries fast, for example daily sales by store. It adds a consistency problem: the aggregate and the detail can disagree, which happens when the aggregate is not rebuilt after a late arriving fact or a dimension change. So the key test is reconciling the aggregate against the detail it summarises, routinely rather than once. I would also check the refresh trigger, and what the aggregate does during rebuild, since a table truncated and repopulated can serve zero rows to a query in that window.

Likely follow-up

How would you catch an aggregate that drifted out of sync a month ago?

What they are assessing

Whether validation is repeatable.

Model answer

Express the checks as SQL that returns zero rows when correct, then run them from a harness or from the orchestration tool itself so they execute on every load. Keep a known input data set and an expected output set under version control so a full comparison is possible in a lower environment. Frameworks such as dbt tests, Great Expectations or a simple Python runner over a folder of SQL files all work. The important properties are that a failure is unambiguous, that the checks run automatically, and that adding a new check is cheap enough that people actually do it.

What they are assessing

A large, high risk scenario.

Model answer

Run both in parallel and reconcile, which is the only approach that gives confidence at reasonable cost. For an agreed period, load both and compare outputs table by table and report by report, investigating every difference rather than assuming the new platform is right. Expect and catalogue legitimate differences: numeric precision, date and timestamp handling, sort collation, null ordering, and division or rounding behaviour, all of which vary by platform. The output of the exercise should be a signed list of accepted differences, not silence.

What they are assessing

Systematic narrowing.

Model answer

Establish which side has more, then find the specific rows rather than theorising: a minus query in the relevant direction, or comparison by key. Once you have the rows, look for what they share. The usual causes are documented filtering nobody told you about, rejected records sitting in an error table, records excluded by a join that should have been a left join, duplicates collapsed by a distinct, records arriving after the extract started, and boundary records at the watermark. Identifying the pattern in the differing rows usually names the cause immediately.

What they are assessing

Layered isolation.

Model answer

Work the layers rather than guessing. Confirm the figures are defined the same way first, because most of these turn out to be definitional: different date basis, different currency, different inclusion rule for cancelled items. Then compare source against staging, staging against the warehouse, the warehouse against any aggregate, and the aggregate against the report. Each comparison either matches or does not, and the first mismatch localises the problem. Reporting that the number is wrong without doing this is what makes ETL testers unpopular with data engineers.

Likely follow-up

The warehouse matches the source but the report does not. What are the likely causes?

What they are assessing

Environment awareness.

Model answer

Data volume first, because production volume exposes timeouts, memory limits and plan changes that a small test set never triggers. Then data content: production contains values test data does not, including nulls, unusual characters, historical formats and records violating assumptions. Then configuration: connection strings, file paths, credentials, parallelism settings and resource allocation. Then schema drift between environments. Then concurrency, since production runs alongside other jobs competing for the same resources and locks.

What they are assessing

Investigative use of the warehouse itself.

Model answer

Use the audit columns and the historical data, which is the advantage of a warehouse over an application. Query the affected condition grouped by the load date or batch identifier to find the first batch that exhibits it, which immediately bounds the investigation. Then correlate that date against release history, source system changes and configuration changes. If the dimension is Type 2, the effective dates let you see the exact point an attribute began arriving wrongly. The output should be both the cause and the range of affected data, because someone has to decide whether to correct it.

What they are assessing

Judgement about correction, not just detection.

Model answer

Quantify the blast radius first: which tables, which rows, which downstream reports and whether anything external such as a regulatory submission used the data. Stop the ongoing error before correcting history, otherwise you fix and re-break. Then decide the correction approach with the business: full reload of the period, targeted correction, or leaving it with documented caveats if reload is impossible. Preserve the incorrect state before overwriting, since audit may need it. And communicate to consumers before changing figures they have already reported on.

Trap to avoid

Rushing to reload. Silently changing six months of numbers that people have already presented externally causes a bigger problem than the original defect.

What they are assessing

Working within real constraints.

Model answer

Get an extract rather than access: a file, a read replica, or a snapshot table you can query. Where even that is refused, agree with the source team a set of control totals, counts and sums per period, published with each extract, which becomes your reconciliation basis. Use the staging layer as the closest available proxy for source, while being explicit that any defect between source and staging is outside your visibility. And record that limitation in the test report, because it is a genuine gap in assurance rather than something to paper over.

What they are assessing

Practical data preparation.

Model answer

A combination. A masked or subsetted copy of production gives realistic distribution and the awkward historical cases, and is usually the most valuable, provided masking preserves referential integrity across tables and keeps formats valid. Then hand crafted records for the specific edge cases: nulls, boundaries, maximum lengths, special characters, duplicates and the combinations the business says cannot happen. Then generated volume for performance. The mistake is testing only with clean synthetic data, which passes everything and tells you nothing about the real source.

Likely follow-up

How do you mask a customer identifier while keeping the joins working?

What they are assessing

Whether you understand the shift in method.

Model answer

The interface is SQL rather than a screen, and the object under test is the data rather than the behaviour. There is typically no UI to interact with, so test cases are queries with expected result sets rather than steps and screenshots. Volume matters far more, since defects appear only at scale or in specific data conditions. And the tester needs to understand the business meaning of the data, because a technically correct load of semantically wrong data passes every structural check.

What they are assessing

Vocabulary that comes up in finance and regulated contexts.

Model answer

Validation checks that individual records and rules are correct. Reconciliation checks that totals agree between two systems, typically control totals such as record counts, sum of a monetary column and hash totals of key fields, compared for the same period. Reconciliation is what finance and audit care about and is often a formal, signed process. The distinction matters because a pipeline can reconcile perfectly while individual records are wrong in offsetting ways, and can fail reconciliation while every rule was applied correctly, because of timing differences.

What they are assessing

Protecting what already worked.

Model answer

Take a checksum or aggregate snapshot of the historical partitions before the run, by period, then recompute after and compare. Any change to a closed period is a defect unless it was an intended correction, and it is the kind of change nobody notices until a monthly report differs from the one distributed last month. This check is cheap and catches a whole class of regression: a transformation change applied retrospectively, an incorrect merge key updating old rows, or a backfill overwriting more than intended.

What they are assessing

Attention to temporal correctness.

Model answer

The critical question is which rate applies: the rate on the transaction date, the rate at load time, or a period average, and these give different answers. So I would test that the rate selected matches the transaction date rather than the current date, which is the most common defect. Then rounding, and specifically whether rounding happens per line or on the total, since the two differ and finance will notice. Then missing rates for a date, weekends and holidays, and the behaviour when a currency has no rate at all.

Likely follow-up

The converted totals are out by a few pence per invoice. What is the likely cause?

What they are assessing

How the role fits modern delivery.

Model answer

Mostly upstream. Reviewing the mapping and the model before build, asking what the grain is, what happens on null, how deletes arrive and what the reconciliation basis will be, which prevents far more defects than validating afterwards. Then writing the validation SQL as part of the story rather than after it, so it runs in the pipeline. Then owning the data quality checks as a durable asset rather than a one-off. Execution is increasingly automated, so the value is in the questions asked before anything is built.

What they are assessing

Leadership scope.

Model answer

Start with ownership, because unowned quality rules decay: each critical data element needs a named business owner who decides what correct means. Then define the rules with them and implement them as automated checks in the pipeline rather than a document. Then thresholds and escalation: what halts a load, what alerts, what is merely recorded. Then a visible quality dashboard by domain, so the trend is public. And a regular review, since rules go stale as the business changes. Tooling matters least of these.

What they are assessing

Structured remediation under pressure.

Model answer

Measure per stage first to find where the seven hours actually goes, because assumptions here are usually wrong. Then separate growth from regression by checking whether the increase correlates with data volume or with a specific release. Quick wins usually exist: indexes, statistics, a lookup that became a full scan, or a stage that could run in parallel. Structural options follow: incremental instead of full, partitioning, moving transformation into the warehouse, or splitting the load so critical tables complete first and the business gets its key reports on time while the rest continues.

Likely follow-up

Which tables would you prioritise completing first, and how would you decide?

What they are assessing

Whether you can solve a credibility problem, not just a technical one.

Model answer

Find out specifically which numbers they distrust and why, because trust is usually lost over a small number of incidents that were never properly closed. Reconcile those specific figures against source publicly and explain any legitimate difference, since many turn out to be definitional rather than defects. Then make quality visible: a dashboard showing reconciliation status and freshness per domain, so the answer to is it right is a link rather than an opinion. Then publish definitions so everyone means the same thing by revenue. Rebuilding trust is mostly transparency and consistency over months, not a fix.