What challenges arise with duplicate records, and how do you address them?

Duplicate record challenges: Inconsistent analytics, incorrect aggregates, join explosions, compliance issues. Solutions: (1) Deduplication—use ROW_NUMBER() or DISTINCT with clear criteria; choose one row per key (e.g., latest by timestamp). (2) Prevention—unique constraints, idempotent pipelines, MERGE with conflict resolution. (3) Detection—dbt unique tests, row-count checks, checksum validation. (4) Golden record—MDM or survivorship rules for conflicting sources. Example: SELECT FROM (SELECT , ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) rn FROM raw) WHERE rn = 1. Best practice: Document dedup logic; validate in staging before load. Why it matters: Design choices compound at scale—wrong approach can cause 100× overhead. Scalability trade-offs: Profile before optimizing; validate on sample then full. Cost implications: Suboptimal choices multiply at billion-row scale.