Smashed our heads against this one for a while and though I think it’s more of a spark question than a Databricks one, wanting to get your thoughts on it. Essentially the gist is this:

• We select into a DF from a delta table
• We display the DF and see 2 records:
  • one with ‘someColumn’ = 1 and ‘otherColumn’ = ‘A’
  • one with ‘someColumn’ = 1 and ‘otherColumn’ = ‘B’
• We run a dropDuplicates([‘someColumn’]) on the DF and store it in DF2
• We run display(DF2) and see the record with ‘otherColumn’ = ‘A’
• We run display(DF2.select(col(‘otherColumn’))) and see the record with ‘otherColumn’ = ‘B’

Somehow, even though we’re just selecting a column from DF2 in the second case, it causes what appears to be a re-evaluation of the dropDuplicates function and that re-evaluation comes in a different order, as after the select we’re seeing the other row than we see before the select. Reproducible results are important to us in this case, as the value of otherColumn is used as an identifier to drop the duplicate dependents from another table based on the duplicate parents that were dropped here.

Some other things that seem to affect the results are

• Inclusion or removal of a group by on the original query
• Inclusion or removal of some unused columns in the original query
• Selecting another column along with the ‘otherColumn’
• Persisting DF2 before selecting on it

Altering those conditions seems to affect what record we see after selecting but haven’t seen anything consistent enough to provide an explanation.

As far as I can hypothesize, dropDuplicates is a lazy function that may end up running multiple times when the DF is used throughout the pipeline, and the behavior of dropDuplicates is largely undefined, as far as what it keeps. Running a window function to assign a row number in the original query and filtering on that instead of using dropDuplicates seems to provide consistent behavior but finding it hard to accept that dropDuplicates is largely unusable in this case without some sort of confirmation on when and why.

The questions I have for you are:

• Have you seen anything like this before and if so, can you provide any insight on it?
• Is this purely a behavior of spark or the underlying frameworks, or could this be related to Databricks runtime or something we’re doing?