Your question is Batch vs Streaming Reconciliation. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Write a SQL query to compare batch-loaded and streaming-loaded records and identify mismatches.
Compare the latest version of each record_id from both tables. Include records present in only one source, and identify differences in entity, event type, event value, event timestamp, or payload hash.
record_id.record_id ascending.| Column | Type | Description |
|---|---|---|
| ingestion_idPK | INT | Unique batch ingestion row identifier |
| record_id | INT | Business identifier of the loaded record |
| entity_id | INT | Identifier of the related entity |
| event_type | VARCHAR(30) | Type of business event |
| event_value | DECIMAL(12,2) | Numeric value associated with the event |
| event_ts | TIMESTAMP | Timestamp at which the event occurred |
| payload_hash | VARCHAR(64) | Hash of the source payload |
| loaded_at | TIMESTAMP | Timestamp when the batch row was loaded |
| Column | Type | Description |
|---|---|---|
| ingestion_idPK | INT | Unique streaming ingestion row identifier |
| record_id | INT | Business identifier of the streamed record |
| entity_id | INT | Identifier of the related entity |
| event_type | VARCHAR(30) | Type of business event |
| event_value | DECIMAL(12,2) | Numeric value associated with the event |
| event_ts | TIMESTAMP | Timestamp at which the event occurred |
| payload_hash | VARCHAR(64) | Hash of the source payload |
| loaded_at | TIMESTAMP | Timestamp when the streaming row was loaded |