Forum Discussion
Advocate IVPower BI Composite Keys: Performance vs. Maintenance tradeoffs in a Lakehouse architecture
Super UserHey YevgenyM ,
I/we lean toward this:
No matter of the data architectecture, meaning if there is a lakehouse or not we are replacing business keys witha surrogate key, meaning integer values. We do not do that if the resulting semantic model is small, less than 1M rows in the largest table(s),
We are doing this using Power Query, if we are using Dataflows (Gen 1or Gen 2) we are making use of linked entities as we are on dedicated capacities. We load source data (..._raw), we transform the raw data, this includes creating the surrogate key (... transform), and finally we build the tables that are consumed by applications further downstream, e.g., semantic models (..._model).
As soon a lakehouse gets involved we are shifting moving the layered dataflows to the metal layers of the lakehouse. We have a tendency to replace dataflows with notebooks, but this is also because we have more data engineers that are familiar with Python than with SQL.
Hopefully this helps to tackle your challenge.
Regards,
Tom
- YevgenyM
I don't understand how you create the surrogate keys using integer values.
Which functions do you use?
Dataflow is not spark - it is not distributed MPP concept AFAIK- TomMartens
Hey,
I think this will provide an idea of how to create a surrogate key in Power Query / Dataflows: https://exceleratorbi.com.au/replace-guids-with-a-surrogate-key-for-better-performance/
And there are tons of examples of how to create a surrogate keys in Spark
Regards,
Tom
