SalesForce refresh failed to process query: INVALID_FIELD

Mahulima

We have scheduled refresh Jobs to refresh the Tables on daily basis. The Wfs are getting the status completed but the tables are actually not getting refreshed. I tried to manually refesh as well but that is also not working. This is happening when there is new column added in the tables and that tables is not getting refreshed. For Manual refresh getting this

we also have raised a ticket on this 3 weeks back but not got any resolution yet. Now it is happening in production so its a burning issue now. Please suggest some workaround until it is permanently fixed

HussainSyed

@Henry Simms Kindly help in above issue, as this is now an issue for us in our production environment as well. I have reported the same to experian support as well but I am yet to receive concrete solution or a workaround.

Josh Boxer

Hello

Are these Datasets connected to files in an External system? If so there is a bug fix in v2.11.6

I will also track down the Support ticket and investigate what has happened since they last gave you an update.

Mahulima

@Josh Boxer Its the Tables in External System. Our ticket Number is : 25510731

Josh Boxer

The team tested and are pretty confident that an upgrade will resolve your issue.

Thanks again for raising the support ticket

Mahulima

@Josh Boxer Hi, You mean upgrade to 2.11.6 will resolve the issue?

Josh Boxer

Yes upgrade to latest version and it should be resolved, you can let us know if not.

KatriM

@Josh Boxer Thanks for all the information! Please confirm to us that you have successfully tested this also with external database connection. Unfortunately we cannot do upgrades very often and especially we cannot do extra upgrades just for testing purposes. That is why it has to be confirmed that it has been tested also with external database connection. Thanks for your understanding!

Josh Boxer

Yes our engineering team tested against a database table with schema changes using the information you provided and it was successful for them in the latest version.

KatriM

@Josh Boxer Thanks a lot for confirmation! We'll do the upgrade on Monday then.

Mahulima

@Josh Boxer Hi, Even after upgrade my refresh issue is not resolved. getting error message in the log even though no error thrown while tried manual refresh on the external system tables.

' INVALID_FIELD: Jigsaw,JigsawContactId,IndividualId, X column ,Y column ^ ERROR at Row:1:Column:837 No such column 'X' on entity 'Contact'.

Caused by: java.sql.SQLException: [ApertureDataStudio][SForce JDBC Driver][SForce]InvalidBatch : Failed to process query: INVALID_FIELD:

Josh Boxer

Hi Mahulima, thanks for providing the log details as this looks to be a different issue than the one being described in the case and will help us to investigate further

I think the error from Salesforce might be this https://developer.salesforce.com/forums/?id=906F0000000BVQ1IAO, your credentials do not have access to the new field. If you can update the credential permissions in Salesforce?

In the meantime you could create a query in Data Studio Selecting only the columns you require that the credentials do have access to. https://docs.experianaperture.io/data-quality/aperture-data-studio-v2/get-started/configure-external-systems/#loaddatausingasqlquery~jdbc-databases

Mahulima

hi Josh, Credential to salesforce is working fine, there is no issue, the access can be field specific?

The columns are not added in Salesforce and instead its deleted from Salesforce and the Aperture instance has those columns which are deleted from Salesforce.

The Work around you suggested for the time being I think its very difficult for us to use. The process we follow is we bring the table and then create view on top of that table and that view we share across all spaces where this tables are required. In this case these 2 tables are our key tables and has 300+ columns. Query is ok to right but as the source itself will change the workflow mapping will break if I change the source. This sources are used in huge workflows , if the mapping gets broken then there will be many workflows to change as these are used in many workflows not just 1.

Josh Boxer

"The columns are deleted from Salesforce" - thanks for the additional info this is different from the one you described, but also helps.

If the columns no longer exists in the Source then you will need to update the Dataset schema marking these columns as optional https://docs.experianaperture.io/data-quality/aperture-data-studio-v2/get-started/change-dataset-schema/

henry_

The Salesforce driver also keeps a local copy of the Salesforce table schemas, which may need to be refreshed using the SchemaMap or CreateMap properties in the External System config (connection properties): https://docs.experianaperture.io/data-quality/aperture-data-studio-v2/get-started/configure-external-systems/#salesforce_salesforceschemahaschangedcolumnsortablesnotshown~jdbc-databases

As a test, see if you can load the table correctly as a new dataset in Aperture before attempting to refresh the existing dataset.

As per Josh's comment, for the existing dataset the missing columns would likely need to be set to Optional

Mahulima

@Josh Boxer @henry_ Suprisingly we are getting same error when we are trying to add this as a new dataset (not refresh). we are adding as new dataset then while adding we are getting the same error message.

Mahulima

@Josh Boxer

When trying to refresh I dont see the configure option as it says its matching. This is strange.

Josh Boxer

Temporarily in the new dataset use a query 'SELECT RECORDID FROM CONTACT' and see if that works successfully

Then try a new dataset with a new query 'SELECT Jigsaw,JigsawContact,Contact_Approval FROM CONTACT' as it seems these are the fields that Salesforce does not want to share

Assuming the first works and the second does not, either these fields exist but credentials do not have access or they no longer exist and you need to refresh the local copy of the schema as Henry mentioned above