Identify and remove duplicate records from a table, keeping the most recent record based on a timestamp column.

To remove duplicates keeping the most recent by timestamp: WITH ranked AS (SELECT , ROW_NUMBER() OVER (PARTITION BY key_columns ORDER BY timestamp_col DESC) rn FROM table_name) DELETE FROM table_name WHERE (key_columns, timestamp_col) IN (SELECT key_columns, timestamp_col FROM ranked WHERE rn > 1). Or create a new table: CREATE TABLE deduped AS SELECT FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) rn FROM raw) t WHERE rn=1. In Spark: df.dropDuplicates(['id']).orderBy(col('ts').desc())—but dropDuplicates is non-deterministic; use Window: from pyspark.sql.window import Window; w = Window.partitionBy('id').orderBy(col('ts').desc()); df.withColumn('rn', row_number().over(w)).filter('rn=1').drop('rn'). 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.