Cross-System Consistency Validation
Description
This ETL workflow performs cross-system schema consistency validation by ingesting active table and column metadata from multiple database connections, normalizing table names, and identifying exact table pairs across systems. It executes pairwise column-level comparisons to detect datatype, length, and nullability mismatches. Aggregated mismatch metrics and row count deltas are computed to quantify schema and data discrepancies. A composite consistency score is derived to classify table pairs into OK, Warning, or Critical statuses. The final output is an ordered dataset providing detailed schema validation metrics and consistency assessments to support data integration quality assurance and governance.
Details
Data integration failures often stem from undetected schema mismatches and row count discrepancies across systems. This recipe systematically compares metadata, datatypes, and population metrics across multiple database connections, surface critical inconsistencies early, and provides a composite consistency score to prioritize remediation efforts before integration goes live.
Business value
- Prevent data quality issues before integration goes into production
- Reduce time spent on manual schema reconciliation and troubleshooting
- Quantify integration readiness with a single consistency score
- Enable risk-based prioritization of remediation work
Who benefits
- Data integration engineers and architects
- Data governance teams and stewards
- Quality assurance and validation teams
- Database and systems operations
What you receive
How the recipe works (guided flow)
Step 1 — Build table pairs
Identify matching source and target tables using normalized table names as the shared business key while preserving all connection and schema identifiers for traceability.
Step 2 — Compare table size
Verify that the target contains approximately the same population as the source and flag major row count deviations early.
Step 3 — Compare column compatibility
Match columns across source and target tables, counting aligned columns and detecting datatype, length, and nullability mismatches.
Step 4 — Compute consistency score
Convert multiple metadata checks (row count delta, datatype mismatches, length mismatches, nullable mismatches) into one comparable quality score for sorting and prioritization.
Step 5 — Assign status
Categorize each table pair into OK (≥85), Warning (≥65), or Critical (<65) based on the composite consistency score to enable risk-based remediation planning.
Sample insights (illustrative)
| Insight Category | What the recipe discovered | Business Impact |
|---|---|---|
| Schema Mismatch Severity | 3 datatype mismatches and 5 length mismatches detected in customer_master table pair, reducing consistency score to 72 (Warning) | Data transformation logic must handle type conversions; coordinate with target system owner for remediation |
| Data Population Variance | Source contains 2.1M rows while target shows 1.9M rows (9.5% delta); transactions table pair flagged as Critical due to 15% variance | Investigate row filtering logic or deletion policies; verify data completeness before cutover |
| Overall Readiness Snapshot | 12 table pairs: 8 OK (≥85), 3 Warning (65–84), 1 Critical (<65); 94% of matched columns align perfectly across paired systems | Integration is largely ready; focus remediation on 4 at-risk pairs before go-live |
Make sure the following ingredients are available in your workspace:
- Connected datasets are crawled and profiled
- Columns have distinct counts and top values generated