idea Re: Allow replication of ANYTYPE data type from source Salesforce to target Snowflake in Suggest an Idea

Allow replication of ANYTYPE data type from source Salesforce to target Snowflake

mhadley — Mon, 05 Apr 2021 21:23:31 GMT

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.

Re: Allow replication of ANYTYPE data type from source Salesforce to target Snowflake - Status changed to: Open - New

Shelley_Brennan — Mon, 19 Apr 2021 13:27:29 GMT

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!

Re: Allow replication of ANYTYPE data type from source Salesforce to target Snowflake

Shelley_Brennan — Mon, 03 May 2021 13:19:42 GMT

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

Re: Allow replication of ANYTYPE data type from source Salesforce to target Snowflake

John_Roberts — Tue, 08 Jun 2021 00:20:09 GMT

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.

Re: Allow replication of ANYTYPE data type from source Salesforce to target Snowflake

Shelley_Brennan — Wed, 09 Jun 2021 19:33:51 GMT

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

Re: Allow replication of ANYTYPE data type from source Salesforce to target Snowflake

John_Roberts — Thu, 10 Jun 2021 19:33:40 GMT

Yes, we think CLOB datatypes would work for this.

Re: Allow replication of ANYTYPE data type from source Salesforce to target Snowflake

John_Roberts — Mon, 21 Jun 2021 13:54:37 GMT

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

Re: Allow replication of ANYTYPE data type from source Salesforce to target Snowflake

Shelley_Brennan — Mon, 26 Jul 2021 13:17:55 GMT

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

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!

Re: Allow replication of ANYTYPE data type from source Salesforce to target Snowflake

John_Roberts — Mon, 26 Jul 2021 17:07:03 GMT

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

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

Connie

Re: Allow replication of ANYTYPE data type from source Salesforce to target Snowflake - Status changed to: Open - On Roadmap

Shelley_Brennan — Tue, 10 Aug 2021 12:07:45 GMT

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.

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

Meghann_MacDonald — Wed, 02 Aug 2023 15:02:51 GMT

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.

Re: Allow replication of ANYTYPE data type from source Salesforce to target Snowflake - Status changed to: Closed - Archived

Ideation — Wed, 02 Aug 2023 15:02:53 GMT