Creating a row number column in spark sql

Question

I'm trying to create a temporary table that pulls data from an existing table and also introduces a new column that just produces a simple row number for the data pulled from the existing table. The order isn't based on any particular column, just the natural order that the data is already in.

My sql code to generate the table is as follows:

spark.sql(
            f"SELECT *, ROW_NUMBER() OVER(ORDER BY 1) AS SOURCE_ROW_NUMBER FROM {database}.{table} "
)

After running the full script I check the table where the data eventually ends up and my SOURCE_ROW_NUMBER column is filled with NULLs. The script isn't erroring out and nothing really happens to this table that could modify the output, so I'm wondering if I am just not writing the above sql query quite right.

Any help is appreciated. Thank you!