Using APS PDW on-prem. Not having Primary Key Constraints is a big issue.
I've had to create an audit process using a control table that holds the primary key columns for all the tables and a procedure that spins through all the tables looking for duplicate counts based off those keys. After 2 years of adding tables with no validation for this, the first time I ran the audit 6 months ago, it found duplication in more than 10-20% of our tables. We're still cleaning up our legacy ETL feeds and removing duplicates. Looking for and cleaning up duplicates after they hit the system is very inefficient.