The DataFrame API

Equality against a NULL gives NULL (unknown), which is not true. So an equality filter excludes NULL rows. This is SQL three-valued logic, the same as in Redshift. To include NULLs, you must test isNull() explicitly.

Source: https://spark.apache.org/docs/4.2.0/api/python/reference/pyspark.sql/functions.html

dropDuplicates on the full business key removes true duplicates. It keeps rows that differ on any part of the key. Dedup on order_item_id alone would wrongly collapse the cross-company collisions. distinct() removes only fully identical rows, so it misses partial duplicates.

Source: https://spark.apache.org/docs/4.2.0/api/python/reference/pyspark.sql/api/pyspark.sql.DataFrame.dropDuplicates.html

SUM over all-NULL input is NULL, not 0 (the same as in SQL). If you need 0, wrap the sum with coalesce(sum, lit(0)). count('amount') also ignores the NULLs, but count('*') does not.

Source: https://spark.apache.org/docs/4.2.0/api/python/reference/pyspark.sql/functions.html

-05:00 at 23:30 is 04:30 UTC the NEXT day. So the correct UTC date is 2026-03-11. Parsing the string to a timestamp uses the offset. Slicing the first 10 characters ignores the offset and gives the wrong day. This is the timezone-naive date-extraction bug.

Source: https://spark.apache.org/docs/4.2.0/sql-ref-datetime-pattern.html

explode and explode_outer spread an array into one row per element. explode_outer also produces a row for null or empty arrays. split only produces an array column (still one row). size returns the array length (one row).

Source: https://spark.apache.org/docs/4.2.0/api/python/reference/pyspark.sql/functions.html

The count of a specific column skips NULLs in that column. count() and count(lit(1)) count rows, whatever the values are. The semantics are the same as SQL COUNT(col) vs COUNT().

Source: https://spark.apache.org/docs/4.2.0/api/python/reference/pyspark.sql/functions.html

First reduce the data to the small distinct set with DataFrame operations. Then collect() that small result. If you collect the whole table, or use a UDF with side effects, you defeat the purpose of Spark and you risk a driver OOM.

Source: https://spark.apache.org/docs/4.2.0/api/python/reference/pyspark.sql/api/pyspark.sql.DataFrame.collect.html

withColumn is a lazy transformation that returns a new DataFrame. If you use an existing name, it replaces that column. Nothing changes in place and nothing runs until an action.

Source: https://spark.apache.org/docs/4.2.0/api/python/reference/pyspark.sql/api/pyspark.sql.DataFrame.withColumn.html