Capturing Dynamic Fields

[mdibaiee]

Some data sources expose dynamic fields (aka formulas). One such example is Salesforce, which allows you to define formula fields (think of them like views on a SQL database, which allow you to calculate a value at query time).

Fivetrain automatically materializes these fields as views on the destination, if such a function is supported. See their approach here.

It would be nice if we could also capture dynamic fields and materialize them as a dynamic field whenever possible.

[mdibaiee]

The most basic strawman approach I can think of: an annotation in JSONSchema of a collection which specifies its dynamic formula. This can be standardised at the capture so that each capture that wants to support this feature needs to write such a formula as a SQL select statement (we can probably go with SQLite's dialect since we use that for derivations), and in the materialization side we will need to support creating views for these fields.

[dyaffe]

Are formula fields on specific tables in Salesforce? If so, and the tables are small, would another potential option be to full refresh those tables and incrementally sync others?

[mdibaiee]

They can be anywhere, since users can add custom field formulas to any object, and that’s how they are mainly used: as custom fields.

[psFried]

I think this is a cool idea. I don't think this is something we should build right now. It'd be a high effort implementation, with an unknown, but presumably large number of corner cases. And these dynamic fields seem like a niche enough case that we'd be better off focusing our efforts elsewhere. Would be cool to dig up this discussion again once we're ready, though.