How to include columns in your unique_key that change from NULL to populated

Help
1

The problem I’m having

I want to do incremental. Initially, some of the values in my unique key may be NULL but they could become populated later & at that time, they would be necessary to determine whether to determine if the record should be inserted or updated. If I don’t include the column, I end up with duplicates. If I use a COALESCE() and include a manufactured value, it acts like a type 2 and creates a new record when I really want it to replace that record.

The context of why I’m trying to do this

I have a report that shows assignment_completion progress. An assignment may have many items and be completed separately. A GUID in a particular table tells me if one of the assignments has been started. If I don’t include it (because they haven’t started any of the items yet), when they do start, it’s giving me an additional record. Should I only include the columns that will always be populated? If so, how do I avoid duplication?

What I’ve already tried

Some example code or error messages

Put code inside backticks
  to preserve indentation
    which is especially important 
      for Python and YAML!
2

It does not make a lot of sense with null keys imo but one thing you could do is simply exclude the null rows, then they will only appear later when the record is complete and the key has been added.

Note: @Lenny originally posted this reply in Slack. It might not have transferred perfectly.