How do you handle duplicates in a fact table during data ingestion?
Write a PostgreSQL query that identifies staging rows eligible for loading. Treat rows with the same source_system, source_transaction_id, and event_date as duplicates, keep the most recently ingested row, and exclude keys already present in the fact table.
source_system, source_transaction_id, event_date, customer_id, amount, and ingested_at.source_system, event_date, and source_transaction_id.| Column | Type | Description |
|---|---|---|
| ingestion_idPK | BIGINT | Unique identifier for the staging ingestion record |
| source_system | VARCHAR(50) | System that produced the fact |
| source_transaction_id | VARCHAR(100) | Transaction identifier from the source system |
| event_date | DATE | Business date associated with the fact |
| customer_id | VARCHAR(50) | Source customer identifier |
| amount | NUMERIC(12,2) | Transaction amount |
| ingested_at | TIMESTAMP | Timestamp when the staging row was ingested |
| Column | Type | Description |
|---|---|---|
| fact_idPK | BIGINT | Warehouse fact identifier |
| source_system | VARCHAR(50) | System that produced the fact |
| source_transaction_id | VARCHAR(100) | Transaction identifier from the source system |
| event_date | DATE | Business date associated with the fact |
| customer_id | VARCHAR(50) | Source customer identifier |
| amount | NUMERIC(12,2) | Transaction amount |
| ingested_at | TIMESTAMP | Timestamp when the fact was loaded |