references/data_reconciliation_strategies.md · 24000
'Time spent on reconciliation jobs',
['job_type']
)
self.auto_corrections = Counter(
'reconciliation_auto_corrections_total',
'Number of automatically corrected inconsistencies',
['table', 'correction_type']
)
self.data_drift_gauge = Gauge(
'data_drift_percentage',
'Percentage of records with inconsistencies',
['table']
)
def record_inconsistency(self, table, inconsistency_type, severity):
"""Record a found inconsistency"""
self.inconsistencies_found.labels(
table=table,
type=inconsistency_type,
severity=severity
).inc()
def record_auto_correction(self, table, correction_type):
"""Record an automatic correction"""
self.auto_corrections.labels(
table=table,
correction_type=correction_type
).inc()
def update_data_drift(self, table, drift_percentage):
"""Update data drift gauge"""
self.data_drift_gauge.labels(table=table).set(drift_percentage)
def record_job_duration(self, job_type, duration_seconds):
"""Record reconciliation job duration"""
self.reconciliation_duration.labels(job_type=job_type).observe(duration_seconds)
### 2. Alerting Rules
```yaml
# Prometheus alerting rules for data reconciliation
groups:
- name: data_reconciliation
rules:
- alert: HighDataInconsistency
expr: reconciliation_inconsistencies_total > 100
for: 5m
labels:
severity: critical
annotations:
summary: "High number of data inconsistencies detected"
description: "{{ $value }} inconsistencies found in the last 5 minutes"
- alert: DataDriftHigh
expr: data_drift_percentage > 5
for: 10m
labels:
severity: warning
annotations:
summary: "Data drift percentage is high"
description: "{{ $labels.table }} has {{ $value }}% data drift"
- alert: ReconciliationJobFailed
expr: up{job="reconciliation"} == 0
for: 2m
labels:
severity: critical
annotations:
summary: "Reconciliation job is down"
description: "The data reconciliation service is not responding"
- alert: AutoCorrectionRateHigh
expr: rate(reconciliation_auto_corrections_total[10m]) > 10
for: 5m
labels:
severity: warning
annotations:
summary: "High rate of automatic corrections"
description: "Auto-correction rate is {{ $value }} per second"
3. Dashboard and Reporting
class ReconciliationDashboard:
def __init__(self, database_client, metrics_client):
self.db = database_client
self.metrics = metrics_client
def generate_daily_report(self, date=None):
"""Generate daily reconciliation report"""
if not date:
date = datetime.utcnow().date()
# Query reconciliation results for the day
daily_stats = self.db.query("""
SELECT
table_name,
inconsistency_type,
COUNT(*) as count,
AVG(CASE WHEN resolution = 'AUTO_CORRECTED' THEN 1 ELSE 0 END) as auto_correction_rate
FROM reconciliation_log
WHERE DATE(created_at) = %s
GROUP BY table_name, inconsistency_type
""", (date,))
# Generate summary
summary = {
'date': date.isoformat(),
'total_inconsistencies': sum(row['count'] for row in daily_stats),
'auto_correction_rate': sum(row['auto_correction_rate'] * row['count'] for row in daily_stats) / max(sum(row['count'] for row in daily_stats), 1),
'tables_affected': len(set(row['table_name'] for row in daily_stats)),
'details_by_table': {}
}
# Group by table
for row in daily_stats:
table = row['table_name']
if table not in summary['details_by_table']:
summary['details_by_table'][table] = []
summary['details_by_table'][table].append({
'inconsistency_type': row['inconsistency_type'],
'count': row['count'],
'auto_correction_rate': row['auto_correction_rate']
})
return summary
def generate_trend_analysis(self, days=7):
"""Generate trend analysis for reconciliation metrics"""
end_date = datetime.utcnow().date()
start_date = end_date - timedelta(days=days)
trends = self.db.query("""
SELECT
DATE(created_at) as date,
table_name,
COUNT(*) as inconsistencies,
AVG(CASE WHEN resolution = 'AUTO_CORRECTED' THEN 1 ELSE 0 END) as auto_correction_rate
FROM reconciliation_log
WHERE DATE(created_at) BETWEEN %s AND %s
GROUP BY DATE(created_at), table_name
ORDER BY date, table_name
""", (start_date, end_date))
# Calculate trends
trend_analysis = {
'period': f"{start_date} to {end_date}",
'trends': {},
'overall_trend': 'stable'
}
for table in set(row['table_name'] for row in trends):
table_data = [row for row in trends if row['table_name'] == table]
if len(table_data) >= 2:
first_count = table_data[0]['inconsistencies']
last_count = table_data[-1]['inconsistencies']
if last_count > first_count * 1.2:
trend = 'increasing'
elif last_count < first_count * 0.8:
trend = 'decreasing'
else:
trend = 'stable'
trend_analysis['trends'][table] = {
'direction': trend,
'first_day_count': first_count,
'last_day_count': last_count,
'change_percentage': ((last_count - first_count) / max(first_count, 1)) * 100
}
return trend_analysis
Advanced Reconciliation Techniques
1. Machine Learning-Based Anomaly Detection
from sklearn.isolation import IsolationForest
from sklearn.preprocessing import StandardScaler
import numpy as np
class MLAnomalyDetector:
def __init__(self):
self.models = {}
self.scalers = {}
def train_anomaly_detector(self, table_name, training_data):
"""Train anomaly detection model for a specific table"""
# Prepare features (convert records to numerical features)
features = self.extract_features(training_data)
# Scale features
scaler = StandardScaler()
scaled_features = scaler.fit_transform(features)
# Train isolation forest
model = IsolationForest(contamination=0.05, random_state=42)
model.fit(scaled_features)
# Store model and scaler
self.models[table_name] = model
self.scalers[table_name] = scaler
def detect_anomalies(self, table_name, data):
"""Detect anomalous records that may indicate reconciliation issues"""
if table_name not in self.models:
raise ValueError(f"No trained model for table {table_name}")
# Extract features
features = self.extract_features(data)
# Scale features
scaled_features = self.scalers[table_name].transform(features)
# Predict anomalies
anomaly_scores = self.models[table_name].decision_function(scaled_features)
anomaly_predictions = self.models[table_name].predict(scaled_features)
# Return anomalous records with scores
anomalies = []
for i, (record, score, is_anomaly) in enumerate(zip(data, anomaly_scores, anomaly_predictions)):
if is_anomaly == -1: # Isolation forest returns -1 for anomalies
anomalies.append({
'record_index': i,
'record': record,
'anomaly_score': score,
'severity': 'high' if score < -0.5 else 'medium'
})
return anomalies
def extract_features(self, data):
"""Extract numerical features from database records"""
features = []
for record in data:
record_features = []
for key, value in record.items():
if isinstance(value, (int, float)):
record_features.append(value)
elif isinstance(value, str):
# Convert string to hash-based feature
record_features.append(hash(value) % 10000)
elif isinstance(value, datetime):
# Convert datetime to timestamp
record_features.append(value.timestamp())
else:
# Default value for other types
record_features.append(0)
features.append(record_features)
return np.array(features)
2. Probabilistic Reconciliation
import random
from typing import List, Dict, Tuple
class ProbabilisticReconciler:
def __init__(self, confidence_threshold=0.95):
self.confidence_threshold = confidence_threshold
def statistical_sampling_validation(self, table_name: str, population_size: int) -> Dict:
"""Use statistical sampling to validate large datasets"""
# Calculate sample size for 95% confidence, 5% margin of error
confidence_level = 0.95
margin_of_error = 0.05
z_score = 1.96 # for 95% confidence
p = 0.5 # assume 50% error rate for maximum sample size
sample_size = (z_score ** 2 * p * (1 - p)) / (margin_of_error ** 2)
if population_size < 10000:
# Finite population correction
sample_size = sample_size / (1 + (sample_size - 1) / population_size)
sample_size = min(int(sample_size), population_size)
# Generate random sample
sample_ids = self.generate_random_sample(table_name, sample_size)
# Validate sample
sample_results = self.validate_sample_records(table_name, sample_ids)
# Calculate population estimates
error_rate = sample_results['errors'] / sample_size
estimated_errors = int(population_size * error_rate)
# Calculate confidence interval
standard_error = (error_rate * (1 - error_rate) / sample_size) ** 0.5
margin_of_error_actual = z_score * standard_error
confidence_interval = (
max(0, error_rate - margin_of_error_actual),
min(1, error_rate + margin_of_error_actual)
)
return {
'table_name': table_name,
'population_size': population_size,
'sample_size': sample_size,
'sample_error_rate': error_rate,
'estimated_total_errors': estimated_errors,
'confidence_interval': confidence_interval,
'confidence_level': confidence_level,
'recommendation': self.generate_recommendation(error_rate, confidence_interval)
}
def generate_random_sample(self, table_name: str, sample_size: int) -> List[int]:
"""Generate random sample of record IDs"""
# Get total record count and ID range
id_range = self.db.query(f"SELECT MIN(id), MAX(id) FROM {table_name}")[0]
min_id, max_id = id_range
# Generate random IDs
sample_ids = []
attempts = 0
max_attempts = sample_size * 10 # Avoid infinite loop
while len(sample_ids) < sample_size and attempts < max_attempts:
candidate_id = random.randint(min_id, max_id)
# Check if ID exists
exists = self.db.query(f"SELECT 1 FROM {table_name} WHERE id = %s", (candidate_id,))
if exists and candidate_id not in sample_ids:
sample_ids.append(candidate_id)
attempts += 1
return sample_ids
def validate_sample_records(self, table_name: str, sample_ids: List[int]) -> Dict:
"""Validate a sample of records"""
validation_results = {
'total_checked': len(sample_ids),
'errors': 0,
'error_details': []
}
for record_id in sample_ids:
# Get record from both source and target
source_record = self.source_db.get_record(table_name, record_id)
target_record = self.target_db.get_record(table_name, record_id)
if not target_record:
validation_results['errors'] += 1
validation_results['error_details'].append({
'id': record_id,
'error_type': 'MISSING_IN_TARGET'
})
elif not self.records_match(source_record, target_record):
validation_results['errors'] += 1
validation_results['error_details'].append({
'id': record_id,
'error_type': 'DATA_MISMATCH',
'differences': self.find_differences(source_record, target_record)
})
return validation_results
def generate_recommendation(self, error_rate: float, confidence_interval: Tuple[float, float]) -> str:
"""Generate recommendation based on error rate and confidence"""
if confidence_interval[1] < 0.01: # Less than 1% error rate with confidence
return "Data quality is excellent. Continue with normal reconciliation schedule."
elif confidence_interval[1] < 0.05: # Less than 5% error rate with confidence
return "Data quality is acceptable. Monitor closely and investigate sample errors."
elif confidence_interval[0] > 0.1: # More than 10% error rate with confidence
return "Data quality is poor. Immediate comprehensive reconciliation required."
else:
return "Data quality is uncertain. Increase sample size for better estimates."
Performance Optimization
1. Parallel Processing
import asyncio
import multiprocessing as mp
from concurrent.futures import ProcessPoolExecutor, ThreadPoolExecutor
class ParallelReconciler:
def __init__(self, max_workers=None):
self.max_workers = max_workers or mp.cpu_count()
async def parallel_table_reconciliation(self, tables: List[str]):
"""Reconcile multiple tables in parallel"""
async with asyncio.Semaphore(self.max_workers):
tasks = [
self.reconcile_table_async(table)
for table in tables
]
results = await asyncio.gather(*tasks, return_exceptions=True)
# Process results
summary = {
'total_tables': len(tables),
'successful': 0,
'failed': 0,
'results': {}
}
for table, result in zip(tables, results):
if isinstance(result, Exception):
summary['failed'] += 1
summary['results'][table] = {
'status': 'failed',
'error': str(result)
}
else:
summary['successful'] += 1
summary['results'][table] = result
return summary
def parallel_chunk_processing(self, table_name: str, chunk_size: int = 10000):
"""Process table reconciliation in parallel chunks"""
# Get total record count
total_records = self.db.get_record_count(table_name)
num_chunks = (total_records + chunk_size - 1) // chunk_size
# Create chunk specifications
chunks = []
for i in range(num_chunks):
start_id = i * chunk_size
end_id = min((i + 1) * chunk_size - 1, total_records - 1)
chunks.append({
'table': table_name,
'start_id': start_id,
'end_id': end_id,
'chunk_number': i + 1
})
# Process chunks in parallel
with ProcessPoolExecutor(max_workers=self.max_workers) as executor:
chunk_results = list(executor.map(self.process_chunk, chunks))
# Aggregate results
total_inconsistencies = sum(r['inconsistencies'] for r in chunk_results)
total_corrections = sum(r['corrections'] for r in chunk_results)
return {
'table': table_name,
'total_records': total_records,
'chunks_processed': len(chunks),
'total_inconsistencies': total_inconsistencies,
'total_corrections': total_corrections,
'chunk_details': chunk_results
}
def process_chunk(self, chunk_spec: Dict) -> Dict:
"""Process a single chunk of records"""
# This runs in a separate process
table = chunk_spec['table']
start_id = chunk_spec['start_id']
end_id = chunk_spec['end_id']
# Initialize database connections for this process
local_source_db = SourceDatabase()
local_target_db = TargetDatabase()
# Get records in chunk
source_records = local_source_db.get_records_range(table, start_id, end_id)
target_records = local_target_db.get_records_range(table, start_id, end_id)
# Reconcile chunk
inconsistencies = 0
corrections = 0
for source_record in source_records:
target_record = target_records.get(source_record['id'])
if not target_record:
inconsistencies += 1
# Auto-correct if possible
try:
local_target_db.insert(table, source_record)
corrections += 1
except Exception:
pass # Log error in production
elif not self.records_match(source_record, target_record):
inconsistencies += 1
# Auto-correct field mismatches
try:
updates = self.calculate_updates(source_record, target_record)
local_target_db.update(table, source_record['id'], updates)
corrections += 1
except Exception:
pass # Log error in production
return {
'chunk_number': chunk_spec['chunk_number'],
'start_id': start_id,
'end_id': end_id,
'records_processed': len(source_records),
'inconsistencies': inconsistencies,
'corrections': corrections
}
2. Incremental Reconciliation
class IncrementalReconciler:
def __init__(self, source_db, target_db):
self.source_db = source_db
self.target_db = target_db
self.last_reconciliation_times = {}
def incremental_reconciliation(self, table_name: str):
"""Reconcile only records changed since last reconciliation"""
last_reconciled = self.get_last_reconciliation_time(table_name)
# Get records modified since last reconciliation
modified_source = self.source_db.get_records_modified_since(
table_name, last_reconciled
)
modified_target = self.target_db.get_records_modified_since(
table_name, last_reconciled
)
# Create lookup dictionaries
source_dict = {r['id']: r for r in modified_source}
target_dict = {r['id']: r for r in modified_target}
# Find all record IDs to check
all_ids = set(source_dict.keys()) | set(target_dict.keys())
inconsistencies = []
for record_id in all_ids:
source_record = source_dict.get(record_id)
target_record = target_dict.get(record_id)
if source_record and not target_record:
inconsistencies.append({
'type': 'missing_in_target',
'table': table_name,
'id': record_id,
'source_record': source_record
})
elif not source_record and target_record:
inconsistencies.append({
'type': 'extra_in_target',
'table': table_name,
'id': record_id,
'target_record': target_record
})
elif source_record and target_record:
if not self.records_match(source_record, target_record):
inconsistencies.append({
'type': 'data_mismatch',
'table': table_name,
'id': record_id,
'source_record': source_record,
'target_record': target_record,
'differences': self.find_differences(source_record, target_record)
})
# Update last reconciliation time
self.update_last_reconciliation_time(table_name, datetime.utcnow())
return {
'table': table_name,
'reconciliation_time': datetime.utcnow(),
'records_checked': len(all_ids),
'inconsistencies_found': len(inconsistencies),
'inconsistencies': inconsistencies
}
def get_last_reconciliation_time(self, table_name: str) -> datetime:
"""Get the last reconciliation timestamp for a table"""
result = self.source_db.query("""
SELECT last_reconciled_at
FROM reconciliation_metadata
WHERE table_name = %s
""", (table_name,))
if result:
return result[0]['last_reconciled_at']
else:
# First time reconciliation - start from beginning of migration
return self.get_migration_start_time()
def update_last_reconciliation_time(self, table_name: str, timestamp: datetime):
"""Update the last reconciliation timestamp"""
self.source_db.execute("""
INSERT INTO reconciliation_metadata (table_name, last_reconciled_at)
VALUES (%s, %s)
ON CONFLICT (table_name)
DO UPDATE SET last_reconciled_at = %s
""", (table_name, timestamp, timestamp))
This comprehensive guide provides the framework and tools necessary for implementing robust data reconciliation strategies during migrations, ensuring data integrity and consistency while minimizing business disruption.