How to Perform Data Validation in ETL Testing

Great topic! Data validation in ETL testing is crucial to ensure that the data extracted, transformed, and loaded is accurate, complete, and consistent. Here’s a clear, step-by-step guide on how to perform data validation in ETL testing:


How to Perform Data Validation in ETL Testing

1. Understand the Requirements and Data Mapping

Review the ETL design documents and data mapping specifications.


Understand source data formats, target data structures, and business rules.


2. Identify Data Validation Points

Source to Staging Validation: Check that data extracted matches the source.


Transformation Validation: Verify the correctness of business rules and transformation logic.


Loading Validation: Confirm that data loaded into the target matches expected results.


3. Check Data Completeness

Ensure all expected records are loaded.


Validate record counts between source and target.


Check for missing or null values where not allowed.


4. Data Accuracy Validation

Compare source and target data values after transformation.


Validate calculated fields (e.g., totals, averages) are correct.


Use SQL queries or ETL tools to compare row-by-row or aggregated data.


5. Data Consistency Checks

Verify that data formats and types conform to target schema.


Check for referential integrity (foreign key constraints).


Ensure duplicates are handled correctly.


6. Data Transformation Validation

Validate transformation rules applied correctly (e.g., filters, joins, aggregations).


Test conditional transformations and lookups.


Verify data cleansing operations (e.g., trimming, case conversion).


7. Performance Validation

Measure ETL job run time and resource usage.


Verify data load within acceptable timeframes.


8. Automate Validation Where Possible

Use automated scripts and tools (e.g., Informatica, Talend, SQL scripts) to compare datasets.


Use data validation frameworks or frameworks like Apache Griffin for data quality.


9. Document Defects and Anomalies

Log discrepancies and work with developers or data engineers for fixes.


Maintain detailed test reports for audit and compliance.


Example SQL Queries for Data Validation

Count Check:


sql

Copy

Edit

SELECT COUNT(*) FROM source_table;

SELECT COUNT(*) FROM target_table;

Data Match Check:


sql

Copy

Edit

SELECT src.id, src.value, tgt.value 

FROM source_table src 

LEFT JOIN target_table tgt ON src.id = tgt.id 

WHERE src.value <> tgt.value OR tgt.value IS NULL;

Null Values Check:


sql

Copy

Edit

SELECT COUNT(*) FROM target_table WHERE important_column IS NULL;

Tools Commonly Used for ETL Data Validation

SQL Query Editors (e.g., SQL Server Management Studio, Oracle SQL Developer)


ETL Tools’ built-in validation (Informatica, Talend)


Data Comparison Tools (e.g., QuerySurge, Datagaps)


Python scripts or frameworks for custom validation

Learn ETL Testing Training in Hyderabad

Read More

Step-by-Step Guide to Writing ETL Test Cases

Why ETL Testing is Crucial in Data Warehousing

How to Get Started with ETL Testing: Tools, Skills, and Roadmap

Visit Our IHUB Talent Training Institute in Hyderabad

Get Directions 


Comments

Post a Comment

Popular posts from this blog

Handling Frames and Iframes Using Playwright

Handling Frames and Iframes Using Playwright When automating modern web applications, you’ll often come across frames or iframes—HTML elements used to embed another HTML document within the current page. If you're using Playwright for browser automation, knowing how to handle frames properly is crucial for interacting with elements inside them. In this blog, we’ll walk through how to identify, access, and interact with frames and iframes using Playwright with code examples in JavaScript and Python. 🧾 What Are Frames and Iframes? <frame>: Rarely used now; part of the outdated <frameset> HTML structure. <iframe> (inline frame): Embeds another HTML page inside the current one. Common for third-party content like ads, maps, or payment gateways. 🎯 Why Frames Matter in Automation Elements inside an iframe are not part of the main DOM, so trying to interact with them directly will result in errors unless you switch the context to the frame. 🔍 Locating an Iframe in Pla...
Read more

Cybersecurity Internship Opportunities in Hyderabad for Freshers

 🚀 1. AICTE Cyber Security Internship (Jul–Aug 2025) Organizer: AICTE via its internship portal—free, fully online Duration: ~4–6 weeks starting July 2025 Perks: Hands-on labs (Kali, Nmap, Wireshark), capstone project, AICTE certificate Cost/Stipend: Free, no stipend Apply by: July 15, 2025 via AICTE portal  en.wikipedia.org +15 govtinternship.com +15 joincomputerit.blogspot.com +15 Perfect for freshers seeking practical exposure with certified recognition. 🏢 2. Gainsight – Application Security Intern (On-site) Role: SOC monitoring, SIEM dashboards, incident response Location: Hyderabad office Requirements: Bachelor’s in CS/related field; enthusiastic freshers welcome Stipend: Not specified  hyderabadtechbytes.blogspot.com placementdrive.in Ideal if you want corporate cybersecurity experience using enterprise tools. 🌱 3. Internshala – Cyber Security Internships Listings: Multiple internship openings across Hyderabad (both remote and on-site) Fields: Ethical hacking, se...
Read more

Tosca for API Testing: A Step-by-Step Tutorial

Tosca for API Testing: A Step-by-Step Tutorial Tricentis Tosca is a powerful and user-friendly test automation tool that supports a wide range of testing types — including API testing. With Tosca, you can easily design, automate, and execute API tests without writing any code. It supports REST, SOAP, and other service types, making it a great choice for end-to-end API validation. 🧰 What is Tosca API Testing? Tosca’s API testing allows testers to: Send requests (GET, POST, PUT, DELETE, etc.) Validate responses (status codes, body, headers) Chain requests and share data between them Perform security, load, and functional tests on APIs ✅ Step-by-Step Guide: Tosca API Testing 🔹 Step 1: Open Tosca and Create a Workspace Launch Tosca Commander Create a new workspace (or open an existing one) Choose the workspace type as Multi-user or Single-user based on your setup 🔹 Step 2: Add a New API Test Case Navigate to the Modules section. Right-click and choose Scan > API Scan. The API Scan wi...
Read more