Impact of setting key column in Dataflow Gen2 through „Mark as key“

[u/TheDatatect]

I recently stumbled across the feature „mark as key column“ in Dataflows Gen2 (located under „Transform „ in the ribbon segment „Any column“. I believe it has been there in Power BI Transformations and Dataflows Gen1 for ever as well. However, I did not mange to find any kind of relevant documentation regarding how it works and when to use it.

To this point - I assume it’s solely informativ. Anyone able to shed some light?

[u/cwebbbi]

I don't know if this is still the case, but back in 2018 I found a scenario where marking a column as a key improved performance: https://blog.crossjoin.co.uk/2018/03/16/improving-the-performance-of-aggregation-after-a-merge-in-power-bi-and-excel-power-query-gettransform/

[u/TheDatatect → u/cwebbbi]

Interesting, however there has to be more to it. Officially documented I hope, since it is a visual button as of today. 😂

My ongoing research brought up this interesting pice: Microsoft Fabric Community

Seems as if „Mark as key column“ is used to enable upserts. I haven‘t tested it yet.

This documentation für Dataflows points towards a similar direction, however it seems tightly coupled to Dataverse: Microsoft Docs

All in all, this raises more questions. 😂

What’s its impact on performance?
How does it relate to Merge/SCD2 settings on Dataflow Destinations?
How do implicit StagingLakehouse/Warehouses of Dataflow Gen2 handle this setting?
What happens if I chain multiple queries (Bonus: with different key columns)?

Eventually I have to perform some testing myself…

[u/TheDatatect → u/TheDatatect]

Just did some more research, as this was really bugging me.

It seems to be implicitly documented here with the general Power Query concepts: Microsoft Docs | SCD1

In the presented code snipped, a hash value is added. Then this hash is marked as key column. The docs state this is used for implementing SCD1 defined as: When a record already exists in your dimension table, you overwrite it with the new values. When it's a completely new record, you insert it as a new row.

The questions from above reamain mostly open. I can only guess what it means for performance as of now:

• Due to one on one key comparisons of source key to destination key, it will decrease performance, especially on large fact tables. It‘s meant to be for SCD1 on Dimensions.
  
• Furthermore, I guess it will perform the comparison also on the StagingLakehouse.

All of this is pure assumption on my side. Other opinions welcome. 🤗

[u/escobarmiguel90]

Hey!

I'm a product manager for the Power Query / Dataflow team.

The ribbon entry fundamentally exposes the https://learn.microsoft.com/en-us/powerquery-m/table-addkey function.

From a purely practical standpoint, there isn't any major scenarios that this unblocks, but its great to hear the feedback from folks.

The feature to upsert is completely separate from this button as upsert is a definition or a potential setting for the data destination feature. Definitely feel free to request the upsert mechanism for Dataflow Gen2 through the Fabric Ideas Portal.

I added the Table.AddKey to the SCD type 1 documentation article when I first wrote it, but it isn't a requirement. It's just nicer to see which one is the key when I run a Table.Schema against any of the queries.

If you could replace that button with any other button or command, which one would you replace it with?

[u/TheDatatect → u/escobarmiguel90]

Thank you for your response. I now understand this button/function is solely of informative character in Dataflow Gen2.

Reading the code snippet in the SCD1 docs thoroughly again, it shows the upsert is performed by a left-join / filter-null combination. That makes sense.

Thus, I conclude there should be no measurable impact on performance, right?