references/data_reconciliation_strategies.md
Data Reconciliation Strategies
Overview
Data reconciliation is the process of ensuring data consistency and integrity across systems during and after migrations. This document provides comprehensive strategies, tools, and implementation patterns for detecting, measuring, and correcting data discrepancies in migration scenarios.
Core Principles
1. Eventually Consistent
Accept that perfect real-time consistency may not be achievable during migrations, but ensure eventual consistency through reconciliation processes.
2. Idempotent Operations
All reconciliation operations must be safe to run multiple times without causing additional issues.
3. Audit Trail
Maintain detailed logs of all reconciliation actions for compliance and debugging.
4. Non-Destructive
Reconciliation should prefer addition over deletion, and always maintain backups before corrections.
Types of Data Inconsistencies
1. Missing Records
Records that exist in source but not in target system.
2. Extra Records
Records that exist in target but not in source system.
3. Field Mismatches
Records exist in both systems but with different field values.
4. Referential Integrity Violations
Foreign key relationships that are broken during migration.
5. Temporal Inconsistencies
Data with incorrect timestamps or ordering.
6. Schema Drift
Structural differences between source and target schemas.
Detection Strategies
1. Row Count Validation
Simple Count Comparison
-- Compare total row counts
SELECT
'source' as system,
COUNT(*) as row_count
FROM source_table
UNION ALL
SELECT
'target' as system,
COUNT(*) as row_count
FROM target_table;
Filtered Count Comparison
-- Compare counts with business logic filters
WITH source_counts AS (
SELECT
status,
created_date::date as date,
COUNT(*) as count
FROM source_orders
WHERE created_date >= '2024-01-01'
GROUP BY status, created_date::date
),
target_counts AS (
SELECT
status,
created_date::date as date,
COUNT(*) as count
FROM target_orders
WHERE created_date >= '2024-01-01'
GROUP BY status, created_date::date
)
SELECT
COALESCE(s.status, t.status) as status,
COALESCE(s.date, t.date) as date,
COALESCE(s.count, 0) as source_count,
COALESCE(t.count, 0) as target_count,
COALESCE(s.count, 0) - COALESCE(t.count, 0) as difference
FROM source_counts s
FULL OUTER JOIN target_counts t
ON s.status = t.status AND s.date = t.date
WHERE COALESCE(s.count, 0) != COALESCE(t.count, 0);
2. Checksum-Based Validation
Record-Level Checksums
import hashlib
import json
class RecordChecksum:
def __init__(self, exclude_fields=None):
self.exclude_fields = exclude_fields or ['updated_at', 'version']
def calculate_checksum(self, record):
"""Calculate MD5 checksum for a database record"""
# Remove excluded fields and sort for consistency
filtered_record = {
k: v for k, v in record.items()
if k not in self.exclude_fields
}
# Convert to sorted JSON string for consistent hashing
normalized = json.dumps(filtered_record, sort_keys=True, default=str)
return hashlib.md5(normalized.encode('utf-8')).hexdigest()
def compare_records(self, source_record, target_record):
"""Compare two records using checksums"""
source_checksum = self.calculate_checksum(source_record)
target_checksum = self.calculate_checksum(target_record)
return {
'match': source_checksum == target_checksum,
'source_checksum': source_checksum,
'target_checksum': target_checksum
}
# Usage example
checksum_calculator = RecordChecksum(exclude_fields=['updated_at', 'migration_flag'])
source_records = fetch_records_from_source()
target_records = fetch_records_from_target()
mismatches = []
for source_id, source_record in source_records.items():
if source_id in target_records:
comparison = checksum_calculator.compare_records(
source_record, target_records[source_id]
)
if not comparison['match']:
mismatches.append({
'record_id': source_id,
'source_checksum': comparison['source_checksum'],
'target_checksum': comparison['target_checksum']
})
Aggregate Checksums
-- Calculate aggregate checksums for data validation
WITH source_aggregates AS (
SELECT
DATE_TRUNC('day', created_at) as day,
status,
COUNT(*) as record_count,
SUM(amount) as total_amount,
MD5(STRING_AGG(CAST(id AS VARCHAR) || ':' || CAST(amount AS VARCHAR), '|' ORDER BY id)) as checksum
FROM source_transactions
GROUP BY DATE_TRUNC('day', created_at), status
),
target_aggregates AS (
SELECT
DATE_TRUNC('day', created_at) as day,
status,
COUNT(*) as record_count,
SUM(amount) as total_amount,
MD5(STRING_AGG(CAST(id AS VARCHAR) || ':' || CAST(amount AS VARCHAR), '|' ORDER BY id)) as checksum
FROM target_transactions
GROUP BY DATE_TRUNC('day', created_at), status
)
SELECT
COALESCE(s.day, t.day) as day,
COALESCE(s.status, t.status) as status,
COALESCE(s.record_count, 0) as source_count,
COALESCE(t.record_count, 0) as target_count,
COALESCE(s.total_amount, 0) as source_amount,
COALESCE(t.total_amount, 0) as target_amount,
s.checksum as source_checksum,
t.checksum as target_checksum,
CASE WHEN s.checksum = t.checksum THEN 'MATCH' ELSE 'MISMATCH' END as status
FROM source_aggregates s
FULL OUTER JOIN target_aggregates t
ON s.day = t.day AND s.status = t.status
WHERE s.checksum != t.checksum OR s.checksum IS NULL OR t.checksum IS NULL;
3. Delta Detection
Change Data Capture (CDC) Based
class CDCReconciler:
def __init__(self, kafka_client, database_client):
self.kafka = kafka_client
self.db = database_client
self.processed_changes = set()
def process_cdc_stream(self, topic_name):
"""Process CDC events and track changes for reconciliation"""
consumer = self.kafka.consumer(topic_name)
for message in consumer:
change_event = json.loads(message.value)
change_id = f"{change_event['table']}:{change_event['key']}:{change_event['timestamp']}"
if change_id in self.processed_changes:
continue # Skip duplicate events
try:
self.apply_change(change_event)
self.processed_changes.add(change_id)
# Commit offset only after successful processing
consumer.commit()
except Exception as e:
# Log failure and continue - will be caught by reconciliation
self.log_processing_failure(change_id, str(e))
def apply_change(self, change_event):
"""Apply CDC change to target system"""
table = change_event['table']
operation = change_event['operation']
key = change_event['key']
data = change_event.get('data', {})
if operation == 'INSERT':
self.db.insert(table, data)
elif operation == 'UPDATE':
self.db.update(table, key, data)
elif operation == 'DELETE':
self.db.delete(table, key)
def reconcile_missed_changes(self, start_timestamp, end_timestamp):
"""Find and apply changes that may have been missed"""
# Query source database for changes in time window
source_changes = self.db.get_changes_in_window(
start_timestamp, end_timestamp
)
missed_changes = []
for change in source_changes:
change_id = f"{change['table']}:{change['key']}:{change['timestamp']}"
if change_id not in self.processed_changes:
missed_changes.append(change)
# Apply missed changes
for change in missed_changes:
try:
self.apply_change(change)
print(f"Applied missed change: {change['table']}:{change['key']}")
except Exception as e:
print(f"Failed to apply missed change: {e}")
4. Business Logic Validation
Critical Business Rules Validation
class BusinessLogicValidator:
def __init__(self, source_db, target_db):
self.source_db = source_db
self.target_db = target_db
def validate_financial_consistency(self):
"""Validate critical financial calculations"""
validation_rules = [
{
'name': 'daily_transaction_totals',
'source_query': """
SELECT DATE(created_at) as date, SUM(amount) as total
FROM source_transactions
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(created_at)
""",
'target_query': """
SELECT DATE(created_at) as date, SUM(amount) as total
FROM target_transactions
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(created_at)
""",
'tolerance': 0.01 # Allow $0.01 difference for rounding
},
{
'name': 'customer_balance_totals',
'source_query': """
SELECT customer_id, SUM(balance) as total_balance
FROM source_accounts
GROUP BY customer_id
HAVING SUM(balance) > 0
""",
'target_query': """
SELECT customer_id, SUM(balance) as total_balance
FROM target_accounts
GROUP BY customer_id
HAVING SUM(balance) > 0
""",
'tolerance': 0.01
}
]
validation_results = []
for rule in validation_rules:
source_data = self.source_db.execute_query(rule['source_query'])
target_data = self.target_db.execute_query(rule['target_query'])
differences = self.compare_financial_data(
source_data, target_data, rule['tolerance']
)
validation_results.append({
'rule_name': rule['name'],
'differences_found': len(differences),
'differences': differences[:10], # First 10 differences
'status': 'PASS' if len(differences) == 0 else 'FAIL'
})
return validation_results
def compare_financial_data(self, source_data, target_data, tolerance):
"""Compare financial data with tolerance for rounding differences"""
source_dict = {
tuple(row[:-1]): row[-1] for row in source_data
} # Last column is the amount
target_dict = {
tuple(row[:-1]): row[-1] for row in target_data
}
differences = []
# Check for missing records and value differences
for key, source_value in source_dict.items():
if key not in target_dict:
differences.append({
'key': key,
'source_value': source_value,
'target_value': None,
'difference_type': 'MISSING_IN_TARGET'
})
else:
target_value = target_dict[key]
if abs(float(source_value) - float(target_value)) > tolerance:
differences.append({
'key': key,
'source_value': source_value,
'target_value': target_value,
'difference': float(source_value) - float(target_value),
'difference_type': 'VALUE_MISMATCH'
})
# Check for extra records in target
for key, target_value in target_dict.items():
if key not in source_dict:
differences.append({
'key': key,
'source_value': None,
'target_value': target_value,
'difference_type': 'EXTRA_IN_TARGET'
})
return differences
Correction Strategies
1. Automated Correction
Missing Record Insertion
class AutoCorrector:
def __init__(self, source_db, target_db, dry_run=True):
self.source_db = source_db
self.target_db = target_db
self.dry_run = dry_run
self.correction_log = []
def correct_missing_records(self, table_name, key_field):
"""Add missing records from source to target"""
# Find records in source but not in target
missing_query = f"""
SELECT s.*
FROM source_{table_name} s
LEFT JOIN target_{table_name} t ON s.{key_field} = t.{key_field}
WHERE t.{key_field} IS NULL
"""
missing_records = self.source_db.execute_query(missing_query)
for record in missing_records:
correction = {
'table': table_name,
'operation': 'INSERT',
'key': record[key_field],
'data': record,
'timestamp': datetime.utcnow()
}
if not self.dry_run:
try:
self.target_db.insert(table_name, record)
correction['status'] = 'SUCCESS'
except Exception as e:
correction['status'] = 'FAILED'
correction['error'] = str(e)
else:
correction['status'] = 'DRY_RUN'
self.correction_log.append(correction)
return len(missing_records)
def correct_field_mismatches(self, table_name, key_field, fields_to_correct):
"""Correct field value mismatches"""
mismatch_query = f"""
SELECT s.{key_field}, {', '.join([f's.{f} as source_{f}, t.{f} as target_{f}' for f in fields_to_correct])}
FROM source_{table_name} s
JOIN target_{table_name} t ON s.{key_field} = t.{key_field}
WHERE {' OR '.join([f's.{f} != t.{f}' for f in fields_to_correct])}
"""
mismatched_records = self.source_db.execute_query(mismatch_query)
for record in mismatched_records:
key_value = record[key_field]
updates = {}
for field in fields_to_correct:
source_value = record[f'source_{field}']
target_value = record[f'target_{field}']
if source_value != target_value:
updates[field] = source_value
if updates:
correction = {
'table': table_name,
'operation': 'UPDATE',
'key': key_value,
'updates': updates,
'timestamp': datetime.utcnow()
}
if not self.dry_run:
try:
self.target_db.update(table_name, {key_field: key_value}, updates)
correction['status'] = 'SUCCESS'
except Exception as e:
correction['status'] = 'FAILED'
correction['error'] = str(e)
else:
correction['status'] = 'DRY_RUN'
self.correction_log.append(correction)
return len(mismatched_records)
2. Manual Review Process
Correction Workflow
class ManualReviewSystem:
def __init__(self, database_client):
self.db = database_client
self.review_queue = []
def queue_for_review(self, discrepancy):
"""Add discrepancy to manual review queue"""
review_item = {
'id': str(uuid.uuid4()),
'discrepancy_type': discrepancy['type'],
'table': discrepancy['table'],
'record_key': discrepancy['key'],
'source_data': discrepancy.get('source_data'),
'target_data': discrepancy.get('target_data'),
'description': discrepancy['description'],
'severity': discrepancy.get('severity', 'medium'),
'status': 'PENDING',
'created_at': datetime.utcnow(),
'reviewed_by': None,
'reviewed_at': None,
'resolution': None
}
self.review_queue.append(review_item)
# Persist to review database
self.db.insert('manual_review_queue', review_item)
return review_item['id']
def process_review(self, review_id, reviewer, action, notes=None):
"""Process manual review decision"""
review_item = self.get_review_item(review_id)
if not review_item:
raise ValueError(f"Review item {review_id} not found")
review_item.update({
'status': 'REVIEWED',
'reviewed_by': reviewer,
'reviewed_at': datetime.utcnow(),
'resolution': {
'action': action, # 'APPLY_SOURCE', 'KEEP_TARGET', 'CUSTOM_FIX'
'notes': notes
}
})
# Apply the resolution
if action == 'APPLY_SOURCE':
self.apply_source_data(review_item)
elif action == 'KEEP_TARGET':
pass # No action needed
elif action == 'CUSTOM_FIX':
# Custom fix would be applied separately
pass
# Update review record
self.db.update('manual_review_queue',
{'id': review_id},
review_item)
return review_item
def generate_review_report(self):
"""Generate summary report of manual reviews"""
reviews = self.db.query("""
SELECT
discrepancy_type,
severity,
status,
COUNT(*) as count,
MIN(created_at) as oldest_review,
MAX(created_at) as newest_review
FROM manual_review_queue
GROUP BY discrepancy_type, severity, status
ORDER BY severity DESC, discrepancy_type
""")
return reviews
3. Reconciliation Scheduling
Automated Reconciliation Jobs
import schedule
import time
from datetime import datetime, timedelta
class ReconciliationScheduler:
def __init__(self, reconciler):
self.reconciler = reconciler
self.job_history = []
def setup_schedules(self):
"""Set up automated reconciliation schedules"""
# Quick reconciliation every 15 minutes during migration
schedule.every(15).minutes.do(self.quick_reconciliation)
# Comprehensive reconciliation every 4 hours
schedule.every(4).hours.do(self.comprehensive_reconciliation)
# Deep validation daily
schedule.every().day.at("02:00").do(self.deep_validation)
# Weekly business logic validation
schedule.every().sunday.at("03:00").do(self.business_logic_validation)
def quick_reconciliation(self):
"""Quick count-based reconciliation"""
job_start = datetime.utcnow()
try:
# Check critical tables only
critical_tables = [
'transactions', 'orders', 'customers', 'accounts'
]
results = []
for table in critical_tables:
count_diff = self.reconciler.check_row_counts(table)
if abs(count_diff) > 0:
results.append({
'table': table,
'count_difference': count_diff,
'severity': 'high' if abs(count_diff) > 100 else 'medium'
})
job_result = {
'job_type': 'quick_reconciliation',
'start_time': job_start,
'end_time': datetime.utcnow(),
'status': 'completed',
'issues_found': len(results),
'details': results
}
# Alert if significant issues found
if any(r['severity'] == 'high' for r in results):
self.send_alert(job_result)
except Exception as e:
job_result = {
'job_type': 'quick_reconciliation',
'start_time': job_start,
'end_time': datetime.utcnow(),
'status': 'failed',
'error': str(e)
}
self.job_history.append(job_result)
def comprehensive_reconciliation(self):
"""Comprehensive checksum-based reconciliation"""
job_start = datetime.utcnow()
try:
tables_to_check = self.get_migration_tables()
issues = []
for table in tables_to_check:
# Sample-based checksum validation
sample_issues = self.reconciler.validate_sample_checksums(
table, sample_size=1000
)
issues.extend(sample_issues)
# Auto-correct simple issues
auto_corrections = 0
for issue in issues:
if issue['auto_correctable']:
self.reconciler.auto_correct_issue(issue)
auto_corrections += 1
else:
# Queue for manual review
self.reconciler.queue_for_manual_review(issue)
job_result = {
'job_type': 'comprehensive_reconciliation',
'start_time': job_start,
'end_time': datetime.utcnow(),
'status': 'completed',
'total_issues': len(issues),
'auto_corrections': auto_corrections,
'manual_reviews_queued': len(issues) - auto_corrections
}
except Exception as e:
job_result = {
'job_type': 'comprehensive_reconciliation',
'start_time': job_start,
'end_time': datetime.utcnow(),
'status': 'failed',
'error': str(e)
}
self.job_history.append(job_result)
def run_scheduler(self):
"""Run the reconciliation scheduler"""
print("Starting reconciliation scheduler...")
while True:
schedule.run_pending()
time.sleep(60) # Check every minute
Monitoring and Reporting
1. Reconciliation Metrics
class ReconciliationMetrics:
def __init__(self, prometheus_client):
self.prometheus = prometheus_client
# Define metrics
self.inconsistencies_found = Counter(
'reconciliation_inconsistencies_total',
'Number of inconsistencies found',
['table', 'type', 'severity']
)
self.reconciliation_duration = Histogram(
'reconciliation_duration_seconds',