Add Row Numbers using window function in PySpark

Adding row numbers in PySpark is achieved using the row_number() window function, which assigns a unique, sequential integer (1, 2, 3, ...) to each row within a defined partition, ordered by specified columns. This function is invaluable for use cases like deduplication, pagination, or identifying the top N records within specific groups.

The row_number() function operates over a Window specification, which defines how rows are grouped and ordered. The Window.partitionBy() clause establishes the logical groups (partitions) within which the row numbers are independently generated, analogous to a GROUP BY operation. Crucially, the orderBy() clause determines the exact sequence in which rows are numbered within each partition. This orderBy clause is mandatory for row_number() to ensure a deterministic and consistent assignment of sequential integers. Unlike rank() or dense_rank(), row_number() guarantees distinct numbers for every row, even if multiple rows share identical values in the orderBy columns, thus preventing ties.

For instance, to deduplicate records based on a combination of dept and salary, keeping only the first occurrence according to the salary order:

from pyspark.sql.window import Window
from pyspark.sql.functions import row_number
w = Window.partitionBy('dept').orderBy('salary')
df = df.withColumn('rn', row_number().over(w))
deduped = df.filter('rn == 1').drop('rn')

This approach provides a robust method for deduplication and can also be adapted for pagination (e.g., filter('rn BETWEEN 11 AND 20')). A significant consideration is that the partitionBy clause can trigger a Spark shuffle, a resource-intensive operation involving data movement across cluster nodes. This can impact performance, especially with large datasets or uneven data distribution (skew).

In the interview, also mention the performance implications of partitionBy due to potential data shuffles, and emphasize that orderBy is mandatory for row_number() to ensure deterministic and consistent row numbering within each partition.