Basel Capital Adequacy Reconciliation Script Generator

Role: Principal Financial Regulatory Data Engineer specializing in Basel III/IV prudential capital reporting pipelines.

Context

• Regulatory Framework: {{reporting_framework}}
• Enterprise Data Warehouse: {{source_data_warehouse}}
• Target Reporting Entity: {{regulatory_entity_id}}
• Exposure & Asset Table: {{risk_weighted_assets_table}}
• Aggregation Granularity: {{aggregation_grain}}
• Reconciliation Tolerance: {{validation_thresholds}}

Task

Author a modular, production-ready Python and SQL data pipeline script that extracts, cleanses, calculates, and validates capital adequacy exposures across wholesale credit portfolios for mandatory regulatory submission filings.

Method

1. Establish connection protocols and parameterize environment variables for {{source_data_warehouse}} with read isolation.
2. Write optimized SQL staging queries targeting {{risk_weighted_assets_table}} partitioned by {{aggregation_grain}}.
3. Implement explicit schema validation handling null values, currency conversions, and counterparty classification codes.
4. Code capital adequacy aggregation logic adhering strictly to {{reporting_framework}} rules.
5. Construct an automated reconciliation module comparing aggregated outputs against {{validation_thresholds}}.
6. Generate structured exception logs and error-routing handlers for anomalous exposure spikes.
7. Format the finalized dataset into a tamper-evident export payload for {{regulatory_entity_id}}.

Constraints

• Code MUST be fully runnable with zero pseudocode or omitted implementation blocks.
• MUST implement idempotent execution safeguards and robust transaction rollback logic.
• Script MUST NOT hardcode credentials, connection strings, or static execution dates.
• SQL syntax must be dialect-compliant with {{source_data_warehouse}} standards.
• Inline code comments must explicitly explain regulatory computation rules.

Output format

1. Pipeline Architecture Summary (Markdown table outlining stage dependencies, runtime inputs, and outputs)
2. SQL Staging and Extraction Script (Full DDL/DML script with parameterized filters)
3. Python Transformation & Validation Script (Object-oriented, typing-annotated Python script incorporating logging and threshold alerts)
4. Verification Test Harness (Unit test script verifying calculations against {{validation_thresholds}})

Self-review

• Confirm all 6 context variables ({{reporting_framework}}, {{source_data_warehouse}}, {{regulatory_entity_id}}, {{risk_weighted_assets_table}}, {{aggregation_grain}}, {{validation_thresholds}}) are deeply integrated into the code logic.
• Verify that edge-case exceptions (division by zero, missing counterparties) are explicitly trapped in the script.
• Check that no placeholder ellipsis or unhandled comments exist in executable code blocks.