ETL Testing Interview Questions

ETL stands for extract, transform, and load. Extract pulls data from source systems, transform applies business rules and cleansing, and load writes the result into the target data store.

Database testing focuses on schema, constraints, and stored procedures within a single database, while ETL testing validates the entire data movement pipeline across multiple source and target systems, including the transformation logic in between.

Source-to-target mapping is a document that specifies how each source field maps to a target field, including any transformation rule applied along the way. Testers use it as the reference to verify data correctness.

Data validation checks that data values loaded into the target match expected results based on the transformation rules, covering data types, formats, ranges, and business logic. It is usually done by comparing source and target query results.

A staging area is an intermediate storage location where extracted data lands before transformation, allowing cleansing and validation steps to run without touching the source or target systems directly. It also simplifies troubleshooting failed loads.

Data completeness testing confirms that all expected records and columns from the source made it into the target, usually by comparing row counts and checking for missing or truncated data. It catches records dropped during extraction or transformation.

Data accuracy testing verifies that transformed values in the target match what the business rules specify, such as confirming a currency conversion or a calculated field was applied correctly. It typically compares actual output against expected output.

A full load reloads the entire dataset from source to target every run, while an incremental load only processes new or changed records since the last run, usually tracked with a timestamp or change data capture. Incremental loads are far more common in production for performance reasons.

Data transformation testing verifies that business rules applied during the ETL process, such as aggregations, lookups, or derived calculations, produce correct results. Testers compare transformed target values against manually calculated expected values.

A data warehouse is a centralized repository that stores integrated data from multiple source systems, structured for reporting and analysis rather than transactional processing. ETL pipelines are the primary way data gets loaded into it.

A fact table stores quantitative, measurable data, such as sales amounts or transaction counts, along with foreign keys linking to dimension tables. It is the central table in a star schema.

A dimension table stores descriptive attributes, such as customer names or product categories, that provide context for the measures in a fact table. It is joined to fact tables through foreign keys.

Data cleansing is the process of identifying and correcting inaccurate, incomplete, or duplicate data before it loads into the target system. Common cleansing steps include trimming whitespace, standardizing formats, and removing duplicates.

A primary key check confirms that a designated key column contains unique, non-null values in the target table after loading. Violations often indicate duplicate records or a flawed deduplication step in the transformation logic.

Data type validation confirms that each target column stores data in the expected format, such as confirming a date field doesn't contain text or a numeric field doesn't have truncated decimals. Mismatches often cause reporting errors downstream.

A null value check verifies that columns which should always have a value, according to business rules, don't contain unexpected nulls in the target after the load. It's one of the most basic but frequently skipped checks.

Row count validation compares the number of records in the source against the number loaded into the target, adjusted for any expected filtering, to catch records lost during extraction or transformation.

A data quality rule is a specific, testable condition that data must satisfy, such as a date being within a valid range or an email field matching a valid format. ETL testers write test cases directly from these rules.

ETL regression testing re-runs existing test cases after a pipeline change, such as a new transformation rule or schema update, to confirm nothing that worked before has broken.

A test data set is a curated collection of source records, often including edge cases and known bad data, used to validate that the ETL pipeline handles both normal and exceptional scenarios correctly.

Duplicate data testing checks that the target does not contain unintended duplicate records after the load, usually by grouping on a business key and counting occurrences greater than one.

A lookup transformation retrieves a value from a reference table during processing, such as converting a country code into a full country name, and inserts it into the target output.

Data profiling examines source data to understand its structure, value distributions, and quality issues before building the ETL pipeline. It helps testers anticipate what kinds of validation checks will matter most.

A schema comparison test verifies that the target table's structure, meaning column names, data types, and lengths, matches what the design specification requires. It catches structural drift between environments.

Threshold-based validation flags records where a calculated value falls outside an expected range, such as a transaction amount that's unusually high, for manual review rather than automatic rejection.

A rejection table captures records that failed validation during the ETL run, along with the reason for rejection, so they can be reviewed and reprocessed rather than silently dropped.

A smoke test is a quick, high-level check run right after a deployment to confirm the pipeline executes without critical failure and loads a basic sample of data correctly, before deeper testing begins.

Metadata testing verifies that structural information about the data, like column definitions, data lineage, and table relationships, is documented and matches the actual implementation.

A business rule validation test confirms a specific piece of transformation logic, defined by the business, is implemented correctly, such as confirming a discount calculation applies only to eligible customer segments.

Data reconciliation is the process of comparing aggregated values, like sums or counts, between source and target to confirm the overall data volume and totals match after the ETL process completes.

A slowly changing dimension is a dimension table designed to track historical changes to attribute values over time, such as a customer's changing address, rather than overwriting the old value.

Unit testing in ETL validates a single transformation step or mapping in isolation, confirming it produces the correct output for a given input before it's tested as part of the full pipeline.

End-to-end testing validates the complete flow from source extraction through transformation to final load, confirming the pipeline works correctly as a whole rather than testing individual components separately.

A data mart is a smaller, subject-specific subset of a data warehouse, built for a particular department or business function, such as sales or finance, and typically loaded through its own ETL process.

Boundary value testing checks how the pipeline handles values at the edge of an expected range, such as the minimum and maximum allowed date or amount, since edge cases often expose transformation bugs that mid-range values don't.

I would independently calculate the expected aggregated values using a separate query against the source data, then compare them against what the target actually shows. I would also test boundary cases, like a month with zero transactions, to confirm the aggregation doesn't break on sparse data.

I would verify the change data capture logic correctly identifies new and modified records since the last run, then confirm the target reflects only those changes without reprocessing unchanged data. I would also test what happens when the incremental job fails partway through, to check if it can resume without duplicating records.

I would update a source record's attribute, run the ETL job, and confirm the dimension table correctly creates a new historical row rather than overwriting, depending on which SCD type is implemented. I'd also verify the effective date ranges and current-record flags are set correctly.

I would test each source's extraction independently first to isolate issues, then test the merge or join logic that combines them, paying close attention to how the pipeline handles mismatched keys or missing records from one source.

I would confirm the exchange rate table being referenced is current and matches the expected rate for the transaction date, then manually calculate a sample of converted values to compare against the target output.

I would deliberately introduce bad data, like an invalid date format or a missing required field, into the source and confirm the pipeline routes it to a rejection table or logs it correctly rather than silently failing or loading corrupted data.

I would run the pipeline against a production-scale data volume in a test environment and measure execution time against the required processing window, then identify the slowest transformation steps if it doesn't meet the SLA.

I would insert known duplicate records into the source test data with slight variations, like different timestamps, and confirm the pipeline retains only the correct record based on the defined deduplication rule.

I would simulate a column being added, removed, or having its data type changed in the source, then confirm whether the pipeline fails gracefully with a clear error or silently corrupts downstream data. Production pipelines should fail loudly rather than load bad data.

I would run a query checking for fact table foreign keys that don't have a matching dimension record, since orphaned facts usually indicate a timing issue between when dimensions and facts are loaded.

I would test each transformation stage with source data containing nulls in various fields, confirming whether the rule requires defaulting, rejecting, or passing through the null, based on what the business rules specify for that field.

I would run both the old and new pipelines against the same source data in parallel, then compare the outputs row by row and column by column to confirm the new implementation produces identical results before cutting over.

I would test records with timestamps across different time zones and around daylight saving transitions, confirming the pipeline consistently converts to the target time zone standard without off-by-one-hour errors.

I would send a record with a business date that falls into an already-processed period and confirm the pipeline either reprocesses that period correctly or handles the late arrival through a defined late-arriving dimension strategy.

I would build a test case for every distinct branch in the logic, including combinations that might interact unexpectedly, rather than only testing the most common path, since branching logic is where most transformation bugs hide.

I would confirm the masked output doesn't allow reverse identification of the original value while still preserving the format needed for downstream reporting, and check that fields requiring masking are consistently covered across all pipeline paths.

I would trace the data lineage back through each transformation stage and validate reconciliation at each intermediate layer rather than only comparing the very first source against the very last report, since that makes it much easier to isolate where a discrepancy was introduced.

I would simulate a failure partway through a load, such as killing the job, then confirm the recovery mechanism doesn't create duplicate records or leave the target in a partially loaded, inconsistent state.

I would independently recompute the KPI from raw source data using a different query path than the ETL pipeline uses, then compare the two results, since KPIs feeding leadership decisions need an independent verification path rather than trusting the pipeline's own logic.

I would build test cases with known near-duplicate records, varying spelling and formatting, and confirm the matching threshold correctly merges true duplicates while not incorrectly merging genuinely distinct customers.

I would trace a specific record through each transformation stage and confirm the audit columns, like load timestamp and source system identifier, are populated accurately at every step, beyond just the final target table.

I would test transactions that land right at rounding boundaries, confirming the pipeline applies the specified rounding method, like round half up, consistently, since small rounding inconsistencies compound into material reporting discrepancies at scale.

I would compare outputs from both implementations against the same source data to confirm they agree, since duplicated logic in two places is a common source of quiet discrepancies that surface only when someone notices numbers don't match.

I would compare data volume and value distribution between test and production, since production data often has edge cases, like real-world nulls or unusual characters, that synthetic test data doesn't capture. I would also check whether production runs under different concurrency or timing conditions that expose race conditions.

I would build automated monitoring that checks data freshness and completeness immediately after each load completes, rather than relying only on periodic manual testing, so SLA breaches get caught within minutes rather than discovered by a downstream consumer.

I would weigh the tool's ability to handle profiling and anomaly detection at scale against the learning curve and licensing cost, and consider whether custom scripts already cover the organization's specific validation patterns well enough that a tool's added value would be marginal.

I would maintain a versioned suite of test cases tied to specific business rule versions, and require any rule change to come with an updated or new test case before deployment, so regression coverage grows in step with the pipeline rather than lagging behind it.

I would trace the issue back through the data lineage to identify the earliest stage where the bad data was introduced, rather than patching each downstream symptom separately, since fixing only the visible report leaves the underlying pipeline defect to resurface elsewhere.

I would build validation that runs on micro-batches or windows of data as they arrive, checking completeness and accuracy incrementally, since waiting for a full end-of-day batch comparison defeats the purpose of a near-real-time pipeline.

I would run the legacy and new cloud pipelines in parallel against production data for a defined period, comparing outputs continuously, and only cut over once discrepancies have stayed at zero for a stable window of time.

I would validate the golden record creation logic in isolation first, since errors there propagate into every downstream pipeline, then separately validate that each consuming pipeline correctly picks up the corrected master data.

Production-scale testing catches performance and data volume issues that small synthetic sets miss, but it costs more in infrastructure and takes longer to set up. I would use synthetic data for fast logic validation during development and reserve production-scale testing for pre-release performance and scale checks.

I would test each stage in isolation with mocked inputs first to catch logic errors quickly, then run integration tests across the full chain to catch issues that only emerge from stage interactions, since testing only the full chain makes failures hard to localize.

I would build a reconciliation check comparing key aggregates between the two targets on a scheduled basis, since even a well-designed dual-write pipeline can drift due to timing differences or partial failures in one path but not the other.

I would build explicit mapping test cases for every field being merged, paying particular attention to fields that exist in one source but not the other, since gaps and default value handling in a merger scenario are where most data quality issues surface.

I would compare execution time trends against data volume growth to see if degradation is proportional or if something structural, like a missing index or an inefficient join added in a recent change, is the real cause, rather than assuming volume growth alone explains it.

I would automate the checks that run every deployment, like row counts and schema validation, since those catch the bulk of regressions cheaply, and reserve manual exploratory testing for new, complex transformation logic where test cases aren't yet well understood.

I would quantify the cost of past data quality incidents in terms of remediation time and business impact, then compare that against the platform's cost, since leadership responds better to a concrete cost avoidance argument than an abstract quality improvement pitch.

I would require a documented test plan covering completeness, accuracy, and performance benchmarks, reviewed by someone outside the development team, before a pipeline goes live, since self-certification tends to miss blind spots the original developer doesn't think to test.

I would prioritize documenting and cross-training on the highest-risk pipelines first, ranked by business impact, rather than trying to document everything at once, since knowledge concentration risk compounds the longer a pipeline goes unreviewed by more than one person.

I would track the frequency and cost of data quality incidents tied to that pipeline over time, since a rising incident rate despite patching is usually a stronger signal that the underlying design needs to change than any single large failure.

I would balance data freshness needs for realistic testing against privacy and storage cost, typically refreshing test data on a defined cadence with appropriate masking for sensitive fields rather than keeping an indefinite live copy.

I would classify incidents by business impact, like whether they affect financial reporting or customer-facing decisions, rather than by technical severity alone, since a technically minor issue in a high-visibility report often deserves faster response than a larger issue in a low-impact pipeline.

I would set strict enforcement for fields that feed regulated or financial reporting, while allowing more flexible schema handling, like accepting and flagging unexpected new fields rather than failing outright, for less critical analytical pipelines where agility matters more than rigid validation.

I would require detailed documentation and sign-off for pipelines feeding regulatory or financial reporting, while allowing lighter documentation for internal analytical pipelines, since applying uniform heavy documentation everywhere slows teams down without proportional risk reduction.

I would frame the cost against the business risk of a bad cutover on financial or regulatory pipelines, since the cost of parallel infrastructure for a few months is generally far smaller than the cost of a major data quality incident discovered after go-live.

I would define a small set of shared metrics, like completeness rate and reconciliation variance, that every unit reports consistently, while allowing supplementary unit-specific metrics, so leadership gets a comparable view without forcing every team into an identical reporting model that doesn't fit their data.

I would start by cataloging existing testing practices across teams to identify what's working well, then establish shared tooling and a common set of baseline checks rather than mandating a single rigid process. I would staff it with people who understand both testing discipline and enough of the business context to prioritize what matters.

I would tie each improvement initiative to a specific business risk it reduces, like fewer late report corrections or faster incident resolution, rather than describing it in testing terminology executives don't have context for. Framing progress in terms of incidents avoided rather than tests written keeps the conversation grounded in what leadership actually cares about.

I would invest early in a shared, reusable validation framework rather than letting each team build one-off scripts, since that reduces duplicated effort and creates a consistent quality bar as the organization's pipeline count grows. I would also build a rotation program so testing expertise spreads rather than concentrating on one original team.

I try to reframe the discussion around the concrete cost of a data quality failure at that bar, using a past incident as a reference point when possible, since abstract disagreements about acceptable risk resolve faster once there's a real cost anchor to discuss against.

I would require documented test rationale, beyond just test scripts, so future testers understand why a check exists and what business rule it protects. I would also pair junior and senior testers on incident investigations, since that's where the deepest institutional knowledge usually gets applied and is worth transferring deliberately.

I present the specific risk of shipping with reduced testing, framed in terms of likely business impact if a data quality issue slips through, and let leadership make an informed tradeoff rather than silently absorbing the risk or unilaterally blocking the deadline.

I would identify testers who already show judgment about which discrepancies matter most for the business, beyond just technical scripting skill, and deliberately involve them in cross-team prioritization decisions well before a transition, since that judgment is the hardest part of the role to build quickly.

I would run regular sessions on how new tooling changes testing approach, since teams that keep testing the new platform the same way they tested the old one tend to miss gaps specific to the new architecture. I would also pilot new testing approaches on a lower-risk pipeline before mandating them broadly.

I would present a clear timeline of what happened, the root cause, the immediate fix, and the longer-term prevention plan, rather than a vague reassurance that it's been handled, since executives responsible for that reporting need enough detail to assess their own exposure.

I would track the ratio of issues caught in testing versus those discovered after deployment over time, since a team that looks busy running tests but still has most issues surface in production has a coverage gap worth investigating directly rather than assuming activity equals effectiveness.

I would quantify the recurring cost of manual regression testing on frequently changing pipelines and compare it against automation's upfront investment, since automation's payback usually becomes clear within a few release cycles once the manual cost is made visible in concrete terms.

I try to show teams the standard as risk insurance framed in terms they already value, like fewer late-night incident calls, rather than presenting it as an unconditional mandate, since testers who understand the personal payoff adopt new process faster than those told simply to comply.

I would push the practice toward embedding validation directly into the pipeline itself, so quality checks run automatically as data moves rather than relying on testers to catch issues after the fact, since self-service platforms multiply the number of pipelines faster than any manual testing team can keep pace with.