Suggest an Idea

[mhadley]

Audit type tables within Salesforce record data in fields with ANYTYPE datatype to account for the varying degree of types to be populated into the tables.  We need to the ability to pull these fields into Snowflake for reporting metrics based on activity performed.

[Shelley_Brennan]

Thank you for the suggestion.  For audit type tables, these are not currently supported by the Salesforce CDC API, as such is Full Load only acceptable for your use case?  Also, could you provide a list of the objects/tables you require?  Thank you!

[Shelley_Brennan]

Just checking again to see if we can get additional information requested above @mhadley - thank you!

[John_Roberts]

We are facing the same issue with ANYTYPE data types:

SalesForce History tables contain fields called NEWVALUE and OLDVALUE that are defined as ANYTYPE. A field that is actioned (i.e. update) generates a History row. The 'before' and 'after' data is stored in the OLDVALUE and NEWVALUE columns. These columns are defined as ANYTYPE because multiple data types can be present in one table and the 'value' columns need to capture any data type. Examples of table names are AccountHistory, ContactHistory etc.

I think there are ways to work around the CDC API limitations to be able to provide this data.

[Shelley_Brennan]

@John_Roberts  What data type would you like to see in the target for the ANYTYPE column?  Would CLOB make sense?  Thanks!

[John_Roberts]

Yes, we think CLOB datatypes would work for this.

[John_Roberts]

Here are the salesforce history tables we are interested in :

•   AccountHistory
•   CampaignHistory
•   CaseHistory
•   Consent__History
•   ContactHistory
•   FinServ_ContactContactRelation_History
•   LeadHistory
•   OpportunityFieldHistory
•   OpportunityHistory

Can you please provide a link to the Salesforce CDC API documentation, where it clearly states that these ANYTYPE fields are not supported by the API ?
(So I can push salesforce with an enhancement request).

Full load is not an option.  At production volumes, these history tables will have too much volume to do full loads on a daily basis.

Our stakeholders are saying they need the contents of the ANYTYPE fields in the history tables, so our team needs support on finding a solution to this.

Appreciate the support,

John

[Shelley_Brennan]

It is not that Salesforce CDC API does not support ANYTYPE data type, rather it does not support the History tables listed above.  The link to the Salesforce reference: https://developer.salesforce.com/docs/atlas.en-us.232.0.object_reference.meta/object_reference/sforc...

Also, what is your use case for replicating the history tables specifically?  Do you have latency requirements here?  This information will help us come up with a possible solution.

Thank you!

[John_Roberts]

Hi Shelley:

John has asked me to respond to your questions.

Our Business partner requires the History tables to be replicated for analytics purposes. An API is available to QLIK for replication of the History tables. The tables associated with that API can be found here: https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_associated_objects_history....

The expected latency would be between 5 and 10 minutes (closer to 5).

Connie

[Shelley_Brennan]

Thank you all for the additional input.  We do have it on our roadmap to add an incremental load option for those Salesforce objects not supported by the Streaming CDC API and will support ANYTYPE data type at this time as well.

[Meghann_MacDonald]

From now on, please track this idea from the Ideation portal. 

Link to new idea

Meghann

NOTE: Upon clicking this link 2 tabs may open - please feel free to close the one with a login page. If you only see 1 tab with the login page, please try clicking this link first: Authenticate me! then try the link above again. Ensure pop-up blocker is off.