Batch vs Streaming Reconciliation

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.

Output

1. One row per distinct record_id.
2. Include the comparison status, mismatch fields, and batch and streaming values.
3. Include matching, mismatched, batch-only, and streaming-only records.
4. Order by record_id ascending.

Output details

• Select columns in this order: record_id, comparison_status, mismatch_fields, batch_entity_id, stream_entity_id, batch_event_type, stream_event_type, batch_event_value, stream_event_value, batch_event_ts, stream_event_ts, batch_payload_hash, stream_payload_hash.
• Choose each source's latest row by loaded_at descending, breaking ties with ingestion_id descending.
• Use statuses MATCH, MISMATCH, BATCH_ONLY, and STREAM_ONLY exactly as spelled.
• Treat two NULL values as equal, but treat NULL and any non-NULL value as different when comparing fields.
• For mismatches, list differing fields in this order: entity_id, event_type, event_value, event_ts, payload_hash, separated by comma-space.
• Use missing_batch_record and missing_stream_record for one-sided rows, and an empty value for matching rows' mismatch_fields field.