What Is ETL Testing? (Definition + EY-Style Real Example)
ETL Testing is the process of ensuring that data extracted from multiple source systems is correctly transformed according to business rules and accurately loaded into a Data Warehouse (DW) for reporting and analytics.
The primary objective of ETL testing is to verify that the Extract → Transform → Load (ETL) process preserves data accuracy, completeness, consistency, and integrity while meeting business and reporting requirements.
ETL testing helps ensure that:
- Data is correctly extracted from all source systems.
- Business transformations are accurately applied.
- Data is loaded into the target data warehouse without loss or duplication.
- Reports and dashboards are based on reliable, validated data.
- Audit, reconciliation, and data quality requirements are satisfied.
Because organizations depend on data warehouses for reporting, compliance, and business intelligence, ETL testing is a critical part of enterprise data engineering and analytics projects.
EY-Style Real-World Example
In large consulting programs (such as those commonly delivered by consulting firms for enterprise clients), ETL testing often involves managing large volumes of data from multiple business applications while ensuring strict data quality and compliance requirements.
A typical ETL process may include:
- Multiple source systems (ERP, CRM, and flat files).
- Complex transformations involving tax rules and regulatory calculations.
- Strict audit, reconciliation, and data quality controls.
An ETL tester validates that:
- Data is extracted correctly from every source system.
- Business transformation rules are applied accurately.
- Financial and regulatory calculations follow approved business logic.
- Data reconciliation is successful between source and target systems.
- Audit fields are populated correctly.
- Financial or regulatory reports exactly match the approved business logic and source data.
These validations help organizations produce accurate reports for internal decision-making, financial analysis, and regulatory compliance.
Data Warehouse Flow: Source → Staging → Transform → Load → Reporting
A Data Warehouse (DW) follows a structured ETL pipeline that moves data from operational systems to reporting platforms.
Source
The source layer contains operational business data collected from different applications.
Typical source systems include:
- OLTP databases
- APIs
- Files
At this stage, ETL testing verifies that all required data is successfully extracted from the source systems without omissions or corruption.
Staging
The staging layer temporarily stores the raw extracted data before any business transformations are applied.
Its primary purposes include:
- Landing raw data.
- Supporting data validation.
- Simplifying troubleshooting.
- Enabling ETL restart and recovery.
Since no transformations are performed in the staging area, ETL testers validate that the staging data matches the source data exactly.
Transformation
The transformation layer applies business rules to convert raw data into a format suitable for reporting and analytics.
Typical transformation activities include:
- Applying business rules.
- Performing joins.
- Handling Slowly Changing Dimensions (SCD).
- Data cleansing.
- Data standardization.
- Regulatory calculations.
- Aggregations.
ETL testers verify that each transformation follows the Source-to-Target (S2T) mapping document and 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, ETL testers validate:
- Successful data loading.
- Record counts.
- Surrogate key generation.
- Referential integrity.
- Audit field population.
These checks ensure that the data warehouse contains accurate and complete business data.
Reporting
The reporting layer is where business users consume curated data through reporting and analytics platforms.
Typical reporting outputs include:
- BI tools.
- Dashboards.
- Regulatory reports.
ETL testing ensures that these reports accurately reflect the data stored in the warehouse and comply with business and regulatory requirements.
EY ETL Testing Interview Questions & Answers
(Basic → Advanced | Scenario-Based)
The following interview questions focus on ETL concepts and real-world scenarios that are commonly discussed in enterprise consulting projects involving large-scale data integration, reporting, and regulatory compliance.
Interviewers typically evaluate your ability to:
- Explain ETL architecture and data warehouse concepts.
- Validate Source-to-Target (S2T) mappings.
- Verify transformation logic.
- Write SQL queries for data validation.
- Perform source-to-target reconciliation.
- Test Slowly Changing Dimensions (SCD Type 1 and Type 2).
- Validate incremental and full data loads.
- Identify and troubleshoot ETL defects.
- Validate audit fields and data quality rules.
- Handle scenario-based production issues such as duplicate records, NULL values, late-arriving data, and performance bottlenecks.
- Ensure that business reports accurately reflect the data stored in the data warehouse.
Preparing both conceptual knowledge and practical ETL validation scenarios will help you confidently answer interview questions related to enterprise data warehouse projects and demonstrate your ability to handle real-world ETL testing challenges.
Basic ETL Testing Questions (1–15)
EY ETL Testing Interview Questions & Answers
(Basic → Advanced | Scenario-Based)
The following interview questions cover the ETL testing concepts and real-world scenarios commonly discussed in enterprise consulting projects involving financial reporting, regulatory compliance, and large-scale data warehouses.
Basic ETL Testing Questions (1–15)
1. What is ETL testing?
Answer
ETL testing is the process of validating extracted, transformed, and loaded data to ensure accuracy, completeness, consistency, and correctness throughout the ETL pipeline.
It verifies that:
- Data is extracted correctly from source systems.
- Business transformations are applied accurately.
- Data is loaded successfully into the target data warehouse.
- No data is lost, duplicated, or corrupted during processing.
The goal is to ensure that reports and analytics are based on reliable and trustworthy data.
2. Why is ETL testing critical in EY projects?
Answer
ETL testing is critical because data is often used for:
- Financial reporting.
- Audit reporting.
- Regulatory reporting.
Enterprise consulting projects typically require high levels of data accuracy and traceability. ETL testing ensures that business reports match approved business rules and comply with regulatory requirements.
3. What is Source-to-Target (S2T) mapping?
Answer
Source-to-Target (S2T) mapping is a document that defines how each source field maps to the corresponding target field, including all transformation rules.
It typically contains:
- Source tables and columns.
- Target tables and columns.
- Data types.
- Transformation logic.
- Lookup rules.
- Default values.
- Business calculations.
ETL testers use the S2T document as the primary reference during validation.
4. What are common ETL testing types?
Answer
The most common ETL testing types include:
- Source validation.
- Transformation validation.
- Target validation.
- Data reconciliation.
Each type focuses on a specific stage of the ETL process to ensure complete and accurate data movement.
5. What is staging area testing?
Answer
Staging area testing ensures that the staging data exactly matches the source data before any transformations are applied.
The tester validates:
- Record counts.
- Data values.
- File completeness.
- No transformations.
- No data loss.
This provides a reliable baseline for subsequent ETL validations.
6. What are audit fields?
Answer
Audit fields are metadata columns used to track ETL processing.
Common audit fields include:
- load_date
- batch_id
- created_by
- updated_date
These fields help monitor ETL execution, support troubleshooting, and maintain data lineage.
7. How do you validate record counts?
Answer
Record count validation compares the number of records in the source and target after applying the required transformation filters.
The tester verifies:
- Successful extraction.
- Complete loading.
- No missing records.
- No unexpected duplicates.
Any mismatch should be investigated using ETL logs, rejected records, and transformation rules.
8. What is data reconciliation?
Answer
Data reconciliation is the process of matching totals and record counts between the source and target systems.
Typical reconciliation activities include:
- Record count comparison.
- Aggregate total comparison.
- Financial amount validation.
- Business metric verification.
Successful reconciliation confirms that the ETL process has preserved data integrity.
9. What is data warehouse testing?
Answer
Data warehouse testing involves validating:
- Database schemas.
- Fact tables.
- Dimension tables.
- Reports and dashboards.
Its objective is to ensure that the data warehouse accurately supports business reporting and analytics.
10. What is a fact table?
Answer
A fact table stores measurable business data, such as:
- Sales.
- Revenue.
- Transactions.
- Quantity.
- Profit.
Fact tables typically contain foreign keys that reference dimension tables.
11. What is a dimension table?
Answer
A dimension table stores descriptive business attributes, such as:
- Customer.
- Product.
- Region.
- Date.
- Employee.
Dimension tables provide context for the numerical data stored in fact tables.
12. Difference between ETL testing and database testing?
Answer
Although both use SQL, they have different objectives.
| ETL Testing | Database Testing |
| Focuses on data movement | Focuses on database structure |
| Validates transformations | Validates constraints and schema |
| Ensures successful loading | Tests stored procedures and triggers |
| Verifies reporting accuracy | Verifies database integrity |
In summary, ETL testing focuses on data movement and transformation, while database testing focuses on the database itself.
13. What is full load vs incremental load?
Answer
Full Load
- Reloads the entire dataset.
- Used during initial loads or complete refreshes.
Incremental Load
- Loads only new or modified (delta) records.
- Improves performance.
- Reduces ETL processing time.
Incremental loading is commonly implemented using Change Data Capture (CDC).
14. What is reject data testing?
Answer
Reject data testing validates records that fail ETL validation rules.
The tester verifies:
- Rejected records.
- Error reason codes.
- Error tables.
- Reject counts.
- Reprocessing capability.
This ensures invalid records are properly handled without affecting valid data.
15. What is data quality testing?
Answer
Data quality testing verifies that data meets defined quality standards.
Typical validations include:
- Accuracy.
- Consistency.
- Completeness.
- Uniqueness.
- Validity.
Maintaining high data quality ensures reliable reporting and analytics.
Intermediate Scenario-Based Questions (16–35)
These questions evaluate your ability to troubleshoot practical ETL issues commonly encountered in enterprise projects.
16. Scenario: Source has 1 million rows, target has 980,000. What do you check?
Answer
Investigate:
- Transformation filters.
- Rejected records.
- Duplicate removal logic.
- ETL execution logs.
- Incremental load conditions.
- Source-to-Target (S2T) mappings.
The objective is to identify why approximately 20,000 records were not loaded.
17. How do you test NULL handling?
Answer
Validate:
- Mandatory fields.
- Default values.
- NULL replacement logic.
- Lookup failures.
- Transformation expressions.
Unexpected NULL values may indicate transformation or mapping issues.
18. What is SCD Type 1?
Answer
SCD Type 1 overwrites existing dimension data without maintaining historical records.
Only the latest value is retained.
19. What is SCD Type 2?
Answer
SCD Type 2 preserves historical information by creating a new row whenever a tracked attribute changes.
It typically uses:
- Effective dates.
- Expiry dates.
- Current flag.
20. Scenario: Customer address changes. What do you test?
Answer
Validate that:
- A new SCD Type 2 record is created.
- Historical records remain unchanged.
- Effective dates are updated correctly.
- Current flag values are maintained properly.
21. What is surrogate key testing?
Answer
Verify that surrogate keys are:
- Unique.
- Non-null.
- Properly generated.
- Correctly referenced by fact tables.
22. What is a late-arriving dimension scenario?
Answer
A late-arriving dimension occurs when a fact record arrives before its corresponding dimension record.
The tester validates:
- Dummy or unknown key handling.
- Temporary surrogate keys.
- Subsequent dimension updates.
23. How do you test aggregation logic?
Answer
Compare aggregated source data with aggregated target data using functions such as:
- SUM()
- COUNT()
- AVG()
This ensures reporting totals remain accurate.
24. Scenario: Duplicate records in the target. Why?
Answer
Common causes include:
- Incorrect joins.
- Missing deduplication logic.
- Duplicate source processing.
- Incorrect merge conditions.
25. What is hashing used for?
Answer
Hashing is used for:
- Change detection.
- Deduplication.
- Data reconciliation.
- Efficient comparison of large datasets.
26. How do you test CDC (Change Data Capture)?
Answer
Validate that:
- Only changed records are processed.
- New inserts are captured.
- Updates are detected.
- Deleted records are handled according to business rules.
27. Scenario: Negative amount in a financial report. What do you test?
Answer
Verify:
- Business rule implementation.
- Source transactions.
- Refund or reversal processing.
- Transformation calculations.
Determine whether the negative amount is expected or indicates a defect.
28. What is referential integrity testing?
Answer
Referential integrity testing verifies that every foreign key in the fact table has a matching key in the corresponding dimension table.
This prevents orphan records and ensures consistent reporting.
29. Scenario: Data type mismatch error. What do you test?
Answer
Validate:
- Casting rules.
- Data type conversions.
- Numeric precision.
- Character length.
- Truncation handling.
30. How do you validate date transformations?
Answer
Verify:
- Time zone conversions.
- Business calendar rules.
- Timestamp consistency.
31. What is soft delete testing?
Answer
Soft delete testing verifies that deleted records are marked using a delete_flag or status indicator instead of being physically removed.
This preserves historical information.
32. Scenario: Re-run ETL job causes duplicates. What failed?
Answer
The ETL restartability logic has likely failed.
Verify:
- Idempotent processing.
- Merge strategy.
- Delete-and-reload logic.
- Checkpoint recovery.
33. How do you test data masking?
Answer
Validate that Personally Identifiable Information (PII) fields are properly obfuscated or masked.
Common fields include:
- Customer names.
- Email addresses.
- Phone numbers.
- Account numbers.
34. What is schema drift?
Answer
Schema drift occurs when the source schema changes unexpectedly.
Examples include:
- Added columns.
- Removed columns.
- Renamed columns.
- Data type changes.
ETL testing ensures that the ETL process adapts correctly to these changes.
35. How do you test BI reports?
Answer
Compare:
- Report totals.
- Dashboard metrics.
- Data warehouse tables.
- Business calculations.
This ensures that BI reports accurately represent the warehouse data.
Advanced & EY Real-Time Scenarios (36–55)
These questions evaluate your ability to troubleshoot complex ETL issues commonly encountered in enterprise consulting projects.
36. Scenario: ETL job misses SLA. What do you test?
Answer
Analyze:
- Query execution plans.
- Index usage.
- Table partitions.
- SQL optimization.
- Resource utilization.
The objective is to identify performance bottlenecks and restore expected execution times.
37. How do you test performance tuning?
Answer
Validate:
- Join strategy.
- Parallel processing.
- Index utilization.
- Query execution plans.
- Partition pruning.
38. Scenario: Many-to-many join inflates data. What test?
Answer
Validate:
- Join cardinality.
- Business key relationships.
- Duplicate generation.
- Join conditions.
39. How do you test window functions?
Answer
Compare ranked results with business rules.
Common functions include:
- ROW_NUMBER()
- RANK()
- DENSE_RANK()
These are frequently used for deduplication and SCD processing.
40. Scenario: Late data impacts aggregates. What do you do?
Answer
Recalculate the impacted partitions and refresh summary tables to ensure reports reflect the newly received data.
41. How do you test audit and control tables?
Answer
Validate:
- Batch status.
- Row counts.
- Batch identifiers.
- Execution timestamps.
- Error counts.
42. Scenario: Regulatory report mismatch. What do you check first?
Answer
Review:
- Source-to-Target (S2T) mapping.
- Aggregation logic.
- Business transformation rules.
- Data reconciliation.
These areas commonly affect regulatory reporting accuracy.
43. How do you test ETL rollback?
Answer
Ensure that:
- Partial loads are reverted.
- Transactions are rolled back.
- Previous data remains intact.
- Recovery procedures execute successfully.
44. Scenario: Parallel jobs cause locking. What test?
Answer
Validate:
- Concurrency handling.
- Transaction isolation.
- Lock management.
- Deadlock prevention.
- Data consistency.
45. How do you test historical reloads?
Answer
Verify:
- Duplicate prevention.
- Historical version preservation.
- SCD handling.
- Record counts.
- Effective dates.
46. Scenario: Source file arrives late. What do you test?
Answer
Validate:
- Job dependencies.
- Scheduling logic.
- Rerun capability.
- Downstream processing.
- Recovery procedures.
47. How do you test checksum/hash totals?
Answer
Generate and compare checksum or hash totals between the source and target datasets.
This helps confirm:
- Data completeness.
- Data consistency.
- No unexpected changes during processing.
48. Scenario: Currency conversion mismatch. What do you validate?
Answer
Verify:
- Exchange rate application.
- Conversion formulas.
- Effective exchange dates.
- Business calculation rules.
49. How do you test cloud DW ETL?
Answer
Validate:
- Cost optimization.
- Scalability.
- Performance.
- Resource utilization.
- Auto-scaling behavior.
50. Scenario: NULLs after LEFT JOIN. Why?
Answer
Unexpected NULL values may occur because matching dimension records are missing.
The tester should verify:
- Dimension data availability.
- Join conditions.
- Lookup mappings.
51. How do you test archival logic?
Answer
Validate:
- Data movement to history tables.
- Archive completeness.
- Record counts.
- Historical accessibility.
- Data retention rules.
52. Scenario: Incorrect SCD expiry date. What failed?
Answer
The effective date logic or SCD Type 2 implementation is likely incorrect.
Verify:
- Effective dates.
- Expiry dates.
- Current flag updates.
- Version management.
53. How do you validate metadata tables?
Answer
Validate:
- Batch IDs.
- Record counts.
- Execution timestamps.
- Job status.
- Error counts.
Metadata tables provide operational tracking for ETL processes.
54. Scenario: Unexpected data spike. What test?
Answer
Investigate:
- Source data anomalies.
- Duplicate processing.
- Incremental load logic.
- Business rule changes.
- Aggregation accuracy.
The goal is to determine whether the spike reflects legitimate business activity or an ETL defect.
55. How do you perform end-to-end ETL testing?
Answer
Perform end-to-end reconciliation across the complete ETL pipeline:
- Source → Staging: Verify that staging data matches the extracted source data without transformation.
- Staging → Transformation: Validate business rules, joins, calculations, lookups, and data cleansing.
- Transformation → Data Warehouse: Confirm that fact and dimension tables are loaded correctly with accurate keys, audit fields, and record counts.
- Data Warehouse → Reports: Reconcile report totals, KPIs, and dashboards with the underlying data warehouse tables.
Successful end-to-end ETL testing ensures that data remains accurate, complete, consistent, and traceable from the original source systems through to the final business and regulatory reports.
Real SQL Query Examples for ETL Validation
SQL is one of the most important skills for ETL testers because it is used to validate data extraction, transformation, loading, reconciliation, and performance. The following SQL examples demonstrate common validations performed in enterprise ETL and Data Warehouse (DW) projects.
Sample Tables
The following sample tables represent a simple ETL workflow.
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 – Date on which the order was placed.
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 – Date when the record was loaded into the target.
1. Record Count Validation
Record count validation confirms that the expected number of records has been successfully loaded into the target.
Source Query
SELECT COUNT(*)
FROM src_orders;
Target Query
SELECT COUNT(*)
FROM fact_sales;
Purpose
This validation helps ensure that:
- All source records are extracted successfully.
- All expected records are loaded into the target.
- No records are lost during ETL processing.
- No unexpected duplicate records are created.
If the source and target counts differ, the ETL tester should investigate transformation filters, rejected records, incremental load logic, and ETL execution logs.
2. JOIN Validation
JOIN validation ensures that every fact record has a corresponding dimension record.
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 validates:
- Referential integrity.
- Successful dimension lookups.
- Fact-to-dimension relationships.
- Correct surrogate key mapping.
If the query returns a value greater than zero, it indicates missing dimension records or lookup failures that require investigation.
3. GROUP BY Aggregation Validation
Aggregation validation compares summarized values between the source and target systems.
SELECT cust_id,
SUM(amount)
FROM src_orders
GROUP BY cust_id;
Purpose
This validation ensures that:
- Customer-level totals remain accurate.
- Business calculations are preserved.
- Revenue and financial totals are correct.
- Reporting data matches the source.
Aggregation validation is commonly performed in finance, banking, insurance, and retail projects.
4. Window Function – Deduplication
Window functions are widely used in ETL testing to identify duplicate records and retain the required version.
SELECT *
FROM
(
SELECT order_id,
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 latest record for each order_id.
- Validate deduplication logic.
- Support incremental load processing.
The ROW_NUMBER() function assigns a sequential number to each record within the same order_id, allowing only the latest record to be selected.
5. Performance Tuning Validation
Performance validation helps ensure that SQL queries execute efficiently on large datasets.
EXPLAIN
SELECT *
FROM fact_sales
WHERE load_dt >= CURRENT_DATE – 1;
Purpose
This query is used to analyze:
- Query execution plans.
- Index usage.
- Table scans.
- Partition pruning.
- Estimated execution cost.
- Overall SQL performance.
Reviewing the execution plan helps identify opportunities to optimize ETL jobs and improve execution time.
ETL Tools Commonly Asked in EY Interviews
Interviewers often ask about ETL tools to understand your exposure to enterprise data integration platforms. However, the primary focus remains on ETL concepts, SQL proficiency, and data validation techniques rather than tool-specific interfaces.
Informatica
Common interview topics include:
- Mappings.
- Workflows.
- Sessions.
- Transformations.
- Repository management.
Microsoft SQL Server Integration Services (SSIS)
Frequently discussed topics include:
- Control Flow.
- Data Flow.
- SQL-centric ETL development.
- Packages.
- Error handling.
Ab Initio
Interviewers may ask about:
- High-performance ETL graphs.
- Parallel processing.
- Components.
- Data integration.
Pentaho
Common topics include:
- Kettle transformations.
- Jobs.
- Data integration.
- Open-source analytics.
Talend
Typical interview discussions cover:
- Cloud ETL.
- Open-source ETL development.
- Job design.
- Data migration.
- Integration components.
Interview Tip:
In ETL interviews, your understanding of SQL, Source-to-Target (S2T) mappings, data validation, and business logic is generally evaluated more heavily than your familiarity with a specific ETL tool.
ETL Defect Examples + Test Case Sample
Understanding real-world ETL defects helps testers identify root causes, validate fixes, and prevent production issues.
Defect Example
Issue
Duplicate fact records appear in the financial report.
Root Cause
Incorrect join logic.
Fix
- Correct the join keys.
- Add deduplication logic.
- Validate business key uniqueness.
- Re-run the ETL process.
- Perform reconciliation to confirm that duplicate records have been eliminated.
Sample Test Case
Test Case
Validate SCD Type 2 update
Objective
Verify that historical changes are preserved correctly using Slowly Changing Dimension Type 2 (SCD2).
Validation Steps
- Load the initial customer record into the dimension table.
- Update one or more customer attributes in the source.
- Execute the ETL process.
- Verify that a new dimension record is inserted.
- Confirm that the previous record is expired.
- Validate effective and expiry dates.
- Verify the current_flag value.
Expected Result
- The old record is expired.
- A new record is inserted with current_flag = ‘Y’.
- Historical data is preserved.
- Effective and expiry dates are maintained correctly.
- A new surrogate key is generated for the updated record.
EY ETL Testing Quick Revision Sheet
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.
- Verify referential integrity.
Mapping Validation
- Check Source-to-Target (S2T) mappings line by line.
- Validate transformation rules.
- Verify lookup logic.
- Confirm filter conditions.
Data Warehouse Concepts
- Test SCD Type 1.
- Test SCD Type 2.
- Validate audit fields.
- Verify hashing and checksum validation.
- Validate surrogate keys.
SQL Techniques
- Use JOIN for data comparison.
- Use GROUP BY for aggregation validation.
- Use window functions for deduplication.
- Analyze execution plans using EXPLAIN.
End-to-End Validation
Always reconcile:
- Source data.
- Target data.
- Business reports.
This end-to-end reconciliation ensures that the ETL pipeline delivers accurate, complete, and reliable data for reporting and analytics.
Frequently Asked Questions (FAQs)
Q1. What are EY ETL testing interview questions?
Answer
EY ETL testing interview questions typically focus on real-time enterprise scenarios involving:
- Data validation.
- Source-to-Target (S2T) mapping verification.
- Data reconciliation.
- SQL-based validation.
- Performance optimization.
- Regulatory-grade accuracy.
- Audit and control validations.
Interviewers often assess your ability to troubleshoot production issues while ensuring that financial and regulatory reports are accurate and compliant with business requirements.
Q2. Which SQL is important for EY ETL roles?
Answer
The most important SQL concepts for ETL testing include:
- JOINs for comparing source and target data.
- GROUP BY for aggregation validation.
- Window functions such as ROW_NUMBER(), RANK(), and DENSE_RANK() for deduplication and ranking.
- Aggregate functions like SUM(), COUNT(), AVG(), MIN(), and MAX().
- Performance tuning queries using EXPLAIN or EXPLAIN ANALYZE.
Strong SQL skills enable ETL testers to validate transformations, identify data quality issues, reconcile large datasets, and optimize ETL performance.
Q3. How do you test SCD2 in real projects?
Answer
Testing an SCD Type 2 implementation involves verifying that historical versions of dimension records are maintained correctly while identifying the current active record.
Key validations include:
- History rows are preserved rather than overwritten.
- A new record is inserted whenever a tracked attribute changes.
- effective_from and effective_to dates are populated correctly.
- The latest record is marked using current_flag = ‘Y’.
- Previous records are expired appropriately.
- Surrogate keys are generated for each new version.
A successful SCD Type 2 validation confirms that historical data is accurately preserved while ensuring that the latest business information is available for reporting and analytics.