What Is ETL Testing? (Definition + Real-World Example)
ETL Testing is the process of validating data as it moves through the Extract → Transform → Load (ETL) stages to ensure accuracy, completeness, consistency, and performance in a Data Warehouse (DW).
The primary objective of ETL testing is to verify that data is extracted correctly from source systems, transformed according to business rules, and loaded into the target data warehouse without data loss, duplication, corruption, or inconsistencies.
ETL testing helps ensure that business reports, dashboards, and analytics are based on accurate and reliable data. Since organizations rely on data warehouses for decision-making, validating every stage of the ETL process is essential for maintaining data quality and business confidence.
Real-World Example
Consider a retail Data Warehouse (DW) project.
The ETL process performs the following activities:
- Daily sales data is extracted from POS (Point of Sale) systems.
- The data is transformed by applying discounts, tax rules, and currency conversion.
- The processed data is loaded into fact tables for reporting and analytics.
An ETL tester validates that:
- No data loss occurs during extraction.
- Transformations follow the defined business rules.
- Aggregated sales in reports match the source totals.
These validations ensure that management reports, sales dashboards, and financial analytics accurately represent business transactions.
This makes ETL testing scenario based interview questions highly practical and business-oriented, as interviewers often expect candidates to explain how they would validate real-world ETL processes and troubleshoot production issues.
Data Warehouse Flow: Source → Staging → Transform → Load → Reporting
A typical ETL process consists of several stages that move data from operational systems into the data warehouse for reporting and analytics.
Source
The source layer contains the original business data collected from various operational systems.
Typical data sources include:
- OLTP databases
- Flat files
- APIs
At this stage, ETL testing focuses on ensuring that all required data is successfully extracted from the source systems without omissions or corruption.
Staging
The staging layer is a temporary storage area where raw extracted data is placed before transformation.
Its primary purposes include:
- Landing raw data
- Supporting data validation
- Enabling data cleansing
- Simplifying debugging and reprocessing
Since staging tables contain unprocessed data, they provide an important checkpoint for comparing source data with transformed data.
Transformation
The transformation layer applies business logic to convert raw data into a format suitable for the data warehouse.
Typical transformation activities include:
- Applying business rules
- Performing joins
- Handling Slowly Changing Dimensions (SCD)
- Data cleansing
- Currency conversion
- Data standardization
- Aggregations
ETL testers verify that all transformations are implemented correctly according to 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 stage, ETL testers validate:
- Successful data loading
- Record counts
- Surrogate key generation
- Referential integrity
- Audit fields
These validations ensure that the warehouse contains complete and accurate business data.
Reporting
The reporting layer is the final stage where business users access curated data through reporting and analytics tools.
Typical reporting outputs include:
- BI dashboards
- Business reports
- KPIs
- Analytics
ETL testing ensures that these reports accurately reflect the data stored in the warehouse and support reliable business decision-making.
ETL Testing Scenario Based Interview Questions & Answers
(Basic → Advanced | Real-Time Focus)
Scenario-based interview questions assess your ability to apply ETL concepts to real production environments. Instead of testing only theoretical knowledge, interviewers expect candidates to explain how they would identify, analyze, and resolve practical ETL issues.
These questions commonly focus on:
- Record count mismatches
- Data reconciliation
- Transformation validation
- Source-to-Target (S2T) mapping verification
- Duplicate detection
- NULL value handling
- Incremental and full load validation
- Slowly Changing Dimensions (SCD)
- Performance optimization
- ETL job restartability
- Audit field validation
- Real-time production defect handling
Preparing both conceptual knowledge and practical scenarios will help you confidently answer ETL testing interview questions commonly asked in enterprise data warehouse projects.
Basic ETL Testing Scenarios (1–15)
1. How do you validate record count between source and target?
Answer
Record count validation ensures that all expected records are successfully loaded into the target after the ETL process.
To validate record counts:
- Compare the source record count with the target record count.
- Apply the same transformation filters used during the ETL process.
- Verify that no records are missing or duplicated.
- Investigate any mismatches by reviewing ETL logs and rejected records.
This validation confirms that the extraction and loading processes are complete and accurate.
2. What if the source has 1 million records and the target has 990,000 records?
Answer
A difference in record counts indicates that some records were not loaded into the target.
Investigate the following:
- Rejected records.
- Filter conditions.
- Duplicate removal logic.
- Source-to-Target (S2T) mappings.
- ETL execution logs.
- Incremental load conditions.
The objective is to determine why approximately 10,000 records were excluded during processing.
3. How do you test NULL handling in ETL?
Answer
NULL handling validation ensures that mandatory fields do not contain unexpected NULL values.
An ETL tester should:
- Validate mandatory columns.
- Verify default values.
- Check transformation logic.
- Confirm NULL replacement rules.
- Review rejected records caused by NULL values.
This helps maintain data quality and prevents reporting issues.
4. Scenario: Source column is nullable but target is NOT NULL. What do you test?
Answer
In this scenario, verify:
- Default value mapping.
- NULL replacement logic.
- Rejection rules.
- Error logging.
- Business rule implementation.
The target should either receive a valid default value or reject the record according to the defined ETL rules.
5. What is Source-to-Target (S2T) validation?
Answer
Source-to-Target (S2T) validation verifies that every source column is correctly mapped to its corresponding target column.
The validation includes:
- Column mappings.
- Transformation rules.
- Data type conversions.
- Lookup logic.
- Filter conditions.
- Target field values.
The S2T mapping document serves as the primary reference during ETL testing.
6. How do you test data type mismatches?
Answer
Data type validation ensures that source values are correctly converted before loading into the target.
An ETL tester verifies:
- Truncation.
- Rounding.
- Casting rules.
- Numeric precision.
- Character lengths.
- Date format conversions.
This prevents data corruption caused by incorrect data type transformations.
7. Scenario: Duplicate records in the target but not in the source. Why?
Answer
Duplicate records in the target may occur due to:
- Incorrect joins.
- Missing deduplication logic.
- Improper incremental load processing.
- Duplicate source processing.
- Incorrect merge conditions.
The tester should review join logic and deduplication rules to identify the root cause.
8. What is audit field testing?
Answer
Audit field testing validates metadata generated during the ETL process.
Common audit fields include:
- load_date
- batch_id
- created_by
The tester verifies that these fields are correctly populated for every loaded record.
9. How do you test incremental loads?
Answer
Incremental load testing ensures that only new or modified (delta) records are processed.
Validation includes:
- Change Data Capture (CDC) logic.
- Timestamp comparisons.
- Batch IDs.
- Last modified dates.
- Record counts.
The objective is to ensure that unchanged records are not reprocessed.
10. Scenario: Job rerun creates duplicates. How do you test restartability?
Answer
Restartability testing verifies that ETL jobs can safely resume after failure.
Validate:
- Idempotent processing logic.
- Delete-and-reload strategy.
- Merge strategy.
- Duplicate prevention.
- Checkpoint recovery.
The ETL process should not create duplicate records when rerun.
11. What is data reconciliation?
Answer
Data reconciliation compares source and target data to ensure consistency.
Typical reconciliation checks include:
- Record counts.
- Aggregate totals.
- Key business metrics.
- Financial values.
- Transaction counts.
Successful reconciliation confirms that the ETL process has preserved data accuracy.
12. How do you validate staging data?
Answer
The staging layer should contain an exact copy of the extracted source data before any transformations are applied.
The tester verifies:
- Record counts.
- Data values.
- File completeness.
- No transformations.
- No data loss.
Staging validation helps isolate issues before transformation begins.
13. What is reject data testing?
Answer
Reject data testing validates records that fail ETL validation rules.
The tester verifies:
- Rejected rows.
- Error reason codes.
- Error tables.
- Record counts.
- Reprocessing capability.
This ensures invalid records are handled correctly without affecting valid data.
14. Scenario: File delimiter changes unexpectedly. What test would you perform?
Answer
Validate:
- File format.
- Delimiter configuration.
- Schema validation.
- Header structure.
- Column alignment.
- ETL parser behavior.
Early detection of format changes prevents incorrect data loading.
15. How do you validate date transformations?
Answer
Date transformation validation includes checking:
- Date formats.
- Time zone conversions.
- Business calendar rules.
- Leap year handling.
- Timestamp accuracy.
This ensures dates are consistently transformed throughout the ETL process.
Intermediate Scenario-Based Questions (16–35)
16. What is SCD Type 1 scenario testing?
Answer
SCD Type 1 testing validates that existing dimension values are overwritten without preserving historical data.
The tester confirms that:
- Old values are replaced.
- No additional history records are created.
- The latest information is available in the dimension table.
17. What is SCD Type 2 scenario testing?
Answer
SCD Type 2 testing verifies that historical changes are preserved.
Validate:
- New row creation.
- effective_from
- effective_to
- current_flag
- Surrogate key generation.
This ensures historical versions remain available.
18. Scenario: Customer address changes. What do you test?
Answer
Verify that:
- A new SCD Type 2 record is created.
- Historical address information is preserved.
- Effective dates are updated correctly.
- The current record is flagged appropriately.
19. How do you test surrogate keys?
Answer
Validate that surrogate keys are:
- Unique.
- Non-null.
- Properly generated.
- Correctly referenced by fact tables.
Surrogate keys maintain referential integrity within the data warehouse.
20. 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.
- Dimension updates after arrival.
- Fact-to-dimension relationships.
21. Scenario: Fact row has an invalid dimension key. What happens?
Answer
Depending on business rules, the ETL process may:
- Reject the record.
- Map it to an “Unknown” dimension key.
- Store it for later reprocessing.
The tester validates that the configured handling is correctly implemented.
22. How do you test aggregation logic?
Answer
Aggregation validation compares summarized target values with source calculations.
Typical validations include:
- SUM()
- COUNT()
- AVG()
- GROUP BY comparisons
This ensures reports display accurate totals.
23. What is hashing used for in ETL testing?
Answer
Hashing is used for:
- Change detection.
- Deduplication.
- Data reconciliation.
- Large dataset comparison.
Hash values help detect differences efficiently.
24. Scenario: One-to-many join inflates data. How do you test?
Answer
Validate:
- Join cardinality.
- Business key relationships.
- Duplicate rows.
- Join conditions.
Incorrect joins often produce inflated record counts.
25. How do you test data quality rules?
Answer
Common data quality validations include:
- Range checks.
- Domain checks.
- Pattern validation.
- Mandatory field validation.
- Uniqueness checks.
These validations ensure that only high-quality data enters the warehouse.
26. Scenario: Negative sales amount appears. What do you test?
Answer
Validate:
- Business rule implementation.
- Source data.
- Transformation logic.
- Refund or return processing.
- Calculation rules.
This determines whether the negative value is expected or indicates a defect.
27. How do you validate currency conversion?
Answer
Compare converted values with:
- Exchange rate tables.
- Business conversion rules.
- Effective exchange dates.
This ensures accurate financial reporting.
28. What is partition testing?
Answer
Partition testing verifies that data is loaded into the correct database partition based on the partitioning strategy.
This improves query performance and simplifies maintenance.
29. Scenario: Historical data reload. What do you test?
Answer
Validate:
- Duplicate prevention.
- Historical version preservation.
- SCD behavior.
- Record counts.
- Effective dates.
30. How do you test ETL error handling?
Answer
Validate:
- Error alerts.
- ETL logs.
- Reject tables.
- Recovery procedures.
- Rerun capability.
Proper error handling ensures reliable ETL processing.
31. 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.
32. Scenario: Dimension record expires incorrectly. What should you check?
Answer
Verify:
- Effective date logic.
- Expiry date calculation.
- Current flag.
- SCD Type 2 implementation.
33. How do you test soft deletes?
Answer
Validate that records are marked using a delete_flag or status indicator instead of being physically removed.
Historical data should remain available.
34. What is a schema drift scenario?
Answer
Schema drift occurs when the source schema changes unexpectedly.
Validate:
- Added columns.
- Removed columns.
- Renamed columns.
- Data type changes.
- ETL adaptability.
35. How do you validate report data?
Answer
Compare:
- BI reports.
- Dashboard totals.
- Data warehouse tables.
- Aggregated calculations.
This confirms end-to-end reporting accuracy.
Advanced & Real-Time Scenarios (36–55)
These questions assess your ability to troubleshoot complex ETL issues in production environments.
36. Scenario: ETL job misses SLA. How do you test performance?
Answer
Analyze:
- Query execution plans.
- Index usage.
- Table partitions.
- SQL optimization.
- Resource utilization.
The goal is to identify performance bottlenecks.
37. How do you test parallel ETL jobs?
Answer
Validate:
- Record locking.
- Duplicate prevention.
- Deadlocks.
- Parallel execution consistency.
- Resource contention.
38. Scenario: Data skew in big tables. What should you test?
Answer
Validate:
- Data distribution.
- Partition balance.
- Processing time.
- Workload distribution.
Balanced partitions improve ETL performance.
39. How do you validate window functions in ETL?
Answer
Compare ranked results with the expected business rules.
Common functions include:
- ROW_NUMBER()
- RANK()
- DENSE_RANK()
These functions are often used for deduplication and SCD processing.
40. Scenario: Late-arriving facts affect aggregates. What should you test?
Answer
Validate:
- Aggregate recalculation logic.
- Partition refresh.
- Summary table updates.
- Report consistency.
41. How do you test CDC failures?
Answer
Validate:
- Missing delta records.
- CDC timestamps.
- Change tracking.
- Incremental load accuracy.
42. Scenario: Reprocessing rejected data. What should you validate?
Answer
Verify:
- Corrected records.
- Updated counts.
- Successful reload.
- Error resolution.
43. How do you test ETL rollback?
Answer
Ensure that:
- Partial loads are reverted.
- Transactions are rolled back.
- Previous data remains intact.
- Recovery procedures succeed.
44. Scenario: PII columns are exposed in the target. What test would you perform?
Answer
Validate:
- Data masking.
- Encryption.
- Role-based access controls.
- Column-level security.
Sensitive information should be protected according to organizational policies.
45. How do you test multi-source integration?
Answer
Validate:
- Source precedence rules.
- Conflict resolution.
- Data consistency.
- Mapping logic.
- Duplicate handling.
46. Scenario: Time zone mismatch. What should you validate?
Answer
Validate:
- Timestamp normalization.
- UTC conversion.
- Date consistency.
- Business time zone rules.
47. How do you test archival logic?
Answer
Verify:
- Data movement to history tables.
- Archive completeness.
- Record counts.
- Historical accessibility.
48. Scenario: Slowly changing fact. How do you test?
Answer
Validate:
- Adjustment records.
- Correction logic.
- Historical accuracy.
- Reporting consistency.
49. How do you test cloud DW ETL?
Answer
Validate:
- Cost optimization.
- Scalability.
- Performance.
- Auto-scaling behavior.
- Resource utilization.
Cloud environments require balancing performance with operational cost.
50. Scenario: Unexpected NULLs after a join. Why?
Answer
Unexpected NULL values may occur because of:
- Outer join behavior.
- Missing lookup records.
- Incorrect join conditions.
- Incomplete source data.
The tester should verify the join logic and lookup mappings.
51. How do you test ETL metadata tables?
Answer
Validate:
- Batch status.
- Load counts.
- Execution timestamps.
- Error counts.
- Job completion status.
Metadata tables provide operational visibility into ETL execution.
52. Scenario: A file arrives late. What should you test?
Answer
Validate:
- Job dependencies.
- Scheduling logic.
- Rerun capability.
- Downstream impact.
- Recovery procedures.
53. How do you validate checksum/hash totals?
Answer
Generate and compare checksum or hash totals for both source and target datasets.
This validation confirms:
- Data completeness.
- Data consistency.
- No unexpected changes during processing.
54. Scenario: Aggregates don’t match reports. What should you test?
Answer
Investigate:
- Data grain mismatches.
- Aggregation logic.
- Report calculations.
- ETL transformations.
- Source-to-report reconciliation.
55. How do you test an end-to-end ETL flow?
Answer
Validate the complete data pipeline by reconciling data across every stage:
- Source → Staging: Ensure the staging data matches the extracted source data.
- Staging → Transformation: Verify that business rules, joins, calculations, and data cleansing are correctly applied.
- Transformation → Data Warehouse: Confirm that fact and dimension tables are loaded accurately with correct keys and audit fields.
- Data Warehouse → BI Reports: Compare report totals, KPIs, and aggregated metrics with the underlying warehouse tables.
End-to-end ETL testing ensures that data remains accurate, complete, and consistent throughout the entire lifecycle—from the source systems to the final business reports and dashboards.
Real SQL Query Examples for ETL Validation
SQL is the primary language used by ETL testers to validate data movement, transformation logic, data quality, and performance. The following examples demonstrate some of the most used SQL queries for ETL validation in real-world Data Warehouse (DW) projects.
Sample Tables
The following sample tables represent different stages of the ETL pipeline.
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 when the order was placed.
Staging Table
stg_orders
Description
The stg_orders table temporarily stores the extracted source data before any transformations are applied.
The staging layer is used for:
- Raw data storage.
- Initial data validation.
- Data cleansing.
- ETL restart and recovery.
- Troubleshooting failed ETL jobs.
Fact Table
fact_sales(
order_id,
cust_sk,
amount,
load_dt
)
Description
- order_id – Order identifier.
- cust_sk – Customer surrogate key.
- amount – Sales amount after transformation.
- load_dt – Date when the record was loaded into the data warehouse.
1. Record Count Validation
Record count validation confirms that all expected records have been successfully loaded from the source to 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 have been extracted.
- All eligible records have been loaded into the target.
- No records are missing.
- No unexpected duplicate records have been 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 verifies 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.
- Dimension key availability.
- Lookup success.
- Fact-to-dimension relationships.
If the query returns any rows, it indicates missing dimension records or failed lookups that need further investigation.
3. GROUP BY Aggregation Check
Aggregation validation compares summarized values between the source and target.
SELECT cust_id,
SUM(amount)
FROM src_orders
GROUP BY cust_id;
Purpose
This validation confirms that:
- Customer totals remain accurate.
- Business calculations are correct.
- Revenue totals are preserved.
- Reporting data matches the source.
Aggregation validation is especially important for financial, banking, insurance, and retail reporting systems.
4. Window Function – Deduplication
Window functions are commonly used to identify duplicate records and retain the required version of each record.
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.
- 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 most recent record to be selected.
5. Performance Tuning Validation
Performance validation ensures that SQL queries execute efficiently against large datasets.
EXPLAIN
SELECT *
FROM fact_sales
WHERE load_dt >= CURRENT_DATE – 1;
Purpose
This query is used to analyze:
- Query execution plan.
- Index utilization.
- Table scans.
- Partition pruning.
- Estimated query cost.
- Overall SQL performance.
Reviewing the execution plan helps identify opportunities to optimize ETL jobs and improve execution time.
ETL Tools Commonly Asked in Interviews
Interviewers often ask about ETL tools to assess your familiarity with industry-standard technologies. However, they primarily evaluate your understanding of ETL concepts, SQL skills, and testing approaches 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.
- Packages.
- SQL-centric ETL development.
- 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:
While knowledge of ETL tools is beneficial, interviewers mainly assess your understanding of ETL concepts, SQL validation techniques, business logic, and problem-solving skills.
ETL Defect Examples + Test Case Sample
Understanding real-world ETL defects helps testers identify root causes, validate fixes, and prevent recurring production issues.
Defect Example
Issue
Duplicate fact records.
Root Cause
Many-to-many join.
Fix
- Correct the join condition.
- Add deduplication logic.
- Validate business keys.
- Re-run ETL processing.
- Perform data reconciliation to verify the correction.
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 an 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 created.
- Confirm that the previous record is expired.
- Validate effective and expiry dates.
- Verify the current_flag value.
Expected Result
- The old record expires.
- A new record is inserted.
- Historical information is preserved.
- The latest record is marked as current_flag = ‘Y’.
- Effective and expiry date ranges are correctly maintained.
SQL Validation
Validate:
- current_flag
- Effective date ranges.
- Expiry date ranges.
- Surrogate key generation.
- Historical versions of the customer record.
ETL Testing Quick Revision Sheet
Use the following checklist for a quick review before ETL testing interviews.
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 thoroughly.
- Validate transformation rules.
- Verify lookup logic.
- Confirm filter conditions.
Data Warehouse Concepts
- Test SCD Type 1.
- Test SCD Type 2.
- Validate audit fields.
- Verify surrogate keys.
- Validate fact and dimension relationships.
SQL Techniques
- Use JOIN for data comparison.
- Use GROUP BY for aggregation validation.
- Use window functions for deduplication.
- Validate execution plans using EXPLAIN.
End-to-End Validation
Always reconcile:
- Source data.
- Target data.
- Business reports.
- Dashboard totals.
This ensures that the entire ETL pipeline—from extraction to reporting—produces accurate, complete, and reliable data.
Frequently Asked Questions (FAQs)
Q1. What are ETL testing scenario-based interview questions?
Answer
ETL testing scenario-based interview questions focus on real-time production issues that testers commonly encounter during ETL validation.
These scenarios typically include:
- Record count mismatches.
- Unexpected NULL values.
- Duplicate records.
- Source-to-Target (S2T) mapping issues.
- Incremental load failures.
- Change Data Capture (CDC) issues.
- Performance bottlenecks.
- Data reconciliation problems.
Interviewers use these questions to assess your ability to analyze problems, apply ETL concepts, and troubleshoot data quality issues in enterprise environments.
Q2. Which SQL is important for ETL testing?
Answer
ETL testers should be proficient in SQL features commonly used for data validation and reconciliation.
The most important SQL concepts 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, detect data quality issues, and verify large datasets efficiently.
Q3. How do you test SCD2 in real projects?
Answer
Testing a Slowly Changing Dimension Type 2 (SCD2) implementation involves verifying that historical changes are preserved while the latest record is correctly maintained.
Key validations include:
- History rows are preserved instead of overwritten.
- New records are created for changed data.
- effective_from and effective_to dates are correctly populated.
- The latest record is marked using the current_flag.
- Previous records are expired appropriately.
- Surrogate keys are generated for new versions.
A successful SCD2 test confirms that the data warehouse accurately maintains historical versions of dimension records while supporting reliable historical reporting.