These ETL testing notes condense everything you need for revision onto one page: concepts, data warehouse basics, testing types, SQL checks, the test process and interview tips. Each section links to a detailed guide if you need more.
How to Save These Notes as PDF
Press Ctrl + P (Windows) or Cmd + P (Mac), choose "Save as PDF" as the printer, and save. It is free and always up to date.
1. ETL Basics
- ETL = Extract (read from sources) → Transform (clean, apply rules) → Load (write to target).
- ELT = load raw first, transform inside the warehouse. ETL vs ELT.
- ETL testing = proving data is complete, correct and consistent after each step. Details.
- Mapping document = source column → target column + rule. Test cases come from it.
2. Data Warehouse
- OLTP runs the business; OLAP/warehouse analyses it.
- Layers: source → staging → warehouse → data marts → reports.
- Fact = measures; dimension = context. Fact vs dimension.
- Star (denormalised dims) vs snowflake (normalised dims). Compare.
- Surrogate key = warehouse ID; business key = source ID.
- Full load vs incremental load (watermark/CDC). All concepts.
3. ETL Testing Types
| Type | Checks |
|---|---|
| Metadata | Columns, data types, lengths, constraints |
| Completeness | Counts, missing and extra records |
| Transformation | Each mapping rule produces the expected value |
| Data quality | Nulls, duplicates, formats, ranges |
| Referential integrity | No orphan foreign keys |
| Reconciliation | Totals match source |
| Incremental | Only changed rows; re-run safe |
| Regression | Old checks still pass after changes |
| Performance | Load finishes within the window |
| Report | Report numbers match the warehouse |
4. SQL Checks to Remember
-- Count SELECT COUNT(*) FROM tgt; -- Missing records (MINUS in Oracle) SELECT id FROM src EXCEPT SELECT id FROM tgt; -- Duplicates SELECT id, COUNT(*) FROM tgt GROUP BY id HAVING COUNT(*) > 1; -- Nulls in mandatory column SELECT COUNT(*) FROM tgt WHERE col IS NULL; -- Orphans SELECT COUNT(*) FROM fact f LEFT JOIN dim d ON d.k = f.k WHERE d.k IS NULL; -- Totals SELECT SUM(amount) FROM src; SELECT SUM(amount) FROM tgt;
15 more: ETL testing SQL queries.
5. Slowly Changing Dimensions
- Type 0: never changes. Type 1: overwrite. Type 2: new row with start/end date and current flag. Type 3: previous-value column.
- Test Type 2: one current row per key, no date overlaps or gaps, old row expired correctly. SCD testing.
6. ETL Test Process
Requirement analysis → mapping review → test plan → test design → test data & environment → execution → defects & retest → regression & sign-off. Process · test plan template · checklist.
7. Common Defects
- Missing or duplicate records; wrong join or filter.
- Untrimmed strings, wrong case, wrong date format.
- Truncated values or wrong data type.
- Wrong default for nulls; wrong lookup value.
- SCD history broken; incremental load misses updates.
Report with: query, expected vs actual, sample rows, load ID, severity.
8. Tools
SQL clients (DBeaver, SSMS, SQL Developer), ETL tools (Informatica, SSIS, ADF), test tools (QuerySurge, dbt tests, Great Expectations), Python (pandas, pytest), Jira. Testing tools · ETL tools list.
9. Interview Tips
- SQL decides most rounds: write queries by hand.
- Prepare a 2-minute project explanation: practice projects.
- Test yourself: free mock test.
- Revise: questions for freshers.
More guides for Indian students: ETL full form · ETL testing with example · ETL testing jobs in India · ETL testing course in India · ETL training in Hyderabad, Bangalore & Pune · best ETL testing course on Udemy · ETL testing roadmap · ETL tester vs ETL developer · interview questions for freshers · free ETL testing mock test · SQL for testers · QA to data engineer · ETL testing kya hai (Hindi) · ETL testing projects for practice · work from home ETL jobs
Data warehouse basics: data warehouse concepts · star vs snowflake schema · fact and dimension tables · SCD types · data reconciliation · data warehouse testing · data warehouse interview questions
Frequently Asked Questions
Where can I get ETL testing notes for free?
This page has complete ETL testing revision notes for free. You can save it as a PDF with your browser's print option and choose "Save as PDF".
Is there an ETL testing notes PDF free download?
Use your browser's print function on this page and choose "Save as PDF" to get a free, up-to-date PDF of these notes.
What topics should ETL testing notes cover?
ETL basics, data warehouse concepts, ETL testing types, SQL checks, slowly changing dimensions, the test process, common defects, tools and interview tips.
Are notes enough to learn ETL testing?
Notes are good for revision. To learn, you need hands-on SQL practice, a practice project and structured lessons.
How do I revise ETL testing before an interview?
Read these notes, rewrite the SQL checks from memory, explain your project out loud and take a mock test.
Asim Noaman Lodhi
Certified Google Partner · QA Consultant · 12+ Years IT
QA consultant specializing in ETL testing and data quality. Trained 913+ students to transition into data testing roles through hands-on, real-world instruction.