How would you structure a systematic data validation process to cross-check post-trade files from external clearing houses against internal execution databases?
Use the provided tables to return every record that is missing from one source or contains a field-level discrepancy.
execution_ref, internal_trade_id, clearing_row_id, discrepancy_type, internal_quantity, clearing_quantity, internal_price, and clearing_price.discrepancy_type, then execution_ref ascending.| Column | Type | Description |
|---|---|---|
| trade_idPK | INT | Internal execution identifier |
| execution_ref | VARCHAR(20) | Execution reference shared with clearing files |
| trade_date | DATE | Execution trade date |
| symbol | VARCHAR(10) | Executed security symbol |
| side | VARCHAR(10) | Buy or sell side |
| quantity | INT | Executed quantity |
| price | NUMERIC(12,2) | Execution price |
| Column | Type | Description |
|---|---|---|
| file_row_idPK | INT | Source-file row identifier |
| execution_ref | VARCHAR(20) | Execution reference supplied by the clearing house |
| trade_date | DATE | Clearing trade date |
| symbol | VARCHAR(10) | Clearing-file security symbol |
| side | VARCHAR(10) | Clearing-file buy or sell side |
| quantity | INT | Clearing-file quantity |
| price | NUMERIC(12,2) | Clearing-file execution price |