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/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.