Data Quality & Validation Rules
Relevant source files
The following files were used as context for generating this wiki page:
Data Quality and Validation Rules within the flext-dbt-oracle-wms project establish an enterprise-grade framework for ensuring the integrity, consistency, and accuracy of Oracle WMS data as it moves through the DBT transformation pipeline. The system enforces rules at multiple layers, from initial extraction validation in Python to complex cross-model referential integrity and KPI accuracy checks in SQL.
The scope of these rules covers staging quality (null checks), business logic validation (quantity and timestamp sanity), and cross-model consistency (referential integrity between allocations, orders, and inventory). This multi-tiered approach ensures that only records marked as VALID contribute to downstream analytical and operational marts.
Sources: tests/data_quality/test_cross_model_consistency.sql:4-6, src/flext_dbt_oracle_wms/_utilities/client.py:126-147, dbt_project.yml:81-85
Validation Architecture
The validation logic is distributed across the application client and the DBT transformation layer. The FlextDbtOracleWmsClient performs initial field-level validation during the extraction phase, while DBT tests execute complex relational and business rule audits on the persisted data.
Extraction Layer Validation
Before data is transformed, the Python client validates extracted records against required_fields_per_entity and invokes a validate_business_rules method. If a record is missing critical fields or violates business rules, the extraction pipeline fails or identifies the specific record index and missing fields.
Sources: src/flext_dbt_oracle_wms/_utilities/client.py:126-147
DBT Transformation Layer
Data quality thresholds are defined in the project configuration to monitor completeness, accuracy, and consistency. The project utilizes store_failures to capture records that violate these rules in a dedicated wms_test_failures schema.
Sources: dbt_project.yml:68-70, dbt_project.yml:81-85
The diagram shows the progression of data through initial Python validation checks into the sequential DBT quality gate layers.
Sources: src/flext_dbt_oracle_wms/_utilities/client.py:46-68, tests/data_quality/test_cross_model_consistency.sql:1-5
Core Validation Rules
The project implements specific validation categories to maintain enterprise data standards.
1. Staging Quality & Null Constraints
Critical business identifiers must be present for any record to be processed. This prevents orphaned data or join failures in downstream models.
Sources: tests/staging/test_stg_wms__allocation_quality.sql:5-9
| Field | Requirement | Context |
|---|---|---|
company_code | NOT NULL | Multi-tenant identification |
facility_code | NOT NULL | Physical location identification |
allocation_id | NOT NULL | Primary key for allocation entities |
allocated_quantity | NOT NULL | Required for inventory and KPI tracking |
2. Business Logic Validation
Business rules ensure that numerical values and timestamps are logical and within acceptable operational ranges.
Sources: tests/staging/test_stg_wms__allocation_business_rules.sql:4-35
- Quantity Constraints:
allocated_quantitymust not be negative, andpacked_quantitycannot exceedallocated_quantity. - Temporal Sanity:
picked_timestampcannot be in the future, andmodified_timestampmust be greater than or equal tocreated_timestamp. - Operational Range: The
business_dateis restricted to a window between 2 years in the past and 1 day in the future.
3. Cross-Model Consistency
These tests validate relationships between different WMS entities to ensure referential integrity.
Sources: tests/data_quality/test_cross_model_consistency.sql:1-120
This diagram illustrates the referential integrity requirements validated by cross-model consistency tests.
Sources: tests/data_quality/test_cross_model_consistency.sql:8-100
Key consistency checks include:
- Orphaned Allocations: Ensuring all allocations reference a valid
order_dtl_id. - Inventory Over-allocation: Verifying
allocated_quantitydoes not exceedavailable_quantityin inventory records. - Quantity Mismatch: Comparing the sum of allocated quantities against the original
ordered_quantityin order details. - Temporal Consistency: Ensuring business dates across related records (Allocations vs. Order Details) do not differ by more than 7 days.
KPI & Metric Validation
To ensure the accuracy of enterprise reporting, the project validates calculated metrics in the Marts layer.
Sources: tests/marts/test_opr_wms__allocation_summary_kpis.sql:4-56
- Percentage Sanity:
pack_efficiency_percentmust remain between 0 and 100%. - Aggregated Integrity: The sum of
status_percentage_of_dailyacross all statuses for a facility/date must equal 100% (allowing for 0.1 minor rounding). - Total Reconciliation:
daily_total_allocationsin summary tables must exactly match the calculated sum of individual status-level records. - Dashboard Alignment: KPI totals in the
met_wms__kpi_dashboardmust align with operational totals inopr_wms__allocation_summarywithin a 0.01 tolerance.
Sources: tests/marts/test_opr_wms__allocation_summary_kpis.sql:11-56, tests/data_quality/test_cross_model_consistency.sql:63-80
Configuration & Thresholds
Data quality behavior is controlled via dbt_project.yml variables, allowing for adjustments to strictness and freshness requirements.
| Configuration Variable | Default Value | Description |
|---|---|---|
data_quality_thresholds.completeness | 0.95 | Minimum ratio of non-null required fields |
data_quality_thresholds.accuracy | 0.98 | Threshold for numerical precision validation |
data_quality_thresholds.consistency | 0.90 | Required alignment between related entities |
freshness_warn_after | 12 hours | Warning threshold for data latency |
freshness_error_after | 24 hours | Error threshold for data latency |
| Sources: dbt_project.yml:76-88, dbt_project.yml:90-95 |
Conclusion
The Data Quality and Validation Rules system provides a comprehensive safety net for Oracle WMS data transformations. By combining immediate Python-level field validation with deep SQL-based relational and KPI audits, the project ensures that downstream business decisions are based on technically sound and operationally consistent data. The use of the data_quality_status = 'VALID' flag serves as the primary filter to isolate high-quality records for final reporting.
Sources: tests/data_quality/test_cross_model_consistency.sql:18-20, src/flext_dbt_oracle_wms/_utilities/client.py:65-68