ETL Testing Using SQL: Tips and Query Examples

 ✅ ETL Testing Using SQL: Tips and Query Examples

ETL Testing (Extract, Transform, Load Testing) is the process of validating data as it moves from source systems to the data warehouse. SQL is a vital tool in ETL testing because it helps you validate data accuracy, completeness, and integrity at every step of the ETL process.


🧩 What is ETL Testing?

ETL Testing ensures:


Data is correctly extracted from source systems.


Data is properly transformed as per business rules.


Data is accurately loaded into the target system or warehouse.


🔍 Key ETL Testing Activities Using SQL

Test Type Goal SQL Example

Row Count Validation Check source vs target row counts SELECT COUNT(*) FROM table_name;

Data Comparison Check if values match between systems SELECT * FROM source MINUS SELECT * FROM target;

Null Checks Ensure mandatory fields are not NULL SELECT * FROM table WHERE column IS NULL;

Data Type Validation Confirm correct data types in columns SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TABLE';

Duplicate Check Detect duplicate rows SELECT column, COUNT(*) FROM table GROUP BY column HAVING COUNT(*) > 1;

Transformation Rules Verify logic like concatenation or math `SELECT first_name

Referential Integrity Validate foreign key constraints SELECT * FROM child WHERE parent_id NOT IN (SELECT id FROM parent);


🧪 Common SQL Queries for ETL Testing

1. Row Count Match

sql

Copy

Edit

-- Source

SELECT COUNT(*) FROM source_table;


-- Target

SELECT COUNT(*) FROM target_table;

2. Data Validation Between Source and Target

sql

Copy

Edit

-- Find mismatched rows

SELECT * FROM source_table

MINUS

SELECT * FROM target_table;

3. Duplicate Records

sql

Copy

Edit

SELECT id, COUNT(*)

FROM target_table

GROUP BY id

HAVING COUNT(*) > 1;

4. Null Value Check

sql

Copy

Edit

SELECT * FROM target_table

WHERE important_column IS NULL;

5. Check Transformed Columns

For example, a salary increase by 10%:


sql

Copy

Edit

SELECT employee_id, salary, new_salary

FROM transformed_table

WHERE new_salary != salary * 1.10;

💡 ETL Testing Best Practices

✅ Understand business rules before writing SQL.


🧾 Validate metadata: column names, types, lengths, and formats.


📊 Use control tables to compare expected vs actual data loads.


🕵️‍♂️ Log errors and mismatches for debugging.


🔄 Test incremental loads with timestamp or ID-based queries.


💾 Back up data before re-running ETL jobs during testing.


⏱️ Monitor performance of long-running SQL queries.


🧠 Summary

SQL is the backbone of effective ETL testing. By writing smart, targeted queries, you can:


Detect data issues early.


Verify transformations.


Ensure the reliability of your data pipeline.

Learn ETL Testing Training in Hyderabad

Read More

Common ETL Bugs and How to Find Them

How to Perform Data Validation in ETL Testing

Step-by-Step Guide to Writing ETL Test Cases

Why ETL Testing is Crucial in Data Warehousing

Visit Our IHUB Talent Training Institute in Hyderabad

Get Directions 

Comments

Post a Comment

Popular posts from this blog

How to Install and Set Up Selenium in Python (Step-by-Step)

 Here’s your step-by-step guide to installing and setting up Selenium in Python — perfect if you're just getting started with browser automation. 🚀 🐍 Step 1: Install Python If you haven’t already: Download Python: https://www.python.org/downloads/ Make sure to check the box "Add Python to PATH" during installation Verify installation: bash Copy Edit python --version pip --version 📦 Step 2: Install Selenium Library Open your terminal or command prompt and run: bash Copy Edit pip install selenium To verify: bash Copy Edit pip show selenium 🌐 Step 3: Download WebDriver for Your Browser Depending on what browser you want to automate: 🔹 Chrome: Download ChromeDriver: ➡️ https://sites.google.com/chromium.org/driver/ Make sure the driver version matches your installed Chrome version. 🔹 Firefox: Download GeckoDriver: ➡️ https://github.com/mozilla/geckodriver/releases Unzip and place the executable somewhere on your system, like: Windows: C:\WebDrivers\ Mac/Linux: /usr/local...
Read more

Feeling Stuck in Manual Testing? Here’s Why You Should Learn Automation Testing

Feeling Stuck in Manual Testing? Here’s Why You Should Learn Automation Testing Are you feeling like your manual testing career is hitting a ceiling? You’re not alone. Many QA professionals start their careers in manual testing but soon realize the need to level up. If you’re wondering what’s next, automation testing might just be the breakthrough you need. Why Manual Testing Isn’t Enough Anymore Manual testing is essential, especially in exploratory, usability, and ad-hoc testing scenarios. But as applications grow more complex and development cycles become shorter, manual testing can’t keep up. Companies are looking for faster, more efficient testing methods—and that’s where automation comes in. Here are a few signs that you're stuck in manual testing: Repeating the same test cases for every build Working on tight deadlines with limited test coverage Seeing peers move ahead with automation skills Lack of growth in responsibilities or pay scale If any of this sounds familiar, it...
Read more

A Beginner's Guide to ETL Testing: What You Need to Know

A Beginner's Guide to ETL Testing: What You Need to Know Introduction: Understanding the Importance of ETL Testing Have you ever wondered how data gets from one place to another in a way that is both reliable and accurate? What happens when that data is transferred and transformed between systems? The answer lies in the ETL (Extract, Transform, Load) process, a crucial part of data management in many industries. But, how do we ensure that data is being handled correctly in this intricate process? Enter ETL testing. ETL testing is vital for ensuring that your data pipeline is working as expected. But as a beginner, where do you start? What should you focus on? This guide will answer these questions and more, helping you build a solid foundation for mastering ETL testing. Whether you're a data engineer, quality assurance professional, or anyone working with data, this article will provide insights into how ETL testing works, why it's important, and the best practices you need...
Read more