Lease Ledger Migration Verification Script

Role: Lead Database Data Engineer specializing in commercial real estate financial data migrations.

Context

• Legacy source database connection: {{source_erp_database}}
• Destination tenant table: {{target_tenant_table}}
• Destination accounting ledger table: {{ledger_entry_table}}
• Maximum allowed financial rounding delta: {{discrepancy_tolerance_amount}}
• Verification cursor batch size: {{validation_batch_size}}
• Output log format: {{reporting_output_format}}

Task

Write a robust Python validation script that extracts financial balances, recurring lease escalations, and security deposits from {{source_erp_database}}, comparing them against {{target_tenant_table}} and {{ledger_entry_table}} to assert 100% data fidelity post-migration.

Method

1. Initialize database connection pools with read-only credentials for source and target databases.
2. Formulate parameterized aggregation queries extracting total balance, prepaid rent, and active lease count grouped by property ID.
3. Execute chunked validation reads using {{validation_batch_size}} to manage memory consumption.
4. Compare ledger sums per lease, flagging discrepancies that exceed {{discrepancy_tolerance_amount}}.
5. Validate foreign key consistency between {{target_tenant_table}} and {{ledger_entry_table}} records.
6. Generate an automated summary matrix tracking matched records, unmatched IDs, and variance magnitudes.
7. Format discrepancy logs and summary data according to {{reporting_output_format}}.

Constraints

• MUST run purely read-only queries with zero mutations on source or destination tables.
• MUST NOT load entire financial ledgers into memory at once; streaming cursors must be used.
• Currency computations MUST use Python Decimal types rather than standard IEEE floating-point.
• Script execution must exit with status code 1 if any discrepancy surpasses {{discrepancy_tolerance_amount}}.

Output format

A standalone Python 3 script containing:

1. Imports, CLI Arguments, and Connection Setup (max 30 lines)
2. SQL Reconciliation Queries and Aggregation Logic (max 50 lines)
3. Batch Execution Engine and Assertions (max 60 lines)
4. Report Exporter in {{reporting_output_format}} (max 40 lines)

Self-review

• Ensure floating-point arithmetic is completely replaced with Python's Decimal class.
• Confirm that database connection handlers properly close cursors and pools on SIGINT or unhandled exceptions.
• Check that discrepancy reporting surfaces exact property IDs, tenant IDs, and ledger line numbers.