Computed Columns in Fabric Data Warehouse

[TwinkleCyril]

We’re exploring support for computed columns in Fabric DW - columns derived from expressions over other columns (for example, totals, normalized values, or derived flags). 

For eg: 

CREATE TABLE Products (

  Price decimal(10, 2),

  Tax decimal(10, 2),

  TotalPrice AS (Price + Tax) PERSISTED

);

These columns are not physically stored (unless marked as PERSISTED in SQL Server) but are computed on the fly during query execution. Computed columns are supported in SQL Server and Azure Synapse. 

Would this be useful for your workloads?

• What scenarios would you use computed columns for?
• Would you prefer persisted vs non‑persisted?
• Any performance or usability considerations we should keep in mind?

We’d love to hear your feedback.

[sam51]

IMHO this is too confusing between a regular table and a view. I would prefer that my columns in tables are always actual columns and that users cannot create the described calculated columns.

[BobDuffyIRL]

We have Zero use case for computer columns.. Even if you add this feature we wont use it (my team) If we wanted to have a column that was a calculation of other columns, we would have done that in the ETL. Now DW feature we could do with - Piecemeal backup. Just restore a few tables (yet, i know we have time travel) - Restore over top of existing DW or side by side - Scalar Function Support - Concurrency!

[koenverbeeck]

Yes I want them. Yes I use them (well, not in the Fabric Warehouse). I use them to calculate a hash over the columns to determine if a row has changed. @sam51: I don't see why this is confusing between a table and a view? There are interesting performance tuning techniques with computed columns, but since we can't create indexes in the warehouse, they don't matter 🙂

[rob_farley]

It has to be persistable. The idea is to create something (such as a hash) when the row is inserted or changed, and then know that the value is for the current set of values. I don’t want to have to work it out each time. Without computed column, it’s more compute every time the ETL runs.

[koenverbeeck]

@rob_farley exactly! Sometimes someone manually updates a table (for example a valid from field for a slowly changing dimension member, because there was an issue in the source system) and with computed columns the hash is recalculated automatically.

[frithjof_v]

I voted after reading the points made by @koenverbeeck and @rob_farley Those are good points.

Tbh I have more urgent need for better Git sync experience with Warehouse, as the Git sync fails due to not being able to deploy items in the right order and thus it fails every time due to missing dependencies. I have already voted for Ideas related to that issue.

Assuming that this is a completely separate feature that doesn't compete for the same Dev resources in the DW team I'm voting for this Idea as well.

Considerations:

• Must be persisted (otherwise we could simply create a view instead). This will also make the column values accessible by Spark and Direct Lake.
• Gets recalculated without delay any time a value it depends on gets updated. I.e. the computed column should be updated as part of the same transaction whenever a column value it depends on gets updated.

[jzimmer]

we use computed columns extensively on our azure sql database. our ETL process flows mostly through a singular, generalized pipeline and the persisted computed columns are incredibly helpful for standardizing records without much custom code once they're loaded to the bronze layer. having to refactor this logic is not a dealbreaker, but it would certainly make migration much more seamless if we were able to utilize persisted columns.