I'm trying to figure out what's the best way to "de-duplicate" data via DLT. Currently, my only leads are:

• Manage data quality with Delta Live Tables | Databricks on AWS
  • Via "Drop invalid records"
• Constraints on Databricks | Databricks on AWS
  • Via "pre-defined" table constraints + UC

I see problems with both of these options.

First, with "Expectations" this functionality seems a bit limited/cumbersome compared to what I would expect. I understand that I can set "filters" to trigger certain expectations, but it doesn't seem smart/sophisticated enough to deduplicate based on simple primary keys.

Second, with "Constraints" this functionality seems closer to what I'm looking for *however* (1) it doesn't look like it's programmatic at all (e.g. requires tables to be pre-defined), and (2) it seems to require Unity Catalog which we don't have enabled in our organization.

I'd like to ask, am I missing something? Data deduplication based on primary keys should be a fairly straightforward and industry standard practice. I would think Databricks/DLT should have a simple/intuitive way to handle this (especially for a company advocating for a medallion architecture), but I can't seem to find good leads on deduplicating data via DLT!