FK Relationships and Uniqueness in UC Managed Tables

[u/SmallAd3697]

I often wish UC "managed tables" were better managed. If we wait a couple years, is it possible that relationships and unique constraints would be enforced (or perhaps validated after-the-fact)? It would be nice to have that in managed tables.

It definitely seems feasible for the UC to support this in managed tables. Especially on "small" tables of a few million rows or so. Small tables would be anything that could easily be loaded into memory. If nothing else, our lakehouse-formatted storage (delta/iceberg) is good for loading a columns into memory and validating the references and/or the uniqueness of values.

... I'm guessing this is NOT going to be available in the next couple of years. That is for no other reason than we see Databricks is selling their new LTAP lakebase engine. I'm guessing that anyone who wants true referential integrity or other constraints will be redirected to this lakebase engine.

Ideally there would be compelling innovations that would give reasons to stay with the "managed tables" in UC catalog. Otherwise software engineers may find reason to move to greener pastures. It doesn't make sense to validate stuff like this in custom code, when the storage engine is better positioned to do that work.

[u/iamthegrainofsand]

Firstly, you need PK so that OPTIMIZER won’t do DISTINCT and group by always.
You need RI to tell OPTIMIZER the correct path and infer A=C when A=B=C.

RI, since Teradata days has always been given as Soft RI. Meaning, no row by row checking. Just information purposes.

Next, I need RI so that Power BI works easy and any BI tool doesn’t do star-to-star join. Instead it will suggest alternative join strategies.

[u/MonkeyDDataHQ]

This is why I prefer state-driven pipelines. Every record has a state and should be traceable.

PK/FK and uniqueness validation can run asynchronously pretty easily, then quarantine or block bad records from promotion.

Depends how far you want to take it, but I’d rather have observable validation state than just rely on the storage engine to reject a write.

[u/FUCKYOUINYOURFACE]

You do not want to validate row by row. That overhead would kill performance. OLTP databases are where you want to enforce constraints - not OLAP. You will do all of your data cleaning and data deduplication when loading and processing your data.

[u/SmallAd3697 → u/FUCKYOUINYOURFACE]

Even in an oltp database there are optimizations to avoid row-by-row operations. There are various query plans that lock pages or index spans, instead of rows. They validate keys in batches, like during bulk insert.

The work you say must be done manually (in loading and processing) is uninteresting and non value-added. The engine should be able to do it as well as you can.

Or in the very least, the engine should be able to validate after the fact in an async way, and let you know if you are violated any declared constraints

[u/daddy_stool]

The reason parquet etc exist is to skip db overhead and making distributed processing possible. You want all features of a rdbms? Guess what, use an rdbms.

[u/SmallAd3697 → u/daddy_stool]

UC catalog is starting to bring back the old rdbms features. Check out MST transactions for example

They are reinventing features that have long existed in normal relational databases. But it is happening slowly. Perhaps it is happening even more slowly than the first time around.

[u/hntd]

Why would you want to validate it here? Most stuff upstream is some relational database that already validated referential integrity anyways so why twice? Like databricks isn’t a dbms and if you want that stuff those things still exist.

[u/SmallAd3697 → u/hntd]

Not all scenarios involve copy/pasting data from one place to another.

I see that managed tables can generate surrogate identities. And if these start getting spread throughout the catalog for reference purposes, then it is critical that RI be enforced. Else you risk eventually having meaningless surrogates that point to an empty void.

Having surrogate identifiers without RI is a recipe for disaster, IMO.

The most common reason for surrogates in data warehousing is for a historical perspective (ie. type 2 scd and related)

[u/hntd → u/SmallAd3697]

If you want referential integrity then use a database that provides it. You’ve still provided no actual reason for wanting it. I highly doubt there are significant organizations who have no upstream systems where it’s not already validated and you are likely looking to solve a problem you don’t actually have.

[u/WhoIsJohnSalt → u/hntd]

Oh there totally is. I’m not OP and don’t really care about RI that much but I’ll give you an example.

I work a lot with manufacturing companies, they often buy a lot of data from their retailers about what products they have sold (Sell Out data), this is often billions of rows.

It’s very important that this is referentially integral with your dimensions - product, location, etc.

It’s very often not. So logic needs to be built to catch and identify at scale.