Idea Summary:
Enhance APEX Data Load Definitions by introducing declarative validation rules. This would allow developers to enforce data integrity by configuring validations such as:
- Required Fields: Ensure critical columns are non-empty.
- Value Ranges: Define minimum/maximum values for numeric/date columns.
- Lookup Constraints: Restrict values to existing entries in lookup tables.
- Regex Patterns: Validate text formats (e.g., emails, phone numbers).
- Custom Error Messages: Provide clear feedback for validation failures.
Use Case:
A hospital imports patient records via CSV. Validations ensure:
- Required Fields: “Patient ID” and “Admission Date” are mandatory.
- Range Checks: “Age” must be 0-120.
- Lookups: “Department Code” must exist in the system’s departments table.
- Formatting: “Email” must match a regex pattern.
During upload, rows violating these rules are flagged with actionable errors (e.g., “Row 5: Department Code ‘XYZ’ not found”), allowing users to correct the file without compromising data quality.
Preferred Solution:
- UI Enhancements:
- Add a Validations section under “Advanced Column Settings” in Data Load Definitions.
- Include checkboxes for Required, fields for Min/Max values, Regex Pattern, and a Lookup Validation toggle (fail if no match).
- Validation Engine:
- Run checks during the staging phase, pre-commit.
- Generate an error report highlighting invalid rows/columns with custom messages.
- Lookup Handling:
- Replace silent empty values with hard failures or configurable defaults (e.g., “Unknown” or NULL).
- Error Reporting:
- Provide a downloadable summary with row numbers, columns, and failure reasons.
- Optionally, allow partial uploads for valid rows (configurable by developer).
Example Configuration:
- Column: “Discount Percentage”
- Validation: Range (0–100)
- Error Message: “Discount must be between 0% and 100%.”
- Column: “Country Code”
- Validation: Lookup (must exist in COUNTRIES table)
- Error Action: Reject row and log “Invalid Country Code: XYZ”.
Benefits:
- Data Integrity: Prevent invalid or incomplete data at the source.
- User Productivity: Reduce back-and-forth corrections with instant feedback.
- Low-Code: No PL/SQL required—configure validations declaratively.
Technical Considerations:
- Ensure validation logic respects column data types (e.g., date ranges vs. numeric ranges).
This is something that you ALWAYS have to do anyway, and right now we have to code this all by hand.