How do you deal with duplicate records coming from the source Oracle or SQL Server systems into your Fact table?
Answer:
We prevent duplicates at multiple stages:
- Staging Checks: In our staging area, we write deduplication logic using window functions (like ROW_NUMBER()) to drop exact duplicate rows before they hit the warehouse.
- Unique Business Keys: In our fact tables, we define natural keys or composite unique constraints (such as combining Policy_ID, Transaction_Date, and Transaction_Type) so that if the same daily file is accidentally loaded twice, the load fails or skips the duplicates.
