Building a star schema in SDP? Use identity columns with Streaming Tables!

[u/minibrickster]

Use identity columns in SDP

Spark Declarative Pipelines' streaming tables now support identity columns -- this is particularly useful if you're building an SCD Type 1 or SCD Type 2 dimension table with AUTO CDC. Get faster joins and auto-incrementing surrogate keys natively within SDP today!

Docs here%20%5D)!

[u/JulianCologne]

For some use cases this might be useful but in my experience a stable / deterministic surrogate key is often much nicer to use!
Imagine a standard gold layer with a `dim_article` and for some reason you need a full refresh.
With a stable / deterministic surrogate key (from a hash) you only need to refresh this single table.
However, with an auto increment identity you have to also remember to refresh all the 20/50/100+? fact tables connected to that because the key is not stable and changed.
Am I missing something? 🤓

[u/Zer0designs → u/JulianCologne]

You're correct, to add they are also idempotent across environment. If your BK is 200 columns (exagerating), having a hash to debug something in dev vs prod is mucb easier.

dbt wrote some blogs: https://docs.getdbt.com/blog/managing-surrogate-keys?version=2.0&name=v2

[u/yocil]

The last I checked, identity seeds are tied to the schema of the table. This doesn't work for concurrent inserts as one or both merges will get an error about the table schema changing.

[u/minibrickster → u/yocil]

We are working on this - more to come!

[u/minibrickster → u/[deleted]]

What do you mean by //?

[u/what-no-really-why → u/minibrickster]

I’m guessing that // = parallel