How Reconciliation Improves the Product
Reconciliation is usually described as a verification step — you run it to check that migration worked. That framing understates what reconciliation actually does. Reconciliation is a feedback system. Every mismatch it detects is a signal about the migration pipeline: the full load, the CDC application, the normalization rules, or the type mapping. A product that uses reconciliation as a feedback loop gets better with each migration; a product that uses it only as a final gate stays the same.
This chapter covers how reconciliation findings drive improvements in the migration pipeline, how patterns across runs generate rule suggestions, and how reconciliation becomes a cutover gate rather than a retrospective audit.
17.1 Reconciliation as a Feedback Loop
Each reconciliation run produces a finding. Each finding is a data point about what the pipeline does incorrectly or incompletely. When patterns in findings are recognized, they become rules:
Finding (run 1): VALUE_DRIFT on customer.email — case mismatch for 3 rows
Finding (run 2): VALUE_DRIFT on supplier.contact_email — case mismatch for 12 rows
Finding (run 3): VALUE_DRIFT on employee.work_email — case mismatch for 47 rows
Pattern recognized: email columns are consistently case-mismatched
Generated rule suggestion: normalizeCase: lowercase for columns matching *email*
The rule suggestion, once approved and applied, eliminates this entire class of discrepancy for future migrations. The reconciliation engine no longer flags email case mismatches as VALUE_DRIFT — they are classified as NORMALIZATION_RULE and filtered from the FAIL count.
This feedback loop is what separates a migration product from a migration script. A script runs once. A product learns. DMS and Debezium have no equivalent of this layer — they emit events. What you do with the knowledge embedded in those events, the patterns, the rules, the institutional memory, is entirely your problem. Pulsaride Transform accumulates it.
Business Impact
The reconciliation feedback loop is what determines the cost trajectory of migrations over time. A migration executed with point tools has a flat — or rising — cost per migration: each engagement re-discovers the same classes of problems from scratch. Pulsaride Transform's pattern engine converts recurring discrepancies into approved rules, which means the second migration on a team's Oracle estate runs cleaner and faster than the first, and the third cleaner than the second.
Teams that have run two or more migrations with Pulsaride Transform report that pre-cutover reconciliation cleanup time drops 40–60% by the third migration as accumulated rules handle what was previously resolved manually. The 4,221-row NULL email case in Chapter 3? After the first migration, it becomes a one-line rule. On the next schema, it never generates a finding at all.
This compounding effect is not available from any tool that treats each migration as a blank slate.
Product note: Pulsaride Transform's reconciliation module maintains a _pulsaride.reconciliation_patterns table that accumulates patterns across runs. The pattern engine runs after each reconciliation cycle and compares current discrepancies against historical discrepancy signatures. When a discrepancy signature appears in 3 or more consecutive runs across 2 or more tables, a rule suggestion is generated and surfaced in the monitoring dashboard under "Suggested Rules."
17.2 Auto-Rule Generation
Rule generation produces a new rule in the pipeline configuration YAML that addresses a detected pattern. The operator reviews and approves before the rule is applied.
Phone Number Normalization (Example of Auto-Generated Rule)
Pattern detected across 8 runs:
customer.phone_number: Oracle stores+1 (555) 012-3456, PostgreSQL stores+1 (555) 012-3456(identical — no mismatch), but the reconciliation type-encoding comparison flags a difference because PostgreSQL compares byte-for-byte and some phone numbers have different Unicode space characters.
Generated rule suggestion:
# Auto-generated rule suggestion (requires operator approval)
transformations:
- table: "*"
column: "*phone*"
operation: normalize
parameters:
type: phone_e164
fallback: preserve # if normalization fails, keep original value
The rule normalizes all columns matching *phone* to E.164 format during full load and CDC. The reconciliation engine applies the same normalization to Oracle values before comparison, eliminating the type-encoding discrepancy.
NULL vs Empty String (Auto-Generated)
Pattern: 22 runs, 15 tables, 47 columns showing NORMALIZATION_RULE discrepancy on normalizeEmpty: true columns.
The current configuration applies normalizeEmpty to 47 columns explicitly. The pattern engine detects that every VARCHAR2 column with historical empty-string data has been manually added. Rule suggestion:
# Suggested: apply normalizeEmpty globally to all VARCHAR2-mapped TEXT columns
transformations:
- table: "*"
column: "*"
sourceType: VARCHAR2
operation: normalizeEmpty
parameters:
emptyToNull: true
This eliminates the need to specify each VARCHAR2 column individually.
Product note: Auto-generated rule suggestions are never applied automatically. They require operator approval. The approval workflow in the monitoring dashboard shows:
- The pattern that triggered the suggestion (table, column, discrepancy type, frequency)
- The generated rule YAML
- The expected impact (how many current discrepancies would be resolved)
- Any risk flag (e.g., "this rule would affect 340 tables — confirm scope")
17.3 Drift Trend Analysis
Reconciliation runs continuously during parallel running. Each run produces a count of discrepancies per table and per classification. Tracking this over time reveals trends:
Converging trend (good): Discrepancy count decreases over time. Expected as CDC catches up and VALUE_DRIFT items are resolved.
Flat trend (acceptable): Discrepancy count is stable at zero or near-zero. System is synchronized.
Diverging trend (bad): Discrepancy count increases over time. Indicates a systematic problem: CDC applying events incorrectly, a normalization rule not applied during CDC, or Oracle schema drift not reflected in PostgreSQL.
Day 1: 4,221 discrepancies (CDC catch-up, mostly EXPECTED_CDC_DELTA)
Day 2: 847 discrepancies (CDC nearly caught up)
Day 3: 12 discrepancies (stable, all advisory)
Day 4: 12 discrepancies (stable)
Day 5: 44 discrepancies ← ALERT: trend reversal
A trend reversal on day 5 requires immediate investigation. Common causes:
- A batch job ran in Oracle that generated many updates not yet applied by CDC (lag spike)
- A schema change in Oracle not reflected in PostgreSQL
- A CDC pipeline configuration change that introduced a normalization regression
Product note: pulsaride-monitoring tracks the reconciliation discrepancy trend as a time-series metric: recon_discrepancy_count{table, classification}. The dashboard shows a 7-day trend chart. An alert fires when the discrepancy count for blocking-tier tables increases by more than 10% in a single reconciliation cycle.
17.4 SLA Reconciliation Gates
Cutover readiness is governed by reconciliation SLAs — specific thresholds that must hold for a defined period before cutover is approved. SLA gates transform reconciliation from a point-in-time check into a sustained quality assurance period.
Example SLA gates:
cutover:
reconciliationSlaGates:
- name: financial_tables_clean
tables: [ledger_entry, payment, transaction]
requirement: PASS
mode: hash
sustainedHours: 24 # must pass hash reconciliation continuously for 24 hours
- name: count_zero_delta
tables: all
requirement: count_delta = 0
sustainedHours: 4 # must have zero count delta for 4 consecutive hours
- name: aggregate_match
tables: [ledger_entry]
requirement: aggregate_delta_percent < 0.0001
aggregates: [total_credits, total_debits]
sustainedHours: 12
The cutover command checks that all SLA gates are currently passing. A gate that was passing for 23 hours but failed 30 minutes ago does not allow cutover — the 24-hour sustained requirement restarts.
This design prevents a scenario where the team rushes to cutover immediately after reconciliation passes, when the system has not demonstrated sustained stability. A 24-hour sustained clean reconciliation on financial tables means 24 hours of CDC running correctly, 24 hours of normalization rules applied correctly, 24 hours of no count drift. That is the confidence baseline for cutover.
17.5 Reconciliation as Root Cause Analysis
When a post-cutover incident occurs — "the account balance is wrong" — the reconciliation history is the starting point for root cause analysis.
The reconciliation report from the hours before cutover shows whether the affected rows were passing or failing reconciliation. If they were passing: the corruption occurred after cutover (application bug, unexpected query). If they were failing: the migration pipeline introduced the error before cutover (missed VALUE_DRIFT, applied compensating write incorrectly).
-- Find reconciliation history for a specific row
SELECT
r.run_id,
r.run_timestamp,
r.classification,
r.source_value,
r.target_value,
r.resolution
FROM _pulsaride.reconciliation_discrepancies r
WHERE r.table_name = 'ledger_entry'
AND r.pk_value = '9201441'
ORDER BY r.run_timestamp;
This query shows every time the row was flagged in reconciliation, what the values were at each point, and how the discrepancy was resolved. This is the migration audit trail at the row level.
17.6 Example: The Reconciliation Feedback Loop Over Four Migrations
A team uses Pulsaride Transform for four consecutive migrations of similar Oracle → PostgreSQL schemas over 18 months.
Migration 1: 4,221 reconciliation discrepancies on day 1. Root causes: CASE_NORMALIZATION (email), VALUE_DRIFT (phone normalization), TYPE_ENCODING (timestamp UTC offset). 38 hours of investigation and compensating writes.
Migration 2: 847 discrepancies on day 1. Three new rules added from migration 1 (email lowercase, phone E.164, timestamp UTC comparison). 12 hours of investigation.
Migration 3: 144 discrepancies on day 1. Two more rules added from migration 2 (number precision comparison, NULL handling for CHAR-padded columns). 3 hours of investigation.
Migration 4: 21 discrepancies on day 1. All 21 were DATA_LOSS findings in one table (discovered: a CDC gap caused by an archived log deletion). Fixed in 2 hours. Zero VALUE_DRIFT or NORMALIZATION_RULE discrepancies — all expected patterns are now handled by rules.
Across four migrations, the investigation time went from 38 hours to 2 hours. The product accumulated 8 normalization rules and 4 comparison rules from the feedback loop. These rules are shared across the team and are now part of the standard Pulsaride Transform configuration for this type of Oracle application.
This is what reconciliation-as-feedback-loop produces over time: a product that gets increasingly accurate with each deployment, reducing the human effort required for each successive migration.