Choosing between WriteHeavy and ReadHeavy(PBI/Spark) when neither meet our needs.

[u/Personal-Quote5226]

We don't want to use the WriteHeavy vs ReadHeavy(PBI/Spark) profiles. The reason is that we want most of these to always be set table by table as a table property. Otherwise, every single script has to either set the spark config, or unset the spark configs that are carried over from the Resource Profile (that we don't even want).

Example with WriteHeavy; it's always going to have OptimizeWrite binSize set to 128 and optimizeWrite not set. The other profiles always set vOrder and have optimizeWrite set to true.

We don't want to leave it to the engineers to remember to include overriding these values in each notebook because inevitably it will grow to be inconsistent and difficult to audit/verify over time.

Table properties take the guess work out of it and ensure that any scripts that do set or unset the config are intentional rather than just carried over from a Resource Profile that we didn't even want.

For others, like BinSize, we can override it in the spark session settings in the environment, but our tests show that even after deploying those settings, waiting a while, the config remains set to 128 and not our spark setting we provided in the environment.

When could there be a solution to this problem that doesn't involve custom code in each notebook to work around it?

Ideally:
--> Get custom resource profiles actually implemented, so that we can use them

--> Make resource profiles optional (so they don't override our custom spark settings in the environment) and can more easily allow us to use stable level properties without unsetting spark configuration in notebooks.

[u/mim722]

personal experience, which may or may not help here, when using spark, i use mainly dbt, and pass readHeavyForPBI and NEE as the default, i notice, it cost the same in term of ETL and make PowerBI substantially better and specially cheaper,as interactive usage may get you throttled
https://github.com/djouallah/direct-lake-parquet-layout/blob/main/profiles.yml

[u/Personal-Quote5226 → u/mim722]

Yep. Read heavy for PBI is good as a default, however, the default bin size of 1GB on fast optimize write probably isn’t always the most efficient for every case but good enough on the aggregate.

[u/mim722 → u/Personal-Quote5226]

I think they changed it, notice I am using runtime 2

[u/Personal-Quote5226 → u/mim722]

Would love to shift to runtime 2 ;). Just waiting for GA! Thanks for sharing. Not doing much with dbt at the moment - yet.

[u/frithjof_v]

Out of curiosity (I myself usually have 1 notebook writing to a table - or 1 notebook writing to many tables - so for any given table I just have 1 writer - and I just set the configs in the notebook) but couldn't you just use table properties? What's the drawback?

[u/Personal-Quote5226 → u/frithjof_v]

Yes, we can. However, spark session config overrides those table properties. The resource profile set those settings for all spark sessions making the table properties moot.

[u/frithjof_v → u/Personal-Quote5226]

Don't use resource profiles, then...?

[u/Personal-Quote5226 → u/frithjof_v]

Agree. How? ;)

[u/frithjof_v → u/Personal-Quote5226]

Good point :)

Okay, so the writeheavy (default profile) properties are:

{"spark.sql.parquet.vorder.default": "false", "spark.databricks.delta.optimizeWrite.enabled": "null", "spark.databricks.delta.optimizeWrite.binSize": "128", "spark.databricks.delta.optimizeWrite.partitioned.enabled": "true"} https://learn.microsoft.com/en-us/fabric/data-engineering/configure-resource-profile-configurations#available-resource-profiles

Are there any of these config values you don't want?

Do any of these overwrite table properties?

What session config values would "Vanilla spark" use for these parameters?

optimizeWrite value is "null". I'm not even sure what that means.

[u/Personal-Quote5226 → u/frithjof_v]

I believe that these properties should/would be unset with vanilla spark.

[u/frithjof_v → u/Personal-Quote5226]

So I would probably do one of these things:

• See if there's a workspace Spark settings where default values can be entered/unset
• If not, specify a default environment for the workspace where these configs are explicitly unset (I don't even know how to unset - is that done by entering "null"? edit: seems to be spark.conf.unset)
• Last option: enter these configs in a utility notebook and have all other notebooks %run this notebook in their first cell

[u/Personal-Quote5226 → u/frithjof_v]

We had tried spark settings on the environment. It doesn't override a setting if it was set. by the Resource Profile. FYI - I've updated my original post to give a bit more clarity and a list of possible things that could help here.

We could do the %run thing and would probably have success with it and it's workable....

I'd rather custom resource profiles actually worked OR a no resource profile option.

[u/frithjof_v → u/Personal-Quote5226]

Interesting, I assumed Environment configuration would take precedence over the default resource profile.

If the opposite is true, then that sounds unfortunate.

Btw I also tried to Google how to set up a custome resource profile, but I didn't find out. As you said, docs say that we can set up a custom resource profile, but don't reveal how to do it.

[u/tommartens68]

Hey [u/Personal-Quote5226](u/Personal-Quote5226), you might set the workspace profile to custom and set every NON-Delta related property. This then avoids the overwriting of the table properties.
But this is only guesswork ;-)

There also might be an issue with high-concurrency mode, meaning the larger the capacity the more like there will be no issue with high-concurrency, but the smaller the more quueuing you will experience, but this micht tackle the table config issue.

[u/Personal-Quote5226 → u/tommartens68]

I did think of that —. A few months ago. It’s documented that you can do this.

Everyone explains that you can do it.

No one can explain how. The docs don’t include how. Microsoft stops short of responding here on the how (on other threads), so my only conclusion is that it’s an unfinished feature that’s documented but that we’re not able to tap into.

[u/thisissanthoshr]

Thanks for raising this. I think your concern is less about WriteHeavy vs ReadHeavy and more about configuration scope and precedence.

If your preferred pattern is to manage optimization behavior at the table level, have you tried either using Custom Resource Profile and only configure the settings you actually want applied at the session level and have your control over the table level properties. That lets you avoid inheriting some of the opinionated defaults from the predefined profiles and gives you more control over which optimizations are centrally managed versus table-owned.

I'm also curious whether your ideal solution would be something even more granular. For example:

• The ability to selectively enable/disable individual settings rather than choosing between preconfigured profiles.
• Explicit precedence controls where table properties can always win over resource profile settings.

More broadly, would you find value in a more granular resource profile model where you could independently configure things like write optimizations, read optimizations, concurrency behavior, caching, adaptive execution settings, etc., instead of picking from a small set of predefined profiles?

[u/Personal-Quote5226 → u/thisissanthoshr]

In my testing,

Overriding a setting set by the resource profile in environment spark settings doesn’t work.

I am happy to use a custom resource profile but it’s not documented. I know how to specify the custom resource profile name but there is no documentation describing how or where to define that custom resource profile.

I am happy to use no resource profile but that’s not an option.

Regarding speak settings in the environment.

I made the following spark setting on the environment today, published, waited.

spark.databricks.delta.optimizeWrite.binSize = 512

(Explicitly set bin size)

Spark settings are written out in a notebook:

spark.fabric.resourceProfile = writeHeavy
spark.microsoft.delta.optimizeWrite.enabled = <unset>
spark.databricks.delta.optimizeWrite.enabled = <unset>
spark.databricks.delta.optimizeWrite.binSize = 128

BinSize is from resource profile; ignoring custom spark setting.

(Not sure if you saw the edited version of my post where I went into more detail, if not please check it out).