Batch vs Streaming Reconciliation

HardSQL · PostgreSQL

Problem

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

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

Schema

batch_records

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

streaming_records

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

Tablesbatch_recordsstreaming_records