Oracle新手咨询：ETL测试中数据完整性检查、边界值验证及示例查询

Hey there, Oracle newbie! Let me walk you through how to perform ETL testing checks for your T1 (source) and T2 (target) tables—specifically focusing on data load integrity and boundary value validation. I’ve included practical, reusable Oracle SQL queries you can adapt to your actual schema.

These checks ensure that all expected data from T1 made it to T2 without missing records, duplicates, or mismatches.

1. Row Count Match Validation #

First, confirm that the total number of rows in T2 matches (or aligns with) T1. If your ETL applies filters (like excluding deleted records), adjust the queries accordingly.

Basic Row Count Comparison: #

-- Get row count from source table T1
SELECT 'T1 Source' AS table_name, COUNT(*) AS row_count FROM T1;

-- Get row count from target table T2
SELECT 'T2 Target' AS table_name, COUNT(*) AS row_count FROM T2;

With Filter Logic (if ETL excludes certain rows): #

If your ETL skips rows where is_active = 'N' in T1, validate the filtered count matches:

-- Filtered row count from T1
SELECT 'T1 Filtered' AS table_name, COUNT(*) AS row_count 
FROM T1 
WHERE is_active = 'Y';

-- Compare to T2's total rows
SELECT 'T2 Target' AS table_name, COUNT(*) AS row_count FROM T2;

2. Identify Missing Records #

Find rows that exist in T1 but didn’t load into T2. Use a unique key (e.g., id) to match records.

Using LEFT JOIN: #

SELECT t1.id, t1.column1, t1.column2
FROM T1 t1
LEFT JOIN T2 t2 ON t1.id = t2.id
WHERE t2.id IS NULL;

This returns all records from T1 that have no matching entry in T2.

Using MINUS (Alternative): #

SELECT id, column1, column2 FROM T1
MINUS
SELECT id, column1, column2 FROM T2;

Note: MINUS removes duplicates and compares all selected columns, so use it if you want to check full record matches.

3. Detect Duplicate Records in Target #

Ensure T2 doesn’t have duplicate entries (which would break integrity):

SELECT id, COUNT(*) AS duplicate_count
FROM T2
GROUP BY id
HAVING COUNT(*) > 1;

Replace id with your table’s unique key. If duplicates exist, you’ll see how many times each key repeats.

Boundary values are the extreme ends of your data (e.g., largest/smallest numbers, earliest/latest dates, longest/shortest strings). These are often where ETL processes fail, so validating them is critical.

1. Numeric Column Boundaries #

Validate that the minimum and maximum values from T1 are correctly loaded into T2.

Check Min/Max Match: #

-- Source min/max for a numeric column (e.g., salary)
SELECT 
  'T1' AS table_name,
  MIN(salary) AS min_salary,
  MAX(salary) AS max_salary
FROM T1;

-- Target min/max comparison
SELECT 
  'T2' AS table_name,
  MIN(salary) AS min_salary,
  MAX(salary) AS max_salary
FROM T2;

Verify Extreme Values Exist in Target: #

To ensure the exact boundary records made it to T2:

-- Get the record with the highest salary from T1
SELECT * FROM T1 WHERE salary = (SELECT MAX(salary) FROM T1);

-- Check if it exists in T2
SELECT * FROM T2 WHERE salary = (SELECT MAX(salary) FROM T1);

2. Date/Time Boundaries #

Check that the earliest and latest dates from T1 are present in T2.

Min/Max Date Comparison: #

-- Source date boundaries
SELECT
  'T1' AS table_name,
  MIN(transaction_date) AS earliest_transaction,
  MAX(transaction_date) AS latest_transaction
FROM T1;

-- Target date boundaries
SELECT
  'T2' AS table_name,
  MIN(transaction_date) AS earliest_transaction,
  MAX(transaction_date) AS latest_transaction
FROM T2;

Validate Specific Boundary Records: #

-- Check if the earliest transaction from T1 exists in T2
SELECT t2.*
FROM T2 t2
JOIN (SELECT MIN(transaction_date) AS min_date FROM T1) t1_min
ON t2.transaction_date = t1_min.min_date;

3. String Length Boundaries #

For string columns (e.g., customer_name), verify that the shortest and longest values from T1 are correctly loaded into T2.

Check Min/Max String Lengths: #

-- Source string length boundaries
SELECT
  'T1' AS table_name,
  MIN(LENGTH(customer_name)) AS shortest_name_length,
  MAX(LENGTH(customer_name)) AS longest_name_length
FROM T1;

-- Target string length boundaries
SELECT
  'T2' AS table_name,
  MIN(LENGTH(customer_name)) AS shortest_name_length,
  MAX(LENGTH(customer_name)) AS longest_name_length
FROM T2;

Verify the Actual Longest/Shortest Strings: #

-- Get the longest name from T1 and check if it's in T2
SELECT t2.*
FROM T2 t2
JOIN (SELECT customer_name FROM T1 WHERE LENGTH(customer_name) = (SELECT MAX(LENGTH(customer_name)) FROM T1)) t1_long
ON t2.customer_name = t1_long.customer_name;

Pro tip: If your tables have large datasets, add ROWNUM <= 10 to boundary record queries to avoid returning too many rows (in case multiple records share the same extreme value).

内容的提问来源于stack exchange，提问作者lifeofpy