Formula Column – Synapse Link for Dataverse

A Dataverse formula column is a dynamic column whose value is calculated using a Power FX formula. Similar to Calculated fields, a Formula column does not store data its value in Dataverse. Instead, the Power FX formula is executed whenever you retrieve a record, so you get the real time value of the formula column. My previous post explains how to create Formula columns and how they can handle null values which Calculated fields don’t support.

A good example of a Formula column is an age column.

When you retrieve a contact the age is calculated in real time.

Synapse Link for Dataverse allows you export your Dataverse data to an Azure Synapse serverless SQL pool. This process automatically exports the Dataverse data in CSV or Parquet format to an Azure storage account. Additionally, it exports the Dataverse metadata, which you can learn more about here. In addition, it automatically generates external tables in the serverless SQL Pool, allowing you to query your Dataverse table using standard TSQL.

If you use Synapse Link for Dataverse and formula columns then you need to know they do not play nicely. Formula columns stores their value when they are exported by Synapse Link for Dataverse. This is probably obvious as the data is written as raw data in a CSV file on Azure Storage. Just something to watch out for. So Joe Bloggs will remain 19 years old in Synapse until the next time the record is exported!