What Is ETL Testing? (Definition + Real Example)
ETL Testing is the process of validating data as it moves from Source systems to the Target Data Warehouse (DW) through the Extract, Transform, and Load (ETL) process. The primary goal of ETL testing is to ensure that data is transferred accurately, transformed according to business requirements, and loaded successfully without any loss, duplication, or corruption.
ETL testing verifies several critical aspects of the data pipeline, including:
- Data accuracy
- Data completeness
- Data consistency
- Transformation correctness
- Performance
- Business-rule compliance
Since business reports and analytical dashboards depend on the quality of data stored in the data warehouse, ETL testing plays a vital role in ensuring reliable decision-making.
Real-World Example
Consider a banking data warehouse (DW) project.
The ETL process performs the following activities:
- Transactions are extracted from OLTP systems.
- Currency values are transformed based on exchange rates.
- Duplicate customer records are removed.
- Slowly Changing Dimension Type 2 (SCD2) logic is applied to dimension tables.
- Fact tables are loaded for reporting and analytics.
An ETL tester validates that:
- Row counts match between source and target.
- Transformation logic is correctly applied.
- Audit fields are properly populated.
- Fact and dimension tables contain accurate data.
- Business reports reflect the correct account balances and transaction details.
This validation ensures that financial reports generated from the data warehouse are accurate, consistent, and trustworthy.
Data Warehouse (DW) Flow: Source → Staging → Transform → Load → Reporting
A typical ETL process follows multiple stages before the data becomes available for business intelligence and reporting.
Source
The source layer contains the original operational data collected from various systems.
Typical sources include:
- OLTP databases
- Flat files
- CSV files
- Excel files
- APIs
- Third-party applications
At this stage, ETL testing focuses on verifying that all required data is successfully extracted from the source systems.
Staging
The staging area acts as a temporary storage location for raw extracted data.
Its primary purposes are:
- Landing raw data
- Performing minimal preprocessing
- Supporting data reprocessing
- Simplifying troubleshooting and debugging
The staging layer allows testers to compare raw source data with transformed data during validation.
Transform
The transformation layer applies business rules before loading the data into the warehouse.
Typical transformation activities include:
- Business logic implementation
- Data cleansing
- Currency conversion
- Duplicate removal
- Data standardization
- Slowly Changing Dimensions (SCD)
- Aggregations
- Lookups
- Derived column calculations
ETL testers verify that each transformation produces the expected results according to business requirements.
Load
After transformation, the processed data is loaded into the target data warehouse.
Typical target objects include:
- Fact tables
- Dimension tables
During this phase, testers validate:
- Record counts
- Surrogate keys
- Referential integrity
- Audit fields
- Successful data loading
Reporting
The reporting layer is the final stage where business users consume the curated data.
Reporting tools use the warehouse data to generate:
- Dashboards
- Business reports
- KPIs
- Analytics
- Executive summaries
ETL testing ensures that reports accurately reflect the underlying warehouse data.
ETL Testing Interview Questions + Best Answers (Basic → Advanced)
The following questions cover fundamental ETL concepts that are commonly asked during ETL testing interviews.
A. Fundamentals (1–10)
1. What is ETL testing?
Answer
ETL testing is the process of validating extracted, transformed, and loaded data against business rules and Source-to-Target (S2T) mappings.
Its objectives include:
- Verifying successful data extraction.
- Validating transformation logic.
- Ensuring correct data loading.
- Preventing data loss or duplication.
- Confirming business-rule compliance.
- Maintaining data quality throughout the ETL process.
The goal is to ensure that the data warehouse contains accurate and reliable information for reporting and analytics.
2. ETL vs DW testing?
Answer
Although closely related, ETL testing and Data Warehouse (DW) testing have different areas of focus.
| ETL Testing | Data Warehouse (DW) Testing |
| Focuses on ETL pipelines | Focuses on the complete data warehouse |
| Validates extraction, transformation, and loading | Validates schemas, facts, dimensions, and reporting |
| Ensures business rules are correctly applied | Ensures reporting accuracy and data integrity |
| Verifies data movement | Verifies warehouse structure and analytics |
In summary:
- ETL testing focuses on pipelines.
- DW testing includes schemas, fact and dimension tables, and reporting accuracy.
3. Why staging?
Answer
The staging area serves as a temporary storage location between the source systems and the data warehouse.
Its purposes include:
- Isolating raw data from production systems.
- Supporting ETL reprocessing.
- Simplifying debugging.
- Allowing intermediate validation.
- Improving ETL reliability.
Because staging tables contain raw extracted data, they provide an important checkpoint for ETL testers before business transformations are applied.
4. What is S2T?
Answer
S2T (Source-to-Target) is the Source-to-Target Mapping document that defines how source data is mapped to the target data warehouse.
It specifies:
- Source tables
- Target tables
- Column mappings
- Data types
- Transformation rules
- Lookup logic
- Default values
- Business calculations
ETL testers use the S2T document as the primary reference when validating ETL processes.
5. Fact vs Dimension?
Answer
Fact and Dimension tables are the two primary components of a dimensional data warehouse.
Fact Tables
Fact tables store measurable business data, such as:
- Sales
- Revenue
- Quantity
- Transactions
- Profit
Fact tables usually contain foreign keys that reference dimension tables.
Dimension Tables
Dimension tables store descriptive business information, such as:
- Customer
- Product
- Branch
- Employee
- Date
- Region
Dimensions provide context for the numerical values stored in fact tables.
6. Types of ETL testing?
Answer
ETL testing consists of several validation types performed throughout the ETL lifecycle.
Common types include:
- Source validation
- Transformation validation
- Target validation
- Data reconciliation
- Performance testing
Each type focuses on a specific stage of the ETL process to ensure complete and accurate data movement.
7. What are audit fields?
Answer
Audit fields are metadata columns that help track ETL processing and data lineage.
Common audit fields include:
- load_dt
- batch_id
- created_by
- updated_dt
These fields are used to monitor ETL execution, support troubleshooting, and maintain traceability of loaded data.
8. What is data reconciliation?
Answer
Data reconciliation is the process of comparing source and target data to verify consistency after the ETL process.
Typical reconciliation activities include:
- Comparing record counts.
- Comparing aggregated sums.
- Validating totals.
- Detecting missing records.
- Identifying duplicate records.
- Verifying business calculations.
Successful reconciliation confirms that the target data accurately reflects the source data.
9. What is data lineage?
Answer
Data lineage refers to the ability to trace data from its original source through every stage of the ETL process until it reaches the final report or dashboard.
Data lineage provides:
- Source traceability.
- Transformation history.
- Load tracking.
- Report validation.
- Regulatory compliance.
- Easier troubleshooting.
It enables ETL testers and data engineers to understand how data has been processed throughout the pipeline.
10. Common ETL defects?
Answer
ETL testing commonly identifies defects that occur during extraction, transformation, or loading.
Some of the most frequently encountered ETL defects include:
- Data truncation.
- Duplicate records.
- NULL value propagation.
- Incorrect joins.
- Mapping errors.
- Missing records.
- Data type mismatches.
- Incorrect business rule implementation.
- Failed lookups.
- Incorrect aggregations.
Identifying and resolving these defects ensures that the data warehouse maintains high data quality and supports accurate business reporting.
B. Mapping & Transformations (11–25)
11. Mapping validation steps?
Answer
Mapping validation ensures that data is correctly transferred from the source system to the target according to the Source-to-Target (S2T) mapping document.
During mapping validation, an ETL tester verifies:
- Joins between source tables.
- Transformation expressions.
- Filter conditions.
- Lookup logic.
- Column mappings.
- Default values.
- Business rules.
- Data type conversions.
The objective is to confirm that every source column is correctly mapped and transformed before loading into the target.
12. Handling nulls?
Answer
Handling NULL values is an important part of ETL testing because mandatory fields should not contain missing data unless explicitly allowed.
Common approaches include:
- Assigning default values.
- Using COALESCE() or similar functions to replace NULLs.
- Rejecting invalid rows.
- Storing rejected rows in error tables with appropriate reason codes.
- Validating mandatory columns before loading.
Proper NULL handling helps maintain data quality and prevents downstream reporting issues.
13. SCD Type 1 vs Type 2?
Answer
Slowly Changing Dimensions (SCDs) manage changes in dimension data over time.
| SCD Type 1 | SCD Type 2 |
| Overwrites existing data | Preserves historical data |
| No history maintained | History maintained |
| Updates existing row | Inserts a new row |
| Simple implementation | Uses effective dates and versioning |
In summary:
- Type 1 overwrites existing values.
- Type 2 preserves history using effective dates and version records.
14. Surrogate vs Natural Key?
Answer
Data warehouses commonly use two types of keys.
Surrogate Key
- Generated by the data warehouse.
- Artificial numeric identifier.
- Stable and independent of source systems.
Natural Key
- Comes directly from the source system.
- Represents the actual business identifier.
- May change over time.
In short:
- Surrogate keys are DW-generated.
- Natural keys originate from the source system.
15. Deduplication strategy?
Answer
Deduplication removes duplicate business records before loading data into the warehouse.
A common strategy includes:
- Creating hashes using business keys.
- Using ROW_NUMBER() to identify duplicate rows.
- Keeping the required record based on business rules.
- Rejecting or removing duplicate records.
This approach ensures that only unique business records are loaded.
16. What is CDC?
Answer
CDC (Change Data Capture) is a technique used to identify and process only new or modified records.
Instead of loading the entire dataset, CDC captures changes such as:
- New inserts.
- Updates.
- Deletes.
CDC is commonly used for incremental ETL loads because it improves performance and reduces processing time.
17. Lookup vs Join?
Answer
Although both combine data, they serve different purposes.
| Lookup | Join |
| Cached reference check | Combines datasets |
| Usually smaller reference data | Used for relational data retrieval |
| Faster for reference validation | Retrieves matching records from multiple tables |
| Frequently used in ETL transformations | Common SQL operation |
In summary:
- Lookups perform cached reference checks.
- Joins combine datasets based on matching keys.
18. Late arriving dimensions?
Answer
Late arriving dimensions occur when fact records arrive before their related dimension records.
A common approach is to:
- Insert placeholder dimension records.
- Assign temporary surrogate keys.
- Update the dimension once complete data becomes available.
This ensures that fact records can still be loaded without violating referential integrity.
19. Reject handling?
Answer
Reject handling manages invalid records encountered during ETL processing.
Typical practices include:
- Routing invalid rows to error tables.
- Recording rejection reason codes.
- Logging validation failures.
- Allowing corrected records to be reprocessed later.
Proper reject handling simplifies troubleshooting and improves data quality.
20. Soft deletes?
Answer
Soft deletes mark records as inactive instead of physically removing them.
Common implementations include:
- Active/Inactive flags.
- Delete indicators.
- Status columns.
This approach preserves historical data while preventing deleted records from appearing in active reports.
21. What is data masking?
Answer
Data masking is the process of obfuscating sensitive information to protect confidential data.
Sensitive fields commonly masked include:
- Customer names.
- Email addresses.
- Phone numbers.
- Credit card numbers.
- Social Security Numbers.
- Account numbers.
Data masking supports privacy regulations and protects Personally Identifiable Information (PII).
22. What is hashing used for?
Answer
Hashing is commonly used in ETL testing for:
- Change detection.
- Deduplication.
- Data reconciliation.
- Checksum validation.
- Comparing large datasets efficiently.
Hash values help detect differences without comparing every column individually.
23. Slowly changing fact?
Answer
Fact tables generally store transactional or measurable data and rarely change after loading.
When corrections are required:
- Adjustment records are inserted.
- Correction transactions are applied.
- Existing fact records are generally not overwritten.
This preserves historical accuracy while reflecting business corrections.
24. Effective dating?
Answer
Effective dating manages historical versions of records.
Typical columns include:
- valid_from
- valid_to
These dates define the period during which a record is considered valid, enabling accurate historical reporting.
25. Conformed dimensions?
Answer
Conformed dimensions are shared dimension tables used across multiple data marts or subject areas.
Examples include:
- Customer
- Product
- Date
- Geography
Using conformed dimensions ensures consistent reporting across the enterprise.
C. SQL-Driven Validation (26–40)
SQL is the primary validation tool used by ETL testers. The following questions focus on common SQL-based validation techniques used in enterprise ETL projects.
26. Row count check?
Answer
Row count validation compares the number of records in the source and target after applying the required filters.
This helps verify:
- Successful extraction.
- Correct transformation.
- Complete loading.
- No missing records.
Row count validation is usually the first step in ETL testing.
27. Sum reconciliation?
Answer
Sum reconciliation validates that numerical measures remain accurate after transformation.
Examples include:
- Sales totals.
- Revenue.
- Quantity.
- Transaction amounts.
Comparing aggregated values between source and target confirms transformation accuracy.
28. Duplicate detection?
Answer
Duplicate records can be identified using SQL aggregation.
A common approach is:
- GROUP BY
- HAVING COUNT(*) > 1
This helps identify duplicate business keys before or after loading.
29. Referential integrity?
Answer
Referential integrity ensures that every foreign key in the fact table matches a valid key in the corresponding dimension table.
This validation prevents orphan records and maintains consistency across the data warehouse.
30. Window functions usage?
Answer
Window functions are commonly used for:
- Ranking.
- Deduplication.
- SCD logic.
- Change detection.
- Latest record selection.
Functions such as ROW_NUMBER(), RANK(), and DENSE_RANK() are widely used in ETL testing.
31. Performance checks?
Answer
Performance validation focuses on ensuring efficient query execution.
Common checks include:
- Index usage.
- Partition pruning.
- Query execution plans.
- Full table scans.
- Query execution time.
These validations help ensure ETL jobs meet performance expectations.
32. Data type mismatch?
Answer
Data type validation ensures that source values are correctly converted before loading into the target.
Testers verify:
- Casting rules.
- Numeric precision.
- Character lengths.
- Date conversions.
- Decimal formatting.
This prevents truncation and conversion errors.
33. Incremental load validation?
Answer
Incremental load validation ensures that only new or modified (delta) records are processed.
Testers verify:
- Newly inserted records.
- Updated records.
- CDC implementation.
- Last modified timestamps.
- Batch processing logic.
34. Reject count validation?
Answer
Reject count validation compares:
- Expected rejected records.
- Actual rejected records.
The goal is to ensure that all invalid records are properly captured in error tables.
35. Data freshness?
Answer
Data freshness confirms that the warehouse contains the latest available data.
A common validation checks whether:
- MAX(load_dt) falls within the defined SLA.
- Daily or scheduled loads completed successfully.
36. Aggregation correctness?
Answer
Aggregation validation verifies that summarized values remain accurate after ETL processing.
Typical validations use:
- GROUP BY
- SUM()
- COUNT()
- AVG()
These checks ensure reporting accuracy.
37. Join correctness?
Answer
Join validation ensures that:
- Correct join keys are used.
- Relationships are accurate.
- Join cardinality is maintained.
- No unintended duplicates or missing records are introduced.
38. Null propagation risk?
Answer
Testers verify that mandatory fields do not unintentionally become NULL during transformation.
Validation focuses on:
- Mandatory columns.
- Transformation expressions.
- Lookup failures.
- Default value assignments.
39. Audit reconciliation?
Answer
Audit reconciliation verifies that:
- Batch totals match.
- Audit counts are accurate.
- Record counts reconcile.
- Batch identifiers are consistent.
This confirms successful ETL execution.
40. Restartability?
Answer
Restartability testing verifies that ETL jobs can be safely rerun after failure.
The process should ensure:
- Idempotent loads.
- No duplicate records.
- No missing records.
- Correct checkpoint recovery.
This guarantees reliable ETL processing in production.
D. Advanced & Real-Time ETL Interview Questions (41–55)
These questions evaluate your ability to handle complex ETL scenarios encountered in enterprise production environments.
41. How to test SCD2 end-to-end?
Answer
Validate:
- Record versioning.
- Effective dates.
- Expiry dates.
- Current flag.
- Surrogate key generation.
- Historical record preservation.
This ensures that SCD Type 2 maintains complete change history.
42. Handling late data?
Answer
Late-arriving data is managed using:
- Backdated inserts.
- Reprocessing affected records.
- Re-aggregation of reports.
- Updating historical dimensions when required.
This keeps reporting accurate despite delayed data arrivals.
43. Large file testing?
Answer
Large datasets are validated using:
- Chunking.
- Parallel processing.
- Checksums.
- Hash totals.
- Sampling.
- Aggregate comparisons.
These techniques improve validation efficiency while maintaining accuracy.
44. Data skew?
Answer
Data skew occurs when data is unevenly distributed across partitions or processing nodes.
ETL testers identify:
- Uneven partition sizes.
- Processing bottlenecks.
- Long-running tasks.
- Resource imbalance.
Detecting skew helps improve ETL performance.
45. Error reprocessing?
Answer
When errors occur:
- Correct the source data or transformation logic.
- Reload data from the staging area.
- Validate corrected records.
- Confirm successful processing.
Using staging tables avoids re-extracting all source data.
46. Cross-system reconciliation?
Answer
Cross-system reconciliation ensures consistency between different systems by validating:
- Currency conversions.
- Time zone differences.
- Data formats.
- Business rules.
- Record counts.
- Financial totals.
This is especially important in multi-system integrations.
47. PII compliance?
Answer
Personally Identifiable Information (PII) is protected using:
- Data masking.
- Encryption.
- Role-based access controls.
- Secure storage.
- Audit logging.
These practices help organizations comply with data privacy regulations.
48. Time-zone issues?
Answer
To maintain consistency across systems, date and time values are typically normalized to UTC (Coordinated Universal Time).
This prevents discrepancies caused by different local time zones and daylight saving changes.
49. End-to-end BI validation?
Answer
End-to-end Business Intelligence (BI) validation ensures that:
- Report totals match the data warehouse.
- Dashboard calculations are accurate.
- KPIs reflect the correct warehouse data.
- Business reports produce consistent results.
This confirms that the entire ETL pipeline supports reliable reporting.
50. SLA breaches?
Answer
When an ETL job exceeds its Service Level Agreement (SLA), testers investigate:
- Job execution statistics.
- SQL performance.
- Resource utilization.
- Data volume changes.
- Bottlenecks in the ETL process.
The objective is to identify the root cause and restore expected performance.
51. Schema drift?
Answer
Schema drift occurs when the structure of the source data changes unexpectedly.
Examples include:
- Added columns.
- Removed columns.
- Renamed columns.
- Data type changes.
ETL testers verify that such changes are detected and handled without disrupting data processing.
52. Data quality rules?
Answer
Data quality rules define acceptable standards for the data being processed.
Typical validations include:
- Completeness checks.
- Uniqueness checks.
- Accuracy validation.
- Consistency validation.
- Threshold-based alerts for exceptions.
These rules help ensure that only high-quality data is loaded into the data warehouse.
53. Rollback strategy?
Answer
A rollback strategy enables recovery if an ETL load fails.
Common approaches include:
- Partition swaps.
- Database backups.
- Transaction rollbacks.
- Restoring previous snapshots.
- Reprocessing from the staging layer.
This minimizes downtime and preserves data integrity.
54. Parallel loads risk?
Answer
Parallel ETL processing improves performance but introduces risks such as:
- Record locking.
- Duplicate data.
- Race conditions.
- Resource contention.
- Out-of-order processing.
ETL testers validate that parallel loads maintain data consistency and integrity.
55. Cloud DW nuances?
Answer
Cloud Data Warehouses introduce considerations beyond traditional on-premises systems.
Common areas include:
- Cost versus performance trade-offs.
- Elastic scaling.
- Compute and storage separation.
- Auto-scaling behavior.
- Query optimization.
- Resource management.
ETL testers ensure that cloud-based ETL processes remain efficient, scalable, and cost-effective while delivering accurate and reliable data.
Real SQL Query Examples (with Sample Datasets)
SQL is the foundation of ETL testing because it is used to validate data extraction, transformation, loading, and reconciliation between source and target systems. The following examples demonstrate common SQL validations performed in real-world ETL projects.
Sample Datasets
The following sample tables represent a simple data warehouse model used for ETL validation.
Source Table
src_orders(
order_id,
cust_id,
amount,
order_dt
)
Description
- order_id – Unique order identifier.
- cust_id – Customer identifier from the source system.
- amount – Order amount.
- order_dt – Order creation date.
Dimension Table
dim_customer(
cust_sk,
cust_id,
current_flag
)
Description
- cust_sk – Surrogate key generated in the data warehouse.
- cust_id – Business (natural) customer identifier.
- current_flag – Indicates whether the dimension record is the current version.
Fact Table
fact_sales(
order_id,
cust_sk,
amount,
load_dt
)
Description
- order_id – Order identifier.
- cust_sk – Customer surrogate key.
- amount – Sales amount stored in the warehouse.
- load_dt – ETL load date.
1. JOIN Validation
JOIN validation checks whether every fact record has a corresponding dimension record. This helps identify orphan records and validates referential integrity.
SELECT COUNT(*) AS missing_dim
FROM fact_sales f
LEFT JOIN dim_customer d
ON f.cust_sk = d.cust_sk
WHERE d.cust_sk IS NULL;
Purpose
This query verifies that:
- Every fact record has a matching dimension record.
- No orphan surrogate keys exist.
- Referential integrity is maintained.
- Dimension lookups have been performed correctly.
If the query returns a value greater than zero, there are fact records without corresponding dimension records that require investigation.
2. GROUP BY Reconciliation
Aggregation reconciliation compares summarized values between the source and target to ensure transformation accuracy.
Source Query
SELECT SUM(amount) AS src_sum
FROM src_orders;
Target Query
SELECT SUM(amount) AS tgt_sum
FROM fact_sales;
Purpose
This validation confirms that:
- Total sales amounts match.
- No data has been lost during transformation.
- Business calculations remain accurate.
- Financial reports are reliable.
If the aggregated totals differ, the ETL tester should review transformation logic, filters, rejected records, and business rules.
3. Window Function (Dedup)
Window functions are commonly used to identify and remove duplicate records while retaining the required version of each record.
SELECT *
FROM
(
SELECT order_id,
cust_id,
order_dt,
ROW_NUMBER() OVER
(
PARTITION BY order_id
ORDER BY order_dt DESC
) rn
FROM src_orders
) t
WHERE rn = 1;
Purpose
This query helps:
- Identify duplicate records.
- Retain the most recent order for each order_id.
- Support deduplication during ETL processing.
- Validate incremental load logic.
The ROW_NUMBER() function assigns a sequential number to each record within the same order_id, allowing only the latest record to be selected.
4. SCD2 Check
This query validates whether multiple versions of a customer record exist, which is expected in a Slowly Changing Dimension Type 2 (SCD2) implementation.
SELECT cust_id,
COUNT(*) AS versions
FROM dim_customer
GROUP BY cust_id
HAVING COUNT(*) > 1;
Purpose
This validation helps verify:
- Customer history is preserved.
- Multiple versions of changed customer records exist.
- SCD Type 2 logic is functioning correctly.
- Historical records have not been overwritten.
Records returned by this query indicate customers with more than one version stored in the dimension table.
5. Performance Tuning Hint
Performance validation helps ensure SQL queries execute efficiently on large datasets.
— Ensure partition pruning and indexed joins
EXPLAIN
SELECT /*+ USE_HASH(f d) */ *
FROM fact_sales f
JOIN dim_customer d
ON f.cust_sk = d.cust_sk;
Purpose
This query is used to analyze:
- Query execution plans.
- Join strategy.
- Index utilization.
- Partition pruning.
- Estimated execution cost.
- Overall SQL performance.
Reviewing the execution plan helps identify opportunities to optimize ETL jobs and improve query performance.
Scenario-Based ETL Testing Questions (Real Time)
Scenario-based questions evaluate your ability to troubleshoot practical ETL issues encountered in production environments.
Scenario 1: Mismatch Counts After Load
Question
The source and target record counts do not match after the ETL load. What should you check?
Answer
Investigate the following:
- Transformation filters.
- Rejected records.
- CDC (Change Data Capture) window.
- Join conditions.
- Incremental load logic.
- ETL error logs.
The objective is to identify why records were excluded or not processed.
Scenario 2: Unexpected NULL Values
Question
Mandatory columns contain unexpected NULL values in the target table. How would you investigate?
Answer
Validate:
- COALESCE() or default value logic.
- Source data constraints.
- Lookup failures.
- Transformation expressions.
- Source-to-Target (S2T) mappings.
- ETL job logs.
This helps determine whether NULL values originated from the source or were introduced during transformation.
Scenario 3: Duplicate Facts
Question
Duplicate records are found in the fact table. What should you verify?
Answer
Check the following:
- Business keys.
- Deduplication logic.
- Join conditions.
- Incremental load process.
- Window function implementation.
- Source duplicate records.
Proper deduplication ensures that each business transaction is loaded only once.
Scenario 4: Slow ETL Jobs
Question
The ETL job is taking longer than expected. What areas would you analyze?
Answer
Review:
- SQL joins.
- Table partitions.
- Parallel processing configuration.
- Database indexes.
- Query execution plans.
- Resource utilization.
Optimizing these areas helps improve ETL performance and ensures that Service Level Agreements (SLAs) are met.
Scenario 5: Late Data Impacts Aggregates
Question
Late-arriving data changes aggregate values. How should this be handled?
Answer
The affected partitions should be recomputed to ensure that aggregate values reflect the newly received data.
Additional validations include:
- Refreshing summary tables.
- Recalculating aggregates.
- Verifying updated reports.
- Confirming data consistency across reporting layers.
ETL Tools You Must Know
Interviewers often ask about popular ETL tools to understand your exposure to industry-standard technologies. However, they generally place greater emphasis on ETL concepts, SQL skills, and testing approaches than on tool-specific user interfaces.
Informatica
Common interview topics include:
- Enterprise mappings.
- Workflows.
- Sessions.
- Transformations.
- Repository management.
Microsoft SQL Server Integration Services (SSIS)
Frequently discussed topics include:
- SQL-centric ETL development.
- Control Flow.
- Data Flow.
- Packages.
- Error handling.
Ab Initio
Interviewers may ask about:
- High-performance ETL graphs.
- Parallel processing.
- Components.
- Data processing pipelines.
Pentaho
Common topics include:
- Open-source analytics.
- Kettle transformations.
- Jobs.
- Data integration.
Talend
Typical interview discussions cover:
- Cloud ETL.
- Open-source ETL development.
- Job design.
- Data migration.
- Integration components.
Interview Tip:
While familiarity with ETL tools is beneficial, interviewers primarily assess your understanding of ETL concepts, SQL validation techniques, business logic, and your overall testing approach.
ETL Defect Examples + Test Case Samples
Understanding real-world ETL defects helps testers identify root causes and validate corrective actions effectively.
Defect Example
Defect
Amount doubled in the fact table
Cause
Many-to-many join resulting in duplicate records.
Fix
- Correct the join keys.
- Add deduplication logic.
- Validate business key uniqueness.
- Re-run ETL processing.
- Perform reconciliation to confirm corrected results.
Sample ETL Test Case
Objective
Validate SCD Type 2 customer history.
Steps
- Load a customer record.
- Modify one or more customer attributes.
- Execute the ETL process.
- Verify that a new dimension record is created.
- Validate effective and expiry dates.
- Confirm surrogate key generation.
- Verify the current flag values.
Expected Result
- The old record is marked as expired.
- A new record is inserted.
- The new record has current_flag = ‘Y’.
- Historical information is preserved correctly.
- Effective and expiry dates are populated as expected.
Quick Revision Sheet (Cheat)
Use the following checklist for a quick review before an ETL testing interview.
Data Validation
- Validate record counts.
- Compare aggregated sums.
- Check NULL values.
- Detect duplicate records.
ETL Concepts
- Source-to-Target (S2T) mapping validation.
- SCD Type 1 and SCD Type 2.
- Audit field validation.
- Fact and dimension tables.
- Incremental and full loads.
SQL Techniques
- JOIN validations.
- GROUP BY reconciliation.
- Window functions for deduplication.
- Aggregate validations.
- Referential integrity checks.
Performance
- Monitor ETL performance.
- Review execution plans.
- Validate partition pruning.
- Ensure SLAs are met.
Frequently Asked Questions (FAQs)
Q1. What are real-time ETL testing scenarios?
Answer
Common real-time ETL testing scenarios include:
- Record count mismatches.
- Late-arriving data.
- Change Data Capture (CDC) failures.
- Duplicate records.
- NULL value propagation.
- Performance issues.
- Data reconciliation failures.
- Incorrect business transformations.
These scenarios help interviewers assess your troubleshooting and problem-solving skills in production environments.
Q2. What is the best SQL for ETL validation?
Answer
The most commonly used SQL techniques for ETL validation include:
- JOINs for data comparison.
- GROUP BY for aggregation validation.
- Window functions for deduplication and ranking.
- Aggregate functions such as SUM() and COUNT().
- EXPLAIN or EXPLAIN ANALYZE for performance analysis.
These SQL features enable testers to validate data quality, transformation logic, and ETL performance effectively.
Q3. How do you test SCD2?
Answer
Testing an SCD Type 2 implementation involves validating:
- Record versioning.
- Effective dates.
- Expiry dates.
- Current flag values.
- Surrogate key generation.
- Historical record preservation.
A successful SCD Type 2 test confirms that previous versions remain available while the latest version is correctly marked as the current record.