How Do I Sync the Content Field From the ContentNote Object?

Question

How do I sync the content field from the ContentNote object?

Environment

Connector: Salesforce

Answer

If you are using the Salesforce Enhanced Notes feature, you can access content values using Salesforce Files:

1. In Fivetran, go to your Salesforce connection page.
2. Select the Settings tab.
3. Click Edit connection.
4. Set the Sync Salesforce Files to your destination's object storage toggle to ON.
5. Click Save & Test.
6. Select the Schema tab and ensure the following tables are selected to sync: ContentNote, ContentDocument, ContentDocumentLink, and ContentVersion.
7. Allow the connection to sync with the updated settings.

To find Salesforce content notes, query the CONTENT_DOCUMENT table and filter file_type = 'SNOTE', then join to the CONTENT_VERSION table on CONTENT_VERSION.content_document_id = CONTENT_DOCUMENT.id. A template of this query is included below. The _fivetran_file_path column in CONTENT_VERSION tells you where the replicated file was stored.

SELECT
  d.id AS content_document_id,
  v.id AS content_version_id,
  v.title AS file_name,
  v.version_data_url AS salesforce_source_url,
  v._fivetran_file_path,
  CONCAT(
    'gs://<your-gcs-bucket>/<optional-folder>/salesforce_unstructured_test/content_version/',
    v._fivetran_file_path
  ) AS gcs_uri
FROM `<database>.<schema_name>.CONTENT_DOCUMENT` d
JOIN `<database>.<schema_name>.CONTENT_VERSION` v
  ON v.content_document_id = d.id

If you are not using the Salesforce Enhanced Notes feature, you can access content values using the Salesforce Note object.

Cause

We do not directly sync the Content field becuase it is stored by Salesforce as a file, and files need to be synced to object storage.