Scenarios for ETL testing

Scenarios in ETL Testing and SQL Examples 1. Validating Row Counts: o Verify that the number of records matches between source and target tables. SELECT COUNT(*) AS source_count FROM source_table; SELECT COUNT(*) AS target_count FROM target_table; 2. Verifying Data Completeness: • Ensure all records in the source exist in the target. SELECT source.id FROM source_table source LEFT JOIN target_table target ON source.id = target.id WHERE target.id IS NULL; 3. Checking Data Transformation: • Validate transformations like currency conversions or date formats. SELECT source.id, source.salary * 0.85 AS expected_salary, target.salary FROM source_table source JOIN target_table target ON source.id = target.id WHERE target.salary != source.salary * 0.85; 4. Testing Aggregated Data: • Compare sums, averages, and other aggregates. SELECT SUM(sales_amount) AS source_total FROM source_table; SELECT SUM(sales_amount) AS target_total FROM target_table; 5. Validating Data Integrity: • Check for duplicates or null values in critical fields. SELECT id, COUNT(*) FROM target_table GROUP BY id HAVING COUNT(*) > 1; SELECT id FROM target_table WHERE critical_field IS NULL; 6. Handling Complex Joins: • Join multiple tables to validate hierarchical data. SELECT o.order_id, c.customer_name, p.product_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN products p ON o.product_id = p.product_id; 7. Using Subqueries: • Extract intermediate results for validation. SELECT * FROM (SELECT order_id, SUM(order_amount) AS total FROM orders GROUP BY order_id) sub WHERE total > 10000;