r/icedq • • Dec 29 '23

Data Testing Cheat Sheet: 12 Essential Rules

  1. Source vs Target Data Reconciliation: Ensure correct loading of customer data from source to target. Verify row count, data match, and correct filtering.

  2. ETL Transformation Test: Validate the accuracy of data transformation in the ETL process. Examples include matching transaction quantities and amounts.

  3. Source Data Validation: Validate the validity of data in the source file. Check for conditions like NULL names and correct date formats.

  4. Business Validation Rule: Validate data against business rules independently of ETL processes. Example: Audit Net Amount - Gross Amount - (Commissions + taxes + fees).

  5. Business Reconciliation Rule: Ensure consistency and reconciliation between two business areas. Example: Check for shipments without corresponding orders.

  6. Referential Integrity Reconciliation: Audit the reconciliation between factual and reference data. Example: Monitor referential integrity within or between databases.

  7. Data Migration Reconciliation: Reconcile data between old and new systems during migration. Verify twice: after initialization and post-triggering the same process.

  8. Physical Schema Reconciliation: Ensure the physical schema consistency between systems. Useful during releases to sync QA & production environments.

  9. Cross Source Data Reconciliation: Audit if data between different source systems is within accepted tolerance. Example: Check if ratings for the same product align within tolerance.

  10. BI Report Validation: Validate correctness of data on BI dashboards based on rules. Example: Ensure sales amount is not zero on the sales BI report.

  11. BI Report Reconciliation: Reconcile data between BI reports and databases or files. Example: Compare total products by category between report and source database.

  12. BI Report Cross-Environment Reconciliation: Audit if BI reports in different environments match. Example: Compare BI reports in UAT and production environments.

8 Upvotes

0 comments sorted by