ETL Testing Interview Questions

"ETL testing is about proving the data is right, not the screens. A job extracts data from one or more sources, transforms it by business rules, and loads it into a target, usually a warehouse. My job is to show nothing got lost, nothing got duplicated, and every value in the target is what the rules say it should be. So the checks are mostly SQL: row counts and totals between source and target, column-by-column comparisons, recomputing each transformation independently, and checking for duplicates, nulls and orphan keys. Then there's behaviour over time, which normal app testing rarely has: does the daily incremental load pick up only the changes, does a rerun create duplicates, do bad records go to a reject table instead of failing the whole job. And the load has to finish inside its window, so performance matters too."

"In classic ETL the transformation happens in a separate engine, between source and target, so I mostly test from the outside: compare the source with the final tables and check the rejects. In ELT the raw data lands in the warehouse first, pretty much untouched, and the transformations run as SQL inside the warehouse. That changes two things for me. First, I get a new checkpoint: I can reconcile the raw layer against the source on its own, so if a number is wrong later I know it wasn't the extract. Second, the transformation logic is usually SQL in version control, so I can read it, review it, and run checks after each step, like uniqueness and not-null tests, as part of the same pipeline. What doesn't change is the end-to-end reconciliation. The business still cares that the final numbers match the source."

"The mapping document is the spec for an ETL job. For every target column it says where the data comes from, which source table and column, what rule is applied, like a trim, a lookup or a calculation, the data types on both sides, and what happens when the source is null. It also usually lists the join conditions and filters, like 'only active accounts'. I turn it into tests in two layers. At the table level I write cases for row counts, the filter rules and key uniqueness. Then I go line by line: a straight move gets a direct compare, a derived column gets a query that recomputes the rule from the source, and a lookup gets a check for the not-found case. While doing that I mark every rule that's ambiguous and take those questions to the analyst before testing starts, because that's where most defects hide."

"I start with counts, but I make them balance, not just match. The source count for the extract window should equal what loaded, plus what went to the reject table, plus what the rules deliberately filter out, like test accounts. If that doesn't add up, something vanished silently. Then I compare keys, not just totals: I pull the business keys from both sides and find which ones are in the source but not the target, and the other way round. That tells me exactly which records to chase. I also break the counts down by something like load date or region, because a total can match by accident when one slice is short and another has duplicates. And I check the extract window itself, because records created right at the boundary time are the ones that most often fall between two runs."

"A count only tells me how many rows arrived, not what's in them. If one row is dropped and another is duplicated, the count still matches. And a row can arrive with an amount rounded wrong or a date shifted by a day. So next I compare aggregates for the same slice on both sides: the sum of the amount columns, the count of distinct keys, and the min and max of the dates. If the sums differ, I know there's a value problem, even with equal counts. For a stronger check I hash each row: join the mapped columns with a delimiter, turn nulls into a fixed marker, format dates and decimals the same way on both sides, then compare the hash per key. Rows with different hashes are exactly the ones to inspect. The formatting step matters, because trailing spaces or a different date format will change the hash even when the data is fine."

"I use a set difference in both directions. Source EXCEPT target gives rows that are missing in the target or arrived with different values. Target EXCEPT source gives rows the target has that the source doesn't, which catches extra rows and the changed version of the same records. Oracle has long used the keyword MINUS for the same thing. Two things make this reliable. First, if a column is transformed, I apply the same rule on the source side in the query, otherwise every row shows up as different. Second, EXCEPT works on distinct rows, so if the target has a record twice, this comparison won't show it. That's why I always pair it with a duplicate check on the business key. One helpful detail: set operators treat two nulls as equal, so nulls don't create false mismatches the way an equals join would."

"I can't join across two platforms directly, and I don't want to move 50 million rows around just to learn that everything matches. So I compare in layers. First, on each side I run a query that groups by a bucket, like load date or a range of the key, and returns the count, the sum of the key amounts, and an aggregated hash for each bucket. That's maybe a few hundred rows per side, easy to compare in a script. Then only the buckets that differ get a row-level comparison, which is small. If the team already lands a raw copy of the source in a staging schema next to the target, I use that and run the set comparison in one database. The trap is making the hash identical on both platforms: same column order, same null marker, same date and decimal formatting, and the same hash function, or everything looks different."

"First I decide which columns should be unique. That's the business key, or for a fact table the grain, like order ID plus line number. Checking the surrogate key is pointless, because the load generates a new one for every row, so it's always unique even when the data is duplicated. Then it's a group by on those columns with HAVING COUNT(*) greater than one. Any row returned is a defect. A quick sanity check alongside it is comparing COUNT(*) with the count of distinct keys. When I find duplicates, the cause is usually one of three things: the job was rerun and inserted instead of upserting, a join to a lookup table matched more than one row, or two incremental windows overlapped. Knowing which one tells the developer where to look."

"For each column the mapping says is mandatory, I count the nulls in the target, and that count should be zero. For columns with a default rule, say a missing country becomes 'UNKNOWN', I test it from both sides. I make sure the source actually has nulls in my test data, then confirm those rows land with the default and not with a null, and that rows with a real value keep it. Then I look at values that aren't null but act like it: empty strings, a string of spaces, or placeholders like 'N/A' or a dummy date. Different source systems send missing data in different ways, and the rule often forgets one of them. I also compare the null count per column between source and target. If the source had 20 null emails and the target has 200, a transformation is wiping values."

"Many warehouses don't enforce foreign keys, for load speed, so I check them myself. I left join the fact table to each dimension on the key and keep rows where the dimension side is null. Those are orphans, and each one is a defect. But a clean result doesn't finish the job, because many designs avoid orphans on purpose. They map a failed lookup to a special 'unknown' row, often with a key like minus one. So I also count how many facts point to that unknown member. A handful might be normal. Thousands after a load means the lookup is broken, maybe a code changed case or picked up a trailing space. Last, I ask how late-arriving dimensions are handled, where a sale arrives before the product is set up, and I test that those facts get fixed once the product row arrives."

"I build a small batch that mixes good rows with rows designed to fail each rule: a missing mandatory field, a date like the 31st of February, a status code that isn't in the lookup, text in a numeric column, a value longer than the target column. Then I run the job and check three things. Every bad row should be in the reject table or error file with a reason that says which rule it broke, so someone can fix it at the source. The good rows in the same batch should load normally, unless the design says one bad row stops everything. And the numbers should balance: source equals loaded plus rejected. I also test the threshold if there is one, like the job failing when too many rows are rejected, and that the alert actually reaches someone. Last, I check that reprocessed rejects don't turn into duplicates."

"I don't read the developer's code and agree with it. I write the rule again myself, straight from the mapping, against the source data. Here I sum each customer's orders, apply the thresholds in a CASE, and join that to the target. The query returns only rows where the loaded segment differs from mine, and I wrap the target side in COALESCE so a null segment shows up as a mismatch instead of being skipped. Then I think about edges. Does a total of exactly 1000 count as silver? The mapping has to say, and I make sure test data sits right on the line. Customers with no orders won't appear in my inner join, so I check separately that they get whatever the rule says. And I ask whether cancelled or refunded orders count, because that's a common gap."

"I rebuild the summary myself from the line items, grouping by exactly the same grain, say date and store, and compare each measure: quantity, gross amount, discount and number of orders. Then I check the overall total for a period equals the detail total, after the same filters. The filters are where most bugs are. Are cancelled lines excluded? Are returns subtracted, and on which day? Then I look at time. If orders are stored in one time zone and the report is by local business day, an order late at night can land on the wrong day. I also check a day with no sales: does the summary have a zero row or no row, and which one does the report expect? And if the summary is refreshed daily, I check that late-arriving orders update an older day correctly."

"Conversions are dangerous because they often don't fail, they just change the value. For text, I check the longest values in the source against the target column length, because some loads cut off the end without any error. I also check names with accents or non-Latin characters, since an encoding mismatch turns them into question marks. For numbers, I compare decimal places: a price with four decimals going into a two-decimal column gets rounded or cut, and the rule should say which. Codes like postal codes or account numbers can lose leading zeros if someone converts them to numbers. Dates are the worst. A source sending 03/04 might mean March or April depending on the format, so I test with a day above twelve. And a timestamp converted between time zones can move to a different date near midnight."

"Slowly changing dimensions are about what happens when an attribute changes, like a customer moving city. Type 1 simply overwrites. So I change the city in the source, run the load, and check there's still one row for that customer with the new city. Type 2 keeps full history. The old row gets an end date and its current flag turned off, and a new row is inserted with a new surrogate key, the new city, a start date and the flag on. I test there's exactly one current row per customer, that the dates chain without gaps or overlaps, and that old facts still point to the old version. Type 3 keeps limited history in a column, like previous city, so I test the old value moves into that column. For all of them, a row with no change shouldn't create anything."

"The first check is simple: filter to current rows, group by customer ID, and flag any customer with more than one. I also check the other direction, active customers in the source with no current row at all. The second check is the history chain. I use LEAD over each customer's versions, ordered by start date, to put the next version's start next to this version's end. Before writing the condition I confirm the team's convention. In this query a version ends exactly where the next begins, so any row where they're not equal is either a gap, where the customer had no valid row for a while, or an overlap, where two versions were valid at once. The empty end date check catches an older version that was never closed, which is a classic defect. If the convention is that the end date is the day before the next start, I change the condition to match."

"A full load rebuilds the target from the whole source each time. It's simple but gets slow as data grows. An incremental load only picks up what changed since the last run, usually by comparing a last-updated timestamp with a saved watermark, or by reading a change log. For testing, I prepare three kinds of rows between two runs: brand new records, updated records, and records that didn't change. After the second run, new ones should be inserted, updated ones changed or versioned, and unchanged ones should be exactly as before, audit columns included. Then the edges. A record updated at exactly the watermark time is the classic miss or double count, depending on whether the filter uses greater-than or greater-than-or-equal. I check the watermark only moves when the run succeeds. And I check what happens when a source system updates a row without touching its timestamp."

"Rerun safety means running the same input twice leaves the target exactly as one run would. To test it, I load a batch, then take a snapshot: row counts per table, sums of the key measures, and a hash of every row for a sample of keys. Then I run the same batch again with the same parameters and compare. Nothing should grow. No duplicate fact rows, no new Type 2 versions for customers that didn't change, and no totals that doubled. When it fails, the usual cause is a plain insert instead of a merge, or a delete step that uses a different date range from the insert. The harder case is a failure in the middle, so I stop the job partway through, rerun it, and check the result matches a single clean run. That's the scenario that really happens at two in the morning."

"This one catches a lot of teams. If the incremental load picks up rows by their last-updated time, a deleted row just disappears from the source and nothing ever tells the warehouse. So first I find out how deletes are supposed to be detected: a change data capture feed that emits delete events, a regular key comparison between source and target, or soft deletes with a flag in the source. Then I confirm the rule for the target, because the business usually doesn't want history erased. Often the row stays but gets a deleted flag, or in a Type 2 dimension the current version is closed. For the test, I delete a few rows in the source, including one that was also updated earlier the same day, run the load, and check each one follows the rule. Then I check the reports drop them from the counts where they should."

"A fact table records events or measurements, like each sale with its quantity and amount, and holds keys that point to the dimensions. A dimension describes the who, what, where and when: customer, product, store, date. Facts are usually long and narrow and grow every day. Dimensions are wider and change more slowly. That changes how I test. For a fact table I reconcile measures against the source, like sums of amounts per day, check that the grain is unique, and check every key resolves to a dimension row. For a dimension I check each attribute against the mapping, that the business key is unique among current rows, that surrogate keys are unique, and that history is kept the way the design says, which is where the SCD tests come in. For the date dimension I also check there are no missing days."

"The grain is the plain answer to 'what is one row in this table?' For example, one row per order line, or one row per account per day. It's the first thing I confirm before testing a fact, because every other check depends on it. To test it, I take the columns that define the grain and check they're unique together. If the design says one row per order line but I find order lines appearing twice, either the data is duplicated or the grain was never what the document claims. The damage shows up in reports. If someone joins an order-level table to line-level data, the order total repeats on every line, and summing it multiplies revenue. Or a daily balance snapshot gets summed across a month, and each balance is counted thirty times. So I check the grain and also how the reports aggregate it."

"I build it from the mapping, not from whatever's lying around. For every rule I want at least one row that takes each branch, and rows sitting right on the boundaries, like an amount exactly at a threshold or a time at midnight. Then I add the uncomfortable data: nulls and blank strings in required fields, duplicates on the business key, invalid dates, text at maximum length, names with accents and apostrophes, and fact rows pointing to dimension keys that don't exist. The keys have to hang together across tables, so records match where I want them to and break only where I mean them to. For each row I write down what the target should look like, so the test is a comparison, not a judgement call. And I script the setup so I can reload the same data for every regression run."

"First I get the facts: how long the window is, what else runs in it, and how fast volume is growing. Then I test at today's production-like volume and at a projected volume, say what we expect in a year or two, because a load that fits today can quietly fail later. I measure each stage separately. If the whole job takes three hours, I want to know whether the time is in the extract, the transformations, or writing to the target. The usual culprits are processing rows one at a time instead of in sets, heavy indexes on the target during a bulk insert, lookups against huge tables without an index, and committing in tiny batches. For incremental loads, I also check that run time follows the number of changed rows, not the size of the whole table. If it grows with the table, something is scanning everything."

"The tool I use most is plain SQL in a database client, because nearly every check is a query. I've tested jobs built in graphical ETL tools and jobs written in code, and in both cases I also read the job logs and run history for row counts and rejects. For automation, I stop keeping queries in a notepad. Each check becomes a query that returns zero rows when things are fine, like the duplicate check or the orphan check, stored in version control with a name and the rule it tests. Then a small script or test framework runs them after every load and fails if any query returns rows. Some teams use a data validation library for this, and that's fine, the idea is the same. What matters is that the checks run every time, not only when a tester remembers."

"At my last company we loaded daily sales transactions into the warehouse. Row counts matched every day, the job was green, and it had passed testing. I was adding a check that compared amount totals per transaction type between source and target, and the sales total in the target was a bit higher than the source while refunds were lower. I pulled the keys that differed and found they all came from one new store system. It sent refunds with a new type code, and the transform had a CASE that treated any unknown code as a sale, so refunds were being added to revenue instead of taken off. The count was right because every row did arrive. We changed the rule so unknown codes go to the reject table instead of a default, reloaded the affected weeks, and I made the totals-per-type check part of the automated run."

"On a project at my last company, I got a mapping for a customer dimension where several rules just said things like 'derive status' or 'clean the phone number'. I couldn't write an expected result for those, so I couldn't test them. Instead of guessing, I went through the whole document, listed every line like that, and for each one wrote a concrete question with an example from the source data, like 'this customer has an expired contract and an open ticket, is the status Active or Lapsed?' I took the list to the business analyst and the developer together. That helped, because the developer had already made assumptions for some of them and two were wrong. We updated the mapping with the agreed rules, and only then did I write the test cases. It cost two days up front but saved a round of defects later."

"When I joined my last team, regression for a release meant a tester running about two hundred saved queries by hand over three days, with results in spreadsheets. I didn't try to automate everything. I started with the checks that had caught real incidents in the past year, mostly reconciliation totals, duplicates on business keys and orphan keys on the big fact tables. I turned each one into a query that returns zero rows when things are fine, put them in version control, and wired them into a script that ran after the nightly load in the test environment and posted failures to the team channel. That took a couple of weeks. After that, regression for those tables took under an hour, and twice in the first month the nightly run caught a broken join before it reached testing."

"First I stop comparing totals and find the actual missing rows by comparing keys, so I have a list instead of a number. Then I look for a pattern. Are they all from one source, one date, one status? I check the reject table first, then any filter rule in the mapping, then whether the source was still being written while the extract ran, which would make it an expected timing difference. If it's explained and matches the rules, I document it and we move on. If it isn't, I don't sit on it. I tell the release owner and the developer straight away: how many records, which ones, what the business impact is, like one region's sales being understated, and the options, fix now, release with a known issue and a reload, or delay. Whether it ships isn't my call alone, but nobody should decide without knowing."

"I don't argue in the ticket. I pull the evidence: a handful of the affected keys, the raw source values, the mapping rule, and what landed in the target, side by side. Sometimes that shows the developer is right. The source really is sending, say, negative quantities that shouldn't exist. Then the defect isn't closed, it's moved. The source team owns fixing the data, and we still ask whether the ETL should reject or flag those rows instead of loading them silently, which may be a new rule for the analyst to agree. Other times the evidence shows the source is fine and the transformation got it wrong, and then it's a normal defect again, with proof attached. Either way I keep it friendly. We both want correct numbers in the report, and a side-by-side usually ends the disagreement quickly."

"I understand why. Production data has variety that made-up data sometimes misses. But I'd raise the risk first. Test environments usually have wider access and weaker controls, and copying personal data there can break data protection rules, which differ by country, and almost certainly company policy. So I'd suggest a masked copy instead. Names, phone numbers, emails and addresses get replaced with realistic fake values, while keys stay consistent, so the same customer masks to the same value in every table and joins still work. Formats should stay valid too, so phone number rules can still be tested. Where we need real edge cases, I'd take the patterns from production, like the odd formats, and recreate them synthetically. And I'd check with whoever owns data protection first, so it's a decision on record."